MySQL Bugs: #11797: "Syntax error for TIME escape sequence" sending literal Strings having curly br

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

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

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?

Oops, I see that they're in single quotes...That _should_ work. Can you test this with Connector/J 3.1.10?

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

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?

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.*}'

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.

Mark, thank you!! And sorry for my horrific... typos :-)

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?

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?

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.