Articles in this section

See more

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

Avatar
Benjamin Wessel
Updated
Follow
Plesk kb: technical ABT: Group B

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

For Linux on CentOS, RHEL, CloudLinux

  1. Connect to the server via SSH

  2. Back up the MySQL configuration file /etc/my.cnf:

    # cp /etc/my.cnf{,.bak}

  3. Add the following lines to the file in step 2. under [mysqld] section:

    default_storage_engine=MyISAM
    innodb_strict_mode = 0

  4. Restart MySQL service:

    # service mysql restart

  5. Import the MySQL database

  6. Restore backed up MySQL configuration file

    # mv /etc/my.cnf.bak /etc/my.cnf --force

  7. Restart MySQL service :

    # service mysql restart

For Linux on Debian or Ubuntu

  1. Connect to the server via SSH

  2. Create a backup of the MySQL configuration file /etc/mysql/my.cnf:

    # cp /etc/mysql/my.cnf{,.bak}

  3. Add the following lines to the file in step 2. under [mysqld] section:

    default_storage_engine=MyISAM
    innodb_strict_mode = 0

  4. Restart MySQL service:

    # service mysql restart

  5. Import the MySQL database

  6. Restore backed up MySQL configuration file

    # mv /etc/mysql/my.cnf.bak /etc/mysql/my.cnf --force

  7. Restart MySQL service :

    # service mysql restart

For Windows

  1. Connect to the server via RDP

  2. Copy the file %plesk_dir%Databases\MySQL\my.ini to %plesk_dir%Databases\MySQL\my.ini_bak

  3. Add the following lines to the file in step 2. under [mysqld] section:

    innodb_strict_mode = 0

  4. Restart MySQL service using Plesk Services Monitor

  5. Import the MySQL database

  6. Restore file %plesk_dir%Databases\MySQL\my.ini_bak to %plesk_dir%Databases\MySQL\my.ini

  7. Restart MySQL service using Plesk Services Monitor

If the issue persists, perform the following steps:

  1. Open the database dump with a text editor

  2. Replace all occurences of ENGINE=InnoDB with ENGINE=MyISAM in the file

  3. Import the database dump

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