Articles in this section

See more

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

Avatar
Nelson Leiva
Updated
Follow
Plesk kb: technical ABT: Group B

Applicable to:

  • Plesk

Symptoms

  • Importing MySQL dump file to subscription's database in Plesk > Subscription > example.com > Databases > Import 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

2 comments

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
  • Avatar
    ABOLEON MEDIA (Edited )

    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

    ROW_FORMAT=DYNAMIC

    solves the problem.

    CREATE TABLE `table` (
        `id` int(10) UNSIGNED NOT NULL,
        ...
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;
    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles