Bug #83946 Comments need semicolon to access the next query
Submitted: 24 Nov 2016 8:49 Modified: 28 Nov 2016 9:25
Reporter: Aulbach A Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.40 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[24 Nov 2016 8:49] Aulbach A
Description:
The next query is not executed

# create dummy entries for 6261 and 6969. they are updated by the idocs. they need to have an fixed id because it's used in the code :(
INSERT .....

It works when a semicolon is added

# create dummy entries for 6261 and 6969. they are updated by the idocs. they need to have an fixed id because it's used in the code :( ;
INSERT .....

How to repeat:
Self explaining.
[24 Nov 2016 9:20] Chiranjeevi Battula
Hello Aulbach,

Thank you for the bug report.
Could you please provide repeatable test case (exact steps, MySQL version, sample connection details, data, etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.
[25 Nov 2016 8:39] Aulbach A
This SQL is used in ANT scripts with a macro:

    <macrodef name="runSqlFromFile" >
        <attribute name="user" />
        <attribute name="pass" />
        <attribute name="host" />
        <attribute name="port" />
        <attribute name="file" />
        <attribute name="database" />
        <sequential>
            <!-- it's currently not possible to replace this with JDBC, cause the SQL_MODE of JDBC is STRICT and the side-effects of changing SQL_MODE in the sql-files are unknown -->
            <echo message="applying @{file} to @{user}(at)@{host}:@{port}/@{database}"/>
            <sql
                    print="true"
                    encoding="UTF-8"
                    classpath="${basedir}/build/lib/mysql/mysql-connector-java-5.1.40-bin.jar"
                    driver="com.mysql.jdbc.Driver"
                    url="jdbc:mysql://@{host}:@{port}/@{database}?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"
                    userid="@{user}"
                    password="@{pass}"
                    src="@{file}"
                    expandProperties="true"
                    showheaders="false"
                    showtrailers="false"
                    showWarnings="true"
            />
        </sequential>
    </macrodef>

which is called like so

    <target name="initialize-database-euronet-fortesting"
                depends="check-for-euronet,initialize-database-euronet-fortesting-check,initialize-database-euronet"
                if="euronet-check"
                unless="initialize-database-euronet-fortesting-forbidden">
        <echo message="Initializing Database from ${basedir}/tests/euronet/initDbForTests.sql" />
        <runSqlFromFile
                user="${euronet.build.database.user}"
                pass="${euronet.build.database.pass}"
                host="${euronet.build.database.host}"
                port="${euronet.build.database.port}"
                file="${basedir}/tests/euronet/initDbForTests.sql"
                database=""
        />
        <echo message="creating QSUser for tests" />
        <runSqlFromFile
                user="${euronet.build.database.user}"
                pass="${euronet.build.database.pass}"
                host="${euronet.build.database.host}"
                port="${euronet.build.database.port}"
                file="${basedir}/build/lib/mysql/createQsUserEuronet.sql"
                database="${euronet.build.database.typo3.database}"
        />

    </target>

Hope that helps. :)
[25 Nov 2016 13:04] Filipe Silva
Hi Aulbach,

Thank you for this report and for your interest in Connector/J.

The Ant 'sql' task recognizes comments when they begin with "//", "--" or "REM" and skips those lines from being executed. Besides that, it concatenates all consecutive lines in a single string until it finds a semi-colon. So, what's happening in your case is that the line starting with "# create dummy (...)", without ending with ";" ends up being concatenated to the next line(s) until a ";" is found. MySQL, on the other hand, accepts "#" as a comment identifier, so it ends up executing a commented line, which doesn't do anything.

As you noticed you can fix this by adding semi-colons at the end of all your comments. But this is dangerous because you can forget one and silently preventing commands from being executed, as it happened to you. Is also has the "problem" of sending all your commented lines to the server for being executed there, which leads to no results and it's time and resource consuming.

Your better option is to use the (most) standard comments prefix "--" or "//" in your comments.

I hope that helps.

I'm closing this report as "Not a bug".
Thank you,
[25 Nov 2016 15:13] Aulbach A
TL;DR: For me the default should be: Either it reads mysql-scripts 1:1 or it should fire an error. But THIS behaviour is definitely a bug.

Well, from your point of view it is OK to see this not as bug. ;)

The problem here resulted from switching from mysql-client to mysql-jdbc-connector, which is a valid task, if you need to maintain big ant-scripts. 

This is also the reasons, why I still think it is a bug, cause the behaviour of the connector is completely unawaited:
- It "forgets" to execute sql, which is quite dangerous. In this case we have had enough tests to find this error.
- if someone has not that many tests he will be unable to find that bug. Especially if he has - like us - many hundret lines of sql-scripts.

The awaited behaviour is also, that mysql-scripts that run with mysql will NOT run with the odbc-driver, cause it even has options to handle the delimiter.
[25 Nov 2016 16:25] Filipe Silva
Hi Aulbach,

You are talking to the wrong audience. It's a bug, alright, it's just not a bug in Connector/J.

Connector/J doesn't "forget" to run anything, it just runs what it's told to run. The 'sql' ant task, otoh is processing the source file in such a way that some commands may not get executed.

You should file this a bug in ant support.

I hope that is clear now.
Thank you,
[28 Nov 2016 9:25] Aulbach A
OK. Thanks for pointing me that out.  :)
[1 Feb 2017 22:16] Robert Piskule
Please try the following. Note that these are valid comments, taken from the following page:  https://dev.mysql.com/doc/refman/5.7/en/comments.html

    <sql
	classpathref="mysql"
	driver="com.mysql.jdbc.Driver"
	url="jdbc:mysql://${hostname}/${database}"
	userid="${username}"
	password="${password}"
	print="true">
      <transaction>
SELECT 1+1;     -- This comment continues to the end of line
SELECT 1 /* this is an in-line comment */ + 1;
SELECT 1+
/*
this is a
multiple-line comment
*/
1;
      </transaction>
    </sql>
    <sleep seconds="5"/>
  </target>

com.mysql.jdbc.exceptions.jdbc4.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 'SELECT 1 /* this is an in-line comment */ + 1' at line 2

Thanks,
-Rob
[2 Feb 2017 0:29] Filipe Silva
Hi Robert,

Please read my answer a few posts above.

Your example falls in the same category. This is caused by the way ant implements the SQL task, specifically, the way it parses the text containing the commands. You should check ant source code to understand what's happening. I also suggest you to turn on MySQL global query log (https://dev.mysql.com/doc/refman/5.7/en/query-log.html) so that you can see what queries are being sent to the sever. Mind that Connector/J has no role in this, it only executes the queries that it is told.