Bug #60114 INSERT DELAYED can call ha_release_auto_increment without having a lock.
Submitted: 11 Feb 2011 12:39 Modified: 15 Oct 2012 14:05
Reporter: Mattias Jonsson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:mysql-trunk OS:Any
Assigned to: CPU Architecture:Any

[11 Feb 2011 12:39] Mattias Jonsson
Description:
As I understand, handler::ha_release_auto_increment should only be called when having a lock (m_lock_type != F_UNLCK), if it is not a temporary table.

If adding 'DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE || m_lock_type != F_UNLCK)' in handler::ha_release_auto_increment() then main.delayed asserts in pb2 'linux x86_64 gcov' test with --ps-protocol.

main.delayed [ fail ]
        Test ended at 2011-02-10 20:46:59

CURRENT_TEST: main.delayed
mysqltest: At line 276: query 'INSERT DELAYED INTO t1 VALUES(mod(1,0),"test3")' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 1365...

mysqld: /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/handler.cc:2839: void handler::ha_release_auto_increment(): Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed.

(here is F_UNLCK = 2)

Stacktrace:

'query' thread:
#3 <signal handler called> #4 0x00000036e2230155 in raise () from /lib64/libc.so.6 #5 0x00000036e2231bf0 in abort () from /lib64/libc.so.6 #6 0x00000036e22295d6 in __assert_fail () from /lib64/libc.so.6 #7 0x0000000000898b68 in handler::ha_release_auto_increment (this=0xb17acb0)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/handler.cc:2838
#8 0x0000000000625a23 in mysql_insert (thd=0xb25a2b0, table_list=0xb3f7640,
    fields=@0xb3f6f60, values_list=@0xb3f6fa8, update_fields=@0xb3f6f90, 
    update_values=@0xb3f6f78, duplic=DUP_ERROR, ignore=false)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/sql_insert.cc:1108
#9 0x0000000000646d0a in mysql_execute_command (thd=0xb25a2b0)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/sql_parse.cc:2845
#10 0x0000000000673b6f in Prepared_statement::execute (this=0xb2034e0,
    expanded_query=0x4170f010, open_cursor=false)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/sql_prepare.cc:3766
#11 0x00000000006740db in Prepared_statement::execute_loop (this=0xb2034e0,
    expanded_query=0x4170f010, open_cursor=false, packet=0xb1c64da "", 
    packet_end=0xb1c64da "")
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/sql_prepare.cc:3419
#12 0x0000000000674928 in mysqld_stmt_execute (thd=0xb25a2b0,
    packet_arg=0xb1c64d1 "S", packet_length=9)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/sql_prepare.cc:2561
#13 0x00000000006518e9 in dispatch_command (command=COM_STMT_EXECUTE,
    thd=0xb25a2b0, packet=0xb1c64d1 "S", packet_length=9)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/sql_parse.cc:1030
#14 0x00000000006540ed in do_command (thd=0xb25a2b0)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/sql_parse.cc:815
#15 0x00000000007aa4a6 in do_handle_one_connection (thd_arg=0xb25a2b0)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/sql_connect.cc:748
#16 0x00000000007aa6cf in handle_one_connection (arg=0xb25a2b0)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/sql_connect.cc:684
#17 0x0000000000be98c4 in pfs_spawn_thread (arg=0xb212d70)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/storage/perfschema/pfs.cc:1359
#18 0x00000036e2e062f7 in start_thread () from /lib64/libpthread.so.0 #19 0x00000036e22d1b6d in clone () from /lib64/libc.so.6 

delayed thread:
Thread 2 (process 6675):
#0 0x00000036e2e0a687 in pthread_cond_timedwait@@GLIBC_2.3.2 ()
   from /lib64/libpthread.so.0
#1 0x0000000000bb07dc in safe_cond_timedwait (cond=0xb3ebae0, mp=0xb3eba70,
    abstime=0x404f3080, 
    file=0x113a798 "/export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/include/mysql/psi/mysql_thread.h", line=1009)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/mysys/thr_mutex.c:278
#2 0x0000000000618b2b in inline_mysql_cond_timedwait (that=0xb3ebae0,
    mutex=0xb3eba70, abstime=0x404f3080, 
    src_file=0x113a250 "/export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/sql_insert.cc", src_line=2728)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/include/mysql/psi/mysql_thread.h:1009
#3 0x000000000061e416 in handle_delayed_insert (arg=0xb3e8930)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/sql/sql_insert.cc:2728
#4 0x0000000000be98c4 in pfs_spawn_thread (arg=0xb189630)
    at /export/home2/pb2/build/sb_1-None-1297355812.67/mysql-trunk-wl5217-gcov/storage/perfschema/pfs.cc:1359
#5 0x00000036e2e062f7 in start_thread () from /lib64/libpthread.so.0 #6 0x00000036e22d1b6d in clone () from /lib64/libc.so.6 

How to repeat:
add 'DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE || m_lock_type != F_UNLCK)' in handler::ha_release_auto_increment()

run in pb2 'Linux x86_64 gcov': mysql-test-run.pl  --force --timer --parallel=auto --comment=ps --skip-ndbcluster --ps-protocol --experimental=collections/default.experimental

I have not succeeded in repeating it more than once anywhere else, but in pb2 it have failed for 5 of 6 pushes.
[26 Feb 2011 1:03] Sveta Smirnova
Thank you for the report.

Verified as described. To repeat I had to use option --repeat=100
[15 Oct 2012 14:05] Erlend Dahl
Fixed in 5.6.6