How to Fix a Large WordPress Database Import That Fails (Gone Away, Collation, Row Size)

You are moving or restoring a WordPress site, and the database import stops partway. phpMyAdmin shows #2006 - MySQL server has gone away, a white page, or No data was received to import. The command line prints ERROR 1273 (HY000) at line LINE_NUMBER: Unknown collation: ‘utf8mb4_0900_ai_ci’, an access denied error about SUPER privileges, or ERROR 1118 (42000): Row size too large (> 8126). Or the import works, but every apostrophe on the site turns into ’.

A .sql dump is just a long list of SQL statements: create this table, insert these rows. The import replays them one by one against the new server. It fails when a statement is too big for the server to accept, when the tool running it gives up on time, or when the new server does not understand something the old server wrote. Each error message tells you which of those happened, and each has a specific, safe fix.

What is happening

Error	What it means	Go to
MySQL server has gone away (2006) or Lost connection to MySQL server during query (2013)	A statement was larger than max_allowed_packet, or the connection timed out	Step 3
phpMyAdmin white page, timeout or You probably tried to upload a file that is too large	PHP upload size or execution time limit	Step 2
Unknown collation: 'utf8mb4_0900_ai_ci' (1273)	A MySQL 8 dump going into MariaDB or older MySQL	Step 4
Access denied; you need (at least one of) the SUPER ... privilege(s) (1227)	DEFINER clauses on views, triggers or routines, or GTID lines	Step 5
Row size too large (1118)	A wide table in an old row format, with strict mode on	Step 6
Characters like é, ’ or ðŸ on the site	Text was read in one character set and written in another	Step 7

If the import succeeds but the site then redirects to the old domain, that is a URL problem covered in FG-40 and the manual migration guide (FG-58), not an import problem.

Before you start

• Access you need: SSH to the server you are importing into, the database name, user and password (from wp-config.php), and the dump file uploaded somewhere outside the public web folder.
• Backups: importing replaces tables. If the target database holds anything you care about, export it first with wp db export ../before-import.sql. Never edit your only copy of a dump: every fix below works on a copy.
• How to run commands. Commands starting with mysql, wp, sed, grep, zcat or pv run in an SSH terminal on the server. Run wp commands from the site folder, the one containing wp-config.php; some hosts name it wp-cli. Lines under a [mysqld] heading are not commands but settings in a MySQL config file.

Step 1: Confirm the dump file is complete

Many failed imports are really failed exports. Over SSH:

ls -lh DUMP_FILE.sql
tail -c 300 DUMP_FILE.sql

DUMP_FILE.sql is your dump’s file name (for a .sql.gz, use zcat DUMP_FILE.sql.gz | tail -c 300). ls -lh shows the size in readable units. tail -c 300 prints the last 300 bytes. A complete mysqldump or mariadb-dump file ends with a comment like -- Dump completed on followed by a date. If the file instead stops in the middle of an INSERT line, the export was cut off, often by a PHP timeout in a backup plugin or a full disk. No import setting can fix that; export again, ideally over SSH as shown in FG-58.

Step 2: Stop fighting phpMyAdmin and use the command line

phpMyAdmin is a PHP application, so it inherits PHP’s limits. The Import tab shows the cap next to the file picker, for example (Max: 50MiB). That figure comes from upload_max_filesize and post_max_size. Large imports also hit max_execution_time and phpMyAdmin’s own $cfg['ExecTimeLimit'], which defaults to 300 seconds.

Workarounds inside phpMyAdmin exist. You can upload a compressed .sql.gz or .zip, which phpMyAdmin unpacks itself. You can check Allow the interruption of an import in case the script detects it is close to the PHP timeout limit and resume using Skip this number of queries. They are fragile on big files. The command line has no upload limit and no PHP timeout. Over SSH, in the site folder:

wp db import DUMP_FILE.sql

wp db import connects with the credentials in wp-config.php and turns off unique and foreign key checks during the import for speed. Good output is Success: Imported from 'DUMP_FILE.sql'. Any error from the server is printed with its line number, which the steps below use.

Without WP-CLI, or for a compressed file with a progress bar:

mysql -u DB_USER -p DB_NAME < DUMP_FILE.sql
pv DUMP_FILE.sql.gz | gunzip | mysql -u DB_USER -p DB_NAME

DB_USER and DB_NAME are the values of DB_USER and DB_NAME in wp-config.php. -p prompts for the password. In the first line, < feeds the file into mysql. In the second, pv reads the compressed file and shows a progress bar with speed and time remaining, gunzip decompresses the stream, and mysql imports it, so you never need disk space for the uncompressed file. If pv is not installed, use gunzip -c DUMP_FILE.sql.gz | mysql -u DB_USER -p DB_NAME. Silence means success; mysql prints only errors, like ERROR 1273 (HY000) at line LINE_NUMBER: followed by the message.

Imports can run for a long time. Start them inside screen or tmux if available, so a dropped SSH connection does not kill the import halfway.

Step 3: Fix “MySQL server has gone away”

mysqldump writes many rows per INSERT statement to save space. A table holding big values (serialized builder data, log tables, base64 images in post content) can produce a single statement of tens of megabytes. If it exceeds max_allowed_packet, the server drops the connection and the client reports that the server has gone away.

Confirm it

Find the longest line in the dump and compare it with the server setting. Over SSH:

awk '{ if (length($0) > max) max = length($0) } END { print max }' DUMP_FILE.sql
mysql -u DB_USER -p -e "SHOW VARIABLES WHERE Variable_name IN ('max_allowed_packet','wait_timeout','net_read_timeout')"

The awk command prints the length in bytes of the longest line (each mysqldump INSERT is one line). The mysql command shows the settings in bytes and seconds. If the longest line is near or above max_allowed_packet, that is your cause. MySQL 8 defaults to 64 MB on the server and 16 MB in the mysql client; MariaDB defaults vary by version and distribution, so read the real value.

Fix it

Both the client and the server must allow the packet. Raise the client side on the command line:

mysql --max_allowed_packet=512M -u DB_USER -p DB_NAME < DUMP_FILE.sql

--max_allowed_packet=512M lets the client send statements up to 512 MB; the hard ceiling is 1 GB. For the server side, on your own server add this under the [mysqld] section of the MySQL or MariaDB config (often /etc/mysql/my.cnf or a file in /etc/mysql/mariadb.conf.d/) and restart the database service:

[mysqld]
max_allowed_packet = 512M
wait_timeout = 28800
net_read_timeout = 600

max_allowed_packet raises the server’s limit. wait_timeout is how long an idle connection survives, which matters when a tool pauses between statements. net_read_timeout is how long the server waits for more data mid-statement, which helps over slow links. Confirm with the SHOW VARIABLES command above. On shared hosting you cannot edit this file; open a ticket and ask for a higher max_allowed_packet, or use the next option.

Or make the statements smaller

If you can export again, ask mysqldump for smaller INSERT statements:

mysqldump -u DB_USER -p --single-transaction --net-buffer-length=1M DB_NAME > DUMP_FILE.sql

--net-buffer-length=1M caps each multi-row INSERT at about 1 MB, well under any server’s limit. A single row larger than the server’s limit still needs the settings above. Replacing it with --skip-extended-insert writes one row per statement, which always fits but imports much more slowly.

Step 4: Fix “Unknown collation: utf8mb4_0900_ai_ci”

MySQL 8 introduced a new default collation, utf8mb4_0900_ai_ci, and writes it into every CREATE TABLE in its dumps. MariaDB and MySQL 5.7 did not have it, so they stop at the first table. Some recent MariaDB releases accept the name as an alias, but many servers still in use do not.

Confirm it and see every variant

grep -o 'utf8mb4_0900_[a-z_]*' DUMP_FILE.sql | sort | uniq -c

grep -o prints only the matching part of each line, and sort | uniq -c counts each distinct value. Typical output is a count followed by utf8mb4_0900_ai_ci, sometimes with utf8mb4_0900_bin or utf8mb4_0900_as_cs as well. Also check what the target server supports with mysql -u DB_USER -p -e "SHOW COLLATION LIKE 'utf8mb4%'".

Fix it with sed on a copy

cp DUMP_FILE.sql DUMP_FILE.fixed.sql
sed -i -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_520_ci/g' -e 's/utf8mb4_0900_as_ci/utf8mb4_unicode_520_ci/g' -e 's/utf8mb4_0900_bin/utf8mb4_bin/g' DUMP_FILE.fixed.sql
grep -c 'utf8mb4_0900' DUMP_FILE.fixed.sql

sed -i edits the file in place (on macOS, use sed -i ''). Each -e adds one substitution of the form s/old/new/g, where g means every occurrence on a line, not just the first. The pattern is the exact collation name, so it matches only that token: the character set utf8mb4 stays the same, and no text is converted, so your content is untouched. utf8mb4_unicode_520_ci is the collation WordPress itself picks on servers that support it; utf8mb4_unicode_ci is a fine alternative on older servers. The final grep -c should print 0. If it reports a variant you did not map, such as utf8mb4_0900_as_cs, add a substitution to utf8mb4_bin for case-sensitive ones. Then import the fixed file.

The only theoretical risk is a post that mentions the collation name in its text, which would also change. That is harmless on almost every site.

Step 5: Fix DEFINER and GTID privilege errors

WordPress core does not use views, triggers or stored routines, but some plugins and custom code do. mysqldump records who created each one in a DEFINER=`user`@`host` clause. Importing as a different, ordinary user fails with ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) (newer MySQL also names SET_USER_ID or SET_ANY_DEFINER). Even if it imports, a definer user that does not exist on the new server makes the view or trigger fail later with ERROR 1449: The user specified as a definer does not exist.

grep -o 'DEFINER=`[^`]*`@`[^`]*`' DUMP_FILE.sql | sort | uniq -c
sed -i -e 's/DEFINER=`[^`]*`@`[^`]*`//g' DUMP_FILE.fixed.sql

The first line lists each definer and how often it appears. In the sed pattern, `[^`]*` means a backtick, any run of characters that are not backticks, and a closing backtick, so it matches the user name and the host exactly and nothing beyond them. Removing the clause makes each object belong to whoever imports it. Good output from a repeat of the first command is nothing. Check afterward with grep -c 'DEFINER=' DUMP_FILE.fixed.sql; any remaining matches will be SQL SECURITY DEFINER, which is fine.

A related MySQL 8 error mentions @@GLOBAL.GTID_PURGED. It comes from replication lines at the top of the dump. Re-export with --set-gtid-purged=OFF, or delete the lines that start with SET @@GLOBAL.GTID_PURGED and SET @@SESSION.SQL_LOG_BIN (plus the matching restore line near the end) from your copy.

Step 6: Fix “#1118 Row size too large”

The full message is Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 768 bytes is stored inline. InnoDB stores rows in pages, and a row has to fit in about half a page. Old row formats (COMPACT, REDUNDANT) keep the first 768 bytes of every long text column inside the row, so a plugin table with dozens of text columns overflows. With innodb_strict_mode on, the default in current MySQL and MariaDB, the CREATE TABLE is refused.

Confirm it

grep -n 'ROW_FORMAT=' DUMP_FILE.sql | head

-n shows line numbers. Matches with ROW_FORMAT=COMPACT or ROW_FORMAT=REDUNDANT on the failing table confirm the cause.

Fix it

sed -i -e 's/ROW_FORMAT=COMPACT/ROW_FORMAT=DYNAMIC/g' -e 's/ROW_FORMAT=REDUNDANT/ROW_FORMAT=DYNAMIC/g' DUMP_FILE.fixed.sql

DYNAMIC stores long values off the row, leaving only a 20 byte pointer, so wide tables fit. It is the default for new tables in current MySQL and MariaDB. If the dump has no ROW_FORMAT at all and still fails, the server’s default may be old; import with a session setting instead:

mysql --init-command="SET SESSION innodb_strict_mode=0" -u DB_USER -p DB_NAME < DUMP_FILE.fixed.sql

--init-command runs the statement as soon as the client connects. Turning strict mode off for that session lets the table be created with a warning instead of an error. A row that is genuinely too big can still fail on insert, so prefer the DYNAMIC fix and treat this as a fallback.

Step 7: Prevent and fix garbled characters (mojibake)

If the import works but text shows é instead of é, ’ instead of a curly apostrophe, or ðŸ where emoji were, the bytes were read as latin1 somewhere and written back as UTF-8. It usually happens when the export or import ran with the wrong connection character set.

Prevent it

Set the character set explicitly on both ends:

mysqldump -u DB_USER -p --single-transaction --default-character-set=utf8mb4 DB_NAME > DUMP_FILE.sql
mysql --default-character-set=utf8mb4 -u DB_USER -p DB_NAME < DUMP_FILE.sql

--default-character-set=utf8mb4 tells the client to talk to the server in full four-byte UTF-8, which includes emoji. Check the dump header with head -30 DUMP_FILE.sql | grep NAMES: you want SET NAMES utf8mb4. Also make sure wp-config.php has define( 'DB_CHARSET', 'utf8mb4' );.

Fix it

The best fix is to re-export from the original source with the flags above and import again. If the source is gone and the data in the database is double-encoded, a column can often be repaired by reversing the mistake. Test on a copy of the database first:

SELECT ID, post_title, CONVERT(CAST(CONVERT(post_title USING latin1) AS BINARY) USING utf8mb4) AS repaired FROM wp_posts WHERE post_title LIKE '%Ã%' LIMIT 20;

Run it in wp db query or phpMyAdmin. wp_ is your table prefix. The expression turns the wrongly decoded text back into its original bytes and reads them correctly as UTF-8. If the repaired column looks right, the same expression in an UPDATE fixes that column. If it shows question marks or blanks, the data was damaged differently; stop and restore from a clean source instead.

Advanced: import huge dumps faster and split them up

For multi-gigabyte databases, check what makes the dump big before you move it. Over SSH, in the site folder:

wp db size --tables

This lists every table with its size. Log tables from security, redirect or email plugins, wp_actionscheduler_logs and expired transients in wp_options are often the bulk. Excluding disposable log tables from the export with wp db export --exclude_tables=TABLE_ONE,TABLE_TWO can shrink the job a lot; TABLE_ONE and TABLE_TWO are names from the list. Database bloat itself is covered in FG-34.

To retry from the point of failure instead of starting over, export one table per file:

for t in $(wp db tables --all-tables-with-prefix); do wp db export "../tables/$t.sql" --tables="$t" --single-transaction; done

This needs a ../tables folder first (mkdir ../tables). wp db tables lists the tables, and the loop exports each to its own file. On the new server, import them one at a time; when one fails, you fix and rerun only that file. On your own server, raising innodb_buffer_pool_size and temporarily setting innodb_flush_log_at_trx_commit = 2 during the import also speeds things up considerably; set the latter back to 1 afterward for crash safety.

When to contact your host

Open a ticket when you need a server setting changed (max_allowed_packet, timeouts, strict mode), when you lack SSH, or when the database server refuses connections. Send the exact error line with its number, the line number in the dump, the dump size, the source and target database versions, the command you ran and a UTC timestamp. Mention whether the dump came from MySQL 8, since that explains collation and GTID errors at a glance. Once the import succeeds, our diagnostic tools help confirm the site responds correctly.