Bug #18308 Cannot convert value '0000-00-00 00:00:00' from column 2
Submitted: 17 Mar 2006 13:57 Modified: 28 Mar 2006 15:07
Reporter: Jeppe Skovbon Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:mysql-connector-java-3.1.10-bin.jar OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[17 Mar 2006 13:57] Jeppe Skovbon
Description:
I have read the bugs report at:
http://bugs.mysql.com/bug.php?id=10717

- commented by [18 May 2005 18:05] Mark Matthews 

I have already changed the connection url to:

noDatetimeStringSync=true

But I still got this error:

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 20
 to TIMESTAMP.

I donĀ“t understand how to solve the problem? Can you help me?

How to repeat:
************* start
Java code:

StringBuffer buf = new StringBuffer();
		  String query = "SELECT * FROM customer";
		  db = new dbConn.DBConn();
		  conn = db.connect2();
		  
		  stat = conn.prepareStatement( query );
		  
		  rs = stat.executeQuery();
		  
		  
    	  if( rs != null ) {
     	  java.sql.ResultSetMetaData rsmd = rs.getMetaData();
      	  int columnCount = rsmd.getColumnCount() + 1;
      	  for( int i = 1; i < columnCount; i++ ) {
          	   buf.append( rsmd.getColumnLabel( i ) );
        	   buf.append( "  " );
      	  }
      	  buf.append( "\n===========================================\n" );

      while( rs.next() ) {
        rsmd = rs.getMetaData();
		 
		for( int i = 1; i < columnCount; i++ ) {
		  System.out.println( rsmd.getColumnTypeName( i ) );
          String type = rsmd.getColumnTypeName( i );
		  
		  buf.append( rs.getObject( i ) );
          buf.append( "  " );
        }
        buf.append( "\n" );
      }

********************
Table documentation:

CREATE TABLE `customer` (                                                     
            `custID` int(11) NOT NULL auto_increment,                                   
            `custInternID` varchar(20) default '0',                                     
            `custFirstname` varchar(50) NOT NULL default '',                            
            `custLastname` varchar(50) NOT NULL default '',                             
            `custLocalNo` varchar(12) NOT NULL default '',                              
            `custMobilNo` varchar(12) NOT NULL default '',                              
            `custEmail` varchar(100) NOT NULL default '',                               
            `custUsername` varchar(50) NOT NULL default '',                             
            `custPassword` varchar(50) NOT NULL default '',                             
            `custPwdIsChanged` tinyint(1) NOT NULL default '0',                         
            `custIPRestrict` varchar(100) NOT NULL default '',                          
            `custIPRestrictIsActive` tinyint(1) NOT NULL default '0',                   
            `custAdminRights` tinyint(1) NOT NULL default '0',                          
            `custIsExternal` tinyint(1) NOT NULL default '0',                           
            `custRelationShip` tinyint(1) NOT NULL default '0',                         
            `custRelationShipText` varchar(100) NOT NULL default '',                    
            `custIsActive` tinyint(1) NOT NULL default '0',                             
            `custIsDeleted` tinyint(1) NOT NULL default '0',                            
            `custTimestampCreated` datetime NOT NULL default '0000-00-00 00:00:00',     
            `custTimestampDeleted` datetime NOT NULL default '0000-00-00 00:00:00',     
            `custTimestampChange` datetime NOT NULL default '0000-00-00 00:00:00',      
            `depID` int(11) NOT NULL default '0',                                       
            `custEmailInvitationIsSent` tinyint(1) NOT NULL default '0',                
            `custEmailInvitationDate` datetime NOT NULL default '0000-00-00 00:00:00',  
            `compID` int(11) NOT NULL default '0',                                      
            `compOwnerID` int(11) NOT NULL default '0',                                 
            `usertypeID` int(11) NOT NULL default '0',                                  
            PRIMARY KEY  (`custID`)                                                     
          ) TYPE=MyISAM                       

************ end
[27 Mar 2006 13:01] Tonci Grgin
Thank's for your bug report. Can you please provide me with data dump?
[27 Mar 2006 13:47] Jeppe Skovbon
Table Customer data dump

Attachment: customer_dump.sql (text/plain), 18.14 KiB.

[27 Mar 2006 13:51] Jeppe Skovbon
Please be aware that the problem occurs in column
20, and not column 2...

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column
20
 to TIMESTAMP
[28 Mar 2006 8:09] Tonci Grgin
Thanks for additional info. 
Test environment: WinXP SP2, 4.1.18-nt-max-log, mysql-connector-java-3.1.12.
I've been able to build test case but the error didn't show up:
custID  custInternID  custFirstname  custLastname  custLocalNo  custMobilNo  custEmail  custUsername  custPassword  custPwdIsChanged  custIPRestrict  custIPRestrictIsActive  custAdminRights  custIsExternal  custRelationShip  custRelationShipText  custIsActive  custIsDeleted  custTimestampCreated  custTimestampDeleted  custTimestampChange  depID  custEmailInvitationIsSent  custEmailInvitationDate  compID  compOwnerID  usertypeID  
===========================================
1  0  Admin  JSF      j_skovbon@hotmail.com  jeppe1  Bj65tUZLhSyLLGj42lOKU70eCm0=  true  127.0.0.2  false  true  false  false    true  true  null  null  2006-02-18 02:22:55.0  0  true  2006-02-18 02:23:01.0  1  0  2  
2  0  Jeppe234  Skovbon23444545  Jeppe    j_skovbon@hotmail.com  demoadm  zQOQIhn2rXrRV3rlhGZKux9bLiY=  true    false  true  false  false    true  true  2004-07-07 00:00:00.0  null  2006-02-17 23:32:34.0  0  true  2006-02-17 23:32:35.0  2  0  4  
3  0  Jeppe  Skovbon  xxxx    j_skovbon@hotmail.com  demokantine1  demokantine2  true    false  true  false  false    true  true  2004-07-07 00:00:00.0  null  2006-02-02 17:00:59.0  0  true  2006-01-22 08:06:54.0  2  0  3  
4  0  test4  test  test    j_skovbon@hotmail.com  idafaktura1  M/PAlLyMDRLnU9khKkZpBUpXgOA=  false    false  false  false  false    true  true  2004-07-07 00:00:00.0  null  2006-03-07 11:24:35.0  0  true  2006-03-07 11:24:38.0  3  0  5  

custID  custInternID  custFirstname  custLastname  custLocalNo  custMobilNo  custEmail  custUsername  custPassword  custPwdIsChanged  custIPRestrict  custIPRestrictIsActive  custAdminRights  custIsExternal  custRelationShip  custRelationShipText  custIsActive  custIsDeleted  custTimestampCreated  custTimestampDeleted  custTimestampChange  depID  custEmailInvitationIsSent  custEmailInvitationDate  compID  compOwnerID  usertypeID  
===========================================
1  0  Admin  JSF      j_skovbon@hotmail.com  jeppe1  Bj65tUZLhSyLLGj42lOKU70eCm0=  true  127.0.0.2  false  true  false  false    true  true  null  null  2006-02-18 02:22:55.0  0  true  2006-02-18 02:23:01.0  1  0  2  
2  0  Jeppe234  Skovbon23444545  Jeppe    j_skovbon@hotmail.com  demoadm  zQOQIhn2rXrRV3rlhGZKux9bLiY=  true    false  true  false  false    true  true  2004-07-07 00:00:00.0  null  2006-02-17 23:32:34.0  0  true  2006-02-17 23:32:35.0  2  0  4  
3  0  Jeppe  Skovbon  xxxx    j_skovbon@hotmail.com  demokantine1  demokantine2  true    false  true  false  false    true  true  2004-07-07 00:00:00.0  null  2006-02-02 17:00:59.0  0  true  2006-01-22 08:06:54.0  2  0  3  
4  0  test4  test  test    j_skovbon@hotmail.com  idafaktura1  M/PAlLyMDRLnU9khKkZpBUpXgOA=  false    false  false  false  false    true  true  2004-07-07 00:00:00.0  null  2006-03-07 11:24:35.0  0  true  2006-03-07 11:24:38.0  3  0  5  
5  0  Jeppe  Skovbon2  hjh''bjbj  61305045  j_skovbon@hotmail.com  jesk98ac  TiIirjt9I7HmHjTAaP5WXTtLdDk=  true    false  false  false  false    true  true  2004-07-07 00:00:00.0  null  2006-03-10 00:00:00.0  2  true  2006-03-08 09:24:01.0  3  2  6  
...
Maybe the problem is with connection string. It should include zeroDateTimeBehavior=convertToNull.
con=DriverManager.getConnection("jdbc:mysql://localhost/test?zeroDateTimeBehavior=convertToNull","root","");
[28 Mar 2006 8:17] Tonci Grgin
Additional info can be found at
http://dev.mysql.com/doc/refman/5.0/en/cj-configuration-properties.html
[28 Mar 2006 11:26] Jeppe Skovbon
That works. Thank you :-)
[28 Mar 2006 15:07] Tonci Grgin
No problem.
[11 May 2010 0:10] Chandni Verma
I had a similar problem. Resolved! Thanks!