Hi,
I am attempting to migrate an old Omeka instance on 2.8 to a new version. I have the new code in place, but when I attempt to login to the web client to upgrade the database, I receive the following error:
Zend_Db_Statement_Mysqli_Exception
Mysqli statement execute error : Field 'id' doesn't have a default value
#0 /var/www/html/collections/application/libraries/Zend/Db/Statement.php(303): Zend_Db_Statement_Mysqli->_execute()
#1 /var/www/html/collections/application/libraries/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute()
#2 /var/www/html/collections/application/libraries/Omeka/Db.php(80): Zend_Db_Adapter_Abstract->query()
#3 /var/www/html/collections/application/libraries/globals.php(46): Omeka_Db->__call()
#4 /var/www/html/collections/application/libraries/Omeka/Db/Migration/Manager.php(156): set_option()
#5 /var/www/html/collections/application/libraries/Omeka/Db/Migration/Manager.php(143): Omeka_Db_Migration_Manager->finalizeDbUpgrade()
#6 /var/www/html/collections/application/libraries/Omeka/Controller/Plugin/Upgrade.php(55): Omeka_Db_Migration_Manager->dbNeedsUpgrade()
#7 /var/www/html/collections/application/libraries/Omeka/Controller/Plugin/Upgrade.php(39): Omeka_Controller_Plugin_Upgrade->_dbNeedsUpgrade()
#8 /var/www/html/collections/application/libraries/Zend/Controller/Plugin/Broker.php(287): Omeka_Controller_Plugin_Upgrade->dispatchLoopStartup()
#9 /var/www/html/collections/application/libraries/Zend/Controller/Front.php(928): Zend_Controller_Plugin_Broker->dispatchLoopStartup()
#10 /var/www/html/collections/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(106): Zend_Controller_Front->dispatch()
#11 /var/www/html/collections/application/libraries/Zend/Application.php(384): Zend_Application_Bootstrap_Bootstrap->run()
#12 /var/www/html/collections/application/libraries/Omeka/Application.php(73): Zend_Application->run()
#13 /var/www/html/collections/admin/index.php(28): Omeka_Application->run()
#14 {main}
Any ideas on how to fix?
Thanks,
Jay Dougherty
Hmm, that’s an odd error. It looks like something’s odd about your installation’s “options” table, from what I can see from that error message.
If you’re comfortable trying, you could connect to your database and run
SHOW CREATE TABLE omeka_options;
(with omeka_ replaced by your configured prefix in db.ini if you’ve set one other than the default of omeka_). That should reveal if there’s something wrong with that table.
This is the result:
mysql> show create table omeka_options;
±--------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±--------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| omeka_options | CREATE TABLE omeka_options (
id int unsigned NOT NULL,
name varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
value text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci |
±--------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
So, definitely something odd has happened here.
Your options table is missing the AUTO_INCREMENT setting on the id column. That’s not something Omeka’s own upgrade or other systems would do… maybe it was done by a manual change to the database at some point, or other process?
You’re also missing the indexes that should be on this table; there should be lines showing indexes on the id and name columns. Here’s what it should look like:
CREATE TABLE `omeka_options` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
`value` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4657 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci
That AUTO_INCREMENT=4657 part is unique to the table I dumped to get that output, but the rest should be what yours should look like: the AUTO_INCREMENT on the id line, the PRIMARY KEY and UNIQUE KEY lines. It’s possible to make these changes to bring the table back into the expected format, but I’d be a little concerned that this isn’t the only table that’s “wrong.”
I did the export in phpmyadmin of the old database. I wonder if I did the export via command line if it would make any difference? What do you think? I don’t think I saw any errors when importing it.
Hmm, I wouldn’t typically expect to see a problem like this from exporting through phpmyadmin. If you do have exports or older backups, they should contain a CREATE TABLE statement for the omeka_options table and you could see if they have the same problem or not.
You could also check other tables in the same way with SHOW CREATE TABLE to see if this is more widespread or not.
To change this to what it should be you could run this statement
ALTER TABLE `omeka_options` MODIFY `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ADD UNIQUE INDEX `name` (`name`);
But obviously that only accounts for this one table.
I fixed this by doing a command line export/import of the database and dropping all of the old tables and data. I suspect either my export in phpmyadmin timed out or there was a setting in phpmyadmin that capped files at 2 gigs. The entire database turned out to be 4.5 gigs so it was missing a lot of data.
Ah an import that didn’t finish could make sense, definitely.
Happy to hear you figured it out.