August 16, 2011 at 00:458123 bytes are enough for everyone
Today, during the transfer of one site from MyISAM tables to InnoDB, the latter revealed one interesting feature. The request to change the engine for two tables returned the strange error "Got error 139 from storage engine". After searching for information on this topic, it was found that this error occurs when any row of the table does not fit in half the memory page that MySQL works with. These pages are 16 Kb, and half, therefore, 8 Kb.
The restriction in itself is rather strange, but at first glance it seems difficult to achieve, because as you know, MySQL stores text data in a storage separate from table rows. It turned out that this is only half true. In fact, InnoDB stores only “surplus” in a separate storage, to which it does not include the first 768 bytes of each text field. Those. any text will eat away as many bytes from the line length as it contains, but no 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. And really, if you run the example , that's it will go smoothly. But it’s worth increasing the number of fields by at least one, and we will get the same error as in the beginning.
The most striking thing is not the absurdity of the constraint, 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 won’t be able to use them only at production when filling out the table with real data. A slurred error message also leaves few positive emotions.
There are two ways to deal with the disease:
Recompilation with an increase in UNIV_PAGE_SIZE ( continued ).
Connecting InnoDB through a plugin that supports the barracuda file format and changing the ROW_FORMAT of the tables to DYNAMIC. Or just use ROW_FORMAT = DYNAMIC if you already have MySQL 5.5.
The restriction in itself is rather strange, but at first glance it seems difficult to achieve, because as you know, MySQL stores text data in a storage separate from table rows. It turned out that this is only half true. In fact, InnoDB stores only “surplus” in a separate storage, to which it does not include the first 768 bytes of each text field. Those. any text will eat away as many bytes from the line length as it contains, but no 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. And really, if you run the example , that's it will go smoothly. But it’s worth increasing the number of fields by at least one, and we will get the same error as in the beginning.
The most striking thing is not the absurdity of the constraint, 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 won’t be able to use them only at production when filling out the table with real data. A slurred error message also leaves few positive emotions.
There are two ways to deal with the disease:
Recompilation with an increase in UNIV_PAGE_SIZE ( continued ).
Connecting InnoDB through a plugin that supports the barracuda file format and changing the ROW_FORMAT of the tables to DYNAMIC. Or just use ROW_FORMAT = DYNAMIC if you already have MySQL 5.5.