Geek Answers Handbook

ERROR 1118 (42000) Line size is too large

I know this question has been asked (and answered) many times, but none of them seem to be the same problem that I see ...

There are only two columns in the table that gives me problems: the first field is an integer, the second field is a longtext. Here is the part of the dump file from MySQL 5.5.30:

1 - MySQL dump 10.13 Distrib 5.5.30, for Linux (x86_64) 2 -- 3 -- Host: localhost Database: mydatabasename 4 -- ------------------------------------------------------ 5 -- Server version 5.5.30-log 32 DROP TABLE IF EXISTS `large_file`; 33 /*!40101 SET @saved_cs_client = @@character_set_client */; 34 /*!40101 SET character_set_client = utf8 */; 35 CREATE TABLE `large_file` ( 36 `id` int(11) NOT NULL AUTO_INCREMENT, 37 `data` longtext, 38 PRIMARY KEY (`id`) 39 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1; 40 /*!40101 SET character_set_client = @saved_cs_client */; 43 -- Dumping data for table `large_file` 44 -- 45 46 LOCK TABLES `large_file` WRITE; 47 /*!40000 ALTER TABLE `large_file` DISABLE KEYS */; 48 INSERT INTO `large_file` VALUES(38,'GyUtMTIzNDVYQ... <large data> ...); 49 /*!40000 ALTER TABLE `large_file` ENABLE KEYS */; 50 UNLOCK TABLES;

As you can see, this dump file came from MySQL 5.5.30, and I can import this data into 5.5.30. But, when I try to import into 5.6.x, I get the error ERROR 1118 (42000) is too big .

The data entering the big_file table is (relatively) large; values ​​range from 15 MB to 25 MB. All ASCII data (base encoding 64).

Other posters had problems with a very large number of columns, but I only have two columns in this table.

The longtext type should be able to store about 4 GB, and this was the case with 5.5.30, but I believe that switching to 5.6.x will be difficult.

Can anyone understand why this is happening? Or how can I get around this?

Thanks in advance!

+8
sql mysql
source share
4 answers

Make sure the innodb_log_file_size parameter is large enough — 10 times the largest BLOB data size found in the rows of the table, plus the length of the other fields of variable length.

Following are MySQL 5.6 Release Notes

InnoDB Notes

  • Important change : re-writing to the log for large, externally stored BLOB fields can overwrite the most recent breakpoint. Patch 5.6.20 limits the size of the BLOB rewriting log to 10% of the size of the replay log file. Patch 5.7.5 corrects the error without imposing restrictions. For MySQL 5.5, the error remains a known limitation.

    As a result of the BLOB BLOB record prefix introduced for MySQL 5.6, innodb_log_file_size must be set to a value greater than the 10 times the largest BLOB data value found in the rows of your tables, as well as the length of other variable-length fields (VARCHAR, VARBINARY and TEXT). Failure to do so may result in oversize errors . No action is required if innodb_log_file_size is already large enough or your tables do not contain BLOB data. (Error No. 16963396, Error No. 19030353, Error No. 69477)

+9
source share

I had this problem with MYSQL 5.7 (OSX 10.11).

The following worked, although it may not be perfect.

In my.cfn add:

 innodb_strict_mode = 0
+6
source share

Include an identical issue importing BLOB data from 5.5 to 5.6.22, where blobs were aprox: maximum 70 MB. However, while increasing innodb_log_file_size did the trick, in my case I had to increase it to 10 GB (I tried in 1 GB increments starting from 1 GB), which is more than 10 times the maximum BLOB size.

+2
source share
 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 ubuntu 16.04 edit path : nano /etc/mysql/mysql.conf.d/mysqld.cnf

it works !!...

Click to document

+2
source share

More articles:


All Articles