Migrate Applications from MySQL Databases to Autonomous AI Database

You can migrate SQL statements from MySQL to Oracle SQL and run the statements on Autonomous AI Database.

Translate MySQL Statements to Oracle SQL

You can translate SQL statements written in MySQL to Oracle SQL and run the translated statements on Autonomous AI Database.

Use DBMS_CLOUD_MIGRATION.MIGRATE_SQL to translate a MySQL statement to Oracle SQL. There are procedure and function variants of DBMS_CLOUD_MIGRATION.MIGRATE_SQL.

Migrate MySQL Statement to Oracle SQL with MIGRATE_SQL Procedure

The following example accepts the SQL statement written in MySQL as input, translates the statement to Oracle SQL, assigns the translated SQL statement to output_sql_result, and prints the result:

SET SERVEROUTPUT ON
   declare output_sql_result CLOB;
BEGIN
  DBMS_CLOUD_MIGRATION.MIGRATE_SQL(
    original_sql => 'CREATE TABLE movie (movie_id INT, title VARCHAR(255));',
    output_sql   => output_sql_result,
    source_db    => 'MYSQL');
    DBMS_OUTPUT.PUT_LINE (output_sql_result);
END;
/

OUTPUT
--------------------------------------------------------------
CREATE TABLE movie (movie_id NUMBER(10), title VARCHAR2(255));

The original_sql parameter specifies the MySQL statement.

The output_sql parameter stores the translated SQL.

The source_db parameter specifies MySQL as the database name.

See MIGRATE_SQL Procedure and Function for more information.

Migrate MySQL Statement to Oracle SQL with MIGRATE_SQL Function

The following example shows the DBMS_CLOUD_MIGRATION.MIGRATE_SQL function within a SELECT statement. The function input is a MySQL statement and the function returns the translated statement in Oracle SQL:

SELECT DBMS_CLOUD_MIGRATION.MIGRATE_SQL(
     'CREATE TABLE movie (film_id INT, title VARCHAR(255));','MYSQL') AS output FROM DUAL;

OUTPUT
------------------------------------------------------------------------------
create table cars (brand VARCHAR2(255), model VARCHAR2(255);

See MIGRATE_SQL Procedure and Function for more information.

Notes for running DBMS_CLOUD_MIGRATION.MIGRATE_SQL:

You may encounter an error during the translation if the input SQL statement is not supported in Oracle SQL. See Limitations for Migration and Translation of MySQL Statements to Oracle SQL for more information.
The DBMS_CLOUD_MIGRATION.MIGRATE_SQL subprograms only accept one SQL statement as input. So, only a single SQL statement can be translated per call.

Run MySQL Statements in Autonomous AI Database

You can interactively translate and run MySQL statements in your Autonomous AI Database.

Use the ENABLE_TRANSLATION procedure to enable real-time translation of SQL statements written in MySQL. After you enable translation in a session, MySQL statements are automatically translated and run as Oracle SQL statements, and you can see the results.

For example, after you enable translation by running ENABLE_TRANSLATION you can interactively do the following in a session:

Create the tables. For example, create the tables MOVIE and INVENTORY.
Insert data into tables.
Query tables.
Perform JOIN operations on tables. For example, you can perform a left outer join on tables.

To enable translation with MySQL and run commands:

Connect to your Autonomous AI Database using a SQL client.

See Connect to Autonomous AI Database for more information.

Note: DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION is not supported in Database Actions and is not supported with the Oracle APEX Service.
Run DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION to enable real-time SQL translation in your session. Use the MYSQL parameter to translate from MySQL.
```
BEGIN
 DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION('MYSQL');
END;
/
```
Use the following query to verify the SQL translation language for your session:
```
SELECT SYS_CONTEXT('USERENV','SQL_TRANSLATION_PROFILE_NAME') FROM DUAL;
```
See ENABLE_TRANSLATION Procedure for more information.
Enter MySQL statements. For example:
```
CREATE TABLE movie (film_id int, title varchar(255));

Table MOVIE created.
```
This automatically translates and runs the MySQL CREATE TABLE statement.

You can verify using the DESC command. For example:
```
DESC movie;

Name        Null?     Type

-------     -----     -------------
FILM_ID               NUMBER(38)

TITLE                 VARCHAR2(255)
```
The MOVIE table is created and the datatypes of each of the columns are automatically converted to Oracle datatypes.

You may encounter an error during the translation if the input SQL statement is not supported. See Limitations for Migration and Translation of MySQL Statements to Oracle SQL for more information.

Insert data into the MOVIE table. For example:

INSERT INTO movie (film_id, title) VALUES (123, 'Tangled');

1 row inserted.

INSERT INTO movie (film_id, title) VALUES (234, 'Frozen');

1 row inserted.

Verify the data insertion by querying the MOVIE table. For example:

SELECT * FROM movie;

FILM_ID  TITLE
-------- ---------
123  Tangled
234  Frozen

Create an INVENTORY table:

CREATE TABLE inventory (film_id int, inventory_id int);

Table INVENTORY created.

You can verify this step with the DESC command. For example:

DESC inventory;

Name        Null?     Type

-------     -----     -------------
FILM_ID               NUMBER(38)

INVENTORY_ID          NUMBER(38)

The INVENTORY table is created and the datatypes of each of the columns are automatically converted to Oracle datatypes.

Insert data into the INVENTORY table. For example:

INSERT INTO inventory(film_id, inventory_id) VALUES (123, 223);

1 row inserted.

INSERT INTO inventory(film_id, inventory_id) VALUES (234, 334);

1 row inserted.

Verify the data insertion by querying INVENTORY. For example:

SELECT * FROM inventory;

FILM_ID  INVENTORY_ID
-------- -------------
123  223
234  334

Perform a left outer join on tables MOVIE and INVENTORY:
```
SELECT m.film_id, m.title, inventory_id
    FROM movie AS m LEFT JOIN inventory
    ON inventory.film_id = m.film_id;

  FILM_ID  TITLE       INVENTORY_ID

---------- ---------- ------------
       234 Frozen      334
       123 Tangled     223
```
This example performs a LEFT OUTER JOIN on the movie and inventory tables. The AS keyword for table aliases in the FROM clause is not supported in Oracle SQL. The query is first translated to Oracle SQL and then run in your session.
Use DBMS_CLOUD_MIGRATION.DISABLE_TRANSLATION procedure to disable real-time SQL language translation for your session.
```
BEGIN
 DBMS_CLOUD_MIGRATION.DISABLE_TRANSLATION;
END;
/
```
This returns an error if SQL language translation is not enabled for your session.

See DISABLE_TRANSLATION Procedure

You can query the V$MAPPED_SQL view to list the MySQL statements that are translated and mapped in memory to Oracle SQL statements.

For example:

SELECT v.*
    FROM v$mapped_sql v, dba_objects o
    WHERE v.sql_translation_profile_id = o.object_id
        AND o.object_name = 'MYSQL'
        AND o.object_type = 'TRANSLATION PROFILE';

See V$MAPPED_SQL for more information.

Migrate MySQL Files to Oracle SQL

You can migrate a file containing MySQL statements to a file containing Oracle SQL statements.

The DBMS_CLOUD_MIGRATION.MIGRATE_FILE procedure translates SQL statements in a MySQL file in Object Storage and generates a new file containing Oracle SQL.

As a prerequisite, upload one or more MySQL files with a .sql extension to a location on Object Storage. The following examples use the file mysqltest.sql that is uploaded to Object Storage. See Put data into object storage for more information.

To migrate MySQL files to Oracle SQL:

Connect to your Autonomous AI Database instance.

See Connect to Autonomous AI Database for more information.
Configure access to Cloud Object Storage using a resource principal or by creating a credential object.

This step provides access to the Cloud Object Storage where you put the files you are migrating:
- Use a resource principal to access Cloud Object Storage:
  
  Define policies and roles to access Oracle Cloud Infrastructure Object Storage with a resource principal. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources andUse Resource Principal with DBMS_CLOUD for more information.
  
  If your files reside in another vendor's Cloud Object Storage, define the appropriate policies and roles to access the resource. See Configure Policies and Roles to Access Resources for more information.
- Create a credential to access Cloud Object Storage:
  
  You can create a credential to access Cloud Object Storage.
  
  For example:
```
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'OBJ_STORE_CRED',
username        => 'user1@example.com',
password        => 'password'
);
END;
/
```
  See CREATE_CREDENTIAL Procedure for more information.
Optionally, you can list the files in Object Storage. For example:
```
VAR function_list CLOB;
SELECT object_name FROM DBMS_CLOUD.LIST_OBJECTS
  (credential_name => 'OCI$RESOURCE_PRINCIPAL', location_uri => 'https://objectstorage.*region*.oraclecloud.com/n/*namespace*/b/*bucket*/o/files');

OBJECT_NAME

---------------------
mysqltest.sql
```
Alternatively, if you create a credential instead of the resource principal, OCI$RESOURCE_PRINCIPAL, specify the credential name in the credential_name parameter.

See LIST_OBJECTS Function for more information.
Run DBMS_CLOUD_MIGRATION.MIGRATE_FILE to migrate the MySQL file to Oracle SQL:
```
BEGIN
DBMS_CLOUD_MIGRATION.MIGRATE_FILE (
   credential_name => 'OCI$RESOURCE_PRINCIPAL',
   location_uri    => 'https://objectstorage.*region*.oraclecloud.com/n/*namespace*/b/*bucket*/o/files/mysqltest.sql',
   source_db       => 'MYSQL'
  );
END;
/
```
The credential_name parameter specifies the credential to access the Cloud Object Storage URI. The user that runs DBMS_CLOUD_MIGRATION.MIGRATE_FILE must have EXECUTE privilege for the credential object used to access the Object Storage URI. That is, the credential you specify with the credential_name parameter. If you use a credential instead of a resource principal, specify the credential name in the credential_name parameter.

The location_uri parameter specifies the source file URI. The format of the URI depends on the Cloud Object Storage service you are using. See DBMS_CLOUD URI Formats for more information.

The source_db parameter specifies MySQL as the database language. Use the value MYSQL to translate MySQL files to Oracle SQL.

Running this command translates the MySQL file mysqltest.sql to Oracle SQL and generates a new file with the name original_filename_oracle.sql.

For this example, running DBMS_CLOUD_MIGRATION.MIGRATE_FILE with the input file mysqltest.sql generates mysqltest_oracle.sql. After the translation step the procedure uploads mysqltest_oracle.sql to Object Storage.

Optionally, use the target_uri parameter to specify the location where the translated file is uploaded. The default value for this parameter is NULL, which means the translated file is uploaded to the same location as specified in the location_uri parameter.

See MIGRATE_FILE Procedure for more information.

Verify that the output file was generated.

SELECT object_name FROM DBMS_CLOUD.LIST_OBJECTS (
    credential_name => 'OCI$RESOURCE_PRINCIPAL', location_uri => 'https://objectstorage.*region*.oraclecloud.com/n/*namespace*/b/*bucket*/o/files');

OBJECT_NAME

---------------------
mysqltest.sql
mysqltest_oracle.sql

Alternatively, if you create a credential instead of the resource principal, OCI$RESOURCE_PRINCIPAL, specify the credential name in the credential_name parameter.

See LIST_OBJECTS Function for more information.

Run the following query to view the content of the mysqltest_oracle.sql file:

SELECT UTL_RAW.CAST_TO_VARCHAR2 (DBMS_CLOUD.GET_OBJECT(
  credential_name => 'OCI$RESOURCE_PRINCIPAL',
  object_uri => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/files'))
FROM dual;

UTL_RAW.CAST_TO_VARCHAR2(DBMS_CLOUD.GET_OBJECT(CREDENTIAL_NAME=>'CRED1',OBJECT_U
--------------------------------------------------------------------------------
DROP TABLE movie;
DROP TABLE inventory;
CREATE TABLE movie (film_id NUMBER(10), title VARCHAR2(255));
INSERT INTO movie (film_id, title) VALUES (123, 'Tangled');
INSERT INTO movie (film_id, title) VALUES (234, 'Frozen');
CREATE TABLE movie (film_id NUMBER(10), inventory_id NUMBER(10));
INSERT INTO movie (film_id, inventory_id) VALUES (123, 223);
INSERT INTO inventory (film_id, inventory_id) VALUES (234, 334);
SELECT * FROM movie;

Alternatively, if you create a credential instead of the resource principal, OCI$RESOURCE_PRINCIPAL, specify the credential name in the credential_name parameter.

See GET_OBJECT Procedure and Function for more information.

Limitations for Migration and Translation of MySQL Statements to Oracle SQL

This section summarizes the limitations for migrating SQL statements from MySQL to Oracle SQL.

Note the following restrictions when migrating from MySQL database flavor to Oracle SQL:

User-Defined Global Variables: MySQL user-defined global variables declared using @var_name format are not supported when translating to Oracle SQL.
Quoted Identifiers: MySQL identifiers that are enclosed with backticks (`), such as var_name, are not supported when translating to Oracle SQL. Only identifiers that are without quotes or are enclosed in double quotation marks are supported.
Delimiters:
- For functions and procedures, only $$ and // delimiters are supported.
- For SQL statements only ; delimiter is supported.

The following list of MySQL functions are not supported:

ADDTIME(datetime, time)
AES_DECRYPT(crypt_str, key_str)
AES_ENCRYPT(str, key_str)
BIN(num)
CONV(num, from_base, to_base)
CONVERT_TZ(datetime, from, to)
CRC32(exp)
DATE_ADD(date, interval)