How to retrieve auto_increment value without insertion [mySQL]

Saeven

Hello,

in mySQL - I need to retrieve the current auto_increment value for a table without first having inserted anything. LAST_INSERT_ID() can thus not be used, and I can't do a SELECT MAX(auto_incremented_column) since the "highest" entry in that column might have been deleted, and it would return an incorrect result.

Any help appreciated.

Thanks.
Alex

LordShryku

http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html

drawmack

SHOW TABLE STATUS LIKE 'table_name'

This returns the following information:
Name, Type, Row_Format, Rows, Avg_Row_Length, Data_Length, Max_Data_Length, Index_Length, Data_Free, Auto_Increment, Create_Time, Update_Time, Check_Time, Create_Options and Comment.

You are interesed in Auto_Increment

For more information see pg 293 of the mysql manual available as a free download from http://www.mysql.com

Sxooter

any chance two people could ever run this script at the same time? If so you have a possible race condition.

drew010

you can easily calculate the next auto_increment value on MyISAM tables using (MAX(auto_increment_table) +1)

here is proof by example:

mysql> select postid from dump_dump;
+--------+
| postid |
+--------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+--------+
7 rows in set (0.00 sec)

mysql> delete from dump_dump where postid = 2;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT (MAX(postid)+1) FROM dump_dump;
+-----------------+
| (MAX(postid)+1) |
+-----------------+
| 8 |
+-----------------+
1 row in set (0.00 sec)

mysql> select count() from dump_dump;
+----------+
| count() |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)

still returns 8 (the next_autoincrement) even though there are only 6 items.

LordShryku

What if you deleted where id = 7? The it would have returned the max + 1 as 7, which is not what the table sees as the next auto_increment id. I'm thinking he's going for the table's next id, not just the max id + 1. Dunno why....

Sxooter

Start two mysql sessions. Type those commands one at a time into each session in parallel.

S1: select max(id)+1 from table; <- assign to i1
S2: select max(id)+1 from table; <- assign to i2
S1: Insert into table (id, txt) values (i1,'info');
S2: Insert into table (id, txt) values (i2,'moreinfo');

You should now get an error on a duplicate key on the second query.

this is a race condition. It's a pain in the ass to troubleshoot, only shows up just often enough to screw things up for a second then disappears for another month or two.

and they become more common as load increases. I.e. they're harder to find in testing, and impossible to find if you don't test with parallel threads.

Sxooter

The proper way of doing this is to insert a dummy record, grab the last insert id, build the data to put in, then put it in later.

But, this can leave records that have no data in them other than the id being set. So you might need a clean up script to run every so often. breaks in the sequence are a problem you just have to deal with if you need the sequence number ahead of time and allow the user to abort the inserting process at some point.

LordShryku

I'd think that if this was for inserting purposes, you'd just skipp the id and let the auto-increment work the way it's supposed to. By doing
Insert into table (id, field) VALUES ($newid, '$value')
You are forcing the id in place. If you were to just use
Insert into table (field) VALUES ('$value')
Then mysql would automatically assign the next id.