error-when-importing-database

How to Fix “ERROR 1118 (42000) at line 25: Row size too large (> 8126)” Error When Importing a MySQL or MariaDB Database

The Problem You Might Be Facing

Imagine this: You’re trying to import your database, and everything seems fine, but suddenly you hit a roadblock. A frustrating error message pops up:

ERROR 1118 (42000) at line 25: 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.

You scratch your head, wondering what went wrong. If this sounds familiar, don’t worry—you’re not alone! This is a common issue that happens when your database tables have too many fields or large rows, and MySQL’s InnoDB strict mode gets in the way.

Row-size-too-large

Why Does This Happen?

The short answer: Your database is trying to store too much data in a single row. MySQL (or MariaDB) has a limit—each row in an InnoDB table can’t exceed 8126 bytes in total. If your table has over 600 columns or lots of VARCHAR fields, you might hit this limit.

To make things worse, InnoDB strict mode enforces this rule, preventing the database import from going through.

How to Fix It

The good news? There’s a way around it! Here’s what you can do to get your database imported successfully.

1. Access Your Server

Since this fix involves changing MySQL settings, you’ll need access to the command line. If you’re using a VPS or dedicated server, you can log in via SSH. If you’re on cPanel/WHM, you can use the Terminal feature.

2. Modify MySQL Configuration

  1. Open the MySQL configuration file by running:
nano /etc/my.cnf

(Or use vim if you prefer.)

2. Look for the [mysqld] or [MariaDB] section.

3. Check if innodb_strict_mode is already there.

4. If it is, change it to OFF. If it’s not there, add this line: innodb_strict_mode=OFF

Save and close the file.

3. Restart MySQL

Now, restart the MySQL service to apply the changes:

/usr/local/cpanel/scripts/restartsrv_mysql

If you’re not using cPanel, use:

systemctl restart mysql

Other Ways to Avoid This Issue

If turning off InnoDB strict mode isn’t an option, here are a few alternative solutions:

  • Reduce the number of columns: If your table has over 600 fields, see if you can break it into multiple tables.
  • Use TEXT or BLOB fields: Instead of long VARCHAR fields, use TEXT or BLOB, which don’t count as part of the row size limit.
  • Enable Dynamic Row Format: Change your table row format to DYNAMIC or COMPRESSED to handle larger data.
  • Consider Partitioning: If your table is massive, breaking it into smaller partitions can help manage the data more efficiently.

Wrapping Up

Hitting the ERROR 1118 (42000) during a database import can be frustrating, but it’s not the end of the world. Disabling InnoDB strict mode is the easiest fix, but if you prefer a long-term solution, optimizing your table structure is the way to go.

If you’re using SatisfyHost and need help fixing this, our support team is here for you. Just reach out, and we’ll be happy to assist!

Now go ahead—get that database up and running! 🚀

Leave a Reply