sakila example database problems

Asked by KentBozlinski on 2010-01-25

I am having trouble getting the sakila database to install properly and was wondering if anyone could help me with the proper changes to the sql files.

running sakila-schema.sql returned the set of errors below. I have not run sakila-data.sql yet.

Kent B.

Query OK, 0 rows affected (0 sec)

Query OK, 0 rows affected (0 sec)

ERROR 1193 (HY000): Unknown system variable 'SQL_MODE'
Query OK, 0 rows affected (0 sec)

Query OK, 1 row affected (0 sec)

Database changed
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(45) NOT NULL,
  ' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  address VARCHAR(50) NOT NULL,
  add' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(25) NOT NULL,
  last_up' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  city VARCHAR(50) NOT NULL,
  countr' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  country VARCHAR(50) NOT NULL,
  las' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  store_id TINYINT UNSIGNED NOT NULL,' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  desc' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SMALLINT UNSIGNED NOT NULL,
  film_id SMALLINT UNSIGNED NOT NULL,
  last_update ' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SMALLINT UNSIGNED NOT NULL,
  category_id TINYINT UNSIGNED NOT NULL,
  last_upda' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SMALLINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  PRIMARY ' at line 6
Query OK, 0 rows affected (0 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    INSERT INTO fil' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
    IF (old.title !' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
    DELETE FROM fil' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  film_id SMALLINT UNSIGNED NOT NULL' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(20) NOT NULL,
  last_updat' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id SMALLINT UNSIGNED NOT N' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'MEDIUMINT UNSIGNED NOT NULL,
  customer_id SMALLINT UNSIGNED NOT NULL,
  return_' at line 8
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(45) NOT NULL,
  l' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  manager_staff_id TINYINT UNSIGNED NO' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'VIEW customer_list
AS
SELECT cu.customer_id AS ID, CONCAT(cu.first_name, _utf8' ' at line 5
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'VIEW film_list
AS
SELECT film.film_id AS FID, film.title AS title, film.descript' at line 5
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'VIEW nicer_but_slower_film_list
AS
SELECT film.film_id AS FID, film.title AS tit' at line 5
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'VIEW staff_list
AS
SELECT s.staff_id AS ID, CONCAT(s.first_name, _utf8' ', s.las' at line 5
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'VIEW sales_by_store
AS
SELECT
CONCAT(c.city, _utf8',', cy.country) AS store
, CO' at line 5
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'VIEW sales_by_film_category
AS
SELECT
c.name AS category
, SUM(p.amount) AS tota' at line 8
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW actor_info
AS
SELECT
a.actor_id,
' at line 5
Query OK, 0 rows affected (0 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'PROCEDURE rewards_report (
    IN min_monthly_purchases TINYINT UNSIGNED
    , I' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FUNCTION get_customer_balance(p_customer_id INT, p_effective_date DATETIME) RETU' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count IN' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'PROCEDURE film_not_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_coun' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FUNCTION inventory_held_by_customer(p_inventory_id INT) RETURNS INT
READS SQL DA' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FUNCTION inventory_in_stock(p_inventory_id INT) RETURNS BOOLEAN
READS SQL DATA
B' at line 1
ERROR 1193 (HY000): Unknown system variable 'SQL_MODE'
Query OK, 0 rows affected (0 sec)

Query OK, 0 rows affected (0 sec)

Question information

Language:
English Edit question
Status:
Answered
For:
Drizzle Edit question
Assignee:
No assignee Edit question
Last query:
2010-01-25
Last reply:
2010-04-11
fmpwizard (diego-fmpwizard) said : #1

Hi Kent,

The errors are due to Drizzle not supporting all the features MySQL
supports (and one column type rename)

See below:

On Mon, Jan 25, 2010 at 3:57 AM, KentBozlinski
<email address hidden> wrote:

> ERROR 1193 (HY000): Unknown system variable 'SQL_MODE'

Drizzle does not have sql modes, most (almost all) mysql warnings are
converted to errors)

> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
>  first_name VARCHAR(45) NOT NULL,
>  ' at line 6

Change all the smallint . tinyint, etc for INTEGER

>
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
>    INSERT INTO fil' at line 1

Triggers and store procedures were removed from the "core / kernel" of
the server, so you cannot use them right now. But they will be able to
be implemented as plugins (not sure if work on this area has started
yet)

> Query OK, 0 rows affected (0 sec)

Thanks

   -Diego

>
>
>
> --
> You received this question notification because you are a member of
> Drizzle-developers, which is an answer contact for Drizzle.
>

--
Diego Medina
Web Developer
http://www.fmpwizard.com

Jay Pipes (jaypipes) said : #2

Please note that the Sakila sample database is NOT released under a free open source license. Please do not redistribute any changed versions of the sakila sample database.

I really need to get a sample database script up on drizzle.org/wiki/ for folks to experiment with...

Jay Pipes (jaypipes) said : #3

Forget my last message. Sakila DB is released under the new BSD license now.

Vahaber (alphagoblin4) said : #4

who delete my last message???

Can you help with this problem?

Provide an answer of your own, or ask KentBozlinski for more information if necessary.

To post a message you must log in.