Bug #13908 MySQL locks and stop responding all queries after "SHOW TABLE STATUS FROM" is e
Submitted: 10 Oct 2005 20:10 Modified: 12 Oct 2005 2:41
Reporter: Guilherme Magalhaes Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.14 OS:Windows (Win2k3 SP1)
Assigned to: MySQL Verification Team CPU Architecture:Any

[10 Oct 2005 20:10] Guilherme Magalhaes 
Description:
MySQL locks and stop responding all queries after "SHOW TABLE STATUS FROM" is executed, resulting in max_user_connection errors after some time.

Basically, when a user browse MySQL database using phpMyAdmin, it executes an "SHOW TABLE STATUS FROM" and for reasons that I don't know the entire server locks with "Opening tables". It still accept connetions, but don't answer queries from any user (even from users of other dbs not related to the db that lock the server). 
As a result, more and more users get connected and stop waiting forever until max_users is reached.

I've seen many bug reports about similar behavior but no respose from MySQL team.

How I can find help on this?

How to repeat:
not sure, just executing SHOW TABLE STATUS FROM table two times locks the entire server.
[11 Oct 2005 1:31] MySQL Verification Team 
Could you please show the output of show status and show variables
when that happens.

Thanks in advance.
[11 Oct 2005 18:28] Guilherme Magalhaes 
Something new happened today: the server crashed with this statement 

"show create table `db`.`del_topics"

I'm sending the required dump when it was locked.
[11 Oct 2005 18:42] MySQL Verification Team 
How many tables do you have in the DB you are issuing:
SHOW TABLE STATUS FROM ?

Thanks
[11 Oct 2005 19:17] Guilherme Magalhaes 
It depends. I got the server locked with 10 tables DBs, and other with 270 tables DBs.

Please don't tell me that SHOW TABLE STATUS it's not thread safe.... =]

Just ran SHOW TABLE STATUS on 270 table DB now witout any problem, the respose what instant. When I tried it again the whole server locks. The same behavior on 10 table DBs.

Will send the logs.
[11 Oct 2005 19:24] Guilherme Magalhaes 
Already tried to monitor the File IO access with FileMon, but nothing strange is shown.

No indexing server or anything that touches the files are running also.
[12 Oct 2005 2:32] MySQL Verification Team 
Using PHPAdmin

Attachment: bug13908.PNG (image/x-png, text), 75.20 KiB.
[12 Oct 2005 2:41] MySQL Verification Team 
Olá Guilherme,

I created 5000 small tables under the test database with the below C
application:

#include <my_global.h>
#include <m_string.h>
#include "mysql.h"

#define DB_HOST			"localhost"
#define DB_USER			"root"
#define DB_PASSW		""
#define DB_NAME			"test"
#define DB_PORT			0
#define DB_UNIX_SOCKET	NULL

void main( void )
{
  MYSQL mysql;
  int x;
	char my_sub[255], my_query[255];
 
  mysql_init(&mysql);
  
   
  if (!mysql_real_connect(&mysql,DB_HOST,DB_USER,DB_PASSW,DB_NAME,
						   DB_PORT,DB_UNIX_SOCKET,0))
  {
    printf("Error: %s\n",mysql_error(&mysql));
    return;
  }
  else
	printf("Connected to the server: %s\n",mysql_get_server_info(&mysql));
  
	for (x = 1; x <= 5000; x++)
	{
	  int10_to_str((int) x,strmov(my_sub,
          "Create Table tb"), 10);

	  strxnmov(my_query,sizeof(my_query)-1,my_sub,
		 " (id int not null auto_increment primary key, name char(20))",NullS);
	 

	 	if (mysql_query( &mysql, my_query) )
		{
      printf("Error (query): %s\n", mysql_error( &mysql ));
      mysql_close( &mysql );
     return;
		}
	}
  
  mysql_close(&mysql);
}

Compiled PHP 5.0.5 against the 5.0.13 libmysql.lib:

C:\Arquivos de programas\Microsoft Visual Studio .NET 2003\Vc7\PlatformSDK\Include\WS2tcpip.h(647) : warning C4142: beni
gn redefinition of type
        rc /fo Release_TS\php.exe.res /D WANT_LOGO  /d FILE_DESCRIPTION="\"CLI\"" /d FILE_NAME="\"php.exe\"" /d PRODUCT_
NAME="\"PHP php.exe\"" /d THANKS_GUYS="\"Thanks to Edin Kadribasic, Marcus Boerger\"" win32\build\template.rc
SAPI sapi\cli build complete

C:\work\php-5.0.5>

Installed PhPMyAdmin 2.6.4 and queried the server several times with
show table status from test without to get the behavior reported,
I attached a file which shows the PhPMyAdmin querying the server.

If you can provide the dump of your data schema then I can try
to repeat the test. Let me know if you are able for.

If yes you can upload it at:

ftp://ftp.mysql.com:/pub/mysql/upload

with a name like bug13908.zip

Thanks in advance.
[26 Oct 2005 11:24] Guilherme Magalhaes 
Thanks for the support Miguel. I'm trying to repeat the buggy behavior on other server with the same MySQL version, config and schema. Will let you know how things goes.