Description:
When check_routines() in upgrading dd_version（8011 to 8025）, the warnings of parsing error of hints will treated as syntax_error.
following is the error logs:
2022-05-24T15:52:36.654063+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-05-24T15:52:36.671590+08:00 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80011' to '80023'.
2022-05-24T15:52:36.671944+08:00 1 [Note] [MY-013327] [Server] MySQL server upgrading from version '80011' to '80025'.
2022-05-24T15:52:36.792012+08:00 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
2022-05-24T15:52:36.793208+08:00 1 [Note] [MY-012356] [InnoDB] Scanned 7 tablespaces. Validated 7.
2022-05-24T15:52:36.989476+08:00 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-05-24T15:52:36.989901+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2022-05-24T15:52:36.992582+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2022-05-24T15:52:37.796185+08:00 0 [Note] [MY-010120] [Server] Binlog end
2022-05-24T15:52:37.797616+08:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'MyISAM'
2022-05-24T15:52:37.799168+08:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'InnoDB'
2022-05-24T15:52:37.799270+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2022-05-24T15:52:37.803291+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2022-05-24T15:52:38.333392+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 19341492
2022-05-24T15:52:38.333590+08:00 0 [Note] [MY-012255] [InnoDB] Removed temporary tablespace data file: "ibtmp1"
2022-05-24T15:52:38.333682+08:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'CSV'
2022-05-24T15:52:38.333792+08:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'daemon_keyring_proxy_plugin'
2022-05-24T15:52:38.335274+08:00 0 [System] [MY-010910] [Server] /mysql/svr/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.25) MySQL Community Server - GPL.
How to repeat:
1. initialize a mysql8.0.11 instance
2. create database/table/routine:
create database testdb;
create table testdb.t1(a int primary key, b int );
use testdb
DROP PROCEDURE IF EXISTS `insdata`;
DELIMITER $$
CREATE PROCEDURE `insdata`(IN beg INT, IN end INT)
BEGIN
-- 定义变量
DECLARE v_i int unsigned DEFAULT 0;
WHILE beg <= end DO
INSERT INTO testdb.t1 values (beg, beg);
SET v_i = v_i+1;
SET beg = beg+1;
END WHILE;
END $$
DELIMITER ;
CALL insdata(1, 1000);
DROP PROCEDURE IF EXISTS `sp_cnt_t1`;
DELIMITER $$
create procedure sp_cnt_t1()
begin
select /*+ SET_VAR(secondary_engine_cost_threshold=3) */ count(*) from testdb.t1;
end $$
DELIMITER ;
DROP PROCEDURE IF EXISTS `sp_cnt2_t1`;
DELIMITER $$
create procedure sp_cnt2_t1()
begin
select /*+ parallel(t1,4) */ count(*) from testdb.t1;
end $$
DELIMITER ;
use testdb
call sp_cnt_t1();
call sp_cnt2_t1();
show warnings;
+---------+------+---------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------+
| Warning | 1064 | Optimizer hint syntax error near 'parallel(t1,4) */ count(*) from testdb.t1;
end' at line 3 |
+---------+------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3. login in mysql8.0.11,and execute shutdown cmd
mysql> shutdown;
Query OK, 0 rows affected (0.01 sec)
4. modify svr to mysql8.0.25
[root@localhost svr]# ll
total 1332824
lrwxrwxrwx 1 greatdb root 23 May 24 15:42 mysql -> mysql-8.0.11-el7-x86_64
drwxr-xr-x 9 greatdb greatdb 129 May 24 14:50 mysql-8.0.11-el7-x86_64
-rw-r--r-- 1 greatdb greatdb 594580226 May 24 14:31 mysql-8.0.11-el7-x86_64.tar.gz
drwxr-xr-x 9 greatdb greatdb 129 May 24 15:33 mysql-8.0.25-el7-x86_64
-rw-r--r-- 1 greatdb greatdb 770227407 May 24 14:59 mysql-8.0.25-el7-x86_64.tar.gz
[root@localhost svr]#
[root@localhost svr]#
[root@localhost svr]#
[root@localhost svr]# unlink mysql
[root@localhost svr]# ln -s mysql-8.0.25-el7-x86_64 mysql
[root@localhost svr]#
[root@localhost svr]# ll
total 1332824
lrwxrwxrwx 1 root root 23 May 24 15:51 mysql -> mysql-8.0.25-el7-x86_64
drwxr-xr-x 9 greatdb greatdb 129 May 24 14:50 mysql-8.0.11-el7-x86_64
-rw-r--r-- 1 greatdb greatdb 594580226 May 24 14:31 mysql-8.0.11-el7-x86_64.tar.gz
drwxr-xr-x 9 greatdb greatdb 129 May 24 15:33 mysql-8.0.25-el7-x86_64
-rw-r--r-- 1 greatdb greatdb 770227407 May 24 14:59 mysql-8.0.25-el7-x86_64.tar.gz
[root@localhost svr]# chown -R greatdb: /mysql/
5. start mysql instance
./mysql/bin/mysqld_safe --defaults-file=/mysql/conf/mysql4406.cnf --user=greatdb &
6. start failed, Data Dictionary initialization failed
Suggested fix:
When check_routines() in upgrading dd_version, the warnings of parsing
error of hints will treated as syntax_error.
DROP PROCEDURE IF EXISTS `sp_cnt_t1`;
DELIMITER $$
create procedure sp_cnt_t1()
begin
select /*+ SET_VAR(secondary_engine_cost_threshold=3) */ count(*) from testdb.t1;
end $$
DELIMITER ;
DROP PROCEDURE IF EXISTS `sp_cnt2_t1`;
DELIMITER $$
create procedure sp_cnt2_t1()
begin
select /*+ parallel(t1,4) */ count(*) from testdb.t1;
end $$
DELIMITER ;
use testdb
call sp_cnt_t1();
call sp_cnt2_t1();
show warnings;
+---------+------+---------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------+
| Warning | 1064 | Optimizer hint syntax error near 'parallel(t1,4) */ count(*) from testdb.t1;
end' at line 3 |
+---------+------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Description: When check_routines() in upgrading dd_version（8011 to 8025）, the warnings of parsing error of hints will treated as syntax_error. following is the error logs: 2022-05-24T15:52:36.654063+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2022-05-24T15:52:36.671590+08:00 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80011' to '80023'. 2022-05-24T15:52:36.671944+08:00 1 [Note] [MY-013327] [Server] MySQL server upgrading from version '80011' to '80025'. 2022-05-24T15:52:36.792012+08:00 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files 2022-05-24T15:52:36.793208+08:00 1 [Note] [MY-012356] [InnoDB] Scanned 7 tablespaces. Validated 7. 2022-05-24T15:52:36.989476+08:00 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2022-05-24T15:52:36.989901+08:00 0 [ERROR] [MY-010119] [Server] Aborting 2022-05-24T15:52:36.992582+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting. 2022-05-24T15:52:37.796185+08:00 0 [Note] [MY-010120] [Server] Binlog end 2022-05-24T15:52:37.797616+08:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'MyISAM' 2022-05-24T15:52:37.799168+08:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'InnoDB' 2022-05-24T15:52:37.799270+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown... 2022-05-24T15:52:37.803291+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed... 2022-05-24T15:52:38.333392+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 19341492 2022-05-24T15:52:38.333590+08:00 0 [Note] [MY-012255] [InnoDB] Removed temporary tablespace data file: "ibtmp1" 2022-05-24T15:52:38.333682+08:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'CSV' 2022-05-24T15:52:38.333792+08:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'daemon_keyring_proxy_plugin' 2022-05-24T15:52:38.335274+08:00 0 [System] [MY-010910] [Server] /mysql/svr/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.25) MySQL Community Server - GPL. How to repeat: 1. initialize a mysql8.0.11 instance 2. create database/table/routine: create database testdb; create table testdb.t1(a int primary key, b int ); use testdb DROP PROCEDURE IF EXISTS `insdata`; DELIMITER $$ CREATE PROCEDURE `insdata`(IN beg INT, IN end INT) BEGIN -- 定义变量 DECLARE v_i int unsigned DEFAULT 0; WHILE beg <= end DO INSERT INTO testdb.t1 values (beg, beg); SET v_i = v_i+1; SET beg = beg+1; END WHILE; END $$ DELIMITER ; CALL insdata(1, 1000); DROP PROCEDURE IF EXISTS `sp_cnt_t1`; DELIMITER $$ create procedure sp_cnt_t1() begin select /*+ SET_VAR(secondary_engine_cost_threshold=3) */ count(*) from testdb.t1; end $$ DELIMITER ; DROP PROCEDURE IF EXISTS `sp_cnt2_t1`; DELIMITER $$ create procedure sp_cnt2_t1() begin select /*+ parallel(t1,4) */ count(*) from testdb.t1; end $$ DELIMITER ; use testdb call sp_cnt_t1(); call sp_cnt2_t1(); show warnings; +---------+------+---------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------+ | Warning | 1064 | Optimizer hint syntax error near 'parallel(t1,4) */ count(*) from testdb.t1; end' at line 3 | +---------+------+---------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 3. login in mysql8.0.11,and execute shutdown cmd mysql> shutdown; Query OK, 0 rows affected (0.01 sec) 4. modify svr to mysql8.0.25 [root@localhost svr]# ll total 1332824 lrwxrwxrwx 1 greatdb root 23 May 24 15:42 mysql -> mysql-8.0.11-el7-x86_64 drwxr-xr-x 9 greatdb greatdb 129 May 24 14:50 mysql-8.0.11-el7-x86_64 -rw-r--r-- 1 greatdb greatdb 594580226 May 24 14:31 mysql-8.0.11-el7-x86_64.tar.gz drwxr-xr-x 9 greatdb greatdb 129 May 24 15:33 mysql-8.0.25-el7-x86_64 -rw-r--r-- 1 greatdb greatdb 770227407 May 24 14:59 mysql-8.0.25-el7-x86_64.tar.gz [root@localhost svr]# [root@localhost svr]# [root@localhost svr]# [root@localhost svr]# unlink mysql [root@localhost svr]# ln -s mysql-8.0.25-el7-x86_64 mysql [root@localhost svr]# [root@localhost svr]# ll total 1332824 lrwxrwxrwx 1 root root 23 May 24 15:51 mysql -> mysql-8.0.25-el7-x86_64 drwxr-xr-x 9 greatdb greatdb 129 May 24 14:50 mysql-8.0.11-el7-x86_64 -rw-r--r-- 1 greatdb greatdb 594580226 May 24 14:31 mysql-8.0.11-el7-x86_64.tar.gz drwxr-xr-x 9 greatdb greatdb 129 May 24 15:33 mysql-8.0.25-el7-x86_64 -rw-r--r-- 1 greatdb greatdb 770227407 May 24 14:59 mysql-8.0.25-el7-x86_64.tar.gz [root@localhost svr]# chown -R greatdb: /mysql/ 5. start mysql instance ./mysql/bin/mysqld_safe --defaults-file=/mysql/conf/mysql4406.cnf --user=greatdb & 6. start failed, Data Dictionary initialization failed Suggested fix: When check_routines() in upgrading dd_version, the warnings of parsing error of hints will treated as syntax_error. DROP PROCEDURE IF EXISTS `sp_cnt_t1`; DELIMITER $$ create procedure sp_cnt_t1() begin select /*+ SET_VAR(secondary_engine_cost_threshold=3) */ count(*) from testdb.t1; end $$ DELIMITER ; DROP PROCEDURE IF EXISTS `sp_cnt2_t1`; DELIMITER $$ create procedure sp_cnt2_t1() begin select /*+ parallel(t1,4) */ count(*) from testdb.t1; end $$ DELIMITER ; use testdb call sp_cnt_t1(); call sp_cnt2_t1(); show warnings; +---------+------+---------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------+ | Warning | 1064 | Optimizer hint syntax error near 'parallel(t1,4) */ count(*) from testdb.t1; end' at line 3 | +---------+------+---------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)