Setting Up the world_x Database

Table of Contents
1 Preface and Legal Notices ......................................................................................................... 1
2 Installation ................................................................................................................................. 2
3 History ...................................................................................................................................... 4

This document describes world_x sample database installation, structure, usage, and history.

The sample data used in the world_x database is Copyright Statistics Finland, http://www.stat.fi/
worldinfigures.

For legal information, see the Legal Notices.

For help with using MySQL, please visit the MySQL Forums, where you can discuss your issues with
other MySQL users.

Document generated on: 2022-12-22 (revision: 74777)

1 Preface and Legal Notices

This document describes world_x sample database installation, structure, usage, and history.

Legal Notices
Copyright © 2016, 2023, Oracle and/or its affiliates.

This software and related documentation are provided under a license agreement containing
restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly
permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate,
broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any
form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless
required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-
free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone
licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated
software, any programs embedded, installed or activated on delivered hardware, and modifications of
such programs) and Oracle computer documentation or other Oracle data delivered to or accessed by
U.S. Government end users are "commercial computer software" or "commercial computer software
documentation" pursuant to the applicable Federal Acquisition Regulation and agency-specific
supplemental regulations. As such, the use, reproduction, duplication, release, display, disclosure,
modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including any
operating system, integrated software, any programs embedded, installed or activated on delivered
hardware, and modifications of such programs), ii) Oracle computer documentation and/or iii) other
Oracle data, is subject to the rights and limitations specified in the license contained in the applicable
contract. The terms governing the U.S. Government's use of Oracle cloud services are defined by the
applicable contract for such services. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management
applications. It is not developed or intended for use in any inherently dangerous applications, including

1
 Setting Up the world_x Database

applications that may create a risk of personal injury. If you use this software or hardware in dangerous
applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and
other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any
damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be
trademarks of their respective owners.

Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC
trademarks are used under license and are trademarks or registered trademarks of SPARC
International, Inc. AMD, Epyc, and the AMD logo are trademarks or registered trademarks of Advanced
Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information about content,
products, and services from third parties. Oracle Corporation and its affiliates are not responsible
for and expressly disclaim all warranties of any kind with respect to third-party content, products,
and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle
Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to
your access to or use of third-party content, products, or services, except as set forth in an applicable
agreement between you and Oracle.

This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the
following terms:

You may create a printed copy of this documentation solely for your own personal use. Conversion
to other formats is allowed as long as the actual content is not altered or edited in any way. You shall
not publish or distribute this documentation in any form or on any media, except if you distribute the
documentation in a manner similar to how Oracle disseminates it (that is, electronically for download
on a Web site with the software) or on a CD-ROM or similar medium, provided however that the
documentation is disseminated together with the software on the same medium. Any other use, such
as any dissemination of printed copies or use of this documentation, in whole or in part, in another
publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/
or its affiliates reserve any and all rights to this documentation not expressly granted above.

Access to Oracle Support for Accessibility

Oracle customers that have purchased support have access to electronic support through My Oracle
Support. For information, visit
https://www.oracle.com/corporate/accessibility/learning-support.html#support-tab.

2 Installation
To enable MySQL users to perform manipulation of the world_x sample database using MySQL, the
data set is available as a set of four tables:

• country: Information about countries of the world.

• city: Information about some of the cities in those countries.

• countrylanguage: Languages spoken in each country.

• countryinfo: Information about each country represented as a JSON document.

The world_x.sql file containing the world_x database is available for download at https://
dev.mysql.com/doc/index-other.html. The file is provided as a compressed tar file or Zip archive.

Note

The world_x database uses features specific to X DevAPI, and thus requires
MySQL 5.7 or higher. A world database variant also exists that does not use X

2
 Setting Up the world_x Database

DevAPI features and may be used with MySQL 5.5 and higher. See Setting Up
the world Database.

To install the world_x sample database, follow these steps:

1. Download the installation archive to a temporary location such as C:\temp\ or /tmp/ and
unpack it. Unpacking the archive results in a directory named world_x-db containing files named
README.txt and world_x.sql.

2. Execute the world_x.sql script to create the database structure and insert the data using one of
the following methods:

• Using MySQL Shell:

$> mysqlsh -u root --sql --file C:\temp\world_x-db\world_x.sql

• Using the standard MySQL command-line client:

Connect to MySQL:

$> mysql -u root -p

From the mysql client, load the file:

mysql> SOURCE C:/temp/world_x-db/world_x.sql;

Enter your password when prompted. A non-root account can be used, provided that the account
has privileges to create new databases.

Replace the path to the world_x.sql file with the actual path on your system.

Note

On Windows, use slashes rather than backslashes when executing the

SOURCE command.

3. To confirm that the sample world_x database is installed correctly, execute the following
statements. You should see output similar to that shown here.

mysql> USE world_x;

Database changed

mysql> SHOW TABLES;

+-------------------+
| Tables_in_world_x |
+-------------------+
| city |
| country |
| countryinfo |
| countrylanguage |
+-------------------+
4 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM city;

+----------+
| COUNT(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.02 sec)

mysql> SELECT COUNT(*) FROM country;

+----------+
| COUNT(*) |
+----------+
| 239 |
+----------+

3
 Setting Up the world_x Database

1 row in set (0.00 sec)

You now have the world_x sample database installed.

Another popular sample database is the Sakila database. For additional details, see http://
dev.mysql.com/doc/sakila/en/.

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.