Description:
We found our Java application was failing to return some data. We eventually tried a manual query in mysql and found that mysql was returning no data and a warning "Warning (Code 1292): Truncated incorrect DOUBLE value: 'testing'"
This query is simple and queries a varchar column and a bit(1) column. Changing the bit column to SMALLINT with alter table fixes the problem immediately. all columns must be not null for this to happen.
How to repeat:
mysql> create table blog_entry (id bigint(20) not null auto_increment,
-> version bigint(20) not null ,
-> content text not null ,
-> format varchar(40) not null ,
-> last_update datetime not null ,
-> live bit(1) not null ,
-> name varchar(50) not null unique,
-> publish_date datetime not null ,
-> title varchar(200) not null, primary key(id)) ENGINE = MYISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> describe blog_entry;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| version | bigint(20) | NO | | NULL | |
| content | text | NO | | NULL | |
| format | varchar(40) | NO | | NULL | |
| last_update | datetime | NO | | NULL | |
| live | bit(1) | NO | | NULL | |
| name | varchar(50) | NO | UNI | NULL | |
| publish_date | datetime | NO | | NULL | |
| title | varchar(200) | NO | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
mysql> insert into blog_entry (name, live) values ('live one', TRUE);
Query OK, 1 row affected, 6 warnings (0.00 sec)
mysql> insert into blog_entry (name, live) values ('dead one', FALSE);
Query OK, 1 row affected, 6 warnings (0.00 sec)
mysql> select name from blog_entry where name = 'live one' and live = TRUE;
Empty set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'live one' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)
Suggested fix:
Workaround is to alter column to smallint, or remove not null constraint.
This problem is not seen in mysql 5.0.45 running on Mac OS X, but we are unclear if it is the different platform or the newer version that fixes it.
Red hat linux currently ships with 5.0.22 as newest, and this problem is 100% reproducible on that platform.
Description: We found our Java application was failing to return some data. We eventually tried a manual query in mysql and found that mysql was returning no data and a warning "Warning (Code 1292): Truncated incorrect DOUBLE value: 'testing'" This query is simple and queries a varchar column and a bit(1) column. Changing the bit column to SMALLINT with alter table fixes the problem immediately. all columns must be not null for this to happen. How to repeat: mysql> create table blog_entry (id bigint(20) not null auto_increment, -> version bigint(20) not null , -> content text not null , -> format varchar(40) not null , -> last_update datetime not null , -> live bit(1) not null , -> name varchar(50) not null unique, -> publish_date datetime not null , -> title varchar(200) not null, primary key(id)) ENGINE = MYISAM; Query OK, 0 rows affected (0.01 sec) mysql> describe blog_entry; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | version | bigint(20) | NO | | NULL | | | content | text | NO | | NULL | | | format | varchar(40) | NO | | NULL | | | last_update | datetime | NO | | NULL | | | live | bit(1) | NO | | NULL | | | name | varchar(50) | NO | UNI | NULL | | | publish_date | datetime | NO | | NULL | | | title | varchar(200) | NO | | NULL | | +--------------+--------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec) mysql> insert into blog_entry (name, live) values ('live one', TRUE); Query OK, 1 row affected, 6 warnings (0.00 sec) mysql> insert into blog_entry (name, live) values ('dead one', FALSE); Query OK, 1 row affected, 6 warnings (0.00 sec) mysql> select name from blog_entry where name = 'live one' and live = TRUE; Empty set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+----------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'live one' | +---------+------+----------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: Workaround is to alter column to smallint, or remove not null constraint. This problem is not seen in mysql 5.0.45 running on Mac OS X, but we are unclear if it is the different platform or the newer version that fixes it. Red hat linux currently ships with 5.0.22 as newest, and this problem is 100% reproducible on that platform.