Not the answer you need?
Register and ask your own question!

timestamp issue when migrating from MySQL to Percona

SullyBeckSullyBeck EntrantCurrent User Role Novice
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.

Comments

  • Bob565656Bob565656 Entrant Inactive User Role Beginner
    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)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.