Prior releases used mixed-case table names. Because MySQL Shell is case-sensitive, table names are changed to lowercase.
world_x.sql file was changed to address
utf8, but MySQL as of 8.0 uses a default character set of
utf8mb4. Converting the database to use
utf8mb4brings it up to date with MySQL 8.0, while retaining compatibility with older series.
Conversion of the database to
utf8mb4was done as follows:
USE world_x; -- turn off foreign key checking; otherwise, the ALTER TABLE -- statements fail with incompatible foreign key errors. SET SESSION foreign_key_checks=0; -- convert database and tables to utf8mb4 ALTER DATABASE world CHARACTER SET utf8mb4; ALTER TABLE city CONVERT TO CHARACTER SET utf8mb4; ALTER TABLE country CONVERT TO CHARACTER SET utf8mb4; ALTER TABLE countrylanguage CONVERT TO CHARACTER SET utf8mb4; ALTER TABLE countryinfo CONVERT TO CHARACTER SET utf8mb4; ALTER TABLE countryinfo MODIFY `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL; -- re-enable foreign key checking SET SESSION foreign_key_checks=1;
MySQL 8.0.17 deprecates these features:
Number of digits in floating-point column definitions; for example,
Display width in integer column definitions; for example,
world_x.sqlfile used both features, causing load warnings in MySQL 8.0.17 and higher.
To avoid number of digits in
FLOATcolumns, it is necessary to manually alter the relevant column. That was done as follows, by altering
USE world_x; ALTER TABLE countrylanguage MODIFY Percentage DECIMAL(4,1) NOT NULL DEFAULT '0.0';
To produce integer column definitions without display widths in the dump file, it suffices to use mysqldump from MySQL 8.0.19 or higher.