MySQL Bugs: #55496: During import of dump file, line numbers are reported incorrectly in error desc

Bug #55496	During import of dump file, line numbers are reported incorrectly in error desc
Submitted:	23 Jul 2010 3:24	Modified:	29 Nov 2010 17:44
Reporter:	Scott Rosenberg	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	Ver 14.14 Distrib 5.1.48, for Win64	OS:	Windows (2008 RC2)
Assigned to:		CPU Architecture:	Any

Description:
When restoring data from a SQL dump file of multiple tables using MySQL Workbench, the following command is issued with the subsequent output:

Restoring D:\temp\mysql_data_dump_from_backup(non-prod)\table_data\bigfiles\joined.sql

Running: mysql.exe --defaults-extra-file="c:\users\scott~1.ros\appdata\local\temp\2\tmpmklgbl"   --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments < "D:\\temp\\mysql_data_dump_from_backup(non-prod)\\table_data\\bigfiles\\joined.sql"

ERROR 1062 (23000) at line 1261: Duplicate entry '1' for key 'PRIMARY'

When the file is viewed in a text editor (e.g. EditPad Lite), line 1261 is in the middle of a comment block.

This makes troubleshooting import errors nearly impossible.

How to repeat:
1) Backup multiple tables into one file by initiating a dump, e.g. using the MySQL admin tool.  -or-  Backup multiple tables into separate files by using MySQL Workbench to dump each table individually, then join those dump files into one unified text file (this is because if the dump file DOES NOT INCLUDE TABLE DEFINITIONS [data only], the MySQL Workbench import tool does not see a directory full of dump files as dump files.. argh).

2) Attempt to restore said files using the mysql.exe command issued by MySQL workbench when importing a dump file.

Suggested fix:
I imagine the problem is that the line counter is not incrementing for comments and/or blank lines, causing an offset.

The line counter, for the purpose of issuing an error message, should count all <LF>s as lines, regardless of their content.

There is even simpler way to repeat, one just needs some file with SQL statement and comments:

macbook-pro:5.1 openxs$ bin/mysql -uroot test < /tmp/t.sql
ERROR 1064 (42000) at line 4: 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 'from
dual' at line 6
macbook-pro:5.1 openxs$ cat /tmp/t.sql
/* comment */
/* one more comment */

select 1,
-- comment
2,
/* comment */
3,
from
dual;

Surely line number is reported wrongly, as "from" keyword is on line 9. But there is a solution since 5.1.23, --comments option:

macbook-pro:5.1 openxs$ bin/mysql -uroot test --comments < /tmp/t.sql
ERROR 1064 (42000) at line 1: 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 'from
dual' at line 9

Read http://dev.mysql.com/doc/refman/5.1/en/mysql-command-options.html#option_mysql_comments.

So, I'd say there is, maybe, a topic for a Workbench feature request (to use --comments when importing, if it does not use it yet), but mysql command line client itself is already perfect :)

Please see the command above that Workbench was executing;  it does include the --comments flag, which as I gleaned from the docs means that all lines including comments are sent to the server.

If I attempt to repeat your example, I receive the same results as you.

However, it seems to me that the --comments flag has the opposite function as defined in the docs when executing the example below.

With this test file named testdump.sql (the fraud_scores table does exist, and the column count is purposefully wrong)...

USE mastersitedb;
/* Test line number */
-- Error should be on line 4
INSERT INTO `fraud_scores` VALUES (1,4464,4,'2009-02-23 23:00:04',0)

...I execute the following commands and observe their responses...

mysql.exe --host=localhost --user=root --port=3306 --default-character-s
et=utf8 --comments < "D:\\temp\\testdump.sql"
ERROR 1136 (21S01) at line 2: Column count doesn't match value count at row 1

mysql.exe --host=localhost --user=root --port=3306 --default-character-s
et=utf8 < "D:\\temp\\testdump.sql"
ERROR 1136 (21S01) at line 4: Column count doesn't match value count at row 1

mysql.exe --host=localhost --user=root --port=3306 --default-character-s
et=utf8 --skip-comments < "D:\\temp\\testdump.sql"
ERROR 1136 (21S01) at line 4: Column count doesn't match value count at row 1

That example more accurately matches my use-case, as comments are not interspersed within statements in my files.

Is my expectation of the output incorrect?

. . .

And considering an application to be perfect is the quickest path to obsolescence.

I wanted to add that I tried my original file again from the command line, both with and w/o the --comments flag, and even more strangely the error was reported as occurring on the same line (1261) either way.

However, the line (1261) is in the middle of a few lines containing comments, and the error is a duplicate key error (so thrown during an INSERT):

**SNIP**
1257: /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
1258: /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
1259:
1260: --
1261: -- Dumping data for table `applicants_x_folders`
1262: --
1263:
1264: LOCK TABLES `applicants_x_folders` WRITE;
**SNIP**

mysql.exe --host=localhost --user=root --port=3306 --default-character-set=utf8 < "D:\\temp\\mysql_data_dump_from_backup(non-prod)\\table_data\\bigfiles\\joined.sql"
ERROR 1062 (23000) at line 1261: Duplicate entry '1' for key 'PRIMARY'

mysql.exe --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments < "D:\\temp\\mysql_data_dump_from_backup(non-prod)\\table_data\\bigfiles\\joined.sql"
ERROR 1062 (23000) at line 1261: Duplicate entry '1' for key 'PRIMARY'

(The file is > 700MB, so obviously I'm not going to attach it here).

Moving category to Workbench, because for command line client this is expected behavior.

Moving category to Workbench, because for command line client this is expected behavior.

Would it be possible for you to attach your problematic script?
I'm not sure this is even a Workbench bug, if you get the prob regardless of the --comments option, even directly from the command line, then this is clearly not a WB specific bug.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".