3 History

September 2016

Prior releases used mixed-case table names. Because MySQL Shell is case-sensitive, table names are changed to lowercase.

December 2019

The world.sql file was changed to address several issues:

• The world database used latin1, but MySQL as of 8.0 uses a default character set of utf8mb4. Converting the database to use utf8mb4 brings it up to date with MySQL 8.0, while retaining compatibility with older series.

  Conversion of the database to utf8mb4 was 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 utf8mb4 for X Protocol connections. Using such a connection to load a latin1-encoded world.sql file produces errors.

  Using mysqldump with options of --default-character-set=utf8mb4 to set the character set and --set-charset so the dump includes SET NAMES for that character set writes a utf8mb4-encoded dump file. Changing the encoding of world.sql to utf8mb4 permits 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, FLOAT(10,2).

  • Display width in integer column definitions; for example, INT(10).

  The world.sql file used both features, causing load warnings in MySQL 8.0.17 and higher.

  To avoid number of digits in FLOAT columns, it is necessary to manually alter the relevant columns. That was done as follows, by altering FLOAT(M,D) to DECIMAL(M,D):

  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.