yup. changing my column definitions fixed everything. we were under the
mistaken impression that float(x,y) meant constraints only on the
display, not the storage. thanks for everyone's help.
jeff
Heikki Tuuri wrote:
> Jeff,
>
> I believe the old behavior was erroneous.
>
> http://dev.mysql.com/doc/mysql/en/Numeric_type_overview.html
> "
> FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
> A small (single-precision) floating-point number. Allowable values
> are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to
> 3.402823466E+38. If UNSIGNED is specified, negative values are disallowed. M
> is the display width and D is the number of decimals. FLOAT without
> arguments or FLOAT(p) (where p is in the range from 0 to 24) stands for a
> single-precision floating-point number.
> "
>
> Define the table like mathis2 below, with less decimal places specified in
> D. I guess D means the number of decimal places after the period '.' in the
> representation.
>
> Best regards,
>
> Heikki
> Innobase Oy
> InnoDB - transactions, row level locking, and foreign keys for MySQL
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
> tables
> http://www.innodb.com/order.php
>
> Order MySQL support from http://www.mysql.com/support/index.html
>
> ........................
>
> heikki@hundin:~/mysql-4.1/client> ./mysql test
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 1 to server version: 4.1.4-beta-debug-log
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> select * from mathis;
> +----------------+----------------+----------------+
> | a | b | c |
> +----------------+----------------+----------------+
> | -999.250000000 | -345.269989014 | 456.779998779 |
> | -9.250000000 | -5.269999981 | 6.780000210 |
> | 999.250000000 | 345.269989014 | -456.779998779 |
> +----------------+----------------+----------------+
> 3 rows in set (0.11 sec)
>
> mysql> insert into mathis values (-9999.25, -3451.27, 4560.78);
> Query OK, 1 row affected, 3 warnings (0.01 sec)
>
> mysql> show warnings;
> +---------+------+------------------------------------------------------+
> | Level | Code | Message |
> +---------+------+------------------------------------------------------+
> | Warning | 1264 | Data truncated; out of range for column 'a' at row 1 |
> | Warning | 1264 | Data truncated; out of range for column 'b' at row 1 |
> | Warning | 1264 | Data truncated; out of range for column 'c' at row 1 |
> +---------+------+------------------------------------------------------+
> 3 rows in set (0.00 sec)
>
> mysql> select * from mathis;
> +----------------+----------------+----------------+
> | a | b | c |
> +----------------+----------------+----------------+
> | -999.250000000 | -345.269989014 | 456.779998779 |
> | -100.000000000 | -100.000000000 | 100.000000000 |
> | -9.250000000 | -5.269999981 | 6.780000210 |
> | 999.250000000 | 345.269989014 | -456.779998779 |
> +----------------+----------------+----------------+
> 4 rows in set (0.00 sec)
>
> mysql> alter table mathis type = myisam;
> Query OK, 4 rows affected, 1 warning (0.07 sec)
> Records: 4 Duplicates: 0 Warnings: 0
>
> mysql> show warnings;
> +---------+------+----------------------------------------------------------
> ----
> ------------+
> | Level | Code | Message
> |
> +---------+------+----------------------------------------------------------
> ----
> ------------+
> | Warning | 1287 | 'TYPE=storage_engine' is deprecated; use
> 'ENGINE=storage_engi
> ne' instead |
> +---------+------+----------------------------------------------------------
> ----
> ------------+
> 1 row in set (0.00 sec)
>
> mysql> insert into mathis values (-9999.25, -3451.27, 4560.78);
> ERROR 1062 (23000): Duplicate entry '-100.000000000' for key 1
> mysql> insert into mathis values (-9999.25, -3451.27, 4560.78);
> ERROR 1062 (23000): Duplicate entry '-100.000000000' for key 1
> mysql>
> mysql>
> mysql> create table mathis2(a float(11,2) not null primary key, b
> float(11,2), c
> float(11,2), index(b)) type = innodb;
> Query OK, 0 rows affected, 1 warning (0.01 sec)
>
> mysql>
> mysql>
> mysql> insert into mathis2 values (-9999.25, -3451.27, 4560.78);
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from mathis2;
> +----------+----------+---------+
> | a | b | c |
> +----------+----------+---------+
> | -9999.25 | -3451.27 | 4560.78 |
> +----------+----------+---------+
> 1 row in set (0.00 sec)
>
> mysql>
>
> ----- Alkuperäinen viesti -----
> Lähettäjä: "Jeff Mathis" <jmathis@stripped>
> Vastaanottaja: <java@stripped>; "Heikki Tuuri"
> <Heikki.Tuuri@stripped>
> Lähetetty: Thursday, August 05, 2004 11:00 PM
> Aihe: bug with 4.1.3?
>
>
>
>>been trying to track a weird issue here. we just upgraded the server
>>from 4.0.4 to 4.1.3. The server starts fine, and shows no errors in the
>>log file. we're using all innodb tables.
>>
>>I have a table with a float(11,9) column. using either the 3.0.11 or
>>3.0.14 Connector/J packages, the column appears to only store -100 or
>>100, even though the insert SQL clearly specifies a different number.
>>
>>is there an issue with 4.1.3?
>>
>>jeff
>>--
>>Jeff Mathis, Ph.D. 505-955-1434
>>Prediction Company jmathis@stripped
>>525 Camino de los Marquez, Ste 6 http://www.predict.com
>>Santa Fe, NM 87505
>>
>
>
>
--
Jeff Mathis, Ph.D. 505-955-1434
Prediction Company jmathis@stripped
525 Camino de los Marquez, Ste 6 http://www.predict.com
Santa Fe, NM 87505
