mySQL: Illegal mix of collations (utf8mb4_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Question

I'm trying to figure out why my CMS is giving me an error, but SQLFiddle and phpMyAdmin both run the query fine. Here is the fiddle:

http://sqlfiddle.com/#!9/cadc53/2

So the sample data:

CREATE TABLE IF NOT EXISTS `glinks_Sales` (
  `TransactionID` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `Billing_Name` char(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `Billing_Street` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `Billing_City` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `Billing_State` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `Billing_PostCode` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `Billing_Country` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `Shipping_Name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `Shipping_Street` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `Shipping_City` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `Shipping_State` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `Shipping_PostCode` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `Shipping_Country` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `Been_Dispatched` int(11) NOT NULL,
  `Postcode_Check_Status` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `Billing_Street_2` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `Shipping_Street_2` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `Items_In_Order` longtext COLLATE utf8mb4_bin,
  `Email` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `unique_id` int(11) NOT NULL,
  `Order_Date` bigint(20) DEFAULT NULL,
  `Method` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `Currency` char(3) COLLATE utf8mb4_bin DEFAULT NULL,
  `Shipping_Amount` float DEFAULT NULL,
  `Language` varchar(5) COLLATE utf8mb4_bin DEFAULT NULL,
  `Cancelled` int(11) DEFAULT NULL,
  `TrackingNumber` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  `PostageCompany` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

ALTER TABLE `glinks_Sales`
  ADD PRIMARY KEY (`unique_id`);

INSERT INTO `glinks_Sales` (`TransactionID`, `Billing_Name`, `Billing_Street`, `Billing_City`, `Billing_State`, `Billing_PostCode`, `Billing_Country`, `Shipping_Name`, `Shipping_Street`, `Shipping_City`, `Shipping_State`, `Shipping_PostCode`, `Shipping_Country`, `Been_Dispatched`, `Postcode_Check_Status`, `Billing_Street_2`, `Shipping_Street_2`, `Items_In_Order`, `Email`, `unique_id`, `Order_Date`, `Method`, `Currency`, `Shipping_Amount`, `Language`, `Cancelled`, `TrackingNumber`, `PostageCompany`) VALUES ('8LL79654AS664260H', 'Andyííííééé Íóé', 'xxxx', 'Rudgwick', 'West Sussex', 'xxx', 'GB', 'Andyííííééé Íóé ', 'foo', 'x', 'West Sussex', 'x', 'GB', 1488558170, '1', '', '', 'test', 'ss@gmail.com', 15, 1488472336, 'PayPal', '', 10, 'en', NULL, 'foo', 'royal mail');

Then a sample query to grab

SELECT * FROM glinks_Sales WHERE (Shipping_Name = "Andyííííééé Íóé" OR Email = "Íóé" OR unique_id = "Íóé" OR TrackingNumber = "Íóé" OR PostageCompany = "Íóé") 

That runs just fine. However, my Perl script that accesses the database does:

SELECT * FROM glinks_Sales WHERE (TransactionID = ?) ORDER BY Order_Date ASC'

...and it spits out the error:

Illegal mix of collations (utf8mb4_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

I'm confused as to where it would be getting utf8_general_ci from? I must be missing something stupid, but I can't see it. Hopefully a fresh pair of eyes will help!

Thanks for any suggestions!

Answer 1

Collation mixes can be a pain. Try to SET collation_connection = 'utf8mb4_bin' before the query:

SET collation_connection = 'utf8mb4_bin'; 

SELECT * FROM glinks_Sales WHERE (TransactionID = ?) ORDER BY Order_Date ASC'; 

This sets the collation for the connection, before you run your query.

If that do not work, check other possible collation settings for database, server or connection. You find some hints here: https://stackoverflow.com/a/37298732/1363190