Almost a year ago, I set up this server and migrated several sites from my old cPanel server.
Most of everything went relatively smoothly, but I have had no mySQL functionality whatsoever. Every few weeks, I try to get it working, but since it is not a revenue generating site, I give up after a few hours and go back to more important things.
Today I was finally able to ascertain that there are no permissions granted to any of the users for any of the databases on any of the sites that I migrated from cPanel. I have now spent hours trying to figure out how to grant those permissions.
You can also check in Virtualmin Tab: drop down to domain in question:
Edit Users > click on the user that needs permissions
Other user permissions > highlight the database and click on the arrow â Granted access to
Save
Okay. These two processes have already been tried.
First, cyberndt: That shows that the user in question has access to all databases under that account (domain).
ID10T: When I go there, the user (in fact, all users for all accounts, except root) shows âno permissionsâ, but when I click on the user, there is a box with what permissions to grant. No matter what I select in that box, nothing gets granted. I would have expected it to be similar to the other page where there are two boxes and arrows to select and deselect, but there isnât.
I have been at those two pages over and over while trying to get this working, but nothing seems to work the way it is supposed to.
Also, I have been trying to install phpmyadmin serverwide, but I can get it installed on only one domain. (Do I have to install it separately on each domain on the system?) If I could get it installed so that I could log in as root, I might get somewhere. (It cannot be installed on the domain that I am currently working on due to another issue. But, of course, I am hoping to solve this issue on all domains.)
Yes. On that page, all users are listed with âAll permissionsâ granted each to its respective database.
Everything I have tried since day one appears to be set correctly, but when I log into mysql from the command prompt and âSHOW GRANTSâ, I get nothing at all. Only root is granted any permissions. I think that I can figure out how to grant permissions through the cli, but for dozens of users and databases that will take a lifetime of typing.
Am I correct in believing that SHOW GRANTS should show all grants for all users? Or should it show only those permissions granted to root?
So I was mistaken that there were no permissions granted to the other users. Good to know.
It seems that all grants are there as they should be, but if I select the user from the user table (in the main mysql db), all permissions are set to âNâ, except for the user to whom I granted all permissions (from the cli).
I created a short php script (while in ssh as root) to connect using those credentials and it connected fine. But the page on the site wonât connect even though I seem to have granted privileges akin to root.
Iâm not sure if maybe Iâm barking up the wrong tree.
Are you using the correct format for the username and database in the config?
You have mentioned that this came over from a cpanel server. I believe their username and database names are in different format from what Virtualmin uses.
So far, I have been testing using mysqli_connect(âlocalhostâ, âusernameâ, âpasswordâ) and doing a var_dump of the result. On the web page, I am simply getting bool(false), while in the script run as root from the cli (exactly the same credentials), I am getting an object where all seems right.
Could it be the difference between running as root vs www?
I havenât yet even made it to actually trying to run queries on any tables.
I have sort of been barking up the wrong tree insofar as itâs not permissions but passwords which are giving me the issue.
I have been able to get the following error out of my db:
âPHP Warning: mysqli_connect(): (HY000/1275): Server is running in --secure-auth mode, but âuserâ@âlocalhostâ has a password in the old format; please change the password to the new format in /home/pathto/script.php on line xâ
The thing is all my passwords are in the ânewâ format (which I think was new almost ten years ago). All of my searching has revealed that the old format had a 16 character hash and the new one 41 characters. All of mine have 41 characters.
So when I run the (portion of the) script in question, I get the above error, both on the command line and in the browser. (Actually, the browser doesnât show the error, just the result.) But when I run the exact same code (with the same credentials) in the new script I wrote as root, it connects with no error.
Also, if I simply change the credentials in the original script to the root user, it also connects flawlessly.
Edited to add: So I guess this isnât really a virtualmin issue, but Iâm grateful for help in any case.
Yes. I had tried both of those solutions. It turns out that it is a Virtualmin problem after all.
I tried also to change the password for the account itself, Virtualmin warning me that that would change the mySQL password. That didnât work.
However, I have since made a discovery (from a 4 year old post on Virtualminâs old forum) that the queries I was running on the mysql database, while appearing to have an effect, did not indeed have any effect.
Instead of UPDATE mysql.user SET Password = PASSWORD(âmypasswordâ) WHEREâŠ, the way to do it (and it seems to need to be done with every imported db) is set password for user@localhost = PASSWORD(âmypasswordâ). Once I used that syntax, everything worked.