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

[11 Feb 2005 16:16] Alan Williamson
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
[11 Feb 2005 17:09] Mark Matthews
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.
[11 Feb 2005 17:19] Alan Williamson
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.
[11 Feb 2005 17:59] Mark Matthews
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?
[11 Feb 2005 18:01] Alan Williamson
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.
[11 Feb 2005 21:31] Sergei Golubchik
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.
[12 Feb 2005 3:59] Paul DuBois
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.