Bug #25363 DataTruncation warning when calling stored proc
Submitted: 2 Jan 2007 12:36 Modified: 18 Jan 2007 22:55
Reporter: Neha Khaitan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.27, 5.0.34BK OS:Windows (Windows XP Professional SP2)
Assigned to: CPU Architecture:Any

[2 Jan 2007 12:36] Neha Khaitan
Description:
I am using mysql server 5.0.27 and mysql connector/j v 3.1.14.

I have a Numeric_Tab with the following DDL::

create table Numeric_Tab (MAX_VAL NUMERIC(30,15), MIN_VAL NUMERIC(30,15), NULL_VAL NUMERIC(30,15) NULL) !

I have the following stored procedure:

create procedure Numeric_Proc (out MAX_PARAM NUMERIC, out MIN_PARAM NUMERIC, out NULL_PARAM NUMERIC) begin select MAX_VAL, MIN_VAL, NULL_VAL  into MAX_PARAM, MIN_PARAM, NULL_PARAM from Numeric_Tab; end; !

I run the following with strict mode disabled in the server::

 Driver driver = (Driver) Class.forName("com.mysql.jdbc.Driver").newInstance();
        Properties props = new Properties();
        props.setProperty("user", "root");
        props.setProperty("password", "password");
        String url = "jdbc:mysql://localhost:3306/mysql";
        props.setProperty("url", url);
        props.setProperty("jdbcCompliantTruncation","false");
        Connection conn = driver.connect(url, props);
        conn.createStatement().executeUpdate("insert into Numeric_Tab values(9,0.01, null)");
            CallableStatement callableStatement = conn.prepareCall("call Numeric_Proc(?,?,?)");
            callableStatement.registerOutParameter(1, java.sql.Types.NUMERIC, 15);
            callableStatement.registerOutParameter(2, java.sql.Types.NUMERIC, 15);
            callableStatement.registerOutParameter(3, java.sql.Types.NUMERIC, 15);
            int i = callableStatement.executeUpdate();
            System.out.println("i = " + i);
            callableStatement.close();
            conn.close();
            System.out.println("Done");

I get the following exception:;

Exception in thread "main" com.mysql.jdbc.MysqlDataTruncation: Data truncation:
Out of range value adjusted for column 'MAX_PARAM' at row 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2973)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3026)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.ja
va:1137)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:1368)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:1283)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:1268)
        at com.mysql.jdbc.CallableStatement.executeUpdate(CallableStatement.java
:743)
        at mysqldrivermanagertest.main(mysqldrivermanagertest.java:31)

How to repeat:
Please See description
[5 Jan 2007 11:28] Tonci Grgin
Hi Neha and thanks for your report. This is not c/J but server related.

Verified on 5.0.27BK and 5.0.34BK on Win XP Pro SP2

USE `test`;
DROP TABLE IF EXISTS tblTestBug25363;
CREATE TABLE tblTestBug25363 (MAX_VAL NUMERIC(30,15), MIN_VAL NUMERIC(30,15), NULL_VAL NUMERIC(30,15) NULL);
DROP PROCEDURE IF EXISTS testBug25363;
CREATE PROCEDURE testBug25363(out MAX_PARAM NUMERIC, out MIN_PARAM NUMERIC, out NULL_PARAM NUMERIC)
BEGIN 
  select MAX_VAL, MIN_VAL, NULL_VAL into MAX_PARAM, MIN_PARAM, NULL_PARAM from tblTestBug25363;
END
insert into tblTestBug25363 values(9,0.01, null);

mysql> Select * from tblTestBug25363;
Field   1:  `MAX_VAL`
Catalog:    `def`
Database:   `test`
Table:      `tblTestBug25363`
Org_table:  `tbltestbug25363`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     32
Max_length: 17
Decimals:   15
Flags:

Field   2:  `MIN_VAL`
Catalog:    `def`
Database:   `test`
Table:      `tblTestBug25363`
Org_table:  `tbltestbug25363`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     32
Max_length: 17
Decimals:   15
Flags:

Field   3:  `NULL_VAL`
Catalog:    `def`
Database:   `test`
Table:      `tblTestBug25363`
Org_table:  `tbltestbug25363`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     32
Max_length: 0
Decimals:   15
Flags:

+-------------------+-------------------+----------+
| MAX_VAL           | MIN_VAL           | NULL_VAL |
+-------------------+-------------------+----------+
| 9.000000000000000 | 0.010000000000000 |     NULL |
+-------------------+-------------------+----------+
1 row in set (0.02 sec)

mysql>

mysql> set @a = 0;
Query OK, 0 rows affected (0.13 sec)

mysql> set @b = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @c = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> call testBug25363(@a, @b, @c);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;

+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Note  | 1265 | Data truncated for column 'MIN_PARAM' at row 1 |
+-------+------+------------------------------------------------+
1 row in set (0.02 sec)

mysql> select @a;
Field   1:  `@a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     67
Max_length: 1
Decimals:   30
Flags:      BINARY

+------+
| @a   |
+------+
| 9    |
+------+
1 row in set (0.00 sec)

mysql> select @b;
Field   1:  `@b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     67
Max_length: 1
Decimals:   30
Flags:      BINARY

+------+
| @b   |
+------+
| 0    |
+------+
1 row in set (0.00 sec)

mysql> select @c;
Field   1:  `@c`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     67
Max_length: 0
Decimals:   30
Flags:      BINARY

+------+
| @c   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>
[18 Jan 2007 22:55] Konstantin Osipov
This is a side effect of the client-server protocol limitation related to handling of OUT parameters of stored procedures - marking as a duplicate of Bug#17898 "No straightforward way to deal withoutput parameters".
[18 Jan 2007 23:18] Mark Matthews
Neha,

It appears you're running the JDBC CTS, we run it with "jdbcCompliantTruncation=false", since there are tests in it that cause truncation if a driver follows the spec to the letter (the test in this case isn't because of these tests, but the setting does make this test pass, and there is no data loss).

Oddly enough, there are no tests that actually _test_ for truncation in the testsuite.