CODEWITHSUNDEEP
mysqlnode.jsencodingsequelize.jscollation
MR_BD
MR_BD

Reputation: 105

Illegal mix of collations in sequelize

I have recently update mysql to version 8.0 and I am facing an error.

I have a query that is UNION between a few tables. When I run it directly in CLI everything works fine, but my node app cannot run it with sequelize. This is the error I get

SequelizeDatabaseError: Illegal mix of collations for operation 'UNION'

Is there any setting in sequelize that can solve this issue?

Upvotes: 0

Views: 618

Answers (1)

nbk
nbk

Reputation: 49373

this is how you convert a text into anothe4r charcterset and collation

SELECT CONVERT('test' USING utf8mb4) COLLATE utf8mb4_bin;
SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;

So you would make something like this

CREATE TABLE t1
(
    c1 CHAR(10)
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci

CREATE TABLE t2
(
    c1 CHAR(10) 
) DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci

SELECT c1 FROm t1
UNION
SELECT c1 FROm t2

Illegal mix of collations for operation 'UNION'

SELECT CONVERT(c1, CHAR CHARACTER SET latin1) COLLATE latin1_german1_ci FROm t1
UNION
SELECT c1 FROm t2

| CONVERT(c1, CHAR CHARACTER SET latin1) COLLATE latin1_german1_ci |
| :--------------------------------------------------------------- |

db<>fiddle here

Upvotes: 1

Related Questions