[SMW-devel] Problem with SMW and MySQL 5.1.49..52

[Patrick N. <ma...@pa...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I first encountered this in October 2010, when I switched to MySQL 5.1.50 on
a testing system (while the production system is still running MySQL
5.0.91). The simple query

{{#ask: [[Category:Freelancers]] [[Joe Smith]] }}

suddenly returned nothing on the testing system, although page "Joe Smith"
is in the Freelancers category.

After lots of digging through all the layers, ruling out one component after
another, I can now say with confidence, that the problem is in MySQL server
versions 5.1.49, 5.1.50, 5.1.51 and 5.1.52. I have been told that it's
caused by a bug in the 'index_merge' behaviour, which is present in these
versions.

For details, have a look at the testcase I built and documented at [1]. A
simple SQL query like "SELECT * FROM smw_inst2 WHERE smw_inst2.s_id=9877 AND
smw_inst2.o_id=650;" exposes the bug.

I have been told that SMW could work around that index_merge bug by changing
the separate indexes to a compound index. I tried it for the smw_inst2
table, and SMW then gives the expected result in the exact query above.
There are other non-compound indexes in various SMW database tables though,
and I don't know enough about the exact problems caused by the index_merge
bug to be sure that transforming the indexes of just the smw_inst2 table
into a compound index will guarantee that no other queries will return
incorrect results. It would be great if someone with deeper knowledge of
MySQL and indexes could clarify.

For now, I recommend to avoid these MySQL server versions.

Patrick.

[1] http://p173.de/gp/index.php?id=7e1bee97ab&view=nl

- -- 
Key ID: 0x86E346D4            http://patrick-nagel.net/key.asc
Fingerprint: 7745 E1BE FA8B FBAD 76AB 2BFC C981 E686 86E3 46D4
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.17 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk2fHs4ACgkQyYHmhobjRtQunQCg1kzYcm4kApGJqSFN4FCoWAFr
asIAnRmNgJAayERjkI36a56TM1edOe0T
=STpH
-----END PGP SIGNATURE-----