Mysql 8.4 user permissions
The process to update from 8.0 to 8.4 completed but Mysql moved to the /db/ directory and would not read the /etc/my.cnf correctly. Took a while and some reboots but finally overcame that much.
The problem now is noone has access to their own databases. The can see them in Cpanel, change passwords etc.
In phpmyadmin all the users see is these 2 databases:
operationsinformation_schema
operationsperformance_schema
but not their databases.
If they try and use their databases from the sites they get permission denied.
Nothing in the /usr/local/cpanel/logs/error_log.
Reran the usergrants and still not fixed.
Ideas what to try next?
-
I see there is a mysql 8.4.5 issue that this might be related to. The fix for that is to go back to 8.4.4.
I do not think I can do that as I did this upgrade from 8.0 to 8.4.5 yesterday and never had 8.4.4 installed.
0 -
1. You need to comment out the /db/config/my.cnf and then make it immutable
2. Then restart MySQL
3. If any Db user is unable to connect , delete the privileges and recreate them0 -
Thanks for the info. We tried to delete the db users and readd in cpanel with full perms.
They still do not have permission to the database and none of the cpanel users databases show up in phpmyadmin, but they do show up in cpanel/manage my databases.
0 -
do this after substituting the dbuser, db in the below
mysql -u root -p --socket=/var/lib/mysql/mysql.sock
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username_dbuser'@'localhost';
GRANT ALL PRIVILEGES ON `username_db`.* TO 'username_dbuser'@'localhost';
FLUSH PRIVILEGES;0 -
No change. What's weird is 0 rows affected.
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'xxx_xxx'@'localhost';
Query OK, 0 rows affected (0.01 sec)mysql> GRANT ALL PRIVILEGES ON `xxx_xxx`.* TO 'xxx_xxx'@'localhost';
Query OK, 0 rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)But if i check MYSQL/USERS db they are all there with permission.
0 -
Hi,
I suggest opening a ticket so the issue can be reviewed closer with access to the server. Although it may be more difficult to accomplish, it still should be possible to perform a minor MySQL downgrade to a non-affected version even though the server skipped several versions when upgrading.
0 -
I did that but they are brushing me off pretty much telling me to read the release.
I cannot see this going well and would appreciate Cpanel support on this.
0 -
@Tos
Whats the exact error messge you are getting?
0 -
We have tried restoring grants but it does not help.
When trying to use a database they own they just get:
Access denied for user 'xxx_xxx'@'localhost' to database 'xxx_xxx'
No matter what user they use. The only one with permission to databases is root.
In phpmyadmin there is no error. All the users see is these 2 databases:
operationsinformation_schema
operationsperformance_schema0 -
I personally haven't heard of MySQL changing the data directory as part of an update, so that's a new one for me. In order to avoid data loss it would be best to create a ticket. If your ticket is with your hosting provider and they can't resolve the issue, they should escalate it to us so we can review it.
0 -
This ended up being related to the new MySQL issue with the incorrect rpms.
I did my 8.0 to 8.4 upgrade during the window they were out. It changed my datadir to the /db/ directory as well as read config files from there. Renaming the my.cnf in /db/ helped get the datadir back to the correct place but problems lingered. The only cure was to fully remove the mysql-cloud rpms and reinstall the mysql-community rpms. I couldn't follow the undo because of the version jump.
0
Please sign in to leave a comment.
Comments
11 comments