diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/signal.result | |
parent | Initial commit. (diff) | |
download | mariadb-upstream.tar.xz mariadb-upstream.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/signal.result')
-rw-r--r-- | mysql-test/main/signal.result | 2386 |
1 files changed, 2386 insertions, 0 deletions
diff --git a/mysql-test/main/signal.result b/mysql-test/main/signal.result new file mode 100644 index 00000000..6a7f1c1e --- /dev/null +++ b/mysql-test/main/signal.result @@ -0,0 +1,2386 @@ +# +# PART 1: syntax +# +# +# Test every new reserved and non reserved keywords +# +drop table if exists signal_non_reserved; +create table signal_non_reserved ( +class_origin int, +subclass_origin int, +constraint_catalog int, +constraint_schema int, +constraint_name int, +catalog_name int, +schema_name int, +table_name int, +column_name int, +cursor_name int, +message_text int, +sqlcode int +); +drop table signal_non_reserved; +drop table if exists diag_non_reserved; +create table diag_non_reserved ( +diagnostics int, +current int, +stacked int, +exception int +); +drop table diag_non_reserved; +drop table if exists diag_cond_non_reserved; +create table diag_cond_non_reserved ( +condition_identifier int, +condition_number int, +condition_name int, +connection_name int, +message_length int, +message_octet_length int, +parameter_mode int, +parameter_name int, +parameter_ordinal_position int, +returned_sqlstate int, +routine_catalog int, +routine_name int, +routine_schema int, +server_name int, +specific_name int, +trigger_catalog int, +trigger_name int, +trigger_schema int +); +drop table diag_cond_non_reserved; +drop table if exists diag_stmt_non_reserved; +create table diag_stmt_non_reserved ( +number int, +more int, +command_function int, +command_function_code int, +dynamic_function int, +dynamic_function_code int, +row_count int, +transactions_committed int, +transactions_rolled_back int, +transaction_active int +); +drop table diag_stmt_non_reserved; +drop table if exists test_reserved; +create table test_reserved (signal int); +ERROR 42000: 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 'signal int)' at line 1 +create table test_reserved (resignal int); +ERROR 42000: 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 'resignal int)' at line 1 +create table test_reserved (condition int); +ERROR 42000: 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 'condition int)' at line 1 +# +# Test the SIGNAL syntax +# +drop procedure if exists test_invalid; +drop procedure if exists test_signal_syntax; +drop function if exists test_signal_func; +create procedure test_invalid() +begin +SIGNAL; +end $$ +ERROR 42000: 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 '; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL foo; +end $$ +ERROR 42000: Undefined CONDITION: foo +create procedure test_invalid() +begin +DECLARE foo CONDITION FOR 1234; +SIGNAL foo; +end $$ +ERROR HY000: SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +SIGNAL SQLSTATE '23000'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +SIGNAL SQLSTATE VALUE '23000'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET CLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET SUBCLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET CONSTRAINT_CATALOG = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET CONSTRAINT_SCHEMA = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET CONSTRAINT_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET CATALOG_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET SCHEMA_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET TABLE_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET COLUMN_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET CURSOR_NAME = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MESSAGE_TEXT = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +create procedure test_invalid() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET CLASS_ORIGIN = 'foo', CLASS_ORIGIN = 'bar'; +end $$ +ERROR 42000: Duplicate condition information item 'CLASS_ORIGIN' +create procedure test_invalid() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MESSAGE_TEXT = 'foo', MESSAGE_TEXT = 'bar'; +end $$ +ERROR 42000: Duplicate condition information item 'MESSAGE_TEXT' +create procedure test_invalid() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 'foo', MYSQL_ERRNO = 'bar'; +end $$ +ERROR 42000: Duplicate condition information item 'MYSQL_ERRNO' +create procedure test_signal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET +CLASS_ORIGIN = 'foo', +SUBCLASS_ORIGIN = 'foo', +CONSTRAINT_CATALOG = 'foo', +CONSTRAINT_SCHEMA = 'foo', +CONSTRAINT_NAME = 'foo', +CATALOG_NAME = 'foo', +SCHEMA_NAME = 'foo', +TABLE_NAME = 'foo', +COLUMN_NAME = 'foo', +CURSOR_NAME = 'foo', +MESSAGE_TEXT = 'foo', +MYSQL_ERRNO = 'foo'; +end $$ +drop procedure test_signal_syntax $$ +SIGNAL SQLSTATE '00000' $$ +ERROR 42000: Bad SQLSTATE: '00000' +SIGNAL SQLSTATE '00001' $$ +ERROR 42000: Bad SQLSTATE: '00001' +create procedure test_invalid() +begin +SIGNAL SQLSTATE '00000'; +end $$ +ERROR 42000: Bad SQLSTATE: '00000' +create procedure test_invalid() +begin +SIGNAL SQLSTATE '00001'; +end $$ +ERROR 42000: Bad SQLSTATE: '00001' +# +# Test conditions information that SIGNAL can not set +# +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET bla_bla = 'foo'; +end $$ +ERROR 42000: 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 'bla_bla = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET CONDITION_IDENTIFIER = 'foo'; +end $$ +ERROR 42000: 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 'CONDITION_IDENTIFIER = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET CONDITION_NUMBER = 'foo'; +end $$ +ERROR 42000: 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 'CONDITION_NUMBER = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET CONNECTION_NAME = 'foo'; +end $$ +ERROR 42000: 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 'CONNECTION_NAME = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET MESSAGE_LENGTH = 'foo'; +end $$ +ERROR 42000: 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 'MESSAGE_LENGTH = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET MESSAGE_OCTET_LENGTH = 'foo'; +end $$ +ERROR 42000: 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 'MESSAGE_OCTET_LENGTH = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET PARAMETER_MODE = 'foo'; +end $$ +ERROR 42000: 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 'PARAMETER_MODE = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET PARAMETER_NAME = 'foo'; +end $$ +ERROR 42000: 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 'PARAMETER_NAME = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET PARAMETER_ORDINAL_POSITION = 'foo'; +end $$ +ERROR 42000: 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 'PARAMETER_ORDINAL_POSITION = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET RETURNED_SQLSTATE = 'foo'; +end $$ +ERROR 42000: 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 'RETURNED_SQLSTATE = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET ROUTINE_CATALOG = 'foo'; +end $$ +ERROR 42000: 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 'ROUTINE_CATALOG = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET ROUTINE_NAME = 'foo'; +end $$ +ERROR 42000: 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 'ROUTINE_NAME = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET ROUTINE_SCHEMA = 'foo'; +end $$ +ERROR 42000: 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 'ROUTINE_SCHEMA = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET SERVER_NAME = 'foo'; +end $$ +ERROR 42000: 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 'SERVER_NAME = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET SPECIFIC_NAME = 'foo'; +end $$ +ERROR 42000: 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 'SPECIFIC_NAME = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET TRIGGER_CATALOG = 'foo'; +end $$ +ERROR 42000: 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 'TRIGGER_CATALOG = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET TRIGGER_NAME = 'foo'; +end $$ +ERROR 42000: 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 'TRIGGER_NAME = 'foo'; +end' at line 3 +create procedure test_invalid() +begin +SIGNAL SQLSTATE '12345' SET TRIGGER_SCHEMA = 'foo'; +end $$ +ERROR 42000: 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 'TRIGGER_SCHEMA = 'foo'; +end' at line 3 +# +# Test the RESIGNAL syntax +# +drop procedure if exists test_invalid; +drop procedure if exists test_resignal_syntax; +create procedure test_invalid() +begin +RESIGNAL foo; +end $$ +ERROR 42000: Undefined CONDITION: foo +create procedure test_resignal_syntax() +begin +RESIGNAL; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_invalid() +begin +DECLARE foo CONDITION FOR 1234; +RESIGNAL foo; +end $$ +ERROR HY000: SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SQLSTATE '23000'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SQLSTATE VALUE '23000'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET CLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET CLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET SUBCLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET SUBCLASS_ORIGIN = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET CONSTRAINT_CATALOG = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET CONSTRAINT_CATALOG = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET CONSTRAINT_SCHEMA = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET CONSTRAINT_SCHEMA = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET CONSTRAINT_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET CONSTRAINT_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET CATALOG_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET CATALOG_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET SCHEMA_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET SCHEMA_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET TABLE_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET TABLE_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET COLUMN_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET COLUMN_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET CURSOR_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET CURSOR_NAME = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET MESSAGE_TEXT = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET MESSAGE_TEXT = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +RESIGNAL SET MYSQL_ERRNO = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET MYSQL_ERRNO = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_invalid() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET CLASS_ORIGIN = 'foo', CLASS_ORIGIN = 'bar'; +end $$ +ERROR 42000: Duplicate condition information item 'CLASS_ORIGIN' +create procedure test_invalid() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET MESSAGE_TEXT = 'foo', MESSAGE_TEXT = 'bar'; +end $$ +ERROR 42000: Duplicate condition information item 'MESSAGE_TEXT' +create procedure test_invalid() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET MYSQL_ERRNO = 'foo', MYSQL_ERRNO = 'bar'; +end $$ +ERROR 42000: Duplicate condition information item 'MYSQL_ERRNO' +create procedure test_resignal_syntax() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +RESIGNAL foo SET +CLASS_ORIGIN = 'foo', +SUBCLASS_ORIGIN = 'foo', +CONSTRAINT_CATALOG = 'foo', +CONSTRAINT_SCHEMA = 'foo', +CONSTRAINT_NAME = 'foo', +CATALOG_NAME = 'foo', +SCHEMA_NAME = 'foo', +TABLE_NAME = 'foo', +COLUMN_NAME = 'foo', +CURSOR_NAME = 'foo', +MESSAGE_TEXT = 'foo'; +end $$ +drop procedure test_resignal_syntax $$ +create procedure test_invalid() +begin +RESIGNAL SQLSTATE '00000'; +end $$ +ERROR 42000: Bad SQLSTATE: '00000' +create procedure test_invalid() +begin +RESIGNAL SQLSTATE '00001'; +end $$ +ERROR 42000: Bad SQLSTATE: '00001' +# +# PART 2: non preparable statements +# +prepare stmt from 'SIGNAL SQLSTATE \'23000\''; +prepare stmt from 'RESIGNAL SQLSTATE \'23000\''; +# +# PART 3: runtime execution +# +drop procedure if exists test_signal; +drop procedure if exists test_resignal; +drop table if exists t_warn; +drop table if exists t_cursor; +create table t_warn(a integer(2)); +create table t_cursor(a integer); +# +# SIGNAL can also appear in a query +# +SIGNAL foo; +ERROR 42000: Undefined CONDITION: foo +SIGNAL SQLSTATE '01000'; +Warnings: +Warning 1642 Unhandled user-defined warning condition +SIGNAL SQLSTATE '02000'; +ERROR 02000: Unhandled user-defined not found condition +SIGNAL SQLSTATE '23000'; +ERROR 23000: Unhandled user-defined exception condition +SIGNAL SQLSTATE VALUE '23000'; +ERROR 23000: Unhandled user-defined exception condition +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 65536; +ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '65536' +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 99999; +ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '99999' +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 4294967295; +ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '4294967295' +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 0; +ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '0' +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = -1; +ERROR 42000: 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 '-1' at line 1 +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 65535; +ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '65535' +SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 65534; +ERROR HY000: Unhandled user-defined exception condition +# +# RESIGNAL can also appear in a query +# +RESIGNAL; +ERROR 0K000: RESIGNAL when handler not active +RESIGNAL foo; +ERROR 42000: Undefined CONDITION: foo +RESIGNAL SQLSTATE '12345'; +ERROR 0K000: RESIGNAL when handler not active +RESIGNAL SQLSTATE VALUE '12345'; +ERROR 0K000: RESIGNAL when handler not active +# +# Different kind of SIGNAL conditions +# +create procedure test_signal() +begin +# max range +DECLARE foo CONDITION FOR SQLSTATE 'AABBB'; +SIGNAL foo SET MYSQL_ERRNO = 65534; +end $$ +call test_signal() $$ +ERROR AABBB: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# max range +DECLARE foo CONDITION FOR SQLSTATE 'AABBB'; +SIGNAL foo SET MYSQL_ERRNO = 65536; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '65536' +drop procedure test_signal $$ +create procedure test_signal() +begin +# Error +DECLARE foo CONDITION FOR SQLSTATE '99999'; +SIGNAL foo SET MYSQL_ERRNO = 9999; +end $$ +call test_signal() $$ +ERROR 99999: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# warning +DECLARE too_few_records CONDITION FOR SQLSTATE '01000'; +SIGNAL too_few_records SET MYSQL_ERRNO = 1261; +end $$ +call test_signal() $$ +Warnings: +Warning 1261 Unhandled user-defined warning condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# Not found +DECLARE sp_fetch_no_data CONDITION FOR SQLSTATE '02000'; +SIGNAL sp_fetch_no_data SET MYSQL_ERRNO = 1329; +end $$ +call test_signal() $$ +ERROR 02000: Unhandled user-defined not found condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# Error +DECLARE sp_cursor_already_open CONDITION FOR SQLSTATE '24000'; +SIGNAL sp_cursor_already_open SET MYSQL_ERRNO = 1325; +end $$ +call test_signal() $$ +ERROR 24000: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# Severe error +DECLARE lock_deadlock CONDITION FOR SQLSTATE '40001'; +SIGNAL lock_deadlock SET MYSQL_ERRNO = 1213; +end $$ +call test_signal() $$ +ERROR 40001: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# Unknown -> error +DECLARE foo CONDITION FOR SQLSTATE "99999"; +SIGNAL foo; +end $$ +call test_signal() $$ +ERROR 99999: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# warning, no subclass +DECLARE warn CONDITION FOR SQLSTATE "01000"; +SIGNAL warn; +end $$ +call test_signal() $$ +Warnings: +Warning 1642 Unhandled user-defined warning condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# warning, with subclass +DECLARE warn CONDITION FOR SQLSTATE "01123"; +SIGNAL warn; +end $$ +call test_signal() $$ +Warnings: +Warning 1642 Unhandled user-defined warning condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# Not found, no subclass +DECLARE not_found CONDITION FOR SQLSTATE "02000"; +SIGNAL not_found; +end $$ +call test_signal() $$ +ERROR 02000: Unhandled user-defined not found condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# Not found, with subclass +DECLARE not_found CONDITION FOR SQLSTATE "02XXX"; +SIGNAL not_found; +end $$ +call test_signal() $$ +ERROR 02XXX: Unhandled user-defined not found condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# Error, no subclass +DECLARE error CONDITION FOR SQLSTATE "12000"; +SIGNAL error; +end $$ +call test_signal() $$ +ERROR 12000: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# Error, with subclass +DECLARE error CONDITION FOR SQLSTATE "12ABC"; +SIGNAL error; +end $$ +call test_signal() $$ +ERROR 12ABC: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# Severe error, no subclass +DECLARE error CONDITION FOR SQLSTATE "40000"; +SIGNAL error; +end $$ +call test_signal() $$ +ERROR 40000: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +# Severe error, with subclass +DECLARE error CONDITION FOR SQLSTATE "40001"; +SIGNAL error; +end $$ +call test_signal() $$ +ERROR 40001: Unhandled user-defined exception condition +drop procedure test_signal $$ +# +# Test the scope of condition +# +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '99999'; +begin +DECLARE foo CONDITION FOR 8888; +end; +SIGNAL foo SET MYSQL_ERRNO=9999; /* outer */ +end $$ +call test_signal() $$ +ERROR 99999: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR 9999; +begin +DECLARE foo CONDITION FOR SQLSTATE '88888'; +SIGNAL foo SET MYSQL_ERRNO=8888; /* inner */ +end; +end $$ +call test_signal() $$ +ERROR 88888: Unhandled user-defined exception condition +drop procedure test_signal $$ +# +# Test SET MYSQL_ERRNO +# +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '99999'; +SIGNAL foo SET MYSQL_ERRNO = 1111; +end $$ +call test_signal() $$ +ERROR 99999: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01000"; +SIGNAL warn SET MYSQL_ERRNO = 1111; +end $$ +call test_signal() $$ +Warnings: +Warning 1111 Unhandled user-defined warning condition +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE not_found CONDITION FOR SQLSTATE "02000"; +SIGNAL not_found SET MYSQL_ERRNO = 1111; +end $$ +call test_signal() $$ +ERROR 02000: Unhandled user-defined not found condition +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE "55000"; +SIGNAL error SET MYSQL_ERRNO = 1111; +end $$ +call test_signal() $$ +ERROR 55000: Unhandled user-defined exception condition +drop procedure test_signal $$ +# +# Test SET MESSAGE_TEXT +# +SIGNAL SQLSTATE '77777' SET MESSAGE_TEXT='' $$ +ERROR 77777: +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '77777'; +SIGNAL foo SET +MESSAGE_TEXT = "", +MYSQL_ERRNO=5678; +end $$ +call test_signal() $$ +ERROR 77777: +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '99999'; +SIGNAL foo SET +MESSAGE_TEXT = "Something bad happened", +MYSQL_ERRNO=9999; +end $$ +call test_signal() $$ +ERROR 99999: Something bad happened +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01000"; +SIGNAL warn SET MESSAGE_TEXT = "Something bad happened"; +end $$ +call test_signal() $$ +Warnings: +Warning 1642 Something bad happened +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE not_found CONDITION FOR SQLSTATE "02000"; +SIGNAL not_found SET MESSAGE_TEXT = "Something bad happened"; +end $$ +call test_signal() $$ +ERROR 02000: Something bad happened +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE "55000"; +SIGNAL error SET MESSAGE_TEXT = "Something bad happened"; +end $$ +call test_signal() $$ +ERROR 55000: Something bad happened +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE something CONDITION FOR SQLSTATE "01000"; +SIGNAL something SET MESSAGE_TEXT = _utf8 "This is a UTF8 text"; +end $$ +call test_signal() $$ +Warnings: +Warning 1642 This is a UTF8 text +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE something CONDITION FOR SQLSTATE "01000"; +SIGNAL something SET MESSAGE_TEXT = ""; +end $$ +call test_signal() $$ +Warnings: +Warning 1642 +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01111"; +SIGNAL warn SET MESSAGE_TEXT = "á a"; +end $$ +call test_signal() $$ +Warnings: +Warning 1642 á a +show warnings $$ +Level Code Message +Warning 1642 á a +drop procedure test_signal $$ +# +# Test SET complex expressions +# +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +MYSQL_ERRNO = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +CLASS_ORIGIN = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'CLASS_ORIGIN' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +SUBCLASS_ORIGIN = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'SUBCLASS_ORIGIN' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +CONSTRAINT_CATALOG = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'CONSTRAINT_CATALOG' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +CONSTRAINT_SCHEMA = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'CONSTRAINT_SCHEMA' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +CONSTRAINT_NAME = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'CONSTRAINT_NAME' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +CATALOG_NAME = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'CATALOG_NAME' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +SCHEMA_NAME = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'SCHEMA_NAME' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +TABLE_NAME = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'TABLE_NAME' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +COLUMN_NAME = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'COLUMN_NAME' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +CURSOR_NAME = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'CURSOR_NAME' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE '99999'; +SIGNAL error SET +MESSAGE_TEXT = NULL; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE something CONDITION FOR SQLSTATE '99999'; +DECLARE message_text VARCHAR(64) DEFAULT "Local string variable"; +DECLARE sqlcode INTEGER DEFAULT 1234; +SIGNAL something SET +MESSAGE_TEXT = message_text, +MYSQL_ERRNO = sqlcode; +end $$ +call test_signal() $$ +ERROR 99999: Local string variable +drop procedure test_signal $$ +create procedure test_signal(message_text VARCHAR(64), sqlcode INTEGER) +begin +DECLARE something CONDITION FOR SQLSTATE "12345"; +SIGNAL something SET +MESSAGE_TEXT = message_text, +MYSQL_ERRNO = sqlcode; +end $$ +call test_signal("Parameter string", NULL) $$ +ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of 'NULL' +call test_signal(NULL, 1234) $$ +ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL' +call test_signal("Parameter string", 5678) $$ +ERROR 12345: Parameter string +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE something CONDITION FOR SQLSTATE "AABBB"; +SIGNAL something SET +MESSAGE_TEXT = @message_text, +MYSQL_ERRNO = @sqlcode; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL' +set @sqlcode= 12 $$ +call test_signal() $$ +ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL' +set @message_text= "User variable" $$ +call test_signal() $$ +ERROR AABBB: User variable +drop procedure test_signal $$ +create procedure test_invalid() +begin +DECLARE something CONDITION FOR SQLSTATE "AABBB"; +SIGNAL something SET +MESSAGE_TEXT = @message_text := 'illegal', +MYSQL_ERRNO = @sqlcode := 1234; +end $$ +ERROR 42000: 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 ' +MYSQL_ERRNO = @sqlcode := 1234; +end' at line 5 +create procedure test_signal() +begin +DECLARE aaa VARCHAR(64); +DECLARE bbb VARCHAR(64); +DECLARE ccc VARCHAR(64); +DECLARE ddd VARCHAR(64); +DECLARE eee VARCHAR(64); +DECLARE fff VARCHAR(64); +DECLARE ggg VARCHAR(64); +DECLARE hhh VARCHAR(64); +DECLARE iii VARCHAR(64); +DECLARE jjj VARCHAR(64); +DECLARE kkk VARCHAR(64); +DECLARE warn CONDITION FOR SQLSTATE "01234"; +set aaa= repeat("A", 64); +set bbb= repeat("B", 64); +set ccc= repeat("C", 64); +set ddd= repeat("D", 64); +set eee= repeat("E", 64); +set fff= repeat("F", 64); +set ggg= repeat("G", 64); +set hhh= repeat("H", 64); +set iii= repeat("I", 64); +set jjj= repeat("J", 64); +set kkk= repeat("K", 64); +SIGNAL warn SET +CLASS_ORIGIN = aaa, +SUBCLASS_ORIGIN = bbb, +CONSTRAINT_CATALOG = ccc, +CONSTRAINT_SCHEMA = ddd, +CONSTRAINT_NAME = eee, +CATALOG_NAME = fff, +SCHEMA_NAME = ggg, +TABLE_NAME = hhh, +COLUMN_NAME = iii, +CURSOR_NAME = jjj, +MESSAGE_TEXT = kkk, +MYSQL_ERRNO = 65534; +end $$ +call test_signal() $$ +Warnings: +Warning 65534 KKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01234"; +SIGNAL warn SET +MYSQL_ERRNO = 999999999999999999999999999999999999999999999999999; +end $$ +call test_signal() $$ +ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '999999999999999999999999999999999999999999999999999' +drop procedure test_signal $$ +SET sql_mode= '' $$ +create procedure test_signal() +begin +DECLARE aaax VARCHAR(65); +DECLARE bbbx VARCHAR(65); +DECLARE cccx VARCHAR(65); +DECLARE dddx VARCHAR(65); +DECLARE eeex VARCHAR(65); +DECLARE fffx VARCHAR(65); +DECLARE gggx VARCHAR(65); +DECLARE hhhx VARCHAR(65); +DECLARE iiix VARCHAR(65); +DECLARE jjjx VARCHAR(65); +DECLARE kkkx VARCHAR(65); +DECLARE lllx VARCHAR(513); +DECLARE warn CONDITION FOR SQLSTATE "01234"; +set aaax= concat(repeat("A", 64), "X"); +set bbbx= concat(repeat("B", 64), "X"); +set cccx= concat(repeat("C", 64), "X"); +set dddx= concat(repeat("D", 64), "X"); +set eeex= concat(repeat("E", 64), "X"); +set fffx= concat(repeat("F", 64), "X"); +set gggx= concat(repeat("G", 64), "X"); +set hhhx= concat(repeat("H", 64), "X"); +set iiix= concat(repeat("I", 64), "X"); +set jjjx= concat(repeat("J", 64), "X"); +set kkkx= concat(repeat("K", 64), "X"); +set lllx= concat(repeat("1", 500), +repeat("2", 10), +repeat("8", 2), +"X"); +SIGNAL warn SET +CLASS_ORIGIN = aaax, +SUBCLASS_ORIGIN = bbbx, +CONSTRAINT_CATALOG = cccx, +CONSTRAINT_SCHEMA = dddx, +CONSTRAINT_NAME = eeex, +CATALOG_NAME = fffx, +SCHEMA_NAME = gggx, +TABLE_NAME = hhhx, +COLUMN_NAME = iiix, +CURSOR_NAME = jjjx, +MESSAGE_TEXT = lllx, +MYSQL_ERRNO = 10000; +end $$ +call test_signal() $$ +Warnings: +Warning 1647 Data truncated for condition item 'CLASS_ORIGIN' +Warning 1647 Data truncated for condition item 'SUBCLASS_ORIGIN' +Warning 1647 Data truncated for condition item 'CONSTRAINT_CATALOG' +Warning 1647 Data truncated for condition item 'CONSTRAINT_SCHEMA' +Warning 1647 Data truncated for condition item 'CONSTRAINT_NAME' +Warning 1647 Data truncated for condition item 'CATALOG_NAME' +Warning 1647 Data truncated for condition item 'SCHEMA_NAME' +Warning 1647 Data truncated for condition item 'TABLE_NAME' +Warning 1647 Data truncated for condition item 'COLUMN_NAME' +Warning 1647 Data truncated for condition item 'CURSOR_NAME' +Warning 1647 Data truncated for condition item 'MESSAGE_TEXT' +Warning 10000 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111222222222288 +drop procedure test_signal $$ +set sql_mode = DEFAULT $$ +create procedure test_signal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01234"; +DECLARE CONTINUE HANDLER for SQLSTATE "01234" + begin +select "Caught by SQLSTATE"; +end; +SIGNAL warn SET +MESSAGE_TEXT = "Raising a warning", +MYSQL_ERRNO = 1012; +end $$ +call test_signal() $$ +Caught by SQLSTATE +Caught by SQLSTATE +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01234"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "Caught by number"; +end; +SIGNAL warn SET +MESSAGE_TEXT = "Raising a warning", +MYSQL_ERRNO = 1012; +end $$ +call test_signal() $$ +Caught by number +Caught by number +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01234"; +DECLARE CONTINUE HANDLER for SQLWARNING +begin +select "Caught by SQLWARNING"; +end; +SIGNAL warn SET +MESSAGE_TEXT = "Raising a warning", +MYSQL_ERRNO = 1012; +end $$ +call test_signal() $$ +Caught by SQLWARNING +Caught by SQLWARNING +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; +DECLARE CONTINUE HANDLER for SQLSTATE "02ABC" + begin +select "Caught by SQLSTATE"; +end; +SIGNAL not_found SET +MESSAGE_TEXT = "Raising a not found", +MYSQL_ERRNO = 1012; +end $$ +call test_signal() $$ +Caught by SQLSTATE +Caught by SQLSTATE +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "Caught by number"; +end; +SIGNAL not_found SET +MESSAGE_TEXT = "Raising a not found", +MYSQL_ERRNO = 1012; +end $$ +call test_signal() $$ +Caught by number +Caught by number +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; +DECLARE CONTINUE HANDLER for NOT FOUND +begin +select "Caught by NOT FOUND"; +end; +SIGNAL not_found SET +MESSAGE_TEXT = "Raising a not found", +MYSQL_ERRNO = 1012; +end $$ +call test_signal() $$ +Caught by NOT FOUND +Caught by NOT FOUND +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE "55555"; +DECLARE CONTINUE HANDLER for SQLSTATE "55555" + begin +select "Caught by SQLSTATE"; +end; +SIGNAL error SET +MESSAGE_TEXT = "Raising an error", +MYSQL_ERRNO = 1012; +end $$ +call test_signal() $$ +Caught by SQLSTATE +Caught by SQLSTATE +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE "55555"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "Caught by number"; +end; +SIGNAL error SET +MESSAGE_TEXT = "Raising an error", +MYSQL_ERRNO = 1012; +end $$ +call test_signal() $$ +Caught by number +Caught by number +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE error CONDITION FOR SQLSTATE "55555"; +DECLARE CONTINUE HANDLER for SQLEXCEPTION +begin +select "Caught by SQLEXCEPTION"; +end; +SIGNAL error SET +MESSAGE_TEXT = "Raising an error", +MYSQL_ERRNO = 1012; +end $$ +call test_signal() $$ +Caught by SQLEXCEPTION +Caught by SQLEXCEPTION +drop procedure test_signal $$ +# +# Test where SIGNAL can be used +# + +# RETURN statement clears Diagnostics Area, thus +# the warnings raised in a stored function are not +# visible outsidef the stored function. So, we're using +# @@warning_count variable to check that SIGNAL succeeded. + +create function test_signal_func() returns integer +begin +DECLARE v INT; +DECLARE warn CONDITION FOR SQLSTATE "01XXX"; +SIGNAL warn SET +MESSAGE_TEXT = "This function SIGNAL a warning", +MYSQL_ERRNO = 1012; +SELECT @@warning_count INTO v; +return v; +end $$ +select test_signal_func() $$ +test_signal_func() +1 +drop function test_signal_func $$ +create function test_signal_func() returns integer +begin +DECLARE not_found CONDITION FOR SQLSTATE "02XXX"; +SIGNAL not_found SET +MESSAGE_TEXT = "This function SIGNAL not found", +MYSQL_ERRNO = 1012; +return 5; +end $$ +select test_signal_func() $$ +ERROR 02XXX: This function SIGNAL not found +drop function test_signal_func $$ +create function test_signal_func() returns integer +begin +DECLARE error CONDITION FOR SQLSTATE "50000"; +SIGNAL error SET +MESSAGE_TEXT = "This function SIGNAL an error", +MYSQL_ERRNO = 1012; +return 5; +end $$ +select test_signal_func() $$ +ERROR 50000: This function SIGNAL an error +drop function test_signal_func $$ +drop table if exists t1 $$ +create table t1 (a integer) $$ +create trigger t1_ai after insert on t1 for each row +begin +DECLARE msg VARCHAR(128); +DECLARE warn CONDITION FOR SQLSTATE "01XXX"; +set msg= concat("This trigger SIGNAL a warning, a=", NEW.a); +SIGNAL warn SET +MESSAGE_TEXT = msg, +MYSQL_ERRNO = 1012; +end $$ +insert into t1 values (1), (2) $$ +drop trigger t1_ai $$ +create trigger t1_ai after insert on t1 for each row +begin +DECLARE msg VARCHAR(128); +DECLARE not_found CONDITION FOR SQLSTATE "02XXX"; +set msg= concat("This trigger SIGNAL a not found, a=", NEW.a); +SIGNAL not_found SET +MESSAGE_TEXT = msg, +MYSQL_ERRNO = 1012; +end $$ +insert into t1 values (3), (4) $$ +ERROR 02XXX: This trigger SIGNAL a not found, a=3 +drop trigger t1_ai $$ +create trigger t1_ai after insert on t1 for each row +begin +DECLARE msg VARCHAR(128); +DECLARE error CONDITION FOR SQLSTATE "03XXX"; +set msg= concat("This trigger SIGNAL an error, a=", NEW.a); +SIGNAL error SET +MESSAGE_TEXT = msg, +MYSQL_ERRNO = 1012; +end $$ +insert into t1 values (5), (6) $$ +ERROR 03XXX: This trigger SIGNAL an error, a=5 +drop table t1 $$ +create table t1 (errno integer, msg varchar(128)) $$ +create trigger t1_ai after insert on t1 for each row +begin +DECLARE warn CONDITION FOR SQLSTATE "01XXX"; +SIGNAL warn SET +MESSAGE_TEXT = NEW.msg, +MYSQL_ERRNO = NEW.errno; +end $$ +insert into t1 set errno=1012, msg='Warning message 1 in trigger' $$ +insert into t1 set errno=1013, msg='Warning message 2 in trigger' $$ +drop table t1 $$ +drop table if exists t1 $$ +drop procedure if exists p1 $$ +drop function if exists f1 $$ +create table t1 (s1 int) $$ +insert into t1 values (1) $$ +create procedure p1() +begin +declare a int; +declare c cursor for select f1() from t1; +declare continue handler for sqlstate '03000' + select "caught 03000"; +declare continue handler for 1326 +select "caught cursor is not open"; +select "Before open"; +open c; +select "Before fetch"; +fetch c into a; +select "Before close"; +close c; +end $$ +create function f1() returns int +begin +signal sqlstate '03000'; +return 5; +end $$ +drop table t1 $$ +drop procedure p1 $$ +drop function f1 $$ +# +# Test the RESIGNAL runtime +# +create procedure test_resignal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01234"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL; +select "after RESIGNAL"; +end; +SIGNAL warn SET +MESSAGE_TEXT = "Raising a warning", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +after RESIGNAL +after RESIGNAL +Warnings: +Warning 1012 Raising a warning +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE not_found CONDITION FOR SQLSTATE "02222"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL; +select "after RESIGNAL"; +end; +SIGNAL not_found SET +MESSAGE_TEXT = "Raising a not found", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 02222: Raising a not found +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE error CONDITION FOR SQLSTATE "55555"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL; +select "after RESIGNAL"; +end; +SIGNAL error SET +MESSAGE_TEXT = "Raising an error", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 55555: Raising an error +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE CONTINUE HANDLER for sqlwarning +begin +select "before RESIGNAL"; +RESIGNAL; +select "after RESIGNAL"; +end; +insert ignore into t_warn set a= 9999999999999999; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +after RESIGNAL +after RESIGNAL +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE x integer; +DECLARE c cursor for select * from t_cursor; +DECLARE CONTINUE HANDLER for not found +begin +select "before RESIGNAL"; +RESIGNAL; +select "after RESIGNAL"; +end; +open c; +fetch c into x; +close c; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 02000: No data - zero rows fetched, selected, or processed +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE CONTINUE HANDLER for sqlexception +begin +select "before RESIGNAL"; +RESIGNAL; +select "after RESIGNAL"; +end; +drop table no_such_table; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 42S02: Unknown table 'test.no_such_table' +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01234"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SET +MESSAGE_TEXT = "RESIGNAL of a warning", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL warn SET +MESSAGE_TEXT = "Raising a warning", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +after RESIGNAL +after RESIGNAL +Warnings: +Warning 5555 RESIGNAL of a warning +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE not_found CONDITION FOR SQLSTATE "02111"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SET +MESSAGE_TEXT = "RESIGNAL of a not found", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL not_found SET +MESSAGE_TEXT = "Raising a not found", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 02111: RESIGNAL of a not found +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE error CONDITION FOR SQLSTATE "33333"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SET +MESSAGE_TEXT = "RESIGNAL of an error", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL error SET +MESSAGE_TEXT = "Raising an error", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 33333: RESIGNAL of an error +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE CONTINUE HANDLER for sqlwarning +begin +select "before RESIGNAL"; +RESIGNAL SET +MESSAGE_TEXT = "RESIGNAL of a warning", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +insert ignore into t_warn set a= 9999999999999999; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +after RESIGNAL +after RESIGNAL +Warnings: +Warning 5555 RESIGNAL of a warning +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE x integer; +DECLARE c cursor for select * from t_cursor; +DECLARE CONTINUE HANDLER for not found +begin +select "before RESIGNAL"; +RESIGNAL SET +MESSAGE_TEXT = "RESIGNAL of not found", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +open c; +fetch c into x; +close c; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 02000: RESIGNAL of not found +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE CONTINUE HANDLER for sqlexception +begin +select "before RESIGNAL"; +RESIGNAL SET +MESSAGE_TEXT = "RESIGNAL of an error", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +drop table no_such_table; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 42S02: RESIGNAL of an error +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01111"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "01222" SET +MESSAGE_TEXT = "RESIGNAL to warning", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL warn SET +MESSAGE_TEXT = "Raising a warning", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +after RESIGNAL +after RESIGNAL +Warnings: +Warning 1012 Raising a warning +Warning 5555 RESIGNAL to warning +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01111"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "02222" SET +MESSAGE_TEXT = "RESIGNAL to not found", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL warn SET +MESSAGE_TEXT = "Raising a warning", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 02222: RESIGNAL to not found +show warnings $$ +Level Code Message +Warning 1012 Raising a warning +Error 5555 RESIGNAL to not found +Note 4094 At line 9 in test.test_resignal +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE warn CONDITION FOR SQLSTATE "01111"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "33333" SET +MESSAGE_TEXT = "RESIGNAL to error", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL warn SET +MESSAGE_TEXT = "Raising a warning", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 33333: RESIGNAL to error +show warnings $$ +Level Code Message +Warning 1012 Raising a warning +Error 5555 RESIGNAL to error +Note 4094 At line 9 in test.test_resignal +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "01222" SET +MESSAGE_TEXT = "RESIGNAL to warning", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL not_found SET +MESSAGE_TEXT = "Raising a not found", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +after RESIGNAL +after RESIGNAL +Warnings: +Error 1012 Raising a not found +Warning 5555 RESIGNAL to warning +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "02222" SET +MESSAGE_TEXT = "RESIGNAL to not found", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL not_found SET +MESSAGE_TEXT = "Raising a not found", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 02222: RESIGNAL to not found +show warnings $$ +Level Code Message +Error 1012 Raising a not found +Error 5555 RESIGNAL to not found +Note 4094 At line 9 in test.test_resignal +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE not_found CONDITION FOR SQLSTATE "02ABC"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "33333" SET +MESSAGE_TEXT = "RESIGNAL to error", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL not_found SET +MESSAGE_TEXT = "Raising a not found", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 33333: RESIGNAL to error +show warnings $$ +Level Code Message +Error 1012 Raising a not found +Error 5555 RESIGNAL to error +Note 4094 At line 9 in test.test_resignal +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE error CONDITION FOR SQLSTATE "AAAAA"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "01222" SET +MESSAGE_TEXT = "RESIGNAL to warning", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL error SET +MESSAGE_TEXT = "Raising an error", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +after RESIGNAL +after RESIGNAL +Warnings: +Error 1012 Raising an error +Warning 5555 RESIGNAL to warning +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE error CONDITION FOR SQLSTATE "AAAAA"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "02222" SET +MESSAGE_TEXT = "RESIGNAL to not found", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL error SET +MESSAGE_TEXT = "Raising an error", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 02222: RESIGNAL to not found +show warnings $$ +Level Code Message +Error 1012 Raising an error +Error 5555 RESIGNAL to not found +Note 4094 At line 9 in test.test_resignal +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE error CONDITION FOR SQLSTATE "AAAAA"; +DECLARE CONTINUE HANDLER for 1012 +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "33333" SET +MESSAGE_TEXT = "RESIGNAL to error", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +SIGNAL error SET +MESSAGE_TEXT = "Raising an error", +MYSQL_ERRNO = 1012; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 33333: RESIGNAL to error +show warnings $$ +Level Code Message +Error 1012 Raising an error +Error 5555 RESIGNAL to error +Note 4094 At line 9 in test.test_resignal +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE CONTINUE HANDLER for sqlwarning +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "01111" SET +MESSAGE_TEXT = "RESIGNAL to a warning", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +insert ignore into t_warn set a= 9999999999999999; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +after RESIGNAL +after RESIGNAL +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +Warning 5555 RESIGNAL to a warning +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE CONTINUE HANDLER for sqlwarning +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "02444" SET +MESSAGE_TEXT = "RESIGNAL to a not found", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +insert ignore into t_warn set a= 9999999999999999; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 02444: RESIGNAL to a not found +show warnings $$ +Level Code Message +Warning 1264 Out of range value for column 'a' at row 1 +Error 5555 RESIGNAL to a not found +Note 4094 At line 8 in test.test_resignal +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE CONTINUE HANDLER for sqlwarning +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "44444" SET +MESSAGE_TEXT = "RESIGNAL to an error", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +insert ignore into t_warn set a= 9999999999999999; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 44444: RESIGNAL to an error +show warnings $$ +Level Code Message +Warning 1264 Out of range value for column 'a' at row 1 +Error 5555 RESIGNAL to an error +Note 4094 At line 8 in test.test_resignal +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE x integer; +DECLARE c cursor for select * from t_cursor; +DECLARE CONTINUE HANDLER for not found +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "01111" SET +MESSAGE_TEXT = "RESIGNAL to a warning", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +open c; +fetch c into x; +close c; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +after RESIGNAL +after RESIGNAL +Warnings: +Error 1329 No data - zero rows fetched, selected, or processed +Warning 5555 RESIGNAL to a warning +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE x integer; +DECLARE c cursor for select * from t_cursor; +DECLARE CONTINUE HANDLER for not found +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "02444" SET +MESSAGE_TEXT = "RESIGNAL to a not found", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +open c; +fetch c into x; +close c; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 02444: RESIGNAL to a not found +show warnings $$ +Level Code Message +Error 1329 No data - zero rows fetched, selected, or processed +Error 5555 RESIGNAL to a not found +Note 4094 At line 10 in test.test_resignal +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE x integer; +DECLARE c cursor for select * from t_cursor; +DECLARE CONTINUE HANDLER for not found +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "44444" SET +MESSAGE_TEXT = "RESIGNAL to an error", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +open c; +fetch c into x; +close c; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 44444: RESIGNAL to an error +show warnings $$ +Level Code Message +Error 1329 No data - zero rows fetched, selected, or processed +Error 5555 RESIGNAL to an error +Note 4094 At line 10 in test.test_resignal +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE CONTINUE HANDLER for sqlexception +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "01111" SET +MESSAGE_TEXT = "RESIGNAL to a warning", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +drop table no_such_table; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +after RESIGNAL +after RESIGNAL +Warnings: +Error 1051 Unknown table 'test.no_such_table' +Warning 5555 RESIGNAL to a warning +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE CONTINUE HANDLER for sqlexception +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "02444" SET +MESSAGE_TEXT = "RESIGNAL to a not found", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +drop table no_such_table; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 02444: RESIGNAL to a not found +show warnings $$ +Level Code Message +Error 1051 Unknown table 'test.no_such_table' +Error 5555 RESIGNAL to a not found +Note 4094 At line 8 in test.test_resignal +drop procedure test_resignal $$ +create procedure test_resignal() +begin +DECLARE CONTINUE HANDLER for sqlexception +begin +select "before RESIGNAL"; +RESIGNAL SQLSTATE "44444" SET +MESSAGE_TEXT = "RESIGNAL to an error", +MYSQL_ERRNO = 5555 ; +select "after RESIGNAL"; +end; +drop table no_such_table; +end $$ +call test_resignal() $$ +before RESIGNAL +before RESIGNAL +ERROR 44444: RESIGNAL to an error +show warnings $$ +Level Code Message +Error 1051 Unknown table 'test.no_such_table' +Error 5555 RESIGNAL to an error +Note 4094 At line 8 in test.test_resignal +drop procedure test_resignal $$ +# +# More complex cases +# +drop procedure if exists peter_p1 $$ +drop procedure if exists peter_p2 $$ +CREATE PROCEDURE peter_p1 () +BEGIN +DECLARE x CONDITION FOR 1231; +DECLARE EXIT HANDLER FOR x +BEGIN +SELECT '2'; +RESIGNAL SET MYSQL_ERRNO = 9999; +END; +BEGIN +DECLARE EXIT HANDLER FOR x +BEGIN +SELECT '1'; +RESIGNAL SET SCHEMA_NAME = 'test'; +END; +SET @@sql_mode=NULL; +END; +END +$$ +CREATE PROCEDURE peter_p2 () +BEGIN +DECLARE x CONDITION for 9999; +DECLARE EXIT HANDLER FOR x +BEGIN +SELECT '3'; +RESIGNAL SET MESSAGE_TEXT = 'Hi, I am a useless error message'; +END; +CALL peter_p1(); +END +$$ +CALL peter_p2() $$ +1 +1 +2 +2 +3 +3 +ERROR 42000: Hi, I am a useless error message +show warnings $$ +Level Code Message +Error 9999 Hi, I am a useless error message +Note 4094 At line 7 in test.peter_p2 +drop procedure peter_p1 $$ +drop procedure peter_p2 $$ +CREATE PROCEDURE peter_p1 () +BEGIN +DECLARE x CONDITION FOR SQLSTATE '42000'; +DECLARE EXIT HANDLER FOR x +BEGIN +SELECT '2'; +SHOW WARNINGS; +RESIGNAL x SET MYSQL_ERRNO = 9999; +END; +BEGIN +DECLARE EXIT HANDLER FOR x +BEGIN +SELECT '1'; +SHOW WARNINGS; +RESIGNAL x SET +SCHEMA_NAME = 'test', +MYSQL_ERRNO= 1232; +END; +/* Raises ER_WRONG_VALUE_FOR_VAR : 1231, SQLSTATE 42000 */ +SET @@sql_mode=NULL; +END; +END +$$ +CREATE PROCEDURE peter_p2 () +BEGIN +DECLARE x CONDITION for SQLSTATE '42000'; +DECLARE EXIT HANDLER FOR x +BEGIN +SELECT '3'; +SHOW WARNINGS; +RESIGNAL x SET +MESSAGE_TEXT = 'Hi, I am a useless error message', +MYSQL_ERRNO = 9999; +END; +CALL peter_p1(); +END +$$ +CALL peter_p2() $$ +1 +1 +Level Code Message +Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' +2 +2 +Level Code Message +Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' +Error 1232 Variable 'sql_mode' can't be set to the value of 'NULL' +3 +3 +Level Code Message +Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' +Error 1232 Variable 'sql_mode' can't be set to the value of 'NULL' +Error 9999 Variable 'sql_mode' can't be set to the value of 'NULL' +Note 4094 At line 8 in test.peter_p1 +ERROR 42000: Hi, I am a useless error message +show warnings $$ +Level Code Message +Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' +Error 1232 Variable 'sql_mode' can't be set to the value of 'NULL' +Error 9999 Variable 'sql_mode' can't be set to the value of 'NULL' +Note 4094 At line 8 in test.peter_p1 +Error 9999 Hi, I am a useless error message +Note 4094 At line 10 in test.peter_p2 +drop procedure peter_p1 $$ +drop procedure peter_p2 $$ +drop procedure if exists peter_p3 $$ +Warnings: +Note 1305 PROCEDURE test.peter_p3 does not exist +create procedure peter_p3() +begin +declare continue handler for sqlexception +resignal sqlstate '99002' set mysql_errno = 2; +signal sqlstate '99001' set mysql_errno = 1, message_text = "Original"; +end $$ +call peter_p3() $$ +ERROR 99002: Original +show warnings $$ +Level Code Message +Error 1 Original +Error 2 Original +Note 4094 At line 4 in test.peter_p3 +drop procedure peter_p3 $$ +drop table t_warn; +drop table t_cursor; +# +# Miscelaneous test cases +# +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 0x12; /* 18 */ +end $$ +call test_signal $$ +ERROR 12345: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 0b00010010; /* 18 */ +end $$ +call test_signal $$ +ERROR 12345: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = '65'; /* 65 */ +end $$ +call test_signal $$ +ERROR 12345: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 'A'; /* illegal */ +end $$ +call test_signal $$ +ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of 'A' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = "65"; /* 65 */ +end $$ +call test_signal $$ +ERROR 12345: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = "A"; /* illegal */ +end $$ +call test_signal $$ +ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of 'A' +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = `65`; /* illegal */ +end $$ +ERROR 42000: Undeclared variable: 65 +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = `A`; /* illegal */ +end $$ +ERROR 42000: Undeclared variable: A +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 3.141592; /* 3 */ +end $$ +call test_signal $$ +ERROR 12345: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 1000, +MESSAGE_TEXT= 0x41; /* A */ +end $$ +call test_signal $$ +ERROR 12345: A +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 1000, +MESSAGE_TEXT= 0b01000001; /* A */ +end $$ +call test_signal $$ +ERROR 12345: A +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 1000, +MESSAGE_TEXT = "Hello"; +end $$ +call test_signal $$ +ERROR 12345: Hello +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 1000, +MESSAGE_TEXT = 'Hello'; +end $$ +call test_signal $$ +ERROR 12345: Hello +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 1000, +MESSAGE_TEXT = `Hello`; +end $$ +ERROR 42000: Undeclared variable: Hello +create procedure test_signal() +begin +DECLARE foo CONDITION FOR SQLSTATE '12345'; +SIGNAL foo SET MYSQL_ERRNO = 1000, +MESSAGE_TEXT = 65.4321; +end $$ +call test_signal $$ +ERROR 12345: 65.4321 +drop procedure test_signal $$ +create procedure test_signal() +begin +DECLARE céèçà foo CONDITION FOR SQLSTATE '12345'; +SIGNAL céèçà SET MYSQL_ERRNO = 1000; +end $$ +ERROR 42000: 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 'èçà foo CONDITION FOR SQLSTATE '12345'; +SIGNAL céèçà S...' at line 3 +create procedure test_signal() +begin +DECLARE "céèçà" CONDITION FOR SQLSTATE '12345'; +SIGNAL "céèçà" SET MYSQL_ERRNO = 1000; +end $$ +ERROR 42000: 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 '"céèçà" CONDITION FOR SQLSTATE '12345'; +SIGNAL "céèçà...' at line 3 +create procedure test_signal() +begin +DECLARE 'céèçà' CONDITION FOR SQLSTATE '12345'; +SIGNAL 'céèçà' SET MYSQL_ERRNO = 1000; +end $$ +ERROR 42000: 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 ''céèçà' CONDITION FOR SQLSTATE '12345'; +SIGNAL 'céèçà...' at line 3 +create procedure test_signal() +begin +DECLARE `céèçà` CONDITION FOR SQLSTATE '12345'; +SIGNAL `céèçà` SET MYSQL_ERRNO = 1000; +end $$ +call test_signal $$ +ERROR 12345: Unhandled user-defined exception condition +drop procedure test_signal $$ +create procedure test_signal() +begin +SIGNAL SQLSTATE '77777' SET MYSQL_ERRNO = 1000, MESSAGE_TEXT='ÁÂÃÅÄ'; +end $$ +drop procedure test_signal $$ |