Table column's auto_increment flag not preserved by backup?

Question

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 · Accepted Answer · 2017-01-30T17:27:41.310

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.

In general, Mikhail should check the command he is using for making the dump. — Tero Kilkanen, Jan 30 '17 at 06:57
Should not `mysqldump` automatically do the necessary changes, if compatibility with PostgreSQL is requested? Or, at least, warn me about the problem? — Mikhail T., Jan 30 '17 at 17:22

Table column's auto_increment flag not preserved by backup?

1 Answers1