Yet another blog about PHP, HTML and CSS

This one was a real hard nut to crack. My workstation ip address has changed and I could no longer connect to my MySQL server remotely. So far nothing new or interesting. I went to cPanel and added my new ip address to “Remote MySQL” – a list of ip addresses that are allowed to access the database server remotely. Being a cautious person, I also removed my old ip address while I was there.

So now I could connect remotely both from my IDE (via JDBC) and from my local mysql client but to my surprise, I was still getting access denied from mysqldump. Mhm…

mysqldump: Got error: 1045: Access denied for user 'myusername'@'my.new.ip.address' (using password: YES) when using LOCK TABLES

I verified that myusername indeed had permissions for locking tables. And I was able to make an alternate dump with Adminer and load the dump into my local database. (There was just one unrelated error with an empty view definition.)

To cut the story short, the problem was in the view. It was defined remotely from my old ip address and it stuck with the view in DEFINER. For the view to work, its definer must have access to the database – which in my case it didn’t because I had already revoked access to my old ip address. So now I couldn’t see the view definition, or use it, getting access denied for the old ip address.

It dawned on me that the “unrelated error” in my Adminer dump probably wasn’t that unrelated. Adminer defines dummy tables for views before dropping them and replacing them with a real view. And the error was triggered when it attempted to create a table with no attributes.

Obviously, it failed to get a definition of the view defined from the old address and thus it couldn’t get its attributes either.

It’s really unfortunate that I was getting access denied for my new ip address when dumping with mysqldump while in fact, access was being denied to the old ip address. The whole thing with DEFINER and its ip address seems rather silly, if you ask me.

Anyway, the solution was to recreate the faulty view from my new ip address (I actually used Adminer to create it from localhost) and all was good then.

This entry was posted on Thursday, July 17th, 2014 at 2:04 pm and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.