Select on table with two colums cause empty result.

[Marcin Grzymski]

Binary package hint: mysql-server-5.1

Table with two integer columns and separate indexes on each column can cause problems
during select with conditions on both columns.
Selecting with condition on one column works ok but multiple columns in same time
generate empty result.

Creating index on two columns fixes results from select.
Version 5.0 works fine in that case.

Looks like bug has been fixed in 5.1.54 + .
http://bugs.mysql.com/bug.php?id=60231

ProblemType: Bug
DistroRelease: Ubuntu 10.10
Package: mysql-server-5.1 5.1.49-1ubuntu8.1
ProcVersionSignature: Ubuntu 2.6.35-25.44-generic 2.6.35.10
Uname: Linux 2.6.35-25-generic x86_64
NonfreeKernelModules: nvidia
Architecture: amd64
Date: Tue Mar 1 10:42:40 2011
ProcEnviron:
 PATH=(custom, user)
 LANG=en_GB.utf8
 SHELL=/bin/bash
SourcePackage: mysql-5.1

[Clint Byrum (clint-fewbar)]

Marcin, thanks for taking the time to file this bug report and help us make Ubuntu better.

Given that you are the only one reporting this issue, I tried to repeat it using the test case from the mysql bug report.

I was unable to reproduce it, with MySQL 5.1 5.1.54-1ubuntu4 on natty, or 5.1.49-1ubuntu8.1 on maverick.

Marcin, I'm going to close this bug as Invalid. If you have further details that will help us reproduce the issue or further understand how your setup differs, feel free to re-open it as Status "New".

[Marcin Grzymski (m-grzymski)]

W dniu 03.03.2011 11:23, Clint Byrum pisze:
> Marcin, thanks for taking the time to file this bug report and help us
> make Ubuntu better.
>
> Given that you are the only one reporting this issue, I tried to repeat
> it using the test case from the mysql bug report.
>
> I was unable to reproduce it, with MySQL 5.1 5.1.54-1ubuntu4 on natty,
> or 5.1.49-1ubuntu8.1 on maverick.
>
> Marcin, I'm going to close this bug as Invalid. If you have further
> details that will help us reproduce the issue or further understand how
> your setup differs, feel free to re-open it as Status "New".
>
> ** Changed in: mysql-dfsg-5.1 (Ubuntu)
> Status: New => Invalid
>
Thank you for response.

Could you double check if you recreated all steps.
I have checked that on ubuntu 10.10 maverick 32 / 64 bits and debian
squeeze 64 bits with mysql 5.1.49 - all systems have same result.
In 10.10 maverick desktop edition 32 bit with fresh install of mysql (no
config changes) with just one db - 'test'.
Debian one have no changes to config too.

Important bit is:

INSERT INTO `a_b` VALUES (12,4);

select * from a_b where a_id = 11 and b_id = 4;
Empty set (0.00 sec)

and how create index fix it:

create unique index index_a_b_on_both_id on a_b (a_id, b_id);

select * from a_b where a_id = 11 and b_id = 4;
+------+------+
| a_id | b_id |
+------+------+
| 11 | 4 |
+------+------+

Kind Regards
Marcin Grzymski

[James Page (james-page)]

I was able to reproduce on Maverick (mysql-server 5.1.49-1ubuntu8.1) by following the steps in the upstream bug report.

[James Page (james-page)]

Log of issue from clean, up-to-date maverick install

[James Page (james-page)]

Also checked in natty - not impacted by the same issue but this is reflected in the upstream bug report.

[James Page (james-page)]

Thanks for reporting this bug and any supporting documentation. Since this bug has enough information provided for a developer to begin work, I'm going to mark it as confirmed and let them handle it from here. Thanks for taking the time to make Ubuntu better!