PageRenderTime 25ms CodeModel.GetById 12ms RepoModel.GetById 1ms app.codeStats 0ms

/mysql-test/r/signal.result

https://bitbucket.org/Habibutsu/mysql
Unknown | 2392 lines | 2392 code | 0 blank | 0 comment | 0 complexity | feb3946d798fe8cbd73f5176f83b72e5 MD5 | raw file
Possible License(s): GPL-2.0, BSD-3-Clause

Large files files are truncated, but you can click here to view the full file

  1. #
  2. # PART 1: syntax
  3. #
  4. #
  5. # Test every new reserved and non reserved keywords
  6. #
  7. drop table if exists signal_non_reserved;
  8. create table signal_non_reserved (
  9. class_origin int,
  10. subclass_origin int,
  11. constraint_catalog int,
  12. constraint_schema int,
  13. constraint_name int,
  14. catalog_name int,
  15. schema_name int,
  16. table_name int,
  17. column_name int,
  18. cursor_name int,
  19. message_text int,
  20. sqlcode int
  21. );
  22. drop table signal_non_reserved;
  23. drop table if exists diag_non_reserved;
  24. create table diag_non_reserved (
  25. diagnostics int,
  26. current int,
  27. stacked int,
  28. exception int
  29. );
  30. drop table diag_non_reserved;
  31. drop table if exists diag_cond_non_reserved;
  32. create table diag_cond_non_reserved (
  33. condition_identifier int,
  34. condition_number int,
  35. condition_name int,
  36. connection_name int,
  37. message_length int,
  38. message_octet_length int,
  39. parameter_mode int,
  40. parameter_name int,
  41. parameter_ordinal_position int,
  42. returned_sqlstate int,
  43. routine_catalog int,
  44. routine_name int,
  45. routine_schema int,
  46. server_name int,
  47. specific_name int,
  48. trigger_catalog int,
  49. trigger_name int,
  50. trigger_schema int
  51. );
  52. drop table diag_cond_non_reserved;
  53. drop table if exists diag_stmt_non_reserved;
  54. create table diag_stmt_non_reserved (
  55. number int,
  56. more int,
  57. command_function int,
  58. command_function_code int,
  59. dynamic_function int,
  60. dynamic_function_code int,
  61. row_count int,
  62. transactions_committed int,
  63. transactions_rolled_back int,
  64. transaction_active int
  65. );
  66. drop table diag_stmt_non_reserved;
  67. drop table if exists test_reserved;
  68. create table test_reserved (signal int);
  69. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'signal int)' at line 1
  70. create table test_reserved (resignal int);
  71. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'resignal int)' at line 1
  72. create table test_reserved (condition int);
  73. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition int)' at line 1
  74. #
  75. # Test the SIGNAL syntax
  76. #
  77. drop procedure if exists test_invalid;
  78. drop procedure if exists test_signal_syntax;
  79. drop function if exists test_signal_func;
  80. create procedure test_invalid()
  81. begin
  82. SIGNAL;
  83. end $$
  84. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
  85. end' at line 3
  86. create procedure test_invalid()
  87. begin
  88. SIGNAL foo;
  89. end $$
  90. ERROR 42000: Undefined CONDITION: foo
  91. create procedure test_invalid()
  92. begin
  93. DECLARE foo CONDITION FOR 1234;
  94. SIGNAL foo;
  95. end $$
  96. ERROR HY000: SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE
  97. create procedure test_signal_syntax()
  98. begin
  99. DECLARE foo CONDITION FOR SQLSTATE '12345';
  100. SIGNAL foo;
  101. end $$
  102. drop procedure test_signal_syntax $$
  103. create procedure test_signal_syntax()
  104. begin
  105. SIGNAL SQLSTATE '23000';
  106. end $$
  107. drop procedure test_signal_syntax $$
  108. create procedure test_signal_syntax()
  109. begin
  110. SIGNAL SQLSTATE VALUE '23000';
  111. end $$
  112. drop procedure test_signal_syntax $$
  113. create procedure test_signal_syntax()
  114. begin
  115. DECLARE foo CONDITION FOR SQLSTATE '12345';
  116. SIGNAL foo SET CLASS_ORIGIN = 'foo';
  117. end $$
  118. drop procedure test_signal_syntax $$
  119. create procedure test_signal_syntax()
  120. begin
  121. DECLARE foo CONDITION FOR SQLSTATE '12345';
  122. SIGNAL foo SET SUBCLASS_ORIGIN = 'foo';
  123. end $$
  124. drop procedure test_signal_syntax $$
  125. create procedure test_signal_syntax()
  126. begin
  127. DECLARE foo CONDITION FOR SQLSTATE '12345';
  128. SIGNAL foo SET CONSTRAINT_CATALOG = 'foo';
  129. end $$
  130. drop procedure test_signal_syntax $$
  131. create procedure test_signal_syntax()
  132. begin
  133. DECLARE foo CONDITION FOR SQLSTATE '12345';
  134. SIGNAL foo SET CONSTRAINT_SCHEMA = 'foo';
  135. end $$
  136. drop procedure test_signal_syntax $$
  137. create procedure test_signal_syntax()
  138. begin
  139. DECLARE foo CONDITION FOR SQLSTATE '12345';
  140. SIGNAL foo SET CONSTRAINT_NAME = 'foo';
  141. end $$
  142. drop procedure test_signal_syntax $$
  143. create procedure test_signal_syntax()
  144. begin
  145. DECLARE foo CONDITION FOR SQLSTATE '12345';
  146. SIGNAL foo SET CATALOG_NAME = 'foo';
  147. end $$
  148. drop procedure test_signal_syntax $$
  149. create procedure test_signal_syntax()
  150. begin
  151. DECLARE foo CONDITION FOR SQLSTATE '12345';
  152. SIGNAL foo SET SCHEMA_NAME = 'foo';
  153. end $$
  154. drop procedure test_signal_syntax $$
  155. create procedure test_signal_syntax()
  156. begin
  157. DECLARE foo CONDITION FOR SQLSTATE '12345';
  158. SIGNAL foo SET TABLE_NAME = 'foo';
  159. end $$
  160. drop procedure test_signal_syntax $$
  161. create procedure test_signal_syntax()
  162. begin
  163. DECLARE foo CONDITION FOR SQLSTATE '12345';
  164. SIGNAL foo SET COLUMN_NAME = 'foo';
  165. end $$
  166. drop procedure test_signal_syntax $$
  167. create procedure test_signal_syntax()
  168. begin
  169. DECLARE foo CONDITION FOR SQLSTATE '12345';
  170. SIGNAL foo SET CURSOR_NAME = 'foo';
  171. end $$
  172. drop procedure test_signal_syntax $$
  173. create procedure test_signal_syntax()
  174. begin
  175. DECLARE foo CONDITION FOR SQLSTATE '12345';
  176. SIGNAL foo SET MESSAGE_TEXT = 'foo';
  177. end $$
  178. drop procedure test_signal_syntax $$
  179. create procedure test_signal_syntax()
  180. begin
  181. DECLARE foo CONDITION FOR SQLSTATE '12345';
  182. SIGNAL foo SET MYSQL_ERRNO = 'foo';
  183. end $$
  184. drop procedure test_signal_syntax $$
  185. create procedure test_invalid()
  186. begin
  187. DECLARE foo CONDITION FOR SQLSTATE '12345';
  188. SIGNAL foo SET CLASS_ORIGIN = 'foo', CLASS_ORIGIN = 'bar';
  189. end $$
  190. ERROR 42000: Duplicate condition information item 'CLASS_ORIGIN'
  191. create procedure test_invalid()
  192. begin
  193. DECLARE foo CONDITION FOR SQLSTATE '12345';
  194. SIGNAL foo SET MESSAGE_TEXT = 'foo', MESSAGE_TEXT = 'bar';
  195. end $$
  196. ERROR 42000: Duplicate condition information item 'MESSAGE_TEXT'
  197. create procedure test_invalid()
  198. begin
  199. DECLARE foo CONDITION FOR SQLSTATE '12345';
  200. SIGNAL foo SET MYSQL_ERRNO = 'foo', MYSQL_ERRNO = 'bar';
  201. end $$
  202. ERROR 42000: Duplicate condition information item 'MYSQL_ERRNO'
  203. create procedure test_signal_syntax()
  204. begin
  205. DECLARE foo CONDITION FOR SQLSTATE '12345';
  206. SIGNAL foo SET
  207. CLASS_ORIGIN = 'foo',
  208. SUBCLASS_ORIGIN = 'foo',
  209. CONSTRAINT_CATALOG = 'foo',
  210. CONSTRAINT_SCHEMA = 'foo',
  211. CONSTRAINT_NAME = 'foo',
  212. CATALOG_NAME = 'foo',
  213. SCHEMA_NAME = 'foo',
  214. TABLE_NAME = 'foo',
  215. COLUMN_NAME = 'foo',
  216. CURSOR_NAME = 'foo',
  217. MESSAGE_TEXT = 'foo',
  218. MYSQL_ERRNO = 'foo';
  219. end $$
  220. drop procedure test_signal_syntax $$
  221. SIGNAL SQLSTATE '00000' $$
  222. ERROR 42000: Bad SQLSTATE: '00000'
  223. SIGNAL SQLSTATE '00001' $$
  224. ERROR 42000: Bad SQLSTATE: '00001'
  225. create procedure test_invalid()
  226. begin
  227. SIGNAL SQLSTATE '00000';
  228. end $$
  229. ERROR 42000: Bad SQLSTATE: '00000'
  230. create procedure test_invalid()
  231. begin
  232. SIGNAL SQLSTATE '00001';
  233. end $$
  234. ERROR 42000: Bad SQLSTATE: '00001'
  235. #
  236. # Test conditions information that SIGNAL can not set
  237. #
  238. create procedure test_invalid()
  239. begin
  240. SIGNAL SQLSTATE '12345' SET bla_bla = 'foo';
  241. end $$
  242. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bla_bla = 'foo';
  243. end' at line 3
  244. create procedure test_invalid()
  245. begin
  246. SIGNAL SQLSTATE '12345' SET CONDITION_IDENTIFIER = 'foo';
  247. end $$
  248. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONDITION_IDENTIFIER = 'foo';
  249. end' at line 3
  250. create procedure test_invalid()
  251. begin
  252. SIGNAL SQLSTATE '12345' SET CONDITION_NUMBER = 'foo';
  253. end $$
  254. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONDITION_NUMBER = 'foo';
  255. end' at line 3
  256. create procedure test_invalid()
  257. begin
  258. SIGNAL SQLSTATE '12345' SET CONNECTION_NAME = 'foo';
  259. end $$
  260. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONNECTION_NAME = 'foo';
  261. end' at line 3
  262. create procedure test_invalid()
  263. begin
  264. SIGNAL SQLSTATE '12345' SET MESSAGE_LENGTH = 'foo';
  265. end $$
  266. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MESSAGE_LENGTH = 'foo';
  267. end' at line 3
  268. create procedure test_invalid()
  269. begin
  270. SIGNAL SQLSTATE '12345' SET MESSAGE_OCTET_LENGTH = 'foo';
  271. end $$
  272. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MESSAGE_OCTET_LENGTH = 'foo';
  273. end' at line 3
  274. create procedure test_invalid()
  275. begin
  276. SIGNAL SQLSTATE '12345' SET PARAMETER_MODE = 'foo';
  277. end $$
  278. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARAMETER_MODE = 'foo';
  279. end' at line 3
  280. create procedure test_invalid()
  281. begin
  282. SIGNAL SQLSTATE '12345' SET PARAMETER_NAME = 'foo';
  283. end $$
  284. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARAMETER_NAME = 'foo';
  285. end' at line 3
  286. create procedure test_invalid()
  287. begin
  288. SIGNAL SQLSTATE '12345' SET PARAMETER_ORDINAL_POSITION = 'foo';
  289. end $$
  290. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARAMETER_ORDINAL_POSITION = 'foo';
  291. end' at line 3
  292. create procedure test_invalid()
  293. begin
  294. SIGNAL SQLSTATE '12345' SET RETURNED_SQLSTATE = 'foo';
  295. end $$
  296. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNED_SQLSTATE = 'foo';
  297. end' at line 3
  298. create procedure test_invalid()
  299. begin
  300. SIGNAL SQLSTATE '12345' SET ROUTINE_CATALOG = 'foo';
  301. end $$
  302. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROUTINE_CATALOG = 'foo';
  303. end' at line 3
  304. create procedure test_invalid()
  305. begin
  306. SIGNAL SQLSTATE '12345' SET ROUTINE_NAME = 'foo';
  307. end $$
  308. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROUTINE_NAME = 'foo';
  309. end' at line 3
  310. create procedure test_invalid()
  311. begin
  312. SIGNAL SQLSTATE '12345' SET ROUTINE_SCHEMA = 'foo';
  313. end $$
  314. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROUTINE_SCHEMA = 'foo';
  315. end' at line 3
  316. create procedure test_invalid()
  317. begin
  318. SIGNAL SQLSTATE '12345' SET SERVER_NAME = 'foo';
  319. end $$
  320. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SERVER_NAME = 'foo';
  321. end' at line 3
  322. create procedure test_invalid()
  323. begin
  324. SIGNAL SQLSTATE '12345' SET SPECIFIC_NAME = 'foo';
  325. end $$
  326. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SPECIFIC_NAME = 'foo';
  327. end' at line 3
  328. create procedure test_invalid()
  329. begin
  330. SIGNAL SQLSTATE '12345' SET TRIGGER_CATALOG = 'foo';
  331. end $$
  332. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIGGER_CATALOG = 'foo';
  333. end' at line 3
  334. create procedure test_invalid()
  335. begin
  336. SIGNAL SQLSTATE '12345' SET TRIGGER_NAME = 'foo';
  337. end $$
  338. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIGGER_NAME = 'foo';
  339. end' at line 3
  340. create procedure test_invalid()
  341. begin
  342. SIGNAL SQLSTATE '12345' SET TRIGGER_SCHEMA = 'foo';
  343. end $$
  344. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIGGER_SCHEMA = 'foo';
  345. end' at line 3
  346. #
  347. # Test the RESIGNAL syntax
  348. #
  349. drop procedure if exists test_invalid;
  350. drop procedure if exists test_resignal_syntax;
  351. create procedure test_invalid()
  352. begin
  353. RESIGNAL foo;
  354. end $$
  355. ERROR 42000: Undefined CONDITION: foo
  356. create procedure test_resignal_syntax()
  357. begin
  358. RESIGNAL;
  359. end $$
  360. drop procedure test_resignal_syntax $$
  361. create procedure test_invalid()
  362. begin
  363. DECLARE foo CONDITION FOR 1234;
  364. RESIGNAL foo;
  365. end $$
  366. ERROR HY000: SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE
  367. create procedure test_resignal_syntax()
  368. begin
  369. DECLARE foo CONDITION FOR SQLSTATE '12345';
  370. RESIGNAL foo;
  371. end $$
  372. drop procedure test_resignal_syntax $$
  373. create procedure test_resignal_syntax()
  374. begin
  375. RESIGNAL SQLSTATE '23000';
  376. end $$
  377. drop procedure test_resignal_syntax $$
  378. create procedure test_resignal_syntax()
  379. begin
  380. RESIGNAL SQLSTATE VALUE '23000';
  381. end $$
  382. drop procedure test_resignal_syntax $$
  383. create procedure test_resignal_syntax()
  384. begin
  385. RESIGNAL SET CLASS_ORIGIN = 'foo';
  386. end $$
  387. drop procedure test_resignal_syntax $$
  388. create procedure test_resignal_syntax()
  389. begin
  390. DECLARE foo CONDITION FOR SQLSTATE '12345';
  391. RESIGNAL foo SET CLASS_ORIGIN = 'foo';
  392. end $$
  393. drop procedure test_resignal_syntax $$
  394. create procedure test_resignal_syntax()
  395. begin
  396. RESIGNAL SET SUBCLASS_ORIGIN = 'foo';
  397. end $$
  398. drop procedure test_resignal_syntax $$
  399. create procedure test_resignal_syntax()
  400. begin
  401. DECLARE foo CONDITION FOR SQLSTATE '12345';
  402. RESIGNAL foo SET SUBCLASS_ORIGIN = 'foo';
  403. end $$
  404. drop procedure test_resignal_syntax $$
  405. create procedure test_resignal_syntax()
  406. begin
  407. RESIGNAL SET CONSTRAINT_CATALOG = 'foo';
  408. end $$
  409. drop procedure test_resignal_syntax $$
  410. create procedure test_resignal_syntax()
  411. begin
  412. DECLARE foo CONDITION FOR SQLSTATE '12345';
  413. RESIGNAL foo SET CONSTRAINT_CATALOG = 'foo';
  414. end $$
  415. drop procedure test_resignal_syntax $$
  416. create procedure test_resignal_syntax()
  417. begin
  418. RESIGNAL SET CONSTRAINT_SCHEMA = 'foo';
  419. end $$
  420. drop procedure test_resignal_syntax $$
  421. create procedure test_resignal_syntax()
  422. begin
  423. DECLARE foo CONDITION FOR SQLSTATE '12345';
  424. RESIGNAL foo SET CONSTRAINT_SCHEMA = 'foo';
  425. end $$
  426. drop procedure test_resignal_syntax $$
  427. create procedure test_resignal_syntax()
  428. begin
  429. RESIGNAL SET CONSTRAINT_NAME = 'foo';
  430. end $$
  431. drop procedure test_resignal_syntax $$
  432. create procedure test_resignal_syntax()
  433. begin
  434. DECLARE foo CONDITION FOR SQLSTATE '12345';
  435. RESIGNAL foo SET CONSTRAINT_NAME = 'foo';
  436. end $$
  437. drop procedure test_resignal_syntax $$
  438. create procedure test_resignal_syntax()
  439. begin
  440. RESIGNAL SET CATALOG_NAME = 'foo';
  441. end $$
  442. drop procedure test_resignal_syntax $$
  443. create procedure test_resignal_syntax()
  444. begin
  445. DECLARE foo CONDITION FOR SQLSTATE '12345';
  446. RESIGNAL foo SET CATALOG_NAME = 'foo';
  447. end $$
  448. drop procedure test_resignal_syntax $$
  449. create procedure test_resignal_syntax()
  450. begin
  451. RESIGNAL SET SCHEMA_NAME = 'foo';
  452. end $$
  453. drop procedure test_resignal_syntax $$
  454. create procedure test_resignal_syntax()
  455. begin
  456. DECLARE foo CONDITION FOR SQLSTATE '12345';
  457. RESIGNAL foo SET SCHEMA_NAME = 'foo';
  458. end $$
  459. drop procedure test_resignal_syntax $$
  460. create procedure test_resignal_syntax()
  461. begin
  462. RESIGNAL SET TABLE_NAME = 'foo';
  463. end $$
  464. drop procedure test_resignal_syntax $$
  465. create procedure test_resignal_syntax()
  466. begin
  467. DECLARE foo CONDITION FOR SQLSTATE '12345';
  468. RESIGNAL foo SET TABLE_NAME = 'foo';
  469. end $$
  470. drop procedure test_resignal_syntax $$
  471. create procedure test_resignal_syntax()
  472. begin
  473. RESIGNAL SET COLUMN_NAME = 'foo';
  474. end $$
  475. drop procedure test_resignal_syntax $$
  476. create procedure test_resignal_syntax()
  477. begin
  478. DECLARE foo CONDITION FOR SQLSTATE '12345';
  479. RESIGNAL foo SET COLUMN_NAME = 'foo';
  480. end $$
  481. drop procedure test_resignal_syntax $$
  482. create procedure test_resignal_syntax()
  483. begin
  484. RESIGNAL SET CURSOR_NAME = 'foo';
  485. end $$
  486. drop procedure test_resignal_syntax $$
  487. create procedure test_resignal_syntax()
  488. begin
  489. DECLARE foo CONDITION FOR SQLSTATE '12345';
  490. RESIGNAL foo SET CURSOR_NAME = 'foo';
  491. end $$
  492. drop procedure test_resignal_syntax $$
  493. create procedure test_resignal_syntax()
  494. begin
  495. RESIGNAL SET MESSAGE_TEXT = 'foo';
  496. end $$
  497. drop procedure test_resignal_syntax $$
  498. create procedure test_resignal_syntax()
  499. begin
  500. DECLARE foo CONDITION FOR SQLSTATE '12345';
  501. RESIGNAL foo SET MESSAGE_TEXT = 'foo';
  502. end $$
  503. drop procedure test_resignal_syntax $$
  504. create procedure test_resignal_syntax()
  505. begin
  506. RESIGNAL SET MYSQL_ERRNO = 'foo';
  507. end $$
  508. drop procedure test_resignal_syntax $$
  509. create procedure test_resignal_syntax()
  510. begin
  511. DECLARE foo CONDITION FOR SQLSTATE '12345';
  512. RESIGNAL foo SET MYSQL_ERRNO = 'foo';
  513. end $$
  514. drop procedure test_resignal_syntax $$
  515. create procedure test_invalid()
  516. begin
  517. DECLARE foo CONDITION FOR SQLSTATE '12345';
  518. RESIGNAL foo SET CLASS_ORIGIN = 'foo', CLASS_ORIGIN = 'bar';
  519. end $$
  520. ERROR 42000: Duplicate condition information item 'CLASS_ORIGIN'
  521. create procedure test_invalid()
  522. begin
  523. DECLARE foo CONDITION FOR SQLSTATE '12345';
  524. RESIGNAL foo SET MESSAGE_TEXT = 'foo', MESSAGE_TEXT = 'bar';
  525. end $$
  526. ERROR 42000: Duplicate condition information item 'MESSAGE_TEXT'
  527. create procedure test_invalid()
  528. begin
  529. DECLARE foo CONDITION FOR SQLSTATE '12345';
  530. RESIGNAL foo SET MYSQL_ERRNO = 'foo', MYSQL_ERRNO = 'bar';
  531. end $$
  532. ERROR 42000: Duplicate condition information item 'MYSQL_ERRNO'
  533. create procedure test_resignal_syntax()
  534. begin
  535. DECLARE foo CONDITION FOR SQLSTATE '12345';
  536. RESIGNAL foo SET
  537. CLASS_ORIGIN = 'foo',
  538. SUBCLASS_ORIGIN = 'foo',
  539. CONSTRAINT_CATALOG = 'foo',
  540. CONSTRAINT_SCHEMA = 'foo',
  541. CONSTRAINT_NAME = 'foo',
  542. CATALOG_NAME = 'foo',
  543. SCHEMA_NAME = 'foo',
  544. TABLE_NAME = 'foo',
  545. COLUMN_NAME = 'foo',
  546. CURSOR_NAME = 'foo',
  547. MESSAGE_TEXT = 'foo';
  548. end $$
  549. drop procedure test_resignal_syntax $$
  550. create procedure test_invalid()
  551. begin
  552. RESIGNAL SQLSTATE '00000';
  553. end $$
  554. ERROR 42000: Bad SQLSTATE: '00000'
  555. create procedure test_invalid()
  556. begin
  557. RESIGNAL SQLSTATE '00001';
  558. end $$
  559. ERROR 42000: Bad SQLSTATE: '00001'
  560. #
  561. # PART 2: non preparable statements
  562. #
  563. prepare stmt from 'SIGNAL SQLSTATE \'23000\'';
  564. ERROR HY000: This command is not supported in the prepared statement protocol yet
  565. prepare stmt from 'RESIGNAL SQLSTATE \'23000\'';
  566. ERROR HY000: This command is not supported in the prepared statement protocol yet
  567. #
  568. # PART 3: runtime execution
  569. #
  570. drop procedure if exists test_signal;
  571. drop procedure if exists test_resignal;
  572. drop table if exists t_warn;
  573. drop table if exists t_cursor;
  574. create table t_warn(a integer(2));
  575. create table t_cursor(a integer);
  576. #
  577. # SIGNAL can also appear in a query
  578. #
  579. SIGNAL foo;
  580. ERROR 42000: Undefined CONDITION: foo
  581. SIGNAL SQLSTATE '01000';
  582. Warnings:
  583. Warning 1642 Unhandled user-defined warning condition
  584. SIGNAL SQLSTATE '02000';
  585. ERROR 02000: Unhandled user-defined not found condition
  586. SIGNAL SQLSTATE '23000';
  587. ERROR 23000: Unhandled user-defined exception condition
  588. SIGNAL SQLSTATE VALUE '23000';
  589. ERROR 23000: Unhandled user-defined exception condition
  590. SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 65536;
  591. ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '65536'
  592. SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 99999;
  593. ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '99999'
  594. SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 4294967295;
  595. ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '4294967295'
  596. SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 0;
  597. ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '0'
  598. SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = -1;
  599. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1
  600. SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 65535;
  601. ERROR HY000: Unhandled user-defined exception condition
  602. #
  603. # RESIGNAL can also appear in a query
  604. #
  605. RESIGNAL;
  606. ERROR 0K000: RESIGNAL when handler not active
  607. RESIGNAL foo;
  608. ERROR 42000: Undefined CONDITION: foo
  609. RESIGNAL SQLSTATE '12345';
  610. ERROR 0K000: RESIGNAL when handler not active
  611. RESIGNAL SQLSTATE VALUE '12345';
  612. ERROR 0K000: RESIGNAL when handler not active
  613. #
  614. # Different kind of SIGNAL conditions
  615. #
  616. create procedure test_signal()
  617. begin
  618. # max range
  619. DECLARE foo CONDITION FOR SQLSTATE 'AABBB';
  620. SIGNAL foo SET MYSQL_ERRNO = 65535;
  621. end $$
  622. call test_signal() $$
  623. ERROR AABBB: Unhandled user-defined exception condition
  624. drop procedure test_signal $$
  625. create procedure test_signal()
  626. begin
  627. # max range
  628. DECLARE foo CONDITION FOR SQLSTATE 'AABBB';
  629. SIGNAL foo SET MYSQL_ERRNO = 65536;
  630. end $$
  631. call test_signal() $$
  632. ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '65536'
  633. drop procedure test_signal $$
  634. create procedure test_signal()
  635. begin
  636. # Error
  637. DECLARE foo CONDITION FOR SQLSTATE '99999';
  638. SIGNAL foo SET MYSQL_ERRNO = 9999;
  639. end $$
  640. call test_signal() $$
  641. ERROR 99999: Unhandled user-defined exception condition
  642. drop procedure test_signal $$
  643. create procedure test_signal()
  644. begin
  645. # warning
  646. DECLARE too_few_records CONDITION FOR SQLSTATE '01000';
  647. SIGNAL too_few_records SET MYSQL_ERRNO = 1261;
  648. end $$
  649. call test_signal() $$
  650. Warnings:
  651. Warning 1261 Unhandled user-defined warning condition
  652. drop procedure test_signal $$
  653. create procedure test_signal()
  654. begin
  655. # Not found
  656. DECLARE sp_fetch_no_data CONDITION FOR SQLSTATE '02000';
  657. SIGNAL sp_fetch_no_data SET MYSQL_ERRNO = 1329;
  658. end $$
  659. call test_signal() $$
  660. ERROR 02000: Unhandled user-defined not found condition
  661. drop procedure test_signal $$
  662. create procedure test_signal()
  663. begin
  664. # Error
  665. DECLARE sp_cursor_already_open CONDITION FOR SQLSTATE '24000';
  666. SIGNAL sp_cursor_already_open SET MYSQL_ERRNO = 1325;
  667. end $$
  668. call test_signal() $$
  669. ERROR 24000: Unhandled user-defined exception condition
  670. drop procedure test_signal $$
  671. create procedure test_signal()
  672. begin
  673. # Severe error
  674. DECLARE lock_deadlock CONDITION FOR SQLSTATE '40001';
  675. SIGNAL lock_deadlock SET MYSQL_ERRNO = 1213;
  676. end $$
  677. call test_signal() $$
  678. ERROR 40001: Unhandled user-defined exception condition
  679. drop procedure test_signal $$
  680. create procedure test_signal()
  681. begin
  682. # Unknown -> error
  683. DECLARE foo CONDITION FOR SQLSTATE "99999";
  684. SIGNAL foo;
  685. end $$
  686. call test_signal() $$
  687. ERROR 99999: Unhandled user-defined exception condition
  688. drop procedure test_signal $$
  689. create procedure test_signal()
  690. begin
  691. # warning, no subclass
  692. DECLARE warn CONDITION FOR SQLSTATE "01000";
  693. SIGNAL warn;
  694. end $$
  695. call test_signal() $$
  696. Warnings:
  697. Warning 1642 Unhandled user-defined warning condition
  698. drop procedure test_signal $$
  699. create procedure test_signal()
  700. begin
  701. # warning, with subclass
  702. DECLARE warn CONDITION FOR SQLSTATE "01123";
  703. SIGNAL warn;
  704. end $$
  705. call test_signal() $$
  706. Warnings:
  707. Warning 1642 Unhandled user-defined warning condition
  708. drop procedure test_signal $$
  709. create procedure test_signal()
  710. begin
  711. # Not found, no subclass
  712. DECLARE not_found CONDITION FOR SQLSTATE "02000";
  713. SIGNAL not_found;
  714. end $$
  715. call test_signal() $$
  716. ERROR 02000: Unhandled user-defined not found condition
  717. drop procedure test_signal $$
  718. create procedure test_signal()
  719. begin
  720. # Not found, with subclass
  721. DECLARE not_found CONDITION FOR SQLSTATE "02XXX";
  722. SIGNAL not_found;
  723. end $$
  724. call test_signal() $$
  725. ERROR 02XXX: Unhandled user-defined not found condition
  726. drop procedure test_signal $$
  727. create procedure test_signal()
  728. begin
  729. # Error, no subclass
  730. DECLARE error CONDITION FOR SQLSTATE "12000";
  731. SIGNAL error;
  732. end $$
  733. call test_signal() $$
  734. ERROR 12000: Unhandled user-defined exception condition
  735. drop procedure test_signal $$
  736. create procedure test_signal()
  737. begin
  738. # Error, with subclass
  739. DECLARE error CONDITION FOR SQLSTATE "12ABC";
  740. SIGNAL error;
  741. end $$
  742. call test_signal() $$
  743. ERROR 12ABC: Unhandled user-defined exception condition
  744. drop procedure test_signal $$
  745. create procedure test_signal()
  746. begin
  747. # Severe error, no subclass
  748. DECLARE error CONDITION FOR SQLSTATE "40000";
  749. SIGNAL error;
  750. end $$
  751. call test_signal() $$
  752. ERROR 40000: Unhandled user-defined exception condition
  753. drop procedure test_signal $$
  754. create procedure test_signal()
  755. begin
  756. # Severe error, with subclass
  757. DECLARE error CONDITION FOR SQLSTATE "40001";
  758. SIGNAL error;
  759. end $$
  760. call test_signal() $$
  761. ERROR 40001: Unhandled user-defined exception condition
  762. drop procedure test_signal $$
  763. #
  764. # Test the scope of condition
  765. #
  766. create procedure test_signal()
  767. begin
  768. DECLARE foo CONDITION FOR SQLSTATE '99999';
  769. begin
  770. DECLARE foo CONDITION FOR 8888;
  771. end;
  772. SIGNAL foo SET MYSQL_ERRNO=9999; /* outer */
  773. end $$
  774. call test_signal() $$
  775. ERROR 99999: Unhandled user-defined exception condition
  776. drop procedure test_signal $$
  777. create procedure test_signal()
  778. begin
  779. DECLARE foo CONDITION FOR 9999;
  780. begin
  781. DECLARE foo CONDITION FOR SQLSTATE '88888';
  782. SIGNAL foo SET MYSQL_ERRNO=8888; /* inner */
  783. end;
  784. end $$
  785. call test_signal() $$
  786. ERROR 88888: Unhandled user-defined exception condition
  787. drop procedure test_signal $$
  788. #
  789. # Test SET MYSQL_ERRNO
  790. #
  791. create procedure test_signal()
  792. begin
  793. DECLARE foo CONDITION FOR SQLSTATE '99999';
  794. SIGNAL foo SET MYSQL_ERRNO = 1111;
  795. end $$
  796. call test_signal() $$
  797. ERROR 99999: Unhandled user-defined exception condition
  798. drop procedure test_signal $$
  799. create procedure test_signal()
  800. begin
  801. DECLARE warn CONDITION FOR SQLSTATE "01000";
  802. SIGNAL warn SET MYSQL_ERRNO = 1111;
  803. end $$
  804. call test_signal() $$
  805. Warnings:
  806. Warning 1111 Unhandled user-defined warning condition
  807. drop procedure test_signal $$
  808. create procedure test_signal()
  809. begin
  810. DECLARE not_found CONDITION FOR SQLSTATE "02000";
  811. SIGNAL not_found SET MYSQL_ERRNO = 1111;
  812. end $$
  813. call test_signal() $$
  814. ERROR 02000: Unhandled user-defined not found condition
  815. drop procedure test_signal $$
  816. create procedure test_signal()
  817. begin
  818. DECLARE error CONDITION FOR SQLSTATE "55000";
  819. SIGNAL error SET MYSQL_ERRNO = 1111;
  820. end $$
  821. call test_signal() $$
  822. ERROR 55000: Unhandled user-defined exception condition
  823. drop procedure test_signal $$
  824. #
  825. # Test SET MESSAGE_TEXT
  826. #
  827. SIGNAL SQLSTATE '77777' SET MESSAGE_TEXT='' $$
  828. ERROR 77777:
  829. create procedure test_signal()
  830. begin
  831. DECLARE foo CONDITION FOR SQLSTATE '77777';
  832. SIGNAL foo SET
  833. MESSAGE_TEXT = "",
  834. MYSQL_ERRNO=5678;
  835. end $$
  836. call test_signal() $$
  837. ERROR 77777:
  838. drop procedure test_signal $$
  839. create procedure test_signal()
  840. begin
  841. DECLARE foo CONDITION FOR SQLSTATE '99999';
  842. SIGNAL foo SET
  843. MESSAGE_TEXT = "Something bad happened",
  844. MYSQL_ERRNO=9999;
  845. end $$
  846. call test_signal() $$
  847. ERROR 99999: Something bad happened
  848. drop procedure test_signal $$
  849. create procedure test_signal()
  850. begin
  851. DECLARE warn CONDITION FOR SQLSTATE "01000";
  852. SIGNAL warn SET MESSAGE_TEXT = "Something bad happened";
  853. end $$
  854. call test_signal() $$
  855. Warnings:
  856. Warning 1642 Something bad happened
  857. drop procedure test_signal $$
  858. create procedure test_signal()
  859. begin
  860. DECLARE not_found CONDITION FOR SQLSTATE "02000";
  861. SIGNAL not_found SET MESSAGE_TEXT = "Something bad happened";
  862. end $$
  863. call test_signal() $$
  864. ERROR 02000: Something bad happened
  865. drop procedure test_signal $$
  866. create procedure test_signal()
  867. begin
  868. DECLARE error CONDITION FOR SQLSTATE "55000";
  869. SIGNAL error SET MESSAGE_TEXT = "Something bad happened";
  870. end $$
  871. call test_signal() $$
  872. ERROR 55000: Something bad happened
  873. drop procedure test_signal $$
  874. create procedure test_signal()
  875. begin
  876. DECLARE something CONDITION FOR SQLSTATE "01000";
  877. SIGNAL something SET MESSAGE_TEXT = _utf8 "This is a UTF8 text";
  878. end $$
  879. call test_signal() $$
  880. Warnings:
  881. Warning 1642 This is a UTF8 text
  882. drop procedure test_signal $$
  883. create procedure test_signal()
  884. begin
  885. DECLARE something CONDITION FOR SQLSTATE "01000";
  886. SIGNAL something SET MESSAGE_TEXT = "";
  887. end $$
  888. call test_signal() $$
  889. Warnings:
  890. Warning 1642
  891. drop procedure test_signal $$
  892. create procedure test_signal()
  893. begin
  894. DECLARE warn CONDITION FOR SQLSTATE "01111";
  895. SIGNAL warn SET MESSAGE_TEXT = "ĂĄ a";
  896. end $$
  897. call test_signal() $$
  898. Warnings:
  899. Warning 1642 ĂĄ a
  900. show warnings $$
  901. Level Code Message
  902. Warning 1642 ĂĄ a
  903. drop procedure test_signal $$
  904. #
  905. # Test SET complex expressions
  906. #
  907. create procedure test_signal()
  908. begin
  909. DECLARE error CONDITION FOR SQLSTATE '99999';
  910. SIGNAL error SET
  911. MYSQL_ERRNO = NULL;
  912. end $$
  913. call test_signal() $$
  914. ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of 'NULL'
  915. drop procedure test_signal $$
  916. create procedure test_signal()
  917. begin
  918. DECLARE error CONDITION FOR SQLSTATE '99999';
  919. SIGNAL error SET
  920. CLASS_ORIGIN = NULL;
  921. end $$
  922. call test_signal() $$
  923. ERROR 42000: Variable 'CLASS_ORIGIN' can't be set to the value of 'NULL'
  924. drop procedure test_signal $$
  925. create procedure test_signal()
  926. begin
  927. DECLARE error CONDITION FOR SQLSTATE '99999';
  928. SIGNAL error SET
  929. SUBCLASS_ORIGIN = NULL;
  930. end $$
  931. call test_signal() $$
  932. ERROR 42000: Variable 'SUBCLASS_ORIGIN' can't be set to the value of 'NULL'
  933. drop procedure test_signal $$
  934. create procedure test_signal()
  935. begin
  936. DECLARE error CONDITION FOR SQLSTATE '99999';
  937. SIGNAL error SET
  938. CONSTRAINT_CATALOG = NULL;
  939. end $$
  940. call test_signal() $$
  941. ERROR 42000: Variable 'CONSTRAINT_CATALOG' can't be set to the value of 'NULL'
  942. drop procedure test_signal $$
  943. create procedure test_signal()
  944. begin
  945. DECLARE error CONDITION FOR SQLSTATE '99999';
  946. SIGNAL error SET
  947. CONSTRAINT_SCHEMA = NULL;
  948. end $$
  949. call test_signal() $$
  950. ERROR 42000: Variable 'CONSTRAINT_SCHEMA' can't be set to the value of 'NULL'
  951. drop procedure test_signal $$
  952. create procedure test_signal()
  953. begin
  954. DECLARE error CONDITION FOR SQLSTATE '99999';
  955. SIGNAL error SET
  956. CONSTRAINT_NAME = NULL;
  957. end $$
  958. call test_signal() $$
  959. ERROR 42000: Variable 'CONSTRAINT_NAME' can't be set to the value of 'NULL'
  960. drop procedure test_signal $$
  961. create procedure test_signal()
  962. begin
  963. DECLARE error CONDITION FOR SQLSTATE '99999';
  964. SIGNAL error SET
  965. CATALOG_NAME = NULL;
  966. end $$
  967. call test_signal() $$
  968. ERROR 42000: Variable 'CATALOG_NAME' can't be set to the value of 'NULL'
  969. drop procedure test_signal $$
  970. create procedure test_signal()
  971. begin
  972. DECLARE error CONDITION FOR SQLSTATE '99999';
  973. SIGNAL error SET
  974. SCHEMA_NAME = NULL;
  975. end $$
  976. call test_signal() $$
  977. ERROR 42000: Variable 'SCHEMA_NAME' can't be set to the value of 'NULL'
  978. drop procedure test_signal $$
  979. create procedure test_signal()
  980. begin
  981. DECLARE error CONDITION FOR SQLSTATE '99999';
  982. SIGNAL error SET
  983. TABLE_NAME = NULL;
  984. end $$
  985. call test_signal() $$
  986. ERROR 42000: Variable 'TABLE_NAME' can't be set to the value of 'NULL'
  987. drop procedure test_signal $$
  988. create procedure test_signal()
  989. begin
  990. DECLARE error CONDITION FOR SQLSTATE '99999';
  991. SIGNAL error SET
  992. COLUMN_NAME = NULL;
  993. end $$
  994. call test_signal() $$
  995. ERROR 42000: Variable 'COLUMN_NAME' can't be set to the value of 'NULL'
  996. drop procedure test_signal $$
  997. create procedure test_signal()
  998. begin
  999. DECLARE error CONDITION FOR SQLSTATE '99999';
  1000. SIGNAL error SET
  1001. CURSOR_NAME = NULL;
  1002. end $$
  1003. call test_signal() $$
  1004. ERROR 42000: Variable 'CURSOR_NAME' can't be set to the value of 'NULL'
  1005. drop procedure test_signal $$
  1006. create procedure test_signal()
  1007. begin
  1008. DECLARE error CONDITION FOR SQLSTATE '99999';
  1009. SIGNAL error SET
  1010. MESSAGE_TEXT = NULL;
  1011. end $$
  1012. call test_signal() $$
  1013. ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'
  1014. drop procedure test_signal $$
  1015. create procedure test_signal()
  1016. begin
  1017. DECLARE something CONDITION FOR SQLSTATE '99999';
  1018. DECLARE message_text VARCHAR(64) DEFAULT "Local string variable";
  1019. DECLARE sqlcode INTEGER DEFAULT 1234;
  1020. SIGNAL something SET
  1021. MESSAGE_TEXT = message_text,
  1022. MYSQL_ERRNO = sqlcode;
  1023. end $$
  1024. call test_signal() $$
  1025. ERROR 99999: Local string variable
  1026. drop procedure test_signal $$
  1027. create procedure test_signal(message_text VARCHAR(64), sqlcode INTEGER)
  1028. begin
  1029. DECLARE something CONDITION FOR SQLSTATE "12345";
  1030. SIGNAL something SET
  1031. MESSAGE_TEXT = message_text,
  1032. MYSQL_ERRNO = sqlcode;
  1033. end $$
  1034. call test_signal("Parameter string", NULL) $$
  1035. ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of 'NULL'
  1036. call test_signal(NULL, 1234) $$
  1037. ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'
  1038. call test_signal("Parameter string", 5678) $$
  1039. ERROR 12345: Parameter string
  1040. drop procedure test_signal $$
  1041. create procedure test_signal()
  1042. begin
  1043. DECLARE something CONDITION FOR SQLSTATE "AABBB";
  1044. SIGNAL something SET
  1045. MESSAGE_TEXT = @message_text,
  1046. MYSQL_ERRNO = @sqlcode;
  1047. end $$
  1048. call test_signal() $$
  1049. ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'
  1050. set @sqlcode= 12 $$
  1051. call test_signal() $$
  1052. ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'
  1053. set @message_text= "User variable" $$
  1054. call test_signal() $$
  1055. ERROR AABBB: User variable
  1056. drop procedure test_signal $$
  1057. create procedure test_invalid()
  1058. begin
  1059. DECLARE something CONDITION FOR SQLSTATE "AABBB";
  1060. SIGNAL something SET
  1061. MESSAGE_TEXT = @message_text := 'illegal',
  1062. MYSQL_ERRNO = @sqlcode := 1234;
  1063. end $$
  1064. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
  1065. MYSQL_ERRNO = @sqlcode := 1234;
  1066. end' at line 5
  1067. create procedure test_signal()
  1068. begin
  1069. DECLARE aaa VARCHAR(64);
  1070. DECLARE bbb VARCHAR(64);
  1071. DECLARE ccc VARCHAR(64);
  1072. DECLARE ddd VARCHAR(64);
  1073. DECLARE eee VARCHAR(64);
  1074. DECLARE fff VARCHAR(64);
  1075. DECLARE ggg VARCHAR(64);
  1076. DECLARE hhh VARCHAR(64);
  1077. DECLARE iii VARCHAR(64);
  1078. DECLARE jjj VARCHAR(64);
  1079. DECLARE kkk VARCHAR(64);
  1080. DECLARE warn CONDITION FOR SQLSTATE "01234";
  1081. set aaa= repeat("A", 64);
  1082. set bbb= repeat("B", 64);
  1083. set ccc= repeat("C", 64);
  1084. set ddd= repeat("D", 64);
  1085. set eee= repeat("E", 64);
  1086. set fff= repeat("F", 64);
  1087. set ggg= repeat("G", 64);
  1088. set hhh= repeat("H", 64);
  1089. set iii= repeat("I", 64);
  1090. set jjj= repeat("J", 64);
  1091. set kkk= repeat("K", 64);
  1092. SIGNAL warn SET
  1093. CLASS_ORIGIN = aaa,
  1094. SUBCLASS_ORIGIN = bbb,
  1095. CONSTRAINT_CATALOG = ccc,
  1096. CONSTRAINT_SCHEMA = ddd,
  1097. CONSTRAINT_NAME = eee,
  1098. CATALOG_NAME = fff,
  1099. SCHEMA_NAME = ggg,
  1100. TABLE_NAME = hhh,
  1101. COLUMN_NAME = iii,
  1102. CURSOR_NAME = jjj,
  1103. MESSAGE_TEXT = kkk,
  1104. MYSQL_ERRNO = 65535;
  1105. end $$
  1106. call test_signal() $$
  1107. Warnings:
  1108. Warning 65535 KKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK
  1109. drop procedure test_signal $$
  1110. create procedure test_signal()
  1111. begin
  1112. DECLARE warn CONDITION FOR SQLSTATE "01234";
  1113. SIGNAL warn SET
  1114. MYSQL_ERRNO = 999999999999999999999999999999999999999999999999999;
  1115. end $$
  1116. call test_signal() $$
  1117. ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '999999999999999999999999999999999999999999999999999'
  1118. drop procedure test_signal $$
  1119. create procedure test_signal()
  1120. begin
  1121. DECLARE aaax VARCHAR(65);
  1122. DECLARE bbbx VARCHAR(65);
  1123. DECLARE cccx VARCHAR(65);
  1124. DECLARE dddx VARCHAR(65);
  1125. DECLARE eeex VARCHAR(65);
  1126. DECLARE fffx VARCHAR(65);
  1127. DECLARE gggx VARCHAR(65);
  1128. DECLARE hhhx VARCHAR(65);
  1129. DECLARE iiix VARCHAR(65);
  1130. DECLARE jjjx VARCHAR(65);
  1131. DECLARE kkkx VARCHAR(65);
  1132. DECLARE lllx VARCHAR(129);
  1133. DECLARE warn CONDITION FOR SQLSTATE "01234";
  1134. set aaax= concat(repeat("A", 64), "X");
  1135. set bbbx= concat(repeat("B", 64), "X");
  1136. set cccx= concat(repeat("C", 64), "X");
  1137. set dddx= concat(repeat("D", 64), "X");
  1138. set eeex= concat(repeat("E", 64), "X");
  1139. set fffx= concat(repeat("F", 64), "X");
  1140. set gggx= concat(repeat("G", 64), "X");
  1141. set hhhx= concat(repeat("H", 64), "X");
  1142. set iiix= concat(repeat("I", 64), "X");
  1143. set jjjx= concat(repeat("J", 64), "X");
  1144. set kkkx= concat(repeat("K", 64), "X");
  1145. set lllx= concat(repeat("1", 100),
  1146. repeat("2", 20),
  1147. repeat("8", 8),
  1148. "X");
  1149. SIGNAL warn SET
  1150. CLASS_ORIGIN = aaax,
  1151. SUBCLASS_ORIGIN = bbbx,
  1152. CONSTRAINT_CATALOG = cccx,
  1153. CONSTRAINT_SCHEMA = dddx,
  1154. CONSTRAINT_NAME = eeex,
  1155. CATALOG_NAME = fffx,
  1156. SCHEMA_NAME = gggx,
  1157. TABLE_NAME = hhhx,
  1158. COLUMN_NAME = iiix,
  1159. CURSOR_NAME = jjjx,
  1160. MESSAGE_TEXT = lllx,
  1161. MYSQL_ERRNO = 10000;
  1162. end $$
  1163. call test_signal() $$
  1164. Warnings:
  1165. Warning 1647 Data truncated for condition item 'CLASS_ORIGIN'
  1166. Warning 1647 Data truncated for condition item 'SUBCLASS_ORIGIN'
  1167. Warning 1647 Data truncated for condition item 'CONSTRAINT_CATALOG'
  1168. Warning 1647 Data truncated for condition item 'CONSTRAINT_SCHEMA'
  1169. Warning 1647 Data truncated for condition item 'CONSTRAINT_NAME'
  1170. Warning 1647 Data truncated for condition item 'CATALOG_NAME'
  1171. Warning 1647 Data truncated for condition item 'SCHEMA_NAME'
  1172. Warning 1647 Data truncated for condition item 'TABLE_NAME'
  1173. Warning 1647 Data truncated for condition item 'COLUMN_NAME'
  1174. Warning 1647 Data truncated for condition item 'CURSOR_NAME'
  1175. Warning 1647 Data truncated for condition item 'MESSAGE_TEXT'
  1176. Warning 10000 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112222222222222222222288888888
  1177. drop procedure test_signal $$
  1178. create procedure test_signal()
  1179. begin
  1180. DECLARE warn CONDITION FOR SQLSTATE "01234";
  1181. DECLARE CONTINUE HANDLER for SQLSTATE "01234"
  1182. begin
  1183. select "Caught by SQLSTATE";
  1184. end;
  1185. SIGNAL warn SET
  1186. MESSAGE_TEXT = "Raising a warning",
  1187. MYSQL_ERRNO = 1012;
  1188. end $$
  1189. call test_signal() $$
  1190. Caught by SQLSTATE
  1191. Caught by SQLSTATE
  1192. Warnings:
  1193. Warning 1012 Raising a warning
  1194. drop procedure test_signal $$
  1195. create procedure test_signal()
  1196. begin
  1197. DECLARE warn CONDITION FOR SQLSTATE "01234";
  1198. DECLARE CONTINUE HANDLER for 1012
  1199. begin
  1200. select "Caught by number";
  1201. end;
  1202. SIGNAL warn SET
  1203. MESSAGE_TEXT = "Raising a warning",
  1204. MYSQL_ERRNO = 1012;
  1205. end $$
  1206. call test_signal() $$
  1207. Caught by number
  1208. Caught by number
  1209. Warnings:
  1210. Warning 1012 Raising a warning
  1211. drop procedure test_signal $$
  1212. create procedure test_signal()
  1213. begin
  1214. DECLARE warn CONDITION FOR SQLSTATE "01234";
  1215. DECLARE CONTINUE HANDLER for SQLWARNING
  1216. begin
  1217. select "Caught by SQLWARNING";
  1218. end;
  1219. SIGNAL warn SET
  1220. MESSAGE_TEXT = "Raising a warning",
  1221. MYSQL_ERRNO = 1012;
  1222. end $$
  1223. call test_signal() $$
  1224. Caught by SQLWARNING
  1225. Caught by SQLWARNING
  1226. Warnings:
  1227. Warning 1012 Raising a warning
  1228. drop procedure test_signal $$
  1229. create procedure test_signal()
  1230. begin
  1231. DECLARE not_found CONDITION FOR SQLSTATE "02ABC";
  1232. DECLARE CONTINUE HANDLER for SQLSTATE "02ABC"
  1233. begin
  1234. select "Caught by SQLSTATE";
  1235. end;
  1236. SIGNAL not_found SET
  1237. MESSAGE_TEXT = "Raising a not found",
  1238. MYSQL_ERRNO = 1012;
  1239. end $$
  1240. call test_signal() $$
  1241. Caught by SQLSTATE
  1242. Caught by SQLSTATE
  1243. Warnings:
  1244. Error 1012 Raising a not found
  1245. drop procedure test_signal $$
  1246. create procedure test_signal()
  1247. begin
  1248. DECLARE not_found CONDITION FOR SQLSTATE "02ABC";
  1249. DECLARE CONTINUE HANDLER for 1012
  1250. begin
  1251. select "Caught by number";
  1252. end;
  1253. SIGNAL not_found SET
  1254. MESSAGE_TEXT = "Raising a not found",
  1255. MYSQL_ERRNO = 1012;
  1256. end $$
  1257. call test_signal() $$
  1258. Caught by number
  1259. Caught by number
  1260. Warnings:
  1261. Error 1012 Raising a not found
  1262. drop procedure test_signal $$
  1263. create procedure test_signal()
  1264. begin
  1265. DECLARE not_found CONDITION FOR SQLSTATE "02ABC";
  1266. DECLARE CONTINUE HANDLER for NOT FOUND
  1267. begin
  1268. select "Caught by NOT FOUND";
  1269. end;
  1270. SIGNAL not_found SET
  1271. MESSAGE_TEXT = "Raising a not found",
  1272. MYSQL_ERRNO = 1012;
  1273. end $$
  1274. call test_signal() $$
  1275. Caught by NOT FOUND
  1276. Caught by NOT FOUND
  1277. Warnings:
  1278. Error 1012 Raising a not found
  1279. drop procedure test_signal $$
  1280. create procedure test_signal()
  1281. begin
  1282. DECLARE error CONDITION FOR SQLSTATE "55555";
  1283. DECLARE CONTINUE HANDLER for SQLSTATE "55555"
  1284. begin
  1285. select "Caught by SQLSTATE";
  1286. end;
  1287. SIGNAL error SET
  1288. MESSAGE_TEXT = "Raising an error",
  1289. MYSQL_ERRNO = 1012;
  1290. end $$
  1291. call test_signal() $$
  1292. Caught by SQLSTATE
  1293. Caught by SQLSTATE
  1294. Warnings:
  1295. Error 1012 Raising an error
  1296. drop procedure test_signal $$
  1297. create procedure test_signal()
  1298. begin
  1299. DECLARE error CONDITION FOR SQLSTATE "55555";
  1300. DECLARE CONTINUE HANDLER for 1012
  1301. begin
  1302. select "Caught by number";
  1303. end;
  1304. SIGNAL error SET
  1305. MESSAGE_TEXT = "Raising an error",
  1306. MYSQL_ERRNO = 1012;
  1307. end $$
  1308. call test_signal() $$
  1309. Caught by number
  1310. Caught by number
  1311. Warnings:
  1312. Error 1012 Raising an error
  1313. drop procedure test_signal $$
  1314. create procedure test_signal()
  1315. begin
  1316. DECLARE error CONDITION FOR SQLSTATE "55555";
  1317. DECLARE CONTINUE HANDLER for SQLEXCEPTION
  1318. begin
  1319. select "Caught by SQLEXCEPTION";
  1320. end;
  1321. SIGNAL error SET
  1322. MESSAGE_TEXT = "Raising an error",
  1323. MYSQL_ERRNO = 1012;
  1324. end $$
  1325. call test_signal() $$
  1326. Caught by SQLEXCEPTION
  1327. Caught by SQLEXCEPTION
  1328. Warnings:
  1329. Error 1012 Raising an error
  1330. drop procedure test_signal $$
  1331. #
  1332. # Test where SIGNAL can be used
  1333. #
  1334. create function test_signal_func() returns integer
  1335. begin
  1336. DECLARE warn CONDITION FOR SQLSTATE "01XXX";
  1337. SIGNAL warn SET
  1338. MESSAGE_TEXT = "This function SIGNAL a warning",
  1339. MYSQL_ERRNO = 1012;
  1340. return 5;
  1341. end $$
  1342. select test_signal_func() $$
  1343. test_signal_func()
  1344. 5
  1345. Warnings:
  1346. Warning 1012 This function SIGNAL a warning
  1347. drop function test_signal_func $$
  1348. create function test_signal_func() returns integer
  1349. begin
  1350. DECLARE not_found CONDITION FOR SQLSTATE "02XXX";
  1351. SIGNAL not_found SET
  1352. MESSAGE_TEXT = "This function SIGNAL not found",
  1353. MYSQL_ERRNO = 1012;
  1354. return 5;
  1355. end $$
  1356. select test_signal_func() $$
  1357. ERROR 02XXX: This function SIGNAL not found
  1358. drop function test_signal_func $$
  1359. create function test_signal_func() returns integer
  1360. begin
  1361. DECLARE error CONDITION FOR SQLSTATE "50000";
  1362. SIGNAL error SET
  1363. MESSAGE_TEXT = "This function SIGNAL an error",
  1364. MYSQL_ERRNO = 1012;
  1365. return 5;
  1366. end $$
  1367. select test_signal_func() $$
  1368. ERROR 50000: This function SIGNAL an error
  1369. drop function test_signal_func $$
  1370. drop table if exists t1 $$
  1371. create table t1 (a integer) $$
  1372. create trigger t1_ai after insert on t1 for each row
  1373. begin
  1374. DECLARE msg VARCHAR(128);
  1375. DECLARE warn CONDITION FOR SQLSTATE "01XXX";
  1376. set msg= concat("This trigger SIGNAL a warning, a=", NEW.a);
  1377. SIGNAL warn SET
  1378. MESSAGE_TEXT = msg,
  1379. MYSQL_ERRNO = 1012;
  1380. end $$
  1381. insert into t1 values (1), (2) $$
  1382. drop trigger t1_ai $$
  1383. create trigger t1_ai after insert on t1 for each row
  1384. begin
  1385. DECLARE msg VARCHAR(128);
  1386. DECLARE not_found CONDITION FOR SQLSTATE "02XXX";
  1387. set msg= concat("This trigger SIGNAL a not found, a=", NEW.a);
  1388. SIGNAL not_found SET
  1389. MESSAGE_TEXT = msg,
  1390. MYSQL_ERRNO = 1012;
  1391. end $$
  1392. insert into t1 values (3), (4) $$
  1393. ERROR 02XXX: This trigger SIGNAL a not found, a=3
  1394. drop trigger t1_ai $$
  1395. create trigger t1_ai after insert on t1 for each row
  1396. begin
  1397. DECLARE msg VARCHAR(128);
  1398. DECLARE error CONDITION FOR SQLSTATE "03XXX";
  1399. set msg= concat("This trigger SIGNAL an error, a=", NEW.a);
  1400. SIGNAL error SET
  1401. MESSAGE_TEXT = msg,
  1402. MYSQL_ERRNO = 1012;
  1403. end $$
  1404. insert into t1 values (5), (6) $$
  1405. ERROR 03XXX: This trigger SIGNAL an error, a=5
  1406. drop table t1 $$
  1407. create table t1 (errno integer, msg varchar(128)) $$
  1408. create trigger t1_ai after insert on t1 for each row
  1409. begin
  1410. DECLARE warn CONDITION FOR SQLSTATE "01XXX";
  1411. SIGNAL warn SET
  1412. MESSAGE_TEXT = NEW.msg,
  1413. MYSQL_ERRNO = NEW.errno;
  1414. end $$
  1415. insert into t1 set errno=1012, msg='Warning message 1 in trigger' $$
  1416. insert into t1 set errno=1013, msg='Warning message 2 in trigger' $$
  1417. drop table t1 $$
  1418. drop table if exists t1 $$
  1419. drop procedure if exists p1 $$
  1420. drop function if exists f1 $$
  1421. create table t1 (s1 int) $$
  1422. insert into t1 values (1) $$
  1423. create procedure p1()
  1424. begin
  1425. declare a int;
  1426. declare c cursor for select f1() from t1;
  1427. declare continue handler for sqlstate '03000'
  1428. select "caught 03000";
  1429. declare continue handler for 1326
  1430. select "caught cursor is not open";
  1431. select "Before open";
  1432. open c;
  1433. select "Before fetch";
  1434. fetch c into a;
  1435. select "Before close";
  1436. close c;
  1437. end $$
  1438. create function f1() returns int
  1439. begin
  1440. signal sqlstate '03000';
  1441. return 5;
  1442. end $$
  1443. drop table t1 $$
  1444. drop procedure p1 $$
  1445. drop function f1 $$
  1446. #
  1447. # Test the RESIGNAL runtime
  1448. #
  1449. create procedure test_resignal()
  1450. begin
  1451. DECLARE warn CONDITION FOR SQLSTATE "01234";
  1452. DECLARE CONTINUE HANDLER for 1012
  1453. begin
  1454. select "before RESIGNAL";
  1455. RESIGNAL;
  1456. select "after RESIGNAL";
  1457. end;
  1458. SIGNAL warn SET
  1459. MESSAGE_TEXT = "Raising a warning",
  1460. MYSQL_ERRNO = 1012;
  1461. end $$
  1462. call test_resignal() $$
  1463. before RESIGNAL
  1464. before RESIGNAL
  1465. after RESIGNAL
  1466. after RESIGNAL
  1467. Warnings:
  1468. Warning 1012 Raising a warning
  1469. Warning 1012 Raising a warning
  1470. drop procedure test_resignal $$
  1471. create procedure test_resignal()
  1472. begin
  1473. DECLARE not_found CONDITION FOR SQLSTATE "02222";
  1474. DECLARE CONTINUE HANDLER for 1012
  1475. begin
  1476. select "before RESIGNAL";
  1477. RESIGNAL;
  1478. select "after RESIGNAL";
  1479. end;
  1480. SIGNAL not_found SET
  1481. MESSAGE_TEXT = "Raising a not found",
  1482. MYSQL_ERRNO = 1012;
  1483. end $$
  1484. call test_resignal() $$
  1485. before RESIGNAL
  1486. before RESIGNAL
  1487. ERROR 02222: Raising a not found
  1488. drop procedure test_resignal $$
  1489. create procedure test_resignal()
  1490. begin
  1491. DECLARE error CONDITION FOR SQLSTATE "55555";
  1492. DECLARE CONTINUE HANDLER for 1012
  1493. begin
  1494. select "before RESIGNAL";
  1495. RESIGNAL;
  1496. select "after RESIGNAL";
  1497. end;
  1498. SIGNAL error SET
  1499. MESSAGE_TEXT = "Raising an error",
  1500. MYSQL_ERRNO = 1012;
  1501. end $$
  1502. call test_resignal() $$
  1503. before RESIGNAL
  1504. before RESIGNAL
  1505. ERROR 55555: Raising an error
  1506. drop procedure test_resignal $$
  1507. create procedure test_resignal()
  1508. begin
  1509. DECLARE CONTINUE HANDLER for sqlwarning
  1510. begin
  1511. select "before RESIGNAL";
  1512. RESIGNAL;
  1513. select "after RESIGNAL";
  1514. end;
  1515. insert into t_warn set a= 9999999999999999;
  1516. end $$
  1517. call test_resignal() $$
  1518. before RESIGNAL
  1519. before RESIGNAL
  1520. after RESIGNAL
  1521. after RESIGNAL
  1522. Warnings:
  1523. Warning 1264 Out of range value for column 'a' at row 1
  1524. Warning 1264 Out of range value for column 'a' at row 1
  1525. drop procedure test_resignal $$
  1526. create procedure test_resignal()
  1527. begin
  1528. DECLARE x integer;
  1529. DECLARE c cursor for select * from t_cursor;
  1530. DECLARE CONTINUE HANDLER for not found
  1531. begin
  1532. select "before RESIGNAL";
  1533. RESIGNAL;
  1534. select "after RESIGNAL";
  1535. end;
  1536. open c;
  1537. fetch c into x;
  1538. close c;
  1539. end $$
  1540. call test_resignal() $$
  1541. before RESIGNAL
  1542. before RESIGNAL
  1543. ERROR 02000: No data - zero rows fetched, selected, or processed
  1544. drop procedure test_resignal $$
  1545. create procedure test_resignal()
  1546. begin
  1547. DECLARE CONTINUE HANDLER for sqlexception
  1548. begin
  1549. select "before RESIGNAL";
  1550. RESIGNAL;
  1551. select "after RESIGNAL";
  1552. end;
  1553. drop table no_such_table;
  1554. end $$
  1555. call test_resignal() $$
  1556. before RESIGNAL
  1557. before RESIGNAL
  1558. ERROR 42S02: Unknown table 'no_such_table'
  1559. drop procedure test_resignal $$
  1560. create procedure test_resignal()
  1561. begin
  1562. DECLARE warn CONDITION FOR SQLSTATE "01234";
  1563. DECLARE CONTINUE HANDLER for 1012
  1564. begin
  1565. select "before RESIGNAL";
  1566. RESIGNAL SET
  1567. MESSAGE_TEXT = "RESIGNAL of a warning",
  1568. MYSQL_ERRNO = 5555 ;
  1569. select "after RESIGNAL";
  1570. end;
  1571. SIGNAL warn SET
  1572. MESSAGE_TEXT = "Raising a warning",
  1573. MYSQL_ERRNO = 1012;
  1574. end $$
  1575. call test_resignal() $$
  1576. before RESIGNAL
  1577. before RESIGNAL
  1578. after RESIGNAL
  1579. after RESIGNAL
  1580. Warnings:
  1581. Warning 1012 Raising a warning
  1582. Warning 5555 RESIGNAL of a warning
  1583. drop procedure test_resignal $$
  1584. create procedure test_resignal()
  1585. begin
  1586. DECLARE not_found CONDITION FOR SQLSTATE "02111";
  1587. DECLARE CONTINUE HANDLER for 1012
  1588. begin
  1589. select "before RESIGNAL";
  1590. RESIGNAL SET
  1591. MESSAGE_TEXT = "RESIGNAL of a not found",
  1592. MYSQL_ERRNO = 5555 ;
  1593. select "after RESIGNAL";
  1594. end;
  1595. SIGNAL not_found SET
  1596. MESSAGE_TEXT = "Raising a not found",
  1597. MYSQL_ERRNO = 1012;
  1598. end $$
  1599. call test_resignal() $$
  1600. before RESIGNAL
  1601. before RESIGNAL
  1602. ERROR 02111: RESIGNAL of a not found
  1603. drop procedure test_resignal $$
  1604. create procedure test_resignal()
  1605. begin
  1606. DECLARE error CONDITION FOR SQLSTATE "33333";
  1607. DECLARE CONTINUE HANDLER for 1012
  1608. begin
  1609. select "before RESIGNAL";
  1610. RESIGNAL SET
  1611. MESSAGE_TEXT = "RESIGNAL of an error",
  1612. MYSQL_ERRNO = 5555 ;
  1613. select "after RESIGNAL";
  1614. end;
  1615. SIGNAL error SET
  1616. MESSAGE_TEXT = "Raising an error",
  1617. MYSQL_ERRNO = 1012;
  1618. end $$
  1619. call test_resignal() $$
  1620. before RESIGNAL
  1621. before RESIGNAL
  1622. ERROR 33333: RESIGNAL of an error
  1623. drop procedure test_resignal $$
  1624. create procedure test_resignal()
  1625. begin
  1626. DECLARE CONTINUE HANDLER for sqlwarning
  1627. begin
  1628. select "before RESIGNAL";
  1629. RESIGNAL SET
  1630. MESSAGE_TEXT = "RESIGNAL of a warning",
  1631. MYSQL_ERRNO = 5555 ;
  1632. select "after RESIGNAL";
  1633. end;
  1634. insert into t_warn set a= 9999999999999999;
  1635. end $$
  1636. call test_resignal() $$
  1637. before RESIGNAL
  1638. before RESIGNAL
  1639. after RESIGNAL
  1640. after RESIGNAL
  1641. Warnings:
  1642. Warning 1264 Out of range value for column 'a' at row 1
  1643. Warning 5555 RESIGNAL of a warning
  1644. drop procedure test_resignal $$
  1645. create procedure test_resignal()
  1646. begin
  1647. DECLARE x integer;
  1648. DECLARE c cursor for select * from t_cursor;
  1649. DECLARE CONTINUE HANDLER for not found
  1650. begin
  1651. select "before RESIGNAL";
  1652. RESIGNAL SET
  1653. MESSAGE_TEXT = "RESIGNAL of not found",
  1654. MYSQL_ERRNO = 5555 ;
  1655. select "after RESIGNAL";
  1656. end;
  1657. open c;
  1658. fetch c into x;
  1659. close c;
  1660. end $$
  1661. call test_resignal() $$
  1662. before RESIGNAL
  1663. before RESIGNAL
  1664. ERROR 02000: RESIGNAL of not found
  1665. drop procedure test_resignal $$
  1666. create procedure test_resignal()
  1667. begin
  1668. DECLARE CONTINUE HANDLER for sqlexception
  1669. begin
  1670. select "before RESIGNAL";
  1671. RESIGNAL SET
  1672. MESSAGE_TEXT = "RESIGNAL of an error",
  1673. MYSQL_ERRNO = 5555 ;
  1674. select "after RESIGNAL";
  1675. end;
  1676. drop table no_such_table;
  1677. end $$
  1678. call test_resignal() $$
  1679. before RESIGNAL
  1680. before RESIGNAL
  1681. ERROR 42S02: RESIGNAL of an error
  1682. drop procedure test_resignal $$
  1683. create procedure test_resignal()
  1684. begin
  1685. DECLARE warn CONDITION FOR SQLSTATE "01111";
  1686. DECLARE CONTINUE HANDLER for 1012
  1687. begin
  1688. select "before RESIGNAL";
  1689. RESIGNAL SQLSTATE "01222" SET
  1690. MESSAGE_TEXT = "RESIGNAL to warning",
  1691. MYSQL_ERRNO = 5555 ;
  1692. select "after RESIGNAL";
  1693. end;
  1694. SIGNAL warn SET
  1695. MESSAGE_TEXT = "Raising a warning",
  1696. MYSQL_ERRNO = 1012;
  1697. end $$
  1698. call test_resignal() $$
  1699. before RESIGNAL
  1700. before RESIGNAL
  1701. after RESIGNAL
  1702. after RESIGNAL
  1703. Warnings:
  1704. Warning 1012 Raising a warning
  1705. Warning 5555 RESIGNAL to warning
  1706. drop procedure test_resignal $$
  1707. create procedure test_resignal()
  1708. begin
  1709. DECLARE warn CONDITION FOR SQLSTATE "01111";
  1710. DECLARE CONTINUE HANDLER for 1012
  1711. begin
  1712. select "before RESIGNAL";
  1713. RESIGNAL SQLSTATE "02222" SET
  1714. MESSAGE_TEXT = "RESIGNAL to not found",
  1715. MYSQL_ERRNO = 5555 ;
  1716. select "after RESIGNAL";
  1717. end;
  1718. SIGNAL warn SET
  1719. MESSAGE_TEXT = "Raising a warning",
  1720. MYSQL_ERRNO = 1012;
  1721. end $$
  1722. call test_resignal() $$
  1723. before RESIGNAL
  1724. before RESIGNAL
  1725. ERROR 02222: RESIGNAL to not found
  1726. show warnings $$
  1727. Level Code Message
  1728. Warning 1012 Raising a warning
  1729. Error 5555 RESIGNAL to not found
  1730. drop procedure test_resignal $$
  1731. create procedure test_resignal()
  1732. begin
  1733. DECLARE warn CONDITION FOR SQLSTATE "01111";
  1734. DECLARE CONTINUE HANDLER for 1012
  1735. begin
  1736. select "before RESIGNAL";
  1737. RESIGNAL SQLSTATE "33333" SET
  1738. MESSAGE_TEXT = "RESIGNAL to error",
  1739. MYSQL_ERRNO = 5555 ;
  1740. select "after RESIGNAL";
  1741. end;
  1742. SIGNAL warn SET
  1743. MESSAGE_TEXT = "Raising a warning",
  1744. MYSQL_ERRNO = 1012;
  1745. end $$
  1746. call test_resignal() $$
  1747. before RESIGNAL
  1748. before RESIGNAL
  1749. ERROR 33333: RESIGNAL to error
  1750. show warnings $$
  1751. Level Code Message
  1752. Warning 1012 Raising a warning
  1753. Error 5555 RESIGNAL to error
  1754. drop procedure test_resignal $$
  1755. create procedure test_resignal()
  1756. begin
  1757. DECLARE not_found CONDITION FOR SQLSTATE "02ABC";
  1758. DECLARE CONTINUE HANDLER for 1012
  1759. begin
  1760. select "before RESIGNAL";
  1761. RESIGNAL SQLSTATE "01222" SET
  1762. MESSAGE_TEXT = "RESIGNAL to warning",
  1763. MYSQL_ERRNO = 5555 ;
  1764. select "after RESIGNAL";
  1765. end;
  1766. SIGNAL not_found SET
  1767. MESSAGE_TEXT = "Raising a not found",
  1768. MYSQL_ERRNO = 1012;
  1769. end $$
  1770. call test_resignal() $$
  1771. before RESIGNAL
  1772. before RESIGNAL
  1773. after RESIGNAL
  1774. after RESIGNAL
  1775. Warnings:
  1776. Error 1012 Raising a not found
  1777. Warning 5555 RESIGNAL to warning
  1778. drop procedure test_resignal $$
  1779. create procedure test_resignal()
  1780. begin
  1781. DECLARE not_found CONDITION FOR SQLSTATE "02ABC";
  1782. DECLARE CONTINUE HANDLER for 1012
  1783. begin
  1784. select "before RESIGNAL";
  1785. RESIGNAL SQLSTATE "02222" SET
  1786. MESSAGE_TEXT = "RESIGNAL to not found",
  1787. MYSQL_ERRNO = 5555 ;
  1788. select "after RESIGNAL";
  1789. end;
  1790. SIGNAL not_found SET
  1791. MESSAGE_TEXT = "Raising a not found",
  1792. MYSQL_ERRNO = 1012;
  1793. end $$
  1794. call test_resignal() $$
  1795. before RESIGNAL
  1796. before RESIGNAL
  1797. ERROR 02222: RESIGNAL to not found
  1798. show warnings $$
  1799. Level Code Message
  1800. Error 1012 Raising a

Large files files are truncated, but you can click here to view the full file