There is a request (someone wrote to me), which is executed on the master and works fine, but on the slave the replica with the error Illegal mix of collations (utf8_general_ci, IMPLICIT) and (latin1_swedish_ci, NUMERIC) for operation '=' on query falls.

The master and slave are identical in settings (version 5.6.17), encoded in both UTF8, compared to utf8_general_ci. The database used and the tables in the query are also in UTF8, utf8_general_ci. The query has variables that are set sessionally in

SET @`date_start`:=_utf8 COLLATE `utf8_general_ci`; SET @`date_stop`:=_utf8 COLLATE `utf8_general_ci`; SET @`prevTelephony`:=NULL; SET @`prevD`:=NULL; SET @`minT`:=NULL; SET @`prevT`:=NULL;

The request itself:

 INSERT INTO AT_t_Dscpl_ForeCast_tmp (Id_Telephony, Id_WFM, date_start,Time_Start, Date_Stop, Time_Stop, Id_Actv) SELECT sFC.Id_Telephony, sFC.Id_WFM, sFC.Date_Start,sFC.minT as Time_Start, Cast(date(max(CONCAT(sFC.Date_Stop, ' ', sFC.Time_Stop))) as DATE) as Date_Stop, Cast(time(max(CONCAT(sFC.Date_Stop, ' ', sFC.Time_Stop))) as TIME)as Time_Stop, sFC.Id_Actv from (SELECT distinct fc.Id_Telephony,fc.Id_WFM, case when @prevTelephony is null then @prevTelephony:=fc.Id_Telephony when @prevTelephony != fc.Id_Telephony then (@prevTelephony := fc.Id_Telephony) and (@minT:= null) and (@prevT := NULL) end as vTelephony, fc.date_start, CASE WHEN @prevD is null THEN @prevD := fc.date_start WHEN @prevD != fc.date_start THEN (@minT:= null) and (@prevT := NULL) END nDayStrt, fc.Time_Start, case when @minT is null then @minT:=fc.Time_Start else @minT:=if(@prevT=fc.Time_Start, @minT, fc.Time_Start) end minT, fc.Date_Stop, fc.Time_Stop, if(fc.Id_Actv, 1, 0) as Id_Actv, @prevT := fc.Time_Stop, @prevD := fc.date_start from (SELECT Id_WFM, Id_Telephony, Date_Start, Time_Start, Date_Stop,Time_Stop,Id_Actv from AT_t_Dscpl_ForeCast where Date_Start BETWEEN @date_start and @date_stop and Id_Actv != 99 and Id_Telephony != 0) AS fc WHERE fc.Date_Start BETWEEN @date_start and @date_stop order BY fc.Id_Telephony, fc.Id_WFM, fc.date_start, fc.Time_Start) sFC group by sFC.Id_Telephony, sFC.Id_WFM, sFC.Date_Start, sFC.minT

1 answer 1

Check the table and column level encoding on the slave server. You don’t have miracles comparing utf8 to latin1-value. Perhaps at the stage of the slave server setup, an error was made and the tables, followed by columns, got the latin1 encoding, instead of utf8.

Now it is the most accurate to contact the slave server and execute the SHOW CREATE TABLE statement for the replicated database for each of the database tables. The operator’s report will give the encoding used If it is latin1, the slave server needs to be recovered by deploying the correct dump.

Locate the my.cnf of both servers and check the contents of character-set-server directives; if not, it is better to explicitly set to utf8. Now it will not help, but will not allow to repeat the situation in the future.

As an option, specify the mapping explicitly in the request.

 WHERE Date_Start BETWEEN @date_start AND @date_stop AND Id_Actv != 99 COLLATE utf8_general_ci AND Id_Telephony != 0 COLLATE utf8_general_ci
  • I already looked, at level of the table CHARSET = utf8. At the column level ... by data type int and date. - Roman Morohovets
  • And as far as I know the encoding at the column level can be set only for char, varchar and text. - Roman Morohovets
  • @Roman Morohovets Strictly speaking, yes, but you have an error message (latin1_swedish_ci, NUMERIC) - i.e. MySQL connects the comparison with a numeric value, you need to calculate where it comes from latin1, and it is better to set the default for the entire utf8 server at the level of my.cnf - cheops
  • in my.cnf on the master and the slave is utf8. << it is necessary to calculate where does latin1 come from >> __ I’m trying to do it for a week now ( - Roman Morohovets
  • @Roman Morohovets, added the answer. - cheops