Robin Schumacher

All -

I’m very proud of the MySQL 5.0 GA release today! Without question, our engineering team deserves a lot of accolades and pats on the back for a job very well done.

Last week, I was visiting a number of industry analyst groups and magazines, and was discussing the upcoming MySQL 5.0 GA release. It was very encouraging to hear them tell me (rather than vice-versa) what an important release this is for MySQL. Many shared with me their belief that 5.0 will open up many new areas of opportunity for MySQL that previously didn’t exist. Obviously, I think so too…

One last exciting nugget I wanted to share: dynamic SQL support in MySQL 5.0 stored procedures is now official! This means you can code dynamic SELECTs and other statements (DML, some DDL, etc.) within your stored procedures. For example, you can use the below proc to get accurate row counts for tables that may have outdated counts in the data dictionary:


CREATE PROCEDURE count_table_rows (dbname varchar(30))
DETERMINISTIC
BEGIN
DECLARE done tinyint DEFAULT 0;
DECLARE tabname varchar(64);
DECLARE asql VARCHAR(100);
DECLARE cur1 CURSOR FOR select table_name
from information_schema.tables
where table_schema=dbname
order by table_name;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

Enjoy 5.0!

Robin

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.