Bug #18740 SQLException or SQLWarning contains only last error / warning when there are mu
Submitted: 3 Apr 2006 14:42 Modified: 5 Apr 2006 20:14
Reporter: Mark Matthews Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:3.1, 5.0 OS:NA
Assigned to: Mark Matthews CPU Architecture:Any

[3 Apr 2006 14:42] Mark Matthews
Description:
> Hello all,
> 
>  I have encountered a strange behavior with JDBC
> and could not find anything on this thread related
> to my question. Here is the problem:
> 
> SQLException or SQLWarning associated to the
> statement only contain the last error or warning
> when there are multiple warnings returned by 
> MySQL server. A good example of multiple warnings
> is using the bulk load statement to load data in
> the table under a single statement. As shown
> below, there should be 2 warnings about data too
> long and truncation. Using the query browser, I
> got both warnings. However, using JDBC, I only
> received the last one back. I am currently using
> currently using connector 3.1.12. Is this a
> potential bug or did I miss something. Any help
> would be greatly appreciated it. Thanks.
> 
> -Brian
> 
> CREATE TABLE tbl (
>   field1 smallint(6),
>   field2 varchar(6),
>   UNIQUE KEY field1(field1)
> ) ENGINE=MyISAM;
> 
> tbl.csv
> ---------------------------
> field1,field2
> 10001, data1
> 10002, data2 foo
> 10003, data3
> 10004999, data4
> 10005, data5
> ----------------------------
> 
> // rows 2,4 should cause data too long and
> truncation warnings
> 
> // MySQL server output using query browser
> Data too long for column 'field2' at row 2
> Out of range value adjusted for column 'field1' at
> row 4
> 
> // code snippet
> stmt = conn.createStatement();
> try{
> 	stmt.executeUpdate("TRUNCATE tbl");
> 	stmt.executeUpdate("LOAD DATA INFILE
> 'c:/temp/tbl.csv' INTO TABLE tbl FIELDS TERMINATED
> BY ',' IGNORE 1 LINES");
> } 
> catch(SQLException e){
> 	// loop SQLException
> 	do{
> 		System.out.println(e.getMessage());
> 	} while(null != e.getNextException());				
> 	System.out.print("\n");
> 
> 	// loop SQLWarning
> 	SQLWarning warning = stmt.getWarnings();
> 	do{
> 		System.out.println(warning.getMessage());
> 	} while(null != warning.getNextWarning());
> }
> 
> // Java output
> Data truncation: Out of range value adjusted for
> column 'field1' at row 4
> 
> Out of range value adjusted for column 'field1' at
> row 4

How to repeat:
> Hello all,
> 
>  I have encountered a strange behavior with JDBC
> and could not find anything on this thread related
> to my question. Here is the problem:
> 
> SQLException or SQLWarning associated to the
> statement only contain the last error or warning
> when there are multiple warnings returned by 
> MySQL server. A good example of multiple warnings
> is using the bulk load statement to load data in
> the table under a single statement. As shown
> below, there should be 2 warnings about data too
> long and truncation. Using the query browser, I
> got both warnings. However, using JDBC, I only
> received the last one back. I am currently using
> currently using connector 3.1.12. Is this a
> potential bug or did I miss something. Any help
> would be greatly appreciated it. Thanks.
> 
> -Brian
> 
> CREATE TABLE tbl (
>   field1 smallint(6),
>   field2 varchar(6),
>   UNIQUE KEY field1(field1)
> ) ENGINE=MyISAM;
> 
> tbl.csv
> ---------------------------
> field1,field2
> 10001, data1
> 10002, data2 foo
> 10003, data3
> 10004999, data4
> 10005, data5
> ----------------------------
> 
> // rows 2,4 should cause data too long and
> truncation warnings
> 
> // MySQL server output using query browser
> Data too long for column 'field2' at row 2
> Out of range value adjusted for column 'field1' at
> row 4
> 
> // code snippet
> stmt = conn.createStatement();
> try{
> 	stmt.executeUpdate("TRUNCATE tbl");
> 	stmt.executeUpdate("LOAD DATA INFILE
> 'c:/temp/tbl.csv' INTO TABLE tbl FIELDS TERMINATED
> BY ',' IGNORE 1 LINES");
> } 
> catch(SQLException e){
> 	// loop SQLException
> 	do{
> 		System.out.println(e.getMessage());
> 	} while(null != e.getNextException());				
> 	System.out.print("\n");
> 
> 	// loop SQLWarning
> 	SQLWarning warning = stmt.getWarnings();
> 	do{
> 		System.out.println(warning.getMessage());
> 	} while(null != warning.getNextWarning());
> }
> 
> // Java output
> Data truncation: Out of range value adjusted for
> column 'field1' at row 4
> 
> Out of range value adjusted for column 'field1' at
> row 4
[3 Apr 2006 15:37] Bugs System
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/4416