"DROP DATABASE" may block indefinitely

[Chehai Wu]

Steps to Reproduce:

1, Install Percona-XtraDB-Cluster-5.6.15-25.5.759

2, mysql < dbs.sql (download it from Bug Attachments)

3, Add "table_definition_cache = 400" and "innodb_open_files = 400" to my.cnf.

4, restart PXC.

5, mysql -e "DELETE FROM db444.t; DROP DATABASE db444"

6, If the command in Step 5 blocks, the bug is reproduced. Otherwise, go back to Step 5 and try a different database (db1, db2, ... db999) until the bug is reproduced.

Possible Explanation:

In storage/innobase/row/row0upd.cc:wsrep_row_upd_check_foreign_constraints, when foreign->referenced_table is NULL, dict_table_open_on_name is called to increase foreign->referenced_table's reference count. However, dict_table_close is never called to decrease the reference count.

This makes some table's reference count non-zero forever. As a result, when MySQL drops a database, it could enter into an infinite loop (see storage/innobase/row/row0mysql.cc:row_drop_database_for_mysql). If this happens in a slave SQL thread, the thread is blocked forever.

Possible Fix:

I fixed my copy of PXC by applying the patch attached here. I am not very familiar with MySQL source code, so I am not sure if my fix is the right way to fix the problem or not. But, I cannot reproduce the problem any more after the fix.

[Yan Zhang (yan.zhang)]

could you give some instructions to reproduce this bug?

[Chehai Wu (wuchehai)]

Simplified steps to reproduce this bug.

[Yan Zhang (yan.zhang)]

I can't reproduce this bug. I run several times but no block happens.

I review the code and to my intuition, we should close table in that function because we opens that table before in the same function. But I'm not sure either.

[Chehai Wu (wuchehai)]

Here is the detailed steps to reproduce. We can reproduce the problem very easily using VirtualBox and Vagrant.

1, Install VirtualBox and Vagrant (https://www.vagrantup.com/downloads.html)

2, vagrant init chef/centos-6.5

3, mkdir ~/vagrant_pxc && cd ~/vagrant_pxc

4, wget https://gist.githubusercontent.com/Chehai/2d0a8ede303b5dc1839e/raw/d7a3a296cf9f1cbe1f8a27ceb36d8041fc9f35a7/Vagrantfile

5, vagrant up

6, vagrant ssh

7, In the VM: mysql -uroot

8, In MySQL: DELETE FROM db444.t;

9, In MySQL: DROP DATABASE db444;

10, DROP DATABASE blocks.

[Yan Zhang (yan.zhang)]

fixed(for 5.6) in http://bazaar.launchpad.net/~codership/codership-mysql/5.6/revision/4087

5.5 seems fine with this problem. However seems there is some problem too. Because in 5.5
```
   if (foreign->referenced_table == NULL) {
    dict_table_get(foreign->referenced_table_name_lookup,
            FALSE, DICT_ERR_IGNORE_NONE);
   }
```
file is opened(foreign->referenced_table_name_lookup), but its handler is not referenced. It may brings resource leak.

But in 5.6, the corresponding code is
```
   if (foreign->referenced_table == NULL) {
    foreign->referenced_table =
     dict_table_open_on_name(
       foreign->referenced_table_name_lookup,
       FALSE, FALSE, DICT_ERR_IGNORE_NONE);
                        }
```

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1669