I've already opened a support ticket with MySQL on this issue, but thought someone here might know the answer, too:
Hi there,I'm trying to make sure I'm not doing something stupid. I'm trying to migrate from 4.0 to 4.1, but I'm hitting a snag. I can't find anything in the docs to describe what I'm seeing.
MySQL's docs seem to suggest that a column with 'float(5,2)' specifies as display width of 5 and a decimal precision of 2. I assumed that the 'display width' worked like the other numeric types and would allow storing larger values.
On 3.32 and 4.0, this was the case. I was able to store large numbers in this float:
mysql> explain testFloat;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| Floating | float(5,2) unsigned | | | 0.00 | |
+----------+---------------------+------+-----+---------+-------+
1 row in set (0.02 sec)
mysql> insert into testFloat values ('134567.12344321');
Query OK, 1 row affected (0.00 sec)
mysql> select * from testFloat;
+-----------+
| Floating |
+-----------+
| 134567.12 |
+-----------+
1 row in set (0.00 sec)
After upgrading to 4.1, however, this functionality seems partially broken:
mysql> explain testFloat;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| Floating | float(5,2) unsigned | | | 0.00 | |
+----------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into testFloat values ('134567234798129348571234.12344321');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from testFloat;
+----------+
| Floating |
+----------+
| 999.99 |
+----------+
1 row in set (0.00 sec)
I say partially broken, because my existing 4.0 data is fine. In fact, I
can change an InnoDB table to MyISAM and then back to InnoDB using
'alter table' and it retains the proper values.
FYI, this is on both 4.1.11 RPM distro from MySQL and 4.1.10a-1 from Red Hat Enterprise 4. Occurs on both x86 and x86_64, RH9 and RHEL4 respectively.
Any ideas? Is this really an incompatible change that just hasn't been documented? Am I missing something obvious? I have a lot of tables I expected to behave the same way as they did in prior releases...
Don
smime.p7s
Description: S/MIME Cryptographic Signature
