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
Triage: Needs Triage: D2 (Serious)

[27 Sep 2011 18:38] Sean Jenkins
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.
[27 Sep 2011 18:54] Peter Laursen
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)
[27 Sep 2011 18:59] Sean Jenkins
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.
[27 Sep 2011 19:07] Peter Laursen
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
[27 Sep 2011 19:09] Peter Laursen
"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.
[27 Sep 2011 19:12] Sean Jenkins
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 :)
[28 Sep 2011 3:54] Valeriy Kravchuk
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'
[21 Jun 2012 5:41] Sean Jenkins
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...
[14 Nov 2016 16:29] Soumya K
5.6.25 and still same issue!
[4 Oct 2019 8:30] Bastien Boussouf
For those landing on this bug like I just did, this might have been fixed in 5.7.4 as mentioned in #68015.