How is yugabyte compatible with oracle and mysql

General
1

Hi
Now yugabyte is only compatible with pgsql, cql and redis.
Now I want to use the syntax of oracle and mysql to operate yugabyte just like pgsql, csql and redis
What should I do?

2

Hi,

Being compatible is not only the syntax, but also the application behavior. For example, with YSQL we have the same protocol and syntax as PostgreSQL, but also same catalog views, same error messages, same execution plan output, and, most of all, the same isolation levels and lock behavior’s. To that’s not so easy as changing the dialect in an ORM for example.

When coming from MySQL or Oracle, there are tools bundled in YugabyteDB Voyager to migrate the schema and move data, but the application still has to be adapted. It can be easy when using CRUD operation via an ORM, or more complex when using lot of PL/SQL code for example.

There’s also the possibility of offloading only data and access it from, let’s say a bunch of Oracle XE with the Heterogenous Services Gateway via ODBC for PostgreSQL and you can access YugabyteDB tables from Oracle code. However, the performance will be bad and you may have to do some transformations anyway.

Moving to a Distributed SQL database is part of IT modernisation. If you have 20 years old code with your business logic in PL/SQL procedures, this is already hard to maintain. Making it working though layers of transformations or emulations will make it even worse. In practice, many users prefer to rewrite their code for PostgreSQL compatibility and they are out of this lock-in because it is open source, close to the SQL standard, with many more features, and there are many postgresql compatible databases, YugabyteDB being the distributed SQL one.

Maybe one day we will have a MySQL API, the architecture allows it, but currently users prefer to go to PostgreSQL for this modernizations and better improving it than adding a new one. For Oracle, it is proprietary and nobody can even use their protocol because there’s a copyrighted poem inside it! (see dac's blog: Oracle Poetry)

1 Like
3

Hi, FranckPachot.
Thank you very much for your reply.
You are really professional and really nice!
Now, I’m still a little confused.
After migrating historical data, most users want to continue to operate yugabyte according to the syntax and protocols of oracle or mysql without changing the application behavior.
Therefore, we have to find solutions to be compatible with the user’s writing data to yugabyte, according to the syntax and protocol of oracle or mysql.
Now there are two solutions as follows:

  1. The implementation of using YugabyteDB Voyager to migrate oracle or mysql data is embedded into the protocol layer of the YugabyteDB kernel to support users to operate yugabyte according to the same protocol and syntax as oracle or mysql ?
  2. Use SQLines to convert the syntax and protocol of Oracle or MySQL into PostgreSQL, and then write the data to yugabyte through PostgreSQL syntax and protocol ? This SQLines supports migrate oracle or mysql to PostgreSQL.

Can you help to make a feasibility analysis for the two solutions mentioned above?
Thank you very much.

4

Sorry, there’s no magic solution to move from one database to another without changing the code. Except, maybe, if you use a framework with multiple dialects (Hibernate, jOOQ, sqlalchemy…) but even there you may have some different behavior’s on race conditions.
The best we can do with YugabyteDB is being PostgreSQL compatible as it is the closest to the SQL standard.

1 Like
5

I got it now. Thank you very much!

1 Like