Emergency

timestamp issue when migrating from MySQL to Percona

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • timestamp issue when migrating from MySQL to Percona

    We are trying to migrate a database used by a webapp from a very old version of MySQL (5.0.95) to Percona (5.6.37) and ran into a problem. In the old database, we can create a table and insert into it with:

    CREATE TABLE `test_table` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `name` varchar(30) default NULL,
    `last_modified` timestamp NOT NULL
    default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`));

    INSERT INTO test_table
    SET name = 'test1';

    INSERT INTO test_table
    SET name = 'test2',
    last_modified = NULL;

    and both of the inserts work and we end up with two rows. In Percona, the second insert fails and produces the error:

    ERROR 1048 (23000): Column 'last_modified' cannot be null

    Of course, the webapp (a PHP program written by several people who no longer are here) produces the second insert and so the insert fails. Unfortunately, because the SQL is automatically generated by the webapp (the table actually has MANY columns), untangling it all will not be trivial. So, before I undertake that, I want to confirm that the second insert is explicitly no longer allowed. If by some chance this is a bug in our version of percona, I'll attempt a workaround, but if this insert is no longer allowed, I'll need to figure out a way to fix the PHP.

    Thanks for any insights.

  • #2
    The following is probably happening
    • in the MYSQL solution, stating `last_modified` timestamp NOT NULL, MYSQL will usually change it to add the default value to be CURRENT_TIMESTAMP with an ON UPDATE CURRNET_TIMESTAMP. Confirm this with SHOW CREATE TABLE test_table
    • I haven't used Percona, but I will just assume that they actually allow timestamp NOT NULL with no default value (which is awesome). So the error you are getting for both statements is correct (as you are not providing a timestamp value)

    Comment

    Working...
    X