Mysql – Storing the last updated date of a row in a different table

exportMySQLrow-modification-time

I need to do a daily export of all of the new rows or updated rows from a legacy database into the new database.

The problem I am running into is that not all of the tables on the legacy database have last modified date columns and we don't want to break anything by adding new columns to the legacy tables.

The worry is that since there are a lot of different processes inserting records, that the columns might not have been specified in those processes, and since we are switching to the new schema it's not worth it to rewrite those processes yet.

See this code contributed by Evan Carroll as to the problem caused by adding another column.

CREATE TABLE f (a int); 
INSERT INTO f VALUES (1); 
ALTER TABLE f ADD b int; 
INSERT INTO f VALUES (2);

This causes the error,

Error Code: 1136. Column count doesn't match value count at row 1

My current idea is to to create a new table in the legacy database and then create a trigger on the old table to insert the date and primary key of any new rows created or updated in the old tables.

Then I can export just the new/updated rows and import them into the new database.

Would there be better ways of doing this?

Best Answer

My current idea is to to create a new table in the legacy database and then create a trigger on the old table to insert the date and primary key of any new rows created or updated in the old tables.

Your method will work, but that seems almost more likely to break something.. In that case, you're talking about doubling your write load. In the other case you're talking about adding a single column time stamp with time zone DEFAULT now() that won't otherwise be touched or seen,

  • unless someone wrote code that relies on *
  • and,
    • that code depends on the amount of columns returns.
    • or, reorders the columns (ie., sorts) and depends on the sorted order.

I would say adding a column is an extremely safe operation and the right thing to do.

See also

Trying a workaround

I tried creating a TRIGGER to add the column BEFORE INSERT on mysql, that also failed. So the only method you have is

  • another table if you're trying to insure against spurious use of INSERT without explicit column names.
  • replication

Example code (doesn't work) 

CREATE TABLE f (a int, b int);

delimiter //
CREATE TRIGGER use_postgresql_insert
BEFORE INSERT ON f
FOR EACH ROW
BEGIN
  SET NEW.b = 1;
END;
//

INSERT INTO f VALUES (5);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
Related Question