20200514
I have been being to create a lookup table for woocommerce orders states. So I decided to implement it as a view:
Before adding in ‘collate utf8_general_ci’ to each record in the view below I was getting an error when consuming the view:
Error Code: 1267. Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT)
create view vRefOrderStates as -- 20200513 LSR -- Initial version select 'wc-cancelled' collate utf8_general_ci as pstatus, 0 as complete, 1 as failed, 0 as open union select 'wc-completed' collate utf8_general_ci as pstatus, 1 as complete, 0 as failed, 0 as open union select 'wc-failed' collate utf8_general_ci as pstatus, 0 as complete, 1 as failed, 0 as open union select 'wc-on-hold' collate utf8_general_ci as pstatus, 0 as complete, 0 as failed, 1 as open union select 'wc-pending' collate utf8_general_ci as pstatus, 0 as complete, 0 as failed, 1 as open union select 'wc-processing' collate utf8_general_ci as pstatus, 0 as complete, 0 as failed, 1 as open union select 'wc-refunded' collate utf8_general_ci as pstatus, 0 as complete, 1 as failed, 0 as open;
Then for speed of excution (an assumption) we turned the above view into an indexed table and had to specify collation as well to prevent similar errors:
CREATE TABLE m4m_refOrderStates ( post_status char(20) ,complete bool not null ,failed bool not null ,incomplete bool not null ,KEY iPostState (post_status) USING BTREE ) COLLATE utf8mb4_unicode_ci; insert into m4m_refOrderStates values('wc-cancelled', 0, 1, 0); insert into m4m_refOrderStates values('wc-completed', 1, 0, 0); insert into m4m_refOrderStates values('wc-failed', 0, 1, 0); insert into m4m_refOrderStates values('wc-on-hold', 0, 0, 1); insert into m4m_refOrderStates values('wc-pending', 0, 0, 1); insert into m4m_refOrderStates values('wc-processing', 0, 0, 1); insert into m4m_refOrderStates values('wc-refunded', 0, 1, 0);
201903 Woocommerce, creating a view that wraps HTML around post_status, so post_status is now calculated column (yes stupid wordpress is ignoring newlines in the pasted SQL snippet)
,concat(
case
when coalesce(mecha_note, '') <> ''
then '
' else '' end, case when post_status like '%cancel%' or post_status like '%refund%' then concat('', post_status, '') when post_status like '%completed%' then concat('', post_status, '
') else post_status end, case when coalesce(mecha_note, '') <> '' then concat('Fulfillment note:
', mecha_note, '
')
else '' end
) as post_status
PHP -> SQL filtering by status errors:
select * from vOrdersList
where post_status <> 'wc-completed'
and post_status <> 'wc-cancelled'
and post_status <> 'wc-refunded';
Error Code: 1267. Illegal mix of collations (utf8mb4_bin,NONE) and (utf8_general_ci,COERCIBLE) for operation 'like' 0.235 sec
What is the collation of the original posts table source column:
select COLLATION(post_status) as post_status,
COLLATION(user()) as user_func,
COLLATION(substring_index(user(), '@', 1)) as substr_func
from wp1_posts;
post_status, user_func, substr_func
'utf8mb4_unicode_ci', 'utf8_general_ci', 'utf8_general_ci'
What about the calculated column in the view?
select COLLATION(post_status) as post_status,
COLLATION(user()) as user_func,
COLLATION(substring_index(user(), '@', 1)) as substr_func
from vOrdersList;
post_status, user_func, substr_func
'utf8mb4_bin', 'utf8_general_ci', 'utf8_general_ci'
My solution: set collation for the calculated column in the view to match the source column, revised view for that column (see last line)
(yes stupid wordpress is ignoring newlines in the pasted SQL snippet) :
,concat(
case
when coalesce(mecha_note, '') <> ''
then '
' else '' end, case when post_status like '%cancel%' or post_status like '%refund%' then concat('', post_status, '') when post_status like '%completed%' then concat('', post_status, '
') else post_status end, case when coalesce(mecha_note, '') <> '' then concat('Fulfillment note:
', mecha_note, '
')
else '' end
) collate utf8mb4_unicode_ci as post_status