MySQL and Oracle

Over the last couple of months I have been working on a MySQL based system for my current customer. This made me realize, once again, how easy we can do stuff in an Oracle based system.
On one hand this is kind of awkward, not being able to do what you are used to, on the other hand it makes you come up with solutions to fix the issues. Some are better than others, but in the end it all works.
These are just a few of the quirks I have come across and for which I had to find another solution.

No Packages

In my PL/SQL code I like to use packages for just about everything. This way I can logically group programs that belong together. In MySQL there is no object like package. Everything is a stand-alone function or procedure. That also means there are no private programs. On the other hand, in this project I have a user that ‘owns’ the objects (tables, view, programs etc.) and other users a granted access to the objects they need. In this case they have no access to tables and just read access to views.

No create or replace

In Oracle you can create or replace almost every object (except for tables). In MySQL you can use create or replace only on views. In all the other situations you have to drop the object first before you can create a new version. Not a big issue, since I am using scripts for the objects anyway, so one of the first lines is to drop the object.

Drop if exists

Somehow, we want to minimize the errors when running scripts, even if these errors aren’t a problem. If you try to drop an object that doesn’t exist, the database (Both Oracle and MySQL) will return an error, something like ‘object to be dropped doesn’t exist’. I actually don’t see this as an error, because the end result is the same. But somehow DBA’s are allergic to these kinds of errors, or any error. MySQL provides us with the IF EXISTS syntax. This will never result in an error, just the desired situation, the object gone.
In Oracle I have created something similar. In this case it is an anonymous block, but you could also create a stored procedure for this. Also, if you need to suppress a different error code, you can adapt the code accordingly.

declare
  table_or_view_does_not_exist exception;
  pragma exception_init(table_or_view_does_not_exist, -00942);
  object_does_not_exist exception;
  pragma exception_init(object_does_not_exist, -04043);
  sequence_does_not_exist exception;
  pragma exception_init(sequence_does_not_exist, -02289);
begin
  execute immediate q'[drop table t purge]'; -- execute the drop
exception
  when table_or_view_does_not_exist
    or object_does_not_exist
    or sequence_does_not_exist then null; -- on purpose, hide any error
end;
/

Functions needs purity info

Every function you create need information about its purity level. It doesn’t have to be accurate (I found out ;-)) but you have to add at least one of the items: DETERMINISTIC, NO SQL, READS SQL DATA. You can, and probably should, add one or more of the other items, although some combinations are not allowed, which makes sense, I think.

Delimiter

The default delimiter for a statement is the semi-colon (;), in Oracle this is officially the slash (/). When you want to create a program, the first thing you do is tell MySQL to use a different delimiter to end a statement.

Delimiter //
Create procedure foo
Begin
  Select ‘bar’;
End//
Delimiter ;

No DBMS_OUTPUT.PUT_LINE

I don’t know about you, but when I am building code and I don’t know exactly how stuff works (new features or even a new database environment) I put a lot of DBMS_OUTPUT.PUT_LINE statements in my code, so I can see what it is doing. That is, only during development and when I don’t have access to a nice debugging IDE.

But there is no such thing a DBMS_OUTPUT.PUT_LINE in MySQL. For as far as I know, there is no way to output messages to the console. If there is, please let me know in the comments ;-).

So, I create a procedure PL myself (When you have a choice, you choose a shorter program name than the 20 character DBMS_OUTPUT.PUT_LINE ;-)). If you just select something, without an into, the results gets echoed to the console.

DROP PROCEDURE if exists PL;
DELIMITER //
CREATE PROCEDURE PL (output  VARCHAR(255))
BEGIN
   SELECT output;
END;
//
DELIMITER ;

Missing SQLERRM

There is no function to return the text for an error code. At least, I couldn’t find it. But I needed a function to return the text associated with the error code my code could return. What better name for such a function than SQLERRM ;-). It doesn’t return the text for the MySQL error codes, but at least I can work a bit like I do in my Oracle databases.

No Sequences

There are no sequences available in MySQL, at least not the way we know them in Oracle. I needed a cross session counter to return me the numbers from 1 to 99 in order and at the end loop back to 1.

In Oracle I would create a sequence like this:

create sequence s 
       minvalue 1   maxvalue 99
       start with 1 increment by 1
       cycle        nocache
/

But, like I said, in MySQL there is nothing like that available. So I came up with my own version. It consists of a table and some code:

drop table if exists sequences;

create table sequences
( name varchar(32)
, min integer
, max integer
, increment tinyint
, last integer
, primary key              (name)
)ENGINE = InnoDB;

insert into sequences(name, min, max, increment, last) values ('my_seq', 1, 99, 1, 0);

drop procedure if exists my_seq_val;
drop function if exists my_seq_currval;
drop function if exists my_seq_nextval;
delimiter $$
create procedure my_seq_val(
  in  increment boolean
, out result    integer)
  reads sql data
  modifies sql data
  comment 'gets (and increments) the last value'
  contains sql 
  sql security invoker
begin
  if increment then
    update sequences set last=case
                            when last+increment > max then min
                            else last+increment
                          end
   where 1=1
       and name = 'my_seq';
-- Even though this is a procedure, it gets called from a function
-- Error code 1422: Explicit or implicit commit is not allowed in a stored function or trigger
--     commit;
  end if;  
  select last into result from sequences where name = 'my_seq';
end$$

create function my_seq_currval() 
  returns integer
  deterministic
  reads sql data
  comment 'Gets the current value from the sequence'
  contains sql 
  sql security invoker
begin
  declare result integer;
  call my_seq_val(false, result);
  return result;
end$$

create function my_seq_nextval() 
  returns integer
  deterministic
  modifies sql data
  comment 'Gets the next value from the sequence'
  contains sql 
  sql security invoker
begin
  declare result integer;
  call my_seq_val(true, result);
  return result;
end$$

delimiter ;

Probably not as fast as the Oracle version and I don’t know what will happen if two sessions increment the sequence at exactly the same moment, but the chance this will happen is low enough that I’ll take my chances 😉

Writing files

One of the requirements of the application was to write certain files. Files with status changes that can be transported to different systems. When it comes to filehandling, MySQL is rather limited compared to Oracle (which is limited itself). It is not possible to overwrite or to delete a file. When you try to write a file that already exists you’ll run into an error. This is one of the reasons I needed the sequences. The good news, in this case, is that we have a process in place that move the file from the location where it is written to a different location. That way we can write a file with the same number at a later stage.

No dynamic SQL in Function/Trigger

To create these files, with their different sequence numbers in the name, I wanted to use a bit of dynamic SQL. This worked really well until I wanted to call my procedure from a function or a trigger. You are not allowed to run dynamic SQL in a function or a trigger. Since it was a rather simple statement and there were ‘only’ 100 options I ended up with a big case statement where I had to specify a different filename for every value of the sequence.

All in all it was a fun project to work on. It made me realize (again) what a great environment the Oracle database is to work in. How easy things can be done, how many possibilities you have. On the other hand, if you can come up with some solutions, that take a bit more thinking and coding, you can also create a nice system in MySQL.

Leave a Reply

Your email address will not be published. Required fields are marked *