| Bug #66148 | incorrect 'data truncated' warning when inserting into decimal field | ||
|---|---|---|---|
| Submitted: | 1 Aug 2012 22:53 | Modified: | 13 Feb 2018 22:14 |
| Reporter: | Andrei D | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.1.32, 5.1.61, 5.1.63, 5.1.66 | OS: | MacOS (10.5) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | warning decimal | ||
[1 Aug 2012 22:53]
Andrei D
[2 Aug 2012 4:22]
Valeriy Kravchuk
What exact server version are you working with? I do not see any warnings with recent 5.5: macbook-pro:5.5 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 33 Server version: 5.5.28-debug Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create table tst (foo decimal(4,4)); Query OK, 0 rows affected (1.73 sec) mysql> insert into tst select 6e-4; Query OK, 1 row affected (0.17 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show warnings\G Empty set (0.00 sec)
[2 Aug 2012 16:10]
Andrei D
5.1 (seen it in 5.1.32 and 5.1.61). Sorry, I should have mentioned that.
[2 Aug 2012 17:49]
Peter Laursen
I do not get any such warning in 5.1.63 (64 bit on Win7) with the test case provided. peter (not a MySQL/Oracle person)
[2 Aug 2012 19:43]
Andrei D
seeing this issue with 64 bit mysql - 5.1.32 on OsX 10.6.6 - 5.1.56 on OsX 10.7.4 - 5.1.61 on CentOS 5.8
[3 Aug 2012 6:12]
Valeriy Kravchuk
Please, check with a recent version, 5.1.63.
[3 Aug 2012 16:03]
Andrei D
Yes, we see it in 5.1.63 as well (Centos 5.8). Output below. ------------------------------------------------------------ mysql> create table tst (foo decimal(4,4)); Query OK, 0 rows affected (0.07 sec) mysql> insert into tst select 6e-4; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1265 Message: Data truncated for column 'foo' at row 1 1 row in set (0.00 sec) mysql> SELECT * FROM tst; +--------+ | foo | +--------+ | 0.0006 | +--------+ 1 row in set (0.00 sec) mysql> SELECT 6e-4; +--------+ | 6e-4 | +--------+ | 0.0006 | +--------+ 1 row in set (0.00 sec) mysql> status; -------------- mysql Ver 14.14 Distrib 5.1.63, for unknown-linux-gnu (x86_64) using readline 5.1 Connection id: 1 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.63-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 1 min 40 sec Threads: 1 Questions: 16 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 10 Queries per second avg: 0.160 --------------
[3 Aug 2012 18:06]
Valeriy Kravchuk
Verified on Mac OS X: macbook-pro:5.1 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.66-debug Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select @@sql_mode; +-------------------+ | @@sql_mode | +-------------------+ | STRICT_ALL_TABLES | +-------------------+ 1 row in set (0.00 sec) mysql> create table tst (foo decimal(4,4)); Query OK, 0 rows affected (0.06 sec) mysql> insert into tst select 6e-4; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1265 Message: Data truncated for column 'foo' at row 1 1 row in set (0.00 sec) mysql> set session sql_mode=''; Query OK, 0 rows affected (0.04 sec) mysql> select @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.00 sec) mysql> insert into tst select 6e-4; Query OK, 1 row affected, 1 warning (0.04 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1265 Message: Data truncated for column 'foo' at row 1 1 row in set (0.00 sec) No warnings on 5.5 though: macbook-pro:5.5 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.28-debug Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop table tst; Query OK, 0 rows affected (0.19 sec) mysql> select @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.00 sec) mysql> create table tst (foo decimal(4,4)); Query OK, 0 rows affected (0.37 sec) mysql> insert into tst select 6e-4; Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0
[3 Aug 2012 18:10]
Peter Laursen
why don't I get a warning on my 5.1.63? Is it because I am using Windows (compiler-related)?
[13 Feb 2018 22:14]
Roy Lyseng
Posted by developer: Fixed in 5.5 and up.
