Change limit for Mysql Row size too large

Question

How can I change the limit  Row size too large    8126   Changing some columns to TEXT or BLOB or using ROW FORMAT DYNAMIC or ROW FORMAT COMPRESSED may help  In current row format  BLOB prefix of 768 bytes is stored inline   Table   id  int 11  No        name    text    No        date    date    No        time    time    No        schedule    int 11  No        category    int 11  No        top a   varchar 255     No        top b   varchar 255     No        top c   varchar 255     No        top d   varchar 255     No        top e   varchar 255     No        top f   varchar 255     No        top g   varchar 255     No        top h   varchar 255     No        top i   varchar 255     No        top j   varchar 255     No        top title a varchar 255     No        top title b varchar 255     No        top title c varchar 255     No        top title d varchar 255     No        top title e varchar 255     No        top title f varchar 255     No        top title g varchar 255     No        top title h varchar 255     No        top title i varchar 255     No        top title j varchar 255     No        top desc a  text    No        top desc b  text    No        top desc c  text    No        top desc d  text    No        top desc e  text    No        top desc f  text    No        top desc g  text    No        top desc h  text    No        top desc i  text    No        top desc j  text    No        status  int 11  No        admin id    int 11  No

User · Answer

I keep running into this issue when I destroy my Laravel Homestead (Vagrant) box and start fresh.

SSH into the box from the command line homestead ssh
Go to the my.cnf file sudo vi /etc/mysql/my.cnf
Add the below lines to the bottom of the file (below the !includedir)

[mysqld]

innodb_log_file_size=512M

innodb_strict_mode=0
Save the changes to my.cnf, then reload MYSQL sudo service mysql restart

User · Answer

I am using MySQL 5 6 on AWS RDS  I updated following in parameter group   innodb file per table 1 innodb file format   Barracuda   I had to reboot DB instance for parameter group changes to be in effect    Also  ROW FORMAT COMPRESSED was not supported  I used DYNAMIC as below and it worked fine    ALTER TABLE nombre tabla ENGINE InnoDB ROW FORMAT DYNAMIC KEY BLOCK SIZE 8

User · Answer

The question has been asked on serverfault too      You may want to take a look at this article which explains a lot   about MySQL row sizes  It s important to note that even if you use   TEXT or BLOB fields  your row size could still be over 8K  limit for   InnoDB  because it stores the first 768 bytes for each field inline in   the page       The simplest way to fix this is to use the Barracuda file format   with InnoDB   This basically gets rid of the problem altogether by   only storing the 20 byte pointer to the  text data instead of storing   the first 768 bytes      The method that worked for the OP there was    Add the following to the my cnf file under  mysqld  section   innodb file per table 1 innodb file format   Barracuda  ALTER the table to use ROW FORMAT COMPRESSED   ALTER TABLE nombre tabla     ENGINE InnoDB     ROW FORMAT COMPRESSED      KEY BLOCK SIZE 8       There is a possibility that the above still does not resolve your issues  It is a known  and verified  bug with the InnoDB engine  and a temporary fix for now is to fallback to MyISAM engine as temporary storage  So  in your my cnf file   internal tmp disk storage engine MyISAM

User · Answer

I ran into this problem recently and solved it a different way  If you are running MySQL version 5 6 20 there is a known bug in the system  See MySQL docs     Important   Due to Bug  69477  redo log writes for large  externally stored BLOB fields could overwrite the most    recent checkpoint  To address this bug  a patch introduced in MySQL 5 6 20 limits the size of redo    log BLOB writes to 10  of the redo log file size  As a result of this limit  innodb log file size    should be set to a value greater than 10 times the largest BLOB data size found in the rows of your    tables plus the length of other variable length fields  VARCHAR  VARBINARY  and TEXT type fields     In my situation the offending blob table was around 16MB  Thus  the way I solved it was by adding a line to my cnf that ensured I had at least 10x that amount and then some   innodb log file size   256M

User · Answer

The other answers address the question asked   I will address the underlying cause   poor schema design   Do not splay an array across columns   Here you have 3 10 columns that should be turned into 10 rows of 3 columns in a new table  plus id  etc   Your Main table would have only  id  int 11  No        name    text    No        date    date    No        time    time    No        schedule    int 11  No        category    int 11  No        status  int 11  No        admin id    int 11  No    Your extra table  Top  would have  id  int 11  No          -- for joining to Main seq TINYINT UNSIGNED    -- containing 1  10 img   varchar 255     No        title varchar 255     No        desc  text    No     PRIMARY KEY id  seq     -- so you can easily find the 10 top titles   There would be 10  or fewer  or more   rows in Top for each id   This eliminates your original problem  and cleans up the schema    This is not  normalization   as debated in some of the Comments    Do not switch to MyISAM  it is going away  Don t worry about ROW FORMAT   You will need to change your code to do the JOIN and to handle multiple rows instead of multiple columns

User · Answer

If this occures on a SELECT with many columns  the cause can be that mysql is creating a temporary table  If this table is too large to fit in memory  it will use its default temp table format  which is InnoDB  to store it on Disk  In this case the InnoDB size limits apply   You then have 4 options    change the innodb row size limit like stated in another post  which requires reinitialization of the server  change your query to include less columns or avoid causing it to create a temporary table  by i e  removing order by and limit clauses   changing max heap table size to be large so the result fits in memory and does not need to get written to disk  change the default temp table format to MYISAM  this is what i did  Change in my cnf   internal tmp disk storage engine MYISAM    Restart mysql  query works

User · Answer

If you can switch the ENGINE and use MyISAM instead of InnoDB  that should help   ENGINE MyISAM  There are two caveats with MyISAM  arguably more     You can t use transactions  You can t use foreign key constraints

User · Answer

Set followings on your my cnf file and restart mysql server   innodb strict mode 0

User · Answer

The maximum row size for an InnoDB table  which applies to data stored locally within a database page  is slightly less than half a page for 4KB  8KB  16KB  and 32KB  For 16kb pages  default   we can calculate   Slightly less than half a page 8126   Number of bytes to threshold for overflow 767   10 59 fields of 767 bytes maximum   Basically  you could max out a row with    11 varchar fields   767 characters  latin1   1 byte per char  or 11 varchar fields   255 characters  utf-8 on mysql   3 bytes per char      Remember  it will only overflow to an overflow page if the field is   767 bytes  If there are too many fields of 767 bytes  it will bust  passing beyond max row size   Not usual with latin1 but very possible with utf-8 if the developers aren   t careful    For this case  I think you could possibly bump the innodb page size to 32kb   in my cnf   innodb page size 32K   References    https   dev mysql com doc refman 8 0 en column-count-limit html row-size-limits https   medium com  adamhooper in-mysql-never-use-utf8-use-utf8mb4-11761243e434 https   mariadb com kb en library innodb-compact-row-format  overflow-pages-with-the-compact-row-format Innodb page size setting

User · Answer

You need to do some changes to the my ini file Add this under  mysqld  innodb strict mode 0  Update These two lines innodb log file size 256M innodb log buffer size 256M  innodb strict mode  When it is enabled  certain InnoDB warnings become errors instead  Reference  https   mariadb com kb en innodb-strict-mode  innodb log file size  amp  innodb log buffer size needs to increase in size

User · Answer

I had the same issue  this solved it for me   ALTER TABLE  my table  ROW FORMAT DYNAMIC    From MYSQL Documentation      The DYNAMIC row format maintains the efficiency of storing the entire   row in the index node if it fits  as do the COMPACT and REDUNDANT   formats   but this new format avoids the problem of filling B-tree   nodes with a large number of data bytes of long columns  The DYNAMIC   format is based on the idea that if a portion of a long data value is   stored off-page  it is usually most efficient to store all of the   value off-page  With DYNAMIC format  shorter columns are likely to   remain in the B-tree node  minimizing the number of overflow pages   needed for any given row

User · Answer

After spending hours I have found the solution  just run the following SQL in your MySQL admin to convert the table to MyISAM   USE db name  ALTER TABLE table name ENGINE MYISAM

User · Answer

Here is simple tip for anyone interested   After upgrade from Debian 9 to Debian 10 with 10 3 17-MariaDB  I have some errors from Joomla databases    Warning  InnoDB  Cannot add field field in table database table because after adding it  the row size is 8742 which is greater than maximum allowed size  8126  for a record on index leaf page   Just in case  I set  innodb default row format   DYNAMIC in  etc mysql mariadb conf d 50-server cnf  it was default anyway   Than  I have used phpmyadmin to run  Optimize table  for all the tables in Joomla database  I think table recreation done by phpmyadmin in the process helped  If you happen to have phpmyadmin installed it is just few clicks to do

User · Answer

I ran into this issue when I was trying to restore a backed up mysql database from a different server   What solved this issue for me was adding certain settings to my conf  like in the questions above  and additionally changing the sql backup file    Step 1   add or edit the following lines in my conf   innodb page size 32K innodb file format Barracuda innodb file per table 1   Step 2 add ROW FORMAT DYNAMIC to the table create statement in the sql backup file for the table that is causing this error    DROP TABLE IF EXISTS  problematic table      40101 SET  saved cs client     character set client        40101 SET character set client   utf8     CREATE TABLE  problematic table     id  bigint 20  NOT NULL AUTO INCREMENT        PRIMARY KEY   id     ENGINE InnoDB AUTO INCREMENT 3 ROW FORMAT DYNAMIC    the important change above is ROW FORMAT DYNAMIC   that was not included in the orignal sql backup file   source that helped me to resolve this issue  MariaDB and InnoDB MySQL Row size too large

User · Answer

I also encountered the same problem  I solve the problem by executing the following sql   ALTER   table  ROW FORMAT COMPRESSED    But  I think u should know about the Row Storage  There are two kinds of columns  variable-length column such as  VARCHAR  VARBINARY  and BLOB and TEXT types  and fixed-length column  They are stored in different types of pages      Variable-length columns are an exception to this rule  Columns such as BLOB and VARCHAR that are too long to fit on a B-tree page are stored on separately allocated disk pages called overflow pages  We call such columns off-page columns  The values of these columns are stored in singly-linked lists of overflow pages  and each such column has its own list of one or more overflow pages  In some cases  all or a prefix of the long column value is stored in the B-tree  to avoid wasting storage and eliminating the need to read a separate page    and when purpose of setting ROW FORMAT is      When a table is created with ROW FORMAT DYNAMIC or ROW FORMAT COMPRESSED  InnoDB can store long variable-length column values  for VARCHAR  VARBINARY  and BLOB and TEXT types  fully off-page  with the clustered index record containing only a 20-byte pointer to the overflow page    Wanna know more about DYNAMIC and COMPRESSED Row Formats

User · Answer

I would like to share an awesome answer  it might be helpful  Credits Bill Karwin see here https   dba stackexchange com questions 6598 innodb-create-table-error-row-size-too-large  They vary by InnoDB file format At present there are 2 formats called Antelope and Barracuda   The central tablespace file  ibdata1  is always in Antelope format  If you use file-per-table  you can make the individual files use Barracuda format by setting innodb file format Barracuda in my cnf   Basic points    One 16KB page of InnoDB data must hold at least two rows of data  Plus each page has a header and a footer containing page checksums and log sequence number and so on  That s where you get your limit of a bit less than 8KB per row  Fixed-size data types like INTEGER  DATE  FLOAT  CHAR are stored on this primary data page and count toward the row size limit  Variable-sized data types like VARCHAR  TEXT  BLOB are stored on overflow pages  so they don t count fully toward the row size limit  In Antelope  up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page  Barracuda supports a dynamic row format  so it may store only a 20-byte pointer on the primary data page  Variable-size data types are also prefixed with 1 or more bytes to encode the length  And InnoDB row format also has an array of field offsets  So there s an internal structure more or less documented in their wiki    Barracuda also supports a ROW FORMAT COMPRESSED to gain further storage efficiency for overflow data   I also have to comment that I ve never seen a well-designed table exceed the row size limit  It s a strong  code smell  that you re violating the repeating groups condition of First Normal Form

[mysql] Change limit for "Mysql Row size too large"

Examples related to mysql