Archive for the ‘MyISAM’ Category
MySQL Triggers with Logging
Somebody asked why you can’t implement MySQL triggers that write information when you want to stop the DML statement, like autonomous procedures in Oracle. The question was a surprise but I didn’t find anything on it, so here’s how you can do it. This is more or less like an autonomous process by leveraging both the InnoDB and MyISAM engine’s behaviors. This post leverages an earlier explanation of MySQL Triggers.
- First you create a MyISAM table, which is a persistent store that auto commits when you’re other InnoDB tables can be transactionally dependent. Here’s a simple MyISAM
logger
table.
CREATE TABLE logger ( logger_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY , logger_event VARCHAR(50) , logger_table VARCHAR(50) , logger_instring VARCHAR(100) , logger_outstring VARCHAR(100) , created_by INT UNSIGNED , creation_date DATE , last_updated_by INT UNSIGNED , last_update_date DATE) ENGINE=MyISAM; |
- Next, you create an on-insert trigger that changes an input but doesn’t stop the transaction. It also writes to the logger MyISAM table in the scope of the transaction.
CREATE TRIGGER contact_insert BEFORE INSERT ON contact FOR EACH ROW BEGIN /* Check if last name contains a white space. */ IF new.last_name REGEXP '^.* .*$' THEN /* Insert into an MyISAM table, which auto commits in the scope of a transaction. */ INSERT INTO logger VALUES ( null ,'insert' ,'contact' , new.last_name , REPLACE(new.last_name,' ','-') , new.created_by , new.creation_date , new.last_updated_by , new.last_update_date ); /* Replace the name for the INSERT INTO the CONTACT table. */ SET new.last_name := REPLACE(new.last_name,' ','-'); END IF; END; $$ |
- Next, you create an on-update trigger that changes an update while aborting the transaction. It also writes to the logger MyISAM table because its outside the InnoDB scope of a transaction and auto committed on insert.
CREATE TRIGGER contact_update BEFORE UPDATE ON contact FOR EACH ROW BEGIN /* Check if last name contains a white space. */ IF new.last_name REGEXP '^.* .*$' THEN /* Insert into an MyISAM table, which auto commits in the scope of a transaction. */ INSERT INTO logger VALUES ( null ,'update' ,'contact' , new.last_name , null , old.created_by , old.creation_date , new.last_updated_by , new.last_update_date ); /* Throw an exception to force the business user to see they can't update a last name with a white space. */ SIGNAL SQLSTATE '42000'; END IF; END; $$ |
- Next, you create a test case with an
INSERT
andUPDATE
statement that meets the condition of the triggers.
/* Insert a row meeting the trigger condition. */ INSERT INTO contact VALUES ( null, 1001, 1003,'Catherine', null,'Zeta Jones', 1001, UTC_DATE(), 1001, UTC_DATE()); /* Update a row meeting the trigger condition. */ UPDATE contact SET last_name = 'Zeta Jones' , last_updated_by = 1003 , last_update_date = UTC_DATE() WHERE last_name = 'Zeta-Jones'; |
- Last, query the logger table. You have a record inserted for both the allowed behavior and the aborted behavior. This means you have the ability to capture material that should never be inserted or updated into a table and who did it by leveraging the who-audit columns of the table.
SELECT * FROM logger; |
It returns:
+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+ | logger_id | logger_event | logger_table | logger_instring | logger_outstring | created_by | creation_date | last_updated_by | last_update_date | +-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+ | 1 | insert | contact | Zeta Jones | Zeta-Jones | 1001 | 2013-04-26 | 1001 | 2013-04-26 | | 2 | update | contact | Zeta Jones | NULL | 1001 | 2013-04-26 | 1003 | 2013-04-26 | +-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+ 2 rows in set (0.00 sec) |
This effectively delivers in MySQL the equivalent of an autonomous transaction in Oracle. The result from the non-critical trigger records the before and after value, while the results from the critical update trigger only record the before values because the event is aborted by raising an error in the trigger. As always, I hope this helps somebody looking for a solution.
Delay or synchronize it?
A couple students in one of my classes ran into a problem when competing Java threads tried to insert new rows in a table. They raised an error when they tried the DELAY
keyword to avoid the race (collision) condition in an INSERT
statement. It was simple to explain to them that the DELAY
keyword doesn’t work with an InnoDB table. Any attempt throws the following error:
ERROR 1616 (HY000): DELAYED OPTION NOT supported FOR TABLE 'message' |
Important Update: INSERT DELAYED
is gone in MySQL 5.6.6 (announcement) and the whole issue comes down to synchronizing threads (some dislike the solution) or using the ON DUPLICATE KEY
clause.
They retested their Java application after redefining the target table using the MyISAM engine. They found it worked but that’s a bad fix in Java (a brief Java/MySQL tutorial post). They really needed to synchronize the Java thread (line #22), leave out the DELAY
keyword, and manage the table with the InnoDB engine. Here’s the modified Java code (by the way, they named their project VulcanTech if you’re wondering about the packages in the import statement):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | package vulcantech.vth.server.commands; import java.io.IOException; import java.net.Socket; import vulcantech.vth.server.combeans.MessageBean; public class MessageHandler implements Handler { @Override public void handleIt(Object... args) { MessageBean message = (MessageBean) args[0]; Socket sock = (Socket) args[1]; DatabaseConnection dbconnection = new DatabaseConnection(); String update = new String("INSERT INTO message(message_timestamp, sender, recipient, message, checked) VALUES(\'" + message.getTimeStamp() + "\', \'" + message.getSender() + "\', \'" + message.getRecipient() + "\', \'" + message.getMessage() + "\', b\'0\')"); synchronized (this) { dbconnection.executeUpdate(update); } try { sock.getOutputStream().write(1); } catch (IOException e) { e.printStackTrace(); } dbconnection.close(); } } |
Hope this helps those who encounter race conditions against MySQL when you’re writing Enterprise Java Beans (EJBs).