Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to solve the problem of ERROR 1799 error report in MySQL". In the daily operation, I believe many people have doubts about how to solve the problem of ERROR 1799 error report in MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt of "how to solve the problem of ERROR 1799 error report in MySQL". Next, please follow the editor to study!

[problem description]:

Version: MySQL 5.6.36 (all the above versions may appear)

Behavior: Online DDL some large tables that are accessed frequently

Phenomenon:

ERROR 1799 (HY000): Creating index 'GEN_CLUST_INDEX' required more than' innodb_online_alter_log_max_size' bytes of modification log. Please try again.

For example:

Mysql > ALTER TABLE an ENGINE=innodb

ERROR 1799 (HY000): Creating index 'GEN_CLUST_INDEX' required more than' innodb_online_alter_log_max_size' bytes of modification log. Please try again.

Mysql > ALTER TABLE a DROP idx_name

ERROR 1799 (HY000): Creating index 'idx' required more than' innodb_online_alter_log_max_size' bytes of modification log. Please try again.

As long as it is related to DDL, you may encounter such problems, such as in optimize table:

Mysql > OPTIMIZE TABLE a

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | test.a | optimize | note | Table does not support optimize, doing recreate + analyze instead | |

| test.a | optimize | error | Creating index 'FTS_DOC_ID_INDEX' required more than' innodb_online_alter_log_max_size' bytes of modification log. Please try again. | |

| | test.a | optimize | status | OK | |

+-+

2 rows in set (18.13 sec)

Although optimize is of no use to the innodb table, it still throws the error

So if you take a look at the manual, you can see:

Innodb_online_alter_log_max_size controls the upper limit size of a temporary log file used during Online DDL operations.

This temporary log file stores records of dml operations during DDL time. This temporary log file is extended according to the value of innodb_sort_buffer_size.

If the log exceeds the maximum innodb_online_alter_log_max_size limit, the DDL operation throws a failure and rolls back all uncommitted DML operations.

Conversely, setting this value higher allows more DML operations to occur when Online DDL is done.

But the problem is that after DDL is done, it takes more time to lock the table and apply these logs.

And for some DDL operations, such as

ADD INDEX/COLUMN, you can speed up the operation by resizing the innodb_sort_buffer_size.

But the memory allocated is actually three times the innodb_sort_buffer_ size value.

Innodb_online_alter_log_max_size and innodb_sort_buffer_size are both new parameters of 5.6Online DDL.

[solution]:

It can be solved by knowing what this parameter controls.

Temporarily increase this value, and here change it to 256MB:

Mysql > SET GLOBAL innodb_online_alter_log_max_size=256*1024*1024

Query OK, 0 rows affected (0.03 sec)

This value defaults to 128MB, but it is recommended that you change it to the default value, which is 134217728, after you finish DDL.

At this point, the study on "how to solve the problem of ERROR 1799 error reporting in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.