MySQL Bugs: #77147: Server doesn't raise "Duplicate index" error even in strict sql

Bug #77147	Server doesn't raise "Duplicate index" error even in strict sql_mode
Submitted:	25 May 2015 10:22	Modified:	25 Aug 2015 5:07
Reporter:	Tsubasa Tanaka (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.7.7	OS:	Linux (CentOS 6.5)
Assigned to:		CPU Architecture:	Any
Tags:	Docs, relase note, relnote

Description:
MySQL 5.7.0-m10 release note says,

```
The server now issues a warning if an index is created that duplicates an existing index, or an error in strict SQL mode. (Bug #37520, Bug #11748842)
```

Actually, MySQL 5.7.7 returns only warning even in strict sql_mode.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (num int);
ALTER TABLE t1 ADD KEY (num);
SET sql_mode= '';
ALTER TABLE t1 ADD KEY (num); -- Raise warning, ok.
SET sql_mode= 'STRICT_TRANS_TABLES';
ALTER TABLE t1 ADD KEY (num); -- Raise warning, not error even in strict sql_mode.

Release Note is here.
MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.0 (Not released, Milestone 10)
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-0.html

Suggested fix:
Fix release note, or add condition if sql_mode is strict or not before calling push_warning_printf.

https://github.com/mysql/mysql-server/blob/5.7/sql/sql_table.cc#L3359-L3372

Thank you for the bug report.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.8-rc Source distribution GIT PUL: 2015/04/13

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > USE sd
Database changed
mysql 5.7 > DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > CREATE TABLE t1 (num int);
Query OK, 0 rows affected (0.19 sec)

mysql 5.7 > ALTER TABLE t1 ADD KEY (num);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.7 > SET sql_mode= '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > ALTER TABLE t1 ADD KEY (num); -- Raise warning, ok.
Query OK, 0 rows affected, 1 warning (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql 5.7 > SET sql_mode= 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > ALTER TABLE t1 ADD KEY (num); -- Raise warning, not error even in strict sql_mode.
Query OK, 0 rows affected, 1 warning (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql 5.7 > SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                              |
+---------+------+----------------------------------------------------------------------------------------------------------------------+
| Warning | 1831 | Duplicate index 'num_3' defined on the table 'sd.t1'. This is deprecated and will be disallowed in a future release. |
+---------+------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.7 > SELECT @@SESSION.SQL_MODE;
+---------------------+
| @@SESSION.SQL_MODE  |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

Posted by the developer:

[21 Aug 2015 3:27] Abhishek Ar Ranjan
Mysql 5.7.5 release note says :

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-5.html

" In strict SQL mode, deprecation warnings about duplicate indexes were
incorrectly promoted to errors."
This means warnings are warnings and should not be promoted to errors.

Closing bug.