Applicable to:
- Plesk
Symptoms
Importing a MySQL dump file to a subscription's database in Subscriptions > example.com > Databases > Import fails with the following error:
ERROR 1118 (42000) at line 432: 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.
-
InnoDB is set as default storage engine in MySQL:
# plesk db "show engines" | egrep DEFAULT
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |plesk db "show engines" | findstr DEFAULT
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
Cause
MySQL InnoDB requires database records to fit in the B-tree leaf page. See MySQL bug tracker, Bug #69336 for more details.
Resolution
-
Connect to the server via SSH
-
Back up the MySQL configuration file
/etc/my.cnf
:# cp /etc/my.cnf{,.bak}
-
Add the following lines to the file in step 2. under [mysqld] section:
default_storage_engine=MyISAM
innodb_strict_mode = 0 -
Restart MySQL service:
# service mysql restart
-
Import the MySQL database
-
Restore backed up MySQL configuration file
# mv /etc/my.cnf.bak /etc/my.cnf --force
-
Restart MySQL service :
# service mysql restart
-
Connect to the server via SSH
-
Create a backup of the MySQL configuration file
/etc/mysql/my.cnf
:# cp /etc/mysql/my.cnf{,.bak}
-
Add the following lines to the file in step 2. under [mysqld] section:
default_storage_engine=MyISAM
innodb_strict_mode = 0 -
Restart MySQL service:
# service mysql restart
-
Import the MySQL database
-
Restore backed up MySQL configuration file
# mv /etc/mysql/my.cnf.bak /etc/mysql/my.cnf --force
-
Restart MySQL service :
# service mysql restart
-
Connect to the server via RDP
-
Copy the file
%plesk_dir%Databases\MySQL\my.ini
to%plesk_dir%Databases\MySQL\my.ini_bak
-
Add the following lines to the file in step 2. under [mysqld] section:
innodb_strict_mode = 0
-
Restart MySQL service using Plesk Services Monitor
-
Import the MySQL database
-
Restore file
%plesk_dir%Databases\MySQL\my.ini_bak
to%plesk_dir%Databases\MySQL\my.ini
-
Restart MySQL service using Plesk Services Monitor
If the issue persists, perform the following steps:
-
Open the database dump with a text editor
-
Replace all occurences of
ENGINE=InnoDB
withENGINE=MyISAM
in the file -
Import the database dump
Comments
2 comments
This did not solve it for me as I have upgraded MariaDB to 10.5.10 with default configuration file and changing as suggested leaded to mysql error because of uknown variable inno_db_strict_mode.
However, I resolved my issue when I founded that it was caused by the ROW_FORMAT=COMPACT directive. Removing it from the import or changing it to
solves the problem.
Hi!
Thanks for posting this solution. It helped me with installation of Zabbix on Raspberry Pi4 running MySQL / MariaDB. Latest Raspbian / Debian 10 buster.
Please sign in to leave a comment.