Troubleshooting MySQL Crashes related to Metadata Locking

I just wrote an article about “Troubleshooting ‘Waiting for table metadata lock’ Errors for both MyISAM and InnoDB Tables” and then ran into a new, different metadata locking issue right after I posted it, and so I thought I’d share that too, just in case anyone ever encounters a similar situation.

In this case, mysqld kept crashing on restart, reporting “out of memory” errors:

/opt/app/mysql/product/mysql/bin//mysqld: Out of memory (Needed 840 bytes)
stack_bottom = 7fb4ebaeae58 thread_stack 0x40000
mysqld: /mysql/mysys/my_new.cc:52: int __cxa_pure_virtual():
  Assertion `! "Aborted: pure virtual method called."' failed.
mysqld: /mysql/mysys/my_new.cc:52: int __cxa_pure_virtual():
  Assertion `! "Aborted: pure virtual method called."' failed.
Fatal signal 6 while backtracing

There is a recent bug fixed (in 5.5.29) that mentions the same assert above, but that is not the same problem here, as that bug always reports “InnoDB: Failing assertion: page_get_n_recs(page) > 1″.

Looking further into these repeated crashes, I noticed the following some of the stack traces always showed this:

stack_bottom = 7f2cd227ce58 thread_stack 0x40000
/opt/app/mysql/product/mysql/bin//mysqld(my_print_stacktrace+0x35)[0x79e2d5]
/opt/app/mysql/product/mysql/bin//mysqld(handle_fatal_signal+0x403)[0x66e233]
/lib64/libpthread.so.0(+0xf7c0)[0x7f46041577c0]
/../mysqld(_ZN10MDL_ticket6createEP11MDL_context13enum_mdl_type+0x26)[0x61a3e6]
/../mysqld(_ZN11MDL_context21try_acquire_lock_implEP11MDL_requestPP10MDL_ticket+0xa2)[0x61c4c2]
/../mysqld(_ZN11MDL_context12acquire_lockEP11MDL_requestm+0x63)[0x61cb53]
/../mysqld[0x539d76]
/../mysqld(_Z10open_tableP3THDP10TABLE_LISTP11st_mem_rootP18Open_table_context+0x1b1)[0x541591]
/../mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x42b)[0x542d2b]
/../mysqld(_Z30open_normal_and_derived_tablesP3THDP10TABLE_LISTj+0x48)[0x543678]
/../mysqld(_ZN18Prepared_statement7prepareEPKcj+0xc5e)[0x58a99e]
/../mysqld(_Z19mysqld_stmt_prepareP3THDPKcj+0xae)[0x58bbee]
/../mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xc21)[0x57c4e1]
/../mysqld(_Z24do_handle_one_connectionP3THD+0x167)[0x613447]
/../mysqld(handle_one_connection+0x54)[0x6134b4]
/../mysqld(pfs_spawn_thread+0x5b)[0x7f6abb]
/lib64/libpthread.so.0(+0x77b6)[0x7f460414f7b6]
/lib64/libc.so.6(clone+0x6d)[0x7f46033e7c5d]

At first glance, it’s kind of hard to read. However, if you focus in, you can extract some useful information.

Looking just before the crash (handle_fatal_signal), we see these 3 lines:

/../mysqld(_ZN10MDL_ticket6createEP11MDL_context13enum_mdl_type+0x26)[0x61a3e6]
/../mysqld(_ZN11MDL_context21try_acquire_lock_implEP11MDL_requestPP10MDL_ticket+0xa2)[0x61c4c2]
/../mysqld(_ZN11MDL_context12acquire_lockEP11MDL_requestm+0x63)[0x61cb53]

The keys parts of that, in reverse order are:

acquire_lock
try_acquire_lock_impl
enum_mdl_type

These 2 functions and variable, respectively, are only found in mdl.cc (and mdl.h) – the metadata lock files.

And after examining the MySQL source code, this stack trace tells us that there is an issue with the metadata locks. Specifically, it is trying to acquire a metadata lock (try_acquire_lock_impl), but it is failing (enum_mdl_type is set when try_acquire_lock_impl() fails, an assert is thrown, and mysqld crashes).

I’ve searched the bugs database for anything similar to this, but didn’t find any existing ones specific to try_acquire_lock_impl. That’s not to say there isn’t some yet-to-be-detected-bug here, but in this case, the machine was testing a custom application, so I strongly suspected the custom app causing the contention, which was the case.

So if you do happen to encounter this crash, then hopefully this will help you track down the problem.

Hope this helps, and happy troubleshooting.