Bug #100249 mysql username with special characters @ and routines parsing issue

• Submitted: 17 Jul 2020 14:44
• Modified: 17 Jul 2020 18:50
• Reporter: lalit Choudhary
• Status: Verified
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[17 Jul 2020 14:44] lalit Choudhary

Description:
mysql username with special characters @ will not work as definer correctly in mysql routines (event,store Procedure , etc)

How to repeat:
Test:

mysql> create user '[e-mail]'@'192.%' identified by 'Admin123';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL ON *.* TO '[e-mail]'@'192.%';
Query OK, 0 rows affected (0.03 sec)

mysql> show grants for  '[e-mail]'@'192.%';
+-------------------------------------------------------------------+
| Grants for [e-mail]@192.%                         |
+-------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO '[e-mail]'@'192.%' |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

# mysql  --host=192.168.0.138 --user="[e-mail]" -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

Current user:		[e-mail]@node2
Event:

mysql> DELIMITER $$
mysql> CREATE DEFINER="[e-mail]'@'192.%" EVENT `test_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2020-07-16 12:54:46' ON COMPLETION PRESERVE ENABLE DO BEGIN INSERT INTO tt(d) SELECT NOW();
    -> END$$
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings$$
+-------+------+-----------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                 |
+-------+------+-----------------------------------------------------------------------------------------+
| Note  | 1449 | The user specified as a definer ('[e-mail]'@'192.%'@'%') does not exist |
+-------+------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DELIMITER ;
mysql error log:

2020-07-17T14:09:46.573561Z 620 [ERROR] Event Scheduler: [[e-mail]'@'192.%@%].[foo.test_event] execution failed, failed to authenticate the user.
2020-07-17T14:09:46.573586Z 620 [ERROR] Event Scheduler: [[e-mail]'@'192.%@%][foo.test_event] The user specified as a definer ('ddtestdev'@'re-testcom.net'@'192.%@%') does not exist
2020-07-17T14:09:46.573599Z 620 [Note] Event Scheduler: [[e-mail]'@'192.%@%].[foo.test_event] event execution failed.
another try:

mysql> DELIMITER $$
mysql> CREATE DEFINER="'[e-mail]'@'192.%'" EVENT `test_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2020-07-16 12:54:46' ON COMPLETION PRESERVE ENABLE DO BEGIN INSERT INTO tt(d) SELECT NOW();
    -> END$$
ERROR 1470 (HY000): String ''[e-mail]'@'192.%'' is too long for user name (should be no longer than 32)
mysql> show warnings$$
+-------+------+-----------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                             |
+-------+------+-----------------------------------------------------------------------------------------------------+
| Error | 1470 | String ''[e-mail]'@'192.%'' is too long for user name (should be no longer than 32) |
+-------+------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DELIMITER ;
Store Procedure:

DELIMITER //
CREATE  DEFINER="[e-mail]'@'192.%" PROCEDURE test_sp()
BEGIN
  SELECT 1;
END //
show warnings//
DELIMITER ;

mysql [localhost] {root} (test) > DELIMITER //
mysql [localhost] {root} (test) > CREATE  DEFINER="[e-mail]'@'192.%" PROCEDURE test_sp()
    -> BEGIN
    ->   SELECT 1;
    -> END //
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost] {root} (test) > show warnings//
+-------+------+-----------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                 |
+-------+------+-----------------------------------------------------------------------------------------+
| Note  | 1449 | The user specified as a definer ('[e-mail]'@'192.%'@'%') does not exist |
+-------+------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > DELIMITER ;

Suggested fix:
it should work the same way it work with mysql client. mysql client does not have any issue with parsing username with special cha.

[17 Jul 2020 17:23] MySQL Verification Team

It works for me on 8.0.21.  I think you've misquoted things for the username when creating the EVENT.  Look closely:

mysql> create user '[e-mail]'@'192.%' identified by 'xxx';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON *.* TO '[e-mail]'@'192.%';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for  '[e-mail]'@'192.%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [e-mail]@192.%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `[e-mail]`@`192.%`                                                                                                                                               |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `[e-mail]`@`192.%` |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE DEFINER='[e-mail]'@'192.%' EVENT `test_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2020-07-16 12:54:46' ON COMPLETION PRESERVE ENABLE DO BEGIN INSERT INTO tt(d) SELECT NOW();
    -> END$$
Query OK, 0 rows affected (0.01 sec)

============
Error log shows the event executed:

2020-07-17T17:21:46.648913Z 8 [ERROR] [MY-010045] [Server] Event Scheduler: [[e-mail]@192.%][test.test_event] Table 'test.tt' doesn't exist
2020-07-17T17:21:46.648955Z 8 [Note] [MY-010046] [Server] Event Scheduler: [[e-mail]@192.%].[test.test_event] event execution failed.

regards,
sbester

[17 Jul 2020 17:26] MySQL Verification Team

mysql> DELIMITER //
mysql> CREATE  DEFINER='[e-mail]'@'192.%' PROCEDURE test_sp()
    -> BEGIN
    ->   SELECT 1;
    -> END //
Query OK, 0 rows affected (0.02 sec)

mysql> show warnings//
Empty set (0.00 sec)

mysql> DELIMITER ;
mysql> call test_sp();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[17 Jul 2020 18:10] lalit Choudhary

Thank you for the test example, Yes single quotes did the magic but only for 8.0 version.

With same test issue reproducible in 5.7.

Test case : MySQL 5.7.30

mysql > set global event_scheduler=on;

mysql > create table tt(d timestamp);
Query OK, 0 rows affected (0.09 sec)

mysql [localhost] {root} (test) > DELIMITER $$
mysql [localhost] {root} (test) > CREATE DEFINER='[e-mail]'@'192.%' EVENT `test_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2020-07-16 12:54:46' ON COMPLETION PRESERVE ENABLE DO BEGIN INSERT INTO tt(d) SELECT NOW();
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} (test) > show warnings$$
Empty set (0.00 sec)

mysql [localhost] {root} (test) > DELIMITER ;

2020-07-17T17:55:23.303148Z 0 [Note] /home/lalit/mysql_tar/mysql/5.7.30/bin/mysqld: ready for connections.
Version: '5.7.30-log'  socket: '/tmp/mysql_sandbox22805.sock'  port: 22805  MySQL Community Server (GPL)
2020-07-17T17:57:31.657794Z 4 [Note] Event Scheduler: scheduler thread started with id 4
2020-07-17T17:57:46.683166Z 5 [ERROR] Event Scheduler: [[e-mail]@192.%].[test.test_event] execution failed, failed to authenticate the user.
2020-07-17T17:57:46.683275Z 5 [ERROR] Event Scheduler: [[e-mail]@192.%][test.test_event] The user specified as a definer ('ddtestdev'@'re-testcom.net@192.%') does not exist
2020-07-17T17:57:46.683297Z 5 [Note] Event Scheduler: [[e-mail]@192.%].[test.test_event] event execution failed.

[17 Jul 2020 18:10] lalit Choudhary

Thank you for the test example, Yes single quotes did the magic but only for 8.0 version.

With same test issue reproducible in 5.7.

Test case : MySQL 5.7.30

mysql > set global event_scheduler=on;

mysql > create table tt(d timestamp);
Query OK, 0 rows affected (0.09 sec)

mysql [localhost] {root} (test) > DELIMITER $$
mysql [localhost] {root} (test) > CREATE DEFINER='[e-mail]'@'192.%' EVENT `test_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2020-07-16 12:54:46' ON COMPLETION PRESERVE ENABLE DO BEGIN INSERT INTO tt(d) SELECT NOW();
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} (test) > show warnings$$
Empty set (0.00 sec)

mysql [localhost] {root} (test) > DELIMITER ;

2020-07-17T17:55:23.303148Z 0 [Note] /home/lalit/mysql_tar/mysql/5.7.30/bin/mysqld: ready for connections.
Version: '5.7.30-log'  socket: '/tmp/mysql_sandbox22805.sock'  port: 22805  MySQL Community Server (GPL)
2020-07-17T17:57:31.657794Z 4 [Note] Event Scheduler: scheduler thread started with id 4
2020-07-17T17:57:46.683166Z 5 [ERROR] Event Scheduler: [[e-mail]@192.%].[test.test_event] execution failed, failed to authenticate the user.
2020-07-17T17:57:46.683275Z 5 [ERROR] Event Scheduler: [[e-mail]@192.%][test.test_event] The user specified as a definer ('ddtestdev'@'re-testcom.net@192.%') does not exist
2020-07-17T17:57:46.683297Z 5 [Note] Event Scheduler: [[e-mail]@192.%].[test.test_event] event execution failed.

[17 Jul 2020 18:14] lalit Choudhary

No issue with Store procedure: tested with MySQL 5.7.30

DELIMITER //
CREATE  DEFINER='[e-mail]'@'192.%' PROCEDURE test_sp()
BEGIN
  SELECT 1;
END //
show warnings//
DELIMITER ;

master [localhost] {root} (test) > DELIMITER //
master [localhost] {root} (test) > CREATE  DEFINER='[e-mail]'@'192.%' PROCEDURE test_sp()
    -> BEGIN
    ->   SELECT 1;
    -> END //
Query OK, 0 rows affected (0.02 sec)

master [localhost] {root} (test) > show warnings//
Empty set (0.00 sec)

master [localhost] {root} (test) > DELIMITER ;
master [localhost] {root} (test) > call test_sp;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)

[17 Jul 2020 18:50] MySQL Verification Team

Okay, on 5.7.31 I got the trouble too:

mysql> set global event_scheduler=on;
Query OK, 0 rows affected (0.00 sec)

mysql> create user '[e-mail]'@'192.%' identified by 'xxx';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.* TO '[e-mail]'@'192.%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for  '[e-mail]'@'192.%';
+-------------------------------------------------------------------+
| Grants for [e-mail]@192.%                         |
+-------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO '[e-mail]'@'192.%' |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE DEFINER='[e-mail]'@'192.%' EVENT `test_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2020-07-16 12:54:46' ON COMPLETION PRESERVE ENABLE DO BEGIN INSERT INTO tt(d) SELECT NOW();
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER //
mysql> CREATE  DEFINER='[e-mail]'@'192.%' PROCEDURE test_sp()
    -> BEGIN
    ->  SELECT 1;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call test_sp();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> show create event test_event \G
*************************** 1. row ***************************
               Event: test_event
            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
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`ddtestdev`@`re-testcom.net@192.%` EVENT `test_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2020-07-16 12:54:46' ON COMPLETION PRESERVE ENABLE DO BEGIN INSERT INTO tt(d) SELECT NOW();
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

=========

2020-07-17T18:47:46.386002Z 4 [ERROR] Event Scheduler: [[e-mail]@192.%].[test.test_event] execution failed, failed to authenticate the user.
2020-07-17T18:47:46.386038Z 4 [ERROR] Event Scheduler: [[e-mail]@192.%][test.test_event] The user specified as a definer ('ddtestdev'@'re-testcom.net@192.%') does not exist
2020-07-17T18:47:46.386046Z 4 [Note] Event Scheduler: [[e-mail]@192.%].[test.test_event] event execution failed.