MySQL Bugs: #19799: delimiter command not working correctly when sourcing a sql file

Bug #19799	delimiter command not working correctly when sourcing a sql file
Submitted:	14 May 2006 8:04	Modified:	11 Dec 2006 8:48
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.0.21-community-nt	OS:	Windows (win32 - XP SP2)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
The mysql client is handling the command 'delimiter' differently depending on whether it is entered directly, or sourced from a sql file.  When sourced, the chosen delimiter functions as expected while the script is running, but when the script ends the delimiter is no longer the same.  It is unknown what it becomes after the script ends.

How to repeat:
Enter the following into a mysql client session. This works as expected:

mysql> delimiter //
mysql> show databases //
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql>

Now create a sql file containing this single statement:
#FILE
delimiter //
#EOF

Source the file and attempt to use the new delimiter.  It is unclear what if anything it has been set to:

mysql> \. c:/foo.sql
mysql> show databases //
    -> \c
mysql> show databases;
    -> \c
mysql> show databases //;
    -> \c

Interestingly if you put these two statements together in the file it works, so while the script is being run, the delimiter is set as expected.  Only after the script ends does the errant behavior begin:
#FILE
delimiter //
show databases //
#EOF

Source the file:
mysql> \. c:/foo.sql
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> show databases //
    -> \c
mysql>

I have experienced this problem as well.
When I save my "source" file in DOS format (i.e. carriage-return plus linefeed for end of line), the DELIMITER fails after executing the script.
When I save my "source" file in unix format (just linefeed for end of line), the DELIMITER works correctly after executing the script.

I am using version:
mysql-standard-5.0.21-linux-i686-glibc23

There is a workaround.  Simply manually reset the delimiter after running the script file.  Then, the delimiter no longer includes the extra carriage-return, and mysql will work as expected.

I patched a local version of 5.0.22 to fix this problem. It looks like intern_read_line in client/readline.cpp was only honoring line feeds as the end of line character. The following diff fixes this my normalizing CRLF to a space followed by a linefeed. I'm not sure if this is production safe so use it at your own risk. The DELIMITER issue was just driving me insane...

--- readline-orig.cpp	2006-05-25 10:56:44.000000000 -0400
+++ readline.cpp	2006-10-17 08:47:31.664113600 -0400
@@ -195,18 +195,49 @@
   buffer->start_of_line=buffer->end_of_line;
   for (;;)
   {
+    int expectNL = 0, foundEnd = 0;
     pos=buffer->end_of_line;
-    while (*pos != '\n' && *pos)
-      pos++;
+    while (!foundEnd)
+    {
+      switch (*pos)
+      {
+      case '\r':
+        expectNL = 1;
+        pos++;
+        break;
+      case '\n':
+        if (expectNL)
+        {
+          /* Got a CR as last character, set it to <space> */
+          *(pos-1) = ' ';
+        }
+        /*FALLTHRU*/
+      case '\0':
+        foundEnd = 1;
+        break;
+      default:
+        if (expectNL)
+        {
+          /* CR without NL, back up and we are done */
+          --pos;
+          foundEnd = 1;
+        }
+        else
+        {
+          pos++;
+        }
+        break;
+      }
+    }
     if (pos == buffer->end)
     {
       if ((uint) (pos - buffer->start_of_line) < buffer->max_size)
       {
-	if (!(length=fill_buffer(buffer)) || length == (uint) -1)
-	  DBUG_RETURN(0);
-	continue;
+        if (!(length=fill_buffer(buffer)) || length == (uint) -1)
+          DBUG_RETURN(0);
+        continue;
       }
-      pos--;					/* break line here */
+      pos--; /* break line here */
     }
     buffer->end_of_line=pos+1;
     *out_length=(ulong) (pos + 1 - buffer->eof - buffer->start_of_line);

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15670

ChangeSet@1.2328, 2006-11-21 21:10:02-05:00, iggy@rolltop.ignatz42.dyndns.org +4 -0
  Bug#19799 delimiter command not working correctly when sourcing a sql file
  - Client side readline functions unconditionally search for Unix '\n' line
  endings. In this case, the delimiter statement was set to '//\r' instead 
  of the intended '//'. When removing the '\n' check for and remove 
  preceeding '\r' character as well.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15678

ChangeSet@1.2330, 2006-11-22 01:27:06-05:00, iggy@rolltop.ignatz42.dyndns.org +2 -0
  Bug#19799 delimiter command not working correctly when sourcing a sql file
  - Use more appropriate test case.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix for 5.0.32 and 5.1.15.