CODEWITHSUNDEEP
mysql
jithin giri
jithin giri

Reputation: 743

Error Code: 1118. Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format,

I have a table with 600 fields in which datatype of all the 600 fields is 'TEXT'.

Now when we are trying to insert some data to this table it is showing the below 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

My configuration file contains the following parameters:

innodb_strict_mode=OFF
innodb_log_file_size = 2G
innodb_log_buffer_size = 512M
innodb_file_format = Barracuda
max_allowed_packet = 1G

But still, couldn't insert the data. I there any way we can resolve this?

Upvotes: 4

Views: 33407

Answers (4)

Nanhe Kumar
Nanhe Kumar

Reputation: 16297

If InnoDB strict mode (10.5.9-MariaDB) is enabled this error can show.

Check enabled or not

SHOW  variables LIKE '%strict%';

If enable then you can disable.

SET GLOBAL innodb_strict_mode=OFF;

For more detail information read here>>

Upvotes: 12

Pratiksha Kayastha
Pratiksha Kayastha

Reputation: 31

Simply in my.ini file, add below line.It worked for me.

innodb_strict_mode = 0

Upvotes: 0

Joyce obi
Joyce obi

Reputation: 79

The suggestion from here worked for me. For those using MariaDB 10.2 and later, the DYNAMIC row format is the default row format therefore increase the length of VARCHAR in the affected table

E.g

col1 varchar(256) NOT NULL,

The 256 byte limit means that a VARCHAR column will only be stored on overflow pages if it is at least as large as a varchar(256):

You can learn more here

Upvotes: 0

Alexander
Alexander

Reputation: 4527

Try to add following options to your configuration file:

  1. To activate new file format: innodb_file_format = Barracuda
  2. To store each table in own file: innodb_file_per_table=1
  3. To prevent surprises: innodb_strict_mode=ON

To store long variable-length column values use dynamic or compressed row format for large tables. For example, use

ALTER TABLE LargeTable ENGINE = InnoDB ROW_FORMAT = DYNAMIC;

Upvotes: 4

Related Questions