質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
87.20%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

受付中

mysqlpump、mysqldumpでのプライマリキー制約について

ito_yu
ito_yu

総合スコア5

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0回答

0評価

1クリップ

126閲覧

投稿2022/06/23 03:42

編集2022/06/27 12:34

発生した問題の概要

mysqlpumpを使用したdumpファイル作成時にAUTO_INCREMENT列を含むテーブルのPRIMARY KEY制約がCREATE TABLEクエリ内にある時と別途ALTER TABLEで出力される時がある。
全て、CREATE TABLE時に付いて欲しい。
なおこの事象がAUTO_INCREMENT列のみに発生するものかは、まだ確認できていません。

経緯

wordpressで利用していたローカルのmysql Ver 5.1環境のデータベースをAWSのRDS mysql Ver 8.0.28環境に以降する為、ローカルのVer 5.1環境のデータをmysqldumpにてdumpし、ローカルのVer 8.0.28環境に取り込み、mysqld_safe起動時に--upgrade=FORCEを行いました。
ここまでは問題なかったのですが、upgrade処理後のVer 8.0.28環境からmysqlpumpを利用してawsに取り込む為のdumpファイルを作成し、mysqlクライアントでAWS RDSへ取り込みを行ったところクエリのエラーが発生しました。

bash

# ホスト、dumpファイルのパスは書き換えています。 mysql --host=example.rds.amazonaws.com --user=admin -p --database=wordpress < /path/to/test.dump
ERROR 1075 (42000) at line 89602: Incorrect table definition; there can be only one auto column and it must be defined as a key

上記エラーの実際の行は以下のとおりです。(dumpではなくmysqlサーバーにログインし、以下クエリを実行した際も同じエラーが発生したことを確認しています)

sql

-- テーブル名のみ書き換えています CREATE TABLE `wordpress`.`wp_12_hoge_fonts` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `font_type` varchar(10) NOT NULL, `font_name` varchar(100) NOT NULL, `font_parameter` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ;

エラー原因は、dumpファイル内のCREATE TABLEクエリの一部に指定されていたAUTO_INCREMENT列にPRIMARY KEY制約が記載されず、別途ALTER TABLEで指定されていたことが原因でした。
しかし、dumpファイル内を確認すると同じ構成のテーブルでもCREATE TABLEクエリのAUTO_INCREMENT列にPRIMARY KEY制約が付いているクエリも見つかりました。

sql

CREATE TABLE `wordpress`.`wp_1000_blc_filters` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `params` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ;

これについて、なぜ差が出ているのかが分かりません。
結局のところAWS RDSのmysql8.0環境にデータを取り込みたいのですが、
mysqlpumpでの出力時に『いづれのテーブルでもCREATE TABLEクエリ内PRIMARY KEY制約が出力される』様になれば最善かと考えています、原因や解決方法を知っている方がいらっしゃいましたらご教授頂きたいです。
なお、質問のタイトルを『mysqlpump、mysqldumpでのプライマリキー制約について』としていますが、データ容量が大きく時間がかかるため、mysqldumpでは試しておらず『おそらく』といった意味で載せています。

Ver 8.0.28環境でdumpファイルを作成した際のmysqlpumpの実行コマンドは以下です。

bash

# ソケットがつながっている事は確認済み DB名も記載の通り 出力先は書き換えています。 mysqlpump --socket=/var/lib/mysql/mysql.sock wordpress > /path/to/test.dump

原因調査

この差の原因について調べたことを記載します。
dump出力元のVer 8.0.28環境で以下クエリを実行してみましたが、結果としては比較した2つのテーブルでINCREMENT列の『id』にパラメータの違いが見つかりませんでした。

show full columnsの差

プライマリキーがALTERで吐かれたテーブル

bash

mysql> show full columns from wp_12_hoge_fonts;
FieldTypeCollationNullKeyDefaultExtraPrivilegesComment
idint unsignedNULLNOPRINULLauto_incrementselect,insert,update,references
font_typevarchar(10)utf8_general_ciNONULLselect,insert,update,references
font_namevarchar(100)utf8_general_ciNONULLselect,insert,update,references
font_parametervarchar(255)utf8_general_ciNONULLselect,insert,update,references

プライマリキーがCREATE TABLEで吐かれたテーブル

bash

mysql> show full columns from wp_1000_blc_filters;
FieldTypeCollationNullKeyDefaultExtraPrivilegesComment
idint unsignedNULLNOPRINULLauto_incrementselect,insert,update,references
namevarchar(100)utf8_general_ciNONULLselect,insert,update,references
paramstextutf8_general_ciNONULLselect,insert,update,references

information_schemaの差

プライマリキーがALTERで吐かれたテーブル

bash

mysql> select * from COLUMNS where TABLE_NAME = 'wp_12_hoge_fonts';
TABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_DEFAULTIS_NULLABLEDATA_TYPECHARACTER_MAXIMUM_LENGTHCHARACTER_OCTET_LENGTHNUMERIC_PRECISIONNUMERIC_SCALEDATETIME_PRECISIONCHARACTER_SET_NAMECOLLATION_NAMECOLUMN_TYPECOLUMN_KEYEXTRAPRIVILEGESCOLUMN_COMMENTGENERATION_EXPRESSIONSRS_ID
defwordpresswp_12_hoge_fontsfont_name3NULLNOvarchar100300NULLNULLNULLutf8utf8_general_civarchar(100)select,insert,update,referencesNULL
defwordpresswp_12_hoge_fontsfont_parameter4NULLNOvarchar255765NULLNULLNULLutf8utf8_general_civarchar(255)select,insert,update,referencesNULL
defwordpresswp_12_hoge_fontsfont_type2NULLNOvarchar1030NULLNULLNULLutf8utf8_general_civarchar(10)select,insert,update,referencesNULL
defwordpresswp_12_hoge_fontsid1NULLNOintNULLNULL100NULLNULLNULLint unsignedPRIauto_incrementselect,insert,update,referencesNULL

プライマリキーがCREATE TABLEで吐かれたテーブル

bash

mysql> select * from COLUMNS where TABLE_NAME = 'wp_1000_blc_filters';
TABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_DEFAULTIS_NULLABLEDATA_TYPECHARACTER_MAXIMUM_LENGTHCHARACTER_OCTET_LENGTHNUMERIC_PRECISIONNUMERIC_SCALEDATETIME_PRECISIONCHARACTER_SET_NAMECOLLATION_NAMECOLUMN_TYPECOLUMN_KEYEXTRAPRIVILEGESCOLUMN_COMMENTGENERATION_EXPRESSIONSRS_ID
defwordpresswp_1000_blc_filtersid1NULLNOintNULLNULL100NULLNULLNULLint unsignedPRIauto_incrementselect,insert,update,referencesNULL
defwordpresswp_1000_blc_filtersname2NULLNOvarchar100300NULLNULLNULLutf8utf8_general_civarchar(100)select,insert,update,referencesNULL
defwordpresswp_1000_blc_filtersparams3NULLNOtext6553565535NULLNULLNULLutf8utf8_general_citextselect,insert,update,referencesNULL

追記

はじめ、『プライマリキー制約が吐かれていない』と思っていたのですが、dumpファイル内に同テーブルに対してプライマリキーをALTERしているクエリが見つかったため大幅に修正しています。

sql

ALTER TABLE `wordpress`.`wp_12_hoge_fonts` ADD UNIQUE KEY `id` (`id`);

確認不足ですみません…

しかしながら、CREATE TABLEのタイミングでプライマリキー制約がつかなければエラーが発生することには変わりません。
CREATE TABLEとしてプライマリキーを出力する時と別途ALTERで出力される違いはどこから来るのでしょうか…

良い質問の評価を上げる

以下のような質問は評価を上げましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

  • プログラミングに関係のない質問
  • やってほしいことだけを記載した丸投げの質問
  • 問題・課題が含まれていない質問
  • 意図的に内容が抹消された質問
  • 過去に投稿した質問と同じ内容の質問
  • 広告と受け取られるような投稿

評価を下げると、トップページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

ito_yu

2022/06/23 04:05 編集

『質問への追記』としてレコードをソースのまま追記したかったのですが、本文と解決方法以外にはマークダウンが使えないのですね… この投稿の削除リクエストは手間がかかりそうなので、中身だけ消しました。 本文のレコードに関して見辛くてすみません。
68user

2022/06/25 04:30

原因はわかりませんが、バグっぽい感じはするので調査するより DDL を更新してしまった方が早いかもしれませんね (手動なり自作スクリプトなりで)。
ito_yu

2022/06/27 03:34

68userさん DDLを更新すれば…というアドバイス、ありがとうございます。 1つ気になったのですが、mysqlpumpでのdumpの場合、現在のテーブル構成に基づいてDDLを生成しているのかと思っていたのですが、テーブル作成時に実行したDDLを出力しているのでしょうか? mysqlpumpで出力されるDDLについてそのあたりが記載されている資料が見つけられず… もし、テーブル作成時のDDLが出力されるのだとすれば、テーブル作成時のmysqlバージョンが5.1環境(これも資料が少ない…)だったため、この頃AUTO_INCREMENT属性にPRIMARYまたは、UNIQUE KEYの制約が必須ではなかったのであれば、当時のテーブル作成時に実行されたCREATE TABLEにも上記の制約の指定がなかった可能性があります。 実際のテーブル作成時のDDLはフレームワーク内で作成しているので、どのような形でクエリを作成しているのかは不明ですが、mysqlpumpのDDL出力の仕様とmysql5.1のAUTO_INCREMENT属性の仕様が分かれば解決しそうです…

まだ回答がついていません

会員登録して回答してみよう

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
87.20%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。