mysql - ALTER IGNORE ADD INDEX removed in mysql 5.7.4 - What to use instead?

Question

I am trying to add an index to a table, but only if one doesn't exist. I don't know what the index name is. In the mysql manual I found the alter ignore command:

ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]

alter_specification:
table_options
...
| ADD {INDEX|KEY} [index_name]
    [index_type] (index_col_name,...) [index_option] ...

So, I tried to do the following:

ALTER IGNORE TABLE payments ADD INDEX (id_project);

However, this fails, with the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE payments ADD INDEX (id_project)' 

Turns out, this has been removed as of mysql 5.7.4.

What's a good way to do this in newer versions of mysql?

Answer 1

FYI...

Changelog 2014-03-27 5.6.17 -- Functionality Added or Changed --

The IGNORE clause for ALTER TABLE ( http://dev.mysql.com/doc/refman/5.6/en/alter-table.html ) is now deprecated and will be removed in a future version of MySQL. ALTER IGNORE TABLE causes problems for replication, prevents online ALTER TABLE for unique index creation, and causes problems with foreign keys (rows removed in the parent table).

Answer 2

This may help:

1. Rename the existing table and create a new table.
2. Use insert ignore to copy data from old table to new table.

Answer 3

I wrote a stored procedure to create indexes and check if one already exists by name.

The code for it is in my answer to MySQL: Create index If not exists I wrote Sep 19, 2012

I tested that code in that old post. It works.

The Stored Procedure checks for the index name only, not the column list combination (See the bottom of this post for what to do if you have such a scenario).

Here is that code from that post

use mydb
DELIMITER $$

DROP PROCEDURE IF EXISTS `CreateIndex` $$
CREATE PROCEDURE `CreateIndex`
(
    given_database VARCHAR(64),
    given_table    VARCHAR(64),
    given_index    VARCHAR(64),
    given_columns  VARCHAR(64)
)
BEGIN

    DECLARE IndexIsThere INTEGER;

    SELECT COUNT(1) INTO IndexIsThere
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE table_schema = given_database
    AND   table_name   = given_table
    AND   index_name   = given_index;

    IF IndexIsThere = 0 THEN
        SET @sqlstmt = CONCAT('CREATE INDEX ',given_index,' ON ',
        given_database,'.',given_table,' (',given_columns,')');
        PREPARE st FROM @sqlstmt;
        EXECUTE st;
        DEALLOCATE PREPARE st;
    ELSE
        SELECT CONCAT('Index ',given_index,' already exists on Table ',
        given_database,'.',given_table) CreateindexErrorMessage;   
    END IF;

END $$

DELIMITER ;

REGARDING DUPLICATE INDEXES

Now, with the command you used

ALTER IGNORE TABLE payments ADD INDEX (id_project);

you could change it to

ALTER TABLE payments ADD INDEX id_project_ndx (id_project);

Of course, it errors out if the index name id_project_ndx already exists.

If you run the following

ALTER TABLE payments ADD INDEX (id_project);

it will create an index called id_project if one does not exist. If an index called id_project already exists, mysqld will create another index (probably id_project_2 or something like it), but as I indicated in the old post under Create the Index Anyway, it will produce a duplicate index (In this context, a duplicate index is an index that has a different name but has the identical list of columns from another index on the same table). If that happens or if you are not sure duplicate indexes exist, you could download pt-duplicate-key-checker and run it so that it recommends what indexes can be removed and still maintain the same searchability.

By using the stored procedure, you are forced to name the index and columns as follows:

use mydb
call CreateIndex(DATABASE(),'payments','id_project_ndx','id_project'); 

Give it a Try !!!