MySQL Bugs: #94416: "set password for... " command in the prepared statement cause 1295 ERROR

Bug #94416	"set password for... " command in the prepared statement cause 1295 ERROR
Submitted:	21 Feb 2019 3:34	Modified:	15 Mar 2019 18:11
Reporter:	SeWoong Jeon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0.15	OS:	Linux (CentOS 7.5)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	regression

Description:
"set password for... " command in the prepared statement cause 1295 ERROR.

How to repeat:
-- mysql 5.7.22
mysql> prepare test from "set password for xxxx@'a.b.c.d' = 'xxxx'";
Query OK, 0 rows affected (0.00 sec) 
Statement prepared

-- mysql 8.0.15
mysql> prepare test from "set password for xxxx@'a.b.c.d' = 'xxxx'";
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

Hello SeWoong Jeon,

Thank you for the report.

regards,
Umesh

- looks like regression to me, 5.7 supports where as issue seen from dmr release of 8.0

rm -rf 94412
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/94412 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/94412 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/94412/log.err --log-error-verbosity=3 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 2
Server version: 5.7.25 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 user xxxx@'a.b.c.d';
Query OK, 0 rows affected (0.00 sec)

mysql> prepare test from "set password for xxxx@'a.b.c.d' = 'xxxx'";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute test;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

- 8.0.15 (seen even in 8.0.1 build, so this is not something new)
- SET is allowed per docs - https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html

 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.15 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 user xxxx@'a.b.c.d';
Query OK, 0 rows affected (0.01 sec)

mysql> prepare test from "set password for xxxx@'a.b.c.d' = 'xxxx'";
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
mysql> show errors;
+-------+------+----------------------------------------------------------------------+
| Level | Code | Message                                                              |
+-------+------+----------------------------------------------------------------------+
| Error | 1295 | This command is not supported in the prepared statement protocol yet |
+-------+------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

- Lowest version checked 8.0.1

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

Copyright (c) 2000, 2017, 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 user xxxx@'a.b.c.d';
Query OK, 0 rows affected (0.00 sec)

mysql> prepare test from "set password for xxxx@'a.b.c.d' = 'xxxx'";
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
mysql>

Posted by developer:
 
Fixed in 8.0.16.

SET PASSWORD FOR ... could not be executed as a prepared statement.