Hi,
Most likely the show index from results didn't auto-discover the altered table.
I think it is safe to say that this is a bug and should be reported as such. Then when
attempting to alter the table again it reported an error which is correct since it tries
to alter an old and no longer existing table.
The failed results also appear very much as a bug so please
report this bug as well.
Rgrds Mikael
jeff murphy wrote:
> 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.
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com