MyBB Community Forums
Thank You Like - Recount very slow - Printable Version

+- MyBB Community Forums (https://community.mybb.com)
+-- Forum: Extensions (https://community.mybb.com/forum-201.html)
+--- Forum: Plugins (https://community.mybb.com/forum-73.html)
+---- Forum: Plugin Support (https://community.mybb.com/forum-72.html)
+---- Thread: Thank You Like - Recount very slow (/thread-239004.html)

Pages: 1 2 3 4

RE: Thank You Like - Recount very slow - lost puppy - 2023-08-22

(2023-08-22, 12:42 PM)Laird Wrote:
(2023-08-22, 12:36 PM)lost puppy Wrote: 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';

That ran fine as well  Huh

This is what it came back with (image cut short to hide my prefix .... but I am thinking that it may just be easier to show it all and change the prefix later)

   

RE: Thank You Like - Recount very slow - Laird - 2023-08-22

"Curiouser and curiouser!" cried Alice.

What about if you run the sixth query immediately after that one (again, replacing the table prefix)?

SET @query = IF(@exist <= 0,
                "
                 ALTER TABLE YOURPREFIX_threads
                 ADD         tyl_tnumtyls int(100) NOT NULL default '0'
                ",
                "SET @dummy1 = 1"
               );


RE: Thank You Like - Recount very slow - lost puppy - 2023-08-22

(2023-08-22, 12:58 PM)Laird Wrote: "Curiouser and curiouser!" cried Alice.

What about if you run the sixth query immediately after that one (again, replacing the table prefix)?

SET @query = IF(@exist <= 0,
                "
                 ALTER TABLE YOURPREFIX_threads
                 ADD         tyl_tnumtyls int(100) NOT NULL default '0'
                ",
                "SET @dummy1 = 1"
               );

Both ran fine 

   

RE: Thank You Like - Recount very slow - Laird - 2023-08-22

OK, now repeat those two queries after first dropping the column we'd added:

ALTER TABLE YOURPREFIX_threads DROP COLUMN tyl_tnumtyls;


RE: Thank You Like - Recount very slow - lost puppy - 2023-08-22

(2023-08-22, 01:08 PM)Laird Wrote: OK, now repeat those two queries after first dropping the column we'd added:

ALTER TABLE YOURPREFIX_threads DROP COLUMN tyl_tnumtyls;

Dropped the column .... all good column gone.
Ran both .... all good but the column is not there!

RE: Thank You Like - Recount very slow - Laird - 2023-08-22

We really are in Wonderland...

What happens now when you run the full set of queries again?

RE: Thank You Like - Recount very slow - lost puppy - 2023-08-22

(2023-08-22, 01:51 PM)Laird Wrote: We really are in Wonderland...

What happens now when you run the full set of queries again?

Well there is still the last part of that query which we didn't add back

PREPARE stmt FROM @query;
EXECUTE stmt;

So I just added it and ran it >> and that got the "Access Denied" error!

RE: Thank You Like - Recount very slow - Laird - 2023-08-22

So, it was the PREPARE query generating the error all along. But why?

What does this show if you run it just before the PREPARE query?

SELECT @query;


RE: Thank You Like - Recount very slow - lost puppy - 2023-08-22

(2023-08-22, 02:28 PM)Laird Wrote: So, it was the PREPARE query generating the error all along. But why?

What does this show if you run it just before the PREPARE query?

SELECT @query;

I get the same "Access Denied" error.

So the full 5th query is:

SELECT COUNT(*)
INTO   @exist
FROM   information_schema.columns 
WHERE  table_schema = database()
       AND
       column_name = 'tyl_tnumtyls'
       AND
       table_name = 'REDACTED_threads';
SET @query = IF(@exist <= 0,
                "
                 ALTER TABLE REDACTED_threads
                 ADD         tyl_tnumtyls int(100) NOT NULL default '0'
                ",
                "SET @dummy1 = 1"
               );
SELECT @query;
PREPARE stmt FROM @query;
EXECUTE stmt;

and the first 2 parts run no problem .... just the last 3 lines create the "Access Denied"

Could it be something to do with the server security I have set?

Fact is Laird ... your code did the job and did it VERY quickly even on 3.5 million likes  Big Grin

Maybe I just should do the TYL update and then run the first set of queries you created?
Or I am happy to continue trying whatever you wish to sort it out?

Either way mate .... You Are A STAR  Heart

RE: Thank You Like - Recount very slow - Laird - 2023-08-22

(2023-08-22, 02:41 PM)lost puppy Wrote: Or I am happy to continue trying whatever you wish to sort it out?

If you have the patience to continue, I'd appreciate it, but let's take it private from here on to avoid clogging up the public forums with an extended debugging conversation. I'll PM you with a fuller response.

(2023-08-22, 02:41 PM)lost puppy Wrote: Either way mate .... You Are A STAR  Heart

Aw, thanks.