Considerations for transitioning from Oracle to MySQL

Question

We build and maintain a fairly large scale enterprise web application using Oracle for our database needs, and are now transitioning to MySQL. I know there are several syntactical differences between the two. But besides those, what other thing should I be keeping in mind?

For instance, I know MySQL doesn't support stored procedures, which is why we are converting our procedures to Java code.

I am asking for potential roadblocks we may face along the way, and what solutions exist for those?

Please refrain from suggesting to not use MySQL, since we are fixed on this.

Answer 1

Yes,

Most importantly,

1. SET your SQL_MODE

   • Similar to Oracle's SQL_MODE
   • By Default MySQL only restricts you from changing the engine of an existing table.
   • This Allows you to do just about anything else. Like inserting NULLS into a NOT NULL column.
     • As it will just auto-convert to an empty string & amplifies the processing time.

2. SET your TX_ISOLATION Level

   This statement specifies transaction characteristics. It takes a list of one or more characteristic values separated by commas. These characteristics set the transaction isolation level or access mode. The isolation level is used for operations on InnoDB tables. The access mode may be specified as to whether transactions operate in read/write or read-only mode.

3. SET YOUR COLLATION

   • set your defaults for Character Set's if you don't the conversion may corrupt your data or be a nightmare to flip over from Latin to the UTF8.

   Example:

   SHOW COLLATION;

   SHOW CHARACTER SET;

   SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

4. Utilize the Performance Reports

   • While developing your setup, this is vital for scalability.
     • If you are using AWS
     • Please be advised, I have ran into issues with settings using RDS Instances and recommend you manage your own EC2 instance with MySQL installed & managed yourself.
     • The issues I have seen
     • Unable to use the Performance Schema
     • Unable to set certain global settings (not sure if this still is an issue but was mine 2 years ago.)

5. Lastly, to Thorsten Kettner's comment

   The concat operator || is absent. MySQL has a function CONCAT for this. Not a big deal, but a lot to re-write still.

This is true but can be controlled by the SQL_MODE PIPES_AS_CONCAT As an example here are one of my settings I personally have used in the past. This is just an example to get you-your team some ideas on what to look for.

SET @@SESSION.TRANSACTION_ALLOW_BATCHING=1;
SET @@SESSION.INNODB_STRICT_MODE = 1;
SET @@SESSION.SQL_WARNINGS = 1;
SET @@SESSION.OPTIMIZER_SEARCH_DEPTH = 12;
SET @@SESSION.SQL_MODE = "TRADITIONAL,ANSI";
-- sets the following: REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SET @@SESSION.TX_ISOLATION = "READ-COMMITTED";
SET @@SESSION.BULK_INSERT_BUFFER_SIZE = 1024*1024*4096;
SET @@SESSION.JOIN_BUFFER_SIZE = 1024*1024;
SET @@SESSION.INNODB_LOCK_WAIT_TIMEOUT = 84600;
SET @@SESSION.INTERACTIVE_TIMEOUT = 84600;
SET @@SESSION.LOCK_WAIT_TIMEOUT = 84600;
SET @@SESSION.WAIT_TIMEOUT = 84600;

Let me know if you have any questions about what I have posted.

Answer 2

To complement other answers, you'll need to relearn query optimization.

MySQL's SQL optimizer is much simpler. That means:

• MySQL's optimizer hinting is more limited than Oracle's.
• MySQL has fewer algorithms and options.
• MySQL optimizer is less comprehensive when producing an execution plan.
• EXPLAIN PLAN gives you quite limited information, compared to Oracle's. E.g., is it sorting first and then filtering, or is it the other way around? -- not clear.

The time will come (for sure) when you'll find slow queries and you'll need to relearn query optimization, since it's quite different.

Answer 3

While "I know MySQL doesn't support stored procedures" is a little misworded, you are right: MySQL doesn't have a programming language such as Oracle's PL/SQL embedded in their DBMS, which limits stored procedures and triggers drastically. If you used many triggers, maybe even compound triggers, then you may have a hard time trying to replace them somehow.

Adding to this, here are some things that come to mind:

• The empty string and a null string are the same thing in Oracle. In MySQL these are two different things. It can be tedious to find all the occurrences where you must consider this.
• MySQL doesn't have windows functions such as ROW_NUMBER. Rewriting queries can be hard work and is prone to errors.
• MySQL has no sequences, so you may have to change things (e.g. an app getting a sequence value first in order to insert parent and child records).
• MySQL has no function Indexes. So queries using these that were very fast may get slow in MySQL.
• If you come from Oracle 12c: MySQL's LIMIT clause has no tie clause, which can make writing queries much more akward.

Then some functions may be a tad different or even missing and must be rewritten

• The concat operator || is absent. MySQL has a function CONCAT for this. Not a big deal, but a lot to rewrite still.
• MySQL has no ORDER BY NULLS FIRST / LAST clause.
• ROWNUM can be emulated by LIMIT somehow, but the former applies in WHERE, the latter much later. So be careful when rewriting.
• TRANSLATE, DECODE, ...

One more thing: When doing update table 1 ...; update table2 ...; rollback; in MySQL, you would by default only rollback the second update! You must set the system autocommit variable to zero to avoid this and switch to transactional behavior as in Oracle.