Resolved After MySQL upgrade to 5.7.18 - I'm unable to solve user table problem

[Mauro Sanua]

In a plesk Onix after MySQL upgrade to 5.7.18 going to the admin panel I see the error:
ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [1045] Access denied for user 'admin'@'localhost' (using password: YES)

Additionally, an exception has occurred while trying to report this error: Zend_Exception
No entry is registered for key 'translate' (Abstract.php:144)

The MySQL log at the start is:
[root@instance-3 log]# vi mysqld.log
2017-06-07T08:03:40.605205Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-06-07T08:03:40.607790Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.18) starting as process 26048 ...
2017-06-07T08:03:40.611725Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-06-07T08:03:40.611753Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-06-07T08:03:40.611758Z 0 [Note] InnoDB: Uses event mutexes
2017-06-07T08:03:40.611761Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2017-06-07T08:03:40.611764Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-06-07T08:03:40.611767Z 0 [Note] InnoDB: Using Linux native AIO
2017-06-07T08:03:40.612099Z 0 [Note] InnoDB: Number of pools: 1
2017-06-07T08:03:40.612253Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-06-07T08:03:40.614121Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-06-07T08:03:40.624706Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-06-07T08:03:40.628267Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-06-07T08:03:40.640842Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-06-07T08:03:40.671741Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-06-07T08:03:40.671867Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-06-07T08:03:40.894670Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-06-07T08:03:40.895624Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-06-07T08:03:40.895640Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-06-07T08:03:40.896450Z 0 [Note] InnoDB: Waiting for purge to start
2017-06-07T08:03:40.946697Z 0 [Note] InnoDB: 5.7.18 started; log sequence number 904417444
2017-06-07T08:03:40.947623Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-06-07T08:03:40.950337Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-06-07T08:03:40.954368Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170607 8:03:40
2017-06-07T08:03:40.955656Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-06-07T08:03:40.955848Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2017-06-07T08:03:40.955877Z 0 [Note] IPv6 is available.
2017-06-07T08:03:40.955882Z 0 [Note] - '::' resolves to '::';
2017-06-07T08:03:40.955913Z 0 [Note] Server socket created on IP: '::'.
2017-06-07T08:03:40.965677Z 0 [Note] Event Scheduler: Loaded 0 events
2017-06-07T08:03:40.965850Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.18' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
2017-06-07T08:03:40.965858Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.
2017-06-07T08:03:40.965861Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-06-07T08:03:41.004442Z 0 [Note] End of list of non-natively partitioned tables
2017-06-07T08:03:41.261885Z 3 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)


I tried to apply the suggestions from mysql to upgrade the user table and reset the root password for mysql but at least I can have access to my plesk only with the --skip-grant-tables option with the user root in the my.cnf file.

the my.cnf file is:
[mysqld]
#bind-address = 127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#user=root
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#skip-grant-tables

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Is there someone who has solved the same problem ?
Thx

 

[AYamshanov]

Hi,

"Access denied for user 'admin'@'localhost' (using password: YES)"

• I think you need to set the correct password for admin user.
  • What is the root password for MySQL?
    
  • https://talk.plesk.com/threads/onyx-password-issue-admin.340811/

"UNKNOWN_MYSQL_USER"

• Did you see MySQL 5.7.17 startup log showing [Note] Access denied for user 'UNKNOWN_MYSQL_USER' ?

 

[Mauro Sanua]

Thx but.. for unknown reason after the upgrade to 5.7 the mysql.user table contains mysql user only ... then if happen the solution is:

After upgrade restore the mysql user table from a previous backup;
run mysql_upgrade;
try to log mysql as a user root, if you cant use the skip-grant-tables option;
select mysql db