8123 bytes is enough for everyone
Today, during the transfer of a single site from MyISAM tables to InnoDB, one of the most interesting features emerged from the latter. A request to change the engine for two tables returned a strange error “Got error 139 from storage engine”. After searching for information on this topic, it was found that this error occurs when a row of the table does not fit into half the page of memory with which MySQL works. These pages are 16 Kb, and half, therefore, 8 Kb.
In itself, the restriction is rather strange, but at first glance it seems difficult to achieve, because as you know, MySQL stores text data in a storage that is separate from table rows. It turned out that this is only half true. In fact, InnoDB stores only “surplus” in a separate repository, to which it does not place the first 768 bytes of each text field. Those. any text will devour from the length of the line as many bytes as it contains, but not more than 768. It is easy to calculate that the maximum number of text fields from 768 bytes in length that can be safely stored in one table is 10. Indeed, if you run the example , all runs smoothly. But it is necessary to increase the number of fields by at least one, and we get the same error as in the beginning.
What is most striking is not the absurdity of the restriction, or even the “gluttony” of string data types, but the silence of this problem. InnoDB makes it easy to create tables with hundreds of text fields. At the same time, you will learn that you cannot use them only on production when filling in the table with real data. An unintelligible error message also leaves few positive emotions.
')
To fight the disease in two ways:
Recompiling with increasing UNIV_PAGE_SIZE ( continued ).
Connect InnoDB via a plugin that supports the barracuda file format and change the ROW_FORMAT tables to DYNAMIC. Or just use ROW_FORMAT = DYNAMIC, if you already have MySQL 5.5.
In itself, the restriction is rather strange, but at first glance it seems difficult to achieve, because as you know, MySQL stores text data in a storage that is separate from table rows. It turned out that this is only half true. In fact, InnoDB stores only “surplus” in a separate repository, to which it does not place the first 768 bytes of each text field. Those. any text will devour from the length of the line as many bytes as it contains, but not more than 768. It is easy to calculate that the maximum number of text fields from 768 bytes in length that can be safely stored in one table is 10. Indeed, if you run the example , all runs smoothly. But it is necessary to increase the number of fields by at least one, and we get the same error as in the beginning.
What is most striking is not the absurdity of the restriction, or even the “gluttony” of string data types, but the silence of this problem. InnoDB makes it easy to create tables with hundreds of text fields. At the same time, you will learn that you cannot use them only on production when filling in the table with real data. An unintelligible error message also leaves few positive emotions.
')
To fight the disease in two ways:
Recompiling with increasing UNIV_PAGE_SIZE ( continued ).
Connect InnoDB via a plugin that supports the barracuda file format and change the ROW_FORMAT tables to DYNAMIC. Or just use ROW_FORMAT = DYNAMIC, if you already have MySQL 5.5.
Source: https://habr.com/ru/post/126375/
All Articles