MySQL Auto Increment Fun

moderndba MySQL,

Originally Posted 10/21/2014

Coming from a heavy Microsoft SQL Server background the concept of the AUTO_INCREMENT attribute in MySQL is very familiar.  In SQL Server we call it the IDENTITY attribute, but they are conceptually very similar.  When a row is inserted into the table the column specified with the AUTO_INCREMENT attribute automatically gets the next value in a sequence.  But while they are conceptually very similar, there are a number of differences that a SQL Server Database Administrator should be aware of.  To start with lets create a database and a table we will use as we explore the AUTO_INCREMENT attribute.  You can copy the following script to a file on your system named “auto_increment.sql”.  Or you can simply run the commands from the mysql prompt to generate the objects and seed the table with data.

CREATE DATABASE IF NOT EXISTS moderndba;

 USE moderndba;

 CREATE TABLE IF NOT EXISTS autofun
 (
       employee_id        INT UNSIGNED NOT NULL AUTO_INCREMENT
     , employee_name      VARCHAR(150) NOT NULL
     , employee_level     TINYINT UNSIGNED NULL
     , PRIMARY KEY (employee_id)
 );

 INSERT INTO autofun (employee_name, employee_level)
 VALUES
  ('Joe Smith', 0)
, ('Tim Bradford', 1)
, ('Betty Stafford', 1);

If you chose to copy the script and run it you can run the script as follows.

[student@percona56 ~]# mysql -u root -p < auto_increment.sql

In SQL Server you can insert directly into an IDENTITY column, but you have to turn on IDENTITY_INSERT first. You can only have it turned on for one table at a time and you need elevated permissions to do so. Let’s see what happens when we try to insert directly into a AUTO_INCREMENT column in MySQL. Run the following statements from the MySQL prompt.

[student@percona56 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24-72.2-log Percona Server (GPL), Release 72.2, Revision 8d0f85b

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE moderndba;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A

 Database changed
 mysql> INSERT INTO autofun
     -> (employee_id, empoyee_name)
     -> VALUES
     -> (10, 'John Wiley');
 Query OK, 1 row affected (0.03 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |          10 | John Wiley     |           NULL |
 +-------------+----------------+----------------+
 4 rows in set (0.00 sec)

 mysql>

As you can see, in MySQL you can insert directly into the identity table as the default behavior. But what happens when we go to add the next row. Will the employee_id be a 4, an 11, or some other number? Let’s see.

mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Amanda King');
 Query OK, 1 row affected (0.03 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 +-------------+----------------+----------------+
 5 rows in set (0.00 sec)

 mysql>

Ok. That makes sense. The last row inserted was a 10, so the next row would be safest obviously to use the last value plus one. But if this is the case, surely when you insert a value into the column explicitly it must be greater than the largest value in the column, correct? Let’s find out. We can run the following commands and test out this theory.

mysql> INSERT INTO autofun
     -> (employee_id, empoyee_name)
     -> VALUES
     -> (5, 'Sarah Wagoner');
 Query OK, 1 row affected (0.03 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 +-------------+----------------+----------------+
 6 rows in set (0.00 sec)

 mysql>

Alright, you can insert into the middle of the series. Well, for safety it will increment the column from the largest value and not the last then, right?

mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Mia Brown');
 Query OK, 1 row affected (0.03 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 +-------------+----------------+----------------+
 7 rows in set (0.00 sec)

 mysql>

Yes it does. That seams pretty safe, since you could easily run into problems using the number after the last number used if you allowed inserts into the middle of the series. But it brings up a specific risk that DBAs and Developers need to be aware of. Remember from the script above we made the employee_id field an unsigned integer. The maximum value we can store in an unsigned integer in MySQL is 4,294,967,295. The risk is probably a lot lower on an unsigned integer than on a signed tiny integer (1 byte with a maximum value of 127), but what will happen if someone accidentally inserts a row with a value for employee_id towards the end of the range. Let’s take a look and see what happens.

mysql> INSERT INTO autofun
     -> (employee_id, empoyee_name)
     -> VALUES
     -> (4294967294, 'Brooke Allen');
 Query OK, 1 row affected (0.03 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 |  4294967294 | Brooke Allen   |           NULL |
 +-------------+----------------+----------------+
 8 rows in set (0.00 sec)

 mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Chris Walters');
 Query OK, 1 row affected (0.03 sec)


 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 |  4294967294 | Brooke Allen   |           NULL |
 |  4294967295 | Chris Walters  |           NULL |
 +-------------+----------------+----------------+
 9 rows in set (0.00 sec)

 mysql>

So everything is cool so far, but we are now up against the limit of the range for an unsigned integer, and we got there with only two inserts. (We could have gotten there with one insert if we really wanted.) So what happens when we go to add the next auto incrementing row to the table. Lets find out.

mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Ben Forest');
 ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
 mysql>

So we have reached the end of the range and when MySQL tries to grab the next value larger than the largest value in the column it can’t and therefore grabs the same maximum value for the data type. This leads to the error of duplicate primary keys. But what if we remove the rows from the table, then what? Is it really looking at the maximum value in the table, or the maximum value ever inserted. Lets see.

mysql> DELETE FROM autofun WHERE employee_name = 'Chris Walters';
 Query OK, 1 row affected (0.05 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 |  4294967294 | Brooke Allen   |           NULL |
 +-------------+----------------+----------------+
 8 rows in set (0.00 sec)

 mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Ben Forest');
 Query OK, 1 row affected (0.03 sec)

 mysql>  SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 |  4294967294 | Brooke Allen   |           NULL |
 |  4294967295 | Ben Forest     |           NULL |
 +-------------+----------------+----------------+
 9 rows in set (0.00 sec)

 mysql> DELETE FROM autofun WHERE employee_name IN ('Ben Forest', 'Brooke Allen');
 Query OK, 2 row affected (0.02 sec)

 mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Nina Gardner');
 Query OK, 1 row affected (0.03 sec)

 mysql>  SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 |  4294967295 | Nina Gardner   |           NULL |
 +-------------+----------------+----------------+
 8 rows in set (0.00 sec)

 mysql>

So it is not using the largest value in the column plus one. Instead it really is using the next value after the largest value ever inserted. The only issue is that when you get to the end of the range, instead of raising an error that the next value is out of range it takes the closest value in the range and tries to use it. This can be an issue since we are dealing with the primary key on the table, what does this behavior mean to other tables with relationships back to this key.

I understand that this is an edge case, and most of the time you will not need to worry about this. But it is an interesting behavior and one that I have not seen in other relational database management systems. I hope you found this as interesting as I have.

One thought on “MySQL Auto Increment Fun”

  1. […] Last blog post I started to explore some of, what I found to be, the interesting behaviors surrounding the AUTO_INCREMENT attribute in MySQL.  With strict mode not set it does some really strange things.  Like allowing you to add the max value in the range over and over again as long as you delete the record that used to hold that value.  This could cause some pretty interesting issues in applications that are not storing their foreign key relationships in the database.  But I wanted to see how strict mode would impact the behavior.  So I rebuilt the environment using the following script file. […]

    Like

    Reply

Leave a comment