MySQL Bugs: #30195: error when represented as java.sql.Time in mysql-connector-java-5.0.5-bin.jar

Bug #30195	error when represented as java.sql.Time in mysql-connector-java-5.0.5-bin.jar
Submitted:	2 Aug 2007 5:01	Modified:	31 Mar 2014 14:51
Reporter:	andy tanzil	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	mysql-connector-java-5.0.5-bin	OS:	Windows (windows 2003 server,XP)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
java.sql.SQLException: java.sql.SQLException: Value '00:00:00 can not be represented as java.sql.Time

How to repeat:
CREATE TABLE `FOO` (

`ID` int(11) not null,
`BEGINDATE` time,
PRIMARY KEY (`ID`) )

insert into FOO values(1,'00:00:00')

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Explanation: Hi Andy and thanks for your report. You didn't submit test case nor attached my.ini/cnf (this behavior could be related to SQL_MODE)... In any case, I don't think this is a bug, according to manual:

• Datetimes with all-zero components (0000-00-00 ...) — These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a Result-Set.
Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the zeroDateTimeBehavior configuration property. The allowable values are:
• exception (the default), which throws an SQLException with an SQLState of S1009.
• convertToNull, which returns NULL instead of the date.
• round, which rounds the date to the nearest closest value which is 0001-01-01.
Starting with Connector/J 3.1.7, ResultSet.getString() can be decoupled from this behavior via noDatetimeStringSync=true (the default value is false) so that you can get retrieve the unaltered all-zero value as a String. It should be noted that this also precludes using any time zone conversions, therefore the driver will not allow you to enable noDatetimeStringSync and useTimezone at the same time.

Please, check also jdbcCompliantTruncation option: Should the driver throw java.sql.DataTruncation exceptions when data is truncated as is required by
the JDBC specification when connected to a server that supports warnings(MySQL 4.1.0 and newer)? true 3.1.2

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I am getting this same error on a time field with value 00:00:00.  I can understand why a datetime value of all 0's would cause problems, but as far as I know, 00:00:00 is a legitimate way to represent midnight for a time only field.

I can do a brutal hack and assign midnight 00:00:01 as I am not really using the seconds anyway;  however this seems like a legitimate bug as a time field should be able to return 00:00:00 without any problems.

I have the same problem but with version 5.0.4 driver. We have to hack our java program to go around this problem. If we get an exception, we set our Java time variable manualy to 00.00.00

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41552

Hi guys. You provided no added value for us and we're supporting you for free... Anyway, I escalated this problem and here's test case (NOT a patch) we added to our source repo (it works as expected on connector-j-trunk (latest version) and 5.0 (latest sources), connection string is all defaults and using "&zeroDateTimeBehavior=exception&noDatetimeStringSync=true", MySQL server both 5.0 and 5.1 tested):

    public void testBug30195() throws Exception {
        createTable("testBug30195", "(time_field TIME)");
        String insert = "INSERT INTO testBug30195 VALUES (?)";
        this.pstmt = this.conn.prepareStatement(insert);
        this.pstmt.setString(1, "00:00:00");
        this.pstmt.execute();
        this.pstmt.setString(1, null);
        this.pstmt.execute();
        this.pstmt.close();
        String sql = "SELECT time_field FROM testBug30195";
        this.pstmt = this.conn.prepareStatement(sql);
        this.rs = this.pstmt.executeQuery();
        assertTrue(this.rs.next());
        Time midnight = new Time(0, 0, 0);
        assertEquals("00:00:00", rs.getString(1));
        assertEquals(midnight, rs.getTime(1));
        assertTrue(this.rs.next());
        assertEquals(null, rs.getString(1));
        assertEquals(null, rs.getTime(1));
        assertFalse(this.rs.next());
        this.rs.close();
    }

Now, I would need a complete *failing* test case as well as all possible info on c/J, MySQL server, JDK etc used.

And tested on OS X 10.4 and WinXP Pro SP2.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have this issue right now, so I'll see if I can provide you with enough info to recreate it. I adapted your test to my current project. 

I'm using:

/usr/bin/mysqladmin  Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486

I will be upgrading to Debian Lenny soon so I'll be able to get my hands on a more up-to-date version at that point. 

Debian Etch on linux:

Linux gondor 2.6.18-6-686 #1 SMP Mon Aug 18 08:42:39 UTC 2008 i686 GNU/Linux

I'm running Java 1.6.0_11 JDK

and using JDBC
      <artifactId>mysql-connector-java</artifactId>
      <version>5.0.5</version>

This is my table:

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| ID             | int(11)      | NO   | PRI | 0       |       |
| IUCN_STATUS_ID | int(11)      | NO   | MUL | NULL    |       |
| GENUS_ID       | int(11)      | NO   | MUL | NULL    |       |
| TITLE          | varchar(255) | NO   | UNI | NULL    |       |
| TIME_OF_DAY    | time         | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

and TIME_OF_DAY is the killer field. 

What confuses me is that normally I have 

sql-mode        = STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,NO_ZERO_DATE

but I removed NO_ZERO_IN_DATE,NO_ZERO_DATE and I still get the problem. 

I'll attach my my.cnf file. 

It is also confusing that I can insert the '00:00:00' value but not read it back.

Obviously I'm looking forward to being able to use 00:00:00 as midnight.

Also interesting (perhaps) is that a normal date with the zero time is OK, e.g. 2009-03-09 00:00:00

my.cnf

Attachment: my.cnf (application/octet-stream, text), 3.76 KiB.

Searching through more google results found me this work-around:

useFastDateParsing=false

thanks to this blogger:

http://www.usefulconcept.com/index.cfm/2008/3/21/MySQL-JDBC-505-bug-with-TIME-column-and-m...

watch out that wrap on the URL above - it is a valid URL.

potential dupe: http://bugs.mysql.com/bug.php?id=26789

I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.