Description:
1. MySQL 5.7 (and mostly all 5.x) allows creating routine with 1 empty parameter in the routine.
mysql> delimiter //
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `testproc`( IN `` INT)
-> BEGIN
->
-> SELECT 1;
-> END; //
Query OK, 0 rows affected (0.02 sec)
mysql> drop procedure testproc; //
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `testproc`( IN `` INT, IN `` INT)
-> BEGIN
->
-> SELECT 1;
-> END; //
ERROR 1330 (42000): Duplicate parameter:
mysql>
mysql> delimiter ;
mysql> select version();
+---------------+
| version() |
+---------------+
| 5.7.35-38-log |
+---------------+
1 row in set (0.00 sec)
mysql> select * from mysql.proc where name ='testproc'\G
*************************** 1. row ***************************
db: test
name: testproc
type: PROCEDURE
specific_name: testproc
language: SQL
sql_data_access: CONTAINS_SQL
is_deterministic: NO
security_type: DEFINER
param_list: OUT `` INT
returns:
body: BEGIN
SELECT 1;
END
definer: root@localhost
created: 2022-08-01 20:07:10
modified: 2022-08-01 20:07:10
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
comment:
character_set_client: utf8
collation_connection: utf8_general_ci
db_collation: latin1_swedish_ci
body_utf8: BEGIN
SELECT 1;
END
1 row in set (0.00 sec)
2. Eventhough this creation is allowed, this procedure won't work
mysql> call testproc();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE test.testproc; expected 1, got 0
mysql> call testproc(1);
ERROR 1414 (42000): OUT or INOUT argument 1 for routine test.testproc is not a variable or NEW pseudo-variable in BEFORE trigger
3. Upgrade won't work
In MySQL 8.0.28
- Upgrade utility doesn't identify this incompatible change
- Upgrade will fail for such procedures as it doesn't accept the empty parameter names... we're using information_schema.routines and information_schema.parameters tables.
util.checkForServerUpgrade
How to repeat:
Try creating following in 5.7 and 8.x.
```
delimiter //
CREATE DEFINER=`root`@`localhost` PROCEDURE `testproc`( IN `` INT)
BEGIN
SELECT 1;
END; //
```
Description: 1. MySQL 5.7 (and mostly all 5.x) allows creating routine with 1 empty parameter in the routine. mysql> delimiter // mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `testproc`( IN `` INT) -> BEGIN -> -> SELECT 1; -> END; // Query OK, 0 rows affected (0.02 sec) mysql> drop procedure testproc; // Query OK, 0 rows affected (0.01 sec) mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `testproc`( IN `` INT, IN `` INT) -> BEGIN -> -> SELECT 1; -> END; // ERROR 1330 (42000): Duplicate parameter: mysql> mysql> delimiter ; mysql> select version(); +---------------+ | version() | +---------------+ | 5.7.35-38-log | +---------------+ 1 row in set (0.00 sec) mysql> select * from mysql.proc where name ='testproc'\G *************************** 1. row *************************** db: test name: testproc type: PROCEDURE specific_name: testproc language: SQL sql_data_access: CONTAINS_SQL is_deterministic: NO security_type: DEFINER param_list: OUT `` INT returns: body: BEGIN SELECT 1; END definer: root@localhost created: 2022-08-01 20:07:10 modified: 2022-08-01 20:07:10 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 comment: character_set_client: utf8 collation_connection: utf8_general_ci db_collation: latin1_swedish_ci body_utf8: BEGIN SELECT 1; END 1 row in set (0.00 sec) 2. Eventhough this creation is allowed, this procedure won't work mysql> call testproc(); ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE test.testproc; expected 1, got 0 mysql> call testproc(1); ERROR 1414 (42000): OUT or INOUT argument 1 for routine test.testproc is not a variable or NEW pseudo-variable in BEFORE trigger 3. Upgrade won't work In MySQL 8.0.28 - Upgrade utility doesn't identify this incompatible change - Upgrade will fail for such procedures as it doesn't accept the empty parameter names... we're using information_schema.routines and information_schema.parameters tables. util.checkForServerUpgrade How to repeat: Try creating following in 5.7 and 8.x. ``` delimiter // CREATE DEFINER=`root`@`localhost` PROCEDURE `testproc`( IN `` INT) BEGIN SELECT 1; END; // ```