[SOLVED] XAMPP Row size too large, innoDB sql import

Ran into some problems recently with XAMPP. Exported a large mySQL production database and tried to import it locally in XAMPP (through Shell access).

Error: ERROR 1118 <42000> at line 437919: 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.

First I tried changing table to ROW_FORMAT=COMPRESSED doing so:

ALTER TABLE <tablename>
    ENGINE=InnoDB
    ROW_FORMAT=COMPRESSED
    KEY_BLOCK_SIZE=8;

No success with that. Then I changed innodb_buffer_pool_size to 32MB (default setting was 8 or 16) in my.ini (mySQL config file in XAMPP)

xampp control panel

# Comment the following if you are using InnoDB tables
#skip-innodb
innodb_data_home_dir = "/Sites/xampp-portable-win32-1.8.3-5-VC11/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "/Sites/xampp-portable-win32-1.8.3-5-VC11/mysql/data"
#innodb_log_arch_dir = "/Sites/xampp-portable-win32-1.8.3-5-VC11/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 32M
innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 10M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table = 1
innodb_file_format = Barracuda

Doing so made it possible to import a 1GB sql table without any problem.

The above applies to XAMPP portable version xampp-portable-win32-5.5.19-0-VC11.zip

 

3 thoughts on “[SOLVED] XAMPP Row size too large, innoDB sql import”

Leave a Reply

Your email address will not be published. Required fields are marked *