| Bug #25363 | DataTruncation warning when calling stored proc | ||
|---|---|---|---|
| Submitted: | 2 Jan 2007 13:36 | Modified: | 18 Jan 2007 23:55 |
| Reporter: | Neha Khaitan | ||
| Status: | Duplicate | ||
| Category: | Server: SP | Severity: | S2 (Serious) |
| Version: | 5.0.27, 5.0.34BK | OS: | Microsoft Windows (Windows XP Professional SP2) |
| Assigned to: | Target Version: | ||
[5 Jan 2007 12: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 23: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".
[19 Jan 2007 0: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.
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