August 17, 2025

Fixing MySQL 8 Import Error: NO_AUTO_CREATE_USER in sql_mode

When importing a MySQL 5.7 (or older) dump into MySQL 8.0, the process can fail with:

ERROR 1231 (42000) at line NNNN: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

1 2	ERROR 1231 (42000) at line NNNN: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

This article explains why it happens and shows safe, repeatable fixes you can apply immediately.

TL;DR (One‑liner fix)

Strip the deprecated token from the dump as you import it:

sed -E 's/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\1/g; s/,,+/,/g' db.sql \
| mysql -h DB_HOST -u DB_USER -p DB_NAME

sed -E 's/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\1/g; s/,,+/,/g' db.sql \

| mysql -h DB_HOST -u DB_USER -p DB_NAME

Replace DB_HOST, DB_USER, and DB_NAME to match your environment.

Why this error appears

Older dumps often contain version‑gated statements like:

/*!50003 SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;

1 2	/!50003 SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' /;

NO_AUTO_CREATE_USER was removed in MySQL 8.0. Attempting to include it in sql_mode causes MySQL to abort the import with ERROR 1231.

The behavior controlled by NO_AUTO_CREATE_USER no longer exists in 8.0 (user creation and grants were redesigned), so it’s safe to remove the token.

Solution A — Clean the dump, then import (recommended)

Locate the offending token

grep -n "NO_AUTO_CREATE_USER" db.sql

1 2	grep -n "NO_AUTO_CREATE_USER" db.sql

Remove it everywhere (preserve the rest of the sql_mode list and clean up commas):

sed -E 's/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\1/g; s/,,+/,/g' db.sql > db.fixed.sql

1 2	sed -E 's/(^\|,)[[:space:]]NO_AUTO_CREATE_USER([[:space:]],\|$)/\1/g; s/,,+/,/g' db.sql > db.fixed.sql

Import the cleaned dump

mysql -h DB_HOST -u DB_USER -p DB_NAME < db.fixed.sql

1 2	mysql -h DB_HOST -u DB_USER -p DB_NAME < db.fixed.sql

Why this is preferred

Keeps the rest of your dump intact.
Produces an artifact (db.fixed.sql) you can archive for auditability.

Solution B — One‑pass pipeline (fastest)

If you don’t need to keep a modified file on disk:

sed -E 's/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\1/g; s/,,+/,/g' db.sql \
| mysql -h DB_HOST -u DB_USER -p DB_NAME

sed -E 's/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\1/g; s/,,+/,/g' db.sql \

| mysql -h DB_HOST -u DB_USER -p DB_NAME

Optional: log errors while piping

set -o pipefail \
&& sed -E 's/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\1/g; s/,,+/,/g' db.sql \
| mysql -h DB_HOST -u DB_USER -p DB_NAME 2> import.err

set -o pipefail \

&& sed -E 's/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\1/g; s/,,+/,/g' db.sql \

| mysql -h DB_HOST -u DB_USER -p DB_NAME 2> import.err

Solution C — Fix it at the source (best long‑term)

If you can re‑create the dump from the source server (e.g., MySQL 5.7), remove the token before dumping so the preamble won’t include it:

-- On the source server with sufficient privileges
SET GLOBAL sql_mode = REPLACE(@@GLOBAL.sql_mode, 'NO_AUTO_CREATE_USER', '');

-- On the source server with sufficient privileges

SET GLOBAL sql_mode = REPLACE(@@GLOBAL.sql_mode, 'NO_AUTO_CREATE_USER', '');

Then re‑generate the dump. Future imports into MySQL 8.0 won’t fail on sql_mode.

Note: Only adjust sql_mode if you understand the implications on the source system. Alternatively, generate a dump and post‑process it with sed as shown above without touching the running instance.

Can I just ignore the error with `--force`?

You can do:

mysql --force -h DB_HOST -u DB_USER -p DB_NAME < db.sql

1 2	mysql --force -h DB_HOST -u DB_USER -p DB_NAME < db.sql

…but it’s noisy and can mask other issues. Cleaning the dump is safer and typically just as quick.

Verifying the import

After the import completes:

Confirm NO_AUTO_CREATE_USER is not present

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode; -- Should not include NO_AUTO_CREATE_USER

1 2	SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode; -- Should not include NO_AUTO_CREATE_USER

Spot‑check key tables

SELECT COUNT(*) FROM important_table; -- Replace with your tables

1 2	SELECT COUNT(*) FROM important_table; -- Replace with your tables

Scan for other removed modes (rare, but good hygiene)

grep -nE "sql_mode|NO_ZERO_DATE|NO_ZERO_IN_DATE" db.sql

1 2	grep -nE "sql_mode\|NO_ZERO_DATE\|NO_ZERO_IN_DATE" db.sql

FAQ

Does removing NO_AUTO_CREATE_USER change behavior on MySQL 8?
No. The option was removed because its behavior became irrelevant in 8.0. Removing it has no effect on 8.0.

Will this token appear multiple times?
Yes, it can. The sed expressions above remove it safely wherever it appears and tidy up stray commas.

What about MariaDB?
Many MariaDB versions still accept NO_AUTO_CREATE_USER. This article focuses on MySQL 8.0 compatibility.

Reusable snippet

Drop this in your toolbox to clean any MySQL dump for 8.0‑compatibility regarding this token:

clean_no_auto_create_user() {
  local infile="$1" outfile="${2:-/dev/stdout}"
  sed -E "s/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\\1/g; s/,,+/,/g" "$infile" > "$outfile"
}

# Example usage:
# clean_no_auto_create_user db.sql db.fixed.sql
# mysql -h DB_HOST -u DB_USER -p DB_NAME < db.fixed.sql

clean_no_auto_create_user() {

local infile="$1" outfile="${2:-/dev/stdout}"

sed -E "s/(^|,)[[:space:]]*NO_AUTO_CREATE_USER([[:space:]]*,|$)/\\1/g; s/,,+/,/g" "$infile" > "$outfile"

}

# Example usage:

# clean_no_auto_create_user db.sql db.fixed.sql

# mysql -h DB_HOST -u DB_USER -p DB_NAME < db.fixed.sql

Conclusion

If your MySQL import fails on NO_AUTO_CREATE_USER, don’t panic. Remove the deprecated token, preserve the rest of sql_mode, and proceed. The quick sed cleanup is safe, auditable, and works whether you choose to preprocess the dump or stream the cleaned content directly into mysql.