Pages

Saturday, October 04, 2008

MySQL Partitioned Tables with Trigger enforced FK constraints

Well, I suppose its' true you can't use the official MySQL ALTER TABLE statement to do it, but if you're willing to trust your trigger coding abilities you can.

All you need is an extra table and a couple triggers.

The concept is fairly straight forward:

  1. Create a before insert trigger on the child table that validates the parent exists in the parent table.
  2. If there is no parent found, then insert a row into a third table that automatically forces an error (say, dup key on index)
  3. Create a before delete trigger on the parent table that validate there are no children in the child table.
  4. If there are children found, then insert a row into the third table that automatically forces an error (dup key on index)
It could also be extended to handle cascading deletes, but I'm personally not a fan of that, so I leave that to you... :)

Here's an example:
- Note: This example uses Innodb tables, but it can be done with MyISAM tables just as well.



Parent Table (orders) :

CREATE TABLE IF NOT EXISTS orders
(
id INT NOT NULL AUTO_INCREMENT,
customer_name VARCHAR(30) NOT NULL,
total_amount DECIMAL,
order_date DATETIME ,
PRIMARY KEY (id,customer_name)
)
ENGINE=innodb
PARTITION BY RANGE (id)
(
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30),
PARTITION p4 VALUES LESS THAN (40),
PARTITION p_max VALUES LESS THAN MAXVALUE
);



Child Table (order_items) :


CREATE TABLE IF NOT EXISTS order_items
(
id INT NOT NULL AUTO_INCREMENT,
order_id INT NOT NULL ,
item_name VARCHAR(30) NOT NULL,
item_desc VARCHAR(4000),
item_amount DECIMAL,
PRIMARY KEY (id,order_id)
)
ENGINE=innodb
PARTITION BY HASH (order_id)
PARTITIONS 20
;



Special Table (fk_trigger) used to enforce FK:


CREATE TABLE IF NOT EXISTS fk_trigger
(
id INT NOT NULL PRIMARY KEY DEFAULT 0
)
ENGINE=innodb
;



Insert default row into special table (fk_trigger)
that will force all inserts to fail:


INSERT INTO fk_trigger VALUES();



Child Trigger (order_item_fk_trig) used to check
that parent row exists before insert:


DELIMITER /

CREATE TRIGGER order_item_fk_trig
BEFORE INSERT ON order_items
FOR EACH ROW BEGIN
DECLARE my_count INT;
SELECT count(*)
INTO @my_count
FROM orders
WHERE id = NEW.order_id;

IF (@my_count = 0) THEN
INSERT INTO fk_trigger VALUES ();
END IF;
END;
/

DELIMITER ;



Parent Trigger (order_fk_trig) used to check
that child row exists before delete:


DELIMITER /

CREATE TRIGGER order_fk_rev_trig
BEFORE DELETE ON orders
FOR EACH ROW BEGIN
DECLARE my_count INT;
SELECT count(*)
INTO @my_count
FROM order_items
WHERE order_id = OLD.id;

IF (@my_count > 0) THEN
INSERT INTO fk_trigger VALUES ();
END IF;
END;
/

DELIMITER ;



Insert some data into the parent table

INSERT INTO orders
(customer_name,total_amount,order_date)
VALUES
('Customer 1',300.00,now()),
('Customer 2',600.00,now());



Insert some data into the child table

INSERT INTO order_items
(order_id,item_name,item_desc, item_amount)
VALUES
(1,'This 1','Stuff 1',100),
(1,'This 2','Stuff 2',100),
(1,'This 3','Stuff 3',100),
(2,'This 1','Stuff 1',200),
(2,'This 2','Stuff 2',200),
(2,'This 3','Stuff 3',200);



Try to delete order #1 (without deleting it's children)

DELETE FROM orders
WHERE id = 1;


Results:

ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'



Try to insert orderitems for order #3 (Which doesn't exist)

INSERT INTO order_items
(order_id,item_name,item_desc, item_amount)
VALUES
(3,'This 3','Stuff 1',100);

Results:

ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'


Wednesday, October 01, 2008

MySQL Data Woes... or, Making Use of the Information_Schema

To be fair - I highly doubt it has anything to do with MySQL.

Database corruption - that's a different issue, often related to a bug in database code; but this was a data corruption issue, which is always a pain in the you know what to figure out and get fixed.

I spent the last day trying to figure out the weirdness in a couple of our databases, digging around, explaining to developers that there's no way it could be an issue with the database - it must be in their caching layer somewhere.

It never really occurred to me that the issue might be the data itself... duh. Especially when it turned out to be an issue with a few fields in a few rows with a little extra utf8 character in front of the actual data that happened to map to an ASCII zero (end of line char, I think). Nice.

Now we have to go check every stinkin database, every table, every char based column and look for that character.... Ugh.

Oh yea - we have an information_schema now in 5.1.x ... :)

So about an hour later after a quick code review with the developers and some validation tests, we had a cool little data issue checker to quickly run everywhere, that uses the information_schema database. I'm sure there's a slicker way to do it (and who knows, there might even be one up on MySQL Forge already). I suppose I should have checked there first, but I then again, it's always fun to re-invent the wheel...

Here's what we came up with:


DELIMITER /

DROP PROCEDURE IF EXISTS check_invalid_data /

CREATE PROCEDURE check_invalid_data(db_to_check varchar(128)) -- pass in db name
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE exec_str TEXT;
DECLARE sql_str TEXT;
DECLARE exec_cur CURSOR FOR
SELECT check_string FROM validate_data;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

DROP TEMPORARY TABLE IF EXISTS validate_data;

CREATE TEMPORARY TABLE validate_data
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
check_string TEXT
);

DROP TEMPORARY TABLE IF EXISTS validate_results;

CREATE TEMPORARY TABLE validate_results
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
table_info VARCHAR (256),
column_info VARCHAR (256),
column_length INT
);

INSERT INTO validate_data (check_string)
SELECT CONCAT ('INSERT INTO validate_results (table_info,column_info, column_length) SELECT ''',table_name,''',''',
column_name, ''',LENGTH(',column_name,') FROM ', db_to_check ,
'.' , table_name, ' WHERE ASCII (', column_name ,' ) = 0 AND LENGTH (', column_name ,') > 0 ;')
FROM information_schema.columns
WHERE table_schema = db_to_check
AND data_type IN ('char','varchar','text','mediumtext','tinytext','long','long varchar','enum','set');

OPEN exec_cur;

REPEAT
FETCH exec_cur INTO sql_str;
set @exec_str = sql_str;
IF NOT done THEN
PREPARE validate_stmt FROM @exec_str;
EXECUTE validate_stmt;
DEALLOCATE PREPARE validate_stmt;
END IF;
UNTIL done END REPEAT;

CLOSE exec_cur;

SELECT * FROM validate_results;
END
/
DELIMITER ;