MySQL Bugs: #62556: mysql_upgrade fails to handle multiple spaces and special symbols

Bug #62556	mysql_upgrade fails to handle multiple spaces and special symbols
Submitted:	27 Sep 2011 18:38	Modified:	21 Jun 2012 5:41
Reporter:	Sean Jenkins	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S1 (Critical)
Version:	5.1.58, 5.5.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql_upgrade fails to handle all cases of special characters/symbols and multiple spaces and will trigger a 1064 error.

How to repeat:
Use the following code to create a table that will trigger the failure:

USE test;
DROP TABLE IF EXISTS `http://www.mysql.com`;
CREATE TABLE `http://www.mysql.com` (
  `id` int NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Now, run mysql_upgrade.  You may have to run mysql_upgrade --force if you're already updated, and you will get the following error:

Got error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`com`  FOR UPGRADE' at line 1 when executing 'CHECK TABLE ...  FOR UPGRADE'

Haven't tested this on MySQL 5.5 yet, but I'm certain the problem exists there too.

Suggested fix:
Properly escape and handle the table name for the mysql_upgrade.

It is more complicated!

If table is named either 'http://www.mysql.com' or 'http:<SPACE><SPACE>www.mysql.com' it fails.

If table is named just 'http://mysql.com' or 'http:<APACXE><SPACE>mysql.com' it does not.  And 'http:$$www.mysql.com' does not either. 'http:##www.mysql.com' does fail.

Peter
(not a MySQL person)

Yeah, I can simulate those same items.  I haven't tried all the combinations obviously, but when I've ran into cases such as this, I'm forced to dump/drop the database, redo the whole mysql_upgrade process, and then recreate/reimport the database.

This is enough to reproduce the error:

USE test;
DROP TABLE IF EXISTS `a.b.c`;
CREATE TABLE `a.b.c` (
  `id` int NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- or replace table name with `a..b` - it also fails.

-- and now run "mysql_upgrade --force"
FATAL ERROR: Upgrade failed

.. so two 'dots' in a tablename is enough it seems. I also tried `a>>b` - and it is no problem. So multiple 'dots' seems to be the issue. But I am not surprised if there are more 'gotchas' to be found here ;-)

This is more severe than 'S3' IMHO.  Two dots can easily occur in an identifier.

-- Peter

"I'm forced to dump/drop the database, redo the whole mysql_upgrade process .. "

Well you could RENAME TABLE, run mysql_upgrade (and rename back if you want).  But database will need to be offline for as long of course.

Just tested my original example on 5.5.16, and got the exact same issue as on 5.1.58:

mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`com`  FOR UPGRADE' at line 1 when executing 'CHECK TABLE ...  FOR UPGRADE'

Also, although I could rename the table, because this is in a shared-hosting environment, I'd rather completely knock the users database offline, than rename their tables and trigger who knows what other problems for them.  Easier to just have a straight out failure when trying to connect to the database then letting some queries go through, and others not.  I'll just go the "safer" route :)

Thank you for the bug report. Verified using Peter's `a.b.c` test case with recent 5.5.17 on Mac OS X also:

Running 'mysqlcheck with default connection arguments
bin/mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`c`  FOR UPGRADE' at line 1 when executing 'CHECK TABLE ...  FOR UPGRADE'

9 months and still no fix? This is still an issue with the latest versions of MySQL and the work arounds are getting old.  Surely this can be easily fixed...

5.6.25 and still same issue!

For those landing on this bug like I just did, this might have been fixed in 5.7.4 as mentioned in #68015.