Jan 25 2010
NEXTGRES Gateway: MySQL Emulator for Oracle
So, a few people have asked me what NEXTGRES Gateway is. My short answer, the ultimate database compatibility server.
Sorry if this blog entry sounds very marketing-oriented, but I’ve been working on this personal project non-stop for the last 8 months and am really excited about it.
NEXTGRES Gateway in a nutshell:
- Designed to assist in database/application migration
- Written in C
- Supports TCP and UDP sockets
- Runs in multi-threaded or multi-process mode (depending on configuration)
- Runs on Windows, UNIX, Linux
- Supports MySQL, PostgreSQL, Oracle, and SQL Server/Sybase (TDS) server-side wire-level protocols
- Supports MySQL, PostgreSQL, Oracle, and SQL Server/Sybase (TDS) client-side wire-level protocols, as well as ODBC
- Supports pass-through SQL or syntax translation between MySQL, PostgreSQL, Oracle, and SQL Server
- Supports native procedural language execution via translation of PL/SQL and T-SQL to C (no C complier is needed on your system though, thanks to TCC)
- Supports data type conversion to/from MySQL, PostgreSQL, Oracle, and SQL Server/Sybase
- Supports local statement/result-set caching
In this entry, I’m going to focus on MySQL server emulation.
MySQL Server Emulation
Now that I’ve fully completed the MySQL server emulation component, something I discussed with the Oracle Data Access guys at OpenWorld, here’s a couple examples for you.
Say you have an application that runs on MySQL and you’d like to migrate it to Postgres, but don’t want to do any code changes. Well, if you’re using fairly standard ODBC/JDBC, you don’t have much to worry about. But what if it’s a PHP application using the mysql_* calls, or an application using the MySQL client libraries, or a third-party application you don’t have the code for? The answer is to use NEXTGRES Gateway.
NEXTGRES Gateway allows you migrate your data to another database transparently to the application. The general process for using NEXTGRES Gateway is as follows:
- Migrate Data (MySQL to Postgres/Oracle/SQL Server)
- Shutdown MySQL
- Start NEXTGRES Gateway in MySQL Emulation mode and point it to the Postgres/Oracle/SQL Server data
- Test
- You’re done!
Unlike other databases which claim to be compatible, NEXTGRES Gateway allows you to migrate an application to another database server with no application changes.
In the following example, I’m using the native MySQL client to connect to a PostgreSQL 8.3 database. It’s important to note that no changes have been made to the MySQL client, it’s just connecting to NEXTGRES Gateway which is emulating the MySQL server by performing SQL syntax and protocol translation to Postgres.
jharris@jharris-desktop$ mysql -A -u root -h 127.0.0.1 pgdb Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select count(*) from pg_tables; +-------+ | count | +-------+ | 51 | +-------+ 1 row in set (0.01 sec) mysql> select tablename from pg_tables limit 5; +-------------------------+ | tablename | +-------------------------+ | sql_features | | sql_implementation_info | | pg_statistic | | sql_languages | | sql_packages | +-------------------------+ 5 rows in set (0.01 sec)
That’s cool and all, but say we want to move our MySQL application to Oracle:
jharris@jharris-desktop$ mysql -A -u root -h 127.0.0.1 oradb Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select count(*) from all_tables; +----------+ | COUNT(*) | +----------+ | 108 | +----------+ 1 row in set (0.03 sec) mysql> select count(*) from user_tables; +----------+ | COUNT(*) | +----------+ | 9 | +----------+ 1 row in set (0.01 sec) mysql> select user from dual; +-----------+ | USER | +-----------+ | AUTOGRAPH | +-----------+ 1 row in set (0.01 sec) mysql> select table_name from all_tables limit 5; +-----------------------+ | TABLE_NAME | +-----------------------+ | DUAL | | SYSTEM_PRIVILEGE_MAP | | TABLE_PRIVILEGE_MAP | | STMT_AUDIT_OPTION_MAP | | AUDIT_ACTIONS | +-----------------------+ 5 rows in set (0.02 sec)
For those that didn’t catch it, NEXTGRES Gateway performed a simple SQL translation from MySQL to Oracle syntax on:
SELECT table_name FROM all_tables LIMIT 5;
to
SELECT table_name FROM all_tables WHERE ROWNUM < 6;
If you want to see more, I’ll be happy to demonstrate MySQL, Postgres, and Oracle emulation at the Southeastern Oracle Users Conference, February 24 & 25 in Charlotte, North Carolina. I’ll also be presenting my session, “Listening In: Passive Capture and Analysis of Oracle Network Traffic”. This session is designed to help you diagnose issues with and optimize applications for, the Oracle network protocol.
Added on 2010-01-30 per Baron’s question:
jharris@jharris-desktop$ mysql -A -u testuser1 -h 127.0.0.1 postgres Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.39 (NEXTGRES Gateway 4.2.0.1) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show tables; +------------------------------------------------------+ | Tables_in_postgres | +------------------------------------------------------+ | active_locks | | active_queries | | information_schema.administrable_role_authorizations | | information_schema.applicable_roles | | information_schema.attributes | | information_schema.check_constraint_routine_usage | | information_schema.check_constraints | | information_schema.column_domain_usage | | information_schema.column_privileges | | information_schema.column_udt_usage | | information_schema.columns | | information_schema.constraint_column_usage | | information_schema.constraint_table_usage | | information_schema.data_type_privileges | | information_schema.domain_constraints | | information_schema.domain_udt_usage | | information_schema.domains | | information_schema.element_types | | information_schema.enabled_roles | | information_schema.information_schema_catalog_name | | information_schema.key_column_usage | | information_schema.parameters | | information_schema.referential_constraints | | information_schema.role_column_grants | | information_schema.role_routine_grants | | information_schema.role_table_grants | | information_schema.role_usage_grants | | information_schema.routine_privileges | | information_schema.routines | | information_schema.schemata | | information_schema.sequences | | information_schema.sql_features | | information_schema.sql_implementation_info | | information_schema.sql_languages | | information_schema.sql_packages | | information_schema.sql_parts | | information_schema.sql_sizing | | information_schema.sql_sizing_profiles | | information_schema.table_constraints | | information_schema.table_privileges | | information_schema.tables | | information_schema.triggered_update_columns | | information_schema.triggers | | information_schema.usage_privileges | | information_schema.view_column_usage | | information_schema.view_routine_usage | | information_schema.view_table_usage | | information_schema.views | | jhhdemotbl | | jhhtest | | pg_aggregate | | pg_am | | pg_amop | | pg_amproc | | pg_attrdef | | pg_attribute | | pg_auth_members | | pg_authid | | pg_autovacuum | | pg_cast | | pg_class | | pg_constraint | | pg_conversion | | pg_cursors | | pg_database | | pg_depend | | pg_description | | pg_enum | | pg_freespacemap_pages | | pg_freespacemap_relations | | pg_group | | pg_index | | pg_indexes | | pg_inherits | | pg_language | | pg_largeobject | | pg_listener | | pg_locks | | pg_namespace | | pg_opclass | | pg_operator | | pg_opfamily | | pg_pltemplate | | pg_prepared_statements | | pg_prepared_xacts | | pg_proc | | pg_rewrite | | pg_roles | | pg_rules | | pg_settings | | pg_shadow | | pg_shdepend | | pg_shdescription | | pg_stat_activity | | pg_stat_all_indexes | | pg_stat_all_tables | | pg_stat_bgwriter | | pg_stat_database | | pg_stat_sys_indexes | | pg_stat_sys_tables | | pg_stat_user_indexes | | pg_stat_user_tables | | pg_statio_all_indexes | | pg_statio_all_sequences | | pg_statio_all_tables | | pg_statio_sys_indexes | | pg_statio_sys_sequences | | pg_statio_sys_tables | | pg_statio_user_indexes | | pg_statio_user_sequences | | pg_statio_user_tables | | pg_statistic | | pg_stats | | pg_tables | | pg_tablespace | | pg_timezone_abbrevs | | pg_timezone_names | | pg_trigger | | pg_ts_config | | pg_ts_config_map | | pg_ts_dict | | pg_ts_parser | | pg_ts_template | | pg_type | | pg_user | | pg_views | | plproxy.cluster_partitions | | plproxy.clusters | | testtbl | | utility.index_byte_sizes | | utility.table_byte_sizes | | utility.user_table_sizes | +------------------------------------------------------+ 132 rows in set (0.03 sec) mysql> show databases; +-----------+ | Database | +-----------+ | template1 | | template0 | | postgres | +-----------+ 3 rows in set (0.01 sec)
Way cool !!!!
Definitely interesting Jonah, would love together a chance to play around with that. Let me know if you want some external validation of this tool.
Hey Dude,
congratulations !! Fantastic Work !!
Keep up the spirit
Prajith
[…] is Jonah H. Harris with an introduction to the NEXTGRES Gateway, a MySQL Emulator for Oracle. Jonah writes: “So, a few people have asked me what NEXTGRES Gateway is. My short answer, the […]
Jonah,
You connected to a Postgres backend with a MySQL client, but then ran a Postgres command to look for tables. What happens if you run SHOW TABLES?
Baron,
Good question. Added to the entry is an example against Postgres.
From a configuration standpoint, I can restrict to showing all tables or only tables you have in your search_path (which means they wouldn’t have to be schema-qualified). In this example, it’s configured to return all tables. I also support the [(FROM | IN) db_name] [LIKE quoted_string] syntax, but not yet the additional WHERE clause.
Where databases are concerned, from a MySQL-compatibility standpoint, you could either create separate actual databases or simulate them by creating separate Postgres schema and using search_path. The second method is similar to what I use when simulating MySQL with an Oracle database. Each MySQL user has an Oracle account that uses synonyms to point to the actual schema containing the data (assuming that you’re connecting with a user other than the owner of those tables in Oracle).
-Jonah
Heh… just realized that my show tables doesn’t currently include views. Oops! Gotta fix that.
Fixed and updated.
[…] Read more here: ORACLE INTERNALS » NEXTGRES Gateway: MySQL Emulator for Oracle […]
This is super cool!! Nice work. I have a question based on a recent project. We received a request to create an environment for open source tools. We have nothing against LAMP, far from it, but it costs money to support more OSes and database platforms, so management wanted to try our existing stack first. We normally run Solaris, Oracle, and weblogic or glassfish. We tried to get several examples of open source tools running on Solaris/Oracle, but Oracle proved to be the part that wouldn’t work, since many of the packages used Oracle reserved words for column names etc. Does your project also have some way to detect and translate between black-box packages that use reserved words?
Tyler,
Yes, NEXTGRES contains a list of reserved words for each target database and permits alterations to be applied to object names during parse tree transformation.
If the word is reserved on the target system, you have the option of applying a prefix/suffix to it. When migrating the data itself, you rename the column/object to a non-reserved word using a prefix or suffix, and configure NEXTGRES to apply that same prefix/suffix transformation when dealing with that column/object.