MDEV-23518 Syntax error in ond SP results in misleading message on SHOW CREATE PROCEDURE

Add info to the error message how to get details about error which happened.

8 files changed, 161 insertions, 15 deletions

diff --git a/mysql-test/main/errors.result b/mysql-test/main/errors.result
index 78695b07b8e..44193865493 100644
--- a/mysql-test/main/errors.result
+++ b/mysql-test/main/errors.result
@@ -196,3 +196,38 @@ ERROR 22003: BIGINT value is out of range in '-73 * -2465717823867977728'
 SELECT UPDATEXML(-73 * -2465717823867977728,@@global.long_query_time,null);
 ERROR 22003: BIGINT value is out of range in '-73 * -2465717823867977728'
 # End of 10.3 tests
+#
+# MDEV-23518: Syntax error in ond SP results in misleading
+# message on SHOW CREATE PROCEDURE
+#
+CREATE PROCEDURE P1 ()
+BEGIN NOT ATOMIC
+IF (SELECT 2) THEN
+SELECT 4;
+END IF ;
+END;
+$$
+select name,db,body from mysql.proc where name = "P1";
+name	db	body
+P1	test	BEGIN NOT ATOMIC
+IF (SELECT 2) THEN
+SELECT 4;
+END IF ;
+END
+update mysql.proc set body_utf8="BEGIN NOT ATOMIC
+IF (SELECT 2) OR foo = 3 THEN
+SELECT 4;
+END IF ;
+END", body="BEGIN NOT ATOMIC
+IF (SELECT 2) OR foo = 3 THEN
+SELECT 4;
+END IF ;
+END"where name = "P1";
+show create procedure P1;
+ERROR HY000: Failed to load routine test.P1 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Error	1327	Undeclared variable: foo
+Error	1457	Failed to load routine test.P1 (internal code -6). For more details, run SHOW WARNINGS
+drop procedure P1;
+# End of 10.4 tests
diff --git a/mysql-test/main/errors.test b/mysql-test/main/errors.test
index f3aedc8e491..a4b95d48e5d 100644
--- a/mysql-test/main/errors.test
+++ b/mysql-test/main/errors.test
@@ -246,3 +246,39 @@ SELECT UPDATEXML(-73 * -2465717823867977728,@@global.auto_increment_increment,nu
 SELECT UPDATEXML(-73 * -2465717823867977728,@@global.long_query_time,null);
 
 -- echo # End of 10.3 tests
+
+--echo #
+--echo # MDEV-23518: Syntax error in ond SP results in misleading
+--echo # message on SHOW CREATE PROCEDURE
+--echo #
+
+DELIMITER $$;
+
+CREATE PROCEDURE P1 ()
+BEGIN NOT ATOMIC
+  IF (SELECT 2) THEN
+    SELECT 4;
+  END IF ;
+END;
+$$
+
+DELIMITER ;$$
+
+select name,db,body from mysql.proc where name = "P1";
+update mysql.proc set body_utf8="BEGIN NOT ATOMIC
+IF (SELECT 2) OR foo = 3 THEN
+SELECT 4;
+END IF ;
+END", body="BEGIN NOT ATOMIC
+IF (SELECT 2) OR foo = 3 THEN
+SELECT 4;
+END IF ;
+END"where name = "P1";
+
+--error ER_SP_PROC_TABLE_CORRUPT
+show create procedure P1;
+show warnings;
+
+drop procedure P1;
+
+-- echo # End of 10.4 tests
diff --git a/mysql-test/main/sp-destruct.result b/mysql-test/main/sp-destruct.result
index 10cfcafdb3b..b5e01f8beeb 100644
--- a/mysql-test/main/sp-destruct.result
+++ b/mysql-test/main/sp-destruct.result
@@ -71,19 +71,49 @@ values
 'alksj wpsj sa ^#!@ '
 );
 select bug14233_1();
-ERROR HY000: Failed to load routine test.bug14233_1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.bug14233_1 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Warning	1601	Creation context of stored routine `test`.`bug14233_1` is invalid
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select count(*) from mysql.user' at line 3
+Error	1457	Failed to load routine test.bug14233_1 (internal code -6). For more details, run SHOW WARNINGS
 create view v1 as select bug14233_1();
-ERROR HY000: Failed to load routine test.bug14233_1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.bug14233_1 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Warning	1601	Creation context of stored routine `test`.`bug14233_1` is invalid
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select count(*) from mysql.user' at line 3
+Error	1457	Failed to load routine test.bug14233_1 (internal code -6). For more details, run SHOW WARNINGS
 select bug14233_2();
-ERROR HY000: Failed to load routine test.bug14233_2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.bug14233_2 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Warning	1601	Creation context of stored routine `test`.`bug14233_2` is invalid
+Error	1320	No RETURN found in FUNCTION test.bug14233_2
+Error	1457	Failed to load routine test.bug14233_2 (internal code -6). For more details, run SHOW WARNINGS
 create view v1 as select bug14233_2();
-ERROR HY000: Failed to load routine test.bug14233_2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.bug14233_2 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Warning	1601	Creation context of stored routine `test`.`bug14233_2` is invalid
+Error	1320	No RETURN found in FUNCTION test.bug14233_2
+Error	1457	Failed to load routine test.bug14233_2 (internal code -6). For more details, run SHOW WARNINGS
 call bug14233_3();
-ERROR HY000: Failed to load routine test.bug14233_3. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.bug14233_3 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Warning	1601	Creation context of stored routine `test`.`bug14233_3` is invalid
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'wpsj sa ^#!@ ' at line 3
+Error	1457	Failed to load routine test.bug14233_3 (internal code -6). For more details, run SHOW WARNINGS
 drop trigger t1_ai;
 create trigger t1_ai after insert on t1 for each row call bug14233_3();
 insert into t1 values (0);
-ERROR HY000: Failed to load routine test.bug14233_3. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.bug14233_3 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Warning	1601	Creation context of stored routine `test`.`bug14233_3` is invalid
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'wpsj sa ^#!@ ' at line 3
+Error	1457	Failed to load routine test.bug14233_3 (internal code -6). For more details, run SHOW WARNINGS
 drop trigger t1_ai;
 drop table t1;
 drop function bug14233_1;
diff --git a/mysql-test/main/sp-destruct.test b/mysql-test/main/sp-destruct.test
index 12299fa230b..4bdfed70545 100644
--- a/mysql-test/main/sp-destruct.test
+++ b/mysql-test/main/sp-destruct.test
@@ -128,20 +128,26 @@ values
 
 --error ER_SP_PROC_TABLE_CORRUPT
 select bug14233_1();
+show warnings;
 --error ER_SP_PROC_TABLE_CORRUPT
 create view v1 as select bug14233_1();
+show warnings;
 
 --error ER_SP_PROC_TABLE_CORRUPT
 select bug14233_2();
+show warnings;
 --error ER_SP_PROC_TABLE_CORRUPT
 create view v1 as select bug14233_2();
+show warnings;
 
 --error ER_SP_PROC_TABLE_CORRUPT
 call bug14233_3();
+show warnings;
 drop trigger t1_ai;
 create trigger t1_ai after insert on t1 for each row call bug14233_3();
 --error ER_SP_PROC_TABLE_CORRUPT
 insert into t1 values (0);
+show warnings;
 
 # Clean-up
 drop trigger t1_ai;
diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result
index d40516da88e..6bde488642a 100644
--- a/mysql-test/main/sp.result
+++ b/mysql-test/main/sp.result
@@ -5306,8 +5306,12 @@ drop database if exists това_е_дълго_име_за_база_данни_�
 create database това_е_дълго_име_за_база_данни_нали|
 INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','', 'utf8', 'utf8_general_ci', 'utf8_general_ci', 'n/a', 'NONE')|
 call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()|
-ERROR HY000: Failed to load routine това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго (internal code -6). For more details, run SHOW WARNINGS
 drop database това_е_дълго_име_за_база_данни_нали|
+show warnings|
+Level	Code	Message
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2
+Error	1457	Failed to load routine това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго (internal code -6). For more details, run SHOW WARNINGS
 CREATE TABLE t3 (
 Member_ID varchar(15) NOT NULL,
 PRIMARY KEY (Member_ID)
diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test
index bddd07bdb6c..c6c00ca8d91 100644
--- a/mysql-test/main/sp.test
+++ b/mysql-test/main/sp.test
@@ -6315,7 +6315,7 @@ INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_да
 --error ER_SP_PROC_TABLE_CORRUPT
 call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()|
 drop database това_е_дълго_име_за_база_данни_нали|
-
+show warnings|
 
 #
 # BUG#21493: Crash on the second call of a procedure containing
diff --git a/mysql-test/suite/compat/oracle/r/sp-package.result b/mysql-test/suite/compat/oracle/r/sp-package.result
index 4f0f05b1939..e7ab4eaa4a2 100644
--- a/mysql-test/suite/compat/oracle/r/sp-package.result
+++ b/mysql-test/suite/compat/oracle/r/sp-package.result
@@ -249,7 +249,11 @@ RETURN f2();
 END;
 END;
 $$
-ERROR HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'garbage' at line 1
+Error	1457	Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
 DROP PACKAGE test2;
 #
 # Broken CREATE PACKAGE at a package function call time
@@ -271,11 +275,23 @@ UPDATE mysql.proc SET `body`='garbage'
   WHERE db='test' AND name='test2' AND type='PACKAGE';
 # sp-cache-invalidate
 SELECT test2.f1();
-ERROR HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'garbage' at line 1
+Error	1457	Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
 SELECT test2.f1();
-ERROR HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'garbage' at line 1
+Error	1457	Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
 SELECT test2.f1();
-ERROR HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'garbage' at line 1
+Error	1457	Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
 DROP PACKAGE test2;
 #
 # Broken CREATE PACKAGE at a package procedure call time
@@ -297,11 +313,23 @@ UPDATE mysql.proc SET `body`='garbage'
   WHERE db='test' AND name='test2' AND type='PACKAGE';
 # sp-cache-invalidate
 CALL test2.p1();
-ERROR HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'garbage' at line 1
+Error	1457	Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
 CALL test2.p1();
-ERROR HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'garbage' at line 1
+Error	1457	Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
 CALL test2.p1();
-ERROR HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
+ERROR HY000: Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
+show warnings;
+Level	Code	Message
+Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'garbage' at line 1
+Error	1457	Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
 DROP PACKAGE test2;
 #
 # Bad routine names
diff --git a/mysql-test/suite/compat/oracle/t/sp-package.test b/mysql-test/suite/compat/oracle/t/sp-package.test
index 5eac3987982..96420c18820 100644
--- a/mysql-test/suite/compat/oracle/t/sp-package.test
+++ b/mysql-test/suite/compat/oracle/t/sp-package.test
@@ -335,6 +335,7 @@ CREATE PACKAGE BODY test2 AS
 END;
 $$
 DELIMITER ;$$
+show warnings;
 
 DROP PACKAGE test2;
 
@@ -367,10 +368,13 @@ UPDATE mysql.proc SET `body`='garbage'
 --source sp-cache-invalidate.inc
 --error ER_SP_PROC_TABLE_CORRUPT
 SELECT test2.f1();
+show warnings;
 --error ER_SP_PROC_TABLE_CORRUPT
 SELECT test2.f1();
+show warnings;
 --error ER_SP_PROC_TABLE_CORRUPT
 SELECT test2.f1();
+show warnings;
 
 DROP PACKAGE test2;
 
@@ -403,10 +407,13 @@ UPDATE mysql.proc SET `body`='garbage'
 --source sp-cache-invalidate.inc
 --error ER_SP_PROC_TABLE_CORRUPT
 CALL test2.p1();
+show warnings;
 --error ER_SP_PROC_TABLE_CORRUPT
 CALL test2.p1();
+show warnings;
 --error ER_SP_PROC_TABLE_CORRUPT
 CALL test2.p1();
+show warnings;
 
 DROP PACKAGE test2;