From: Martijn Tonies
Date: September 19 2005 11:18am
Subject: Re: possible MySQL bug - insert into 'double' column problem with mysql 4.1
List-Archive: http://lists.mysql.com/mysql/189257
Message-Id: <024901c5bd0b$e2f871f0$c802a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hello Andrew,

> I have a strange error when trying to insert into a table with 2
> 'double' fields.  It inserts into the 1st field OK but fills the 2nd one
> with nines.  See below for a complete recreate.
>
> Is this a known problem?  Does anyone have a solution?
>
> I'm running standard MySQL binaries on redhat linux 7.2 kernel
> 2.4.20-28.7smp.  Help!
>
> Cheers,
>
> Andrew
>
>
> mysql> desc table1;
> +---------------+--------------+------+-----+---------+----------------+
> | Field         | Type         | Null | Key | Default | Extra          |
> +---------------+--------------+------+-----+---------+----------------+
> | id            | mediumint(6) |      | PRI | NULL    | auto_increment |
> | GeoQuality    | varchar(5)   | YES  |     | NULL    |                |
> | lon           | double(7,6)  | YES  | MUL | NULL    |                |
> | lat           | double(7,6)  | YES  |     | NULL    |                |
> | GeocodeDate   | date         | YES  |     | NULL    |                |
> | GeocodeSource | varchar(25)  | YES  |     | NULL    |                |
> | state         | varchar(70)  | YES  |     | NULL    |                |
> | client_id     | varchar(40)  | YES  | MUL | NULL    |                |
> +---------------+--------------+------+-----+---------+----------------+
> 15 rows in set (0.00 sec)
>
> mysql> INSERT INTO table1VALUES
> (8002,'2a',-0.361080,39.468270,'2005-08-31','ES052',NULL,NULL);
> Query OK, 1 row affected, 1 warning (0.01 sec)

My guess is that the second value overflows the maximum value in the column
and that MySQL is SILENTLY inserting the max value for the column.

Have you tried a larger "double" column?

Obviously: screw silent data changes!!

> mysql> select * from table1 where id=8002\G
> *************************** 1. row ***************************
>            id: 8002
>    GeoQuality: 2a
>           lon: -0.361080
>           lat: 9.999999
>   GeocodeDate: 2005-08-31
> GeocodeSource: ES052
>         state: NULL
>     client_id: NULL

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com