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: | |
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
[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.