MySQL Bugs: #27840: Fixes for Sakila sample database script

Bug #27840	Fixes for Sakila sample database script
Submitted:	16 Apr 2007 0:34	Modified:	16 Apr 2007 8:26
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	All	OS:	Any
Assigned to:	Stefan Hinz	CPU Architecture:	Any
Tags:	sakila

Description:
I downloaded the Sakila sample database and tried to use it to populate various versions of MySQL servers.  I had trouble with anything earlier than version 5.  The problems are with storage engine specifications, version-specific SET commands, syntaxes like TIMESTAMP ON  UPDATE DEFAULT CURRENT_TIMESTAMP, non-backwards-compatible keywords like SCHEMA, foreign keys that don't have indexes, and of course version-specific features like views and triggers.

There may be some other problems too, but I've lost track...

How to repeat:
Try to load the Sakila scripts on any MySQL server before version 5.

Suggested fix:
I have taken the liberty of changing the files quite a bit.  I solved all of the problems above by splitting the files into multiple files containing subsets of the features and data, reorganizing the files in some cases (for example, the data file actually contained some trigger statements).  I used version-specific comments to control which statements run.

Apparently the SOURCE keyword can't be placed inside a version-specific comment.  To address this limitation I just SELECT instructions for the user inside the comment.

I added a README file with instructions on how to load the data.

The resulting files load without error for me on versions 3.23.58, 4.0.27, 4.1.0, 4.1.22, 5.1.15, and 5.2.3.

I will provide a tarball of the resulting data in a separate upload.

Re-worked files that load on versions 3.23.58 through 5.2.3

Attachment: sakila-db.tar.bz2 (application/x-bzip, text), 484.95 KiB.

Baron: Thanks for your contribution/modification! However, the Sakila DB was created specifically to highlight MySQL 5.0 features, and also we don't want to officially encourage the use of older MySQL versions, so we'll not make your version available on http://dev.mysql.com/doc. But that's exactly what MySQL Forge is for -- please upload your version (and the accompanying description) to http://forge.mysql.com. Thanks!