List:General Discussion« Previous MessageNext Message »
From:Andrew Braithwaite Date:September 19 2005 8:25pm
Subject:Re: possible MySQL bug - insert into 'double' column problem with
mysql 4.1
View as plain text  
Hmm - and further to this.....  Strange differences of behaiour between the
last two production versions of MySQL....

sh-2.05b# ln -s mysql-standard-4.0.24-apple-darwin7.7.0-powerpc mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.24-standard-log

mysql> create table wibble3 (
    -> test1 double(3,6),
    -> test2 double(7,6),
    -> test3 double(9,6));
Query OK, 0 rows affected (0.11 sec)

mysql> desc wibble3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| test1 | double(7,6) | YES  |     | NULL    |       |
| test2 | double(7,6) | YES  |     | NULL    |       |
| test3 | double(9,6) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.17 sec)

mysql> insert into wibble3 values(50.123456,50.123456,50.123456);
Query OK, 1 row affected (0.08 sec)

mysql> select * from wibble3;
+-----------+-----------+-----------+
| test1     | test2     | test3     |
+-----------+-----------+-----------+
| 50.123456 | 50.123456 | 50.123456 |
+-----------+-----------+-----------+
1 row in set (0.08 sec)

sh-2.05b# ln -s mysql-standard-4.1.10a-apple-darwin7.7.0-powerpc mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.10a-standard-log

mysql> insert into wibble3 values(50.123456,50.123456,50.123456);
Query OK, 1 row affected, 2 warnings (0.11 sec)

mysql> select * from wibble3;
+-----------+-----------+-----------+
| test1     | test2     | test3     |
+-----------+-----------+-----------+
| 50.123456 | 50.123456 | 50.123456 |
|  9.999999 |  9.999999 | 50.123456 |
+-----------+-----------+-----------+
2 rows in set (0.06 sec)

Looks like while MySQL 4.1 was not changing what was stored in the data but
changing what is inserted into new records to match the proper data tye
definitions.


On 19/9/05 17:49, "Andrew Braithwaite" <Andrew@stripped> wrote:

> Thanks; you're absolutely right - doh!  It's just amazing that this ever
> worked in MySQL 4.0 and below...
> 
> Andrew
> 
> -----Original Message-----
> From: Roger Baklund [mailto:roger@stripped]
> Sent: Mon, 19 Sep 2005 12:27
> To: mysql@stripped
> Cc: Andrew Braithwaite
> Subject: Re: possible MySQL bug - insert into 'double' column problem
> with mysql 4.1
> 
> Andrew Braithwaite wrote:
>> Hi All,
>> 
>> 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.
> [...]
>> | lon           | double(7,6)  | YES  | MUL | NULL    |
> |
>> | lat           | double(7,6)  | YES  |     | NULL    |
> |
> 
>  From the manual:
> 
> DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
> 
> M is the total number of decimal digits and D is the number of digits
> following the decimal point.
> 
> <URL: http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html >
> 
>> 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)
>> 
>> mysql> select * from table1 where id=8002\G
>> *************************** 1. row ***************************
>>            id: 8002
>>    GeoQuality: 2a
>>           lon: -0.361080
>>           lat: 9.999999
> 
> You have created your columns with a max width of 7, out of which 6 are
> decimals, but you are trying to insert a number with 8 digits.
> 
> --
> Roger
> 
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 


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