init_replica: ERROR 1270 (HY000): Illegal mix of collations

[ghost]

We got the following table:

 CREATE TABLE `channel_attributes` (
  `channel_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `attribute_id` bigint(20) NOT NULL,
  `value` varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT 'true',
  PRIMARY KEY (`channel_id`,`attribute_id`),
  KEY `attribute_id` (`attribute_id`),
  CONSTRAINT `channel_attributes_ibfk_2` FOREIGN KEY (`attribute_id`) REFERENCES `channel_attributes_domain` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

This table produces the following issue when running init_replica:

Jul 17 09:46:49: [INFO] - mysql_lib.py (612): copying table channel_attributes
Jul 17 09:46:49: [DEBUG] - mysql_lib.py (615): estimating rows in channel_attributes
Jul 17 09:46:49: [DEBUG] - mysql_lib.py (644): channel_attributes will be copied in 1 slices of 3054105 rows
Jul 17 09:46:49: [DEBUG] - mysql_lib.py (651): Executing query for table channel_attributes
Jul 17 09:46:49: [ERROR] - mysql_lib.py (654): error when pulling data from channel_attributes. sql executed: SELECT REPLACE(CONCAT('"',CONCAT_WS('","',COALESCE(REPLACE(`channel_id`, '"', '""'),'NULL') ,COALESCE(REPLACE(`attribute_id`, '"', '""'),'NULL') ,COALESCE(REPLACE(`value`, '"', '""'),'NULL') ),'"'),'"NULL"','NULL') as data FROM channel_attributes;
Jul 17 09:46:49: [DEBUG] - mysql_lib.py (656): Starting extraction loop for table channel_attributes
Jul 17 09:46:49: [INFO] - mysql_lib.py (692): the table channel_attributes does not exist

running the sql manually shows the following extra error:

mysql> SELECT REPLACE(CONCAT('"',CONCAT_WS('","',COALESCE(REPLACE(`channel_id`, '"', '""'),'NULL') ,COALESCE(REPLACE(`attribute_id`, '"', '""'),'NULL') ,COALESCE(REPLACE(`value`, '"', '""'),'NULL') ),'"'),'"NULL"','NULL') as data FROM channel_attributes;
ERROR 1270 (HY000): Illegal mix of collations (utf8_bin,NONE), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation 'replace'

The issue is primarily with the value column - removing it from the sql and it works.

[ghost]

Note we got a similar issue with a table that contains a bit column:

CREATE TABLE `channel_attributes_domain` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `title` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `internal_description` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `attribute_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Boolean',
  `editable` bit(1) NOT NULL DEFAULT b'1',
  `channel_type` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'All',
  `channel_id` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_2` (`code`,`channel_type`,`channel_id`),
  KEY `channel_id` (`channel_id`),
  KEY `code` (`code`),
  CONSTRAINT `channel_attributes_domain_ibfk_1` FOREIGN KEY (`channel_id`) REFERENCES `channel` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=630 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

[the4thdoctor]

thanks for the report. I'll have a look asap.

[the4thdoctor]

can you please test the branch ver1.5? I've added a cast with character set on the column generation.

Thanks

[ghost]

running tests right now

[ghost]

those channel_attribute* tables are now running without issues when running init_replica
Thanks!

[the4thdoctor]

thank you for the quick feedback :)
I'll release the patched version at last by Saturday 22nd.
I need to sort out the default value DDL replica first.