MySQL Bugs: #57795: Simple queries return wrong result after update from 5.1.41 to 5.1.49 and 5.1.51

Bug #57795	Simple queries return wrong result after update from 5.1.41 to 5.1.49 and 5.1.51
Submitted:	28 Oct 2010 9:20	Modified:	1 Nov 2010 9:09
Reporter:	Martin Thorsen Ranang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1.49, 5.1.51	OS:	Linux (Ubuntu 10.10 Maverick Meerkat)
Assigned to:		CPU Architecture:	Any
Tags:	query inconsistency, regression

Description:
When upgrading from Ubuntu 10.04 Lucid Lynx to Ubuntu 10.10 Maverick Meerkat, the MySQL server packages got upgraded from version 5.1.41-3ubuntu12.3 to 5.1.49-1ubuntu8.

Soon after some of my applications that interact with MySQL broke severely.  After some digging I was able to write a script that demonstrates the observed error.  I will attach the script once I have submitted this form.

When run against MySQL server version 5.1.41-3ubuntu12.3, the output of the script is:

$ mysql -u mysql_demo -p mysql_demo < pure_sql.sql
NOTE 1:
This works in all tested versions; 5.1.41-3ubuntu12.3, but not in 5.1.49-1ubuntu8 and 5.1.51:

EXPLAIN
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	activity	const	PRIMARY	PRIMARY	4	const	1	
1	SIMPLE	activity_participant_1	ref	activity_id,user_id	activity_id	5	const	1	Using where

RESULT
activity_activity_id	activity_name
2	writing #1

NOTE 2:
This works in version 5.1.41-3ubuntu12.3, but does not work in versions 5.1.49-1ubuntu8 and 5.1.51 (from source):

EXPLAIN
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	activity	const	PRIMARY	PRIMARY	4	const	1	
1	SIMPLE	activity_participant_1	index_merge	activity_id,user_id	activity_id,user_id	5,5	NULL	1	Using intersect(activity_id,user_id); Using where; Using index

RESULT
activity_activity_id	activity_name
2	writing #1

While, under versions 5.1.49-1ubuntu8 and 5.1.51, compiled from source that was downloaded from mysql.com, the same script outputs:

$ mysql -u mysql_demo -p mysql_demo < pure_sql.sql
NOTE 1:
This works in all tested versions; 5.1.41-3ubuntu12.3, but not in 5.1.49-1ubuntu8 and 5.1.51:

EXPLAIN
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	activity	const	PRIMARY	PRIMARY	4	const	1	
1	SIMPLE	activity_participant_1	ref	activity_id,user_id	activity_id	5	const	1	Using where

RESULT
activity_activity_id	activity_name
2	writing #1

NOTE 2:
This works in version 5.1.41-3ubuntu12.3, but does not work in versions 5.1.49. and 5.1.51 (from source):

EXPLAIN
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	activity	const	PRIMARY	PRIMARY	4	const	1	
1	SIMPLE	activity_participant_1	index_merge	activity_id,user_id	activity_id,user_id	5,5	NULL	1	Using intersect(activity_id,user_id); Using where; Using index

RESULT

That is, the last result set is empty, which clearly is wrong.

I have checked my /var/log/mysql/error.log, but it seems OK:

101028 10:16:58 [Note] Plugin 'FEDERATED' is disabled.
101028 10:16:58  InnoDB: Started; log sequence number 0 260173432
101028 10:16:58 [Note] Event Scheduler: Loaded 0 events
101028 10:16:58 [Note] /opt/mysql-5.1.51/libexec/mysqld: ready for connections.
Version: '5.1.51'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Source distribution

How to repeat:
The results should be possible to repeat by running my attached script against the different versions of the MySQL server.

Suggested fix:
I got a tip that this maybe was the same error as reported in ticket http://bugs.mysql.com/bug.php?id=56862 and I even tried the patch mentioned there against 5.1.51, but the results were the same as with the unpatched 5.1.51 server.

Unfortunately I do not have a real fix for this, only a workaround that might help other Ubuntu users.  If you experience this error, then until it is fixed, you may downgrade your MySQL server packages.  You can do so by first uninstalling the newer pacakges:

  $ sudo apt-get remove mysql-server-5.1 mysql-server-core-5.1

Then, add a file like /etc/apt/sources.list.d/mysql_downgraded.list containing the text:

  deb http://security.ubuntu.com/ubuntu lucid-security main 

Then, add the file /etc/apt/preferences.d/mysql containig the text:

  Package: mysql-server-5.1
  Pin: release a=lucid-security
  Pin-Priority: 500
  Package: mysql-server-core-5.1
  Pin: release a=lucid-security
  Pin-Priority: 500

After that you may install and use the packages from the Lucid Lynx release:

  $ sudo apt-get update
  $ sudo apt-get install mysql-server-5.1/lucid mysql-server-core-5.1/lucid

I hope this might help others experiencing the same problems.

Simple script to repeat results.

Attachment: pure_sql.sql (text/x-sql), 3.39 KiB.

Verified just as described:

...

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.1.51-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT activity.activity_id AS activity_activity_id,
    ->        activity.name AS activity_name
    -> FROM activity, activity_participant AS activity_participant_1
    -> WHERE activity.activity_id = 2
    ->       AND activity.activity_id = activity_participant_1.activity_id
    ->       AND 1 = activity_participant_1.user_id;
Empty set (0.00 sec)

mysql> SELECT activity.activity_id AS activity_activity_id,
    ->        activity.name AS activity_name
    -> FROM activity, activity_participant AS activity_participant_1 FORCE INDEX
(activity_id)
    -> WHERE activity.activity_id = 2
    ->       AND activity.activity_id = activity_participant_1.activity_id
    ->       AND 1 = activity_participant_1.user_id;
+----------------------+---------------+
| activity_activity_id | activity_name |
+----------------------+---------------+
|                    2 | writing #1    |
+----------------------+---------------+
1 row in set (0.00 sec)

Hi, I just forgot to supply some basic system information:

$ uname -a
Linux mtr-laptop 2.6.35-22-generic #35-Ubuntu SMP Sat Oct 16 20:36:48 UTC 2010 i686 GNU/Linux

$ dpkg --status libc6 libgcc1 libstdc++6 |grep 'Package:\|Version:'
Package: libc6
Version: 2.12.1-0ubuntu8
Package: libgcc1
Version: 1:4.5.1-7ubuntu2
Package: libstdc++6
Version: 4.5.1-7ubuntu2

$ sudo lshw -sanitize |head -104
computer                  
    description: Notebook
    product: 17068GG
    vendor: LENOVO
    version: ThinkPad X60
    serial: [REMOVED]
    width: 32 bits
    capabilities: smbios-2.4 dmi-2.4 smp-1.4 smp
    configuration: administrator_password=disabled boot=normal chassis=notebook cpus=2 frontpanel_password=unknown keyboard_password=disabled power-on_password=disabled uuid=5FD0BA01-4896-11CB-8041-AAC48D558B25
  *-core
       description: Motherboard
       product: 17068GG
       vendor: LENOVO
       physical id: 0
       version: Not Available
       serial: [REMOVED]
     *-firmware
          description: BIOS
          vendor: LENOVO
          physical id: 0
          version: 7BETD6WW (2.17 ) (06/19/2008)
          size: 144KiB
          capacity: 1984KiB
          capabilities: pci pcmcia pnp upgrade shadowing escd cdboot bootselect socketedrom edd acpi usb biosbootspecification
     *-cpu:0
          description: CPU
          product: Genuine Intel(R) CPU           T2400  @ 1.83GHz
          vendor: Intel Corp.
          physical id: 6
          bus info: cpu@0
          version: 6.14.8
          serial: [REMOVED]
          slot: None
          size: 1833MHz
          capacity: 1833MHz
          width: 32 bits
          clock: 167MHz
          capabilities: boot fpu fpu_exception wp vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx constant_tsc arch_perfmon bts aperfmperf pni monitor vmx est tm2 xtpr pdcm cpufreq
          configuration: id=0
        *-cache:0
             description: L1 cache
             physical id: a
             slot: Internal L1 Cache
             size: 64KiB
             capacity: 64KiB
             capabilities: synchronous internal write-back instruction
        *-cache:1
             description: L2 cache
             physical id: c
             slot: Internal L2 Cache
             size: 2MiB
             capacity: 2MiB
             capabilities: burst internal write-back unified
        *-logicalcpu:0
             description: Logical CPU
             physical id: 0.1
             width: 32 bits
             capabilities: logical
        *-logicalcpu:1
             description: Logical CPU
             physical id: 0.2
             width: 32 bits
             capabilities: logical
     *-cache
          description: L1 cache
          physical id: b
          slot: Internal L1 Cache
          size: 64KiB
          capacity: 64KiB
          capabilities: synchronous internal write-back data
     *-memory
          description: System Memory
          physical id: 29
          slot: System board or motherboard
          size: 4GiB
        *-bank:0
             description: SODIMM DDR2 Synchronous
             physical id: 0
             slot: DIMM 1
             size: 2GiB
             width: 64 bits
        *-bank:1
             description: SODIMM DDR2 Synchronous
             physical id: 1
             slot: DIMM 2
             size: 2GiB
             width: 64 bits
     *-cpu:1
          physical id: 1
          bus info: cpu@1
          version: 6.14.8
          serial: [REMOVED]
          size: 1833MHz
          capacity: 1833MHz
          capabilities: vmx ht cpufreq
          configuration: id=0
        *-logicalcpu:0
             description: Logical CPU
             physical id: 0.1
             capabilities: logical
        *-logicalcpu:1
             description: Logical CPU
             physical id: 0.2
             capabilities: logical

The problem fixed in Bug#56423.

The problem is not repeatable in latest 5.1 tree,
it's fixed in Bug#56423.
This bug is closed as duplicate.