Jili demo games online free download.Claim Your Free 999 Pesos Bonus Today

Resolved

(@gutding)

Hi there,
this thing took my almost 2 days to figure out…
My website became very slow the last couple of days. As our hosting service had problems with the performance I thought it was because of these problems.
Yesterday morning my site kind of crashed. I wasn’t able to access WC orders in the backend (frontend was still working fine). My error log first showed memory limit problems (I had 256M configured). I changed memory to 2GB. Then I ran into script time-outs (30s). I also changed that to 300s. Then I got memory limit problems again (2GB weren’t enough!). The last line of my error-logs stack trace showed
WC_Order->read_meta_data() /home/www...
So I fired up php-myadmin and had a look at wp_postmeta.
It had a whopping 1.000.000 entries. As wp_postmeta has no index at all and no unique column(s), I searched for duplicate entries:
SELECT *, COUNT(meta_key) as num_clones from wp_postmeta group by meta_id, post_id, meta_key, meta_value having num_clones>1 order by num_clones desc;
I’ve found several entries with >100.000 duplicate entries each!
So I had to clean-up my table:

-- create new postmeta
CREATE TABLE wp_postmeta_new (
  meta_id bigint(20) UNSIGNED NOT NULL,
  post_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  meta_key varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  meta_value longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- copy the data (without duplicates)
INSERT INTO wp_postmeta_new (meta_id, post_id, meta_key, meta_value)
SELECT DISTINCT meta_id, post_id, meta_key, meta_Value FROM wp_postmeta order by meta_id, post_id, meta_key, meta_value;

-- swap tables
RENAME TABLE wp_postmeta
	TO wp_postmeta_orig;
	
RENAME TABLE wp_postmeta_new
	TO wp_postmeta;

This way my wp_postmeta shrank from appr. 1,000,000 entries to around 25,000.
Good thing was I could work again. Bad thing is, the error, that causes the inflation of my table is still there.
I made a copy of my site to my staging site. I did de-activate all plugins except for WC (plus the send no mail plugin). When I change the order status on the order detail page from something to something else, I will find 17 entries in wp_postmeta (from 67 total) for that order 6-fold. Interestingly that effects only metadata from other WC-plugins (which are de-activated at that time).
I have no clue why WC is doing that.

Here is my system-status:


### WordPress Environment ###

Home URL: https://dev.gutding.org
Site URL: https://dev.gutding.org
WC Version: 3.5.6
Log Directory Writable: ?
WP Version: 5.1.1
WP Multisite: –
WP Memory Limit: 2 GB
WP Debug Mode: ?
WP Cron: ?
Language: de_DE
External object cache: –

### Server Environment ###

Server Info: Apache
PHP Version: 7.2.15-1+0~20190209065123.16+stretch~1.gbp3ad8c0
PHP Post Max Size: 150 MB
PHP Time Limit: 300
PHP Max Input Vars: 5000
cURL Version: 7.52.1
OpenSSL/1.0.2q

SUHOSIN Installed: –
MySQL Version: 5.5.5-10.2.12-MariaDB-log
Max Upload Size: 150 MB
Default Timezone is UTC: ?
fsockopen/cURL: ?
SoapClient: ?
DOMDocument: ?
GZip: ?
Multibyte String: ?
Remote Post: ?
Remote Get: ?

### Database ###

WC Database Version: 3.5.6
WC Database Prefix: wp_
Datenbank-Gesamtgr??e: 51.05MB
Datenbank-Datengr??e: 49.58MB
Datenbank-Indexgr??e: 1.47MB
wp_woocommerce_sessions: Daten: 0.00MB + Index: 0.00MB
wp_woocommerce_api_keys: Daten: 0.00MB + Index: 0.00MB
wp_woocommerce_attribute_taxonomies: Daten: 0.00MB + Index: 0.00MB
wp_woocommerce_downloadable_product_permissions: Daten: 0.00MB + Index: 0.00MB
wp_woocommerce_order_items: Daten: 0.04MB + Index: 0.02MB
wp_woocommerce_order_itemmeta: Daten: 0.55MB + Index: 0.25MB
wp_woocommerce_tax_rates: Daten: 0.01MB + Index: 0.01MB
wp_woocommerce_tax_rate_locations: Daten: 0.00MB + Index: 0.00MB
wp_woocommerce_shipping_zones: Daten: 0.00MB + Index: 0.00MB
wp_woocommerce_shipping_zone_locations: Daten: 0.00MB + Index: 0.00MB
wp_woocommerce_shipping_zone_methods: Daten: 0.00MB + Index: 0.00MB
wp_woocommerce_payment_tokens: Daten: 0.00MB + Index: 0.00MB
wp_woocommerce_payment_tokenmeta: Daten: 0.00MB + Index: 0.00MB
wp_woocommerce_log: Daten: 0.00MB + Index: 0.00MB
wp_acm_cron_logs: Daten: 0.02MB + Index: 0.00MB
wp_bounced_email_logs: Daten: 0.02MB + Index: 0.00MB
wp_chanimator: Daten: 0.01MB + Index: 0.00MB
wp_commentmeta: Daten: 0.00MB + Index: 0.00MB
wp_comments: Daten: 0.09MB + Index: 0.00MB
wp_csshero3: Daten: 0.26MB + Index: 0.00MB
wp_elm_daily_stats: Daten: 0.06MB + Index: 0.00MB
wp_elm_hourly_stats: Daten: 0.02MB + Index: 0.00MB
wp_elm_summary: Daten: 5.52MB + Index: 0.00MB
wp_elm_summary_progress: Daten: 0.02MB + Index: 0.00MB
wp_gdpr_data_register: Daten: 0.00MB + Index: 0.00MB
wp_gdpr_del_requests: Daten: 0.00MB + Index: 0.00MB
wp_gdpr_log: Daten: 0.01MB + Index: 0.00MB
wp_gdpr_requests: Daten: 0.00MB + Index: 0.00MB
wp_links: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_custom_fields: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_forms: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_log: Daten: 0.02MB + Index: 0.00MB
wp_mailpoet_mapping_to_external_entities: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_newsletters: Daten: 0.32MB + Index: 0.00MB
wp_mailpoet_newsletter_links: Daten: 0.19MB + Index: 0.11MB
wp_mailpoet_newsletter_option: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_newsletter_option_fields: Daten: 0.00MB + Index: 0.01MB
wp_mailpoet_newsletter_posts: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_newsletter_segment: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_newsletter_templates: Daten: 4.51MB + Index: 0.00MB
wp_mailpoet_scheduled_tasks: Daten: 0.01MB + Index: 0.01MB
wp_mailpoet_scheduled_task_subscribers: Daten: 0.05MB + Index: 0.06MB
wp_mailpoet_segments: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_sending_queues: Daten: 0.53MB + Index: 0.00MB
wp_mailpoet_settings: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_statistics_clicks: Daten: 0.00MB + Index: 0.01MB
wp_mailpoet_statistics_forms: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_statistics_newsletters: Daten: 0.01MB + Index: 0.03MB
wp_mailpoet_statistics_opens: Daten: 0.01MB + Index: 0.02MB
wp_mailpoet_statistics_unsubscribes: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_stats_notifications: Daten: 0.02MB + Index: 0.03MB
wp_mailpoet_subscribers: Daten: 0.02MB + Index: 0.02MB
wp_mailpoet_subscriber_custom_field: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_subscriber_ips: Daten: 0.00MB + Index: 0.00MB
wp_mailpoet_subscriber_segment: Daten: 0.01MB + Index: 0.01MB
wp_options: Daten: 2.73MB + Index: 0.00MB
wp_postmeta: Daten: 2.83MB + Index: 0.00MB
wp_postmeta_backup: Daten: 2.87MB + Index: 0.00MB
wp_postmeta_old: Daten: 2.83MB + Index: 0.00MB
wp_posts: Daten: 17.49MB + Index: 0.17MB
wp_query_override_terms: Daten: 0.00MB + Index: 0.00MB
wp_query_wrangler: Daten: 0.00MB + Index: 0.00MB
wp_termmeta: Daten: 0.00MB + Index: 0.01MB
wp_terms: Daten: 0.00MB + Index: 0.01MB
wp_term_relationships: Daten: 0.03MB + Index: 0.06MB
wp_term_taxonomy: Daten: 0.02MB + Index: 0.01MB
wp_toolset_post_guid_id: Daten: 0.01MB + Index: 0.01MB
wp_usermeta: Daten: 0.15MB + Index: 0.10MB
wp_users: Daten: 0.01MB + Index: 0.01MB
wp_wcpdf_invoice_number: Daten: 0.00MB + Index: 0.00MB
wp_wc_download_log: Daten: 0.00MB + Index: 0.00MB
wp_wc_webhooks: Daten: 0.00MB + Index: 0.00MB
wp_wfblockediplog: Daten: 0.02MB + Index: 0.00MB
wp_wfblocks7: Daten: 0.02MB + Index: 0.05MB
wp_wfconfig: Daten: 0.45MB + Index: 0.00MB
wp_wfcrawlers: Daten: 0.02MB + Index: 0.00MB
wp_wffilechanges: Daten: 0.02MB + Index: 0.00MB
wp_wffilemods: Daten: 4.52MB + Index: 0.00MB
wp_wfhits: Daten: 0.28MB + Index: 0.08MB
wp_wfhoover: Daten: 0.02MB + Index: 0.02MB
wp_wfissues: Daten: 0.02MB + Index: 0.06MB
wp_wfknownfilelist: Daten: 2.52MB + Index: 0.00MB
wp_wflivetraffichuman: Daten: 0.02MB + Index: 0.02MB
wp_wflocs: Daten: 0.02MB + Index: 0.00MB
wp_wflogins: Daten: 0.02MB + Index: 0.03MB
wp_wfnotifications: Daten: 0.02MB + Index: 0.00MB
wp_wfpendingissues: Daten: 0.02MB + Index: 0.06MB
wp_wfreversecache: Daten: 0.06MB + Index: 0.00MB
wp_wfsnipcache: Daten: 0.02MB + Index: 0.05MB
wp_wfstatus: Daten: 0.13MB + Index: 0.09MB
wp_wftrafficrates: Daten: 0.02MB + Index: 0.00MB
wp_wpgdprc_access_requests: Daten: 0.00MB + Index: 0.00MB
wp_wpgdprc_consents: Daten: 0.00MB + Index: 0.00MB
wp_wpgdprc_delete_requests: Daten: 0.00MB + Index: 0.00MB
wp_wpgdprc_log: Daten: 0.02MB + Index: 0.00MB
wp_yoast_seo_links: Daten: 0.01MB + Index: 0.01MB
wp_yoast_seo_meta: Daten: 0.03MB + Index: 0.03MB

### Post Type Counts ###

attachment: 683
custom_css: 1
da_image: 1
generate_page_header: 1
invoice: 131
is_search_form: 1
jetpack_migration: 2
jp_img_sitemap: 1
jp_sitemap: 1
jp_sitemap_master: 1
jp_vid_sitemap: 1
mailpoet_page: 1
maplist: 576
ml-slide: 5
ml-slider: 1
nav_menu_item: 44
page: 37
popup: 1
popup_theme: 7
post: 68
product: 99
product_variation: 31
shop_coupon: 1
shop_order: 105
shop_order_refund: 5
woo_product_tab: 5
wp-rest-api-log: 59
wpcf7_contact_form: 4
wphtmlmail_mail: 11
yith-wccos-ostatus: 2

### Security ###

Secure connection (HTTPS): ?
Hide errors from visitors: ?

### Active Plugins (2) ###

Disable Emails: von WebAware – 1.4.0
WooCommerce: von Automattic – 3.5.6

### Settings ###

API Enabled: ?
Force SSL: ?
Currency: EUR (€)
Currency Position: right_space
Thousand Separator: .
Decimal Separator: ,
Number of Decimals: 2
Taxonomies: Product Types: external (external)
grouped (grouped)
simple (simple)
variable (variable)
woosb (woosb)

Taxonomies: Product Visibility: exclude-from-catalog (exclude-from-catalog)
exclude-from-search (exclude-from-search)
featured (featured)
outofstock (outofstock)
rated-1 (rated-1)
rated-2 (rated-2)
rated-3 (rated-3)
rated-4 (rated-4)
rated-5 (rated-5)

### WC Pages ###

Shop-Basis: #197 - /shop/
Warenkorb: #199 - /warenkorb/
Kasse: #201 - /kasse/
Mein Konto: #203 - /mein-konto/
Allgemeine Gesch?ftsbedingungen: #212 - /agb/

### Theme ###

Name: Twenty Nineteen
Version: 1.3
Author URL: https://de.www.ads-software.com/
Child Theme: ? – Wenn du WooCommerce mit einem Theme
das du nicht selbst entwickelt hast
an eigene Bedürfnisse anpasst
dann empfehlen wir die Verwendung eines Child-Themes. Siehe Wie du ein Child-Theme erstellst (engl.)

WooCommerce Support: ?

### Templates ###

Overrides: –

Has anybody experienced a similar behaviour yet? Has anybody a clue what’s going on here?

thanks for your support, Rado

Viewing 1 replies (of 1 total)

Thread Starter gutding
(@gutding)

6 years ago
Ok, as I will probably get sick cleaning the wp_postmeta table again and again, I thought of a different approach.
I thought giving wp_postmeta a primary index would be a way to stop WC from adding duplicates. So I added an unique index this way:
```
-- Fix the wp_postmeta problem
ALTER TABLE wp_postmeta ADD u_key CHAR(40);

UPDATE wp_postmeta SET u_key = SHA1(CONCAT(CONVERT(meta_id, char(20)), CONVERT(post_id,char(20)), meta_key, meta_value));

-- ALTER IGNORE automatically deletes all duplicates
-- no need for prior cleansing
ALTER IGNORE TABLE wp_postmeta ADD PRIMARY KEY(u_key);

CREATE TRIGGER SHA1OnInsert BEFORE INSERT ON wp_postmeta
FOR EACH ROW 
  SET NEW.u_key = SHA1(CONCAT(CONVERT(NEW.meta_id, char(20)), CONVERT(NEW.post_id,char(20)), NEW.meta_key, NEW.meta_value));

CREATE TRIGGER SHA1OnUpdate BEFORE UPDATE ON wp_postmeta
FOR EACH ROW 
  SET NEW.u_key = SHA1(CONCAT(CONVERT(NEW.meta_id, char(20)), CONVERT(NEW.post_id,char(20)), NEW.meta_key, NEW.meta_value));
```
I know there is a statistically relevant chance that 2 different value combinations might get the same SHA1 value. I’m going to take that risk. One could minimize this risk by also using the MD5 function. But that would mean bigger index and slower speed…

The upside of this solution is that there will be no further maintenance needed, the downside is that all the now not possible duplicate entries will cause many many entries in my error logs.
One log entry looks like this:
[13-Mar-2019 21:34:23 UTC] WordPress-Datenbank-Fehler Duplicate entry '23a098e9aa4af8a773b6410b73debd9e93fb350a' for key 'PRIMARY' für Abfrage INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES (5746, '_billing_comments', '') von edit_post, wp_update_post, wp_insert_post, do_action('save_post'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Admin_Meta_Boxes->save_meta_boxes, do_action('woocommerce_process_shop_order_meta'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Meta_Box_Order_Items::save, wc_save_order_items, WC_Abstract_Order->update_taxes, WC_Order->save, WC_Data_Store->update, WC_Order_Data_Store_CPT->update, Abstract_WC_Order_Data_Store_CPT->update, WC_Data->save_meta_data, WC_Data_Store->__call, WC_Data_Store_WP->add_meta, add_metadata

Maybe these infos are helpful for anybody who wants to further dig into this and find the flaws in the code…

Regards, Rado

Viewing 1 replies (of 1 total)

The topic ‘multiple duplicate entries in wp_postmeta’ is closed to new replies.