What are the challenges you face when working across database platforms? Take the survey

Illegal mix of collections of operation 'UNION'

I got an Illegal mix of collections of operation 'UNION' when I try to compare an AWS MySQL to a localhost MySQL. 


  • Options
    PeterLPeterL Posts: 1 New member
    Same problem here, and this appears to be the bit that causes the error:

    SELECT <br>&nbsp;&nbsp;&nbsp; 'TRIGGER' AS `TYPE`,<br>&nbsp;&nbsp;&nbsp; t.TRIGGER_SCHEMA AS `DATABASE`,<br>&nbsp;&nbsp;&nbsp; t.TRIGGER_NAME&nbsp; AS `NAME` ,<br>&nbsp;&nbsp;&nbsp; 'TABLE' AS `REFERENCED_TYPE`,<br>&nbsp;&nbsp;&nbsp; t.EVENT_OBJECT_SCHEMA AS `REFERENCED_DATABASE`,<br>&nbsp;&nbsp;&nbsp; t.EVENT_OBJECT_TABLE AS `REFERENCED_NAME`<br>FROM <br>&nbsp;&nbsp;&nbsp; INFORMATION_SCHEMA.TRIGGERS t<br>WHERE t.TRIGGER_SCHEMA = 'sys'<br><br>UNION ALL<br>SELECT<br>&nbsp;&nbsp;&nbsp; 'TABLE' AS `TYPE`,<br>&nbsp;&nbsp;&nbsp; c.CONSTRAINT_SCHEMA AS `DATABASE`,<br>&nbsp;&nbsp;&nbsp; c.TABLE_NAME&nbsp; AS `NAME`,<br>&nbsp;&nbsp;&nbsp; 'TABLE' AS `REFERENCED_TYPE`,<br>&nbsp;&nbsp;&nbsp; c.UNIQUE_CONSTRAINT_SCHEMA AS `REFERENCED_DATABASE`,<br>&nbsp;&nbsp;&nbsp; c.REFERENCED_TABLE_NAME AS `REFERENCED_NAME`<br>FROM<br>&nbsp;&nbsp;&nbsp; INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c<br>WHERE c.CONSTRAINT_SCHEMA = 'sys';

    TRIGGER_NAME is utf8_general_ci, while TABLE_NAME is utf8_tolower_ci [at least in MySQL 8.0.16]. This basically makes the tool unusable, so it'd be nice to get it fixed...
  • Options
    I also get the error "Illegal mix of collations for operation 'UNION'" when trying to compare to a local mysql (version 
    database. This will occur even if it is a completely empty database. I've tried all options available in the mysqlcompare tool, nothing helps. 
    As PeterL notes, this makes the tool unusable.
  • Options
    TrueByteTrueByte Posts: 1 New member
    Hi guys,

    Is there a fix for this issue?

Sign In or Register to comment.