MySQL Bugs: #8443: Date format no longer working in latest MySQL

Bug #8443	Date format no longer working in latest MySQL
Submitted:	11 Feb 2005 16:16	Modified:	12 Feb 2005 3:59
Reporter:	Alan Williamson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	4.1.9	OS:	Linux (Linux + Windows)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The following date format is no longer supported in the 4.1.9 release.

    {ts \'2005-02-11 10:17:01\'}

How to repeat:
Run this code on a version prior to 4.1.9; everything works

CREATE TABLE `testdate` (
`dd` DATETIME NOT NULL
);

insert into testdate values ('{ts \'2005-02-11 10:17:01\'}');

update testdate set dd = '{ts \'2005-02-11 10:17:01\'}';

Run this exact same code on 4.1.9 and the row fails to insert or update

Alan, are you using JDBC to do this?

As far as I know, the server itself doesn't support ODBC-escape codes, so you were just lucky that the server was parsing that format of date.

I just tested it with JDBC and it works fine...

In any case, I'm curious about the '\' s in your statement, because they're not required, and technically would be a syntax error.

Yes Mark it is.  The \' is just escaping the quote.  But this is what we are seeing in the mysql bin-log file.  We can reproduce this away from the java layer though, by simply opening up a console.

Here is where it actually originates from:

  Statmt.setTimestamp( ColIndex, new java.sql.Timestamp( ts ) );

  (Driver MySQL Connector/J 3.0.14-production)

One thing we can add ... the 4.0.x branch doesn't seem to suffer from it.

Alan,

3.0.14 doesn't issue queries with that format...If you take a look at PreparedStatement.setTimestamp(), which then calls PreparedStatement.setTimestampInternal(), you'll see the following code:

if (this.tsdf == null) {
                this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''");
            }
 timestampString = this.tsdf.format(x);

So, I don't know where the '{ts ....}' is coming from, unless you're setting it directly in your application. (if you do a search for the literal "{ts " in the sources, you'll only find it in _one_ place, and that's in EscapeProcessor, and it's a literal there that's compared with a parsed token to see if the value needs coverted to MySQL's timestamp format).

I'm not able to repeat this in any fashion given the information you've given so far...Is there any way you could generate a standalone testcase so we can exactly what API calls you're making?

Mark, i've already commented on the mailing list, that this bug has nothing to do with the Java connector/driver.  

It is specific to the MySQL server.

You're right, the query worked in 4.0, but it does not work in 4.1 (since 4.1.1).
The problem is that syntax you're using is invalid, correct ODBC syntax is

insert into testdate values ({ts '2005-02-11 10:17:01'});

that is {...} construct is not a string literal, but something a parser should parse.
The query written correctly works both in 4.0 and 4.1.
Your original query worked before, because 4.0 had a very forgiving
string-to-date conversion function - it ignored the leading garbage up
to the first digit. It 4.1 it ignores only spaces, so your original query
doesn't work anymore.

We'll make sure this change is mentioned in the manual.

I've noted the change in behavior here:

http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html

See the "SQL changes" section.