| Bug #25932 | Mysql DELIMITER in 5.0 cannot be set to ";;" | ||
|---|---|---|---|
| Submitted: | 29 Jan 2007 22:08 | Modified: | 29 Jan 2007 23:34 |
| Reporter: | Lebert (Lee) Thompson | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0.24-max-log | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | ;; | ||
[29 Jan 2007 23:22]
MySQL Verification Team
Thank you for the bug report. I was unable to repeat with 5.0.27 on Windows
and current source server on Linux. Could you please upgrade and try again.
Thanks in advance.
C:\build\5.0\bin>mysql -uroot -P3307
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.27-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> source c:/a/sakila-schema.sql
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.17 sec)
Query OK, 0 rows affected (0.16 sec)
Query OK, 0 rows affected (0.09 sec)
Query OK, 0 rows affected (0.09 sec)
Query OK, 0 rows affected (0.09 sec)
Query OK, 0 rows affected (0.16 sec)
Query OK, 0 rows affected (0.17 sec)
Query OK, 0 rows affected (0.17 sec)
Query OK, 0 rows affected (0.14 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.17 sec)
Query OK, 0 rows affected (0.11 sec)
Query OK, 0 rows affected (0.19 sec)
Query OK, 0 rows affected (0.17 sec)
Query OK, 0 rows affected (0.17 sec)
Query OK, 0 rows affected (0.17 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show triggers from sakila\G
*************************** 1. row ***************************
Trigger: ins_film
Event: INSERT
Table: film
Statement: BEGIN
INSERT INTO film_text (film_id, title, description)
VALUES (new.film_id, new.title, new.description);
END
Timing: AFTER
Created: NULL
sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USE
R
Definer: root@localhost
*************************** 2. row ***************************
Trigger: upd_film
Event: UPDATE
Table: film
Statement: BEGIN
IF (old.title != new.title) or (old.description != new.description)
THEN
UPDATE film_text
SET title=new.title,
description=new.description,
film_id=new.film_id
WHERE film_id=old.film_id;
END IF;
END
Timing: AFTER
Created: NULL
sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USE
R
Definer: root@localhost
*************************** 3. row ***************************
Trigger: del_film
Event: DELETE
Table: film
Statement: BEGIN
DELETE FROM film_text WHERE film_id = old.film_id;
END
Timing: AFTER
Created: NULL
sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USE
R
Definer: root@localhost
3 rows in set (0.06 sec)
mysql>
[29 Jan 2007 23:34]
Lebert (Lee) Thompson
I have attempted this on 4 servers with 4 instanes each and the same error might be a bug in 5.0.24-max-log
[root@leftblank lthompson]# mysql -uroot -p -S /tmp/mysql-203.sock < sakila-schema.sql
Enter password:
ERROR at line 182: DELIMITER must be followed by a 'delimiter' character or string
ERROR 1064 (42000) at line 183: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO film_text (film_id, title, description)
VALUES (new.film_id,' at line 2
Description: In the sakila test database you are setting the DELIMITER to ";;" (minus the quotes double semi-colon) this will not allow the schema to get created. ERROR: DELIMITER must be followed by a 'delimiter' character or string ERROR: No query specified ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO film_text (film_id, title, description) VALUES (new.film_id,' at line 2 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 ERROR: No query specified ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE film_text SET title=new.title, description=ne' at line 3 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1 I have changed the delimter to $$ and created the schema and sucsesfully imorted the sakila database. How to repeat: import the sakila schema into a 5.0 server you will get the error s there. Suggested fix: I have changed the delimter to $$ and created the schema and sucsesfully imorted the sakila database.