MySQL Bugs: #39758: Auto-increment value in backup dump

Bug #39758	Auto-increment value in backup dump
Submitted:	30 Sep 2008 14:51	Modified:	19 Feb 2010 14:40
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Susanne Ebrecht	CPU Architecture:	Any

Description:
This bug is related to bug #30957.

The same as in mysqldump happens in BACKUP.

ENGINE=MyISAM AUTO_INCREMENT=7

Doesn't matter which storage engine.

How to repeat:
CREATE TABLE t(id serial);
INSERT INTO t VALUES (1),(2),(4),(5),(6);

BACKUP DATABASE <your database> TO 'foo';

less var/foo

triage: set tag as CHECKED

See also Bug#47484. We explicitly take effort to restore the auto_increment value properly.

If you really want to be able to reset the auto_increment value, it would require SQL syntax to be added to the BACKUP and/or RESTORE statement.

Please suggest such syntax. And, btw, isn't it a feature request?

Susanne: What is actually the bug?  What auto_inc value do you *expect* for the CREATE TABLE statement and what value do you *get*?

Ingo: Assuming that one does:

  CREATE TABLE t(id serial);
  INSERT INTO t VALUES (1),(2),(4),(5),(6);
  BACKUP DATABASE <your database> TO 'foo';

1. What should be the stored autoinc value in the backup for the table (i.e. in the stored CREATE TABLE statement)?
2. What should be the value *after* doing a RESTORE?

So let me answer your questions.

In my eyes it is a serious bug and not a feature request.

Why?
when you have a new database project then typically you have a test machine for your development and testing.

After finishing testing you want to set up your database on production machine. Of course you not want to copy your test data.

The usual way to do this is to backup or dump only all DDL. Means all CREATE statements.

Unfortunately, we store here als the last auto_inc value. But of course on production machine you want to start all auto_inc values with 1.

I know auto_inc is not in sql standard but data type serial is and it is given in sql standard that serial will be reset when you do scenario above.

That our users can't decide here on backup/dump from only DDL if they want to reset or not reset the auto_inc value is a bug.

It is a serious bug because the user will get wrong data on his production server.

Hi Susanne,

if I understand your scenario correctly, it would also require that BACKUP can produce backup image files, which don't contain table data?

In this case you might want to file another (serious?) bug against MySQL backup. It cannot do this at the moment.

Given that BACKUP/RESTORE do *always* include the table data, what shall the auto_increment value be after RESTORE?

Regards
Ingo

-> Analyzing -> Susanne, as requested by her on IRC.

Closed it ... because of thinking errors.