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

[21 Feb 2019 3:34] SeWoong Jeon
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
[21 Feb 2019 6:01] MySQL Verification Team
Hello SeWoong Jeon,

Thank you for the report.

regards,
Umesh
[21 Feb 2019 6:02] MySQL Verification Team
- 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>
[15 Mar 2019 18:11] Paul Dubois
Posted by developer:
 
Fixed in 8.0.16.

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