ALTER TABLE commands running multiple times, bogging down site

Hi @jondaley

I’m sorry to hear that you’re having this issue. Let me help you with this one.

I think this is the file and the line of code you’re looking for. In the meantime, allow me to share this with the team, and I’ll get back to you once I know more.

Also, please provide the version of the following. This will help me understand how your system is set up.

• PHP
• WordPress
• The Events Calendar
• Theme

Great, thanks. That looks likely.

I added a logging statement inside the if(update) block, and on a page load to the home page and it ran that code 7 times, 5 times via the user, and twice via cronjob.

I added a second statement inside the if(!fully_activated) and that code never runs.

Is it expected to have this code running that many times. I can see it running on each user’s visit to the site (it ran 80 times while typing this message)

I’m running 6.0.12.

Hi @jondaley

One of my colleague mentioned that the check should only happen once a day. Here’s the logic via transient.

Regardless of how often it checks – once it checks and runs the updates, it shouldn’t run this query again: ALTER TABLE wp_tec_occurrences CHANGE COLUMN start_date start_date VARCHAR(19) NOT NULL

To investigate this issue further, could you check the following:

• do you have a?transient tool? or is your transients disabled?
• check the WP options table and look for this key tec_ct1_occurrences_table_schema_version. It should be set at?1.0.2?(our latest for the occurrences table)
• Check the logs for errors

Looking forward to your response.

Definitely it happens more than once a day – I’ve seen multiple simultaneous operations – perhaps the logic is bad in not setting that it is working until the operation is completed, so if simultaneous users come in, it will run a bunch of times before the first one completes?
This site has a higher-than-average number of users who have logins. (10,000 users, though a lot of those are old; probably 500 are actively using the site on a regular basis)

if(update) check has passed and run the schema_builder->up() 44,000 times since I last wrote to you.

I am using W3 total cache. I assume these settings are the defaults, but these seem relevant:

Default lifetime of cache objects:?1800 seconds
Garbage collection interval:?3600 seconds
Store transients in database: No

| 72659615 | tec_ct1_series_relationship_table_schema_version | 1.0.0 | yes |
| 72659616 | tec_ct1_events_table_schema_version | 1.0.1 | yes |
| 72659617 | tec_ct1_occurrences_table_schema_version | 1.0.2 | yes |

I’m curious about the ordering of line ~90 in Activation.php, where you set the transient/cache value and then erase the other. I don’t know your code or W3’s code, but it seems safer to erase and then create?

UPDATE: I swapped the order and it doesn’t make any difference to the “random” last_run_time false vs a timestamp.

• This reply was modified 1 year, 11 months ago by jondaley.

wp_using_ext_object_cache() is true.
wp_cache_get(static::ACTIVATION_TRANSIENT)
returns false very often.

That is at least part of the problem.

• This reply was modified 1 year, 11 months ago by jondaley.

My log messages (“none” means it is a cronjob or otherwise unauthenticated user), a (##) means a logged in user is viewing a page.

last_run_timeC means the ext_cache is true and returned that number, and then the init: is right at the top of init() and then if it prints wp_cache_set, it fell through to the ->up(), etc.

2023-04-27 15:03:26: (None) last_run_timeC: 1682607800
2023-04-27 15:03:26: (None) init: 1682607800
2023-04-27 15:03:38: (None) last_run_timeC: 1682607800
2023-04-27 15:03:38: (None) init: 1682607800
2023-04-27 15:03:44: (None) last_run_timeC: 1682607800
2023-04-27 15:03:44: (None) init: 1682607800
2023-04-27 15:03:56: (None) last_run_timeC: 1682607800
2023-04-27 15:03:56: (None) init: 1682607800
2023-04-27 15:03:57: (5) last_run_timeC:
2023-04-27 15:03:57: (5) init:
2023-04-27 15:03:57: (5) wp_cache_set
2023-04-27 15:03:57: (5) updating the events calendar
2023-04-27 15:03:57: (5) last_run_timeC:
2023-04-27 15:03:57: (5) init:
2023-04-27 15:03:57: (5) wp_cache_set
2023-04-27 15:03:57: (5) updating the events calendar

• This reply was modified 1 year, 11 months ago by jondaley.

enabling the writing of transients to the database doesn’t make any difference (probably because your ext_cache check, so you don’t ever use database transients if W3 is installed?)

memcached is using 87MB out of 256MB, so presumably the activation transient isn’t getting kicked out of the cache within seconds of being set…

Not sure what this means, if anything. I expected to see the timestamp in the cache.

>/usr/share/memcached/scripts/memcached-tool ~/memcached.socket dump | grep -a tec_custom_tables_v1_initialized
Dumping memcache contents
a:7:{s:10:”last_error”;s:0:””;s:10:”last_query”;s:109:”SELECT option_value FROM wp_options WHERE option_name = ‘_transient_tec_custom_tables_v1_initialized’ LIMIT 1″;s:11:”last_result”;a:0:{}s:8:”col_info”;a:1:{i:0;O:8:”stdClass”:13:{s:4:”name”;s:12:”option_value”;s:7:”orgname”;s:12:”option_value”;s:5:”table”;s:10:”wp_options”;s:8:”orgtable”;s:10:”wp_options”;s:3:”def”;s:0:””;s:2:”db”;s:15:”brett_strenuous”;s:7:”catalog”;s:3:”def”;s:10:”max_length”;i:0;s:6:”length”;i:4294967295;s:9:”charsetnr”;i:246;s:5:”flags”;i:4113;s:4:”type”;i:252;s:8:”decimals”;i:0;}}s:8:”num_rows”;i:0;s:10:”return_val”;i:0;s:11:”key_version”;i:4045940;}

a:7:{s:10:”last_error”;s:0:””;s:10:”last_query”;s:117:”SELECT option_value FROM wp_options WHERE option_name = ‘_transient_timeout_tec_custom_tables_v1_initialized’ LIMIT 1″;s:11:”last_result”;a:0:{}s:8:”col_info”;a:1:{i:0;O:8:”stdClass”:13:{s:4:”name”;s:12:”option_value”;s:7:”orgname”;s:12:”option_value”;s:5:”table”;s:10:”wp_options”;s:8:”orgtable”;s:10:”wp_options”;s:3:”def”;s:0:””;s:2:”db”;s:15:”brett_strenuous”;s:7:”catalog”;s:3:”def”;s:10:”max_length”;i:0;s:6:”length”;i:4294967295;s:9:”charsetnr”;i:246;s:5:”flags”;i:4113;s:4:”type”;i:252;s:8:”decimals”;i:0;}}s:8:”num_rows”;i:0;s:10:”return_val”;i:0;s:11:”key_version”;i:4045943;}

• This reply was modified 1 year, 11 months ago by jondaley.

If I call wp_cache_get directly after the wp_cache_set, it works properly, but the next visit to the site may or may not contain the set value…

weird.

disabling the object cache switches to transients and this function starts working as expected.

Hi @jondaley

Thanks for providing additional details. I’ll make sure to share this with the team.

Just to confirm, is by disabling the object cache resolves the issue?

In addition, you mentioned about W3 total cache, could you configure it based on the suggestions provided in this article, and see if that makes a difference?

I don’t use minify via W3. I do use cloudflare, which I think minifies things. Sounds like you need to do some edits on your code if minifying breaks it?

Hi @jondaley

Just to confirm, are you still seeing the ALTER?statements after you disable the object cache?