Re: can not use indexes with mySQL Cluster?

[jeff murphy]

im seeing a similar problem with 4.1.11-max. we have a similar setup with NDB and server on the same node (in our case 2 nodes). mgmt is on a separate, third machine.

occasionally we find that queries using indexed columns start returning no results. if we drop the index (or dont use it) then the data is returned. if we re-create the index after dropping it, data is returned.

mysql> select * from results;
+--------------+---------------------+----------+----------+------------+
| macAddress | dt | testType | ID | status |
+--------------+---------------------+----------+----------+------------+
| 112233445566 | 2001-01-01 00:00:00 | nessus | 12219 | pending |
| 112233445566 | 2005-04-01 00:00:00 | snort | 265 | user-fixed |
| aabbccddeef1 | 2005-04-29 09:49:29 | manual | msg:test | pending |
| 112233445566 | 2001-01-01 00:00:00 | manual | msg:dmca | pending |
| 00065b86c88c | 2005-04-10 11:10:31 | nessus | 11819 | pending |
| 000393bc3c78 | 2005-04-18 19:59:26 | nessus | 12219 | user-fixed |
+--------------+---------------------+----------+----------+------------+
6 rows in set (0.05 sec)

mysql> SELECT * from results where macAddress = '000393bc3c78';
Empty set (0.00 sec)


mysql> drop index macAddress on results;

mysql> SELECT * from test1 where macAddress like '000393bc3c78';
+--------------+---------------------+----------+-------+------------+
| macAddress | dt | testType | ID | status |
+--------------+---------------------+----------+-------+------------+
| 000393bc3c78 | 2005-04-18 19:59:26 | nessus | 12219 | user-fixed |
+--------------+---------------------+----------+-------+------------+
1 row in set (0.04 sec)


mysql> create index ma_idx on results (macAddress);

mysql> SELECT * from results where macAddress like '000393bc3c78';
+--------------+---------------------+----------+-------+------------+
| macAddress | dt | testType | ID | status |
+--------------+---------------------+----------+-------+------------+
| 000393bc3c78 | 2005-04-18 19:59:26 | nessus | 12219 | user-fixed |
+--------------+---------------------+----------+-------+------------+
1 row in set (0.00 sec)




i also noticed that the 'create index' didnt appear to take effect on both cluster members - just the one i entered it on. "show index from results" didnt show the "ma_idx" index on the 2nd cluster member.

when i entered "create index.." on the 2nd cluster member, i recvd an error

ERROR 1296 (HY000): Got error 241 'Invalid schema object version' from ndbcluster


and then the index appeared in "show index from results".

the maddening part of this is it doesnt seem to _immediately_ stop working. instead queries will work for a period of a few days and then suddenly stop working. drop index, re-create and they start working again.