DB-Error: too big data for insert

(@krut1)

Hey people, we are using WooCommerce in Multisite and we have big problem with some blogs.

You tries to insert in the DB more than 46Mb as plain text 🙁

Our logs look like:

[16-Jun-2020 11:14:37 UTC] WordPress-Datenbank-Fehler Got a packet bigger than 'max_allowed_packet' bytes für Abfrage UPDATE 458_options SET option_value = '...' WHERE option_name = '_transient_wc_layered_nav_counts_pa_brand';
von require('wp-blog-header.php')
require_once('wp-includes/template-loader.php'),
include('/plugins/elementor/modules/page-templates/templates/header-footer.php'),
Elementor\Modules\PageTemplates\Module->print_content,
call_user_func,
ElementorPro\Modules\ThemeBuilder\Classes\Locations_Manager->ElementorPro\Modules\ThemeBuilder\Classes\{closure},
ElementorPro\Modules\ThemeBuilder\Classes\Locations_Manager->do_location
ElementorPro\Modules\ThemeBuilder\Documents\Theme_Document->print_content,
ElementorPro\Modules\ThemeBuilder\Documents\Theme_Document->get_content
Elementor\Core\Base\Document->get_content
Elementor\Frontend->get_builder_content
ElementorPro\Modules\ThemeBuilder\Documents\Theme_Document->print_elements_with_wrapper
Elementor\Core\Base\Document->print_elements
Elementor\Element_Base->print_element
Elementor\Element_Base->_print_content
Elementor\Element_Base->print_element
Elementor\Element_Base->_print_content
Elementor\Element_Base->print_element
Elementor\Element_Base->_print_content
Elementor\Element_Base->print_element
Elementor\Element_Base->_print_content
Elementor\Element_Base->print_element
Elementor\Widget_Base->_print_content
Elementor\Widget_Base->render_content
Elementor\Widget_Sidebar->render
dynamic_sidebar
WP_Widget->display_callback
WC_Widget_Layered_Nav->widget
WC_Widget_Layered_Nav->layered_nav_list
WC_Widget_Layered_Nav->get_filtered_term_product_counts
set_transient
update_option

Version of WooCommerce is 4.0.1

This topic was modified 5 years, 11 months ago by krut1.

Viewing 5 replies - 1 through 5 (of 5 total)

Nicola Mustone
(@nicolamustone)

Automattic Happiness Engineer

5 years, 11 months ago
Hello there,
This issue seems to be caused by Elementor. 46mb of plain text for a regular product would mean 46 million characters. The average amount of characters for a word is 5. That takes us to 9.2 million words in your product. A book in average has 90 thousand words. This means that your product content is the equivalent of 102 books.

This is obviously not realistically possible, except when plugins are considered as well. Even in that case though, it is still way too much.

It must be caused by conflict of some sort. This kind of problem is usually caused by either a conflict with your theme or with another plugin.

The best way to determine this is to:
- Temporarily switch your theme to Storefront
- Disable all plugins except for WooCommerce
- Repeat the action that is causing the problem
If you’re not seeing the same problem after completing the conflict test, then you know the problem was with the plugins and/or theme you deactivated. To figure out which plugin is causing the problem, reactivate your other plugins one by one, testing after each, until you find the one causing conflict. You can find a more detailed explanation on how to do a conflict test here.
Thread Starter krut1
(@krut1)

5 years, 11 months ago
Hi, thanks for your answer. As you can see, the problem happens in WC_Widget_Layered_Nav->get_filtered_term_product_counts. Or rather, in the process of storing the query result in the cache: set_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ), $cached_counts, DAY_IN_SECONDS );.

The result of the query should be an array such this:
```
Array
(
    [46] => 2
    [47] => 2
    [48] => 2
    [58] => 1
)
```
right?

How can be there this:

[17-Jun-2020 07:24:24 UTC] WordPress-Datenbank-Fehler Got a packet bigger than 'max_allowed_packet' bytes für Abfrage UPDATE 458_options SET option_value = 'a:67395:{i:0;b:0;s:32:\"7b067ca573b7add02bb674547eaeba92\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:122;i:1;i:123;i:1;i:124;i:1;i:125;i:1;i:126;i:1;i:127;i:1;i:128;i:1;i:129;i:1;i:130;i:1;i:131;i:1;i:132;i:1;i:133;i:1;i:134;i:1;i:135;i:1;i:136;i:1;}s:32:\"d6bada27888dbe49999720c37404bfa7\";a:0:{}s:32:\"eeceb1d0b7618678813d0ee468efeda4\";a:0:{}s:32:\"dbd9941eef4978fa293ba704d305037c\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:122;i:1;i:123;i:1;i:124;i:1;i:125;i:1;i:126;i:1;i:127;i:1;i:128;i:1;i:129;i:1;i:130;i:1;i:131;i:1;i:132;i:1;i:133;i:1;i:134;i:1;i:135;i:1;i:136;i:1;}s:32:\"88885b436ffb52642c3597348d659d3b\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:122;i:1;i:123;i:1;i:124;i:1;i:125;i:1;i:126;i:1;i:127;i:1;i:128;i:1;i:129;i:1;i:130;i:1;i:131;i:1;i:132;i:1;i:133;i:1;i:134;i:1;i:135;i:1;i:136;i:1;}s:32:\"f40a1c63d1daffbcef62cf25ebcaf993\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:122;i:1;i:123;i:1;i:124;i:1;i:125;i:1;i:126;i:1;i:127;i:1;i:128;i:1;i:129;i:1;i:130;i:1;i:131;i:1;i:132;i:1;i:133;i:1;i:134;i:1;i:135;i:1;i:136;i:1;}s:32:\"d7b5aedde99a4a2add046065565ee0e3\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:122;i:1;i:123;i:1;i:124;i:1;i:125;i:1;i:126;i:1;i:127;i:1;i:128;i:1;i:129;i:1;i:130;i:1;i:131;i:1;i:132;i:1;i:133;i:1;i:134;i:1;i:135;i:1;i:136;i:1;}s:32:\"2f2f970e3d25b004b2319caa96bb697f\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:122;i:1;i:123;i:1;i:124;i:1;i:125;i:1;i:126;i:1;i:127;i:1;i:128;i:1;i:129;i:1;i:130;i:1;i:131;i:1;i:132;i:1;i:133;i:1;i:134;i:1;i:135;i:1;i:136;i:1;}s:32:\"1d65e6190441581ef8d9cfa24835fbe9\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:122;i:1;i:123;i:1;i:124;i:1;i:125;i:1;i:126;i:1;i:127;i:1;i:128;i:1;i:129;i:1;i:130;i:1;i:131;i:1;i:132;i:1;i:133;i:1;i:134;i:1;i:135;i:1;i:136;i:1;}s:32:\"fc6beb150f52f9ca773559002281ad5c\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:122;i:1;i:123;i:1;i:124;i:1;i:125;i:1;i:126;i:1;i:127;i:1;i:128;i:1;i:129;i:1;i:130;i:1;i:131;i:1;i:132;i:1;i:133;i:1;i:134;i:1;i:135;i:1;i:136;i:1;}s:32:\"e65d0c7eb8efd3b83e3e2281babfc6fe\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:122;i:1;i:123;i:1;i:124;i:1;i:125;i:1;i:126;i:1;i:127;i:1;i:128;i:1;i:129;i:1;i:130;i:1;i:131;i:1;i:132;i:1;i:133;i:1;i:134;i:1;i:135;i:1;i:136;i:1;}s:32:\"422189d5401122809ff27c76be64ce97\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:122;i:1;i:123;i:1;i:124;i:1;i:125;i:1;i:126;i:1;i:127;i:1;i:128;i:1;i:129;i:1;i:130;i:1;i:131;i:1;i:132;i:1;i:133;i:1;i:134;i:1;i:135;i:1;i:136;i:1;}s:32:\"e14e2fe1899d369a097423b8ef00ce37\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:122;i:1;i:123;i:1;i:124;i:1;i:125;i:1;i:126;i:1;i:127;i:1;i:128;i:1;i:129;i:1;i:130;i:1;i:131;i:1;i:132;i:1;i:133;i:1;i:134;i:1;i:135;i:1;i:136;i:1;}s:32:\"5f1f86bc9649f25c63938b131c40b00f\";a:20:{i:117;i:1;i:118;i:1;i:119;i:1;i:120;i:1;i:121;i:1;i:12 ... WHERE option_name = '_transient_wc_layered_nav_counts_pa_brand';

It looks not like an array, or?
- This reply was modified 5 years, 11 months ago by krut1.
- This reply was modified 5 years, 11 months ago by krut1.
- This reply was modified 5 years, 11 months ago by krut1.
Thread Starter krut1
(@krut1)

5 years, 11 months ago
I have found the problem in your code. I try to explain it you. Look please in the function get_filtered_term_product_counts. There is this part of code:
```
// Maybe store a transient of the count values.
$cache = apply_filters( 'woocommerce_layered_nav_count_maybe_cache', true );
if ( true === $cache ) {
  $cached_counts = (array) get_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ) );
} else {
  $cached_counts = array();
}

if ( ! isset( $cached_counts[ $query_hash ] ) ) {
  $results  = $wpdb->get_results( $query, ARRAY_A ); // @codingStandardsIgnoreLine
  $counts   = array_map( 'absint', wp_list_pluck( $results, 'term_count', 'term_count_id' ) );

  $cached_counts[ $query_hash ] = $counts;
  if ( true === $cache ) {
    set_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ), $cached_counts, DAY_IN_SECONDS );
  }
}
```
If we have the widget Filter Products by Attribute for taxonomy color (for example), then you will save in transient wc_layered_nav_counts_pa_color this array:
```
Array(
  '7b067ca573b7add02bb674547eaeba92' => [46 => 2, 47 => 2]
)
```
ATTENTION! If we have a search function on our website, and our user looks for something, then this array will be increased:
```
Array(
  '7b067ca573b7add02bb674547eaeba92' => [46 => 2, 47 => 2],
  'd41d8cd98f00b204e9800998ecf8427e' => [46 => 1, 47 => 0]
)
```
At the request of the next user, it will be increased again:
```
Array(
  '7b067ca573b7add02bb674547eaeba92' => [46 => 2, 47 => 2],
  'd41d8cd98f00b204e9800998ecf8427e' => [46 => 1, 47 => 0],
  '74be16979710d4c4e7c6647856088456' => [46 => 1, 47 => 0]
)
```
And each time there is an update of this transient for the next 24 hours:

set_transient( 'wc_layered_nav_counts_' . sanitize_title( $taxonomy ), $cached_counts, DAY_IN_SECONDS );

Thus, the transient will never become outdated (because users use the search at least once a day) and its size will only grow.
- This reply was modified 5 years, 11 months ago by krut1.
Thread Starter krut1
(@krut1)

5 years, 10 months ago

Why did you close this issue? This problem is still not resolved. Please fix at first. Description of the problem is in the preview message.

Thank you.

Thread Starter krut1
(@krut1)

5 years, 9 months ago

Hey guys, do you have a solution?

Viewing 5 replies - 1 through 5 (of 5 total)

The topic ‘DB-Error: too big data for insert’ is closed to new replies.