Bug #6511 Column header corruption in query results
Submitted: 9 Nov 2004 0:32 Modified: 16 Feb 2005 19:21
Reporter: Travers Carter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:Linux (Linux x86)
Assigned to: Jim Winstead

[9 Nov 2004 0:32] Travers Carter
Description:
At times query results shown in the command line client are displayed with corrupted column 
headers as below. 
 
First case (MyISAM latin1 table originally created in MySQL 4.0.x):  
 
mysql> SELECT * FROM region; 
+------+-------------------+-----+------+---------------------+------+-----------------------+----------------------+-----------------------------+ 
| #B   | @                |    |     |                    |      |                       |                      |                             | 
+------+-------------------+-----+------+---------------------+------+-----------------------+----------------------+-----------------------------+ 
... 
 
Expected result was 
mysql> select * from region; 
+----+-------------------+-------------+-----------+---------------------+-----------+-----------------------+----------------------+-----------------------------+ 
| id | Name              | Description | ShortName | Path                | GMTOffset | EmailFromAddress      | 
ContactNumber        | ReplyToAddress              | 
+----+-------------------+-------------+-----------+---------------------+-----------+-----------------------+----------------------+-----------------------------+ 
... 
 
Second  case: 
 
mysql> SELECT * FROM permissionaction (MyISAM latin1 table created in MySQL 4.1.7); 
+------+-------------------+--------------------------+------+ 
| #B   |                  |                          | !    | 
+------+-------------------+--------------------------+------+ 
... 
 
Expected result was 
 
mysql> select * from permissionaction; 
+----+-------------------+--------------------------+--------------+ 
| id | Name              | DefineSymbol             | PermissionID | 
+----+-------------------+--------------------------+--------------+ 
... 

How to repeat:
The first case could be repeated by running 
 SELECT * FROM region\G 
before running 
 SELECT * FROM region 
 
but dropping and recreating the table appeared to resolve this, but a very simillar problem has 
started occuring on a new table that was created with MySQL 4.1.7, I have been unable to 
determine what triggers the second case, it appears to be random, but once it occurs once it will 
happen consistantly unless SQL_NO_CACHE is specified, eg. 
 
mysql> select SQL_NO_CACHE * from permissionaction; 
+----+-------------------+--------------------------+--------------+ 
| id | Name              | DefineSymbol             | PermissionID | 
+----+-------------------+--------------------------+--------------+
[9 Nov 2004 12:31] Alexander Keremidarski
Check your terminal character sets and compare it to the:

SHOW VARIABLES LIKE "char%";
[10 Nov 2004 22:08] Travers Carter
It looks like the terminal is UTF-8, and MySQL is using latin1 can this cause the problem? 
If so shouldn't the client be picking up the terminal character set from the $LANG 
environment? 
The problem wasn't present using the same terminal with MySQL 4.0.21 on the same 
server. 
 
[trav@dev trav]$ echo $LANG 
en_AU.UTF-8 
[trav@dev trav]$ mysql -u root -p -e 'SHOW VARIABLES LIKE "char%";' mysql 
Enter password: 
+--------------------------+----------------------------+ 
| Variable_name            | Value                      | 
+--------------------------+----------------------------+ 
| character_set_client     | latin1                     | 
| character_set_connection | latin1                     | 
| character_set_database   | latin1                     | 
| character_set_results    | latin1                     | 
| character_set_server     | latin1                     | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+
[16 Nov 2004 0:11] Travers Carter
The problem also occurs (although I still can't replicate it on demand) when accessing the 
Database server though PHP (4.3.9) compiled against the MySQL 4.1.7 client library, once 
it occurs on a particular query the problem result seems to be cached by the server as per 
the situation when using the command line client. 
 
It does not seem to occur with PHP (4.3.8) when compiled against the bundled MySQL 
client library, which seems to be version 3.23.49
[26 Nov 2004 22:08] Alex Villacis
Apparently this bug was previously reported for 4.1.2-alpha-standard (Bug #4011)

I am being troubled by this exact same bug (column name corruption). No, it has nothing to do with utf8-enabled consoles. I made some tests with a CPAN Perl package called Net-MySQL-0.08 which reimplements the 3.x MySQL protocol in pure Perl. When
debugging is on for this package, the hexdump shows that the "corrupt header" is 
actually the result of a radical change in the layout of the binary packet layout sent for the recordset from the 4.1.7 server (for the exact same query). This occurs if at least one client which uses the 3.x protocol issues queries against a 4.1.7 server. If all the clients for a 4.1.7 server use the libmysqlclient.so.14 library, or use the protocol as implemented by said library, everything goes fine. If, however, at least one client uses the libmysqlclient.so.10/libmysqlclient.so.12 protocol, it seems that the 4.1.7 server gets confused as to which protocol should speak to which client: in my case, my compiled PHP (libmysqlclient.so.14) started receiving incorrect column names after some queries were sent by another machine with a mysql command client from 4.0.20. I have also observed the Net-MySQL hanging because it cannot recognize that it has reached the
end of the recordset, and keeps waiting for more data. This same layout change results
in the corrupt column names in the standard mysql clients.

As one can deduce from the previous explanation, one workaround is to ensure that only
clients with libmysqlclient.so.14 ever get to connect to the 4.1.7 server. However, this
may bite anyone who downloads and installs MySQL-shared-compat and attempts to
connect to 4.1.7 without recompiling packages (for Fedora2, this affects the MySQL-python, perl-DBD-mysql, and the mysql package for PHP). I worked around this
on my system by recompiling the MySQL support for Perl and PHP.

I am not a MySQL developer, but if I had to give a name for this bug, it would be "mixup of protocol versions sent to concurrent clients with different protocol versions". 
Hopefully this explanation might help a MySQL developer to pinpoint this bug.
[27 Nov 2004 11:29] Konstantin Osipov
Is the bug repeatable if the query cache is switched off?
My main suspicion is the query cache, could you verify that mangled metadata
goes from the query cache (check query_cache_hist status variable before and after the query).
[28 Nov 2004 1:36] Travers Carter
Did some more testing based on the additional information and found the following: 
 
1) Turning off the query cache makes the problem go away 
 
2) When the problem occurs the Qcache_hits variable being incremented on an otherwise 
idle server (was query_cache_hist a typo, or is there something else I should check too?) 
 
3) Based on the additional information that's been posted I can now replicate the problem 
reliably, it occurs when a client of a particular version issues a query that would be answered 
from the cache, where the cache was populated by that query from a different client version. 
 
If the query is first issued from a 4.1.7 client, I get the following results: 
3.23.49 (PHP): Error 2000 
4.0.20 (CLI): "ERROR 2027 Malformed Packet" 
4.1.7 (CLI) : No problems 
 
If the query is first issued from a 3.23.49 or 4.0.20 client, I get the following results: 
3.23.49 (PHP): No problems 
4.0.20 (CLI): No problems 
4.1.7 (CLI): Column name corruption as described in the original report 
 
I'm not sure if it's significant, but the 4.1.7 & 3.23.49 clients are on the same server as 
the database and connecting via unix sockets, but the 4.0.20 client is on a remote machine 
connecting via TCP.
[16 Dec 2004 10:13] Mark Overmeer
Maybe related, maybe not.  I recently started running 4.1.7 as well (SuSE9.0), and since then experience comparible problems with "Malformed packages".

In my case, the complaints start when I have rebuilt a large table with high speed.  It seems that the query cache gets corrupted.  It also happens when I run my system for about two weeks on normal pace: after a while the errors come in.  I have a large cache.  A "RESET QUERY CACHE" does help!

My guess is that the garbage collection of the cache is not working correctly under some pressure.
mark@overmeer.net
[27 Dec 2004 0:31] Travers Carter
Looking at the code it seems that the problem is caused by the cache not using the CLIENT_PROTOCOL_41 flag in thd->client_capabilities as part of the cache entry key, the following patch (against 4.1.8) seems to fix the problem for me.

diff -Narub mysql-4.1.8-pristine/sql/mysql_priv.h mysql-4.1.8/sql/mysql_priv.h
--- mysql-4.1.8-pristine/sql/mysql_priv.h       2004-12-14 23:40:37.000000000 +1100
+++ mysql-4.1.8/sql/mysql_priv.h        2004-12-26 13:42:39.000000000 +1100
@@ -377,6 +377,7 @@
 struct Query_cache_query_flags
 {
   unsigned int client_long_flag:1;
+  unsigned int client_protocol_41;
   uint character_set_client_num;
   uint character_set_results_num;
   uint collation_connection_num;
diff -Narub mysql-4.1.8-pristine/sql/sql_cache.cc mysql-4.1.8/sql/sql_cache.cc
--- mysql-4.1.8-pristine/sql/sql_cache.cc       2004-12-14 23:40:39.000000000 +1100
+++ mysql-4.1.8/sql/sql_cache.cc        2004-12-26 13:44:06.000000000 +1100
@@ -774,6 +774,7 @@
     Query_cache_query_flags flags;
     // fill all gaps between fields with 0 to get repeatable key
     bzero(&flags, QUERY_CACHE_FLAGS_SIZE);
+    flags.client_protocol_41= (thd->client_capabilities & CLIENT_PROTOCOL_41 ? 1 : 0);
     flags.client_long_flag= (thd->client_capabilities & CLIENT_LONG_FLAG ?
                             1 : 0);
     flags.character_set_client_num=
@@ -966,6 +967,7 @@

   // fill all gaps between fields with 0 to get repeatable key
   bzero(&flags, QUERY_CACHE_FLAGS_SIZE);
+  flags.client_protocol_41= (thd->client_capabilities & CLIENT_PROTOCOL_41 ? 1 : 0);
   flags.client_long_flag= (thd->client_capabilities & CLIENT_LONG_FLAG ?
                           1 : 0);
   flags.character_set_client_num= thd->variables.character_set_client->number;
[1 Feb 2005 1:49] Jim Winstead
Pushed to 4.1.
[16 Feb 2005 19:21] Paul Dubois
Mentioned in 4.1.10 change notes.