vGauxDYR

1/*
2-- existing table structure, missing index for the field1
3CREATE TABLE IF NOT EXISTS `table1` (
4`id` int(11) NOT NULL auto_increment,
5`field1` int(11) not null, 
6PRIMARY KEY (`id`)
7) ENGINE=InnoDB DEFAULT CHARSET=utf8;
8
9*/
10
11use test;
12-- new table structure with added index
13CREATE TABLE IF NOT EXISTS `table1_shadow` (
14`id` int(11) NOT NULL auto_increment,
15`field1` int(11) not null, 
16PRIMARY KEY (`id`),
17KEY idx_field1 (field1)
18) ENGINE=InnoDB DEFAULT CHARSET=utf8;
19
20DROP TRIGGER IF EXISTS test_table1_update_before_trigger;
21DROP TRIGGER IF EXISTS test_table1_insert_after_trigger;
22DROP TRIGGER IF EXISTS test_table1_delete_after_trigger;
23DROP PROCEDURE IF EXISTS proc_test_table1_update;
24delimiter |
25CREATE PROCEDURE proc_test_table1_update( op int
26, a_id int(11) 
27, a_field1 int(11) 
28)
29proc:BEGIN
30
31INSERT INTO table1_shadow SET
32  id=a_id
33, field1=a_field1
34ON DUPLICATE KEY UPDATE
35  id=a_id
36, field1=a_field1
37;
38END;
39|
40CREATE TRIGGER test_table1_insert_after_trigger AFTER INSERT ON table1 FOR EACH ROW BEGIN
41CALL proc_test_table1_update(0
42, new.id
43, new.field1
44);
45END;
46|
47CREATE TRIGGER test_table1_update_before_trigger BEFORE update ON table1 FOR EACH ROW BEGIN
48CALL proc_test_table1_update(1
49, new.id
50, new.field1
51);
52END;
53|
54CREATE TRIGGER test_table1_delete_after_trigger AFTER delete ON table1 FOR EACH ROW BEGIN
55DELETE FROM table1_shadow where id=old.id LIMIT 1;
56END;
57|
58delimiter ;
59
60
61-- at this moment all updates to table1 will be reflected on table1_shadow
62
63
64-- execute fake updates
65
66-- echo 'select id from table1'|mysql test --skip-column-names >ids_to_update.txt
67--
68-- simplified example with fake updates
69-- cat ids_to_update.txt|awk '{print "UPDATE table1 SET id=id WHERE id="$1" LIMIT 1;";}'|mysql test
70--
71-- at this moment, table1_shadow is full and live copy of table1
72--
73-- rename tables and drop triggers
74-- mysql test
75--
76-- RENAME TABLE table1 to table1_old, table1_shadow to table1;
77-- DROP TRIGGER IF EXISTS test_table1_update_before_trigger;
78-- DROP TRIGGER IF EXISTS test_table1_insert_after_trigger;
79-- DROP TRIGGER IF EXISTS test_table1_delete_after_trigger;
80-- DROP PROCEDURE IF EXISTS proc_test_table1_update;