Mind the SQL_MODE when running ALTER TABLE

Posted in: Technical Track

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:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/alter-table-problems.html
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Interested in working with Augusto? Schedule a tech call.

4 Comments. Leave new

Augusto – thanks!
Have encountered this problem before but never took the time to investigate.

I suspect this SQL_MODE will yet surprise us all in mysterious bugs.

Reply
Log Buffer #129: a Carnival of the Vanities for DBAs
January 2, 2009 12:38 pm

[…] While I’m on the Pythian blog, I should mention also Augusto Bott’s advice to MySQL DBAs to mind the SQL_MODE when running ALTER TABLE. […]

Reply
Artículos destacados, Diciembre de 2008 | cambrico.net
January 6, 2009 3:31 pm

[…] del sql_mode cuando se ejecuta un ALTER TABLE, en The Pythian Group (en […]

Reply
code.openark.org » Blog Archive » Do we need sql_mode?
January 8, 2009 1:14 am

[…] Did we remember to set NO_AUTO_VALUE_ON_ZERO? Oh dear, we’ve dumped our database for backup, restored, but AUTO_INCREMENT values have changed! […]

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *