POUWIEL|COM

JeroenPouwiel

MySQL, triggers and the obscure disappearance of data when using RoR…

Working through the excellent rails tutorial by Michael Hartl, somewhere around the end chapter 9 I came across some unusual behavior of (what I suspect) Ruby/Rails. My data, which is committed to a MySQL database, kept disappearing.
Whenever a change was made to any of the spec or controller files, all my users would be gone 🙁
To get a grip on who the culprit is, I went off to create some triggers who would log what happened and when:

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)
, creation_date     DATETIME
, last_update_date  DATETIME) ENGINE=MyISAM;

DELIMITER $$
CREATE TRIGGER trg_users_insert
AFTER INSERT ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'insert'
           ,'users'
           , new.email
           , null
           , new.created_at
           , null );
END$$

CREATE TRIGGER trg_users_update
AFTER update ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'update'
           ,'users'
           , old.email
           , new.email
           , old.updated_at
           , new.updated_at );	
END$$

CREATE TRIGGER trg_users_delete
before delete ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'delete'
           ,'users'
           , old.email
           , null
           , null
           , current_timestamp );	
END$$

Not the most sophisticated piece of code ever, but let’s see if it does the trick…

Damn PHP

Not for n00bs
How on Earth do you return the column definitions of a table as the names for the start- and endtags ?

On Adobe.com® there is a tutorial for Adobe Flex® where the result of a query to a MySQL® database is returned via PHP as XML. The column names retrieved in this query are looped through and put in the start- and endtags in the XML-document.
Of course, MySQL® isn’t “good” enough >;-) and this is altered to query an Oracle® database….

click for source code.
click for xml result.

Categories