• Resolved hungwenchun

    (@hungwenchun)


    I love Spectra! But there is a big problem such as 2.0.14 DB error | WordPress.org, Spectra gone crazy on wp_actionscheduler_actions | WordPress.org and Cant enable plugin / spectra_total_blocks_count_action | WordPress.org, it is still not resolved yet.

    The row “spectra_blocks_count_status” will lead to huge WP_OPTIONS table, even up to 10GB in my case. But there is no data in WP_OPTIONS table and table keep big size. And then it is hard to backup.

    The status of “spectra_blocks_count_status” is autoload, then creating many ‘wp_collect_spectra_blocks_count_batch_[hexdec]’. If I enable Redis Cache, it will creating automatically.

    Now I set the autoload status of “spectra_blocks_count_status” as NO and turn off Redis Cache, finally it stop to lead to huge WP_OPTIONS table.

    Actually I don’t understand why “spectra_blocks_count_status” should exist and autoload. Hope it will disappear in future.

    • This topic was modified 1 year, 4 months ago by hungwenchun.
    • This topic was modified 1 year, 4 months ago by hungwenchun.
Viewing 15 replies - 1 through 15 (of 15 total)
  • I sent a ticket about this with all the error_log they need to troubleshoot and they can’t figure it out without me making them a test instance – lol, no thanks, This plugin does not work and people with it installed need to know.

    YES, thank you for that! I kept blowing past my available memory and knew it was in the options table, but couldn’t the cause. Setting to ‘no’ brought my admin area back online.

    Edit:

    Full fix for me, which dropped my wp_options table from 1.8GB to 9MB. This might be a little overkill, but it seems to be based on data collection, so you definitely need to stop the data from generating or it comes right back.

    1. Go to wp-admin -> settings -> general. On the very bottom of that page, uncheck the box that says to allow Brainstorm Force to collect data.
    2. Run the following database commands:
      • DELETE FROM wp_options WHERE option_name LIKE 'wp_collect_spectra%';
      • UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_count_status';
      • UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_pages_counted';
    • This reply was modified 1 year, 4 months ago by Sageth. Reason: Added fix
    • This reply was modified 1 year, 4 months ago by Sageth.
    Thread Starter hungwenchun

    (@hungwenchun)

    Thanks for great and complete solution. It did work and help people facing the same problem.

    Actually, I think it is not overkill and safe until Brainstorm Force can really solve the problem.

    Seems that it is not helping to open a ticket on https://wpspectra.com/support/ until now.

    • This reply was modified 1 year, 4 months ago by hungwenchun.
    • This reply was modified 1 year, 4 months ago by hungwenchun.

    Holy mackerel, I had no idea they injected that tracking nonsense – totally unethical and ineptly programmed – what a stupid debacle.

    I have the same bug. I have spoken with Spectra (Brainstormforce) about it but they seem unwilling to remove the plugin component that causes the fault.

    For me the bug shows when using WP LiteSpeed Cache. When WPLS is disabled Spectra stops collecting the data. I think I know roughly what’s happening. Maybe this info will help Spectra solve the problem.

    Thanks to your tip about setting autoload to ‘no’ on the ‘spectra_blocks_count_status’ row I now see that when WPLS is enabled that row reverts back to autoload=yes. I tested several times.

    The issue appears to not happen when other cache plugins are used (I’ve tried with a few of them). With WPLS disabled I can set autoload=no and it will stay as autoload=no. Immediately I enable WPLS autoload=no switches to autoload=yes and within 2 minutes I have a wp_options table exceeding 2GB in size.

    On a VPS server or any server with finite storage space, when the InnoDB database grows beyond the server’s storage capacity the database server crashes. That crash can damage any databases beyond repair. It happened to me twice on two different servers due to this bug in Spectra.

    WPLS is a good plugin. Spectra was a great plugin. I just wish Spectra would fix this bug and stop denying that it exists. What do they want the data for? Is it even legal for Spectra to keep collecting the data?

    Note that even with autoload off, Spectra is still trying to make the queries and failing per my log (old log, but new lines are the same) – turn on WP_DEBUG and check for yourselves:

    2023/01/07 10:36:35 [error] 1127#1127: *40274 FastCGI sent in stderr: “PHP message: PHP Warning: Packets out of order. Expected 3 received 2. Packet size=60 in /var/www/html/myhost.com/public_html/wp-includes/class-wpdb.php on line 2187PHP message: PHP Warning: Packets out of order. Expected 3 received 2. Packet size=60 in /var/www/html/myhost.com/public_html/wp-includes/class-wpdb.php on line 2187PHP message: WordPress database error MySQL server has gone away for query INSERT INTO wp_options (option_nameoption_valueautoload) VALUES (‘wp_collect_spectra_blocks_count_batch_a0a4292e5f6a79959c087deeaa’, ‘a:3975:{i:0;a:2:{s:4:\”data\”;i:149806;s:11:\”list_blocks\”;a:68:{s:21:\”uagb/advanced-heading\”;a:11:{s:4:\”slug\”;s:0:\”\”;s:16:\”admin_categories\”;a:2:{i:0;s:7:\”content\”;i:1;s:4:\”core\”;}s:4:\”link\”;s:16:\”advanced-heading\”;s:3:\”doc\”;s:16:\”advanced-heading\”;s:5:\”title\”;s:7:\”Heading\”;s:11:\”description\”;s:57:\”Add heading, sub heading and a separator using one block.\”;s:7:\”default\”;b:1;s:9:\”extension\”;b:0;s:8:\”priority\”;i:2;s:10:\”deprecated\”;b:0;s:14:\”dynamic_assets\”;a:1:{s:3:\”dir\”;s:16:\”advanced-heading\”;}}s:15:\”uagb/blockquote\”;a:11:{s:3:\”doc\”;s:10:\”blockquote\”;s:4:\”slug\”;s:0:\”\”;s:16:\”admin_categories\”;a:1:{i:0;s:6:\”social\”;}s:4:\”link\”;s:10:\”blockquote\”;s:5:\”title\”;s:10:\”Blockquote\”;s:11:\”description\”;s:45:\”Display qoutes/” while reading response header from upstream, client: cli.ent.ip.addy, server: ser.ver.ip.addy, request: “POST /wp-admin/admin-ajax.php HTTP/1.1”, upstream: “fastcgi://unix:/var/run/php/php7.4-fpm.sock:”, host: “myhost.com”, referrer: “https://myhost.com/wp-login.php”

    Here is another option which also doesn’t solve the root problem, but may help in certain cases, like litespeed cache.

    Create a mysql event to delete the posts hourly. This does require turning on the event scheduler, which may not be available, particularly on shared hosting. If you’re managing your own database, you can do this (change items <marked> as applicable to your own database:

    SET DELIMITER ;;
    
    CREATE EVENT wp_options_cleanup ON SCHEDULE EVERY 1 HOUR STARTS '2023-01-01 00:15:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Cleanup of Spectra events' DO BEGIN
    
    -- CLEAN UP HUGE wp_options FROM SPECTRA
    DELETE FROM <schema>.wp_options WHERE option_name LIKE 'wp_collect_spectra%';
    
    END;;
    SET DELIMITER ;

    Then go to /etc/my.cnf (or applicable file for your system) and under the [mysqld] section, add a line like this — a reboot may be required, but I think you can just restart the sql service:

    [mysqld]
    event_scheduler=ON

    This will delete the entries every hour and might buy some time.

    Wish I had a better answer… but like you, it doesn’t matter how good the plugin is, it can’t bring so many systems to a grind and while it’s going to be a PITA, I may need to start looking at other options as well.

    Random question: I have more than 8000 entries in my wp_posts table on my site. Is this happening to anyone with only a few posts for a static-ish site?

    • This reply was modified 1 year, 4 months ago by Sageth. Reason: Didn't finish my question. :/
    • This reply was modified 1 year, 4 months ago by Sageth. Reason: Clarifying
    Thread Starter hungwenchun

    (@hungwenchun)

    I try to share this issue on SPECTRA official FB group: https://www.facebook.com/groups/wpspectra.

    Then the post was rejected and deleted. What a pity!

    Manners Maketh Plugin!

    • This reply was modified 1 year, 4 months ago by hungwenchun.

    Anyone know where this query INSERT INTO wp_options (option_nameoption_valueautoload) VALUES (‘wp_collect_spectra_blocks_count_batch…

    is happening that keeps failing so I can stop it?

    Thread Starter hungwenchun

    (@hungwenchun)

    I am not a programmer, what I can do is try and error. Based on my tries.

    When you run commands below:

    • DELETE FROM wp_options WHERE option_name LIKE 'wp_collect_spectra%';
    • UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_count_status';
    • UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_pages_counted';

    There are 8 rows of SPECTRA in wp-options. When running some cache or database cleaning plugins, and cleaning _transient data in wp-options. The row of “spectra_settings_data” disappeared.

    Now autoload status of “spectra_blocks_count_status” became YES again, and 3 or more rows created with nightmare.

    And try to run commands again, and clean 3 or more new rows created. There will be 7 rows left.

    • DELETE FROM wp_options WHERE option_name LIKE 'wp_collect_spectra%';
    • UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_count_status';
    • UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_pages_counted';

    Finally, go to wp-admin and refresh setting page of SPECTRA. The disappeared row of “spectra_settings_data” comeback again.

    If the _transient of SPECTRA is cleaned, do it again and again, again, again, again, again, again, again, again, again…………………….

    Thread Starter hungwenchun

    (@hungwenchun)

    Spectra 2.3.1 released. It solved this problem for me.

    Anyone still in trouble?

    Thanks for Spectra. I love this plugin.

    Plugin Support BSF – Judith Pete

    (@judith0)

    Hello @bcolflesh @sageth @leehodson

    We have released current version 2.3.1 which has fixed this issue. Please update and let us know whether you are still experiencing these issues.

    Thanks.

    Thread Starter hungwenchun

    (@hungwenchun)

    Now I faced a problem.

    When I edited and saved a custom layout of Astra using Spectra’s blocks. All related posts with edited custom layout, showing a message “There has been a critical error on this website.”

    • This reply was modified 1 year, 4 months ago by hungwenchun.
    Plugin Support mohsinbsf

    (@mohsinbsf)

    Hi @hungwenchun,

    Sorry for the inconvenience caused to you.

    I am not able to replicate the issue on my installation. Please get in touch with our support team from our website so that they can investigate the issue in detail and assist you.

    Have a nice day!

    Plugin Support BSF – Judith Pete

    (@judith0)

    Hello @hungwenchun,

    It’s been a while since we heard from you. I am going to mark this as resolved. Please feel free to open a new thread in case of any other queries.

    Regards.

    Judith.

Viewing 15 replies - 1 through 15 (of 15 total)
  • The topic ‘Huge WP_OPTIONS table size!’ is closed to new replies.