insufficient history for index

Question

In MySQL log, there is one error 'InnoDB: insufficient history for index 0'

I don't know why it occurs. I have googled and found this:

InnoDB: If a transaction was started with a consistent snapshot, then new indexes were added to the table while the transaction was in progress, a subsequent UPDATE statement could incorrectly encounter the error:

ER_TABLE_DEF_CHANGED: insufficient history for index
This issue could cause an assertion error in debug builds. (Bug #14036214)

In my case, I started the transaction and inserted data into table, in between I did not insert or update data in that table. Why did this error occur?

Was it a tmp table? " When more than one InnoDB temporary table was created and accessed within the same transaction, queries on those temporary tables could fail with an ER_TABLE_DEF_CHANGED error. (Bug #14234581)" — Rick James, Feb 05 '16 at 01:29
@RickJames Thanks for your response. I am using 5.6.28-0ubuntu0.14.04.1 version. I have written many procedure between a procedure. this procedure is parent procedure and in procedures insert/update/delete queries are run. — Vipin Jain, Feb 05 '16 at 04:59
@RickJames i checked in my procedure there is no any temp table created. — Vipin Jain, Feb 05 '16 at 06:36
@RickJames I checked in my all procedures(parent and child) there is no any temp table exist. — Vipin Jain, Feb 05 '16 at 07:27
this error occured in production. same error not generated in testing db. i dont understand what happening. — Vipin Jain, Feb 06 '16 at 17:18
@RyanVincent i tried in small batches of data. but i didnt get error. and i didnt try to different version. i am using 5.6.28-0ubuntu0.14.04.1 version of mysql. i will check in different version. — Vipin Jain, Feb 06 '16 at 17:27
Ok, if production works with smaller batches of data then run with that for now. That way your users will get their data. — Ryan Vincent, Feb 06 '16 at 17:30
It may be worthwhile talking to the supplier of the database support people as they can give you instructions on what to look at and maybe add suggestions for what to record so you and they can find the error. They will be interested in fixing this as well? I have no more suggestions to add. ;-/ — Ryan Vincent, Feb 06 '16 at 17:33
The symptom "working in small batches" suggests a different execution plan is being used which perhaps avoids a table spool. — grahamj42, Feb 06 '16 at 18:16

score 2 · Answer 1 · answered Feb 15 '16 at 18:03

The bug that you are pointing is only for debugging compiled versions:

storage/innobase/handler/ha_innodb.cc

        if (!m_prebuilt->index_usable) {
            if (dict_index_is_corrupted(m_prebuilt->index)) {
                    // Code removed for clarity
                    }
            } else {
                    push_warning_printf(
                            m_user_thd, Sql_condition::SL_WARNING,
                            HA_ERR_TABLE_DEF_CHANGED,
                            "InnoDB: insufficient history for index %u",
                            keynr);
            }

            /* The caller seems to ignore this.  Thus, we must check
            this again in row_search_for_mysql(). */
            DBUG_RETURN(HA_ERR_TABLE_DEF_CHANGED);
    }

The error happens when the index is being created and the table structure changes, it means that the index is not usable. Please check the errors provided in you mysqld.err file as there could be more errors that can help to determine in which phase it is placed.

If your case is a MySQL compiled with debug symbols, you will probably take note of the bug reported on your comment. Otherwise I will suggest to review if any other transaction happen to change the table structure related to the index during a transaction (did you remove an index during a long transaction? Did the catalog got corrupted during the operation?).

there is no any table that is alter between running procedure. — Vipin Jain, Feb 16 '16 at 09:21
Are you able to reproduce the error? Can you share the steps? @VipinJain — 3manuek, Feb 17 '16 at 17:48
I am not able to produce in testing enviorment its come in production. same data process in production. — Vipin Jain, Feb 18 '16 at 06:11
@VipinJain having the same data is not the same. Have in mind that this error comes within a load occurred on the master. If you have access to the logs, please share the errors above and bellow the "Insufficient ..." line. — 3manuek, Feb 19 '16 at 20:01

insufficient history for index

1 Answers1