Please make sure to update to WPML 4.3.5 and check our list of Known Issues before reporting

Hi, Amit here, I am the WPML Support Manager, our current ticket queue is high, update your WPML plugins and make sure you meet the minimal requirements for running WPML before reporting an issue please - many tickets are resolved doing that

Please look at our updated list of Known Issues and you can also use our support search to find helpful information and of course review our documentation before opening a ticket.

If you do need to open a ticket please make sure to provide us with all the needed information as described in this page

This is the technical support forum for WPML - the multilingual WordPress plugin.

Everyone can read, but only WPML clients can post here. WPML team is replying on the forum 6 days per week, 22 hours per day.

This topic contains 26 replies, has 3 voices.

Last updated by George L. 2 months, 3 weeks ago.

Assigned support staff: Yvette.

Author Posts
August 14, 2019 at 8:45 am #4393397

George L.

I am trying to: Load admin dashboard after enabling WooCommerce Multilingual

Link to a page where the issue can be seen: /wp-admin

I expected to see: The admin dashboard page

Instead, I got: a 504 gateway timeout after waiting for long.

It appears that when WCML is enabled, the WooCommerce reports queries are taking very long to complete. This only affects Live, since our staging does not have any orders or customer data.

Some queries that take long are the following

SELECT
	COUNT(DISTINCT posts.id) as count,
	posts.post_date as post_date
FROM
	wp_posts AS posts  
WHERE
	posts.post_type IN ( 'shop_order' )
	AND	posts.post_status IN ( 'wc-completed','wc-processing','wc-refunded','wc-missing-post','wc-ready-for-despatc','wc-reviewing','wc-packing-1','wc-packing-2','wc-packing-3','wc-days28','wc-days28packing','wc-special','wc-intl-shipping','wc-renewal-prep','wc-new-order-prep','wc-blade-prep-3','wc-blade-prep-5','wc-blade-prep-6','wc-packing','wc-noths','wc-intl-priority','wc-personalisation','wc-tracked-24','wc-tracked-48','wc-priority-despatch','wc-first-class')
	AND posts.post_date >= '2019-08-01 00:00:00'
	AND	posts.post_date < '2019-08-15 00:00:00'
	AND posts.ID IN ( ALL_ORDER_IDS* )
GROUP BY YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date)
ORDER BY post_date ASC

and

		 
SELECT
	order_items.order_item_name as order_item_name,
	SUM( order_item_meta_discount_amount.meta_value) as discount_amount,
	posts.post_date as post_date
FROM
	wp_posts AS posts
	INNER JOIN wp_woocommerce_order_items AS order_items ON (posts.ID = order_items.order_id) AND (order_items.order_item_type = 'coupon')
	INNER JOIN wp_woocommerce_order_itemmeta AS order_item_meta_discount_amount ON (order_items.order_item_id = order_item_meta_discount_amount.order_item_id) AND (order_item_meta_discount_amount.meta_key = 'discount_amount') 
WHERE
	posts.post_type IN ( 'shop_order' )
	AND posts.post_status 	IN ( 'wc-completed','wc-processing','wc-refunded','wc-missing-post','wc-ready-for-despatc','wc-reviewing','wc-packing-1','wc-packing-2','wc-packing-3','wc-days28','wc-days28packing','wc-special','wc-intl-shipping','wc-renewal-prep','wc-new-order-prep','wc-blade-prep-3','wc-blade-prep-5','wc-blade-prep-6','wc-packing','wc-noths','wc-intl-priority','wc-personalisation','wc-tracked-24','wc-tracked-48','wc-priority-despatch','wc-first-class')
	AND posts.post_date >= '2019-08-01 00:00:00'
	AND posts.post_date < '2019-08-15 00:00:00'
	AND order_items.order_item_type = 'coupon'
	AND posts.ID IN ( ALL_ORDER_IDS* )
GROUP BY YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date), order_item_name
ORDER BY post_date ASC

From what i can tell, when WCML is enabled, the AND posts.ID IN ( ALL_ORDER_IDS* ) is added to the report queries which causes the query to become very slow

* ALL_ORDER_IDS indicates literarily all order IDs separated by comma. The bigger the site, the more IDs are there

August 14, 2019 at 1:45 pm #4395587

Yvette
Supporter

Languages: English (English ) Spanish (Español )

Timezone: Europe/Madrid (GMT+01:00)

Hello

I will be helping you with these questions. I¨ve forwarded the case and your questions as you have phrased them to our 2nd tier support group for inputs.

I will get back to you once I have some news.

August 14, 2019 at 3:10 pm #4396279

Yvette
Supporter

Languages: English (English ) Spanish (Español )

Timezone: Europe/Madrid (GMT+01:00)

Hello

Our 2nd tier support group has asked that you try the following code to deactivate the filter instead:

global $woocommerce_wpml;
remove_filter('woocommerce_reports_get_order_report_query', array($woocommerce_wpml->multi_currency->reports, 'filter_dashboard_status_widget_sales_query'));

The note: "A hook can not be removed with a class name if it was added with an object.
Please wrap the code to some hook to make sure WCML classes are loaded before removing."

If removing this hook fixes the issue as you suspect, then please notify me so that I can then escalte this issue for debugging to our 2nd tier group.

Thanks

August 14, 2019 at 3:33 pm #4396439

George L.

Hi Yvette,

The remove_filter you sent me seems to have solved the issue on the second query, but the first query i mentioned is still experiencing the same issue.

I imagine there's a different filter for that query?

August 14, 2019 at 4:07 pm #4396593

George L.

I updated your code slightly to the following

global $woocommerce_wpml;
			remove_filter('woocommerce_reports_get_order_report_query', array($woocommerce_wpml->multi_currency->reports, 'filter_dashboard_status_widget_sales_query'));
			remove_filter('woocommerce_dashboard_status_widget_sales_query', array($woocommerce_wpml->multi_currency->reports, 'filter_dashboard_status_widget_sales_query'));
			remove_filter('woocommerce_dashboard_status_widget_top_seller_query', array($woocommerce_wpml->multi_currency->reports, 'filter_dashboard_status_widget_sales_query'));

But now this query is slow

SELECT
	SUM( meta__order_total.meta_value) as sparkline_value,
	posts.post_date as post_date
FROM
	wp_posts AS posts
	INNER JOIN wp_postmeta AS meta__order_total ON ( posts.ID = meta__order_total.post_id AND meta__order_total.meta_key = '_order_total' ) 
WHERE 
	posts.post_type IN ( 'shop_order','shop_order_refund' )
	AND posts.post_status 	IN ( 'wc-completed','wc-processing','wc-missing-post','wc-refunded','wc-ready-for-despatc','wc-reviewing','wc-packing-1','wc-packing-2','wc-packing-3','wc-days28','wc-days28packing','wc-special','wc-intl-shipping','wc-renewal-prep','wc-new-order-prep','wc-blade-prep-3','wc-blade-prep-5','wc-blade-prep-6','wc-packing','wc-noths','wc-intl-priority','wc-personalisation','wc-tracked-24','wc-tracked-48','wc-priority-despatch','wc-first-class')
	AND post_date > '2019-08-01'
	AND posts.ID IN ( ALL_ORDER_IDS* )
GROUP BY YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date)
August 14, 2019 at 4:12 pm #4396599

George L.

and this one

SELECT 
	order_item_meta__product_id.meta_value as product_id,
	SUM( order_item_meta__qty.meta_value) as sparkline_value,
	posts.post_date as post_date
FROM
	wp_posts AS posts
	INNER JOIN wp_woocommerce_order_items AS order_items ON (posts.ID = order_items.order_id) AND (order_items.order_item_type = 'line_item')
	INNER JOIN wp_woocommerce_order_itemmeta AS order_item_meta__product_id ON (order_items.order_item_id = order_item_meta__product_id.order_item_id)  AND (order_item_meta__product_id.meta_key = '_product_id')
	INNER JOIN wp_woocommerce_order_itemmeta AS order_item_meta__qty ON (order_items.order_item_id = order_item_meta__qty.order_item_id)  AND (order_item_meta__qty.meta_key = '_qty') 
WHERE 
	posts.post_type IN ( 'shop_order','shop_order_refund' )
	AND posts.post_status 	IN ( 'wc-completed','wc-processing','wc-missing-post','wc-refunded','wc-ready-for-despatc','wc-reviewing','wc-packing-1','wc-packing-2','wc-packing-3','wc-days28','wc-days28packing','wc-special','wc-intl-shipping','wc-renewal-prep','wc-new-order-prep','wc-blade-prep-3','wc-blade-prep-5','wc-blade-prep-6','wc-packing','wc-noths','wc-intl-priority','wc-personalisation','wc-tracked-24','wc-tracked-48','wc-priority-despatch','wc-first-class')
	AND post_date > '2019-08-01' AND order_item_meta__product_id.meta_value = '105458'
	AND posts.ID IN ( ALL_ORDER_IDS* )
GROUP BY YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date)
August 14, 2019 at 4:18 pm #4396607

George L.

so it seems the removal of this filter solves the issue, but there are multiple places where this approach has been used and we'd need to spend long trying to identify them.

Can you please ask your developers to investigate and come back with the filters we'd need to use as well as the reasons for changing the queries like that?

We need to ensure that by removing those filters, we don't cause any other unexpected issues

August 16, 2019 at 8:57 am #4405501

Yvette
Supporter

Languages: English (English ) Spanish (Español )

Timezone: Europe/Madrid (GMT+01:00)

Our second tier group is very interested in debugging this issue.

How can we get a copy of your site for us to investigate since we need the volumes of orders to see the issue?

Do you have a staging site or are you able to create one?
Woudl you be willing to migrate the site to a Cloudways server?

Thanks for having signalled this issue and for your further collaboration.

August 16, 2019 at 9:03 am #4405543

George L.

Hi there,

We have a staging environment but it doesn't have any order data and unfortunately we can't give you access to our live environment.

From my point of view, the issue arises from the vast number of order IDs passed to the query and is not related to our other plugins, so you could generate random order data on WooCommerce and try again.

Maybe something like this would help your developers create some sample orders? hidden link

August 16, 2019 at 9:22 am #4405671

George L.

Reading through your code, i believe you attempt to identify all order IDs of the currency set in the _wcml_dashboard_currency cookie.

So the code is running the following query to come up with the IDs

SELECT order_currency.post_id 
FROM {$this->wpdb->postmeta} AS order_currency
WHERE order_currency.meta_key = '_order_currency' 
AND order_currency.meta_value = %s

It then identifies all IDs and injects them into various queries.

If i'm reading the code correctly and this is indeed the case, it would probably be more efficient to inject the queries with something like

INNER JOIN wp_postmeta pm ON posts.ID = pm.post_id AND pm.meta_key = '_order_currency' AND pm.meta_value = 'GBP'

See here for some reference https://stackoverflow.com/a/5919165

August 16, 2019 at 9:33 am #4405699

George L.

Some results on the IN vs JOIN from our live database

SELECT
	COUNT(DISTINCT posts.id) as count, posts.post_date as post_date
FROM
	wp_posts AS posts
	INNER JOIN wp_postmeta pm ON posts.ID = pm.post_id AND pm.meta_key = '_order_currency' AND pm.meta_value = 'GBP'
WHERE
	posts.post_type IN ( 'shop_order' )		
	AND posts.post_status 	IN ( 'wc-completed','wc-processing','wc-refunded','wc-missing-post','wc-ready-for-despatc','wc-reviewing','wc-packing-1','wc-packing-2','wc-packing-3','wc-days28','wc-days28packing','wc-special','wc-intl-shipping','wc-renewal-prep','wc-new-order-prep','wc-blade-prep-3','wc-blade-prep-5','wc-blade-prep-6','wc-packing','wc-noths','wc-intl-priority','wc-personalisation','wc-tracked-24','wc-tracked-48','wc-priority-despatch','wc-first-class')
	AND posts.post_date >= '2019-08-01 00:00:00'
	AND posts.post_date < '2019-08-17 00:00:00'
GROUP BY YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date)
ORDER BY post_date ASC;
/* Affected rows: 0  Found rows: 16  Warnings: 0  Duration for 1 query: 0.687 sec. */
SELECT
	COUNT(DISTINCT posts.id) as count, posts.post_date as post_date FROM wp_posts AS posts  
WHERE
	posts.post_type IN ( 'shop_order' )
	AND posts.post_status 	IN ( 'wc-completed','wc-processing','wc-refunded','wc-missing-post','wc-ready-for-despatc','wc-reviewing','wc-packing-1','wc-packing-2','wc-packing-3','wc-days28','wc-days28packing','wc-special','wc-intl-shipping','wc-renewal-prep','wc-new-order-prep','wc-blade-prep-3','wc-blade-prep-5','wc-blade-prep-6','wc-packing','wc-noths','wc-intl-priority','wc-personalisation','wc-tracked-24','wc-tracked-48','wc-priority-despatch','wc-first-class')
	AND posts.post_date >= '2019-08-01 00:00:00'
	AND posts.post_date < '2019-08-17 00:00:00'
	AND posts.ID IN (7116,7117,7132,7133,7139,7140,7150,7151,7177,7178,7185,7186,7191,7192,7197,7198,7205,7206,7223,7224,7249,7250,7265,7269,7272,7274,7276,7286,7287,7293,7294,7302,7303,7323,7324,7333,7334,7344,7345,7355,7356,7366,7367,7379,7381,7412,7413,7426,7427,7435,7436,7446,7447,7453,7454,7458,7459,7480,7482,7493,7494,7501,7502,7508,7509,7513,7514,7523,7524,7529,7530,7534,7535,7539,7540,7545,7546,7555,7556,7561,7562,7567,7568,7572,7573,7580,7581,7587,7588,7594,7595,7600,7607,7608,7618,7619,7629,7633,7634,7638,7639,7643,7647,7648,7653,7654,7658,7659,7663,7664,7667,7668,7675,7676,7681,7682,7691,7692,7696,7699,7704,7705,7709,7710,7713,7714,7720,7721,7724,7725,7730,7731,7734,7735,7740,7741,7745,7746,7749,7750,7757,7758,7765,7766,7769,7770,7777,7778,7783,7784,7789,7790,7795,7796,7797,7798,7803,7804,7807,7808,7816,7817,7824,7825,7830,7831,7834,7835,7843,7844,7854,7855,7861,7862,7866,7867,7870,7871,7888,7889,7894,7895,7901,7902,7908,7909,7918,7919,7925,7929,7933,7934,7939,7940,7949,7950,7954,7955,7958,7959,7962,7963,7971,7972,7978,7979,7994,7995,8005,8006,8009,8010,8021,8022,8025,8026,8033,8034,8029,8042,8045,8046,8050,8054,8044,8058,8059,8064,8065,8057,8075,8074,8080,8088,8089,8092,8093,8083,8110,8111,8116,8117,8121,8122,8123,8124,8134,8136,8139,8140,8/* large SQL query (2.2 MiB), snipped at 2,000 characters */
/* Affected rows: 0  Found rows: 16  Warnings: 0  Duration for 1 query: 00:05:15.9 */

I think it's quite obvious that you need to update all queries to use JOINs for the plugin to work well on larger sites.

August 16, 2019 at 9:58 am #4405771

Yvette
Supporter

Languages: English (English ) Spanish (Español )

Timezone: Europe/Madrid (GMT+01:00)

THank you again.

I will mention all these notes in the escalated ticket. IN CASE WE ARE NOT ABLE TO REPRODUCE it for any reason...even after loading up the order ids...would it be possible to compare server settings with your staging site?

I doubt it will be necessary, but the 2nd tier group may ask this and I want to anticipate a speedy push-through.

If so, is it possible to provide wp-admin access in the private section I am opening. To repeat - this would be for comparison purposes of configuration with your staging server.

August 16, 2019 at 10:06 am #4405785

George L.

Some more info

JOIN

explain
SELECT
	COUNT(DISTINCT posts.id) as count, posts.post_date as post_date
FROM
	wp_posts AS posts
	INNER JOIN wp_postmeta pm ON posts.ID = pm.post_id AND pm.meta_key = '_order_currency' AND pm.meta_value = 'GBP'
WHERE
	posts.post_type 	IN ( 'shop_order' )		
	AND posts.post_status 	IN ( 'wc-completed','wc-processing','wc-refunded','wc-missing-post','wc-ready-for-despatc','wc-reviewing','wc-packing-1','wc-packing-2','wc-packing-3','wc-days28','wc-days28packing','wc-special','wc-intl-shipping','wc-renewal-prep','wc-new-order-prep','wc-blade-prep-3','wc-blade-prep-5','wc-blade-prep-6','wc-packing','wc-noths','wc-intl-priority','wc-personalisation','wc-tracked-24','wc-tracked-48','wc-priority-despatch','wc-first-class')
	AND posts.post_date >= '2019-08-01 00:00:00'
	AND posts.post_date < '2019-08-17 00:00:00'
GROUP BY YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date)
ORDER BY post_date ASC;
/* Affected rows: 0  Found rows: 2  Warnings: 0  Duration for 1 query: 0.078 sec. */

returns

"id"	"select_type"	"table"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"Extra"
"1"	"SIMPLE"	"posts"	"ref"	"PRIMARY,type_status_date,post_type,post_status,post_type, post_status, post_author,post_type, post_status,post_type, post_status, post_date,post_type, post_status, post_modified_gmt,post_type, post_password,post_type, post_status, post_password, post_date_gmt"	"type_status_date"	"82"	"const"	"8997"	"Using where; Using index; Using temporary; Using filesort"

IN

explain
SELECT
    COUNT(DISTINCT posts.id) as count, posts.post_date as post_date FROM wp_posts AS posts  
WHERE 
    posts.post_type IN ( 'shop_order' )
    AND posts.post_status 	IN ( 'wc-completed','wc-processing','wc-refunded','wc-missing-post','wc-ready-for-despatc','wc-reviewing','wc-packing-1','wc-packing-2','wc-packing-3','wc-days28','wc-days28packing','wc-special','wc-intl-shipping','wc-renewal-prep','wc-new-order-prep','wc-blade-prep-3','wc-blade-prep-5','wc-blade-prep-6','wc-packing','wc-noths','wc-intl-priority','wc-personalisation','wc-tracked-24','wc-tracked-48','wc-priority-despatch','wc-first-class')
    AND posts.post_date >= '2019-08-01 00:00:00'
    AND posts.post_date < '2019-08-17 00:00:00'
    AND posts.ID IN (7116,7117,7132,7133,7139,7140,7150,7151,7177,7178,7185,7186,7191,7192,7197,7198,7205,7206,7223,7224,7249,7250,7265,7269,7272,7274,7276,7286,7287,7293,7294,7302,7303,7323,7324,7333,7334,7344,7345,7355,7356,7366,7367,7379,7381,7412,7413,7426,7427,7435,7436,7446,7447,7453,7454,7458,7459,7480,7482,7493,7494,7501,7502,7508,7509,7513,7514,7523,7524,7529,7530,7534,7535,7539,7540,7545,7546,7555,7556,7561,7562,7567,7568,7572,7573,7580,7581,7587,7588,7594,7595,7600,7607,7608,7618,7619,7629,7633,7634,7638,7639,7643,7647,7648,7653,7654,7658,7659,7663,7664,7667,7668,7675,7676,7681,7682,7691,7692,7696,7699,7704,7705,7709,7710,7713,7714,7720,7721,7724,7725,7730,7731,7734,7735,7740,7741,7745,7746,7749,7750,7757,7758,7765,7766,7769,7770,7777,7778,7783,7784,7789,7790,7795,7796,7797,7798,7803,7804,7807,7808,7816,7817,7824,7825,7830,7831,7834,7835,7843,7844,7854,7855,7861,7862,7866,7867,7870,7871,7888,7889,7894,7895,7901,7902,7908,7909,7918,7919,7925,7929,7933,7934,7939,7940,7949,7950,7954,7955,7958,7959,7962,7963,7971,7972,7978,7979,7994,7995,8005,8006,8009,8010,8021,8022,8025,8026,8033,8034,8029,8042,8045,8046,8050,8054,8044,8058,8059,8064,8065,8057,8075,8074,8080,8088,8089,8092,8093,8083,8110,8111,8116,8117,8121,8122,8123,8124,8134,8136,81/* large SQL query (2.2 MiB), snipped at 2,000 characters */
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 00:05:48.1 */

returns

"id"	"select_type"	"table"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"Extra"
"1"	"SIMPLE"	"posts"	"ref"	"PRIMARY,type_status_date,post_type,post_status,post_type, post_status, post_author,post_type, post_status,post_type, post_status, post_date,post_type, post_status, post_modified_gmt,post_type, post_password,post_type, post_status, post_password, post_date_gmt"	"type_status_date"	"82"	"const"	"8997"	"Using where; Using index; Using temporary; Using filesort"
"1"	"SIMPLE"	"pm"	"ref"	"post_id,meta_key,meta_value,post_id, meta_key"	"post_id, meta_key"	"1031"	"const,gruum.posts.ID"	"1"	"Using index condition; Using where"
August 16, 2019 at 10:09 am #4405791

George L.

If you can open another private section, i can paste the results of

SHOW VARIABLES;
SHOW GLOBAL VARIABLES;

Since the problem is with the SQL query, those settings would be everything your developers need to make an informed decision, although i think the above results speak for themselves

August 16, 2019 at 10:25 am #4405823

Yvette
Supporter

Languages: English (English ) Spanish (Español )

Timezone: Europe/Madrid (GMT+01:00)

Ok - the private section is open. YOu might have to put some dummy data in the form for the response to upload to forum.