| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.7 | OS: | Linux (Linux x86) |
| Assigned to: | Jim Winstead | CPU Architecture: | Any |
[9 Nov 2004 0:32]
Travers Carter
[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.
