Bug #30654 mysqlcheck fails during upgrade of tables whose names include backticks or blank
Submitted: 27 Aug 2007 23:30 Modified: 20 Dec 2007 1:27
Reporter: Omer Barnir (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.0 5.1 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: backticks, identifiers, mysql_upgrade, mysqlcheck

[27 Aug 2007 23:30] Omer Barnir
Description:
the mysqlcheck utility fails if the system upgraded includes tables with names that require delimiters when defined (i.e table names that include quotation marks '`' blanks etc. Examples:
  - ta1`b - created by:  create table `ta1``b`
  - v 1   - created by: create table `v 1`
  etc.

Because mysqlcheck fails, the remainder of the tables are not checked and the mysql_fix_privilege_tables script is never run. 

How to repeat:
1) Create a 5.0 system and add a table named "ta1`b" using:
   CREATE TABLE `ta1``b` (I INT);
2) Upgrade the system to 5.1 
3) Run the mysql_upgrade command:
      mysql_upgrade --datadir=<path_to_data> --basedir=<path to binaries> --force
                    --socket=>socket_file> --user=root

>> mysqlcheck will report the following error:
Got error: 1064: You have an error in your SQL sybtax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b\ FOR UPGRADE at lie 1 wen executing 'CHECK TABLE... FOR UPGRADE'
FATAL ERROR: Upgrade failed

Suggested fix:
Add ` ` (back-quotes) around all table names when executing the "CHECK TABLE... FOR UPGRADE" command in mysqlcheck
[27 Aug 2007 23:37] Omer Barnir
Workaround:

Before running mysql_upgrade, rename all tables that required a delimiter when defined, to names that do not require it, for example:
  RENAME TABLE `ta1``b` TO ta1_b;

Run the mysql_upgrade program

Rename the tables back to their original names:
  RENAME TABLE ta1_b TO `ta1``b`;
[29 Aug 2007 8:48] Jon Stephens
NOTE: A delimiter is a *separator* e.g. the usual delimiter for words in a sentence is a space character.

The issue here is with identifiers including characters (e.g. quotation marks) that cannot be used literally because they're normally interpreted as control sequences (e.g. the beginning or end of a string value) - i.e. characters which must be *escaped* in order to be treated *as characters* and not as something else.

I tested with 5.2-bk and encountered this issue only with table names containing the backtick character. Please verify that table identifiers containing other characters requiring escapes are affected, because they don't appear to be in 5.2.

Thanks!
[29 Aug 2007 13:37] Omer Barnir
This also affect table names that have a space (blank) in their name, i.e. if a table  was created as CREATE TABLE `tab with space` (i INT);
it will show in SHOW TABLES as 
+----------------+
| Tables_in_test |
+----------------+
| tab with space |
+----------------+
and will need to be renamed as part of the workaround
[13 Sep 2007 0:32] Paul DuBois
Issue has been noted under "Server Changes" at:

http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html

Setting report to Verified status.
[16 Oct 2007 6:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35613

ChangeSet@1.2536, 2007-10-16 11:44:31+05:00, ramil@mysql.com +3 -0
  Fix for bug #30654: mysqlcheck fails during upgrade of tables whose 
  names include backticks or blank
  
  Problem: mysqlcheck doesn't escape backtick characters in the table names.
  
  Fix: escape them.
[30 Oct 2007 8:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36617

ChangeSet@1.2536, 2007-10-30 12:51:57+04:00, ramil@mysql.com +3 -0
  Fix for bug #30654: mysqlcheck fails during upgrade of tables whose 
  names include backticks or blank
  
  Problem: mysqlcheck doesn't escape backtick characters in the table names.
  
  Fix: escape them.
[7 Dec 2007 23:07] Bugs System
Pushed into 6.0.5-alpha
[7 Dec 2007 23:08] Bugs System
Pushed into 5.1.23-rc
[7 Dec 2007 23:10] Bugs System
Pushed into 5.0.54
[20 Dec 2007 1:27] Paul DuBois
Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

mysqlcheck did not properly escape backticks in table names when
processing tables. 

Also updated the upgrading-to-5.1 section.