Sakila Sample Database  /  Sakila Change History

11 Sakila Change History

This section describes changes made in each version of the Sakila sample database.

Version 1.5

  • Fixed MySQL Bug #112552: Accented characters were missing from the address fields.

Version 1.4

  • Fixed MySQL Bug #112131: Made the film_text.film_id field unsigned to match the other film_id definitions.

  • Films without an actor were not returned by the film_list and nicer_but_slower_film_list views.

Version 1.3

  • Fixed MySQL Bug #106951: Accented characters were missing from the city and country fields; their values were updated using the world database. In addition, the acute accent character itself was also missing.

  • Fixed MySQL Bug #107158: Removed five rows in the payment table that had a null rental_id value.

Version 1.2

  • Database objects now use utf8mb4 rather than utf8. This change caused a Specified key was too long; max key length is 767 bytes error in MySQL 5.6 for the film.title column, 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.

  • sakila-schema.sql and sakila-data.sql include a SET NAMES utf8mb4 statement.

  • sakila-data.sql was converted from DOS (CRLF) line endings to Unix (LF) line endings.

  • The address.location column is a GEOMETRY column that has a SPATIAL index. As of MySQL 8.0.3, SPATIAL indexes are ignored unless the index spatial column has an SRID attribute. The location column was changed to include an SRID 0 attribute for MySQL 8.0.3 and higher.

  • The staff.password column was declared as VARCHAR(40) BINARY. This is use of BINARY as shorthand in a character column declaration for specifying a _bin collation, which is deprecated as of MySQL 8.0.17. The column was redeclared as what BINARY is shorthand for, that is, VARCHAR(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin.

  • In the rewards_report() stored procedure, the min_dollar_amount_purchased parameter was declared as DECIMAL(10,2) UNSIGNED. Use of UNSIGNED with DECIMAL is deprecated as of MySQL 8.0.17. The parameter was redeclared without UNSIGNED.

  • The film_in_stock() and 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 FROM and WHERE clauses as its associated query. This is more expensive than using FOUND_ROWS() but produces the same result.

  • The film_text table uses MyISAM rather than InnoDB prior 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.)

  • The sakila.mwb file for MySQL Workbench was updated per the preceding changes.

Version 1.1

  • Removed all MyISAM references. The film_text table, and its FULLTEXT definition, 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 ENGINE value for the film_text table to MyISAM in the sakila-schema.sql SQL file.

Version 1.0

  • Merged sakila-schema.sql and sakila-spatial-schema.sql into 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 InnoDB). Also, InnoDB full-text search is used as of MySQL server 5.6.10, when before MyISAM was used.

Version 0.9

  • 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.sql file rather than the sakila-schema.sql file.

  • Modified GROUP BY clause of the nicer_but_slower_film_list and film_list view definitions to be compatible with ONLY_FULL_GROUP_BY SQL mode, which is enabled by default as of MySQL 5.7.5.

Version 0.8

  • Corrected upd_film trigger definition to include changes to film_id values.

  • Added actor_info view.

  • Changed error handler for inventory_held_by_customer function. Function now has an exit handler for NOT FOUND instead of the more cryptic 1329.

  • Added template for new BSD license to schema and data files.

  • Added READS SQL DATA to the stored procedures and functions where appropriate to support loading on MySQL 5.1.

  • Fixed date-range issue in the rewards_report procedure (thanks Goplat).

Version 0.7

  • Fixed bug in sales_by_store view that caused the same manager to be listed for every store.

  • Fixed bug in inventory_held_by_customer function that caused function to return multiple rows.

  • Moved rental_date trigger to sakila-data.sql file to prevent it from interfering with data loading.

Version 0.6

  • Added film_in_stock stored procedure.

  • Added film_not_in_stock stored procedure.

  • Added inventory_help_by_customer stored function.

  • Added inventory__in_stock stored function.

  • Optimized data file for loading (multiple-row INSERT statements, transactions). (Thanks Giuseppe)

  • Fixed error in payment table loading script that caused infinitely increasing payment amounts.

Version 0.5

  • Added sales_by_store and sales_by_film_category views, submitted by Jay Pipes.

  • Added rewards_report stored procedure, submitted by Jay Pipes.

  • Added get_customer_balance stored procedure.

  • Added sakila-data.sql file to load data into sample database.

Version 0.4

  • Added password column to staff table (VARCHAR(40) BINARY DEFAULT NULL).

Version 0.3

  • Changed address.district to VARCHAR(20).

  • Changed customer.first_name to VARCHAR(45).

  • Changed customer.last_name to VARCHAR(45).

  • Changed customer.email to VARCHAR(50).

  • Added payment.rental_id column (an INT NULL column).

  • Foreign key added for payment.rental_id to rental.rental_id.

  • rental.rental_id added, INT Auto_Increment, made into surrogate primary key, old primary key changed to UNIQUE key.

Version 0.2

  • All tables have a last_update TIMESTAMP column with traditional behavior (DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP).

  • actor_id is now a SMALLINT.

  • address_id is now a SMALLINT.

  • category_id is now a TINYINT.

  • city_id is now a SMALLINT.

  • country_id is now a SMALLINT.

  • customer_id is now a SMALLINT.

  • first_name, last_name for customer table are now CHAR instead of VARCHAR.

  • customer table now has email CHAR(50).

  • create_date on customer table is now DATETIME (to accommodate last_update TIMESTAMP).

  • customer table has a new ON INSERT trigger that enforces create_date column being set to NOW().

  • film_id is now SMALLINT.

  • film.description now has DEFAULT NULL.

  • film.release_year added with type YEAR.

  • film.language_id and film.original_language_id added along with language table. For foreign films that may have been subtitled. original_language_id can be NULL, language_id is NOT NULL.

  • film.length is now SMALLINT.

  • film.category_id column removed.

  • New table: film_category; allows for multiple categories per film.

  • film_text.category_id column removed.

  • inventory_id is now MEDIUMINT.

  • payment_id is now SMALLINT.

  • payment.payment_date is now DATETIME.

  • Trigger added to payment table to enforce that payment_date is set to NOW() upon INSERT.

  • rental.rent_date is now rental.rental_date and is now DATETIME.

  • Trigger added to rental table to enforce that rental_date is set to NOW() upon INSERT.

  • staff_id is now TINYINT.

  • staff.email added (VARCHAR(50)).

  • staff.username added (VARCHAR(16)).

  • store_id is now TINYINT.

  • film_list view updated to handle new film_category table.

  • nicer_but_slower_film_list view updated to handle new film_category table.


PREV   HOME   UP  
Download this Manual
PDF (US Ltr) - 180.8Kb
PDF (A4) - 180.7Kb