MySQL database / username connection

[shacker23]

Hi -

If I run these .sql queries in this order:

CREATE DATABASE `username1_dbname`;
GRANT ALL ON `username1_dbname`.* TO 'username1_foo'@'localhost' IDENTIFIED BY 'lskdfj';
FLUSH PRIVILEGES;

It works. The user can now use this database, dbname, and db pass however they like.

But in cPanel's MySQL panel, the db account name and the database both appear, but don't appear connected - it appears as if that user has no grants on that db. I would assume cPanel would show the grant as already existing.

How can I run a series of SQL statement as above that not only works, but also displays properly in cPanel? What is cPanel checking for when displaying grants between db account names and databases?

Thanks,
Scot

[shacker23]

OK - After much trial and error, I've solve this one. You need to escape the underscore in the GRANT statement line. You do NOT need to escape the underscore in the CREATE DATABASE line. Go figure. so this code gets it right (the change is in red below):

CREATE DATABASE `username1_dbname`;
GRANT ALL ON `username1\_dbname`.* TO 'username1_foo'@'localhost' IDENTIFIED BY 'lskdfj';
FLUSH PRIVILEGES;

I have no idea why this is necessary. Again, the original version works just fine, it's just that the graphical cPanel database admin user page does not show the connection between the username and the db name unless you do the escaping as above. Quirky.