Issue with WooCommerce Attributes/terms

[Phiph]

Hi

I've noticed an issue with the SQL query getting parsed for getting the attribute terms used in woocommerce. Its creates the attributes and terms fine, but the SQL query to select them is errors due to the column names in the order by not being qualified.

SELECT  DISTINCT t.*, tt.* 
FROM wp_terms AS t  
INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id 
LEFT JOIN wp_termmeta AS tm ON (t.term_id = tm.term_id AND tm.meta_key = 'order_pa_colour')  WHERE tt.taxonomy IN ('pa_colour') ORDER BY t, tt, tm ASC, 
t.name ASC

Would this be a more plugin specific issue to raise?

[patrickebates]

looks like we're going to need to trap this query and write a specific translation for it. there's at least one other ticket on this exact same issue.

[patrickebates]

Do you have output from the Translation log available? It will show the Before and After SQL statements and make it easier to prepare a fix.

[Phiph]

HI @patrickebates ,

I've only recently started using Project Nami for my WordPress development because having everything just work in VS with MSSQL is amazing, and what I'm used to (so thank you all).

So because of that I don't know how to enable the Translation Log. Do you mind letting me know how to turn that on?

However I have attached the SQL Queries in CSV format, just as a text file, from the MSSQL profiler which is recording the following actions :

1. Adding new attribute "size".
2. Navigating to Configure Terms.
3. Adding new term Small.
4. Refreshing the page.

WooCommerce Profile

[patrickebates]

The translation log is enabled through an App Setting of ProjectNamiLogTranslate with a value of 1. If you used our Deploy To Azure button to set up your site, this setting was applied for you automatically.

The translation log will be written to the same location as the PHP error log.

[Phiph]

It took me a while, but I figured it out. I'm running locally using PHP 7 on IIS (not Express) and adding the appsetting didn't change anything, in the end I just commented out the 4 getenv() statements.

If you know a workaround that would be great.

translate.txt

[patrickebates]

Apparently WooCommerce is constructing this query by hand rather than allowing the WP Term Query class to do it.

We're preparing a translation for future release. In the meantime, you can try the following.

Look for the following string in the files for WooCommerce ORDER BY tm.meta_value+0 and replace it with this CAST(tm.meta_value as numeric)

[DLozanoNavas]

Hello everyone ,

Saddly, I'm still having this issue.

I've been trying the following with no success:

ORDER BY tm.meta_value+0 on includes\wc-term-functions.php was already replaced by CAST(tm.meta_value as numeric) (it was a string, so i replaced as a string). Then I disabled the Woocommerce plugin, cleaned up my database of woocommerce tables and options, and then i activated it again.

SELECT DISTINCT t., tt. FROM fa_terms AS t INNER JOIN fa_term_taxonomy AS tt ON t.term_id = tt.term_id LEFT JOIN fa_termmeta AS tm ON (t.term_id = tm.term_id AND tm.meta_key = 'order') WHERE tt.taxonomy IN ('product_cat') CAST(tm.meta_value as numeric)ASC, t.name ASC

(this is what i can see after replacing.)

(It says "UNDEFINED" when i try to add a category on top of tis method)

I also tried the same process on adding tm.meta value to the 'DISTINC' list as below:

-   $clauses['fields'] = 'DISTINCT ' . $clauses['fields'];
 +  $clauses['fields'] = 'DISTINCT ' . $clauses['fields'] . ', tm.meta_value';

Woocommerce solution, but it doesn't solve it either, and as notification of that, the following explanation:

SELECT DISTINCT t., tt. FROM fa_terms AS t INNER JOIN fa_term_taxonomy AS tt ON t.term_id = tt.term_id LEFT JOIN fa_termmeta AS tm ON (t.term_id = tm.term_id AND tm.meta_key = 'order') WHERE tt.taxonomy IN ('product_cat') ORDER BY t, tt, tm ASC, t.name ASC

It must be said that the database stores the values, but for some reason they are not shown

Does anyone succeeded on this?

PS: my translate log seems to be a large file. is there any particular log that we should look for?

Thank you all very much in advance.

[polyxena]

Hello Everybody, Still getting this error, is there any solution for this? Thanks

[readej]

I also have issues with Woocommerce. What's the status of this? Thanks,

[patrickebates]

We did add this translation over a year ago, so if it's not helping then we would need to see new Error and Translate logs. The original query containing +0 indicates they are attempting to force the field to numeric sorting, so the idea of the CAST failing due to values not being numeric seems strange.

[readej]

Here is the first error we get after activating Woocommerce:

Error Code: 10785 -- Query NOT translated due to non-defined error code.
ALTER TABLE wp_comments ADD INDEX woo_idx_comment_type (comment_type)

[09-May-2018 12:18:33 UTC] WordPress database error [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'PRIMARY'. Code - 156 for query ALTER TABLE wp_woocommerce_downloadable_product_permissions DROP CONSTRAINT [_1_1] PRIMARY KEY CLUSTERED (1,1) WITH (IGNORE_DUP_KEY = OFF),
CONSTRAINT [_1_1] PRIMARY KEY CLUSTERED (1,1) WITH (IGNORE_DUP_KEY = OFF)
); made by activate_plugin, do_action('activate_woocommerce/woocommerce.php'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Install::install, WC_Install::create_tables

Prevents table from being created and we get a lot of errors stating the default schema does not exist (like the one below):

[09-May-2018 12:18:33 UTC] WordPress database error [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The default schema does not exist. Code - 2797 for query CREATE TABLE wp_woocommerce_sessions (
  session_id BIGINT NOT NULL IDENTITY(1,1),
  session_key char(32) NOT NULL,
  session_value NVARCHAR(MAX) NOT NULL,
  session_expiry BIGINT NOT NULL,
  CONSTRAINT [wp_woocommerce_sessions_session_key] PRIMARY KEY CLUSTERED (session_key) WITH (IGNORE_DUP_KEY = OFF),
CONSTRAINT [wp_woocommerce_sessions_session_id] UNIQUE NONCLUSTERED (session_id)
); made by activate_plugin, do_action('activate_woocommerce/woocommerce.php'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Install::install, WC_Install::create_tables, dbDelta
[09-May-2018 12:18:33 UTC] WordPress database error [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ')'. Code - 102 for query CREATE TABLE wp_woocommerce_api_keys (
  CONSTRAINT [wp_woocommerce_api_keys_key_id] PRIMARY KEY CLUSTERED (key_id) WITH (IGNORE_DUP_KEY = OFF),

); made by activate_plugin, do_action('activate_woocommerce/woocommerce.php'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Install::install, WC_Install::create_tables, dbDelta

[09-May-2018 12:18:33 UTC] WordPress database error [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The default schema does not exist. Code - 2797 for query CREATE TABLE wp_woocommerce_attribute_taxonomies (
  attribute_id BIGINT NOT NULL IDENTITY(1,1),
  attribute_name NVARCHAR(200) NOT NULL,
  attribute_label NVARCHAR(200) NULL,
  attribute_type NVARCHAR(20) NOT NULL,
  attribute_orderby NVARCHAR(20) NOT NULL,
  attribute_public int NOT NULL DEFAULT 1,
  CONSTRAINT [wp_woocommerce_attribute_taxonomies_attribute_id] PRIMARY KEY CLUSTERED (attribute_id) WITH (IGNORE_DUP_KEY = OFF),

); made by activate_plugin, do_action('activate_woocommerce/woocommerce.php'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Install::install, WC_Install::create_tables, dbDelta