MySQL Bugs: #17402: INSERT failures when comment char used in field value

Bug #17402	INSERT failures when comment char used in field value
Submitted:	14 Feb 2006 22:13	Modified:	27 Mar 2006 9:22
Reporter:	Brian Hart	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.0.11, 3.1.12, 5.0.0b	OS:	Windows (Windows 2003)
Assigned to:		CPU Architecture:	Any

Description:
Using the Java connector, having an issue where an insert fails if the comment characters '--' are used in field value.  For example:

INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (3,'-- Choose one --');
INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (4,'Inlined -- hyphens');

How to repeat:
1.  Create table JDBCBUG.
CREATE TABLE JDBCBUG (ID INTEGER NOT NULL AUTO_INCREMENT, IMPORTANTDATA VARCHAR(255), PRIMARY KEY (ID));

2.  Using Ant 1.6.5's SQL task, imported a file that contained:
INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (1,'Plain text.');
INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (2,'Text - with one hyphen.');
INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (3,'-- Choose one --');
INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (4,'Inlined -- hyphens');

This was done as one transaction.

Error is:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException:
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 ';
 INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (4,'Inlined -- hyphens')' at line
 1

Resulting table is:
mysql> select * from jdbcbug;
+----+-------------------------+
| ID | IMPORTANTDATA           |
+----+-------------------------+
|  1 | Plain text.             |
|  2 | Text - with one hyphen. |
+----+-------------------------+
2 rows in set (0.00 sec)

Suggested fix:
Review code to ensure '--' comment characters are handled correctly when they fall within field values.

Thanks for your test case. I am sorry but I'm unable tor repeat the problem.
Tested with MySQL 4.1.3  on Linux and 5.1.7 nt.max on WINXP SP2. mysql-connector-java-3.1.12. 
Java console output:
Compiling 1 source file to ...
compile:
run:
1Plain text.
2Text - with one hyphen.
3-- Choose one --
4Inlined -- hyphens

Additional test done on Windows 2003, JDK 1.4.1.05 still can't repeat.

I'm having the same problem. However I'm using ant-1.6.5-2jpp.2 on Fedora Core 6.

STEPS TO REPRODUCE:

1. Create an ANT build.xml file with the following contents:

<?xml version="1.0" encoding="UTF-8"?>
<project basedir="." default="restore" name="jdbc.test">

   <property name="db.name" value="jdbc"/>
   <property name="dump.name" location="jdbc.sql"/>

   <target name="restore">
      <sql driver="com.mysql.jdbc.Driver"
         url="jdbc:mysql://localhost/"
         userid="root" 
         password="">
         <classpath>
            <pathelement path="mysql-connector-java-5.0.7-bin.jar"/>
         </classpath>
         <transaction>
            drop database if exists `${db.name}`;
            create database `${db.name}`;
            use `${db.name}`;
         </transaction>
         <transaction src="${dump.name}"/>
      </sql>
   </target>

</project>

2. Put the following in jdbc.sql

CREATE TABLE JDBCBUG (ID INTEGER NOT NULL AUTO_INCREMENT, IMPORTANTDATA VARCHAR(255), PRIMARY KEY

INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (1,'Plain text.');
INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (2,'Text - with one hyphen.');
INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (3,'-- Choose one --');
INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (4,'Inlined -- hyphens');

3. run ant

RESULT:

$ ant
Buildfile: build.xml

restore:
      [sql] Executing commands
      [sql] Executing file: /home/leif/jdbc_test/jdbc.sql
      [sql] Failed to execute:  INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (3,'-- Choose one --');
      [sql]  INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (4,'Inlined -- hyphens');
      [sql]  

BUILD FAILED
/home/leif/jdbc_test/build.xml:11: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 ';
 INSERT INTO JDBCBUG (ID,IMPORTANTDATA) VALUES (4,'Inlined -- hyphens')' at line 1

Total time: 0 seconds

In case it helps my java version is:

$ java -version
java version "1.5.0_11"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_11-b03)
Java HotSpot(TM) Client VM (build 1.5.0_11-b03, mixed mode, sharing)

One more thing, my mysql version:

$ mysql --version
mysql  Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using readline 5.0

Leif, consulting.

No one seems to be able to repeat this problem... Possibly it's ant-script related (though I don't know how that can be) as we do our tests in Eclipse. I'll give it another try this week.

I wonder if it's related to Bug #26215 ?

I have no problem restoring this test database with the command line like:

$ mysql -u root jdbc < jdbc.sql

$ mysql -u root jdbc -e "select * from JDBCBUG"
+----+-------------------------+
| ID | IMPORTANTDATA           |
+----+-------------------------+
|  1 | Plain text.             | 
|  2 | Text - with one hyphen. | 
|  3 | -- Choose one --        | 
|  4 | Inlined -- hyphens      | 
+----+-------------------------+

Leif, I have asked my colleague to clarify that.

Valeriy says bug#26215 is not related to this one. Proceeding as planned (retest).

Still no luck here...
3.1 and 5.0 branch, latest sources:
Connected to 5.0.50-pb1046-log
java.vm.version         : 1.5.0_12-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_12-b04
os.name                 : Windows XP
os.version              : null
sun.management.compiler : HotSpot Client Compiler

Time: 0,312

OK (1 test)

Default VM arguments:
-Xmx512M -XX:+UseParallelGC -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://localhost:3306/test?user=root&password=*****&noAccessToProcedureBodies=true

Test case, using SVN repository sources and our test fw

Attachment: TestBug17402.java (text/java), 1.71 KiB.

For those who still have the problem with <sql> task: use keepformat="yes" - this seems to be a fix to this bug.

@Andrey Anisimov 

Thankyou! That worked.