MySQL Bugs: #12344: statements which work in comand line, don't work with 'source' comand in mysql

Bug #12344	statements which work in comand line, don't work with 'source' comand in mysql
Submitted:	3 Aug 2005 10:35	Modified:	29 Aug 2005 10:14
Reporter:	Gleb Paharenko	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.10	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When I'm executing statements in console in 'mysql' command line client they work perfectly,
but the same statements which were written to file and executed with 'source' command
from mysql comman line client produce an error. 

How to repeat:
The contents of pr.sql file:

DROP FUNCTION IF EXISTS nextval;
DELIMITER //
CREATE FUNCTION nextval (seqname CHAR(20))
RETURNS INT UNSIGNED
BEGIN
 DECLARE nextid INT UNSIGNED;
 INSERT INTO sequences VALUES (seqname,1)
  ON DUPLICATE KEY UPDATE val=LAST_INSERT_ID(val+1);
 SELECT val INTO nextid FROM sequences ORDER BY val DESC LIMIT 1;
 RETURN nextid;
END
//
DELIMITER ;

Execution from command line:
mysql> DROP FUNCTION IF EXISTS nextval;
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER //
mysql> CREATE FUNCTION nextval (seqname CHAR(20))
    -> RETURNS INT UNSIGNED
    -> BEGIN
    ->  DECLARE nextid INT UNSIGNED;
    ->  INSERT INTO sequences VALUES (seqname,1)
    ->   ON DUPLICATE KEY UPDATE val=LAST_INSERT_ID(val+1);
    ->  SELECT val INTO nextid FROM sequences ORDER BY val DESC LIMIT 1;
    ->  RETURN nextid;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Executing with source command:
mysql> source pr.sql
Query OK, 0 rows affected (0.01 sec)

ERROR 1054 (42S22): Unknown column 'seqname' in 'field list'

Suggested fix:
I think the same SQL code should't depend on method which is used to execute it ( typing in console by hand or 'source' command). Fix this or provide more explanations in the manual.

Please, post the exact definition of your sequences table.

What version fo mysql (command line client) do you use? (mysql --version command prints it).

mysql> show create table sequences\G;
*************************** 1. row ***************************
       Table: sequences
Create Table: CREATE TABLE `sequences` (
  `name` char(20) NOT NULL,
  `val` int(10) unsigned default NULL,
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

[gleb@blend mysql-debug-5.0.10-beta-linux-i686]$ ./bin/mysql --version
./bin/mysql  Ver 14.12 Distrib 5.0.10-beta, for pc-linux-gnu (i686) using readline 5.0

I understood the problem. It is not a bug, really. According to the documentation (http://dev.mysql.com/doc/mysql/en/batch-commands.html, http://dev.mysql.com/doc/mysql/en/mysql-commands.html) source command is used to execute SQL statements, and "delimiter" is NOT a SQL statement (it is mysql command), and so should not be included into the .sql file to be "sourced".

Simply split your file into 2 (one for drop, and one for create) and execute SQL from each one of them using source command (but set the appropriate delimiter before each source command), or just use your unmodified pr.sql from the command line:

$ bin/mysql -u root < pr.sql
$ bin/mysql -u root -S /home/openxs/mysql5.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.11-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show function status;
+------+---------+----------+----------------+---------------------+------------
---------+---------------+---------+
| Db   | Name    | Type     | Definer        | Modified            | Created
         | Security_type | Comment |
+------+---------+----------+----------------+---------------------+------------
---------+---------------+---------+
| test | nextval | FUNCTION | root@localhost | 2005-08-03 15:07:49 | 2005-08-03
15:07:49 | DEFINER       |         |
+------+---------+----------+----------------+---------------------+------------
---------+---------------+---------+
1 row in set (0.00 sec)

This is how it works. I can only ask documentation team to add the appropriate explicit note.

Thank you very much for good explanations. In my opinion some notes in the manual
about that we can't use DELIMITER (and other 'mysql' commands) in SQL scripts for 'source'
command will prevent users from reporting such silly bugs :)

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

see bug#11523