Page 1 of 1

Mysql underscore in user name

PostPosted: 16. March 2011 00:03
by nesredep
I use xampp for php/mysql development, but I have run into a real PITA problem.

My hosting website (using CPanel) forces you to prefix the master account name on both database names, and user names.

So I have databases named like this:

accountname_treedb, accountname_citydb, and so on.

MORE IMPORTANTLY, when I set up the name of a user who is going to get access to these databases, it ALSO prefixes the same thing, like:

accountname_user1, accountname_user2.

Ok, that's fine, I can live with it. But when I'm developing on my local machine, I want to use the same naming as is on the production machine.

The naming of the database is fine, but when I name a user with an underscore, xampp chokes. It APPEARS to allow me to name a user like this:

accountname_user1

....BUT, it invariably gives me "user denied access, etc".

If I create a user on xampp named simply "user1", and I change my php code from accountname_user1 to just user1, everything works. No other changes.

Is there someplace, some setting, where I can tell the xampp package to cut me some slack on usernames???

I know this is trivial, but if I do my development using different names, I just know that two or three times it's going to play "gotcha" with me, and I'll be debugging a newly uploaded software a few times for no reason. I'd be very grateful if somebody has the answer.

John

Re: Mysql underscore in user name

PostPosted: 17. March 2011 18:39
by glitzi85
You cannot create users "on XAMPP", because XAMPP is not a software. How did you create that user? With the MySQL command line tool or with phpMyAdmin?

glitzi

Re: Mysql underscore in user name

PostPosted: 17. March 2011 19:23
by nesredep
glitzi85 wrote:You cannot create users "on XAMPP", because XAMPP is not a software. How did you create that user? With the MySQL command line tool or with phpMyAdmin?

glitzi


Ok, pardon my carelessness. I meant when I create users on the system that uses XAMPP". To answer your question: I used phpMyAdmin.

And now that you mention it, on the remote machine using CPanel, you can't set priviledges and create users using phpMyAdmin. You have to go to the MySQL page and use their form (which probably uses MySQL cli under the hood).

Thanks for that insight. So is this a phpMyAdmin bug? If I create the user using command line, will it subsequently allow that user with the name containing an underscore to actually log in? If nobody knows the answer, I'll try it tonight, but I just thought I'd ask.

John

Re: Mysql underscore in user name

PostPosted: 18. March 2011 03:18
by nesredep
How about that!

I used select * from mysql.user to confirm that the user name was in the user table.

On my local machine, I did (with command line):

DROP USER xxxxxxx_yyy@localhost

then select * from mysql.user, to make sure it was really gone, then

CREATE USER xxxxxxx_yyy@localhost identified by 'password'

(the exact same name!)

then used phpMyAdmin to set up permissions on the database, and things are working. Thanks for the guidance. I sure wouldn't have been sprinkling underscores in the names except that cPanel pushed me into it. There are so many cPanel web hosts out there, I hope this might help others who want to set up a development box to emulate the production system. The lesson seems to be, if you create a user name with an underscore using phpMyAdmin, it might look like all is well, but looks can be misleading.

John