Bug#989 is in. Again.

The infamous Bug#989 was pushed into the trunk.

This marks an end of an important project that I personally and the runtime team spent eons of time on.

Why do I say we pushed it “again”? The fix, which is comprised by three worklogs, was initially pushed into 6.0 tree. Yeah, an intended basis for the next GA. But 6.0 received a bunch of alpha code, which, worse yet, was since then abandoned by its authors.

Just taking 200 changesets out, stabilizing them in a 5.1 based clone, and re-merging/integrating with the remaining stable features that were added to the trunk took 2 months. Thanks to brilliant 6.0 planning and the turbulent times…

But we didn’t just do that. Thanks to MySQL’s internal QA, and the time that the patch sat in a public tree, we were able to identify a lot of collateral problems, regressions, nice-to-haves, and get them solved.

I’m sure such a significant change will not affect everyone positively. But at the same time, I’m looking forward to what it enables us to do in the future: higher scalability, a more transparent architecture, flexibility to extend the system.

Today I’m cautiously proud to list all the tasks that have been completed:

WL#3726 “DDL locking for all metadata objects” WL#4284 “Transactional DDL locking” WL#4144 “Lock MERGE engine children”

Bug#989 “If DROP TABLE while there’s an active transaction, wrong binlog order” Bug#25144 “replication / binlog with view breaks” Bug#39675 rename tables on innodb tables with pending transactions causes slave data issue”

Bug#30977 “Concurrent statement using stored function and DROP FUNCTION breaks SBR” Bug#37346 “innodb does not detect deadlock between update and alter table” Bug#45225 “Locking: hang if drop table with no timeout” Bug#46224 “HANDLER statements within a transaction might lead to deadlocks” Bug#41804 “purge stored procedure cache causes mysterious hang for many minutes” Bug#26141 “mixing table types in trigger causes full table lock on innodb table” Bug#43685 “Lock table affects other non-related tables” Bug#22876 “Four-way deadlock” Bug#44613 “SELECT statement inside FUNCTION takes a shared lock” Bug#33948 “performance issue when using sysbench benchmark on a multiple-core system.”

These are bugs that our QA engineers found and we fixed before the push: Bug#40181 "Partitions: hang if create index Bug#43867 "ALTER TABLE on a partitioned table causes unnecessary deadlocks Bug#43272 "HANDLER SQL command does not work under LOCK TABLES" Bug#46272 "New MDL: unnecessary deadlock" Bug#46654 "False deadlock on concurrent DML/DDL with partitions, inconsistent behavior" Bug#48541 "Deadlock between LOCK_open and LOCK_mdl (was deadlock with LOCK_thread_count)" Bug#42862 "Crash on failed attempt to open a children of a merge table" Bug#46273 "New MDL: Bug#989 is not fully fixed in case of ALTER" Bug#50913 "Deadlock between open_and_lock_tables_derived and MDL" Bug#45949 "Assertion `!tables->table' in open_tables() on ALTER + INSERT DELAYED" Bug#47635 "assert in start_waiting_global_read_lock during CREATE VIEW" Bug#46452 "Crash in MDL, HANDLER OPEN + TRUNCATE TABLE" Bug#46495 "Crash in reload_acl_and_cache on SIGHUP" Bug#50821 "Deadlock between LOCK TABLES and ALTER TABLE" Bug#51136 "Crash in pthread_rwlock_rdlock on TEMPORARY + HANDLER + LOCK + SP" Bug#50907 "Assertion `hash_tables->table->next == __null' on HANDLER OPEN" Bug#50908 "Assertion `handler_tables_hash.records == 0' failed in enter_locked_tables_mode" Bug#51134 "Crash in MDL_lock::destroy on a concurrent DDL workload" Bug#50412 "Assertion `! is_set()' failed in Diagnostics_area::set_ok_status at PREPARE" Bug#42074 "concurrent optimize table and alter table = Assertion failed: thd->is_error()" Bug#47335 "assert in get_table_share" Bug#48246 "assert in close_thread_table" Bug#47313 "assert in check_key_in_view during CALL procedure" Bug#45035 "Altering table under LOCK TABLES results in "Error 1213 Deadlock found..." Bug#39897 "lock_multi fails in pushbuild: timeout waiting for processlist" Bug#48248 "assert in MDL_ticket::upgrade_shared_lock_to_exclusive" Bug#41425 "Assertion in Protocol::end_statement() (pushbuild2) (diagnostics_area)" Bug#42147 "Concurrent DML and LOCK TABLE ... READ for InnoDB table cause warnings in errlog" Bug#42546 "Backup: RESTORE fails, thinking it finds an existing table" Bug#46747 "Crash in MDL_ticket::upgrade_shared_lock_to_exclusive on TRIGGER + TEMP table" Bug#45781 "infinite hang/crash in "opening tables" after handler tries to open merge table" Bug#46673 "Deadlock between FLUSH TABLES WITH READ LOCK and DML" Bug#47107 "assert in notify_shared_lock on incorrect CREATE TABLE, HANDLER" Bug#38661 "all threads hang in "opening tables" or "waiting for table" and cpu is at 100%" Bug#50912 "Assertion `ticket->m_type >= mdl_request->type' failed on HANDLER + I_S" Bug#50786 "Assertion `thd->mdl_context.trans_sentinel() == __null' failed in open_ltable()" Bug#51093 "Crash (possibly stack overflow) in MDL_lock::find_deadlock" Bug#48210 "FLUSH TABLES WITH READ LOCK deadlocks against concurrent CREATE PROCEDURE" Bug#45066 "FLUSH TABLES WITH READ LOCK deadlocks against LOCK TABLE" Bug#45067 "Assertion `stmt_da->is_error()' in Delayed_insert::open_and_lock_table" Bug#46610 "MySQL 5.4.4: MyISAM MRG engine crash on auto-repair of child" Bug#47249 "assert in MDL_global_lock::is_lock_type_compatible" Bug#46374 "crash, INSERT INTO t1 uses function, function modifies t1" Bug#48724 "Deadlock between INSERT DELAYED and FLUSH TABLES" Bug#50998 "Deadlock in MDL code during test rqg_mdl_stability" Bug#47648 "main.merge fails sporadically" Bug#49988 "MDL deadlocks with mysql_create_db, reload_acl_and_cache" Bug#48940 "MDL deadlocks against mysql_rm_db" Bug#48538 "Assertion in thr_lock() on LOAD DATA CONCURRENT INFILE" Bug#44040 "MySQL allows creating a MERGE table upon VIEWs but crashes when using it" Bug#39674 "On shutdown mdl_destroy() called before plugin_shutdown()" Bug#46044 "MDL deadlock on LOCK TABLE + CREATE TABLE HIGH_PRIORITY FOR UPDATE".

Finally, some fixes are not yet in, but were “enabled” by the new design and have patches:

Bug#33669 “Transactional temporary tables do not work under –read-only” Bug#36171 “CREATE TEMPORARY TABLE and MERGE engine” Bug#42643: InnoDB does not support replication of TRUNCATE TABLE

Wooh.