Bug #107384 the warnings of parsing error of hints are treated as syntax_error when upgrade
Submitted: 24 May 8:10 Modified: 24 May 9:29
Reporter: beon feng Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S3 (Non-critical)
Version:8.0.25, 8.0.29 OS:CentOS (CentOS Linux release 7.6.1810 (Core) )
Assigned to: CPU Architecture:x86
Tags: datadictionary

[24 May 8:10] beon feng
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)
[24 May 9:29] MySQL Verification Team
Hello beon feng,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh