MySQL Bugs: #31416: #1271 - Illegal mix of collations for operation 'concat'

Bug #31416	#1271 - Illegal mix of collations for operation 'concat'
Submitted:	5 Oct 2007 3:13	Modified:	2 Nov 2007 17:34
Reporter:	Gregory Agerba	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.0.45-community-log	OS:	Linux (CentOS 4.4)
Assigned to:		CPU Architecture:	Any
Tags:	#1271, 1271, Collations, error, Illegal, mix

Description:
Hello,

A customer reported a strange error with a VIEW. 

Before posting this issue here, I have googled and tried a few solutions, such as changing the collation in the /etc/my.cnf and so...

I have tried also to reproduce this on my local EasyPHP2.0b and I did not got any error, the error only does this on all servers with the same version (as it's a profile and all our machines).

I have seen this : http://bugs.mysql.com/bug.php?id=21505 - maybe it is something common to it !?

Server details :

- Linux CentOS 4.4
- Apache 2.2.6
- PHP 5.2.4 (CGI) & 4.4.7 (CGI)
- MySQL 5.0.45-community-log

my.cnf :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
#skip-networking
safe-show-database
query_cache_limit=1M
query_cache_size=96M
query_cache_type=1
max_user_connections=200
max_connections=500
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=192M
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=3M
read_buffer_size=3M
read_rnd_buffer_size=3M
thread_concurrency=4
myisam_sort_buffer_size=64M
server-id=1
log_slow_queries=/var/log/mysql-slow-queries.log
long_query_time=2
collation-server=latin1_swedish_ci
#collation-server=latin1_general_ci
old-passwords

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

How to repeat:
Create two TABLES :

CREATE TABLE rmatest (
NumeroRMA int(10) unsigned NOT NULL auto_increment,
DataRMA date NOT NULL,
CodiceCliente varchar(12) collate latin1_general_ci NOT NULL,
CodiceDestin varchar(5) collate latin1_general_ci NOT NULL,
CodCentroAssist varchar(12) collate latin1_general_ci NOT NULL,
CodCentroAssistDest varchar(5) collate latin1_general_ci NOT NULL,
CodVettore varchar(5) collate latin1_general_ci NOT NULL,
Chiusa int(10) unsigned NOT NULL default '0',
Spedita int(11) unsigned NOT NULL default '0',
NrRappCentroAssist int(10) unsigned NOT NULL default '0',
DtRappCentroAssist date NOT NULL default '0000-00-00',
NrDocResoCentroAssist varchar(50) collate latin1_general_ci NOT NULL,
DtDocResoCentroAssist date NOT NULL default '0000-00-00',
PRIMARY KEY  (NumeroRMA)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=138 ;

CREATE TABLE rmarighe (
NumeroRMA int(10) unsigned NOT NULL auto_increment,
NrRiga int(10) unsigned NOT NULL,
CodProdotto varchar(20) collate latin1_general_ci NOT NULL,
NrSeriale varchar(50) collate latin1_general_ci NOT NULL default '',
Quantita int(10) unsigned NOT NULL default '0',
Difetto varchar(250) collate latin1_general_ci NOT NULL default '',
NrDDTAcq int(10) unsigned NOT NULL default '0',
DtDDTAcq date NOT NULL default '2000-01-01',
ErrNrSeriale int(10) unsigned NOT NULL default '0',
ResoGaranzia int(10) unsigned NOT NULL default '0',
PRIMARY KEY (NumeroRMA,NrRiga)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='InnoDB free: 35840 kB' AUTO_INCREMENT=138 ; 

Create the VIEW :
-----------------

CREATE VIEW rmarighe_exp_w AS
select t.NumeroRMA AS numerorma
,concat(substr(concat(space(10),r.NumeroRMA),-10,10) ,'|'
,substr(concat(space(5),r.NrRiga),-5,5) ,'|'
,substr(concat(r.CodProdotto,space(20)),1,20) ,'|'
,substr(concat(r.NrSeriale,space(50)),1,50) ,'|'
,substr(concat(space(10),r.Quantita),-10,10) ,'|'
,substr(concat(r.Difetto,space(250)),1,250) ,'|'
,substr(concat(space(10),r.NrDDTAcq),-10,10),'|'
,date_format(r.DtDDTAcq,'%Y-%m-%d'),'|') AS record
from (rmatest t join rmarighe r on((r.NumeroRMA = t.NumeroRMA)))
where (t.Spedita = 0);

Show the VIEW :

SHOW INDEX FROM `rmarighe_exp_w` ;

Error you should get :

#1271 - Illegal mix of collations for operation 'concat' 

Suggested fix:
Maybe relative to this : http://bugs.mysql.com/bug.php?id=21505 ?

Thank you for the bug report. I am not able to repeat with current source
server:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.52-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> SHOW INDEX FROM `rmarighe_exp_w` ;
Empty set (0.00 sec)

Hello,

This have corrected the problem, but I guess I shouldn't have any error, even without to change it, no ?

CREATE TABLE rmatest (
NumeroRMA int(10) unsigned NOT NULL auto_increment,
DataRMA date NOT NULL,
CodiceCliente varchar(12) collate utf8_general_ci NOT NULL,
CodiceDestin varchar(5) collate utf8_general_ci NOT NULL,
CodCentroAssist varchar(12) collate utf8_general_ci NOT NULL,
CodCentroAssistDest varchar(5) collate utf8_general_ci NOT NULL,
CodVettore varchar(5) collate utf8_general_ci NOT NULL,
Chiusa int(10) unsigned NOT NULL default '0',
Spedita int(11) unsigned NOT NULL default '0',
NrRappCentroAssist int(10) unsigned NOT NULL default '0',
DtRappCentroAssist date NOT NULL default '0000-00-00',
NrDocResoCentroAssist varchar(50) collate utf8_general_ci NOT NULL,
DtDocResoCentroAssist date NOT NULL default '0000-00-00',
PRIMARY KEY  (NumeroRMA)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=138 ;

CREATE TABLE rmarighe (
NumeroRMA int(10) unsigned NOT NULL auto_increment,
NrRiga int(10) unsigned NOT NULL,
CodProdotto varchar(20) collate utf8_general_ci NOT NULL,
NrSeriale varchar(50) collate utf8_general_ci NOT NULL default '',
Quantita int(10) unsigned NOT NULL default '0',
Difetto varchar(250) collate utf8_general_ci NOT NULL default '',
NrDDTAcq int(10) unsigned NOT NULL default '0',
DtDDTAcq date NOT NULL default '2000-01-01',
ErrNrSeriale int(10) unsigned NOT NULL default '0',
ResoGaranzia int(10) unsigned NOT NULL default '0',
PRIMARY KEY (NumeroRMA,NrRiga)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='InnoDB free: 35840 kB' AUTO_INCREMENT=138 ;

Hello,

Changing the system charset to UTF-8 has solved the problem.

This issue can be closed.