List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:September 19 2005 11:18am
Subject:Re: possible MySQL bug - insert into 'double' column problem with mysql 4.1
View as plain text  
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

Thread
possible MySQL bug - insert into 'double' column problem with mysql 4.1Andrew Braithwaite19 Sep
  • Re: possible MySQL bug - insert into 'double' column problem with mysql 4.1Martijn Tonies19 Sep
  • Re: possible MySQL bug - insert into 'double' column problem withmysql 4.1Roger Baklund19 Sep
  • How to bind mysqld to 2 ip on 3andrea_brujo_websol19 Sep
    • bind (listen) questionAndrea21 Sep
      • Re: bind (listen) questionAlexey Polyakov21 Sep
RE: possible MySQL bug - insert into 'double' column problem with mysql 4.1Andrew Braithwaite19 Sep
  • Re: possible MySQL bug - insert into 'double' column problem withmysql 4.1Andrew Braithwaite19 Sep