大网数据广告活动
服务器运维问答

Table column's auto_increment flag not preserved by backup?

· 52CE 我爱测

After dumping a database on MySQL-5.1.73 and loading it into MySQL-5.5, I notice, that at least one table's primary key didn't have its auto_increment flag transferred... The describe table on the original reads thus: +----------…

问题原文

原帖链接 · Mikhail T. · CC BY-SA 3.0

After dumping a database on MySQL-5.1.73 and loading it into MySQL-5.5, I notice, that at least one table's primary key didn't have its auto_increment flag transferred... The describe table on the original reads thus:

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| FIELD_ID  | bigint(20)   | NO   |     | 0       |                |
| ID        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| ISSUE_ID  | bigint(20)   | NO   | MUL | 0       |                |
| LOCK_HASH | varchar(255) | YES  | MUL | NULL    |                |
| LOCK_TIME | bigint(20)   | YES  |     | NULL    |                |
| RANK      | varchar(255) | NO   | MUL | NULL    |                |
| TYPE      | int(11)      | NO   |     | 0       |                |
+-----------+--------------+------+-----+---------+----------------+

But, after loading into the target, the last column is empty -- auto_increment flag of the ID-field is gone.

Indeed, looking into the dump, I see the table's definition as:

CREATE TABLE "AO_60DB71_LEXORANK" (
  "FIELD_ID" bigint(20) NOT NULL DEFAULT '0',
  "ID" bigint(20) NOT NULL,
  "ISSUE_ID" bigint(20) NOT NULL DEFAULT '0',
  "LOCK_HASH" varchar(255) COLLATE utf8_bin DEFAULT NULL,
  "LOCK_TIME" bigint(20) DEFAULT NULL,
  "RANK" varchar(255) COLLATE utf8_bin NOT NULL,
  "TYPE" int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY ("ID"),
  KEY "index_ao_60db71_lexorank_rank" ("RANK"),
  KEY "index_ao_60db71_lex604083109" ("ISSUE_ID"),
  KEY "index_ao_60db71_lex1632828616" ("LOCK_HASH")
);

There is no obvious instruction to set ID to automatically increment... The non-default flags used with mysqldump were:

extended-insert=false
compatible=postgres
single-transaction

Is this a known problem? Should I attempt a bug-report with MySQL (Oracle)?

已采纳解答

原帖链接 · Moshe Katz · CC BY-SA 3.0

AUTO_INCREMENT is a MySQL-only option (other Database software uses different commands -- for example, PostgreSQL uses a special column type called SERIAL).

Therefore, when you export with --compatible=postgres it will not be included in the dump.

You can either stop exporting with the PostgreSQL compatibility flag, or you can manually add the AUTO_INCREMENT option. In either case, if you actually plan to import this schema into PostgreSQL, you will have to modify the dump file to change the column type to SERIAL for any column that you want to auto increment.


Note that, despite the name, the --compatible option does not transform the schema into a form that is guaranteed to work in the database you chose.

All that the option does is remove things that are unique to MySQL so that you can more easily finish the transformation yourself, either by hand or using some kind of script that you have built.

From the documentation:

This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example, --compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2017-01-30)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。