mpopp.net blog – Markus Popp

Is it possible to run Stored Procedures on MySQL 4.x? Yes and no – of course the feature is not available directly in MySQL 4.x, but if you have a MySQL 5.x server with the FEDERATED Storage Engine available, it’s no big deal to accomplish that.

Here’s how it works – we start with this on a MySQL 4.x server:

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 4.0.18-nt |
+-----------+
1 row in set (0.03 sec)

mysql> SHOW CREATE TABLE tt \G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `val` int(10) unsigned NOT NULL default '0',
  `ts` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM
1 row in set (0.03 sec)

mysql> SELECT * FROM tt;
Empty set (0.03 sec)

The next step is to create a FEDERATED table with the same definition on a MySQL 5.x server:

mysql> SELECT version();
+-----------------+
| version()       |
+-----------------+
| 5.1.13-beta-log |
+-----------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `tt` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `val` int(10) unsigned NOT NULL default '0',
    ->   `ts` timestamp(14) NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=FEDERATED
    -> CONNECTION='mysql://user:password@remote_4x_server/database/tt';
Query OK, 0 rows affected (0.72 sec)

All we need to do is to create a Stored Procedure on the MySQL 5.x server which accesses the tt FEDERATED table:

DROP PROCEDURE IF EXISTS fill_tt;

DELIMITER //

CREATE PROCEDURE fill_tt (_rows INT)
BEGIN
  DECLARE cnt INT DEFAULT 0;

  WHILE cnt < _rows DO
    INSERT INTO tt (val) VALUES (FLOOR(RAND()*1000));

    SET cnt := cnt + 1;
  END WHILE;
END //

DELIMITER ;

Now it’s simple to insert a few rows using this Stored Procedure:

mysql> CALL fill_tt(10);
Query OK, 1 row affected (0.69 sec)

mysql> SELECT * FROM tt;
+----+-----+---------------------+
| id | val | ts                  |
+----+-----+---------------------+
|  1 | 952 | 2006-11-23 01:57:54 |
|  2 | 591 | 2006-11-23 01:57:54 |
|  3 |  98 | 2006-11-23 01:57:54 |
|  4 | 718 | 2006-11-23 01:57:54 |
|  5 | 295 | 2006-11-23 01:57:54 |
|  6 | 324 | 2006-11-23 01:57:54 |
|  7 | 733 | 2006-11-23 01:57:54 |
|  8 | 693 | 2006-11-23 01:57:54 |
|  9 | 269 | 2006-11-23 01:57:54 |
| 10 | 264 | 2006-11-23 01:57:54 |
+----+-----+---------------------+
10 rows in set (0.09 sec)

Running the same query on the MySQL 4.x server proves that the records have been properly inserted:

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 4.0.18-nt |
+-----------+
1 row in set (0.03 sec)

mysql> SELECT * FROM tt;
+----+-----+----------------+
| id | val | ts             |
+----+-----+----------------+
|  1 | 952 | 20061123015754 |
|  2 | 591 | 20061123015754 |
|  3 |  98 | 20061123015754 |
|  4 | 718 | 20061123015754 |
|  5 | 295 | 20061123015754 |
|  6 | 324 | 20061123015754 |
|  7 | 733 | 20061123015754 |
|  8 | 693 | 20061123015754 |
|  9 | 269 | 20061123015754 |
| 10 | 264 | 20061123015754 |
+----+-----+----------------+
10 rows in set (0.03 sec)