subselect bug in amd64 compiled version

[Patrik Wallström]

Binary package hint: mysql-server-5.0

I just got hit by this bug using the amd64 version of mysql-server-5.0 in Dapper. The bug fits the description in this upstream bug report:
http://bugs.mysql.com/bug.php?id=24056

The bug is not present in the x86 version in the same package.

[Mathias Gug (mathiaz)]

Thanks taking the time to report this bug and helping to make Ubuntu better. However, I am closing it because the bug has been fixed in the latest development version of Ubuntu - the Gutsy Gibbon.

[Mathias Gug (mathiaz)]

Confirmed by upstream. Fixed in 5.0.32.

[Martin Pitt (pitti)]

The patch looks sane: http://lists.mysql.com/commits/15001

This looks quite serious to me, and we should consider it for the dapper point release.

[Mathias Gug (mathiaz)]

I've looked at the patch. However, it doesn't apply as the code related to the subquery optimization is not in dapper.

Can you confirm that the testcase described in the upstream bug report (subselect.test) fails on dapper 64 bits ?

[Patrik Wallström (pawal)]

On Tue, 24 Jul 2007, Mathias Gug wrote:

> I've looked at the patch. However, it doesn't apply as the code related
> to the subquery optimization is not in dapper.
>
> Can you confirm that the testcase described in the upstream bug report
> (subselect.test) fails on dapper 64 bits ?

I don't really understand what you mean. I used the MySQL from Dapper,
works fine in x86 and fails on amd64. Don't know about the actual code
or patch, but the patch is very likely referring to the exact problem
I had. (I rewrote my own code since so I won't be affected.)

--
patrik_wallstrom->foodfight-><email address hidden>->[phone]

[Mathias Gug (mathiaz)]

On Tue, Jul 24, 2007 at 07:41:48PM -0000, Patrik Wallström wrote:
> I don't really understand what you mean. I used the MySQL from Dapper,
> works fine in x86 and fails on amd64.
Can you provide a specific test case ?

[Patrik Wallström (pawal)]

On Tue, 24 Jul 2007, Mathias Gug wrote:

> On Tue, Jul 24, 2007 at 07:41:48PM -0000, Patrik Wallström wrote:
> > I don't really understand what you mean. I used the MySQL from Dapper,
> > works fine in x86 and fails on amd64.
> Can you provide a specific test case ?

Here is the diff that fixed our problem:

 SELECT DOMAINS.*
 FROM DOMAINS
-WHERE DATE_ADD(REGDATE, INTERVAL 5 DAY) < NOW()
-AND '".$welcome_desc."' NOT IN (
-SELECT VALUE FROM DOMAIN_LOG
-WHERE DOMAIN_LOG.DOMAIN_ID=DOMAINS.DOMAIN_ID AND
-LOGDATE > DOMAINS.REGDATE
-)
+LEFT JOIN DOMAIN_LOG
+ ON (DOMAIN_LOG.DOMAIN_ID=DOMAINS.DOMAIN_ID AND
+ DOMAIN_LOG.VALUE='".$welcome_desc." AND
+ LOGDATE > DOMAINS.REGDATE')
+WHERE
+DATE_ADD(REGDATE, INTERVAL 5 DAY) < NOW() AND
+DOMAIN_LOG.VALUE IS NULL
 AND STATE = ".DOMAIN_ACTIVE."

It would take a lot of effort to strip out the tables and generate
test data for you, but let me know if you really need it. I may not be
able to reach my people right now since everybody is on vacation in
Sweden. But you might be able figure out the relevant schema by
looking at the above SQL statements.

--
patrik_wallstrom->foodfight-><email address hidden>->[phone]

[Mathias Gug (mathiaz)]

Thanks for your answer.

I've tried to reproduce a test case, but I haven't succeeded. I'm missing the data under which the crash occurs. I've attached test.sql that is a stub for a testcase. Can you have a look at it and come up with the right INSERT commands to get the server crash ?

Also, the mysql bug you've mentioned depends on code that has been introduced in release 5.0.32 of mysql. The version of mysql in dapper is based on 5.0.22. So I'm not sure that you've hit the same bug. Could it be bug 8804 (http://bugs.mysql.com/bug.php?id=8804) ?

Thank you,

[Martin Pitt (pitti)]

Needs more investigation, let's drop that for dapper.2.

[Jamie Strandboge (jdstrand)]

In investigating another issue, I came across this bug. The patch for http://lists.mysql.com/commits/15001 is dependent upon the patches for http://bugs.mysql.com/bug.php?id=8804. 8804 does not apply cleanly and probably requires at least one additional patch that needs to be identified. Based on the comments in 8804, the patch for http://bugs.mysql.com/bug.php?id=23485 is likely also desirable.

I am not actively working on this bug and am simply passing along what I found in my research.

[JC Hulce (soaringsky)]

Thank you for taking the time to report this bug and helping to make Ubuntu better. The version of Ubuntu you're reporting this issue on is in End of Life status, and newer versions have fixed this issue. You can learn more about this at https://wiki.ubuntu.com/Releases