MySQLi error when trying to install plugin

[bakerstewart]

Hello!

I am trying to install the CSV Import plugin on my institutions’ Omeka installation (https://omeka.wou.edu) and am running into the following error:

Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Invalid default value for ‘added’ in /webvol/application/libraries/Zend/Db/Statement/Mysqli.php:214
Stack trace:
#0 /webvol/application/libraries/Zend/Db/Statement.php(303): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /webvol/application/libraries/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 /webvol/application/libraries/Omeka/Db.php(80): Zend_Db_Adapter_Abstract->query(‘CREATE TABLE IF…’)
#3 /webvol/plugins/CsvImport/CsvImportPlugin.php(107): Omeka_Db->__call(‘query’, Array)
#4 /webvol/application/libraries/Omeka/Plugin/Broker.php(142): CsvImportPlugin->hookInstall(Array)
#5 /webvol/application/libraries/Omeka/Plugin/Installer.php(126): Omeka_Plugin_Broker->callHook(‘install’, Array, Object(Plugin))
#6 /webvol/application/controllers/PluginsController.php(95): Omeka_Plugin_Installer->install(Object(Plugin))
#7 /webvol/application/libraries/Zend/Controller/Action.php(516): PluginsController->installAction()
#8 /webvol/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch(‘installAction’)
#9 /webvol/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#10 /webvol/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#11 /webvol/application/libraries/Zend/Application.php(384): Zend_Application_Bootstrap_Bootstrap->run()
#12 /webvol/application/libraries/Omeka/Application.php(73): Zend_Application->run()
#13 /webvol/admin/index.php(28): Omeka_Application->run()
#14 {main}

Any ideas how I can resolve that? We are running mariadb 10.3.28 and PHP 7.2.24.

ETA looks like this is similar to this reported issue which closed without the final comment on it being resolved.

[jflatnes]

And just to be sure, what version of CSV Import are you trying to install?

[bakerstewart]

Hi John,

I’m installing the latest version, 2.0.5

[jflatnes]

Hmm, okay.

In the CSVImportPlugin.php file for your copy of CSVImport, there’s a block of code called hookInstall. It should start at or around line 102. Can you paste here that whole function (it should end around line 137)?

[bakerstewart]

Sure thing:

    public function hookInstall()
    {
        $db = $this->_db;

        // create csv imports table
        $db->query("CREATE TABLE IF NOT EXISTS `{$db->prefix}csv_import_imports` (
           `id` int(10) unsigned NOT NULL auto_increment,
           `item_type_id` int(10) unsigned NULL,
           `collection_id` int(10) unsigned NULL,
           `owner_id` int unsigned NOT NULL,
           `delimiter` varchar(1) collate utf8_unicode_ci NOT NULL,
           `original_filename` text collate utf8_unicode_ci NOT NULL,
           `file_path` text collate utf8_unicode_ci NOT NULL,
           `file_position` bigint unsigned NOT NULL,
           `status` varchar(255) collate utf8_unicode_ci,
           `skipped_row_count` int(10) unsigned NOT NULL,
           `skipped_item_count` int(10) unsigned NOT NULL,
           `is_public` tinyint(1) default '0',
           `is_featured` tinyint(1) default '0',
           `serialized_column_maps` text collate utf8_unicode_ci NOT NULL,
           `added` timestamp NOT NULL default '2000-01-01 00:00:00',
           PRIMARY KEY  (`id`)
           ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;");

        // create csv imported items table
        $db->query("CREATE TABLE IF NOT EXISTS `{$db->prefix}csv_import_imported_items` (
          `id` int(10) unsigned NOT NULL auto_increment,
          `item_id` int(10) unsigned NOT NULL,
          `import_id` int(10) unsigned NOT NULL,
          PRIMARY KEY  (`id`),
          KEY (`import_id`),
          UNIQUE (`item_id`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;");

        $this->_installOptions();
    }

[jflatnes]

Hmmm, that’s definitely the current version.

Here’s the problem: As far as I’m aware, the only things that cause this issue are the “NO_ZERO_IN_DATE” and “NO_ZERO_DATE” modes. People used to encounter this issue when a table specified a default timestamp of 0000-00-00 00:00:00, which later became disallowed by more recent MySQL versions. But, the date we’re specifying there: midnight, January 1, 2000, isn’t a “zero” date.

We use that same specific default timestamp value in lots of places, including in the Omeka core. So I’m not quite sure what could be going wrong here. I wonder if there’s some MariaDB-specific setting that could be at issue… but I’m not aware of any. I almost want to say this might be a bug in this version of MariaDB, but it’s fairly rare for that to be the answer to a problem like this.

[bakerstewart]

There are some other oddities I’m noticing on our install as well, so I’m thinking we probably just have something messed up on our server rather than it being an issue with Omeka/CSVImport.

That said, we’re planning to migrate off Omeka in a few months anyway (not because of any deficiency with Omeka but because we have multiple IRs and we’re consolidating them all to a hosted one by another vendor) so I’m not sure it’s worth trying to hunt down the issue when we can just manually load the items we need for now.