Error with latest 4.6 dev version

GugUser · 2016-05-23 21:27:52

For an experiment I wanted to do an online Textpattern 4.6 dev installation. After the first login, an error message was displayed and I couldn’t do anything (PHP version 5.5.19):

User_Error “INDEX command denied to user ‘web111’@‘localhost’ for table ‘txp_css’”
in /home/www/web111/html/versuch/textpattern/lib/txplib_db.php at line 405.

textpattern/lib/txplib_misc.php:1677 adminErrorHandler()
updateErrorHandler()
textpattern/lib/txplib_db.php:405 trigger_error()
textpattern/lib/txplib_db.php:666 safe_query()
textpattern/update/_to_4.6.0.php:168 safe_drop_index()
textpattern/update/_update.php:84 include()
textpattern/index.php:180 include()

I tried it three times and always I got the same result.

Bloke · 2016-05-23 21:44:23

In reply to GugUser #299219:

User_Error “INDEX command denied to user ‘web111’@‘localhost’ for table ‘txp_css’”

Make sure your MySQL user (web111 in this case) has enough privileges to perform tasks required by Textpattern. Prior versions didn’t necessarily require INDEX privileges, now they do. Note to self: we should document this in the README if not done already.

To see what privileges have been assigned, execute this SQL command from phpMyAdmin or equivalent:

SHOW GRANTS FOR 'web111'@'localhost';

If it shows you don’t have ALL, or only shows some subset of privileges (like SELECT, CREATE, and so forth) you’ll have to add any that are missing. e.g.

GRANT INDEX ON your-txp-database TO 'web111'@'localhost';

You can supply a comma-separated list of privileges required. From memory, you should only need:

SELECT, CREATE, ALTER, INSERT, UPDATE, DELETE, DROP, INDEX, LOCK TABLES

but I may have missed one or two. Someone else may be able to clarify this for you.

GugUser · 2016-05-24 00:23:23

Thank you Bloke for your explications.

It show only this privileges:

It shows GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW

INDEX is missing and I can’t add it:

#1142 - GRANT,INDEX command denied to user 'web111'@'localhost' for table 'textpattern'

I will contact the hosting support.

Thank you!

Bloke · 2016-05-24 00:38:59

GugUser wrote #299234:

INDEX is missing and I can’t add it:

OK, well that confirms the error message. Wierd you can’t add it though. I hope I got the command right in my post above! You might want to verify my work as I didn’t test it.

GugUser · 2016-05-24 00:59:22

Your command was correct, thank you. I think there is a restriction of the hosting. Tomorrow I’ll know more (here still is Monday 19:58).

GugUser · 2016-05-24 12:10:11

The hosting support added the INDEX privilege and now it works. Thank you Bloke.

Bloke · 2016-05-24 13:10:21

GugUser wrote #299245:

The hosting support added the INDEX privilege and now it works. Thank you Bloke.

Good to know. I’ve updated the system requirements page accordingly. Debating whether to add the info to the README as well.

gaekwad · 2016-05-24 15:54:53

Bloke wrote #299246:

Good to know. I’ve updated the system requirements page accordingly. Debating whether to add the info to the README as well.

Suggestion: confirm what MySQL user rights are required across the board, and wait until the 4.6 beta to decide on next steps — may be an isolated incident on GugUser’s hosting infrastructure.

it would make most sense to have this as a pophelp in the setup process, and also ready an FAQ for when 4.6 drops proper.

Bloke · 2016-05-24 15:59:27

gaekwad wrote #299247:

Suggestion: confirm what MySQL user rights are required across the board

Given this line in GugUsers’s trace log:

textpattern/update/_to_4.6.0.php:168 safe_drop_index()

I’m inclined towards a core issue :-) We drop and create indexes (indices?) in this version, which we haven’t done much / any of before.

gaekwad · 2016-05-24 16:16:24

Bloke wrote #299248:

We drop and create indexes (indices?) in this version, which we haven’t done much / any of before.

Oh, absolutely – I understand it’s a user rights issue in core, no argument there. My point was more that it a) it’d be helpful to know what permissions are required to run Textpattern in addition to INDEX and (additionally) b) check they exist during the installation if that’s feasible.

The upgrade route (backup, overwrite core files, drop config in, done) won’t take this into account, of course, but if we can state clearly what’s needed, perhaps in an article that’s ISP tech support friendly, we can avoid a slew of upgrade snags.

I’ve checked on client ISPs just now (3x countries, 5x providers) and they all include INDEX as default. This may be an isolated issue, or it may not be. The beta may find more hosts that don’t have INDEX, or perhaps not. Unknown.

If INDEX is required, and it clearly is, then it’d be really helpful to know what permissions are needed. I’m OK giving a user full permissions over that database only, and that includes INDEX, but as ancillary information this would be useful, especially for organisations with database access policies.

That said, a one liner in README could go like this and then job=done:

From Textpattern 4.6.0, the MySQL database user requires INDEX permissions in addition to existing permissions.

GugUser · 2016-05-24 17:22:41

gaekwad wrote #299249:

I’ve checked on client ISPs just now (3x countries, 5x providers) and they all include INDEX as default. This may be an isolated issue, or it may not be.

The hosting support said it was because of an error in the configuration panel, usually INDEX is activated as default.

Bloke · 2016-05-24 19:35:03

gaekwad wrote #299249:

it’d be helpful to know what permissions are required to run Textpattern in addition to INDEX

Gotcha. The list on the system requirements page is complete as far as I know. We use all those in core. MLP, for instance, requires the temporary table priv. Don’t know of any plugins that use Views, but there may be some.

check they exist during the installation if that’s feasible.

That’d be a nice feature. I’ve always wondered about that. In theory, once we know the user from early on in the setup, it’s just a SHOW GRANTS call, split the result into a list and then array_intersect() or array_diff() (for example) that list with a known-required list and check for fallout. Or allow things to proceed if it reports ALL. There may be some edges cases (like, perhaps it reporting different values depending on MySQL version?) which make this task difficult or impractical.

a one liner in README

Being my usual wordy self, it turned into, a few-liner. Suggestions for improvement / curtailment welcome.

Textpattern CMS

Textpattern CMS support forum

#1 2016-05-23 21:27:52