Thank You Like - Recount very slow

[lost puppy]

Do you also get an error when you run this (after replacing "YOURPREFIX" with your actual prefix)?

SHOW COLUMNS FROM `YOURPREFIX_threads` LIKE 'tyl_tnumtyls';

[...]

No error [...]

That's odd. You say that your database server is MySQL (I assume you mean that it's not the MariaDB fork), and the MySQL documentation says:

The same privileges apply to selecting information from INFORMATION_SCHEMA and viewing the same information through SHOW statements.

However, you're getting a permission error on one and not on the other. I'm not sure why.

All the tables in the db are MyISAM

OK, thanks, that's good to know. It rules out the applicability of this from the same MySQL documentation page (my clarifying addition in square brackets):

Some [INFORMATION_SCHEMA] tables have different privilege requirements; for these, the requirements are mentioned in the applicable table descriptions. For example, InnoDB tables (tables with names that begin with INNODB_) require the PROCESS privilege.

I'm still trying to figure out from here what's going on...

On that first query all I get back (in green with a tick) is:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0005 seconds.)
SHOW COLUMNS FROM `REDACTED_threads` LIKE 'tyl_tnumtyls';

The server states that the DB type in use is MariaDB version 10.6.12

In the tables each has MyISAM as a 'type' as per this image:

   

[Laird]

Oh, it's MariaDB rather than MySQL. Unfortunately, I can't find any MariaDB-specific documentation about access rights on the INFORMATION_SCHEMA database.

Let's check whether you can at least alter the YOURPREFIX_threads table to add the tyl_tnumtyls column, since we already know it doesn't exist yet. Does this query complete successfully with YOURPREFIX replaced again?

ALTER TABLE YOURPREFIX_threads ADD tyl_tnumtyls int(100) NOT NULL default '0';

[lost puppy]

Oh, it's MariaDB rather than MySQL. Unfortunately, I can't find any MariaDB-specific documentation about access rights on the INFORMATION_SCHEMA database.

Let's check whether you can at least alter the YOURPREFIX_threads table to add the tyl_tnumtyls column, since we already know it doesn't exist yet. Does this query complete successfully with YOURPREFIX replaced again?

ALTER TABLE YOURPREFIX_threads ADD tyl_tnumtyls int(100) NOT NULL default '0';

Yes my friend .... worked perfectly and the column is now in the threads table 

[Laird]

Huh. How peculiar. Do you still get errors now when you rerun the script in full?

[lost puppy]

Huh. How peculiar. Do you still get errors now when you rerun the script in full?

Only the one 'Access Denied' on the creation of tyl_tnumtyls column, query number 5 .... no others now.
The same error 

Error
SQL query: Copy


PREPARE stmt FROM @query;
MySQL said: Documentation

#1044 - Access denied for user 'REDACTED'@'%' to database 'information_schema'

[Laird]

That's really bizarre. You have permission to alter the table and add the column, but not to check whether the column exists.

[Edit: I mean, not via the INFORMATION_SCHEMA table - you seem to be able to perform the check just fine via a SHOW COLUMNS query]

[lost puppy]

That's really bizarre. You have permission to alter the table and add the column, but not to check whether the column exists.

I don't know if this would make a difference but I have 5 different databases on the server and can only look at one at a time (it won't let me even have 2 open in different tabs) ... would it think that I am trying to change them all?

I mean with the "INTO @exist" part of that query?

[Laird]

I don't know if this would make a difference but I have 5 different databases on the server and can only look at one at a time (it won't let me even have 2 open in different tabs) ... would it think that I am trying to change them all?

I mean with the "INTO @exist" part of that query?

That part just assigns the result of the query into the variable @exist, so that it can be referenced later. Let's try to rule that part out as the cause of the error though by reducing the query down to this (same need for prefix replacement):

SELECT COUNT(*)
FROM   information_schema.columns 
WHERE  table_schema = database()
       AND
       column_name = 'tyl_tnumtyls'
       AND
       table_name = 'YOURPREFIX_threads';

Does that still generate the error?

[lost puppy]

I don't know if this would make a difference but I have 5 different databases on the server and can only look at one at a time (it won't let me even have 2 open in different tabs) ... would it think that I am trying to change them all?

I mean with the "INTO @exist" part of that query?

That part just assigns the result of the query into the variable @exist, so that it can be referenced later. Let's try to rule that part out as the cause of the error though by reducing the query down to this (same need for prefix replacement):

SELECT COUNT(*)
FROM   information_schema.columns 
WHERE  table_schema = database()
       AND
       column_name = 'tyl_tnumtyls'
       AND
       table_name = 'YOURPREFIX_threads';

Does that still generate the error?

No, that works fine and returns a count of zero << if it is meant to do that??

[Laird]

No, that works fine and returns a count of zero << if it is meant to do that??

It is meant to return a count of one given that the column now exists.

And I was expecting it to generate the same error given that the only change we made was to delete INTO @exist.

Let's put that back in again and see whether we get an error this time:

SELECT COUNT(*)
INTO   @exist
FROM   information_schema.columns 
WHERE  table_schema = database()
       AND
       column_name = 'tyl_tnumtyls'
       AND
       table_name = 'YOURPREFIX_threads';