Annoying mySQL issue...

[skeedo]

Ok, this is hardly programming but I couldn't find any other relevant places to post so here goes.

In the wonderful world of webhosting, sometimes we move a customer's database to a separate server to help alleviate server load. I did this tonight, and everything went well with the move, website worked fine. However after the database was moved, the user was not able to make changes within the database, and received the following error:

An error occurred while saving this configuration: SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('joebob_mysql'@'localhost') does not exist

Google tells me with this error that the user has to be created and granted privs, however the user joebob_mysql had already existed, and had been given all privileges on the remote host. I was kind of mindblown as to why he still could not make changes to the database.

The customer informed me that he would just dump the database and use sed to strip all the definer statements, then re-import the database. After some trial and error he got this to work.

Question is, what's going on here? The best thing I can think of is that mysql was expecting the user to be coming from localhost, and not @remoteip and this is why the definer statements had to be stripped out? Shouldn't there be a mysqldump command to exclude definer statements? Or maybe I'm just braindead, I've had a very rough week.

 

[Albatross]

It might be a mysql issue where you have to create a user twice once with

CREATE USER 'joebob_mysql'@'localhost'
CREATE USER 'joebob_mysql'@'%'

It is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order.

http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

Yes,mysql is retarded.

 

[skeedo]

Hmm good to know, thanks....I'll have to try that next time.