Bug #11797 "Syntax error for TIME escape sequence" sending literal Strings having curly br
Submitted: 7 Jul 2005 16:19 Modified: 8 Jul 2005 17:40
Reporter: Ken Johanson Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:mysql-connector-java-3.2.0-alpha OS:Linux (Linux)
Assigned to: Mark Matthews CPU Architecture:Any

[7 Jul 2005 16:19] Ken Johanson
Description:
Running mysql-standard-5.0.7-beta-linux-i686 with options:
set-variable=sql-mode=PIPES_AS_CONCAT,ANSI_QUOTES,NO_BACKSLASH_ESCAPE

and inserting a string containing brackets (escaped using only ANSI single quote doubling), a java.sql.SQLException: Syntax error for TIME escape sequence is thrown:

Example sql literal:

UPDATE test SET a = 'ON {tbl_name | * | *.* | db_name.*}'

(above String is a real exmaple of some of my syntax notes that was trying to be saved)

How to repeat:
Run a:
UPDATE test SET a = 'ON {tbl_name | * | *.* | db_name.*}'
type query.

Suggested fix:
Apparently this is an ODBC-style escape sequence trigger, but my desire is to use only ANSI style escaping, which I dont believe applies..
[7 Jul 2005 16:25] Mark Matthews
As far as I can tell from the JDBC specification, the only style escapes that are recognized are ODBC ones, and that { and } are reserved characters unless you turn off escape processing.

Do you interpret it differently?
[7 Jul 2005 16:27] Mark Matthews
Oops, I see that they're in single quotes...That _should_ work. Can you test this with Connector/J 3.1.10?
[7 Jul 2005 16:46] Ken Johanson
Mark, same problem with that version as well. 

Icidentally, my config is pretty minimal:
mysql.url=jdbc:mysql://127.0.0.1/ken?user=ken&password=pass&useTimezone=false&noDatetimeStringSync=true
[7 Jul 2005 18:41] Mark Matthews
Ken,

I've just tried testing this, and can't repeat it. If I pass that string to Connection.nativeSQL() (which runs it through the escape processor), I get it back.

The escape processor code doesn't even know about the sql_mode of the server, so I'm a bit stumped.

Can you post a _full_ stack trace, or a repeatable testcase instead of just the string?
[7 Jul 2005 18:53] Ken Johanson
Mark, my apologies, it was ineed not reporducable with the sample query. The query that causes the problme is below (note, it contrain and ANSI-legal syntax; doubled single-quotes)

select 'ESCAPED BY ''\'' ON {tbl_name | * | *.* | db_name.*}'
[7 Jul 2005 19:32] Mark Matthews
Fixed for 3.1.11, will move it up to 3.2.x when the merge happens. See nightly snapshots after 00:00 GMT July 8th at http://downloads.mysql.com/snapshots.php#connector-j to test a new build.
[7 Jul 2005 19:46] Ken Johanson
Mark, thank you!! And sorry for my horrific... typos :-)
[8 Jul 2005 17:35] Ken Johanson
Mark, I tired last nights build (from http://downloads.mysql.com/snapshots/mysql-connector-java-3.2/mysql-connector-java-3.2-nig...)
, having a datestamp on the Jar file of 8 Jul 2005 00:00 UTC, same problem exists.

By 'will move it up to 3.2 when the merge happens', do you mean that the fix wont be available until some manual-merge opertaions occurs? - or should the automated build have done this?
[8 Jul 2005 17:40] Mark Matthews
Ken,

We don't automagically merge stuff, the process has to started by hand.

The fix is in the nightlies for _3.1_, not _3.2_ right now. 3.2 is probably going to get pretty seriously overhauled in the next few weeks, and basically become what 3.1 is now but with _features_ backported but not any refactorings.

Is there something in particular you need from 3.2 for your application?
[8 Jul 2005 17:49] Ken Johanson
Mark, sorry I havent been keeping track of all features/fixes, that would only apply to 3.2 (I write them off / forgot about them after fixes in 3.2). But two do stand out: Running the server in ANSI compliant mode (no non-standard backslash behavior namely, since I'm only running the server in that mode now), and needing Timezone offsets to NOT be applied to Timestamp.toString() (implicit UTC) (useTimezone=false & noDatetimeStringSync=true). If those are in 3.1 then I can pretty easily go with that version.