Description:
When granting privileges to a user an extra space after database.table (*.*) results in a syntax error.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 8.4.4-4 Percona Server (GPL), Release 4, Revision 844fde07
Copyright (c) 2009-2024 Percona LLC and/or its affiliates
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
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 repl_user@'%' IDENTIFIED BY 'S3cr3TP@55';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@'%';
ERROR 1064 (42000): 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 ' TO repl_user@'%'' at line 1
mysql>
Removing the extra space after *.* is successful:
mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@'%';
Query OK, 0 rows affected (0.00 sec)
Extra spaces in other places do not result in syntax error:
mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@'%';
Query OK, 0 rows affected (0.00 sec)
This is not limited to *.*:
mysql> GRANT REPLICATION SLAVE ON test.t TO repl_user@'%';
ERROR 1064 (42000): 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 ' TO repl_user@'%'' at line 1
I tested this on 5.7.17, 8.0.42, and 8.4.4
Apologies if this is a duplicate, I could not find it in the bugs db.
Cheers!
Dov Endress
How to repeat:
Create user.
Attempt to assign privilege with an extra space after database.table declaration.
Suggested fix:
make the whitespace irrelevant
