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

Question

発生した問題の概要

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へ取り込みを行ったところクエリのエラーが発生しました。

# ホスト、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サーバーにログインし、以下クエリを実行した際も同じエラーが発生したことを確認しています)

-- テーブル名のみ書き換えています
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制約が付いているクエリも見つかりました。

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の実行コマンドは以下です。

# ソケットがつながっている事は確認済み 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で吐かれたテーブル

mysql> show full columns from wp_12_hoge_fonts;

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
id	int unsigned	NULL	NO	PRI	NULL	auto_increment	select,insert,update,references
font_type	varchar(10)	utf8_general_ci	NO		NULL		select,insert,update,references
font_name	varchar(100)	utf8_general_ci	NO		NULL		select,insert,update,references
font_parameter	varchar(255)	utf8_general_ci	NO		NULL		select,insert,update,references

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

mysql> show full columns from wp_1000_blc_filters;

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
id	int unsigned	NULL	NO	PRI	NULL	auto_increment	select,insert,update,references
name	varchar(100)	utf8_general_ci	NO		NULL		select,insert,update,references
params	text	utf8_general_ci	NO		NULL		select,insert,update,references

information_schemaの差

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

mysql> select * from COLUMNS where TABLE_NAME = 'wp_12_hoge_fonts';

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	GENERATION_EXPRESSION	SRS_ID
def	wordpress	wp_12_hoge_fonts	font_name	3	NULL	NO	varchar	100	300	NULL	NULL	NULL	utf8	utf8_general_ci	varchar(100)			select,insert,update,references			NULL
def	wordpress	wp_12_hoge_fonts	font_parameter	4	NULL	NO	varchar	255	765	NULL	NULL	NULL	utf8	utf8_general_ci	varchar(255)			select,insert,update,references			NULL
def	wordpress	wp_12_hoge_fonts	font_type	2	NULL	NO	varchar	10	30	NULL	NULL	NULL	utf8	utf8_general_ci	varchar(10)			select,insert,update,references			NULL
def	wordpress	wp_12_hoge_fonts	id	1	NULL	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int unsigned	PRI	auto_increment	select,insert,update,references			NULL

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

mysql> select * from COLUMNS where TABLE_NAME = 'wp_1000_blc_filters';

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	GENERATION_EXPRESSION	SRS_ID
def	wordpress	wp_1000_blc_filters	id	1	NULL	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int unsigned	PRI	auto_increment	select,insert,update,references			NULL
def	wordpress	wp_1000_blc_filters	name	2	NULL	NO	varchar	100	300	NULL	NULL	NULL	utf8	utf8_general_ci	varchar(100)			select,insert,update,references			NULL
def	wordpress	wp_1000_blc_filters	params	3	NULL	NO	text	65535	65535	NULL	NULL	NULL	utf8	utf8_general_ci	text			select,insert,update,references			NULL

追記

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

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

確認不足ですみません…

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

Comments

[ito_yu]

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

[68user]

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

[ito_yu]

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属性の仕様が分かれば解決しそうです…