xxxxxxxxxx
Open the sql file in your text editor;
1. Search: utf8mb4_unicode_ci Replace: utf8_general_ci (Replace All)
2. Search: utf8mb4_unicode_520_ci Replace: utf8_general_ci (Replace All)
3. Search: utf8mb4 Replace: utf8 (Replace All)
Save and upload!
xxxxxxxxxx
Replace the below string:
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
with:
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
xxxxxxxxxx
find:
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
replace with:
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
in your .sql file
xxxxxxxxxx
open your .sql file
replace from
utf8mb4_0900_ai_ci
To
utf8mb4_0900_ci
import error
xxxxxxxxxx
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
in your .sql file, and swapping it with
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
comment out containing this line code
GTID_PURGED
xxxxxxxxxx
# Edit .sql file
#Search table collation
#FROM:
ENGINE=InnoDB AUTO_INCREMENT=429 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
#TO:
ENGINE=InnoDB;
xxxxxxxxxx
sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' backup.sql
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql
xxxxxxxxxx
# I believe this error is caused because the local server
# and live server are running different versions of MySQL.
# To solve this:
# 1- Open the sql file in your text editor
# 2- Find and replace all utf8mb4_unicode_520_ci with utf8mb4_unicode_ci
# 3- Save and upload to a fresh mySql db
xxxxxxxxxx
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
replace with
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;