Prior releases used mixed-case table names. Because MySQL Shell is case-sensitive, table names are changed to lowercase.
world.sql file was changed to address
latin1, 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; -- 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; -- re-enable foreign key checking SET SESSION foreign_key_checks=0;
MySQL Shell requires a character set of
utf8mb4for X Protocol connections. Using such a connection to load a
world.sqlfile produces errors.
Using mysqldump with options of
--default-character-set=utf8mb4to set the character set and
--set-charsetso the dump includes
SET NAMESfor that character set writes a
utf8mb4-encoded dump file. Changing the encoding of
utf8mb4permits it to be loaded without errors in MySQL Shell using X Protocol connections.
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.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 columns. That was done as follows, by altering
USE world; ALTER TABLE country MODIFY SurfaceArea DECIMAL(10,2) NOT NULL DEFAULT '0.00', MODIFY LifeExpectancy DECIMAL(3,1) DEFAULT NULL, MODIFY GNP DECIMAL(10,2) DEFAULT NULL, MODIFY GNPOld DECIMAL(10,2) DEFAULT NULL; 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.