Mysql – What happens when I reach the last id with MySQL InnoDB

MySQLmysql-5.5

So perhaps I don't know the technical name of the 'id' column which has autoincrement so if a similar question exists, I cannot find it. I was wondering about the limit of the number of rows in an InnoDB table and the id reference that goes with it, and what happens when the limit is reached (assuming it can be reached).

It's a risk I have to consider. I have a table which contains a series of tens to hundreds of records which can be added/removed regularly. Assuming my product reaches enormous success (?), assuming records are changed regularly, the id field will quickly tick by. Add a year or more, even five years…

I asked a similar question to this on stackoverflow some time ago and from what I recall I was just asked not to worry about it but nobody could direct me to a hard answer.

Anyone care to share?

Thanks…

Best Answer

Do not use GUIDs -- they have a serious performance problem at scale. (This is because of their randomness.)

Within 5 years (probably within 1 year), you will decide on some significant change and this 'last id' question will be moot.

You are "removing" regularly? Why? If you are doing a REPLACE or DELETE, you probably have some other unique key on which you are finding what to remove. Consider making that the PRIMARY KEY and eliminate the AUTO_INCREMENT.

I have a PRIMARY KEY on virtually every table I make, yet only about 1/3 are AUTO_INCREMENT.

Beware of INSERT IGNORE and REPLACE (and others) -- they pre-allocate AUTO_INCREMENT ids, but may not use them.

Related Solutions

Mysql – What happens when InnoDB hits its tablespace autoextend max

To answer the question what will happen, an error 1114 (ER_RECORD_FILE_FULL) will be generated when you try to insert new record. I've learned this by accident, while converting large MyISAM table to InnoDB, and there was a max setting left.

MySQL – What Happens When InnoDB Logspace is Too Small

MySQL can still write the changes to the binlogs (completed SQL commands) without concern for the results making it to disk.

InnoDB is responsible for flushing two-phase commits of data changes from logs to final destination (.ibd for innodb_file_per_table or ibdata1 otherwise). If InnoDB cannot flush data from its logs, there will be some housecleaning info stuck in the Redo Logs (ib_logfile0,ib_logfile1), the Undo Logs (1023 of them inside ibdata1) and the InnoDB Buffer Pool. Please see the InnoDB Infrastructure Map. Consequently, new transactions cannot never be written (or even started).

This is very possible if

innodb_file_per_table is disabled
ibdata1 has reached maximum size for the disk
No more room inside ibdata1 to write Undo Log entries
I wrote about this recently : How to solve "The table ... is full" with "innodb_file_per_table"?

Please run the following

Flush everything from all logs and shutdown mysql

mysql -uroot -p -Ae"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

Next, change the logs to have a larger size along with a larger log buffer

[mysqld]
innodb_log_file_size = 1G
innodb_log_buffer_size = 32M

Then, remove logs and start mysql

cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
service mysql start

I discussed this before : Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity

Give it a Try !!!

Best Answer

Related Solutions

Mysql – What happens when InnoDB hits its tablespace autoextend max

MySQL – What Happens When InnoDB Logspace is Too Small

Related Question