1

I've done a dump of my Wordpress database using Windows's command prompt:

mysqldump -u username -p databasename > filename.sql

Then, on a different server, imported it using:

mysql -u username -p databasename < filename.sql

I get this error when importing the values column in the wordpress_options table:

#1118 - 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. 

The weird thing is, both the original and new databases are using 'longtext' for this column, which I believe can store 4GB of data. My options value column is only 1.9MB!

I'm wondering if it is a setting in mysql? I've set the maximum packet size to 16MB and the key buffer size to 32MB

EDIT Here is the SQL that creates the WordPress options table

--
-- Table structure for table `wordpress_options`
--

DROP TABLE IF EXISTS `wordpress_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wordpress_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4872 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

I've also tried using phpMyAdmin for the import, instead of the command line, but the result is the same.

  • probably a mysql question, but I am going to guess that your DBs don't have the same structure – Mark Kaplun Sep 24 '14 at 10:31
  • Hi @MarkKaplun I've added the SQL that creates the table if that helps. – Chris Sep 24 '14 at 10:38
  • As I said, this is a mysql question not a wordpress one. I have no real idea even what to look for. – Mark Kaplun Sep 24 '14 at 10:41
  • 2
    Check the innodb_log_file_size in the mySQL instance. stackoverflow.com/questions/22637733/… – Otto Sep 24 '14 at 10:51
  • Thanks @Otto you're a life-saver! I'm using MySQL 5.6.20 so that was indeed the problem. Feel free to add this as an answer and I'll accept :) – Chris Sep 24 '14 at 11:32
1

The problem occurs in MySQL 5.6.20 or later, if the innodb_log_file_size in the my.ini is set too low. I changed mine to 16M and the problem went away. Thanks to @Otto for the solution.

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.