Migrating from Oracle to MySQL

With the loss of the Oracle Standard Edition One license, and the free Oracle Express Edition database stuck at the old 11g Release 2 from 2014, many smaller projects are considering whether MySQL might be an alternative.

If you are building your application from scratch, most projects can start with any relational database. But if you have an existing database, you need to investigate carefully if it makes sense to move.

Feature Comparison

Many of the features of Oracle are also available in MySQL:

• Tables
• Views
• Primary keys
• Unique keys
• Foreign keys
• Procedures
• Functions
• Triggers

For some Oracle features, MySQL has equivalent functionality but implemented differently. For example, MySQL does not have sequences but instead allows columns to be defined as AUTO_INCREMENT. This eliminates the primary key trigger that was necessary until Oracle 12.

MySQL does not have anything corresponding to PL/SQL packages, only functions and procedures. This means that you also do not have package variables, but will have to use session-scoped user defined variables (@my_var) to store values.

Users and Security

Databases, schemas, and users also work differently. In Oracle, you have one database with many schemas that are also users. In MySQL, the word DATABASE is synonymous with SCHEMA and is decoupled from users. So users need to be granted access to schemas/databases.

The security model in MySQL is generally much simpler than in Oracle, so if you have detailed and/or complicated security in an Oracle application, it will be hard to migrate to MySQL.

Data Types

The data types are different between Oracle and MySQL, but can generally be mapped easily.

Note that the MySQL DATE variable only has the date part and no time. The easy conversion is to turn every Oracle DATE into a MySQL DATETIME, but if you never need the time element, it makes sense to use MySQL DATE for these values.

MySQL has lots of different datatypes, from 3-digit TINYINT to 19-digit BIGINT. You need to use one of the INT types for primary columns that use AUTO_INCREMENT.

What about PL/SQL?

The programming language in MySQL is much simpler than PL/SQL, and MySQL does not have anything like the capability of the built-in packages in Oracle. If you are using many built-in packages, it will become very hard to move to MySQL.

While you have the same table trigger capabilities in MySQL as in Oracle (BEFORE/AFTER, row or table), you don’t have all the other triggers Oracle offers, like DDL triggers, ON-LOGON etc.

There are a number of syntactic differences – for example, PL/SQL EXCEPTION branches are handled in MySQL with DECLARE HANDLER. MySQL has basic control structures like IF-THEN-ELSE, WHILE and REPEAT loops, but you will look in vain for a FOR loop construct. MySQL has no concept of anchored variables with %TYPE and %ROWTYPE.

The Migration Wizard

MySQL Workbench comes with a database migration wizard. This wizard helps you migrate from MS SQL Server, MS Access, PostgreSQL, and Sybase. Since MySQL is owned by Oracle, there is obviously no migration from Oracle to MySQL.

There is also a generic migration that can be used to migrate “most ODBC compliant databases.” However, this also cannot be used to migrate from Oracle to –MySQL—the wizard will connect but not see any database object to migrate.

Other Migration Tools

Microsoft will be happy to help you migrate from Oracle to SQL Server with the Microsoft SQL Server Migration Assistant. From there, you can use the MySQL migration wizard, which does support migrating from MS SQL Server.

Searching the Internet will also produce a number of third-party tools that purport to be able to migrate your database from Oracle to MySQL.

Manual Migration

You can also export your Oracle database to a file using Oracle SQL Developer and then make the necessary adjustments to create an equivalent MySQL database. Below is an example of an export from SQL Developer of a simple database containing many of the normal Oracle schema objects, and how they map to MySQL.

Tables

SQL Developer exports tables with double quotes, like this:

--------------------------------------------------------
--  DDL for Table EVENTS
--------------------------------------------------------
 
CREATE TABLE "EVENTS"
  ("EV_ID" NUMBER,
   "DESCRIPTION" VARCHAR2(240),
   "START_DATE" DATE,
   "END_DATE" DATE
  );
--------------------------------------------------------
--  DDL for Table MEMBERS
--------------------------------------------------------
 
CREATE TABLE "MEMBERS"
  ("MEM_ID" NUMBER,
   "FIRST_NAME" VARCHAR2(80),
   "LAST_NAME" VARCHAR2(80),
   "EMAIL" VARCHAR2(240)
  );
--------------------------------------------------------
--  DDL for Table REGISTRATIONS
--------------------------------------------------------
 
CREATE TABLE "REGISTRATIONS"
  ("EV_ID" NUMBER,
   "MEM_ID" NUMBER,
   "REG_DATE" DATE DEFAULT sysdate
  );

To create these tables in MySQL, you need to remove the double quotes and change the datatypes. SYSDATE is an Oracle-specific function that you need to replace with the equivalent CURRENT_TIMESTAMP. A MySQL script could look like this:

CREATE TABLE EVENTS
  (EV_ID INT,
   DESCRIPTION VARCHAR(240),
   START_DATE DATETIME,
   END_DATE DATETIME,
   ATTENDANCE_COST NUMERIC
  );
CREATE TABLE MEMBERS
  (MEM_ID INT,
   FIRST_NAME VARCHAR(80),
   LAST_NAME VARCHAR(80),
   EMAIL VARCHAR(240)
  );
  CREATE TABLE REGISTRATIONS
  (EV_ID INT,
   MEM_ID INT,
   REG_DATE DATETIME DEFAULT CURRENT_TIMESTAMP
  );

Sequences

You don’t need sequences in MySQL, so you don’t have to include these if you export your Oracle database with SQL Developer. If you are using triggers purely to create primary key values, you can leave those out as well.

Constraints

The SQL Developer export adds all constraints as individual ALTER TABLE statements, like this:

--------------------------------------------------------
--  Constraints for Table EVENTS
--------------------------------------------------------
  ALTER TABLE "EVENTS" MODIFY ("EV_ID" NOT NULL ENABLE);
  ALTER TABLE "EVENTS" ADD CONSTRAINT "EVENTS_PK" PRIMARY KEY ("EV_ID")
    USING INDEX  ENABLE;
  ALTER TABLE "EVENTS" MODIFY ("DESCRIPTION" NOT NULL ENABLE);
  ALTER TABLE "EVENTS" ADD CONSTRAINT "EVENTS_DESCR_UK"
    UNIQUE ("DESCRIPTION") USING INDEX  ENABLE;
 
--------------------------------------------------------
--  Constraints for Table MEMBERS
--------------------------------------------------------
  ALTER TABLE "MEMBERS" MODIFY ("MEM_ID" NOT NULL ENABLE);
  ALTER TABLE "MEMBERS" MODIFY ("FIRST_NAME" NOT NULL ENABLE);
  ALTER TABLE "MEMBERS" MODIFY ("LAST_NAME" NOT NULL ENABLE);
  ALTER TABLE "MEMBERS" MODIFY ("EMAIL" NOT NULL ENABLE);
  ALTER TABLE "MEMBERS" ADD CONSTRAINT "MEMBERS_PK"
    PRIMARY KEY ("MEM_ID") USING INDEX  ENABLE;
  ALTER TABLE "MEMBERS" ADD CONSTRAINT "MEMBERS_EMAIL_UK"
  UNIQUE ("EMAIL") USING INDEX  ENABLE;
 
--------------------------------------------------------
--  Constraints for Table REGISTRATIONS
--------------------------------------------------------
 
  ALTER TABLE "REGISTRATIONS" MODIFY ("EV_ID" NOT NULL ENABLE);
  ALTER TABLE "REGISTRATIONS" MODIFY ("MEM_ID" NOT NULL ENABLE);
  ALTER TABLE "REGISTRATIONS" ADD CONSTRAINT "REGISTRATIONS_PK"
    PRIMARY KEY ("EV_ID", "MEM_ID") USING INDEX  ENABLE;
 
--------------------------------------------------------
--  Ref Constraints for Table REGISTRATIONS
--------------------------------------------------------
  ALTER TABLE "REGISTRATIONS" ADD CONSTRAINT "REG_EV_FK"
    FOREIGN KEY ("EV_ID") REFERENCES "EVENTS" ("EV_ID") ENABLE;
  ALTER TABLE "REGISTRATIONS" ADD CONSTRAINT "REG_MEM_FK"
    FOREIGN KEY ("MEM_ID") REFERENCES "MEMBERS" ("MEM_ID") ENABLE;

In MySQL, you need to remove the double quotes. The syntax is also slightly different, without parentheses around column specifications. Also, MySQL requires you to provide a full column specification including datatype for every ALTER statement. You have to make a column primary key before you can add AUTO_INCREMENT. In MySQL, the above could become:

ALTER TABLE EVENTS MODIFY EV_ID INT NOT NULL;
ALTER TABLE EVENTS MODIFY DESCRIPTION VARCHAR(240) NOT NULL;
ALTER TABLE EVENTS ADD CONSTRAINT EVENTS_PK PRIMARY KEY (EV_ID);
ALTER TABLE EVENTS ADD CONSTRAINT EVENTS_DESCR_UK UNIQUE (DESCRIPTION);
ALTER TABLE EVENTS MODIFY EV_ID INT NOT NULL AUTO_INCREMENT;
 
ALTER TABLE MEMBERS MODIFY MEM_ID INT NOT NULL;
ALTER TABLE MEMBERS MODIFY FIRST_NAME VARCHAR(80) NOT NULL;
ALTER TABLE MEMBERS MODIFY LAST_NAME VARCHAR(80) NOT NULL ;
ALTER TABLE MEMBERS MODIFY EMAIL VARCHAR(240) NOT NULL;
ALTER TABLE MEMBERS ADD CONSTRAINT MEMBERS_PK PRIMARY KEY (MEM_ID);
ALTER TABLE MEMBERS ADD CONSTRAINT MEMBERS_EMAIL_UK UNIQUE (EMAIL);
ALTER TABLE MEMBERS MODIFY MEM_ID INT NOT NULL AUTO_INCREMENT;
 
ALTER TABLE REGISTRATIONS MODIFY EV_ID INT NOT NULL;
ALTER TABLE REGISTRATIONS MODIFY MEM_ID INT NOT NULL;
ALTER TABLE REGISTRATIONS ADD CONSTRAINT REGISTRATIONS_PK
    PRIMARY KEY (EV_ID, MEM_ID);
ALTER TABLE REGISTRATIONS ADD CONSTRAINT REG_EV_FK FOREIGN KEY (EV_ID)
    REFERENCES EVENTS (EV_ID);
ALTER TABLE REGISTRATIONS ADD CONSTRAINT REG_MEM_FK FOREIGN KEY (MEM_ID)
    REFERENCES MEMBERS (MEM_ID);

Views

The default view export from Oracle via SQL Developer looks like this:

CREATE OR REPLACE EDITIONABLE VIEW "REG_EMAIL"
    ("EV_ID", "DESCRIPTION", "EMAIL")
AS
  select ev.ev_id, ev.description, mem.email
  from   events ev, members mem, registrations reg
  where  reg.ev_id = ev.EV_ID
  and    reg.mem_id = mem.mem_id;

In MySQL, there is no CREATE OR REPLACE, nor the concept of editionable, so the MySQL equivalent becomes:

CREATE VIEW REG_EMAIL (EV_ID, DESCRIPTION, EMAIL)
AS
  select ev.ev_id, ev.description, mem.email
  from   events ev, members mem, registrations reg
  where  reg.ev_id = ev.EV_ID
  and    reg.mem_id = mem.mem_id;

Data

SQL Developer can export your data in the form of INSERT statements, like this:

REM INSERTING into EVENTS
SET DEFINE OFF;
insert into EVENTS (EV_ID,DESCRIPTION,START_DATE,END_DATE)
values ('101','Oracle OpenWorld 2017',
        to_date('2017-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),
        to_date('2017-10-05 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into EVENTS (EV_ID,DESCRIPTION,START_DATE,END_DATE)
values ('102','Oracle Code London',
        to_date('2017-04-20 00:00:00','YYYY-MM-DD HH24:MI:SS'),null);
REM INSERTING into MEMBERS
SET DEFINE OFF;
insert into MEMBERS (MEM_ID,FIRST_NAME,LAST_NAME,EMAIL)
values ('1','Sten','Vesterli','sten@vesterli.com');
insert into MEMBERS (MEM_ID,FIRST_NAME,LAST_NAME,EMAIL)
values ('2','Larry','Ellison','larry.ellison@oracle.com');

SQL Developer adds some SQL*Plus syntax (REM and SET) that you need to remove. MySQL doesn’t have a TO_DATE function, but accepts DATETIME values directly as quoted strings in ISO standard format. In MySQL you just need something like this:

insert into EVENTS (EV_ID,DESCRIPTION,START_DATE,END_DATE)
values ('101','Oracle OpenWorld 2017',
        '2017-10-01 00:00:00','2017-10-05 00:00:00');
insert into EVENTS (EV_ID,DESCRIPTION,START_DATE,END_DATE)
values ('102','Oracle Code London','2017-04-20 00:00:00',null);
insert into MEMBERS (MEM_ID,FIRST_NAME,LAST_NAME,EMAIL)
values ('1','Sten','Vesterli','sten@vesterli.com');
insert into MEMBERS (MEM_ID,FIRST_NAME,LAST_NAME,EMAIL)
values ('2','Larry','Ellison','larry.ellison@oracle.com');

Triggers

A SQL Developer export of a trigger looks like this:

--------------------------------------------------------
--  DDL for Trigger REGISTRATIONS_AFT_INS_REC
--------------------------------------------------------
 
CREATE OR REPLACE EDITIONABLE TRIGGER "REGISTRATIONS_AFT_INS_REC"
AFTER INSERT ON REGISTRATIONS
FOR EACH ROW
DECLARE
  l_email varchar2(240);
  l_ev_desc varchar2(240);
BEGIN
  select email
  into   l_email
  from   MEMBERS
  where  MEM_ID = :new.mem_id;
  select description
  into   l_ev_desc
  from   EVENTS
  where  EV_ID = :new.ev_id;
  UTL_MAIL.send(
    sender => 'registration@conference.org'
  , recipients => l_email
  , subject => 'Registration for ' || l_ev_desc
  , message => 'We received your registration for '
    || l_ev_desc
  );
END;
/
ALTER TRIGGER "REGISTRATIONS_AFT_INS_REC" ENABLE;

MySQL doesn’t have CREATE OR REPLACE or EDITIONABLE, and the declarations must be made individually with DECLARE. The syntax for referring to the triggering record is just NEW in MySQL, not :NEW as in Oracle. You also need to explicitly set the DELIMITER in MySQL to say when the trigger code is complete. Note that MySQL triggers, functions, and procedures can't have the name as part of the last END statement.

In this example, the Oracle trigger uses UTL_MAIL which is one of the many built-ins not available in MySQL. You need to find another way to implement this type of functionality in MySQL. Your MySQL equivalent might look like this:

DELIMITER //
CREATE TRIGGER REGISTRATIONS_AFT_INS_REC
AFTER INSERT ON REGISTRATIONS
FOR EACH ROW
BEGIN
DECLARE l_email varchar(240);
DECLARE  l_ev_desc varchar(240);
  select email
  into   l_email
  from   MEMBERS
  where  MEM_ID = new.mem_id;
  select description
  into   l_ev_desc
  from   EVENTS
  where  EV_ID = new.ev_id;
  -- send mail somehow
END;
//

Functions

An Oracle function exported from SQL Developer looks like this:

--------------------------------------------------------
--  DDL for Function EV_REGISTRATION_TOTAL
--------------------------------------------------------
 
CREATE OR REPLACE EDITIONABLE FUNCTION "EV_REGISTRATION_TOTAL" (
  P_EV_ID IN NUMBER
) RETURN NUMBER AS
  l_total number;
BEGIN
  select count(*) into l_total
  from   registrations
  where  ev_id = p_ev_id;
  RETURN l_total;
END EV_REGISTRATION_TOTAL;
/

In MySQL with extraneous keywords removed, declaration made explicit and datatypes changed, this procedure could look like this:

DELIMITER //
CREATE FUNCTION EV_REGISTRATION_TOTAL (
  P_EV_ID NUMERIC
) RETURNS NUMERIC
BEGIN
  DECLARE l_total numeric;
  select count(*) into l_total
  from   registrations
  where  ev_id = p_ev_id;
  RETURN l_total;
END;
//

Procedures

An example of a PL/SQL procedure from an Oracle database might look like this when exported from SQL Developer:

--------------------------------------------------------
--  DDL for Procedure REGISTER_FOR_EVENT
--------------------------------------------------------
set define off;
CREATE OR REPLACE EDITIONABLE PROCEDURE "REGISTER_FOR_EVENT" (
  P_EV_ID IN NUMBER
, P_FIRST_NAME IN VARCHAR2
, P_LAST_NAME IN VARCHAR2
, P_EMAIL IN VARCHAR2
) AS
  l_mem_id number;
BEGIN
  begin
    select mem_id
    into   l_mem_id
    from   members
    where  first_name = p_first_name
    and    last_name = p_last_name
    and    email = p_email;
  exception
    when no_data_found
    then
      insert into members(first_name, last_name, email)
      values (p_first_name, p_last_name, p_email)
      returning mem_id into l_mem_id;
  end;
  insert into registrations(ev_id, mem_id)
  values (p_ev_id, l_mem_id);
END REGISTER_FOR_EVENT;
/

In MySQL, the parameter syntax is different, with the IN or OUT keyword first. Exceptions are declared with DECLARE … HANDLER FOR – in the below example, everything in the BEGIN END block below the handler declaration is the handler for the specific condition NOT FOUND. Handlers can either CONTINUE as shown below, or EXIT to terminate the BEGIN END block it is part of. In MySQL, the Oracle procedure above could look like this:

DELIMITER //
CREATE PROCEDURE REGISTER_FOR_EVENT (
  IN P_EV_ID NUMERIC
, IN P_FIRST_NAME VARCHAR(80)
, IN P_LAST_NAME VARCHAR(80)
, IN P_EMAIL VARCHAR(240)
)
BEGIN
  DECLARE l_mem_id numeric;
  DECLARE CONTINUE HANDLER FOR not found
    BEGIN
      insert into members(first_name, last_name, email)
      values (p_first_name, p_last_name, p_email);
      set l_mem_id = last_insert_id();
    END;
  select mem_id
  into   l_mem_id
  from   members
  where  first_name = p_first_name
  and    last_name = p_last_name
  and    email = p_email;
  insert into registrations(ev_id, mem_id)
  values (p_ev_id, l_mem_id);
END;
//

Conclusion

There is no easy automatic migration from Oracle to MySQL.

MySQL has equivalents for the basic features of the Oracle database, although the syntax is slightly different for many things. The syntax of triggers, functions, and procedures is slightly different but easy to adapt. The syntax for exception handlers is rather different and they are placed differently in the code, so it will take a little thought to adapt these for MySQL.

The big difference and the potential deal-breaker when considering moving is the amount of PL/SQL in your application. The lack of the many powerful built-in packages you find in Oracle and the simpler programming language without constructs like cursor FOR loops mean that if you have a lot of PL/SQL, you will face a big task moving to MySQL.