Collation Error on UPDATE statement using a defined function

Question

As you will see from my code, I've already specified the collation of my created table and the return of my function, yet I'm still getting the error message. Why is that ?

I'm getting the following error when applying my query in PHPmyAdmin-MYSQL: SQL query: UPDATE tb SET balance = (SELECT fnaccount_getbalance(acguid COLLATE utf8_general_ci,accur COLLATE utf8_general_ci,@stdate,@endate)) Error:

1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

Here is my Query:

CREATE TEMPORARY TABLE tb (id INT PRIMARY KEY AUTO_INCREMENT,
                           acguid char(38),
                           accur char(38),
                           account varchar(255),
                           debitsyp float,
                           creditsyp float,
                           balance float,
                           currency varchar(255))
CHARACTER SET utf8
COLLATE utf8_unicode_ci;
SET @stdate = '2011-01-01';
SET @endate = CURDATE();

INSERT INTO tb (acguid,accur,account,debitsyp,creditsyp,balance,currency)
SELECT ac.guid,ac.currencyguid,CONCAT(ac.code,'-',ac.name) AS account,0,0,0,my.code
FROM ac000 ac INNER JOIN my000 my ON ac.currencyguid = my.guid
WHERE ac.guid IN (SELECT accountguid FROM en000);

UPDATE tb SET debitsyp = (SELECT SUM(debit) FROM en000 WHERE accountguid = acguid);
UPDATE tb SET creditsyp = (SELECT SUM(credit) FROM en000 WHERE accountguid = acguid);

UPDATE tb SET balance  = (SELECT fnaccount_getbalance(acguid,accur,@stdate,@endate));

SELECT * FROM tb;

And here is the code for my function fnaccount_getbalance:

BEGIN
    DECLARE acbal float;
    SET acbal = (SELECT IFNULL(SUM((CASE
                                    WHEN currencyguid = accur THEN debit / currencyval
                                    ELSE debit / fngetcurval(accur,endate)
                                    END) - 
                                (CASE
                                    WHEN currencyguid = accur THEN credit / currencyval
                                    ELSE credit / fngetcurval(accur,endate)
                                END)),0) as balance
                FROM en000 WHERE accountguid = acguid
                AND endate BETWEEN stdate AND endate);
    
    RETURN (acbal COLLATE utf8_unicode_ci);
END

Possible duplicate of [Troubleshooting "Illegal mix of collations" error in mysql](https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql) — zod, Sep 20 '19 at 21:56
As you can see from my code, I've already specified the collation of my created table and the return of my function, yet I'm still getting the error message. Why is that ? — Salah Khaldoun Mousa Basha, Sep 20 '19 at 22:01
Balance field and actbal variables are numbers, so collation cannot be a problem, so using collations to turn them into strings is pointless. The error must come from within the function - probably when you do the select from the other table. The error message shows explicit string colkation conversion, while the code does not have this conversion when you pass the parameters. Why is that? — Shadow, Sep 20 '19 at 22:24

score 1 · Accepted Answer · answered Sep 20 '19 at 23:03

1

I resolved the matter by changing the DATABASE collation to utf8_unicode_ci

Previously it was utf8_general_ci

Then I Dropped and recreated all the functions I had in a new SQL query

Originally I set the database collation to utf8_general_ci based on an advice from another website, is it bad to set DATABASE collation to unicode instead of general ?

answered Sep 20 '19 at 23:03

Salah Khaldoun Mousa Basha

21
6

`utf8_general_ci` is a simplistic collation. Better is `utf8_unicode_ci`, which corresponds to the rather old Unicode 4.0 standard. Then came the 5.20 standard and `utf8_unicode_520_ci`. When you get to MySQL 8.0, there is an even better collation. – Rick James Oct 06 '19 at 05:39
On the other hand, you are not likely to notice any difference in those collations. – Rick James Oct 06 '19 at 05:40

Collation Error on UPDATE statement using a defined function

1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

1 Answers1