Mydumper/myloader does not care for 0-s in AUTO_INCREMENT fields

[Andras Fabian]

While restoring quite a few databases (during a migration with mydumper/myloader), we found that one of our applications threw a strange error. After some investigation it turned out, that in a table with a primary key, the entry with ID=0 was missing (which was there in the original table).

This column is an AUTO_INCREMENT field and mydumper/myloader INSERTs all the rows correctly ... just the one with ID=0 is wrong (well, it gets the new highest ID instead).

Obviously this is a "problem" with how Mysql handles identity inserts. NULLs and 0s are remapped to the new highest value.

BUT, the original mysqldump (and a restore of its export) still handles it correctly .... because they use a special setting:
"NO_AUTO_VALUE_ON_ZERO"
http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

It can be seen in each mysqldump script at the beginning:
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

The question is: would it be possible to include something like this in Mdumper/myloader too ???

And maybe it would be interesting to evaluate all the "special" setting mysqldump puts in its script (just in case, there are other "weirdnesses" it needs to take care off).

[Domas Mituzas (domas-mituzas)]

Zeroes in auto-inc fields, eh? :) I'd prefer this to be part of myloader than mydumper, I guess. Having pure data files is a good property, and any loading script can prepend options itself: (echo "..."; cat file) | mysql

[Andras Fabian (fabian-atrada)]

Well, yes, I know that 0 in an auto-inc field is not good style, but sometimes developers tend to have "brilliant" ideas :-) Nevertheless, I as the DB-Admin need to take care of things like this (which need to work, even if they are "not recommended") ... and thats the obvious reason, why the standard Mysql dumper has this a "buil in feature".

Now in the case of mydumper/myloader its irrelevant how its technically solved. And I completely agree, that it might be better to keep the dumps themselves clean (on the other hand even now there are already two setting written into them ...so). So it might be better to leave this ti myloader ... either as default behavior, or something we can "activate" via a simple command line setting.

[Max Bubenick (max-bubenick)]

Im ok with pure data files is a good property, but sometimes data integrity/consistency depends on statements like the one discussed here, and for me all statement that ensure this should be in the same place as the data. thoughts?