Bug #68175 Database names containing an underscore showing up escaped/ cause SQL Error
Submitted: 25 Jan 2013 0:37 Modified: 2 Aug 2013 14:16
Reporter: Mike Reid Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Administration Severity:S3 (Non-critical)
Version:5.2.46 (Revision 10386) OS:Any
Assigned to: CPU Architecture:Any
Tags: Database name, grant, permissions, schema name, underscore
Triage: Needs Triage: D3 (Medium)

[25 Jan 2013 0:37] Mike Reid
Description:
You can no longer assign schema privileges using the provided database list if the database name contains an underscore.

The database name shows underscores "escaped" (e.g. "sample\_database\_name\_here")

Trying to add permissions to said database throws an SQL error:

Error executing 'GRANT SELECT ON sample\_table.* TO 'user'@'localhost''
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
SQL Error: 1064

How to repeat:
1. Create a database with an underscore in the name (e.g. sample_database)
2. Open MySQL Administrator
3. Go to Users and Privileges, choose Schema Privileges tab, select a user.
4. Click "Add Entry..." button
5. Under the Schema section, choose "Selected schema:" option
6. Select a database name containing an underscore (It will be escaped like "sample\_database") and press OK.
7. Select some Object Rights. (e.g. SELECT, INSERT, etc)
8. Click "Save Changes"
9. See SQL Error: 1064 prompt 

Suggested fix:
Correctly account for underscores / prevent escaping. (\_ should be: _)
[28 Jan 2013 6:53] Umesh Shastry
Hello Mike,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[28 Jan 2013 6:56] Umesh Shastry
Database names containing an underscore showing up escaped/ cause SQL Error

Attachment: 68175.png (image/png, text), 131.36 KiB.

[5 Feb 2013 16:31] Mike Reid
Updated impacted version to latest 5.2.46 release. Also, changed OS from "Mac OS X" to "Any" since provided confirmation / screenshot appears to be from Linux.
[19 Feb 2013 9:25] Stephen Edwards
This error also occurs on Windows 8 64bit platform.
[19 Feb 2013 9:43] Stephen Edwards
Just checked and this bug still exists in MySQL Workbench version 5.2.47 CE Revision 10398
[23 Feb 2013 18:55] Slava Dev
Temporary workaround (and how it should be fixed in Workbench):

Run SQL manually, using quotes around database name:

GRANT SELECT ON `sample\_table`.* TO 'user'@'localhost'

Also works with wildcards:

GRANT SELECT ON `sample\_%`.* TO 'user'@'localhost'
[24 Feb 2013 7:47] Umesh Shastry
http://bugs.mysql.com/bug.php?id=68475 marked as duplicate of this one.
[2 Mar 2013 18:08] Miguel Solorzano
http://bugs.mysql.com/bug.php?id=68540 marked as duplicate.
[11 Mar 2013 6:08] Umesh Shastry
http://bugs.mysql.com/bug.php?id=68627 marked as duplicate of this one.
[14 Mar 2013 17:40] Oliver Drummond Oliver
Same thing happening to me on both Windows and Mac versions
[24 Apr 2013 15:32] Miguel Solorzano
http://bugs.mysql.com/bug.php?id=69056 duplicate of this one.
[1 May 2013 6:04] Umesh Shastry
http://bugs.mysql.com/bug.php?id=69106 marked as duplicate of this one.
[15 May 2013 3:57] Sean Newton
This bug looks a lot like a bug in oracle workbench ---->

The driver calls used to display the list of tables and views in a specific schema expects a wildcard expression. To avoid listing the objects for USERX1 when displaying the objects for USER_1 the underscore must be escaped. The driver will create an expression similar to AND onwer LIKE 'USER_1' ESCAPE '\' (which would return tables for USERA1, USERB1 and so on, including of course USER_1).

The character that is used to escape the wildcards is reported by the driver. SQL Workbench/J sends e.g. the value USER\_1 if the driver reports that a backslash is used to escape wildcards.

However some older Oracle drivers report the wrong escape character, so the value sent to the database results in AND onwer LIKE 'USER\_1' ESCAPE '/'. The backslash in the expression is the character reported by the driver, whereas the forward slash in the expression is the character actually used by the driver.

To fix this problem, the escape character reported by the driver can be overridden by setting a property in workbench.settings:

workbench.db.oracle.searchstringescape=/

------------------

Is there a workaround similar to the above fix that we can manually do on windows to get it working in the interim??
[16 May 2013 8:58] Umesh Shastry
http://bugs.mysql.com/bug.php?id=69256 marked as duplicate of this one.
[5 Jun 2013 12:18] Umesh Shastry
Bug #69404 is marked as duplicate of this one.
[14 Jun 2013 5:59] Philip Olson
Fixed as of the upcoming MySQL Workbench 6.0.2 public beta release, and here's the changelog entry:

A database with a name that contained an underscore could not be assigned
schema privileges using the provided database list in the Server
Administrator, as the database name was not escaped properly.

Thank you for the bug report.
[3 Jul 2013 5:55] Dragon Chuang
This bug still exist in 6.0.2.10924
[14 Jul 2013 18:17] Miguel Solorzano
http://bugs.mysql.com/bug.php?id=69742 marked as duplicate with this one.
[19 Jul 2013 16:56] Miguel Solorzano
http://bugs.mysql.com/bug.php?id=69792 marked as duplicate of this one.
[2 Aug 2013 14:08] Mike Reid
Reopening this bug as it appears to have resurfaced in the new 6.0 BETA.
[2 Aug 2013 14:16] Mike Reid
False alarm (closing) -- while the entry stored in mysql.db still contains escaped underscores, and so does the list of databases in Workbench, it appears the error is gone and permissions still work.
[2 Aug 2013 15:32] Miguel Solorzano
http://bugs.mysql.com/bug.php?id=69909 duplicate of this one.
[1 Dec 2014 15:59] Douglas Hipenbecker
This bug still exists in Windows Workbench 6.2.3.12312 against a Redhat Oracle Linux MySQL database version 5.5.40