| Bug #116234 | confused collation error | ||
|---|---|---|---|
| Submitted: | 25 Sep 2024 20:24 | Modified: | 27 Sep 2024 11:45 |
| Reporter: | Nigel Gomm | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.37 | OS: | Any (Azure) |
| Assigned to: | CPU Architecture: | x86 (Azure MySQL Flex Server) | |
| Tags: | character sets, collate | ||
[26 Sep 2024 10:51]
MySQL Verification Team
Hi Mr. Gomm, Thank you very much for your bug report. However, let us inform you that this is a forum for reports with fully repeatable test cases. What we are missing here are the tables that are used to create a view. We need all CREATE TABLE commands and sufficient info to create the VIEW and get the error message. You should also let us know how have you set connection collation, because it could be an error as well. Hence, we also need the output for all character sets and collations, on the server level, schema level, table level, results level , client level and others ....... We are waiting on your full feedback.
[26 Sep 2024 12:22]
Nigel Gomm
CREATE TABLE `invoices` ( `invoiceid` INT NOT NULL AUTO_INCREMENT, `Date` DATE NULL DEFAULT NULL, `Invoice` INT NULL DEFAULT NULL, `Sub_total` DECIMAL(22,2) NULL DEFAULT NULL, `Vat` DECIMAL(22,2) NULL DEFAULT NULL, `Vat_rate` DECIMAL(22,2) NULL DEFAULT NULL, `Total` DECIMAL(22,2) NULL DEFAULT NULL, `Paid` BIT(1) NULL DEFAULT NULL, `Paydate` DATE NULL DEFAULT NULL, `Propref` DECIMAL(15,0) NULL DEFAULT NULL, `Building` DECIMAL(15,0) NULL DEFAULT NULL, `Llord` DECIMAL(15,0) NULL DEFAULT NULL, `Deal` DECIMAL(15,0) NULL DEFAULT NULL, `Who` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Descript` MEDIUMTEXT NULL DEFAULT NULL COLLATE 'latin1_general_ci', `X_seq` DECIMAL(15,0) NULL DEFAULT NULL, `Remsent` DATE NULL DEFAULT NULL, `Post_seq` DECIMAL(15,0) NULL DEFAULT NULL, `Dealid` INT NULL DEFAULT NULL, `Paytransid` INT NULL DEFAULT NULL, `Term` INT NULL DEFAULT NULL, `Clientpay` BIT(1) NULL DEFAULT NULL, `Statement` BIT(1) NULL DEFAULT NULL, `Paid2date` DATE NULL DEFAULT NULL, `Paidas` INT NULL DEFAULT NULL, `Paidhow` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Commrate` DECIMAL(22,2) NULL DEFAULT NULL, `Commfee` INT NULL DEFAULT NULL, `Commission` DECIMAL(22,2) NULL DEFAULT NULL, `Rentdemand` BIT(1) NULL DEFAULT NULL, `Duedate` DATE NULL DEFAULT NULL, `Void` BIT(1) NULL DEFAULT NULL, `Exportsage` DATETIME NULL DEFAULT NULL, `Voiddate` DATE NULL DEFAULT NULL, `Contactid` INT NULL DEFAULT NULL, `Offerid` INT NULL DEFAULT NULL, `Paid2landlord` BIT(1) NULL DEFAULT NULL, `Paid2chequenum` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Paid2bankref` INT NULL DEFAULT NULL, `Transferamount` DECIMAL(22,2) NULL DEFAULT NULL, `Oldstylevat` BIT(1) NULL DEFAULT NULL, `Archived` BIT(1) NULL DEFAULT NULL, `Lastmodified` DATETIME NULL DEFAULT NULL, `Transferdate` DATE NULL DEFAULT NULL, `Billstotal` DECIMAL(22,2) NULL DEFAULT NULL, `Created` DATETIME NULL DEFAULT NULL, `Billstotal2` DECIMAL(22,2) NULL DEFAULT NULL, `Termdays` INT NULL DEFAULT NULL, `Reimbursables` DECIMAL(22,2) NULL DEFAULT NULL, `Receivefrom` INT NULL DEFAULT NULL, `Companyid` INT NULL DEFAULT NULL, `Accountcode` DECIMAL(15,0) NULL DEFAULT NULL, `Applicantid` INT NULL DEFAULT NULL, `Payhow` INT NULL DEFAULT NULL, `Transferred` DECIMAL(22,2) NULL DEFAULT NULL, `Rec2office` DECIMAL(22,2) NULL DEFAULT NULL, PRIMARY KEY (`invoiceid`) USING BTREE, INDEX `building` (`Building`) USING BTREE, INDEX `clientpay` (`Clientpay`) USING BTREE, INDEX `archived` (`Archived`) USING BTREE, INDEX `accountcod` (`Accountcode`) USING BTREE, INDEX `companyid` (`Clientpay`) USING BTREE, INDEX `date` (`Date`) USING BTREE, INDEX `deal` (`Deal`) USING BTREE, INDEX `dealid` (`Dealid`) USING BTREE, INDEX `duedate` (`Duedate`) USING BTREE, INDEX `invoice` (`Invoice`) USING BTREE, INDEX `llord` (`Llord`) USING BTREE, INDEX `offerid` (`Offerid`) USING BTREE, INDEX `paid` (`Paid`) USING BTREE, INDEX `post_seq` (`Post_seq`) USING BTREE, INDEX `propref` (`Propref`) USING BTREE, INDEX `void` (`Void`) USING BTREE, INDEX `who` (`Who`) USING BTREE, INDEX `xferamount` (`Transferamount`) USING BTREE, INDEX `x_seq` (`X_seq`) USING BTREE, INDEX `term` (`Term`) USING BTREE, INDEX `total` (`Total`) USING BTREE, INDEX `transferd` (`Transferred`) USING BTREE ) COLLATE='latin1_general_ci' ENGINE=InnoDB AUTO_INCREMENT=6089 ; CREATE TABLE `trans` ( `seq` INT NOT NULL AUTO_INCREMENT, `Building` INT NULL DEFAULT NULL, `Propref` INT NULL DEFAULT NULL, `Debit` DECIMAL(22,2) NULL DEFAULT NULL, `Credit` DECIMAL(22,2) NULL DEFAULT NULL, `Detail` VARCHAR(250) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `code` VARCHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_cs', `Date` DATE NULL DEFAULT NULL, `Invoice` INT NULL DEFAULT NULL, `Vendorseq` DECIMAL(15,0) NULL DEFAULT NULL, `Vendor` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Bill_inv` VARCHAR(25) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Cheque_no` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Exp` BIT(1) NULL DEFAULT NULL, `How` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Deal` DECIMAL(15,0) NULL DEFAULT NULL, `Due` DECIMAL(22,2) NULL DEFAULT NULL, `Till` DATE NULL DEFAULT NULL, `Reconciled` BIT(1) NULL DEFAULT NULL, `Recon_dat` DATE NULL DEFAULT NULL, `Deposited` DATE NULL DEFAULT NULL, `Llord` INT NULL DEFAULT NULL, `Bank` DECIMAL(15,0) NULL DEFAULT NULL, `Seq2` INT NULL DEFAULT NULL, `Cleared` DATE NULL DEFAULT NULL, `Bacsdate` DATE NULL DEFAULT NULL, `Depslipno` DECIMAL(15,0) NULL DEFAULT NULL, `Dealid` INT NULL DEFAULT NULL, `Account` INT NULL DEFAULT NULL, `Deleted` DATETIME NULL DEFAULT NULL, `Dssportion` DECIMAL(22,2) NULL DEFAULT NULL, `Manstatdat` DATETIME NULL DEFAULT NULL, `Lastmodified` DATETIME NULL DEFAULT NULL, `Lastcode` VARCHAR(254) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Lastuser` VARCHAR(25) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Reversal` BIT(1) NULL DEFAULT NULL, `Jobno` INT NULL DEFAULT NULL, `Reinvoice` DECIMAL(22,2) NULL DEFAULT NULL, `Vatamount` DECIMAL(22,2) NULL DEFAULT NULL, `Vatrate` DECIMAL(22,2) NULL DEFAULT NULL, `Attachment` VARCHAR(200) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Payshort` DECIMAL(22,2) NULL DEFAULT NULL, `Seq3` INT NULL DEFAULT NULL, `Datetime` DATETIME NULL DEFAULT NULL, `Who` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Offerid` INT NULL DEFAULT NULL, `Applicantid` INT NULL DEFAULT NULL, `Contactid` INT NULL DEFAULT NULL, `X_seq` INT NULL DEFAULT NULL, `Tenantid` INT NULL DEFAULT NULL, `Manstatnum` INT NULL DEFAULT NULL, `Blockid` INT NULL DEFAULT NULL, `Budgetitemid` INT NULL DEFAULT NULL, `Creditac` INT NULL DEFAULT NULL, `Debitac` INT NULL DEFAULT NULL, `Tbdate` DATE NULL DEFAULT NULL, `Tbamount` DECIMAL(22,2) NULL DEFAULT NULL, `Standingorderid` INT NULL DEFAULT NULL, `Ofxid` VARCHAR(250) NULL DEFAULT NULL COLLATE 'latin1_general_ci', `Manstatid` INT NULL DEFAULT NULL, `Invoiced` INT NULL DEFAULT NULL, `Llindex` INT NULL DEFAULT NULL, `Detail2` VARCHAR(250) NULL DEFAULT NULL COLLATE 'latin1_general_ci', PRIMARY KEY (`seq`) USING BTREE, INDEX `cleared` (`Cleared`) USING BTREE, INDEX `propref` (`Propref`) USING BTREE, INDEX `seq` (`seq`) USING BTREE, INDEX `seq2` (`Seq2`) USING BTREE, INDEX `till` (`Till`) USING BTREE, INDEX `reinvoice` (`Reinvoice`) USING BTREE, INDEX `vendor` (`Vendor`) USING BTREE, INDEX `vendorseq` (`Vendorseq`) USING BTREE, INDEX `seq3` (`Seq3`) USING BTREE, INDEX `who` (`Who`) USING BTREE, INDEX `payshort` (`Payshort`) USING BTREE, INDEX `x_seq` (`X_seq`) USING BTREE, INDEX `tenantid` (`Tenantid`) USING BTREE, INDEX `detail` (`Detail`) USING BTREE, INDEX `tbdate` (`Tbdate`) USING BTREE, INDEX `reconciled` (`Reconciled`) USING BTREE, INDEX `ofxid` (`Ofxid`) USING BTREE, INDEX `account` (`Account`) USING BTREE, INDEX `bank` (`Bank`) USING BTREE, INDEX `building` (`Building`) USING BTREE, INDEX `code` (`code`) USING BTREE, INDEX `date` (`Date`) USING BTREE, INDEX `deal` (`Deal`) USING BTREE, INDEX `dealid` (`Dealid`) USING BTREE, INDEX `due` (`Due`) USING BTREE, INDEX `how` (`How`) USING BTREE, INDEX `invoice` (`Invoice`) USING BTREE, INDEX `llord` (`Llord`) USING BTREE, INDEX `manstatdat` (`Manstatdat`) USING BTREE, INDEX `jobno` (`Jobno`) USING BTREE, INDEX `bill_inv` (`Bill_inv`) USING BTREE, INDEX `applicanti` (`Applicantid`) USING BTREE, INDEX `contactid` (`Contactid`) USING BTREE, INDEX `offerid` (`Offerid`) USING BTREE, INDEX `credit` (`Credit`) USING BTREE, INDEX `debit` (`Debit`) USING BTREE, INDEX `deposited` (`Deposited`) USING BTREE, INDEX `creditac` (`Creditac`) USING BTREE, INDEX `invoiced` (`Invoiced`) USING BTREE, INDEX `manstatid` (`Manstatid`) USING BTREE, INDEX `bacsdate` (`Bacsdate`) USING BTREE ) COLLATE='latin1_general_ci' ENGINE=InnoDB AUTO_INCREMENT=87020 ;
[26 Sep 2024 12:30]
Nigel Gomm
using HeidiSQL i login to mySQL and the session variable collation_connection defaults to utf8mb4_0900_ai_ci. Create a view there and all is ok. Change the session variable with a SET COLLATION_CONNECTION and the problem appears. When i connect using ODBC32 the session variable is latin_general_ci and if that connection (i.e. my application) creates the view the problem appears. This user and all other users get that illegal mix error. But if my application first SETS the variable to utf8mb4_0900_ai_ci... all is well.
[26 Sep 2024 14:33]
MySQL Verification Team
Hi Mr. Gomm, We have not got all the info we asked for. Let us repeat what we need: We need all CREATE TABLE commands and sufficient number of rows from each table to create the VIEW and get the error message. You should also let us know how have you set connection collation, because it could be an error as well. Hence, we also need the output for all character sets and collations, on the server level, schema level, table level, results level , client level and others ....... We are waiting on your full feedback.
[27 Sep 2024 10:39]
Roy Lyseng
This could be the same as bug#115925, which will be fixed in the 9.1 release.
[27 Sep 2024 11:12]
MySQL Verification Team
Thank you, Roy.
[27 Sep 2024 11:45]
Nigel Gomm
sounds like #115925. I have a workaround for now so am happy for this to be closed. I can try and put together a script that replicates if required but still dealing with fallout from this on production servers so a bit swamped right now.
[27 Sep 2024 11:47]
MySQL Verification Team
Hi MR. Gomm, Please let us know how did it go ......
Description: i have a 8.0.37 mysql server running on azure. The server, the database, all tables and all columns are set to character set latin1 and collate latin1_general_ci. when i create a view with unions from a user where the session variable collation_connection is utf8mb4_0900_ai_ci the view works as it should for every other user. if i create the view from a user where that session variable is 'latin1_general_ci' every user gets the Illegal mix of collations for operation 'UNION' error message. I should add one of the selects in the union uses CAST as CHAR and CONCAT. How to repeat: set server, database, tables and columns to 'latin1' and 'latin_general_ci'. login to mySQL with a user where the session variable collation_connection is utf8mb4_0900_ai_ci. create a view with unions (e.g. see below). try selecting from that view and it will work. now set collation_connection = 'latin1_general_ci' and drop and recreate the view. I'm getting the 'illegal mix' error when i try to select from that view. this is my VIEW. Note column trans.code is collated as latin1_general_cs. drop view if exists vwlandlordtransactions; CREATE OR REPLACE VIEW vwlandlordtransactions as SELECT t.seq,r.shortaddress as property,b.badd1,t.building,t.propref,t.DATE,t.credit,t.debit+payshort AS debit,t.due,t.DETAIL,t.how,t.cheque_no,t.llord,t.CODE,t.deal,t.ACCOUNT,t.attachment,t.invoice FROM trans T left JOIN building b ON b.building = t.building LEFT JOIN rental r ON r.propref = t.propref WHERE t.how NOT IN ('Adjustment','DSS-Adjustment') AND t.ACCOUNT IN (201,400,800) AND (t.CODE <> 'B' OR t.reinvoice=0) union all SELECT 0 as seq,r.shortaddress as property,b.badd1,i.building,i.propref,i.dueDATE AS date,i.total-i.total AS credit,i.total AS debit,i.total-i.total AS due,left(CONCAT('Invoice ',CAST(i.invoice as CHAR),' :: ',i.DEscript),250) AS detail,' ' AS how,' ' as cheque_no,i.llord,'C' AS code,i.deal,150 AS account,'' as attachment,i.invoice FROM invoices i left JOIN building b ON b.building = i.building LEFT JOIN rental r ON r.propref = i.propref WHERE i.invoice NOT IN (select invoice from trans where CODE='C' AND i.invoice = trans.invoice) AND i.void = 0 AND i.archived = 0 and i.llord > 0 union all SELECT t.seq,r.shortaddress as property,b.badd1,t.building,t.propref,t.DATE,t.credit,t.due AS debit,t.due,t.DETAIL,t.how,t.cheque_no,t.llord,t.CODE,t.deal,t.ACCOUNT,attachment,invoice FROM trans T left JOIN building b ON b.building = t.building LEFT JOIN rental r ON r.propref = t.propref WHERE code='J' and due > 0 ORDER BY 3,4,5;