New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL error in migration #831

Open
edsu opened this Issue Dec 28, 2017 · 3 comments

Comments

Projects
None yet
2 participants
@edsu

edsu commented Dec 28, 2017

I was migrating an old version of Omeka from 1.3.1 to 2.5.1 and ran across an error in this line:

ALTER IGNORE TABLE `{$this->db->Taggings}`

which threw this error when upgrading:

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `omeka_taggings`
DROP INDEX `tag`,
DROP `entity_id`,
ADD UNIQUE IND' at line 1 in /var/www/html/application/libraries/Zend/Db/Statement/Mysqli.php:77
Stack trace:
#0 /var/www/html/application/libraries/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('ALTER IGNORE TA...')
#1 /var/www/html/application/libraries/Zend/Db/Adapter/Mysqli.php(388): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), 'ALTER IGNORE TA...')
#2 /var/www/html/application/libraries/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Adapter_Mysqli->prepare('ALTER IGNORE TA...')
#3 /var/www/html/application/libraries/Omeka/Db.php(79): Zend_Db_Adapter_Abstract->query('ALTER IGNORE TA...')
#4 /var/www/html/application/migrations/20120221000000_unEntityTags.php(24): Omeka_Db->__call('query', Array)
#5 /var/www/html/application/migrations/20120221000000_unEntityTags.php(18): unEntityTags->_updateSchema()
#6 /var/www/html/application/libraries/Omeka/Db/Migration/Manager.php(237): unEntityTags->up()
#7 /var/www/html/application/libraries/Omeka/Db/Migration/Manager.php(111): Omeka_Db_Migration_Manager->_migrateUp(Object(DateTime))
#8 /var/www/html/application/controllers/UpgradeController.php(49): Omeka_Db_Migration_Manager->migrate()
#9 /var/www/html/application/libraries/Zend/Controller/Action.php(516): UpgradeController->migrateAction()
#10 /var/www/html/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch('migrateAction')
#11 /var/www/html/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#12 /var/www/html/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#13 /var/www/html/application/libraries/Zend/Application.php(384): Zend_Application_Bootstrap_Bootstrap->run()
#14 /var/www/html/application/libraries/Omeka/Application.php(77): Zend_Application->run()
#15 /var/www/html/admin/index.php(28): Omeka_Application->run()
#16 {main}

After removing IGNORE keyword the migration continued flawlessly. I doubt many people run across this because they've stayed on top of updates better but I thought I'd submit this just in case anyone finds themselves off in the weeds like me.

@zerocrates

This comment has been minimized.

Show comment
Hide comment
@zerocrates

zerocrates Jan 11, 2018

Member

What version of MySQL are you using? I presume it's fairly recent, because they seem to have removed ALTER IGNORE.

Now the mystery of why I put the IGNORE in there in the first place... I suppose it was to easily handle the possibility of existing duplicates since that seems to be about all it was good for.

Member

zerocrates commented Jan 11, 2018

What version of MySQL are you using? I presume it's fairly recent, because they seem to have removed ALTER IGNORE.

Now the mystery of why I put the IGNORE in there in the first place... I suppose it was to easily handle the possibility of existing duplicates since that seems to be about all it was good for.

@edsu

This comment has been minimized.

Show comment
Hide comment
@edsu

edsu Jan 11, 2018

Yeah, it was whatever the latest version of MySQL is in Docker.

edsu commented Jan 11, 2018

Yeah, it was whatever the latest version of MySQL is in Docker.

@zerocrates

This comment has been minimized.

Show comment
Hide comment
@zerocrates

zerocrates Feb 6, 2018

Member

The IGNORE was definitely used for its duplicate ignoring facilities... so simply removing it will avoid this problem for users with sufficiently new MySQL, but anyone who actually has duplicates (i.e., tags that vary only by the entity/user they apply for, a concept we've removed from Omeka) will encounter a different error.

MySQL's generic guidance around the subject, such as it is, is to deduplicate by inserting (with the IGNORE flag, which persists for INSERT) into a new table, dropping the old, and then renaming the new one... which seems like an awful lot of trouble for a six year old migration. At the same time, I'd rather not cut off the ability to migrate large distances in terms of version at once.

I suppose we could use GROUP BY on the new set of unique columns, select one tagging ID from each group and delete every tagging not in that set... which wouldn't be too painful.

Member

zerocrates commented Feb 6, 2018

The IGNORE was definitely used for its duplicate ignoring facilities... so simply removing it will avoid this problem for users with sufficiently new MySQL, but anyone who actually has duplicates (i.e., tags that vary only by the entity/user they apply for, a concept we've removed from Omeka) will encounter a different error.

MySQL's generic guidance around the subject, such as it is, is to deduplicate by inserting (with the IGNORE flag, which persists for INSERT) into a new table, dropping the old, and then renaming the new one... which seems like an awful lot of trouble for a six year old migration. At the same time, I'd rather not cut off the ability to migrate large distances in terms of version at once.

I suppose we could use GROUP BY on the new set of unique columns, select one tagging ID from each group and delete every tagging not in that set... which wouldn't be too painful.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment