Date type issue with Like clause Arabic string in mysql

Question

I work on old system, and the search in popup was made dynamically. for example if popup fields: field1, field2, field3 and the user type كلمة in the textbox for search or any arabic word. the system will create a dynamic search condition like this:

field1 LIKE '%كلمة%' OR field2 LIKE '%كلمة%' OR field3 LIKE '%كلمة%'

the problem occur when one of these fields is date type, in this case give me mysql error:

illegal mix of collations for operation 'like'

when I change the the word I search to be English word, it give me warning but work correctly because I don't need the field of date type in this situation (I search for text). also I can't change the core of this dynamic search php function because all system was built, so I can't use like this code:

OR DATE_FORMAT(field1, '%Y-%m-%d') LIKE '%كلمة%'

because I don't know which field is a Date type, it is a dynamic search.

also this issue occur on the hosting server only, and not on my localhost, my host is: Server version: 5.5.47-cll - MySQL Community Server (GPL) and my localhost mysql version is: 5.5.5.

this is show create table script:

CREATE TABLE `POS_QUOTATIONS` (
  `DEPT_ID` varchar(20) NOT NULL,
  `BILL_TYPE` int(11) NOT NULL,
  `BILL_NUMBER` int(11) NOT NULL,
  `BILL_NUMBER_TO_RTRN` int(11) NOT NULL,
  `SALESPERSON_NAME` varchar(255) NOT NULL,
  `BILL_DATE` date NOT NULL,
  `POS_ID` int(11) NOT NULL,
  `STOCK_ID` varchar(20) NOT NULL,
  `CURRENCY_ID` varchar(3) NOT NULL,
  `EXCHANGE_RATE` float NOT NULL,
  `SALESPERSON_TYPE` int(1) NOT NULL,
  `SALESPERSON_ID` int(10) NOT NULL,
  `CLIENT_TYPE` int(1) NOT NULL,
  `ACC_CODE` varchar(20) NOT NULL,
  `CLIENT_NAME` varchar(255) NOT NULL,
  `CLIENT_APPROVAL_NUMBER` varchar(20) NOT NULL,
  `NOTES` varchar(255) NOT NULL,
  `BILL_VALUE_BEFORE_DISCOUNT` double NOT NULL,
  `OVRALL_COST` double DEFAULT '0',
  `SALES_TAX` double NOT NULL,
  `ADITIONAL_AMOUNT` double NOT NULL,
  `DISCOUNT_PERCENTAGE` double NOT NULL,
  `DISCOUNT_AMOUNT` double NOT NULL,
  `BILL_VALUE_AFTER_DISCOUNT` double NOT NULL,
  `CLIENT_PAYMENT` decimal(13,2) NOT NULL,
  `REMAINING_AMOUNT` decimal(13,2) NOT NULL,
  `PAYMENT_METHOD` int(11) NOT NULL,
  `CREDIT_CARD_DETAILS` varchar(255) NOT NULL,
  `BANK_OPERATION_NUMBER` varchar(50) NOT NULL,
  `BANK_DETAILS` varchar(255) NOT NULL,
  `BANK_ACC_CODE` varchar(20) NOT NULL,
  `PAYMENT_METHOD_INFO` varchar(500) NOT NULL,
  `CONTRACT_PRODUCT_DETAILS` text NOT NULL,
  `CONTRACT_REC_VOUCHER_NO` varchar(255) NOT NULL,
  `CONTRACT_REPAYMENT_DATE` varchar(255) NOT NULL,
  `ROWID` varchar(255) NOT NULL,
  `YEAR` int(4) NOT NULL,
  `CREATION_USER` varchar(10) DEFAULT NULL,
  `CREATION_TIMESTAMP` varchar(14) DEFAULT NULL,
  `CREATION_COMPUTER_NAME` varchar(50) DEFAULT NULL,
  `LASTUPDATE_USER` varchar(10) DEFAULT NULL,
  `LASTUPDATE_TIMESTAMP` varchar(50) DEFAULT NULL,
  `LASTUPDATE_COMPUTER_NAME` varchar(50) DEFAULT NULL,
  `TRANS_FLAG` varchar(10) DEFAULT NULL,
  `RECORD_STATUS` varchar(1) DEFAULT NULL,
  `JOURNAL_STATUS` tinyint(1) DEFAULT NULL,
  `PROCESS_NUMBER` varchar(50) DEFAULT NULL,
  `OFFER_TERMS` text,
  `TECHNICAL_SPECIFICATIONS` text,
  `OFFER_STATUS` tinyint(1) DEFAULT NULL,
  `PROJECT_DESCRIPTION` varchar(500) DEFAULT NULL,
  `POS_CONTRACT_AGREEMENT_ID` varchar(50) DEFAULT NULL,
  `COST_CENTER_CODE` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ROWID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and whatever the charset of database on the hosting server it produce this error when charset is utf8_general_ci or latin1_swedish_ci thanks in advance.

Which api do you use to connect to the mysql server? PDO, mysqli, mysql_* ? — VolkerK, Jan 10 '16 at 13:51
If you cannot change the PHP that generates and executes this query how did you propose to solve this? Seems no matter the answer you get it is going to have to involve some modification of the PHP. — Wobbles, Jan 10 '16 at 13:51
@VolkerK I am talking about mysql error. when I run the query directly on mysql it give this error. — Gouda Elalfy, Jan 10 '16 at 13:56
@Wobbles I can't change the dynamic search script, because this need a lot of test in different parts, and the system already launched on the server. — Gouda Elalfy, Jan 10 '16 at 13:57
I was only asking because I was about to write a little script to fetch the relevant information for this question. I'll use PDO then. — VolkerK, Jan 10 '16 at 13:57
@GoudaElalfy ok... so like I said.... how did you imagine this was going to be resolved without access to the PHP anymore? — Wobbles, Jan 10 '16 at 13:58
Your table/field definitions and connection charset/collation lead to a query that implicitly does something like `SELECT _utf8'foo' COLLATE utf8_general_ci LIKE _utf8'bar' COLLATE utf8_turkish_ci`, i.e. two differing collations colliding. — VolkerK, Jan 10 '16 at 15:54
You said when you change the word to english you get a warning? What is the warning? — Shawn, Jan 12 '16 at 16:09
To what @VolkerK said, try the solution here to make sure all your tables are the same collation. http://stackoverflow.com/a/1294156/1361532 — Shawn, Jan 12 '16 at 16:11
I'm still pretty certain, to solve your actual problem we'd need the table deifnitions (including field,table,database charset+collation) and the charset/collation settings of your application, i.e. the mysql connection collation (plus maybe the actual character encoding of the parameters your php scripts receives and how the php script uses them). see also https://dev.mysql.com/doc/refman/5.7/en/charset-collations.html and esp. https://dev.mysql.com/doc/refman/5.7/en/charset-collation-expressions.html — VolkerK, Jan 12 '16 at 17:14
How you "don't know which field is a Date type" ? Is db schema dynamic and can be changed runtime? — Alex Blex, Jan 13 '16 at 10:06
@AlexBlex I mean don't distinguish between varchar, int, and date all treat by the same way: fieldname like '%search_keyword_in_Arabic%' — Gouda Elalfy, Jan 13 '16 at 10:12
Please provide `SHOW CREATE TABLE` for the table in question. — Rick James, Jan 13 '16 at 23:12
Btw: the actual error message should contain the collations involved and whether they have been set im- or explicitly. In my previous example that would be "Illegal mix of collations (utf8_general_ci,EXPLICIT) and (utf8_turkish_ci,EXPLICIT) for operation 'like'". Could you please add that part of the message you get? — VolkerK, Jan 14 '16 at 14:56
And the field "causing" the error is `\`BILL_DATE\` date NOT NULL,` - as it is the only date/time/datetime/timestamp field in the table defintion? — VolkerK, Jan 14 '16 at 15:07
@VolkerK I put the error message like mysql produce exactly, and of course there are another date or datetime column according to the create table script provided but I use BILL_DATE only in my search columns query with others varchar and int column. But I sure if there is any date type column other in query also will produce the same error. — Gouda Elalfy, Jan 14 '16 at 16:50
"there are another date or datetime column according to the create table script provided" - I know this must sound really nagging, but please understand that I would like there to be a _precise_ answer (not just throwing in converts() everywhere); but that requires equally precise input ;-) According to the CREATE TABLE statement you've provided there are 28*varchar, 2*tinyint, 3*text, 9*int, 1*float, 7*double, 2*decimal fields and only one _date field_, and that's BILL_DATE. So, I ask again: are you _absolutely positive_, BILL_DATE is the _single_ "cause" of the error? — VolkerK, Jan 14 '16 at 17:45
And if you don't get the `(utf8_general_ci,EXPLICIT) and (utf8_turkish_ci,EXPLICIT)` part, the error code is probably 1271 (and not 1267 as I assumed). Is the error code somewhere in the error message, so you can confirm that? — VolkerK, Jan 14 '16 at 17:59

score 8 · Accepted Answer · answered Jan 13 '16 at 11:56

8

When you use like on Date fields, they treated as latin strings. Assuming you are using utf8 charset, you need to convert date column before like it:

convert(field1 using utf8) LIKE '%كلمة%' OR convert(field2 using utf8) LIKE '%كلمة%' OR convert(field3 using utf8) LIKE '%كلمة%'

Ideally, if you add some logic here and conditionally apply conversion to date fields only. E.g. if you know that field2 is the only date field, you can build the query:

field1 LIKE '%كلمة%' OR convert(field2 using utf8) LIKE '%كلمة%' OR field3 LIKE '%كلمة%'

answered Jan 13 '16 at 11:56

Alex Blex

34,704
7
48
75

but why this error only on the hosting server, it was running without any errors on my localhost. do you know? – Gouda Elalfy Jan 13 '16 at 12:20
Servers must differ somehow. I would compare `show variables where value like '%utf%';` for both systems, but to be honest, I don't know much about it. I just shamelessly stole the answer from http://stackoverflow.com/questions/14519271/mysql-collation-operation-with-a-like-issue – Alex Blex Jan 13 '16 at 13:10
1

If you have to `CONVERT` to utf8, then the field cannot contain Arabic characters. utf8 (and utf8mb4) are the only `CHARACTER SETs` (in MySQL) than can represent Arabic. Hence, I don't see how this solution can work. So, maybe it is a mix of `COLLATIONs`? In which case, let's see `SHOW CREATE TABLE`. @VolkerK's comment comes closer. – Rick James Jan 13 '16 at 23:16
"they treated as latin strings" - is that so, or are they converted to the charset_connection/collation_connection? – VolkerK Jan 14 '16 at 00:44
@RickJames you are right, my charset is utf8, but the problem occur with date type column not varchar columns. – Gouda Elalfy Jan 14 '16 at 07:45

Date type issue with Like clause Arabic string in mysql

1 Answers1