Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3(EOL)
-
None
Description
This problem was reported by Peter Gulutzan:
This script creates without problems a stored procedure consisting of a single FOR statement, but an attempt to execute it fails with an error:
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1() |
FOR i IN 1..10 DO |
set @x = 5; |
END FOR; |
$$
|
DELIMITER ;
|
CALL p1;
|
ERROR 1457 (HY000): Failed to load routine test.p1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
|
Querying mysql.proc tells that the body value is wrong for this procedure:
SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1'; |
+-------------------------------------------+
|
| body |
|
+-------------------------------------------+
|
| i IN 1..10 DO
|
set @x = 5;
|
END FOR |
|
+-------------------------------------------+
|
Notice, the keyword FOR is missing in the beginning.
The same problem is repeatable with a stored function consisting of a single FOR statement:
DELIMITER $$
|
CREATE OR REPLACE FUNCTION f1() RETURNS INT |
FOR i IN 1..10 DO |
RETURN 1; |
END FOR; |
$$
|
DELIMITER ;
|
SELECT f1(); |
ERROR 1457 (HY000): Failed to load routine test.f1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
|
SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1'; |
+---------------------------------------+
|
| body |
|
+---------------------------------------+
|
| i IN 1..10 DO
|
RETURN 1;
|
END FOR |
|
+---------------------------------------+
|
It should be fixed to preserve the FOR keyword in body.
Attachments
Issue Links
- relates to
-
MDEV-16020 SP variables inside GROUP BY..WITH ROLLUP break replication
-
- Closed
-
-
MDEV-16095 Oracle-style placeholder inside GROUP BY..WITH ROLLUP breaks replication
-
- Closed
-
The problem is repeatable with statements starting with any keyword that can be a part of keyword contraction in the function MYSQLlex() in sql_lex.cc:
...
...
...
}
This script demonstrates the same problem with WITH:
DELIMITER $$
$$
DELIMITER ;
CALL p1;
ERROR 1457 (HY000): Failed to load routine test.p1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+---------------------------+
| body |
+---------------------------+
| t1 AS (SELECT 1) SELECT 1 |
+---------------------------+
This script demonstrates the same problem with VALUES:
DELIMITER $$
$$
DELIMITER ;
CALL p1;
ERROR 1457 (HY000): Failed to load routine test.p1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+------+
| body |
+------+
| (1) |
+------+