new MySQL user not working through SSH

Avatar
Brettflan
  • updated
  • Answered

We have just upgraded to a VPS plan in order to get Magento 2.4 fully working (it requires elasticsearch, which needed the VPS and which I've already installed; that's unrelated to my problem though).


I've just added a new MySQL database through cPanel, and added a user for it, which is of course listed as associated with the database.

When I go into the Root WHM panel's Terminal and try to connect to MySQL through commandline for that user, it works fine.

Example command (replaced actual username with "test_db_user"):

mysql -u test_db_user -p

It connects with that user account fine.

However, when I connect through SSH as my standard CPanel user and run the same command, giving the same password,I get:

ERROR 1045 (28000): Access denied for user 'test_db_user'@'localhost' (using password: YES)

After checking through various things, I then added privileges for an existing MySQL user to the new database. I again can connect fine through the root terminal to the older username, and running "show databases;" lists the older database that user already had access to along with the new database which I just added privileges for. No problems there.

I then connect again through SSH as my standard CPanel user and try to connect to MySQL while specifying that older MySQL user. This time it connects properly, no error. However, when I run "show databases;", it only lists the old database that MySQL user already had access to; it doesn't list the new database, even though the new database showed up through the same command through the root terminal.


It's like it's seeing an older cached version of the MySQL databases (or maybe just MySQL users) when I try to connect through my standard account through SSH. I've tried restarting the MySQL service and restarting the VPS itself, no change in behavior.


Why would this be happening? How can I fix it? Will it just fix itself given enough time? I need to be able to run the installer for Magento 2.4 through SSH which naturally requires database access, so I need to find a fix. The only workaround I can think of is to co-opt an older existing database and user, just wiping that database, but I'd rather not have to resort to that.

Replies 3
Avatar
0
anonymous
  • Answered
Quote from Brettflan

Further followup, found the cause with help from support. When the account was upgraded to VPS, the VPS is on a new IP address. Since we don't have our DNS handled through inMotionHosting, we need to update it manually; an e-mail was sent to that effect, but it got missed on our end.

So, the SSH account (and domain name and website) are still pointing to the old server, while all changes I made through cPanel were on the new server which has a (now outdated) copy of all of the data from the old one.

So, I'll need to temporarily close our storefront to not lose sales data, make the DNS changes to point to the new server, then have support re-sync our data from the old server to the new one again (or possibly do it myself), and reopen the storefront on the new server.

Well, at least I know what the problem is now.

I'm glad you got an answer on this. Remember, the Community Support Center is only monitored 9AM-5PM Mon-Friday.

Avatar
0
Brettflan

Further followup, found the cause with help from support. When the account was upgraded to VPS, the VPS is on a new IP address. Since we don't have our DNS handled through inMotionHosting, we need to update it manually; an e-mail was sent to that effect, but it got missed on our end.

So, the SSH account (and domain name and website) are still pointing to the old server, while all changes I made through cPanel were on the new server which has a (now outdated) copy of all of the data from the old one.

So, I'll need to temporarily close our storefront to not lose sales data, make the DNS changes to point to the new server, then have support re-sync our data from the old server to the new one again (or possibly do it myself), and reopen the storefront on the new server.

Well, at least I know what the problem is now.

Avatar
0
Brettflan

OK, I managed to find a workaround.


First, for testing, I added a new table to the old database that old account was showing to MySQL run from the commandline through SSH on the server. I then connected to that database again through SSH, and found that the new table wasn't showing up. So it wasn't just a user/permission caching issue, it was the entire database.

Second, in WHM panel, under "Additional MySQL Access Hosts", I added each of the IPs listed in the MySQL users table (through phpMyAdmin, the "mysql" database, the "users" table) which seemed to be associated with every user, 3 IPs in total. I don't think this did anything, as the mysql commandline run through SSH was still showing only the old databases for that one user which I added privileges on the new database. But, I'm including this step as there's a slim chance it contributed.

Finally, I tried specifying each of those 3 IP addresses as the host on the command line, ex. "mysql -u test_db_user -p -h 1.2.3.4". One of them didn't respond, the second one connected but still only showed the old database, but the third one I tried worked! It is correctly showing the new database as accessible to that user. SO I'm going to assume that was the necessary step, connecting to that one specific IP as MySQL host instead of just using localhost.

So, that works, I guess.

I'm thinking this problem likely stems from the hosting account previously being a Pro account, and having been upgraded to VPS a few days ago.