Articles in this section

See more

MySQL database import fails: ERROR 1118 (42000): Row size too large

Avatar
Marc Vidal
Updated
Follow
Plesk for Windows Plesk for Linux kb: technical

Applicable to:

  • Plesk for Linux
  • Plesk for Windows

Symptoms

  • Importing MySQL dump file to subscription's database fails with the following error:

    CONFIG_TEXT: 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 |

    C:\> 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 the record to fit in the B-tree leaf page. See MySQL bug tracker, Bug #69336 for more details.

Resolution

Warning: It is recommended to revert the changes after importing dump file by removing the added lines from the configuration and restarting the MySQL server.

For Linux

  1. Connect to the server via SSH.

  2. Open the MySQL configuration file using vi:

    • On .rpm-based distributions (CentOS, RHEL, CloudLinux): /etc/my.cnf file

    • On .deb-based distributions (Debian, Ubuntu): /etc/mysql/my.cnf file

  3. Add the following content under [mysqld] section:

    CONFIG_TEXT: default_storage_engine=MyISAM
    innodb_strict_mode = 0

  4. Restart MySQL service:

    # service mysql restart

  5. Import the MySQL database

  6. Once the MySQL database is imported, it is recommended to revert the changes.

For Windows

  1. Connect to the server via RDP.

  2. Open the MySQL configuration file %plesk_dir%Databases\MySQL\my.ini.

  3. Add the following line under [mysqld] section:

    CONFIG_TEXT: innodb_strict_mode = 0

  4. Restart MySQL service using Plesk Services Monitor.

  5. Import the MySQL database

  6. Once the MySQL database is imported, it is recommended to revert the changes.

Note: in case the issue still persists, open the database dump with any text editor and replace all occurences of ENGINE=InnoDB to ENGINE=MyISAM.

Comments

1 comment

Sort by Date Votes
  • Avatar
    Mariusz B

    Hi!

    Thanks for posting this solution.  It helped me with installation of Zabbix on Raspberry Pi4 running MySQL / MariaDB.  Latest Raspbian / Debian 10 buster.

     

     

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles