Bug #71689 mysqlindexcheck should display unique indexes

• Submitted: 12 Feb 2014 20:46
• Modified: 13 Jan 2015 8:21
• Reporter: Daniël van Eeden (OCA)
• Status: Can't repeat
• Category: MySQL Utilities
• Severity: S3 (Non-critical)
• Version: 1.3.6
• OS: Any
• CPU Architecture: Any

[12 Feb 2014 20:46] Daniël van Eeden

Description:
mysqlindexcheck does not display unique indexes differently than 'regular' indexes. If you copy/paste the create index statements there is no unique constraint.

How to repeat:
mysqlindexcheck -i mydb.t1 (t1 has unique keys)

[13 Feb 2014 5:29] MySQL Verification Team

Hello Daniel,

Thank you for the report.

Thanks,
Umesh

[13 Feb 2014 5:31] MySQL Verification Team

// Create table with UK

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create table t2 like t1;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create table t3 like t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t4 like t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t5 like t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t6 like t1;
Query OK, 0 rows affected (0.01 sec)

// Check o/p reported by mysqlindexcheck

mysqluc> mysqlindexcheck test -vv  --server=root@localhost:5617
# Source on localhost: ... connected.
# Checking indexes...
# Getting indexes for test.cru_stored_path
# Table test.cru_stored_path has no duplicate nor redundant indexes.
#
# Getting indexes for test.t1
#   Table %s does not contain a PRIMARY key.
# Table test.t1 has no duplicate nor redundant indexes.
#
# Getting indexes for test.t2
#   Table %s does not contain a PRIMARY key.
# Table test.t2 has no duplicate nor redundant indexes.
#
# Getting indexes for test.t3
#   Table %s does not contain a PRIMARY key.
# Table test.t3 has no duplicate nor redundant indexes.
#
# Getting indexes for test.t4
#   Table %s does not contain a PRIMARY key.
# Table test.t4 has no duplicate nor redundant indexes.
#
# Getting indexes for test.t5
#   Table %s does not contain a PRIMARY key.
# Table test.t5 has no duplicate nor redundant indexes.
#
# Getting indexes for test.t6
#   Table %s does not contain a PRIMARY key.
# Table test.t6 has no duplicate nor redundant indexes.
#
# ...done.

mysqluc>

[13 Feb 2014 6:57] Daniël van Eeden

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `num1` (`num1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

$ mysqlindexcheck --server=127.0.0.1 -i test.t2 -f sql
# Source on 127.0.0.1: ... connected.
# Showing indexes from test.t2:
#
ALTER TABLE test.t2 ADD PRIMARY KEY (id)
CREATE INDEX num1 ON test.t2 (num1) USING BTREE
#

In the output I would expect num1 to be listed as UNIQUE, but it isn't.