Error upgrade 1.5.3 to 2.5


I tried to upgrade two omekas from 1.5.3 to 2.5. I followed the documentation and renamed “archive” to “files” and “archive/files” to “files/original”. I moved the db.ini and themes/plugins.

I have the message about upgrading the database but this upgrade fails with the following message:

Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Invalid default value for 'added' in /sites/myserver/www/my_site/application/libraries/Zend/Db/Statement/Mysqli.php:214
Stack trace:
#0 /sites/myserver/www/my_site/application/libraries/Zend/Db/Statement.php(303): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /sites/myserver/www/my_site/application/libraries/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 /sites/myserver/www/my_site/application/libraries/Omeka/Db.php(79): Zend_Db_Adapter_Abstract->query('\n\nALTER TABLE `...')
#3 /sites/myserver/www/my_site/application/libraries/Omeka/Db.php(282): Omeka_Db->__call('query', Array)
#4 /sites/myserver/www/my_site/application/migrations/20120220000000_decoupleEntities.php(33): Omeka_Db->queryBlock('ALTER TABLE `om...')
#5 /sites/myserver/www/my_site/application/migrations/20120220000000_decoupleEntities.php(18): decoupleEntities->_updateSchema()
#6 /sites/myserver/www/my_site/application/libraries/Omeka/Db/Migration/Manager.php(231): decoupleEntities->up()
#7 /sites/myserver/www/my_site/application/libraries/Omeka/Db/Migration/Manager.php(111): Omeka_Db_Migration_Manager->_migrateUp(Object(DateTime))
#8 /sites/myserver/www/my_site/application/controllers/UpgradeController.php(49): Omeka_Db_Migration_Manager->migrate()
#9 /sites/myserver/www/my_site/application/libraries/Zend/Controller/Action.php(516): UpgradeController->migrateAction()
#10 /sites/myserver/www/my_site/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch('migrateAction')
#11 /sites/myserver/www/my_site/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#12 /sites/myserver/www/my_site/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#13 /sites/myserver/www/my_site/application/libraries/Zend/Application.php(384): Zend_Application_Bootstrap_Bootstrap->run()
#14 /sites/myserver/www/my_site/application/libraries/Omeka/Application.php(77): Zend_Application->run()
#15 /sites/myserver/www/my_site/admin/index.php(28): Omeka_Application->run()
#16 {main}

Is there a way to fix this?

What version of MySQL are you using? This problem looks like it’s either related to a change we made in Omeka 2.5 or a change in MySQL’s behavior in MySQL 5.7. A date format we previously used extensively in the database is no longer valid in those later versions. The problem is that MySQL will refuse to process even other changes to the database if those old date defaults are still there.

If your MySQL is older than 5.7, one simple thing that should work is doing the update in two steps: first from 1.5.3 to 2.4.1, then from 2.4.1 to 2.5. If it’s MySQL 5.7+, then a slightly different fix is required.

The MySQL version is 5.6.35-log. I will try the update in two steps.


Let us know how that works.

We should be able to make a fix in a version 2.5.1 that would remove the need for a two-step upgrade.

Omeka encounters some errors while upgrading from Omeka 1.5.3 to 2.4.1

You can bypass by modification of omeka admin files located in /application/migrations and reloading the upgrade page. Here’s a list of successive issues I encountered and modifications I did with some issues left at the end.

#Error 1: 20120220000000_decoupleEntities.php(35)
– Error: Mysqli statement execute error : Duplicate column name ‘name’
#6 C:\Program Files\EasyPHP-DevServer-14.1VC11\data\localweb\poincare\application\migrations\20120220000000_decoupleEntities.php(35): Omeka_Db->queryBlock(‘ALTER TABLE `om…’)
– user table already get name and email column
– 26-30 line suppressed, OK

#Error 2: 20120221000000_unEntityTags.php(29)
#5 C:\Program Files\EasyPHP-DevServer-14.1VC11\data\localweb\omeka-2.4.1\application\migrations\20120221000000_unEntityTags.php(29): Omeka_Db->query(‘ALTER IGNORE TA…’)
– suppress line 25 : DROP INDEX tag, OK

#Error 3: 20121015000000_removeElementTextsFulltext.php(19)
#5 C:\Program Files\EasyPHP-DevServer-14.1VC11\data\localweb\omeka-2.4.1\application\migrations\20121015000000_removeElementTextsFulltext.php(19): Omeka_Db->query(‘ALTER TABLE `om…’)
– suppress lines 18, 19

#Error 4: 20121018000001_removeCollectionNameDescriptionCollectors.php(59)
#5 C:\Program Files\EasyPHP-DevServer-14.1VC11\data\localweb\omeka-2.4.1\application\migrations\20121018000001_removeCollectionNameDescriptionCollectors.php(59): Omeka_Db->query(‘INSERT INTO `om…’, Array)
– SQL error in migration: Mysqli statement execute error : Duplicate entry ‘0’ for key ‘PRIMARY’

#Error 41 reload then : new error, same file
– SQL error in migration: Mysqli prepare error: Unknown column ‘name’ in ‘field list’
#6 C:\Program Files\EasyPHP-DevServer-14.1VC11\data\localweb\omeka-2.4.1\application\migrations\20121018000001_removeCollectionNameDescriptionCollectors.php(18): Omeka_Db->query(‘SELECT id, `n…’)
– remove line 16 to 39
– then reload

– Then migration is said to be OK, but in fact there are many issues : in collections and items management
– The name of collections is lost, and you cannot modify or create them.
– Perhaps is it due to errors in 20121018000001_removeCollectionNameDescriptionCollectors.php
– Item modification is impossible

– Last event recorded in omeka_schema-migration is 20120914000000

Any help would be much appreciated.

The previous message is from a co-worker. I tried the two steps upgrade (1.5.3->2.4.1 and then 2.4.1->2.5) without success and is working on a solution (with some problems as he described).

These kinds of errors are fairly typical if you’re retrying upgrades over and over on the same database.

The recommended solution if you encounter an error in an upgrade and want to retry is to restore your pre-upgrade backup and start the upgrade over, not to try to run it again. Because the step that failed is often half-complete, trying to re-try on the same database often results in errors such as columns already existing or already having been removed.

The collection names migration is a good example of this: it operates by selecting out all the old collection names and data, deleting those columns from the collection table, then re-inserting the collection names and information as element texts. On the first run, there was an error in the re-insertion process. Running it again without restoring a backup means that it immediately fails: the columns it’s trying to select from have already been removed.

The “duplicate key” error you got the first time (“error 4”), is a strange one. It’s complaining that the value 0 was inserted as the ID of an element text more than once. The strange part here is that the upgrade doesn’t specifically select element text IDs, it just (as Omeka essentially always does for all records) uses MySQL’s auto-increment to generate a new ID for each row. Is it possible that someone manually edited your database and purposely or accidentally removed the auto-increment setting from the id column of the element_texts table?

Finally, 20120914000000 is far older than the most recent migration should be on an upgrade to 2.4.1. The actual last value should be 20151209103300, meaning you’re off by three years worth of migrations.

In sum, if you encounter an error in an upgrade and think that you can alter the migration to resolve the problem, it’s almost always best to restore from your database backup and start the upgrade from the beginning again. Trying again without restoring almost always requires you to edit far more of the migration (to skip everything that already succeeded the first time).

Many thanks for help. Migration is now on the way to be done using an export of data via the 2.4.1 api, a reshape to CSV and a reimport in a functional 2.5 database.

Your suggestion : “accidental removing the auto-increment setting from the id column of the element_texts table” was very interesting. Auto-increment was in fact missing on files and collections tables. Thanks again.