Mind the SQL_MODE when running ALTER TABLE

The other day, a client mentioned they were getting strange results when running ALTER TABLE. The episode involved modifying an existing primary key to add an auto_increment primary key: it was “shifting” values. Say what?!

As it turns out, it was a very special value getting changed: zero. Some fiddling revealed the underlying reason. Care to join me?

To understand what’s going on, follow the example below as we start with an empty database, create a table and insert a few rows:

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table test_table (id int not null primary key) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> desc test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI |         |       | 
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into test_table (id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table (id) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table (id) values (0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+----+
| id |
+----+
|  0 | 
|  1 | 
|  2 | 
+----+
3 rows in set (0.00 sec)

mysql>

Now let’s change our PK and make it auto_increment (please notice we get zero warnings):

mysql> alter table test_table modify id int not null auto_increment, auto_increment=3;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test_table;
+----+
| id |
+----+
|  1 | 
|  2 | 
|  3 | 
+----+
3 rows in set (0.00 sec)

mysql>

But . . . what happened? Did MySQL just generate a new sequence of auto_increment values as suggested in the description of this bug: ALTER TABLE regression in 5.0? Probably not, as we didn’t drop the PK and recreate it. Further investigation is needed to determine the cause of this apparently odd behaviour.

The next step was to verify that MySQL allows a value of zero on an auto_increment primary key (again, no warnings):

mysql> update test_table set a=0 where a=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_table;
+---+
| a |
+---+
| 0 | 
| 1 | 
| 2 | 
+---+
3 rows in set (0.00 sec)

mysql>

And we have our test_table with the same values as we wanted, but that’s not a solution.

Let’s create a more complete example to check if the data gets mixed and reordered as well, shall we?

mysql> drop table test_table;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test_table (id int not null primary key, data varchar(255)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_table (id, data) values (4,'two');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table (id, data) values (3,'one');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table (id, data) values (2,'zero');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+----+------+
| id | data |
+----+------+
|  2 | zero | 
|  3 | one  | 
|  4 | two  | 
+----+------+
3 rows in set (0.00 sec)

mysql>

Let’s add our special value and change that PK into auto_increment:

mysql> insert into test_table (id, data) values (0,'FIVE');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+----+------+
| id | data |
+----+------+
|  0 | FIVE | 
|  2 | zero | 
|  3 | one  | 
|  4 | two  | 
+----+------+
4 rows in set (0.00 sec)

mysql> desc test_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI |         |       | 
| data  | varchar(255) | YES  |     | NULL    |       | 
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table test_table modify id int not null auto_increment, auto_increment=5;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc test_table;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment | 
| data  | varchar(255) | YES  |     | NULL    |                | 
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from test_table;
+----+------+
| id | data |
+----+------+
|  2 | zero | 
|  3 | one  | 
|  4 | two  | 
|  5 | FIVE | 
+----+------+
4 rows in set (0.00 sec)

mysql>

The good news: MySQL is not reordering my data. Data is (mostly) preserved. The bad news: I don’t yet have a clue what’s going on.

At this point, something came to mind: exactly how does MySQL do an ALTER TABLE operation? A few more minutes reading the manual’s “Problems with ALTER TABLE” led me to a theory based on the description from the manual:

ALTER TABLE works in the following way:

• Create a new table named A-xxx with the requested structural changes.
• Copy all rows from the original table to A-xxx.
• Rename the original table to B-xxx.
• Rename A-xxx to your original table name.
• Delete B-xxx.

That got me thinking. What if copying all rows is actually implemented by INSERTing rows from the old into the new table? This idea is supported by the ALTER TABLE manual page:

• To use ALTER TABLE, you need ALTER, INSERT, and CREATE privileges for the table.

So let’s check our SQL_MODE:

mysql> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    | 
+--------------------+
1 row in set (0.00 sec)

Empty? That must mean . . . default. A few moments later, after I got the chance to review the documentation on SQL_MODE, I loaded our data again:

mysql> select * from test_table;
+----+------+
| id | data |
+----+------+
|  0 | FIVE | 
|  2 | zero | 
|  3 | one  | 
|  4 | two  | 
+----+------+
4 rows in set (0.00 sec)

But this time, we want to change the default behaviour:

mysql> set sql_mode='NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_mode;
+-----------------------+
| @@session.sql_mode    |
+-----------------------+
| NO_AUTO_VALUE_ON_ZERO | 
+-----------------------+
1 row in set (0.00 sec)

We recreate our table, insert the same sample data and give it a shot:

mysql> alter table test_table modify id int not null auto_increment, auto_increment=5;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test_table;
+----+------+
| id | data |
+----+------+
|  0 | FIVE | 
|  2 | zero | 
|  3 | one  | 
|  4 | two  | 
+----+------+
4 rows in set (0.00 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql>

And that looks just the way we wanted it!

Useful links:
https://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
https://dev.mysql.com/doc/refman/5.0/en/alter-table-problems.html
https://dev.mysql.com/doc/refman/5.0/en/alter-table.html