Response to recent eweek article
Do new features in MySQL 5.0 depend on InnoDB as a recent article by eweek implied? An interesting quote in the article implied there was no way to know:
“So you can take your pick of who to believe: On one side you have an ex-Oracle employee who’s working with a company that competes with Oracle plus an open-source expert and MySQL user of long standing. On the other side you have MySQL itself, which, of course, has got to put a sunny face on this. ”
True? No – in fact it can easily be proven in less than a few minutes. Observe…
First, we edit the my.cnf file, skip InnoDB enablement, and start the MySQL Server:
# The MySQL Server
[mysqld]
Skip-innodb
.
.
.
We then log in and check to ensure InnoDB is not enabled after startup:
mysql> show engines; +------------+----------+----------------------------------------------------------------+ | Engine | Support | Comment | +------------+----------+----------------------------------------------------------------+ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | HEAP | YES | Alias for MEMORY | | MERGE | YES | Collection of identical MyISAM tables | | MRG_MYISAM | YES | Alias for MERGE | | ISAM | NO | Obsolete storage engine, now replaced by MyISAM | | MRG_ISAM | NO | Obsolete storage engine, now replaced by MERGE | | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys | | INNOBASE | DISABLED | Alias for INNODB | | BDB | NO | Supports transactions and page-level locking | | BERKELEYDB | NO | Alias for BDB | | NDBCLUSTER | DISABLED | Clustered, fault-tolerant, memory-based tables | | NDB | DISABLED | Alias for NDBCLUSTER | | EXAMPLE | YES | Example storage engine | | ARCHIVE | YES | Archive storage engine | | CSV | YES | CSV storage engine | | FEDERATED | YES | Federated MySQL storage engine | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | +------------+----------+----------------------------------------------------------------+
So InnoDB is clearly disabled in MySQL. Let’s find out if any 5.0 features are dependent upon InnoDB.
Let’s first test stored procedures:
mysql> show create table SH_Part\G
*************************** 1. row ***************************
Table: SH_Part
Create Table: CREATE TABLE `SH_Part` (
`Model` int(11) NOT NULL,
`ProductID` int(11) NOT NULL,
`Serial_Number` char(12) NOT NULL,
`Sub_Category` char(5) NOT NULL,
`Version` int(11) NOT NULL,
`Part_Name` char(24) NOT NULL,
`Comment1` char(30) NOT NULL,
`Price` double NOT NULL,
`VendorID` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> delimiter //
mysql> create procedure testing ()
-> begin
-> select count(*) from SH_Part;
-> end
-> //
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;
mysql> call testing();
+----------+
| count(*) |
+----------+
| 40000 |
+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Now, how about a trigger that encrypts data on insert:
mysql> create table test (userid int, username varchar(20), userssn varchar(15));
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter //
mysql> create trigger t_test_insert before insert on test
-> for each row
-> begin
-> set NEW.userssn = aes_encrypt(NEW.userssn,'password');
-> end
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> insert into test values (1, 'robin','432123454');
Query OK, 1 row affected, 0 warning (0.01 sec)
mysql> select * from test;
+--------+----------+--------------------+
| userid | username | userssn |
+--------+----------+--------------------+
| 1 | robin | O╗¿▓A"⌡╕Qi►ô╫█v |
+--------+----------+--------------------+
1 row in set (0.00 sec)
Let’s now finish our test with a view creation:
mysql> create view v_test as select userid, username,
-> aes_decrypt(userssn,'password') as userssn
-> from test;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_test;
+--------+----------+--------------+
| userid | username | userssn |
+--------+----------+--------------+
| 1 | robin | 432123454 |
+--------+----------+--------------+
1 row in set (0.00 sec)
We could continue on with all other MySQL 5.0 features, but the above tests should suffice to prove beyond any doubt that new 5.0 features such as stored procedures, triggers, and views are not dependent on InnoDB.