This section describes changes made in each version of the Sakila sample database.
Database objects now use
utf8. This change caused a
Specified key was too long; max key length is 767 byteserror in MySQL 5.6 for the
film.titlecolumn, which was declared as
VARCHAR(255). The actual maximum title length is 27 characters, so the column was redeclared as
VARCHAR(128)to avoid exceeding the maximum key length.
SET NAMES utf8mb4statement.
sakila-data.sqlwas converted from DOS (CRLF) line endings to Unix (LF) line endings.
address.locationcolumn is a
GEOMETRYcolumn that has a
SPATIALindex. As of MySQL 8.0.3,
SPATIALindexes are ignored unless the index spatial column has an
locationcolumn was changed to include an
SRID 0attribute for MySQL 8.0.3 and higher.
staff.passwordcolumn was declared as
VARCHAR(40) BINARY. This is use of
BINARYas shorthand in a character column declaration for specifying a
_bincollation, which is deprecated as of MySQL 8.0.17. The column was redeclared as what
BINARYis shorthand for, that is,
VARCHAR(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin.
rewards_report()stored procedure, the
min_dollar_amount_purchasedparameter was declared as
DECIMAL(10,2) UNSIGNED. Use of
DECIMALis deprecated as of MySQL 8.0.17. The parameter was redeclared without
film_not_in_stock()stored procedures used the
FOUND_ROWS()function, which is deprecated as of MySQL 8.0.17. In each procedure, the
FOUND_ROWS()query was replaced by a query that uses
COUNT(*)with the same
WHEREclauses as its associated query. This is more expensive than using
FOUND_ROWS()but produces the same result.
InnoDBprior to MySQL 5.6.10 to avoid table-creation failure in older versions. (However, we still recommend upgrading to MySQL 5.6.10 or higher.)
sakila.mwbfile for MySQL Workbench was updated per the preceding changes.
film_texttable, and its
FULLTEXTdefinition, now uses
InnoDB. If you use an older MySQL server version (5.6.10 and lower), we recommend upgrading MySQL. If you cannot upgrade, change the
ENGINEvalue for the
sakila-spatial-schema.sqlinto a single file by using MySQL version-specific comments.
Spatial data, such as
address.location, is inserted into the sakila database as of MySQL server 5.7.5 (when spatial indexing support was added to
InnoDBfull-text search is used as of MySQL server 5.6.10, when before
Added an additional copy of the Sakila example database that includes spatial data with the geometry data type. This is available as a separate download, and requires MySQL server 5.7.5 or later. To use this database, load the
sakila-spatial-schema.sqlfile rather than the
GROUP BYclause of the
film_listview definitions to be compatible with
ONLY_FULL_GROUP_BYSQL mode, which is enabled by default as of MySQL 5.7.5.
upd_filmtrigger definition to include changes to
Changed error handler for
inventory_held_by_customerfunction. Function now has an exit handler for
NOT FOUNDinstead of the more cryptic
Added template for new BSD license to schema and data files.
READS SQL DATAto the stored procedures and functions where appropriate to support loading on MySQL 5.1.
Fixed date-range issue in the
rewards_reportprocedure (thanks Goplat).
Fixed bug in
sales_by_storeview that caused the same manager to be listed for every store.
Fixed bug in
inventory_held_by_customerfunction that caused function to return multiple rows.
sakila-data.sqlfile to prevent it from interfering with data loading.
Optimized data file for loading (multiple-row
INSERTstatements, transactions). (Thanks Giuseppe)
Fixed error in
paymenttable loading script that caused infinitely increasing payment amounts.
sales_by_film_categoryviews, submitted by Jay Pipes.
rewards_reportstored procedure, submitted by Jay Pipes.
sakila-data.sqlfile to load data into sample database.
Foreign key added for
INT Auto_Increment, made into surrogate primary key, old primary key changed to
All tables have a
TIMESTAMPcolumn with traditional behavior (
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP).
actor_idis now a
address_idis now a
category_idis now a
city_idis now a
country_idis now a
customer_idis now a
customertable are now
customertable now has
customertable is now
customertable has a new
ON INSERTtrigger that enforces
create_datecolumn being set to
film.release_yearadded with type
film.original_language_idadded along with
languagetable. For foreign films that may have been subtitled.
film_category; allows for multiple categories per film.
Trigger added to
paymenttable to enforce that
payment_dateis set to
rental.rental_dateand is now
Trigger added to
rentaltable to enforce that
rental_dateis set to
film_listview updated to handle new
nicer_but_slower_film_listview updated to handle new