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
Hi!
Thanks for posting this solution. It helped me with installation of Zabbix on Raspberry Pi4 running MySQL / MariaDB. Latest Raspbian / Debian 10 buster.
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.
Please sign in to leave a comment.