Problem
I’d like to make a table with 325 columns:
CREATE TABLE NAMESCHEMA.NAMETABLE
(
ROW_ID TEXT NOT NULL , //this is the primary key
324 column of these types:
CHAR(1),
DATE,
DECIMAL(10,0),
DECIMAL(10,7),
TEXT,
LONG,
) ROW_FORMAT=COMPRESSED;
I changed all the VARCHAR to TEXT and added Barracuda to MySQL’s my.ini file. Here are the properties I added:
innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_file_format_check = ON
However, I continue to receive the following error:
Error Code: 1118
Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
EDIT: Because it’s a legacy application/system/database, I can’t change the database’s structure. It is an export of the legacy database to generate a new table.
EDIT2: I wrote this question that is similar to others, but inside there are some solutions that I found on the internet, such as VARCHAR and Barracuda, but I still have the same problem, so I decided to open a new question with the classic answer already inside to see if anyone has any other answers.
Asked by Diego87
Solution #1
I tried every option provided, but just one parameter worked.
innodb_strict_mode = 0
solved my day…
From the manual:
Answered by Stefano Brozzi
Solution #2
Due to a change in MySQL Server 5.6.20, I recently encountered the same error code. By altering the innodb log file size setting in the my.ini text file, I was able to resolve the issue.
An innodb log file size that is too small will result in a “Row size too large error,” according to the release notes.
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html
Answered by user2635717
Solution #3
ERROR 1118 (42000) at line 1852:
Row size too large (> 8126). Changing some columns to TEXT or
BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
[mysqld]
innodb_log_file_size = 512M
innodb_strict_mode = 0
edit path: ubuntu 16.04 ubuntu 16.04 ubuntu 16.04 ubun
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
On Windows, the path will look like this:
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
Remember to restart the service (or restart your machine)
Answered by ตอ ตอม
Solution #4
innodb page size is the most important parameter.
The trick is that this parameter may only be modified during the INITIALIZATION of the mysql service instance, therefore changing it after the instance has already been initialized has no effect (the very first run of the instance)
If you don’t modify this number before initialization in my.ini, the default value will be 16K, with an 8K row size restriction. That is why the mistake occurs.
If you raise innodb page size, you must also increase innodb log buffer size. Make sure it’s at least 16M. You can’t raise innodb page size to 32k or 64k if the ROW FORMAT is set to COMPRESSED. It has to be DYNAMIC (default in 5.7).
Furthermore, the innodb buffer pool size should be extended from 128M to 512M at the very least, otherwise the instance would fail to initialize (I do not have the exact error).
The row size error was resolved as a result of this.
The issue is that you must build a new MySql instance and move data from the old one to the new DataBase instance.
After starting a new instance and initializing with the my.ini that is initially adjusted with these settings, I altered the following parameters and they worked:
innodb_page_size=64k
innodb_log_buffer_size=32M
innodb_buffer_pool_size=512M
Here are all of the settings and descriptions where I discovered the solution:
Hope this helps!
Regards!
Answered by fefe
Solution #5
I had a similar problem this morning, and the following solution saved my life:
Have you tried disabling innodb strict mode?
SET GLOBAL innodb_strict_mode = 0;
then try importing it again.
Using MySQL >= 5.7.7, innodb strict mode is ON, whereas previously it was OFF.
Answered by Abdur Rehman
Post is based on https://stackoverflow.com/questions/22637733/mysql-error-code-1118-row-size-too-large-8126-changing-some-columns-to-te