MySQL Bugs: #19295: collation_connection doesn't works all the time

Bug #19295	collation_connection doesn't works all the time
Submitted:	24 Apr 2006 9:26	Modified:	12 Jun 2006 9:51
Reporter:	Sylvain	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Linux (Debian Sarge)
Assigned to:		CPU Architecture:	Any

Description:
Hi Guys !

I'm developping a web application in PHP 4.3.10 which use persistents connections, I use equally phpMyAdmin 2.8.0.3 which use persistents connections.

My database use latin1_swedish_ci collation for all fields and I use phpMyAdmin with utf8_general_ci collation connection.

Now, in my application I make always a SET collation_connection = 'latin1_swedish_ci' to force the collation connection to use because i've encountered some problems with UTF-8 characters in my application : the reason is that the login/pass are the same for phpMyAdmin and my application so my application used an UTF-8 database connection. So i forced the collation connection in my application.

The problem is that, even if I force the collation connection, I have sometimes UTF-8 characters in my application, a simple refresh of the browser correct the problem. I have play with PhpMyAdmin & my application in same time to reproduce the problem, it's very randomized and when UTF-8 characters appears, the collation connections used is set as an latin1_swedish_ci one (show variables like '%collation%') !! I don't understand..

How to repeat:
Make a table which use latin1_swedish_ci collation with some information (with accents) in MySQL.

Make a small application which use persistant connection in PHP (mysql_pconnect), the application will print the content of database and the %collation% variables.

Duplicate the application to make one with a latin1_swedish_ci and utf8_general_ci collation connection.

Play with the 2 applications in same time to try to reproduce the problem.

Oops, just for information, I use InnoDB tables

Thank you for a problem report. Please, provide a complete test case, with CREATE TABLE, some data in table, exect PHP application to demonstrate the problem. Then try to repeat with a newer version of MySQL, 4.1.18 (and don't forget to recompile PHP with newer mysqlclient library), and inform about the results.

File for latin1 :
================ CUT HERE ================
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fr">
<head>
        <title>Bug 19295</title>
        <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>

<?php

function showError(){
  die('#' . mysql_errno() . ' : ' . mysql_error()) ;
}

mysql_pconnect('bdd','user','pass');
mysql_select_db('db') or showError();
$q = "SET collation_connection = 'latin1_swedish_ci'" ;
mysql_query($q) or showError() ;

$q = "SELECT data FROM bug19295" ;
$rs = mysql_query($q) or showError() ;
while($row= mysql_fetch_assoc($rs)){
  echo $row['data'] . '<br />' ;
}

?>
</body>
</html>
================ CUT HERE ================

File for UTF-8 :

================ CUT HERE ================
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fr">
<head>
        <title>Bug 19295</title>
        <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<?php

function showError(){
  die('#' . mysql_errno() . ' : ' . mysql_error()) ;
}

mysql_pconnect('bdd','user','pass');
mysql_select_db('db') or showError() ;
$q = "SET collation_connection = 'utf8_general_ci'" ;
mysql_query($q) or showError() ;

$q = "SELECT data FROM bug19295" ;
$rs = mysql_query($q) or showError() ;
while($row= mysql_fetch_assoc($rs)){
  echo $row['data'] . '<br />' ;
}

?>
</body>
</html>
================ CUT HERE ================

DB
================ CUT HERE ================
CREATE TABLE `bug19295` (
  `data` varchar(20) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `bug19295` (`data`) VALUES ('Tête'),
('Cassé');
================ CUT HERE ================

I try this code many times but the bug don't reappear !?

I explain my method :
- open phpMyAdmin with utf8 collation connection
- launch 100 times file for UTF-8
- in phpMyAdmin, make a SHOW PROCESS and keep the pid list
- launch 1 time file for latin-1
- in phpMyAdmin, make a SHOW PROCESS and compare the pid list with the old one, if there are the same, latin-1 program reuse surely an UTF-8 collaction connection and normally would bug sometimes..

I don't understand, the problem is still in my own application which force collation connection to latin1 !? I fetch "D&Atilde;&copy;penses" instead of "Dépenses". The test application works all the time.
The problem is very randomized, I can make refresh of my browser many hundred times with no error and one time in more : *boom* :-/

I will use a MySQL 5 backport, I hope the problem will disappear.

Yes, please, try to repeat with the latest MySQL 5 version, 5.0.21, and PHP5/mysqli, and inform about the results. 

Is it intended to use DEFAULT CHARSET=latin1; for the table?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".