DB upgrade error moving from 2.0.3 to 2.5.1


#1

Hello,

We have had omeka running for a few years on version 2.0.3. I tried upgrading today to 2.5.1 and thought everything was going well until I went back to the admin web page to check to see if the DB needed to be upgraded (step 6 in the instructions). It indicated I did, so I clicked upgrade, but I get the following:

Omeka encountered an error when upgrading your installation.

SQL error in migration: Mysqli statement execute error : ALTER command denied to user ‘omeka_user’@‘localhost’ for table ‘omeka_keys’

exception ‘Zend_Db_Statement_Mysqli_Exception’ with message ‘Mysqli statement execute error : ALTER command denied to user ‘omeka_user’@‘localhost’ for table ‘omeka_keys’’ in /var/www/html/application/libraries/Zend/Db/Statement/Mysqli.php:214
Stack trace:
#0 /var/www/html/application/libraries/Zend/Db/Statement.php(303): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /var/www/html/application/libraries/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 [internal function]: Zend_Db_Adapter_Abstract->query(‘ALTER TABLE om...') #3 /var/www/html/application/libraries/Omeka/Db.php(79): call_user_func_array(Array, Array) #4 /var/www/html/application/migrations/20130429000000_addAccessedToKeys.php(16): Omeka_Db->__call('query', Array) #5 /var/www/html/application/migrations/20130429000000_addAccessedToKeys.php(16): Omeka_Db->query('ALTER TABLEom…’)
#6 /var/www/html/application/libraries/Omeka/Db/Migration/Manager.php(237): addAccessedToKeys->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}

Please restore from your database backup and try again. If you have any questions please refer to Omeka documentation or post a message on the Omeka forums.

Any thoughts on how to get around this error?

Thank you.


#2

This means the MySQL user you have in db.ini doesn’t have permission to change tables.

You need to either grant that permission to that user (it’s called ALTER) or change db.ini to have a user that does have that permission.


#3

Thanks for the reply. I put the old files back and the site is working on the old version again. I compared the two db.ini files and they are identical (since I copied it from the old version to the new version). When I had the new version in place I even went so far as to drop the DB and re-create it using the steps from the install doc. Is the command on the install doc correct?

mysql> insert into db(host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) values (‘localhost’,‘omeka_db’,‘omeka_user’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’,‘Y’);

should that statement also include Alter_priv?

Thanks.

https://omeka.org/codex/Install_on_Fedora


#4

Ah, I see. Those distro-specific installation instructions are all originally community contributions and don’t really have someone who owns them and keeps them up to date, so they can definitely have issues.

If you notice, that command lists a bunch of "priv"s, select, insert, update, delete, create, and drop, but notably not “Alter”, and that’s the source of the problem. If you log in to the mysql command line again and run this:

GRANT ALTER ON omeka_db.* TO 'omeka_user'@'localhost';
FLUSH PRIVILEGES;

it should give you the permission you need.


#5

Thank you. That seems to have taken care of the issue. I appreciate the help.