Forums » Upgrade to 1.4 DB error (Duplicate column name 'added')

RSS feed for this topic

Info

Tags

No tags yet.

  1. chefandy Posted 11 months ago

    Hello!
    I'm fairly new to Omeka, so I'm not really sure what relevant details I could post that would help you guys out.

    I unzipped 1.4 into its directory on the OpenSUSE box that was running 1.3.2 without a problem. I copy the files that are listed on the upgrade instructions page and grabbed the confirmed-good mysqldump that I had made earlier, just in case.

    When I pointed my browser at the admin page, it prompted me to click on the button to upgrade the db. Nearly instantly after I do, it throws the following exception:

    ---------------

    Omeka encountered an error when upgrading your installation:

    
SQL error in migration: Mysqli statement execute error : Duplicate column name 'added'

#0 /srv/www/htdocs/omeka/application/libraries/Zend/Db/Statement.php(300): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /srv/www/htdocs/omeka/application/libraries/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 [internal function]: Zend_Db_Adapter_Abstract->query('ALTER TABLE `om...', Array)
#3 /srv/www/htdocs/omeka/application/libraries/Omeka/Db.php(84): call_user_func_array(Array, Array)
#4 /srv/www/htdocs/omeka/application/libraries/Omeka/Db.php(298): Omeka_Db->__call('query', Array)
#5 /srv/www/htdocs/omeka/application/libraries/Omeka/Db.php(298): Omeka_Db->query('ALTER TABLE `om...', Array)
#6 /srv/www/htdocs/omeka/application/libraries/Omeka/Db.php(314): Omeka_Db->exec('ALTER TABLE `om...')
#7 /srv/www/htdocs/omeka/application/migrations/20100810120000_detachCollectorsFromEntities.php(37): Omeka_Db->execBlock('ALTER TABLE `om...')
#8 /srv/www/htdocs/omeka/application/migrations/20100810120000_detachCollectorsFromEntities.php(20): detachCollectorsFromEntities->_migrateCollections(Object(Omeka_Db))
#9 /srv/www/htdocs/omeka/application/libraries/Omeka/Db/Migration/Manager.php(230): detachCollectorsFromEntities->up()
#10 /srv/www/htdocs/omeka/application/libraries/Omeka/Db/Migration/Manager.php(117): Omeka_Db_Migration_Manager->_migrateUp(Object(DateTime))
#11 /srv/www/htdocs/omeka/application/controllers/UpgradeController.php(51): Omeka_Db_Migration_Manager->migrate()
#12 /srv/www/htdocs/omeka/application/libraries/Zend/Controller/Action.php(513): UpgradeController->migrateAction()
#13 /srv/www/htdocs/omeka/application/libraries/Zend/Controller/Dispatcher/Standard.php(295): Zend_Controller_Action->dispatch('migrateAction')
#14 /srv/www/htdocs/omeka/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#15 /srv/www/htdocs/omeka/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(97): Zend_Controller_Front->dispatch()
#16 /srv/www/htdocs/omeka/application/libraries/Zend/Application.php(366): Zend_Application_Bootstrap_Bootstrap->run()
#17 /srv/www/htdocs/omeka/application/libraries/Omeka/Core.php(166): Zend_Application->run()
#18 /srv/www/htdocs/omeka/admin/index.php(45): Omeka_Core->run()
#19 {main}

    Please restore from your database backup and try again. If you continue to experience errors, you can notify us on the Omeka Forums.
    ---------------

    This is what's in the SQL logs:
    2011-06-22T23:28:05-04:00 DEBUG (7): SELECT name, value FROM omeka_options
    2011-06-22T23:28:05-04:00 DEBUG (7): SELECT p.* FROM omeka_plugins AS p
    2011-06-22T23:28:06-04:00 DEBUG (7): SELECT version FROM omeka_schema_migrations
    2011-06-22T23:28:06-04:00 DEBUG (7): SELECT version FROM omeka_schema_migrations
    2011-06-22T23:28:07-04:00 DEBUG (7): SELECT name, value FROM omeka_options
    2011-06-22T23:28:07-04:00 DEBUG (7): SELECT p.* FROM omeka_plugins AS p
    2011-06-22T23:28:07-04:00 DEBUG (7): SELECT version FROM omeka_schema_migrations
    2011-06-22T23:28:07-04:00 DEBUG (7): SELECT version FROM omeka_schema_migrations
    2011-06-22T23:28:07-04:00 DEBUG (7): SELECT version FROM omeka_schema_migrations
    2011-06-22T23:28:07-04:00 DEBUG (7): SELECT version FROM omeka_schema_migrations
    2011-06-22T23:28:07-04:00 DEBUG (7): ALTER TABLE omeka_collections ADD added TIMESTAMP NOT NULL default '0000-00-00 00:00:00'

    Is there some sort of logic that generates this on the fly, or is it a flat script?

  2. chefandy Posted 11 months ago

    This deployment needs to go fairly quickly, so I'm probably going to stick to 1.3.2 at the moment, but I'll keep chipping away at a dev box. I have a feeling that it's simply a statement that needs to be taken out or modified. I'll post anything I find in this thread. If anybody has any info/ideas, I'd love to hear them. Thanks!

  3. John Flatness (Omeka Dev Team) Posted 11 months ago

    The detachCollectorsFromEntities code that it's complaining about is a DB migration. DB migrations are located in application/migrations.

    The odd thing here is that detachCollectorsFromEntities is an old migration: it was there in version 1.3. Omeka shouldn't be trying to run it again, and the fact that it is is why you're getting that SQL error.

    You could delete the offending migration, 20100810120000_detachCollectorsFromEntities (convertCollectorArrays is also an old one from 1.3.2), as a quick fix, but I'd be interested to see what you have in your omeka_schema_migrations table. This table should be storing the migrations that you've already done, so that this doesn't happen.

  4. chefandy Posted 11 months ago

    So, restored to my 1.3.2 DB, looked in the table "omeka_collections"... the added field already exists:

    +-------------+------------------+------+-----+---------------------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +-------------+------------------+------+-----+---------------------+----------------+
    | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
    | name | varchar(255) | NO | | NULL | |
    | description | text | NO | | NULL | |
    | collectors | text | NO | | NULL | |
    | public | tinyint(1) | NO | MUL | NULL | |
    | featured | tinyint(1) | NO | MUL | NULL | |
    | added | timestamp | NO | | 0000-00-00 00:00:00 | |
    | modified | timestamp | NO | | 0000-00-00 00:00:00 | |
    | owner_id | int(10) unsigned | NO | MUL | NULL | |
    +-------------+------------------+------+-----+---------------------+----------------+

    In the file "application/migrations/20100810120000_detachCollectorsFromEntities.php" I found the following code:

    private function _migrateCollections($db)
    {
    $db->execBlock(<<<COL
    ALTER TABLE $db->Collection ADD added TIMESTAMP NOT NULL default '0000-00-00 00:00:00';
    ALTER TABLE $db->Collection ADD modified TIMESTAMP NOT NULL;
    ALTER TABLE $db->Collection ADD owner_id INT( 10 ) UNSIGNED NOT NULL;
    ALTER TABLE $db->Collection ADD INDEX ( owner_id );
    ALTER TABLE $db->Collection ADD collectors TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL AFTER description ;
    COL
    );

    This is the only file that I found that contained references to the added field.

    So I modified this block to say:

    ALTER TABLE $db->Collection MODIFY added TIMESTAMP NOT NULL default '0000-00-00 00:00:00';
    ALTER TABLE $db->Collection MODIFY modified TIMESTAMP NOT NULL;
    ALTER TABLE $db->Collection MODIFY owner_id INT( 10 ) UNSIGNED NOT NULL;
    ALTER TABLE $db->Collection ADD INDEX ( owner_id );
    ALTER TABLE $db->Collection MODIFY collectors TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL AFTER description ;

    Just to be safe, even though I'm fairly sure that I could have just blown away the whole block... upgrade process completed successfully. I'll consider this resolved until I see otherwise. I'll also post this to the dev list.

  5. chefandy Posted 11 months ago

    I'll restore from backup and go through the process again and give you a dump of that table;

  6. chefandy Posted 11 months ago

    Before the upgrade, the only entry in the version field in that table is 20100401000000.

    Now it is

    | 20100401000000 |
    | 20110113000000 |
    | 20110124000001 |
    | 20110301103900 |
    | 20110328192100 |
    | 20110426181300

    I was not the administrator or developer that worked with this installation initially, so I've got very few details on the initial deployment to share, unfortunately.

    With that file removed, the upgrade process also went smoothly.

  7. chefandy Posted 11 months ago

    AFAIK this is resolved. As long as I know that I'd just need to update the table or remove those files if a discrepancy pops up again, that's totally fine.

    However, this is a dev box, and I've had a lot of coffee this morning, so I'd be more than happy to blow away/restore as needed to provide details for your own use... though I suspect I've just encountered some sort of anomaly resulting from improper administration or installation.

  8. John Flatness (Omeka Dev Team) Posted 11 months ago

    Yeah, it seems that it's the "before upgrade" state of that table that's the problem. It should have had three entries, one for each of the migrations that existed in 1.3.2.

    It doesn't look like there's much more information about this you'll be able to get at, since it seems the problem was pre-existing.

    Thanks for sharing these details. I don't think there's a bug here, but this kind of info is always welcome.

Reply

You must log in to post.