MySQL Bugs: #96288: MEMBER Reserved Word causes server start failure on upgrade to 8.0.17

Bug #96288	MEMBER Reserved Word causes server start failure on upgrade to 8.0.17
Submitted:	23 Jul 2019 9:58	Modified:	7 Nov 2019 15:57
Reporter:	Luciano Nicastro	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	8.0.17	OS:	Ubuntu (18.04)
Assigned to:		CPU Architecture:	Any
Tags:	reserved keywords, udf

Description:
Upgrading from 8.0.16 to 8.0.17 made my system unusable. Any --upgrade option failed. The reason being the introduction of the reserved word MEMBER I am using as parameter in a UDF. The log reported:

[System] [MY-011090] [Server] Data dictionary upgrading from version '80016' to '80017'.
[ERROR] [MY-013235] [Server] Error in parsing Routine 'myro'.'assign_usr_to_grp' during upgrade. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'member BOOLEAN) RETURNS int(11)

How to repeat:
In version 18.0.16 or earlier create and install a UDF with a parameter "member". Try to upgrade to version 18.0.17.

Suggested fix:
Manage properly the introduction of new reserved words in the upgrade process, e.g. issuing warnings and skipping the upgrade of functions/procedures using them.

Hello Luciano,

Thank you for the report and feedback.
Observed that in-place upgrade fails i.e when upgrading from 8.0.16 to 8.0.17 server fails to start if 8.0.16 contains any stored function and has "member" word used in it. But, I didn't observe any "crash", please confirm on what "crash" you are seeing? Please provide unaltered server log(mark it as private if you wish to). 

regards,
Umesh

- Start 8.0.16, create some func with reserve word	

rm -rf 96288/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/96288 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/96288 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3333 --log-error=$PWD/96288/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ 2>&1 &	

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql>  delimiter //
mysql>
mysql>  CREATE FUNCTION hello (MEMBER CHAR(20))
    ->  RETURNS CHAR(50) DETERMINISTIC
    ->     RETURN CONCAT('Hello, ',MEMBER,'!')//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>  delimiter ;
mysql>
mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.16: bin/mysqladmin -uroot -S /tmp/mysql_ushastry.sock  shutdown
[1]+  Done                    bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/96288 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/96288/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv=/tmp/ 2>&1

- Bring down 8.0.16, attempt in place upgrade to 8.0.17
- 8.0.17 build details

 cat docs/INFO_SRC
commit: a5b2e523c881ab8c68fd0adb478a6232dd69e522
date: 2019-06-25 11:50:40 +0200
build-date: 2019-06-25 12:24:04 +0200
short: a5b2e52
branch: mysql-8.0.17-release

MySQL source 8.0.17

bin/mysqld --no-defaults --basedir=$PWD --datadir=/export/umesh/server/binaries/GABuilds/mysql-8.0.16/96288 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3333 --log-error=/export/umesh/server/binaries/GABuilds/mysql-8.0.16/96288/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ 2>&1 &	

^^ noticed that server fails to start

2019-07-23T10:37:50.472786Z 1 [Note] [MY-013327] [Server] MySQL server upgrading from version '80016' to '80017'.
2019-07-23T10:37:50.593513Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
2019-07-23T10:37:50.594641Z 1 [Note] [MY-012356] [InnoDB] Validated 6/6  tablespaces
2019-07-23T10:37:50.595156Z 0 [Note] [MY-011946] [InnoDB] Loading buffer pool(s) from /export/umesh/server/binaries/GABuilds/mysql-8.0.16/96288/ib_buffer_pool
2019-07-23T10:37:50.596002Z 0 [Note] [MY-011946] [InnoDB] Buffer pool(s) load completed at 190723 12:37:50
2019-07-23T10:37:50.838468Z 1 [ERROR] [MY-013235] [Server] Error in parsing Routine 'test'.'hello' during upgrade. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MEMBER CHAR(20)) RETURNS char(50) CHARSET utf8mb4
    DETERMINISTIC
RETURN CONCA' at line 1
2019-07-23T10:37:50.850684Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-07-23T10:37:50.850969Z 0 [ERROR] [MY-010119] [Server] Aborting

- with --upgrade

2019-07-23T10:37:50.472516Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80016' to '80017'.
2019-07-23T10:37:50.472786Z 1 [Note] [MY-013327] [Server] MySQL server upgrading from version '80016' to '80017'.
2019-07-23T10:37:50.593513Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
2019-07-23T10:37:50.594641Z 1 [Note] [MY-012356] [InnoDB] Validated 6/6  tablespaces
2019-07-23T10:37:50.595156Z 0 [Note] [MY-011946] [InnoDB] Loading buffer pool(s) from /export/umesh/server/binaries/GABuilds/mysql-8.0.16/96288/ib_buffer_pool
2019-07-23T10:37:50.596002Z 0 [Note] [MY-011946] [InnoDB] Buffer pool(s) load completed at 190723 12:37:50
2019-07-23T10:37:50.838468Z 1 [ERROR] [MY-013235] [Server] Error in parsing Routine 'test'.'hello' during upgrade. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MEMBER CHAR(20)) RETURNS char(50) CHARSET utf8mb4
    DETERMINISTIC
RETURN CONCA' at line 1
2019-07-23T10:37:50.850684Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-07-23T10:37:50.850969Z 0 [ERROR] [MY-010119] [Server] Aborting

- 8.0.16

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql>  delimiter //
mysql>
mysql>  CREATE FUNCTION hello (MEMBER CHAR(20))
    ->  RETURNS CHAR(50) DETERMINISTIC
    ->     RETURN CONCAT('Hello, ',MEMBER,'!')//
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql>  delimiter ;
mysql>
mysql>  delimiter //
mysql>  DROp PROCEDURE IF EXISTS simpleproc;
    ->  CREATE PROCEDURE simpleproc (OUT MEMBER INT)
    ->  BEGIN
    ->    SELECT COUNT(*) INTO MEMBER FROM t;
    ->  END//
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

- Use mysql shell to see if there is any issue in upgrade etc

 bin/mysqlsh --version
bin/mysqlsh   Ver 8.0.17 for Linux on x86_64 - for MySQL 8.0.17 (MySQL Community Server (GPL))

 bin/mysqlsh root:@localhost:3333 -e "util.checkForServerUpgrade();"
WARNING: Using a password on the command line interface can be insecure.
The MySQL server at localhost:3333, version 8.0.16 - MySQL Community Server -
GPL, will now be checked for compatibility issues for upgrade to MySQL 8.0.17...

1) Circular directory references in tablespace data file paths
  No issues found

2) Issues reported by 'check table x for upgrade' command
  No issues found

Errors:   0
Warnings: 0
Notices:  0

No known compatibility errors or issues were found.

8.0.17 introduced "MEMBER" reserve word https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-new-in-current-series
Imho reserved words are permitted as identifiers if you quote them with  backticks as explained here https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Posted by developer:
 
'MEMBER' became reserved as part of the work on multi-valued indexes, which introduced

MEMBER OF
---------
Another addition of this WL is the MEMBER OF function. Syntax is:

<sql scalar> MEMBER OF (<json array>)

It returns true if the scalar on the left is found in the array on the right.
False otherwise (not found/no array on right), and NULL if either of args is
NULL. If two scalars are given on both sides, then it behaves like '='. The
function is similar to IN(), with difference being that IN() operates on sql
row of values, while MEMBER OF() operates on a JSON array. Other difference is
that subselects aren't allowed in neither argument.

Hi Umesh,

thank you for your replies.
You are right that the word "crash" referred to the failure to start the MySQL server is not correct, but it surely applies to my RDBMS system as a whole. Bug title changed.

Regarding the keywords quoting, I know that very well, but you understand that it's unlikely one would go through the whole list of functions, procedures, triggers stored on a (multi-user) server and check parameter names before any version upgrade. In fact this is the first time such a thing happened to me in 20 years of usage of MySQL.
Anyway it would be good to know if this is something accounted for by the developers, and left to the user's care, or if in fact it can/will be managed in future versions.

Ciao, Luciano

Thank you Luciano for clarifying on "crash" part.
I fully agree with you, at the moment only thing I can confirm is that this has already got required attention.

regards,
Umesh

Related - Bug #96350

Documented fix as follows in the MySQL 8.0.19 changelog:

    If a stored procedure had a parameter named "member" or "array",
    and it had been defined without quoting the parameter names, the
    database in which it was defined could not be upgraded to 8.0.17
    or 8.0.18.

Closed.