Analysis of Mysql 5.7 Error Reporting 1546, 1577 and 1682

Environmental description:
The backup data of xtrabackup on MySQL 5.6.40 is imported into MySQL 5.7.24 instance, the service of MySQL 5.7 is started, and the database MySQL 5.7 instance is logged in. However, when drop user user@'127.0.0.1', the error is reported as follows:

2019-08-15T19:02:31.160910+08:00 1546 [ERROR] /usr/local/mysql5.7/bin/mysqld: Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 50640, now running 50724. Please use mysql_upgrade to fix this error.

The example version of mysql is as follows:

[root@e ~]# /usr/local/mysql/bin/mysqld -V
/usr/local/mysql/bin/mysqld  Ver 5.6.40 for Linux on x86_64 (Source distribution)
[root@e ~]# /usr/local/mysql5.7/bin/mysqld -V
/usr/local/mysql5.7/bin/mysqld  Ver 5.7.24 for linux-glibc2.12 on x86_64 (MySQL Community Server (GPL))

Solution: MySQL 5.7 Upgraded

/usr/local/mysql5.7/bin/mysql_upgrade -uroot -p'ln.orge#dieurw5199' -S /tmp/3306.sock
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.

testdb29.dtr_wx_gotourl                             OK
testdb29.dtr_zone                                   OK
testdb29.hm_planstats_h                            OK
Upgrade process completed successfully.
Checking if update is needed.

Users logged in MySQL 5.7 to delete the library successfully

mysql> drop user backupuser@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

Tip: The MySQL 5.7.24 instance did not restart at this time

When a timer is created to call the stored procedure, the following error is reported:

mysql> CREATE EVENT test1
    -> ON SCHEDULE EVERY 2 second STARTS TIMESTAMP '2019-08-10 16:16:22'
    -> ON COMPLETION PRESERVE
    -> DO
    -> BEGIN
    -> CALL test1();
    -> END//

ERROR 1577 (HY000): Cannot proceed because system tables used by Event Scheduler were found damaged at server start
mysql> show events;
ERROR 1577 (HY000): Cannot proceed because system tables used by Event Scheduler were found damaged at server start

### The prompt cannot continue because the system table used by the event scheduler was found to be corrupted at server startup

Looking at mysql system variables at this point, the following errors are reported:

mysql> show variables like 'event_scheduler';
ERROR 1682 (HY000): Native table 'performance_schema'.'session_variables' has the wrong structure

The reason for this is that I upgraded the version of mysql, but did not restart the MySQL instance.
MySQL 5.7.24 service needs to be restarted. Otherwise, some of MySQL's system tables will not be recognized.

service restart mysql5.7.24

The problem has been solved here.

mysql> show events;
Empty set (0.00 sec)

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.01 sec)

Create a new timer and execute successfully:

mysql> CREATE PROCEDURE test1() 
    -> BEGIN
    -> INSERT INTO tb01(username,password,create_time) values('Li Si', 'Zhang San',now());
    -> END//
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE EVENT test1
    -> ON SCHEDULE EVERY 5 second STARTS TIMESTAMP '2019-08-16 17:34:22'
    -> ON COMPLETION PRESERVE
    -> DO
    -> BEGIN
    -> CALL test1();
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ; 
mysql> select now();

Tags: Linux MySQL Database Stored Procedure

Posted on Fri, 04 Oct 2019 15:52:26 -0400 by danrah