Oracle to MySQL migration

In the example we present in this blog, we use Oracle as the more expensive application and move to MySQL as a cost-effective solution.

Below we present the key features of both database servers as well as the migration concerns that might arise if you decide to migrate from Oracle to MySQL. Finally, we present the migration process that we followed during our application.

Oracle and MySQL key features

MySQL and Oracle databases have a different backgrounds but share many common features which are presented in the list below:

• tables
• views
• primary keys
• foreign keys
• unique keys
• procedures
• functions
• triggers

The Oracle database is mostly used for very large-scale deployments because it handles a large amount of data quickly. Therefore, it has more features and an advanced programming language. To compare, one thing that MySQL lacks is a feature similar to PL/SQL packages and package variables. Instead, the session-scoped user-defined variables (@my_var) can be used to store values.

Migration concerns

Before you start a MySQL migration you should consider the following:

• The security model in MySQL is much simpler than in Oracle, so if you have detailed or complicated security in Oracle application, it'll be hard to migrate to MySQL.
• As mentioned previously, the programming language in MySQL is simpler than PL/SQL and MySQL doesn't have anything like the capability of the built-in packages. Therefore, if the usage of built-in packages is frequent in Oracle instance, it'll become very hard to move to MySQL.

Migration process

There are different approaches to the migration process depending on various factors like the amount of data that the database currently holds, or the traffic of the applications which are connected to the database. For example, if an application downtime isn't important, maybe an approach that completes the migration process in phases is the best option (“Trickle” Migration). Otherwise, if the amount of data requires only a short period of downtime, a full transfer (“Big Bang” Migration) could be the optimal choice.

Below we present the steps of our migration:

Export Oracle db schema.

You can use any cross-platform database IDE like DataGrip from JetBrains to export your db schema to a script.

Translate Oracle db schema script to MySQL.

1. The Oracle and MySQL programming languages have almost the same dialect, but there are some differences. A quick way to translate Oracle scripts to MySQL is by tools that do this automatically. We used a tool provided online.
2. For some Oracle features, MySQL has equivalent functionality which is implemented differently. For example, MySQL doesn't have sequences, instead allows columns to be defined as AUTO_INCREMENT. If there are sequences used for primary keys, you can simply delete them from the script and add to the tables primary key which they're referring to, the AUTO_INCREMENT keyword as shown below:

create table foo

(

    FOO_ID INT auto_increment primary key,

    UPDATE_DATE DATETIME not null,

);

1. 3. Sequences though can be used also for other purposes besides primary key's increment. Emulation of a sequence in MySQL for any reason can be done with procedures and functions as shown below:

DROP PROCEDURE IF EXISTS CreateSequence;

 

CREATE PROCEDURE CreateSequence (name VARCHAR(30), start INT, inc INT)

BEGIN

  -- Create a table to store sequences

  CREATE TABLE IF NOT EXISTS _sequences

  (

      name VARCHAR(70) NOT NULL UNIQUE,

      next INT NOT NULL,

      inc INT NOT NULL

      );

  -- Add the new sequence

  INSERT INTO _sequences VALUES (name, start, inc);

END

 

DROP FUNCTION IF EXISTS NextVal;

 

CREATE FUNCTION NextVal (vname VARCHAR(30)) RETURNS INT

BEGIN

-- Retrieve and update in single statement

UPDATE _sequences

    SET next = (@next := next) + 1

WHERE name = vname;

RETURN @next;

END

 

-- Create a sequence

CALL CreateSequence('foo_seq', 1, 1);

 

--Get Next Vallue of foo_seq

SELECT NextVal(‘foo_seq');

 

Develop software to transfer data from Oracle database to MySQL.

One option to migrate data from one database to another is to dump the whole database into a script and then execute the script to the new database. This option comes with some issues. Firstly, it's time-consuming especially for a large amount of data. Also, the script will need translation to MySQL language because there are some differences between Oracle and MySQL programming languages.

That said, the most efficient way to migrate the data is by developing software that's connected to both databases, and with basic insert/select transactions, data can be retrieved from Oracle and stored in MySQL database. This way language translation is done automatically and the whole operation is faster, so the downtime is limited. It also reduces potential errors.

Translate potential SQL queries in your application.

An extra step that can't be avoided is the translation of native SQL queries in your application. Unfortunately, this is a manual process and can be time-consuming depending on the number of queries. Below we present a list with the most common modifications in native SQL queries:

1. 1. Remove all redundant SQL sequences that are used for primary keys as MySQL uses AUTO_INCREMENT for new record insertion.
2. 2. DATE_FORMAT and TO_CHAR Format Specifiers.

If to_char() function is used it needs to be modified to the corresponding date_format() MySQL function.

1. 3. Convert Oracle's To_Date() function to MySQL STR_TO_DATE().
2.  
3. 4. Emulate Oracle's NVL() function with MySQL COALESCE() function like the example below:
4. nvl(nullable_parameter,'foo') -> coalesce(nullable_parameter,’foo’ ​)​​
   •  
5. 5. Convert Oracle's keyword || to MySQL function CONCAT() like the example below:
6. '%' || value || '%' -> concat(‘%',value,'%')
7.  
8. 6. Convert Oracle's DECODE() function to MySQL IF() like the example below:
9. SELECT DECODE(value, 1,'X',2,'Y') as foo -> SELECT IF(value = 1,'X','Y') as foo
10.  
11. 7. Convert Oracle's (+) keyword in 'where' clause with MySQL LEFT OUTER JOIN in 'from' clause like the example below:
12. • • • • Oracle:

13. select *

    from projects p, users u

    where p.user_id = u.user_id (+)

     

14. • • • • MySQL:

15. select *

     from projects p

     left outer join users u on p.user_id = u.user_id

16. 8. Make sure that when inserting a new row the auto-increment primary key has no value.
17.  

Test, test, test.

• Finally, it's important to dedicate a lot of time for testing. If possible, we recommend creating the whole migration process on a test environment with live data so the tests will be accurate.

•  

• Conclusion

• In this article, we discussed the migrating process specifically for Oracle and MySQL databases and gave some key steps that should be done for a successful migration. Of course, in such a complex process no one can predict all the steps and modifications that should be done. It's highly possible that bugs can be left behind on the first try. That's why testing is a crucial part of the migration.

•  

• Take a look at our Web Design and Development Services to see where we can help.

•  

•