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 18:19	Modified:	8 Jul 2005 19:40
Reporter:	Ken Johanson
Status:	Closed
Category:	Connector/J	Severity:	S2 (Serious)
Version:	mysql-connector-java-3.2.0-alpha	OS:	Linux (Linux)
Assigned to:	Mark Matthews	Target Version:

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.