Bug #64918 [mysql] Importing SQL files does not generate error
Submitted: 9 Apr 2012 14:41 Modified: 10 May 2012 15:11
Reporter: Roberto Caiola Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.1.62 OS:Windows
Assigned to: CPU Architecture:Any

[9 Apr 2012 14:41] Roberto Caiola
Description:
I have dozens SQL files to import by a command line, although some cannot be imported by command line (mysqldump), we have to do a copy/paste and execute the query.

Not sure why mysqldump cannot import and does not give any output error, altough if I copy/paste in a MySQL editor it runs properly (e.g. Navicat and HeidiSQL work ok).

After the mysqldump import with the command below:
- mysqldump does not generate any error
- DROP FUNCTION is executed and drops the function
- FUNCTION is not created.

I have several files with the same style of functions, some are created and others doesnt where this behavior happens... I cant find the problem.. very weird.

mysql -uroot -ppassword -h localhost dbqualistats < func_Period_dsTimekeepTractions_count.sql
____________________________________________________________________
DROP FUNCTION IF EXISTS func_Period_dsTimekeepTractions_value;

DELIMITER $$

CREATE FUNCTION func_Period_dsTimekeepTractions_value(
	p_date1_begin    VARCHAR(20),
	p_date1_end      VARCHAR(20),
	p_transport_type VARCHAR(10)
)
RETURNS REAL
BEGIN

SET 
	@d_start1 = p_date1_begin,
	@d_end1   = p_date1_end;
	
DO 
	@v1 := IFNULL((
		SELECT SUM(vw.IS_DELAY)
		FROM rpt_qualitydaily_vw_all_transports vw
		WHERE
			    vw.transport_date BETWEEN @d_start1 AND @d_end1
			AND vw.transport_type = p_transport_type
			AND vw.hour_real IS NOT NULL
	),0),
	@v2 := IFNULL((
		SELECT SUM(vw.IS_TRANSPORT)
		FROM rpt_qualitydaily_vw_all_transports vw
		WHERE
			    vw.transport_date BETWEEN @d_start1 AND @d_end1
			AND vw.transport_type = p_transport_type
			AND vw.hour_real IS NOT NULL
	),0),
	@r := (
		SELECT IF(@v2 = 0, 0, @v2 / (@v2 + @v1))
	)
	;
	
RETURN @r;

END
$$

DELIMITER ;
____________________________________________________________________

How to repeat:
Execute import by a command line.
[9 Apr 2012 14:57] Roberto Caiola
Instead of mysqldump I mean mysql
[9 Apr 2012 15:03] Valeriy Kravchuk
Please, check if the same problem still happens with a recent version, 5.1.62. 

I can not repeat it with 5.1.61, for example:

macbook-pro:5.1 openxs$ bin/mysql -uroot test < bug64918.sql 
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.61-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create function  func_Period_dsTimekeepTractions_value\G
*************************** 1. row ***************************
            Function: func_Period_dsTimekeepTractions_value
            sql_mode: STRICT_ALL_TABLES
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `func_Period_dsTimekeepTractions_value`(
	p_date1_begin    VARCHAR(20),
	p_date1_end      VARCHAR(20),
	p_transport_type VARCHAR(10)
) RETURNS double
BEGIN

SET 
	@d_start1 = p_date1_begin,
	@d_end1   = p_date1_end;
	
DO 
	@v1 := IFNULL((
		SELECT SUM(vw.IS_DELAY)
		FROM rpt_qualitydaily_vw_all_transports vw
		WHERE
			    vw.transport_date BETWEEN @d_start1 AND @d_end1
			AND vw.transport_type = p_transport_type
			AND vw.hour_real IS NOT NULL
	),0),
	@v2 := IFNULL((
		SELECT SUM(vw.IS_TRANSPORT)
		FROM rpt_qualitydaily_vw_all_transports vw
		WHERE
			    vw.transport_date BETWEEN @d_start1 AND @d_end1
			AND vw.transport_type = p_transport_type
			AND vw.hour_real IS NOT NULL
	),0),
	@r := (
		SELECT IF(@v2 = 0, 0, @v2 / (@v2 + @v1))
	)
	;
	
RETURN @r;

END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.02 sec)
[9 Apr 2012 16:05] Roberto Caiola
Upgraded to version: Server version: 5.1.62-community MySQL Community Server (GPL)

Some functions are not created but this one is still giving error! :)

Error as follows:
C:\aweb\xxxxxxxxx\www\reports>mysql -uroot -ppassword -h localhost dbqualista
ts  0<func_Period_dsTimekeepTractions_value.sql
ERROR 1054 (42S22) at line 1: Unknown column 'p_transport_type' in 'where clause
'

Changed the query to define variable "p_transport_type", although it is a parameter, it should find it...
_________________________________________________________________________
DROP FUNCTION IF EXISTS func_Period_dsTimekeepTractions_value;

DELIMITER $$

CREATE FUNCTION func_Period_dsTimekeepTractions_value(
	p_date1_begin    VARCHAR(20),
	p_date1_end      VARCHAR(20),
	p_transport_type VARCHAR(10)
)
RETURNS REAL
BEGIN

SET 
	@d_start1 = p_date1_begin,
	@d_end1   = p_date1_end,
	@p_transport_type = p_date1_end;
	
DO 
	@v1 := IFNULL((
		SELECT SUM(vw.IS_DELAY)
		FROM rpt_qualitydaily_vw_all_transports vw
		WHERE
			    vw.transport_date BETWEEN @d_start1 AND @d_end1
			AND vw.transport_type = @p_transport_type
			AND vw.hour_real IS NOT NULL
	),0),
	@v2 := IFNULL((
		SELECT SUM(vw.IS_TRANSPORT)
		FROM rpt_qualitydaily_vw_all_transports vw
		WHERE
			    vw.transport_date BETWEEN @d_start1 AND @d_end1
			AND vw.transport_type = @p_transport_type
			AND vw.hour_real IS NOT NULL
	),0),
	@r := (
		SELECT IF(@v2 = 0, 0, @v2 / (@v2 + @v1))
	)
	;
	
RETURN @r;

END
$$

DELIMITER ;
_________________________________________________________________________

Now the error is:

C:\aweb\xxxxxxxxx\www\reports>mysql -uroot -ppassword -h localhost dbqualista
ts  0<func_Period_dsTimekeepTractions_value.sql
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the ma
nual that corresponds to your MySQL server version for the right syntax to use n
DELIMITER' at line 1

Copy/paste in HeidiSQL works fine!

Very weird :)
[9 Apr 2012 16:14] Roberto Caiola
Ah! Found the issue!

The dump was using UNIX line feed.

With Notepad++ do:
Edit // EOL Conversion // Windows Format

Very weird behavior, but when you copied/paste it should have assumed CR+LF or CR for Mac, but in my case Windows with LF (UNIX format) it has messed up the import.
[9 Apr 2012 16:37] Roberto Caiola
Small correction:

Lines in the files were being terminated with CR (Mac format) and need to be converted to CR+LF (Windows Format).

Thank you for your assistance.
[10 Apr 2012 7:38] Valeriy Kravchuk
Please, upload the .sql file that gives you problems on Windows (with whatever line endings it has).
[10 Apr 2012 9:13] Roberto Caiola
SQL working file (CR+LF line feed)

Attachment: func_Period_OVL_TimekeepTractions_value.sql (application/octet-stream, text), 924 bytes.

[10 Apr 2012 9:13] Roberto Caiola
SQL ** NOT ** working file (CR line feed)

Attachment: func_Period_OVL_TimekeepTractions_value_(not_working_CR_line_feed).sql (application/octet-stream, text), 880 bytes.

[10 Apr 2012 9:18] Roberto Caiola
Good morning,

I have added 2 files, one that it is working and the doesn't.

Recap:
- Importing SQL files with CR line feed gives problem
- Importing SQL files with CR+LF line feed works OK under Windows

Note: I have not tested under Linux (CentOS, etc)
[11 Apr 2012 6:10] Valeriy Kravchuk
On Windows it just complains about missing table:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test < "c
:\tmp\work\func_Period_OVL_TimekeepTractions_value_(not_working_CR_line_feed).sq
l"
ERROR 1146 (42S02) at line 1: Table 'test.rpt_qualitydaily_vw_all_transports' do
esn't exist

Please, send the output of

show create table rpt_qualitydaily_vw_all_transports\G

for completeness.
[11 Apr 2012 10:46] Roberto Caiola
All files needed to test the non working import

Attachment: func_Period_OVL_TimekeepTractions_value.sql.zip (application/zip, text), 3.55 KiB.

[11 Apr 2012 10:57] Roberto Caiola
If you need any other information let me know.

To test:

- Edit "func_Period_[CREATE_FUNCTIONS].bat" with your credentials.

- The file "func_Period_OVL_TimekeepTractions_value.sql" is a non working file.

- With Notepad++ after doing Edit // EOL Conversion // Windows Format, it will import successfully

Let me know your results :)
[12 Apr 2012 7:55] Valeriy Kravchuk
I've got the following:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 -e "selec
t version()"
+------------------+
| version()        |
+------------------+
| 5.1.62-community |
+------------------+

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql --version
mysql  Ver 14.14 Distrib 5.1.62, for Win32 (ia32)

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test < "c
:\tmp\work\\[TABLE]_agencies.sql"

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test < "c
:\tmp\work\\[TABLE]_data_timekeeping.sql"

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test < "c
:\tmp\work\\[TABLE]_transport_timekeeping_types.sql"

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test < "c
:\tmp\work\rpt_QualityDaily_vw_all_transports.sql"

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test < "c
:\tmp\work\func_Period_OVL_TimekeepTractions_value.sql"
ERROR 1054 (42S22) at line 1: Unknown column 'p_transport_type' in 'where clause
'

So, I still fail to see the problem you initially described. Yes, 0x0D is used for line endings:

C:\...VL_TimekeepTractions_value.sql        DOS           880     Col 0       0%
0000000000:  44 52 4F 50 20 46 55 4E │ 43 54 49 4F 4E 20 49 46  DROP FUNCTION IF
0000000010:  20 45 58 49 53 54 53 20 │ 66 75 6E 63 5F 50 65 72   EXISTS func_Per
0000000020:  69 6F 64 5F 4F 56 4C 5F │ 54 69 6D 65 6B 65 65 70  iod_OVL_Timekeep
0000000030:  54 72 61 63 74 69 6F 6E │ 73 5F 76 61 6C 75 65 3B  Tractions_value;
0000000040:  0D 0D 44 45 4C 49 4D 49 │ 54 45 52 20 24 24 0D 0D  ♪♪DELIMITER $$♪♪
0000000050:  43 52 45 41 54 45 20 46 │ 55 4E 43 54 49 4F 4E 20  CREATE FUNCTION
0000000060:  66 75 6E 63 5F 50 65 72 │ 69 6F 64 5F 4F 56 4C 5F  func_Period_OVL_
0000000070:  54 69 6D 65 6B 65 65 70 │ 54 72 61 63 74 69 6F 6E  TimekeepTraction
0000000080:  73 5F 76 61 6C 75 65 28 │ 0D 09 70 5F 64 61 74 65  s_value(♪○p_date
...
[12 Apr 2012 15:56] Roberto Caiola
Hello,

Yes, after upgrading to version 5.1.62 the "error" where it did not display the error when importing the SQL files has disappeared.

I have rechecked randomly some of the files (they are dozens), with "CR" line feed and with this version it gives an error.

Although not very explicit, as this issue was about to drive me nuts... I was trying to find an error in SQL when it was an issue with the line feed.

I would considered this as bug, but with a another title resuming all the comments, where mysql does not import properly if the line feed is being done only with "CR" line feed.
[13 Apr 2012 7:03] Valeriy Kravchuk
I am a bit lost. For version 5.1.62 on Windows, do you still see any bug in mysql command line client?
[13 Apr 2012 12:08] Roberto Caiola
Yes, I see the same error as you do:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test < "c
:\tmp\work\func_Period_OVL_TimekeepTractions_value.sql"
ERROR 1054 (42S22) at line 1: Unknown column 'p_transport_type' in 'where clause
'

This error is not supposed to happen, it is because of the "CR" line feed, with "CR+LF" line feed it does not happen.
[8 May 2012 19:40] Sveta Smirnova
Thank you for the feedback.

I think technically this is not a bug if caused by using non-standard for your operating system line breaks.
[8 May 2012 23:36] Roberto Caiola
I do not agree.. I see a portability issue in here.

So, if a "Macintosh" user creates a SQL file with "CR" line feed.
"Windows" and "Linux" users cannot use that file...

They have to convert it, to be able to use it...

And I not mentioning the really hard to find issue, where you are trying to find SQL syntax error when there is none!
[10 May 2012 15:11] Sveta Smirnova
Thank you for update.

I think this can be converted to feature request: add option --line-endings=UNIX|WIN|MAC to mysql command line client.