Bug #29334 pseudo-finished SHOW GLOBAL STATUS

• Submitted: 25 Jun 2007 11:04
• Modified: 25 Feb 2010 17:46
• Reporter: Victoria Reznichenko
• Status: Duplicate
• Category: MySQL Server: Locking
• Severity: S2 (Serious)
• Version: 5.1.20
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[25 Jun 2007 11:04] Victoria Reznichenko

Description:
If there are many queries running against tables with many partitions and then in another connection you run SHOW GLOBAL STATUS, it finishes, returns status variables, returns command prompt, but if you run next command in this connection it hangs.
If you open new connection and check SHOW PROCESSLIST you can see that 1st connection is still closing tables for SHOW GLOBAL STATUS query.

1st connection:

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              52 |
+-----------------+
1 row in set (0.10 sec)

mysql> show global status;

[snip]

| Threads_connected                 | 52       |
| Threads_created                   | 53       |
| Threads_running                   | 51       |
| Uptime                            | 103      |
+-----------------------------------+----------+
255 rows in set (0.90 sec)

mysql> show processlist;
*hang*

^^ as you can see I got result of SHOW GLOBAL STATUS

2nd connection

| 52 | root | localhost                | test | Query   |   20 | closing tables | show global status                                                                                   |
| 53 | root | localhost                | test | Query   |    0 | NULL           | show processlist                                                                                     |
+----+------+--------------------------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
52 rows in set (0.04 sec)

^^^ connection 52 is still closing tables and query is 'show global status'

How to repeat:
- test.sql contains table structures
- testcase.c - generates load for table from test.sql (INSERT commands)

1. create tables from test.sql
2. build testcase.c and run it (fix user/password/database/port etc settings before)
3. while testcase program is running open 1st connection to database and run:
select connection_id()
4. run show global status and wait while it returns the result
5. open another connection to the database
6. in the second connection run: show processlist and see status of your first connection

[10 Jul 2007 12:09] Georgi Kodinov

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Actually what happens is that "show process list" is done executing and is indeed waiting on the table open mutex. This mutex is taken (on a great speed) by the worker threads doing the INSERTs, and since there're so many partitions for each table it simply takes too much time to release the mutex (on my Dual-core Intel Core 2 Duo extreme with 2G ram it takes about 30 secs to get a mysql prompt after the tables are created). 
After a while (a good while) the 'SHOW PROCESS LIST' will eventually disappear from "show process list" and the INSERTs are all there is. 
During the tests the MySQL server was never locked and was running on 100% CPU (full steam).

[10 Jul 2007 12:31] MySQL Verification Team

I'm still sure it is a bug and thus I re-opened it.
MySQL shouldn't return control back to the client while it waits for mutex.
It waits for mutex in all other cases and I don't see any reson why it doesn't wait in this case.

Also this makes command what you run and SHOW PROCESSLIST output not consistent.
I.e. after running SHOW STATUS command you get control back, you think everything is ok (as nobody checks SHOW PROCESSLIST after every command), you run next any command and .. and it looks like 'hang'. You run show processlist in another connection and it still shows SHOW STATUS running.

This makes impossible/very difficult to track down what's going wrong on the server.

So this is a bug and it must be fixed. It has to wait for mutex.

[10 Jul 2007 18:15] Konstantin Osipov

Georgi,
it's a violation of locking protocol.
send_eof or send_ok should not be sent to the client until the server is done executing the query.

[11 Jul 2007 8:51] Georgi Kodinov

The server must not send ok to the client until it has committed the transaction at hand (even if may be a read-only transaction as SHOW GLOBAL STATUS most often is).
The effect observed in this bug will (largely) remain the same, because the server performs cleanup, and in particular, returns tables to the table cache, after sending successful operation status to the client, and the table cache is one point of contention in the test case. But this bug revealed the design problem above. So I'm moving it to "to be fixed later" because of the order of commit and send_ok(). According to Konstantin we don't have current plans to change that design.

[8 Feb 2010 22:40] Davi Arnaut

Closed as a duplicate of Bug#37521. It's essentially the same root issue, we should reconsider the design decision to send a status reply to the client before the query execution has truly finished.

[4 Aug 2010 7:51] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 7:55] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 7:56] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 7:57] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 7:58] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 7:59] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:00] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:06] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:13] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:14] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:15] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:16] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:17] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:21] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 9:00] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:20)

[4 Aug 2010 9:02] Bugs System

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (pib:20)