You can override MySQL behaviour of generating a new value if you insert a 0 into an auton_increment field.
Quoting from the manual: > NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. > Normally, you generate the next sequence number for the column by inserting > either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior > for 0 so that only NULL generates the next sequence number. > This mode can be useful if 0 has been stored in a table's > AUTO_INCREMENTcolumn. (Storing > 0 is not a recommended practice, by the way.) For example, if you dump the > table with *mysqldump* and then reload it, MySQL normally generates new > sequence numbers when it encounters the 0 values, resulting in a table > with contents different from the one that was dumped. Enabling > NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. > *mysqldump* now automatically includes in its output a statement that > enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem. > So you must alter the SQL-mode: Issue : mysql>SET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO; For altering the sessions sql_mode... now you will have to insert the dump with: mysql>SOURCE /path/to/dump.sql You can change it globally mysql>SET @@global.sql_mode = NO_AUTO_VALUE_ON_ZERO; But it's not recommend ... because until you change it back... MySQL will not generate auto increment values for your inserts if you use 0... only if you use NULL !!! The advantage is that you can import the dump from the command line. Another way is to add the statement: SET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO; into the dump itself... but... i think it's not that small... I believe this will do for you... but keep in mind that a value of 0 in an auto_increment column is not a good thing ... as everybody said before ! Good luck ! -- Gabriel PREDA Senior Web Developer
