MySQL Bugs: #15855: Upgrade + old privilege tables yields information

Bug #15855	Upgrade + old privilege tables yields information_schema timeout
Submitted:	19 Dec 2005 10:43	Modified:	8 Jun 2006 17:50
Reporter:	Björn Wiberg	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	IBM AIX 5.3 ML2
Assigned to:		CPU Architecture:	Any

Description:
After upgrading to 5.0.16, but not having run the mysql_fix_privilege_tables yet, certain queries to the information_schema database seems to yield a timeout (set to 5 minutes in our case):

  SELECT COUNT( * ) AS num FROM `information_schema`.`ROUTINES`

(Nothing happens...)

In this case, we get (from phpMyAdmin in our case):

  #2013 - Lost connection to MySQL server during query

...because of MySQL "hanging" in its attempts to retrieve the information, and the timeout kicking in.

The MySQL server works just fine meanwhile and afterwards, serving other connections, but the current connection "hangs" and then times out.

Just wanted to point this out.

How to repeat:
Upgrade from 4.0.16 to 5.0.16, but don't run the mysql_fix_privilege_tables script just yet.

Then issue the query:

SELECT COUNT( * ) AS num FROM `information_schema`.`ROUTINES`

MySQL will give no response to this query.

Suggested fix:
Always run the mysql_fix_privilege_tables after upgrading.
But, perhaps MySQL should complain instead of "hanging"?

Thank you for a problem report. As the need to run this script is clearly described in the manual, I think, we can call this a feature request (yes, it will be useful for MySQL server to check and complain in such cases). Do you agree with this severity of the report?

Hello Valeriy!

Thank you for your reply!

Yes, a feature request classification would be fine with me. As long as the server complains things would be OK (as the situation of missing tables indicate an error but shouldn't "hang" the server -- I wonder what it is actually doing while it appears to be doing nothing?).

Best regards,
Björn

After some internal discussion it was decided that hang or crash is unacceptable behaviour in any case, so this bug report is reopened now.

I've tried to repeat on Linux with latest 5.0-BK, and server complained:

[openxs@Fedora 5.0]$ bin/mysqld_safe &
[1] 3262
[openxs@Fedora 5.0]$ Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) as num from INFORMATION_SCHEMA.routines;
ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist

So, please, next time when you encounter "hang" in a similar situation, send the results of SHOW PROCESSLIST command - they will show what's going on. You may also check your error log for some strange messages for the appropriate period.

Hello Valeriy!

We have since upgraded all our privilege tables, and after doing so, the problem went away.

Because of this, we currently do not have any installation to try this on. If we manage to do so with a separate instance, I will let you know!

Best regards,
Björn

OK. I'll put this report into "Need feedback" state. Reopen it if you'll be able to repeat the problem.

I run a test system with MySQL 5.0.18 and forgot to run the mysql_fix_priveledge_tables script, and am now experiencing the same issue. Since this is a testsystem I will wait with running the mysql_fix_priveledge_tables script and try to provide the information you need.

When I run the suggested "select count(*) as num from INFORMATION_SCHEMA.routines;" query, it just hangs and doesn't seem to timeout either (though I didn't configure any, if the default is to have none).

While it is hanging, "SHOW PROCESSLIST;" outputs: 

mysql> SHOW PROCESSLIST;
+----+------+-----------+--------------------+---------+------+-------+------------------+
| Id | User | Host      | db                 | Command | Time | State | Info             |
+----+------+-----------+--------------------+---------+------+-------+------------------+
| 13 | root | localhost | NULL               | Sleep   | 1031 |       | NULL             |
| 14 | root | localhost | information_schema | Sleep   | 1031 |       | NULL             |
| 16 | root | localhost | test               | Sleep   |  645 |       | NULL             |
| 17 | root | localhost | test               | Sleep   |  298 |       | NULL             |
| 18 | root | localhost | test               | Query   |    0 | NULL  | SHOW PROCESSLIST |
+----+------+-----------+--------------------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

The MySQL error log doesn't seem to have anything in it relating ot this either (just startup/shutdown messages). Unfortunately I am a bit of a MySQL (and databases in general) novice and do now know where to get more information.

It should be noted that after I installed MySQL I noticed the priveledges were acting odd (obviously because I didn't run the script) in phpMyAdmin, and sortoff fixed it by deleting /adding records with the cli mysql client. I don't recall exactly what SQL queries I made though.

Please let me know if/what further information you need.

Hans,

Do you have this hang also on AIX 5.3 or on some other OS?

I suppose I should've mentioned this in the original comment, I am experiencing this on FreeBSD 6.0 (with latest security updates).

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I was not able to get hang with the query. 

Hans,

I your case I also see no query hanging in the SHOW PROCESSLIST results.

Any ideas on how exactly to repeat that hang are welcomed. Is binary migration from 4.0.16 to 5.0.x on AIX 5.3 (we have no this exact OS to test, sorry), without running any upgrade scripts, the only way?

Hello Valeriy!

I just noticed an error in my original problem report. We upgraded from 4.1.16 to 5.0.16, not 4.0.16. Sorry about that!

Also, when it comes to hangs etc., the pthread libraries on AIX 5.3 on a maintenance levell <= ML2 have shown to cause hangs on other occasions. We're now running TL4 (~ ML4), and everything seems to be working fine now, so maybe this problem won't manifest itself unless you have old enough pthread libraries...

Although the FreeBSD report is interesting -- maybe old thread libs there, too?

Best regards,
Björn

I'll try to repeat with 4.1.18 to 5.0.20 binary upgrade once again, on AIX or FreeBSD or Linux. In case I will not be able to get the same results I'll put this bug report in Can't repeat state.

I tried again with 5.0.23-BK installed over latest 4.1-BK (on Linux), and once again got:

openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.23

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) as num from INFORMATION_SCHEMA.routines;
ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist

No hangs. 

So, please, try with the latest version officially released, 5.0.22. As we still have no AIX 5.3 available, I can not verify on that platform anyway.

Hello Valeriy!

Unfortunately, I do not have an installation available for testing, as we have finished our migration to MySQL 5 and are running everything on servers in operation.

The hangs could possibly be related to the problems of hangs with earlier version of phtread libs on AIX, but we are now on the latest level and, since then, have not had any problems.

I would like to thank you for your help in any case!

Best regards,
Björn