• Resolved jondaley

    (@jondaley)


    This appears to have started with a recent update. I can’t find the code that is doing it, but there is some bug that keeps trying to upgrade the database over and over again, which locks the tables, and brings the site to a halt.

    Can you point me to the place in your code where it runs such commands like:

    ALTER TABLE wp_tec_occurrences CHANGE COLUMN start_date start_date VARCHAR(19) NOT NULL

    I’m not doing anything in the plugin particularly, just normal site visitors (or maybe a cron job?) are triggering this update over and over again (and lots more queries just like it, on lots/every? column.

    • This topic was modified 1 year, 1 month ago by jondaley.
Viewing 15 replies - 1 through 15 (of 19 total)
  • Plugin Support Darian

    (@d0153)

    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
    Thread Starter jondaley

    (@jondaley)

    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.

    Plugin Support Darian

    (@d0153)

    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.

    Thread Starter jondaley

    (@jondaley)

    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 |

    Thread Starter jondaley

    (@jondaley)

    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, 1 month ago by jondaley.
    Thread Starter jondaley

    (@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, 1 month ago by jondaley.
    Thread Starter jondaley

    (@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, 1 month ago by jondaley.
    Thread Starter jondaley

    (@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?)

    Thread Starter jondaley

    (@jondaley)

    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…

    Thread Starter jondaley

    (@jondaley)

    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, 1 month ago by jondaley.
    Thread Starter jondaley

    (@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.

    Thread Starter jondaley

    (@jondaley)

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

    Plugin Support Darian

    (@d0153)

    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?

    Thread Starter jondaley

    (@jondaley)

    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?

    Plugin Support Darian

    (@d0153)

    Hi @jondaley

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

Viewing 15 replies - 1 through 15 (of 19 total)
  • The topic ‘ALTER TABLE commands running multiple times, bogging down site’ is closed to new replies.