Bug #71610 SIGNAL sqlstates should be interpreted as their literal state

• Submitted: 6 Feb 2014 17:14
• Reporter: Trey Raymond
• Status: Open
• Category: MySQL Server: Stored Routines
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any
• Tags: signal

[6 Feb 2014 17:14] Trey Raymond

Description:
When a state is called by a SIGNAL command, mysql should treat it like it normally treats that state (otherwise why use anything but 45000, eh?).

See the "how to repeat" for details.  Input could be controlled and sanitized by the trigger.  currently, even though the state/error are duplicate key, the statement will terminate on any signal without a manually specified handler.  this is a feature we could use here at yahoo, and I'm sure many others would like to as well (search for people trying to use triggers to skip/sanitize, you'll see a lot, the solutions that work these days are really hacky and slower, like subbing in an existing row).

How to repeat:
Example...
table A has a BEFORE INSERT trigger.  the trigger:
if NEW.column2='skip' then
SIGNAL SQLSTATE '23000' SET MYSQL_ERRNO = 1022;
end if;

then a process that did, for example, a load data infile ignore:

LOAD DATA LOCAL INFILE '/tmp/file' IGNORE INTO TABLE A (column1,column2);

with records:
1 awesome
2 cool
3 skip
4 okay again
5 not bad
6 skip
etc...

Suggested fix:
Have mysql treat SIGNAL error states as it would if encountering the actual state.