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?

Answer 1

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?).