In MySQL, you can create indexes in the table definition, if you have CREATE TABLE privileges. But to add/drop indexes on existing tables, requires the INDEX privilege. (This privilege may not be granted by sysadmins when creating a db user.)
See: - SHOW GRANTS; - SELECT privilege_list FROM mysql.user WHERE user = ' database.username '; - SELECT privilege_list FROM mysql.db WHERE user = ' database.username ' and db = ' database.dbname ';
I'm going to close this ticket for now. It's MySQL specific, and we don't check all the other privileges...
I agreed with the closed tickets - can we instead add a FAQ item? Anthon, you now have WP privilege to add faq items I believe. otherwise just send me the Q&A and I'll add it!
vote for wontfix
This has been a recurring pain point that I'm reluctant to close...
Q: The upgrade failed with
SQLSTATE: Syntax error or access violation: 1142 INDEX command denied to user 'myUserID'@'SomeIP' for table 'SomePiwikTable'```. What do I do? A: Ask your sysadmin to GRANT your database user INDEX privilege. This is required to CREATE, DROP, and ALTER indexes. You may be able to comment out the SQL statement to continue the upgrade, but doing so will result in degraded system performance (ie maintaining unused index ot using an efficient index).
(On my iPhone. Sorry.)
Thanks, added in FAQ: http://piwik.org/faq/how-to-update/#faq_78 - I removed the part that says you can comment the SQL as re-running the queries would most likely fail with "field already exist, table not found", etc.