MySQL Select with Where on a View calculated column results in Illegal mix of collations

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

Todo

20190317

  • Large TVs against Gge door (temporary)
  • Middle window:
    • 6 x FCabs inline workbench
      • Cabs in place, rough wooden bench top
      • Needs power run, shelving, on left after determine:
        • where PCs live
        • where soldering/rework station lives
    • Box shelving above:
      • partially done, pending decision on what tops 100 drawer cab
    • 100 Drawer cab:
      • Wall off left side
      • 42″ HD TV mounted on side of cab
      • Leave room for 55″ Tv to slide out?
  • Soldering / rework station:
    • Consider removable self contained tray: could sit above 100 drawer can when not in use
  • Lundia:
    • Isle 3 middle:
      • Bottom shelf rubber mats height
      • Next shelf Steel cutoff saw, Arc welder / Plasma cutter
      • Top shelf: linear rails, acme leadscrews etc
      • Remaining: TDB
    • Simexperience:
      • Tidy, sell?
    • RC4: rotate 100 degrees


  • 3030 / 3060
  • Construct Bench to go under
  • Cutting bench:
    • Level left side
    • Make fence & Fit digital readout
    • Sort right side
  • Boxes to keep:
    • Shelf on top of tilt door
    • Shelf above window
  • Loose iCreme containers etc below corrugated cardboard rolls:
    • empty some into new 100 drawer cabinet
    • Rear wall of toilet workbench? does that leave enough room for 3d printers?
  • New Mill parts:
    • Construct bench opposite existing mill
    • Construct Mill (mount head to compound table
  • Pinballs (Prioritised):
    • Remove broken platics from IJ
    • Fix & LED CFTBL & sell
    • Fix STTNG ball sensors
    • Fix JD
    • LED TFTC
  • Bikes:
    • Sell Ego
    • Sell C600
    • Fix PGO
    • Sell ZX?
    • Buy H2?

$$$ 20190218

WhatBefore GST/PAYENetTot
BNZ -$26,927.00  
BNZ -$8,698.00  
Kbank -$3,000.00  
Coop -$1,000.00  
   -$39,625.00
    
Sell Kymco-1500-$1,304.35  
BMW-7500-$6,521.74  
Sell van-7500-$6,521.74  
Sell ZX14-12000-$10,434.78  
   -$24,782.61
    
    
    
Sell CFTBL -$6,000.00 -$6,000.00
    
Coop Jan -$6,650.00  
 65%-$4,322.50 -$4,322.50
Coop Dec -$5,000.00  
 65%-$3,250.00 -$3,250.00
    
Total realised  -$77,980.11
    
Buy H229995 $26,082.61 $26,082.61
Left realised  -$51,897.50

Mobile phone testing platform

Width:

Keyboard is less than 335mm wide, with feet out sits < 50mm up

Height at front

From keyboard above, 50mm under

Height:

My screen height from desk to screen (24″) is 190mm (assuming top of screen should be at my eyeline, that screen is about 20mm too low for me)

Edwards is 160mm on 1 (27″) screen and 220mm on the other (24″)

So Bezels at bottom of screens are at least 30mm (24″) and more for the 27″

So we will assume 190 + 30mm = 220mm maximum height of platform

Depth

I have > 300mm from rear of keyboard to screen which is a lot more than I expect is needed, if less depth we can alter angle more without obscuring the screen

Design points:

  • Adjustable angle
  • Able to pull forward to user and slide over keyboard
  • Non slip matting
  • Soft ridge above bottom cable slot
  • USB Hub under
  • Cables via slots top and bottom, Micro USB & Lightning only
  • Arrange micro usb on 1 side, iOS on the other?

Measure/Cutting fence (Mitre Station)

https://www.carbatec.co.nz/product/10069-kreg-precision-trak-stop-system-metric-tapes-

I also ruled out an automatically positioned stop, like the previously mentioned Tiger StopAccu-Cut Gold, or ProScale. I passed by these options because of the price

Maya Positioning Equipment makes some beautiful fences and stops and I was introduced to the company when I met the owner at IWF in Atlanta last year. I was very impressed with his company’s products and saved his brochure for this very occasion. I was also able to find several other candidates, Glide StopScotchman, and SawHelper,via a Google web search.

http://www.mayaposi-stop.com/saw_stops.htm

https://www.axminster.co.uk/ujk-technology-track-stop-kit-101500

http://www.proscale.com/products/woodworking_related/prostop2.htm

https://scotchman.com/measuring-systems/advanced-measuring-system/quick-loc/

Kreg Tool Precision Trak & Stop 8′ System KMS8000

biesemeyer 78-806

Woocommerce -> MySQL -> vVariationMetaData vProductVariation

20181220 Product variations (attribute = length only currently)

alter view vVariationMetaData
as
-- 20181220 LSR
-- Product Variations meta data
-- Note is selected metadata only (not quite all)
select po.post_parent, pm.post_id
,max(case when meta_key = 'attribute_length' then meta_value else null end) as pvm_attribute_length
,max(case when meta_key = '_sku' then meta_value else null end) as pvm_sku
,max(case when meta_key = '_weight' then meta_value else null end) as pvm_weight
,max(case when meta_key = '_length' then meta_value else null end) as pvm_length
,max(case when meta_key = '_width' then meta_value else null end) as pvm_width
,max(case when meta_key = '_height' then meta_value else null end) as pvm_height
,max(case when meta_key = '_manage_stock' then meta_value else null end) as pvm_manage_stock
,max(case when meta_key = '_regular_price' then meta_value else null end) as pvm_regular_price
,max(case when meta_key = '_price' then meta_value else null end) as pvm_price
,max(case when meta_key = '_variation_description' then meta_value else null end) as pvm_variation_description
from wp1_postmeta pm
join wp1_posts po on po.ID = pm.post_id
where po.post_type = 'product_variation'
group by po.post_parent, pm.post_id;


alter view vProductVariations
as
-- 20181220 LSR
-- Product meta data
select ID, post_title, pm_sku, pvm.*
from vProductMetaData pm
join vVariationMetaData pvm on pvm.post_parent = pm.id
order by pm.ID, pvm_attribute_length*0;

xx

Simtools Installation 2 PCs

Simtools 2 PC setup proposed

  • Simtools Game manager on Gaming PC
    • Currently either:
      • i3 xxx GTX1060 3GB
      • Zotak Mek1 (i7-7700, GTX7010 Ti)
        •  Already has 2 x Gigabit Ethernet connections on board
        • The physically lower Ethernet connection is Ethernet 1 which is the one we shall use
        • Ethernet 1 hardcoded IP to 192.168.1.241
  • Simtools Game Engine running on dedicated PC
    • Ex office HP Compaq 8200 gifted sans HD
      • Intel i7 something + 8GB ram, gave it vacuum as pretty dusty
        • Spare slots:
          • 1 x PCI
          • 1 x PCIe x16
          • 1 x PCIe x1
        • Slots future use possibilities for:
          • Second GigaBit NIC
          • Multi channel audio card e.g. ASUS Xonar DX 7.1 PCI-E Low Profile Sound Card
      • 240GB Sata SSD split with ~70Gb data partition
      • 20181124 Installed & activated Win 7 64 Pro using license on side of case
      • 20181124 Upgraded to Win 10 64 Pro via Microsoft online, confirmed activation
    • Hardcoded IP to 192.168.1.240

Connectivity:

  • Docs & forums recommend a crossover cable, initially we will try this setup:
    • Game Engine & Game manager Gigabit connections to a dedicated Gigabit switch
    • Dedicated Gigabit switch connection to my normal network Gigabit switch
    • No other connections to the dedicated Gigabit switch
  • If that presents problems we will look at adding additional NIC cards to both PCs and going the cross over cable route
    • In fact I will proceed to order a couple of Gigabit PCI cards anyway so we will consider this an interim solution

Setup on Game manager PC

  • Complete normal gaming setup
    • Windows 10 64 and up to date
    • Steam
    • Gaming Wheel drivers
      • 20181125 is Thrustmaster TS-PC with T3PA pedal set
  • Install Simtools package & Activate
    • Click then Shift + double click on SimTools launcher to select Game Manager only
    • In Game Manager
      • Tools -> Engine IP = 192.168.1.240

Setup on Game Engine PC

  • Bare Win 10 64 & up to date
  • Install Simtools package & Activate
    • Click then Shift + double click on SimTools launcher to select Game Manager only
    • In Game Engine
      • Tools -> Manager IP = 192.168.1.241