MySQL Forums
Forum List  »  General

Data Truncation with server-side prepared statements
Posted by: Alexander Hristov
Date: September 26, 2006 03:32AM

I've been fighting recently with an incomprehensible data truncation error. I initially filed it as a Connector/J bug but after further experiments it seems a server side issue. I'd like to know whether anyone has any explanation, or is it really a bug.

Server version: 5.0.22 on Fedora Core 5 AND 5.0.24a on Windows XP. Both servers operating in STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mode.

Ok. the issue (and test case) is this : a single table with a single column of type numeric(6,2)

Create table foo (cost decimal(6,2) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

when trying to insert *some* values (-855.2 or 8517.45 or others) using server-side prepared statements, the server returns a

Note 1265 Data truncated for column 'cost' at row 1

The warning does NOT happen when inserting the value using a plain insert statement using any client console. It also does NOT happen when the server does not operate in strict mode.


Initially I thought that it was a Connector/J issue, but after making a short test with Connector/NET, the issue is present there too, only that Connector/NET chooses not to raise an exception.

I also wrote a quick "proxy" for monitoring what stuff were sending both connectors over the wire and they send the same packets, so it doesn't seem a client side issue.

The fllowing should be viewed using a fixed width font:
<snip>
FROM CLIENT TO SERVER: 31 bytes
1b 01 01 01 16 69 6e 73 65 72 74 20 69 6e 74 6f .....insert into
20 66 6f 6f 20 76 61 6c 75 65 73 20 28 3f 29 foo values (?)
FROM SERVER TO CLIENT: 52 bytes
01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 ................
17 01 01 01 01 64 65 66 01 01 01 01 3f 01 01 3f .....def....?..?
01 01 01 01 01 fd 80 01 01 01 01 01 01 01 01 fe .....ý?........þ
01 01 01 01 ....
FROM CLIENT TO SERVER: 26 bytes
16 01 01 01 17 01 01 01 01 01 01 01 01 01 01 01 ................
01 01 9a 99 99 99 b9 a2 c0 40 ..????¹¢À@
FROM SERVER TO CLIENT: 11 bytes
01 01 01 01 01 01 01 01 01 01 01 ...........
FROM CLIENT TO SERVER: 18 bytes
01 01 01 01 01 53 48 4f 57 20 57 41 52 4e 49 4e .....SHOW WARNIN
47 53 GS
FROM SERVER TO CLIENT: 173 bytes
01 01 01 01 01 1b 01 01 01 01 64 65 66 01 01 01 ..........def...
01 4c 65 76 65 6c 01 01 3f 01 01 01 01 01 fd 81 .Level..?.....ý?
01 1f 01 01 1a 01 01 01 01 64 65 66 01 01 01 01 .........def....
43 6f 64 65 01 01 3f 01 01 01 01 01 01 a1 01 01 Code..?......¡..
01 01 1d 01 01 01 01 64 65 66 01 01 01 01 4d 65 .......def....Me
73 73 61 67 65 01 01 3f 01 01 01 01 01 fd 81 01 ssage..?.....ý?.
1f 01 01 01 01 01 01 fe 01 01 01 01 34 01 01 01 .......þ....4...
01 4e 6f 74 65 01 31 32 36 35 29 44 61 74 61 20 .Note.1265)Data
74 72 75 6e 63 61 74 65 64 20 66 6f 72 20 63 6f truncated for co
6c 75 6d 6e 20 27 63 6f 73 74 27 20 61 74 20 72 lumn 'cost' at r
6f 77 20 31 01 01 01 01 fe 01 01 01 01 ow 1....þ....
</snip>

Any help would be greately appreciated.



Edited 1 time(s). Last edit at 09/26/2006 03:33AM by Alexander Hristov.

Options: ReplyQuote


Subject
Written By
Posted
Data Truncation with server-side prepared statements
September 26, 2006 03:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.