Question 3010 in Woocommerce Question posted in
The official Woocommerce documentation can be found here.

One column ends up with different collation in view – Woocommerce

I’ve a bit of a strange issue going on. My database is set to the utf8mb4_unicode_ci collation. So are the (WooCommerce) tables within that database. The columns in the table are also at utf8mb4_unicode_ci.

I’ve create a view and now get a different collation (as utf8mb4_general_ci) for the calculated field sku_class. How is this possible? And, more important, how can I resolve it?

The shortened query to create the view is:

SET character_set_connection = 'utf8mb4';

CREATE VIEW vw_wc_product_details AS 

SELECT
        pm.sku,
        CASE
            -- manual fixes 
            WHEN pm.sku LIKE 'AB1404/%' THEN 'c'    
            -- default rules
            WHEN pm.sku LIKE 'ABs__-_%' THEN 's'    
            WHEN pm.sku LIKE 'ABt__-_%' THEN 't'
            WHEN pm.sku LIKE 'AB____/%' THEN 'pc'  
            WHEN ( SELECT COUNT(sku) FROM wp_wc_product_meta_lookup WHERE sku LIKE CONCAT(pm.sku, '/_%') ) >= 1 THEN 'p' 
            WHEN pm.sku LIKE 'AB____%' AND LENGTH(pm.sku) = 6 THEN 'c' 
            WHEN pm.sku LIKE 'AX%' AND LENGTH(pm.sku) >= 6 THEN 'm'
            ELSE 'x'
        END as sku_class
    
FROM    wp_wc_product_attributes_lookup AS pa
LEFT    JOIN wp_terms AS t ON pa.term_id = t.term_id
LEFT    JOIN wp_wc_product_meta_lookup AS pm ON pa.product_id = pm.product_id
LEFT    JOIN wp_posts AS p ON pa.product_id = p.ID

COLLATE 'utf8mb4_unicode_ci'

The SET character_set_connection = 'utf8mb4'; and COLLATE 'utf8mb4_unicode_ci' lines have been added due to the illegal mix of collations error I received and which put me on the path of double checking the collations of everything involved.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Based on the input provided by @shadow in the comments above, I ended up with adding COLLATE 'utf8mb4_unicode_ci' to the END as sku_class line, so it's now reads as END COLLATE 'utf8mb4_unicode_ci' as sku_class.

    This does the trick for me for the view I created. That said, I still run into an illegal mix of collations error message when using the view within a secondary view although it -weirdly enough- works without error message from my PHP PDO query...

    I'll update this post when I figured out a way to remove the error message for the secondary view in phpMyAdmin as well.


  2. 0

    Ok – figured it out, I initially misread the error message so I was looking at the wrong section of my query.

    This is actually the part that bugged me:

    case
    when pd.sku_class not in ('s','t') then 'act'

    The pd.sku_class apparently caused the collation issue, still no idea why because everything in the database and the tables are with utf8mb_unicode_ci. Only the server setting is with latin, so that must be the reason.

    Anyway, changing pd.sku_class not in ('s','t') into not find_in_set( cast( pd.sku_class as char ), 's,t' ) did the trick!

    case
    when not find_in_set( cast( pd.sku_class as char ), 's,t' ) then 'act'

    So it is now all working for me. Hopefully this helps someone else in the future too 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search