Server Migration: Incorrect TIMESTAMP value

[aday]

I’m moving our Omeka install to a new server (from Ubuntu 16.04 to Ubuntu 20.04 server) and have run into an error when doing the DB upgrade.

The error is:

Omeka encountered an error when upgrading your installation.

SQL error in migration: Mysqli prepare error: Incorrect TIMESTAMP value: ‘0000-00-00 00:00:00’

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: Incorrect TIMESTAMP value: ‘0000-00-00 00:00:00’ in /omeka-2.8/application/libraries/Zend/Db/Statement/Mysqli.php:77
Stack trace:
#0 /omeka-2.8/application/libraries/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare()
#1 /omeka-2.8/application/libraries/Zend/Db/Adapter/Mysqli.php(388): Zend_Db_Statement->__construct()
#2 /omeka-2.8/application/libraries/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Adapter_Mysqli->prepare()
#3 /omeka-2.8/application/libraries/Omeka/Db.php(80): Zend_Db_Adapter_Abstract->query()
#4 /omeka-2.8/application/migrations/20151209103299_removeZeroDates.php(22): Omeka_Db->__call()
#5 /omeka-2.8/application/libraries/Omeka/Db/Migration/Manager.php(229): removeZeroDates->up()
#6 /omeka-2.8/application/libraries/Omeka/Db/Migration/Manager.php(106): Omeka_Db_Migration_Manager->_migrateUp()
#7 /omeka-2.8/application/controllers/UpgradeController.php(46): Omeka_Db_Migration_Manager->migrate()
#8 /omeka-2.8/application/libraries/Zend/Controller/Action.php(516): UpgradeController->migrateAction()
#9 /omeka-2.8/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch()
#10 /omeka-2.8/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch()
#11 /omeka-2.8/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#12 /omeka-2.8/application/libraries/Zend/Application.php(384): Zend_Application_Bootstrap_Bootstrap->run()
#13 /omeka-2.8/application/libraries/Omeka/Application.php(73): Zend_Application->run()
#14 /omeka-2.8/admin/index.php(28): Omeka_Application->run()
#15 {main}

Any help would be greatly appreciated!

[rhutton]

I’m working on upgrading a large set of Omeka sites from an Ubuntu 16.04 host to Ubuntu 20.04. The way I managed to get past this error was to temporarily update the sql_mode in the application/libraries/Omeka/Db.php, removing:

NO_ZERO_IN_DATE,NO_ZERO_DATE,

Then after the database upgrade was complete, I put them back to how they were. In my bash upgrade script this looks like this:

# change sql_mode temporarily during upgrade to allow zero dates
mv "$site/application/libraries/Omeka/Db.php" "$site/application/libraries/Omeka/Db.php.orig"
sed 's/NO_ZERO_IN_DATE,NO_ZERO_DATE,//g' "$site/application/libraries/Omeka/Db.php.orig" > "$site/application/libraries/Omeka/Db.php"

# do database upgrade
curl http://localhost/$site/admin/upgrade/migrate

# revert change to sql_mode
mv "$site/application/libraries/Omeka/Db.php.orig" "$site/application/libraries/Omeka/Db.php"

After the upgrade, I checked the database to see if there were any dates that were actually set to 0000-00-00 and there weren’t, so I’m not exactly sure why this is failing in the first place.