MySQL Bugs: #74017: Failing assertion: current <= max_value in file ha

Bug #74017	Failing assertion: current <= max_value in file ha_innodb.cc line 1473
Submitted:	22 Sep 2014 16:33	Modified:	23 Sep 2014 5:34
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5.23	OS:	Linux (CentOS 5)
Assigned to:		CPU Architecture:	Any
Tags:	assert, Failing assertion: current <= max_value

Description:
A master crashed recently when we had a table with an id value close to the id range's max value:

the crash report was:

140922 18:01:42  InnoDB: Assertion failure in thread 1275046208 in file ha_innodb.cc line 1473
InnoDB: Failing assertion: current <= max_value
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
16:01:42 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=805306368
read_buffer_size=4194304
max_used_connections=186
max_threads=200
thread_count=37
connection_count=36
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1821846 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2ad88923ebc0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 4bffa0b0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7d2a55]
/usr/sbin/mysqld(handle_fatal_signal+0x3e1)[0x68b441]
/lib64/libpthread.so.0[0x3e4fa0eb10]
/lib64/libc.so.6(gsignal+0x35)[0x3e4ee30265]
/lib64/libc.so.6(abort+0x110)[0x3e4ee31d10]
/usr/sbin/mysqld[0x7dc96b]
/usr/sbin/mysqld[0x7e80a1]
/usr/sbin/mysqld(_ZN7handler21update_auto_incrementEv+0x20a)[0x68e4da]
/usr/sbin/mysqld[0x7e50d8]
/usr/sbin/mysqld(_ZN7handler12ha_write_rowEPh+0x54)[0x691144]
/usr/sbin/mysqld(_Z12write_recordP3THDP5TABLEP12st_copy_info+0x74)[0x561f84]
/usr/sbin/mysqld(_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0xe44)[0x569494]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x39ca)[0x57b49a]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10b)[0x57dd9b]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x14fb)[0x57f31b]
/usr/sbin/mysqld(_Z10do_commandP3THD+0xc4)[0x57f694]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xef)[0x61902f]
/usr/sbin/mysqld(handle_one_connection+0x54)[0x619124]
/usr/sbin/mysqld(pfs_spawn_thread+0x5b)[0x902b4b]
/lib64/libpthread.so.0[0x3e4fa0673d]
/lib64/libc.so.6(clone+0x6d)[0x3e4eed44bd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (2ad87cf3f9e0): is an invalid pointer
Connection ID (thread ID): 144374624
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
140922 18:01:59 mysqld_safe Number of processes running now: 0

The rpm involved is: MySQL-server-5.5.23-1.rhel5  (community version)

How to repeat:
This was triggered when a table defined as below:

root@myhost [mydb]> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=65535 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I first did this:

root@myhost [mydb]> alter table mytable modify id mediumint unsigned not null auto_increment;
Query OK, 65534 rows affected (1.75 sec)
Records: 65534  Duplicates: 0  Warnings: 0

Then because some tables that use this table might trigger issues if they xxx_id column were not adjusted accordingly, I changed it back again.

alter table AdminAction modify id smallint unsigned not null auto_increment; 

Immediately afterwards it seemed the server crashed with the given stack trace.

Suggested fix:
I realise this is an old version of MySQL so the issue may be known, and may be fixed, but just in case I'm reporting the problem here.

Check http://bugs.mysql.com/bug.php?id=66836. According to Miguel it was repeatable with 5.5.27, but not later versions.

Version: '5.5.23'  socket: ''  port: 3307  MySQL Community Server (GPL)
InnoDB: Assertion failure in thread 2808 in file ha_innodb.cc line 1473
InnoDB: Failing assertion: current <= max_value

mysqld.exe!innobase_next_autoinc()[ha_innodb.cc:1473]
mysqld.exe!ha_innobase::get_auto_increment()[ha_innodb.cc:10170]
mysqld.exe!handler::update_auto_increment()[handler.cc:2463]
mysqld.exe!ha_innobase::write_row()[ha_innodb.cc:5168]
mysqld.exe!handler::ha_write_row()[handler.cc:4808]
mysqld.exe!write_record()[sql_insert.cc:1734]
mysqld.exe!mysql_insert()[sql_insert.cc:931]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2909]
mysqld.exe!mysql_parse()[sql_parse.cc:5626]
mysqld.exe!dispatch_command()[sql_parse.cc:1039]
mysqld.exe!do_command()[sql_parse.cc:773]
mysqld.exe!do_handle_one_connection()[sql_connect.cc:814]
mysqld.exe!handle_one_connection()[sql_connect.cc:735]
mysqld.exe!pthread_start()[my_winthread.c:63]
mysqld.exe!_callthreadstartex()[threadex.c:348]
mysqld.exe!_threadstartex()[threadex.c:326]

Version: '5.5.27'  socket: ''  port: 3307  MySQL Community Server (GPL)
InnoDB: Assertion failure in thread 7056 in file ha_innodb.cc line 1477
InnoDB: Failing assertion: current <= max_value
...

Version: '5.5.28'  socket: ''  port: 3307  MySQL Community Server (GPL)
InnoDB: Assertion failure in thread 4344 in file ha_innodb.cc line 1477
InnoDB: Failing assertion: current <= max_value

....

I couldn't repeat on 5.5.29 or newer.  Looking at the change logs, I see:
https://bugs.mysql.com/bug.php?id=20964
https://bugs.mysql.com/bug.php?id=55071

So maybe they fixed it!

Thanks very much for checking. This is one of the last 5.5 boxes that's still running and the version is quite old. The cause of filling the auto-increment value to it's maximum was an exception due to a bug in the code which has now been resolved, so I don't think this is worth following up any further. I just wanted to make a record of it.

I'll see if I can get this server and its slaves upgraded to 5.6 and trust with that the problem should go away. If not I can open a new ticket and reference this one again.