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_x.sql file was changed to address several issues:

• The world_x database used utf8, 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_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;

  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.

• 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_x.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 column. That was done as follows, by altering FLOAT(M,D) to DECIMAL(M,D):

  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.