Is there a way to run exception code in mysql

Question

say that i run a query that copies 100 records that was marked to be copied (out of 200) into a temp table then copy thew contents of that temp table into another table, now during this query, one of the 100 records causes a primary key violation

now if this happens i want to run another query which adds an entry to an error log table and delete all the data from that may have been copied from the temp table and clear the temp table itself

i'm wondering if i can throw an exception in MySQL so it can run my alternate code

NOTE: i do not want to use a transaction as it locks tables and i have other queries that run at the same time that need to read/write the the first table (they dont touch the records that have been marked to be copied thank to a flag that indicates that they've been marked)

Erin · Accepted Answer · 2013-05-17 01:59:37Z

1

You'll want to use an exit handler for that if writing this solely in SQL! There is a duplicate key violation example on that page. Handlers can only be used in stored procedures, but to log the occurrence to a table, you'll want to use a procedure, anyway. You'd call a stored procedure for each of the records you want to copy. Then, if one of those fails, have a record be inserted into a log table with maybe the id that caused the violation.

This is less efficient, however, than having the application read the potential MySQL error and writing it to a log file, as you could make use of a bulk INSERT into the temporary table.

answered May 17, 2013 at 1:59

Erin

5393 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is there a way to run exception code in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related