Bug #10284 "SHOW INDEX FROM" incorrect results

• Submitted: 30 Apr 2005 21:21
• Modified: 17 Jun 2005 7:42
• Reporter: jeff murphy
• Status: Can't repeat
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• Version: 4.1.11
• OS: Linux (redhat linux 4)
• Assigned to: Martin Skold
• CPU Architecture: Any

[30 Apr 2005 21:21] jeff murphy

Description:
we have a 2 node cluster. each node is both a mysql server and data server. a third machine is the management server.

node1> create table t1 (a varchar(128), b integer, primary key(b)) engine=ndb;
node1> create index t1_idx on t1 (a);
node1> show index from t1;

both indexes are shown.

node2> show index from t1;

only the primary key index is shown. 

node1% ndb_show_tables | grep idx

shows the index.

node2% ndb_show_tables | grep idx

shows the index.

so the index apparently is created on both nodes, even tho "show index from" says it's only available on one.

expected behaviour:

either node> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | b           | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| t1    |          1 | t1_idx   |            1 | a           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)

How to repeat:
create a test table, create an index on any column in the test table (as a separate command - not as part of the create table command) and then issue a "show index from" command on each mysql server.

[1 May 2005 2:45] Jorge del Conde

Tested w/4.1.12 from bk

[17 Jun 2005 7:42] Martin Skold

On 4.1.13:
Node 1:
mysql> create table t1 (a varchar(128), b integer, primary key(b)) engine=ndb;
Query OK, 0 rows affected (0.99 sec)

mysql> create index t1_idx on t1 (a);
Query OK, 0 rows affected (2.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | b           | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| t1    |          1 | t1_idx   |            1 | a           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)

Node2:
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | b           | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| t1    |          1 | t1_idx   |            1 | a           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.04 sec)