Tuesday, September 20, 2005

MySQL to an Oracle DBA, Part II

Found out some things that you need to be aware of when converting from Oracle to MySQL. Let me create a table and show you some examples.

mysql> create table number_stuff (
-> description varchar(20) not null,
-> ti tinyint,
-> ival integer,
-> fl float,
-> dc decimal(15,5));
Query OK, 0 rows affected (0.13 sec)


This is kind of cool. You can insert multiple values in one shot.

mysql> insert into number_stuff values
-> ('123',123, 123, 123, 123),
-> ('384',384, 384, 384, 384),
-> ('12.5', 12.5, 12.5, 12.5, 12.5),
-> ('13.5', 13.5, 13.5, 13.5, 13.5),
-> ('14.5', 14.5, 14.5, 14.5, 14.5),
-> ('12345.67890',12345.67890, 12345.67890, 12345.67890, 12345.67890);
Query OK, 6 rows affected, 2 warnings (1.78 sec)
Records: 6 Duplicates: 0 Warnings: 2

mysql> show warnings
-> ;
+---------+------+------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'ti' at row 2 |
| Warning | 1264 | Out of range value adjusted for column 'ti' at row 6 |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)


Fair enough. We tried to insert values outside the bounds (-127 to 127) for TINYINT, and it makes sense that they’d fail. So let’s see what we got here:

mysql> select * from number_Stuff;
+-------------+------+-------+---------+-------------+
| description | ti | ival | fl | dc |
+-------------+------+-------+---------+-------------+
| 123 | 123 | 123 | 123 | 123.00000 |
| 384 | 127 | 384 | 384 | 384.00000 |
| 12.5 | 13 | 13 | 12.5 | 12.50000 |
| 13.5 | 14 | 14 | 13.5 | 13.50000 |
| 14.5 | 15 | 15 | 14.5 | 14.50000 |
| 12345.67890 | 127 | 12346 | 12345.7 | 12345.67890 |
+-------------+------+-------+---------+-------------+
6 rows in set (0.00 sec)


Our first row looks like it went in just fine.

The second row is what makes the DBA in me a little nervous. We tried to insert the integer value 384 into a TINYINT column and MySQL correctly gave us a warning. However, it then modified our data and put a 127 (the maximum value) into the TINYINT field.

Rows 3, 4, and 5 appear to have gone in without a hitch. But wait, it looks like MySQL insert the rounded number into the columns that contained integers. Hold on, you’re being paranoid, Oracle does the same thing:

SQL> create table xyz (x integer);

Table created.

SQL> insert into xyz (x) values (12.5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

X
----------
13


But what am I trying to show you with these numbers? Just that FLOAT and NUMBER round differently:

mysql> select description, round(fl), round(dc) from number_stuf
-> where description in ('12.5','13.5', '14.5')
-> ;
+-------------+-----------+-----------+
| description | round(fl) | round(dc) |
+-------------+-----------+-----------+
| 12.5 | 12 | 13 |
| 13.5 | 13 | 14 |
| 14.5 | 14 | 15 |
+-------------+-----------+-----------+
3 rows in set (0.00 sec)


Last, but not least, lets look at real numbers.


mysql> select description, fl, dc
-> from number_stuff
-> where description = '12345.67890';
+-------------+---------+-------------+
| description | fl | dc |
+-------------+---------+-------------+
| 12345.67890 | 12345.7 | 12345.67890 |
+-------------+---------+-------------+
1 row in set (0.00 sec)


Is that fl a display issue or a storage issue? Lets just use the TRUNCATE function to see what MySQL is really storing:

mysql> select description, fl, truncate(fl, 25) trunc_fl, dc
-> from number_stuff
-> where description = '12345.67890';
+-------------+---------+---------------------------------+-------------+
| description | fl | trunc_fl | dc |
+-------------+---------+---------------------------------+-------------+
| 12345.67890 | 12345.7 | 12345.6787109375000000000000000 | 12345.67890 |
+-------------+---------+---------------------------------+-------------+
1 row in set (0.00 sec)


Yup, that’s really what MySQL is storing, a wrong value. But does it really? Lets multiply by 10000 to see:

mysql> select description, fl, fl * 10000 big_fl, dc
-> from number_stuff
-> where description = '12345.67890';
+-------------+---------+-----------------+-------------+
| description | fl | big_fl | dc |
+-------------+---------+-----------------+-------------+
| 12345.67890 | 12345.7 | 123456787.10938 | 12345.67890 |
+-------------+---------+-----------------+-------------+
1 row in set (0.00 sec)


Sure enough, that’s the value being stored.

Note to self: Don’t use FLOAT to store money or fractional shares.

As we were going through data types, I couldn’t understand why there were all these different INTEGER subtypes. The reason is that MySQL will take the entire 4 bytes in a 4 byte INTEGER even though it might not need it. For example, you create the table below:

CREATE TABLE xyz (
id BIGINT);

BIGINT is a data type that is an 8 byte integer. You intend to fill xyz.id with sequential numbers starting at 1. When you insert the value 1 into xyz.id, you have taken 8 bytes. This is different than Oracle in that the number data types are variable length. Is this good or bad, depends. It’s just something to be aware of.

5 comments:

SydOracle said...

Apparently float inherently has rounding issues as it allows 'any' number to be stored in fewer number of bytes than actually required.

http://www.cmpe.boun.edu.tr/courses/cmpe360/spring2001/goldberg91what.pdf

Oracle gets around this by not actually using it. A FLOAT is stored as an Oracle NUMBER, which can use a lot more space than a FLOAT would. However since an Oracle NUMBER only use as much storage as necessary, in most cases it will 'save' enough storage to offset the times when it doesn't.

Anonymous said...

Hi Jeff,

Nice post. Ironic because I'm doing an article series for DBA Zine called MySQL for Oracle DBAs. I do a lot with MySQL myself. I'll keep a closer eye on your series.


Sean (shull@iheavy.com)
http://iheavy.com

Jeff Hunter said...


Nice post. Ironic because I'm doing an article series for DBA Zine called MySQL for Oracle DBAs. I do a lot with MySQL myself. I'll keep a closer eye on your series.

feel free to send donations to...

Anonymous said...

Good post Jeff, I blogged it at http://www.openwin.org/mike/index.php/archives/2005/09/mysql-from-an-oracle-dbas-point-of-view/

As for the issues you see with silent data changes, this can be solved by installing MySQL 5 and using the TRADITIONAL SQL mode. In TRADITIONAL mode the server will return errors and roll back transactions when data is out of range.

http://dev.mysql.com/doc/mysql/en/server-sql-mode.html

Mike

Anonymous said...

See Mike Hillyer's post about the out-of-bounds handling in 5.0.

5.0 also has precision math, so money will not go walkabout in fixed-point col types like NUMERIC.

FLOAT is inherently unsuitable for money.
I believe someone else already commented that Oracle doesn't actually store floats as floats but as NUMBERs.....