Category Archives: Uncategorized

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 theSoutheastern 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)

More info to come!

NEXTGRES Gateway SQL*Plus Teaser…

Do you love SQL*Plus but hate that you can’t use it with other, non-Oracle databases?  Do you wish you could more easily migrate some of your MySQL or Postgres applications to Oracle?  If so, stay tuned, because my next few blog entries are going to show you how to do just that.  But first, I want to throw a little teaser out there for the SQL*Plus crowd…

Let’s face it, SQL*Plus is an awesome tool.  And, it’s a tool everyone who’s had to manage databases from multiple RDBMS vendors wishes they could use against all of them.  Well, if you’re using Postgres, MySQL, SQL Server, or DB2 and are tired of using cheap, JDBC-driven SQL*Plus knock-offs, my next blog entry will show you how to interface the real SQL*Plus with each of those systems using nothing but Instant Client, a couple libraries, and a little ingenuity.

OpenWorld 2009 Recap

Having now had several days to recover from, and reflect on, my trip to beautiful San Francisco for Oracle OpenWorld 2009, I wanted to follow up with a blog post describing my experience.

First, before anything else, I went to In-N-Out Burger where I had my usual Double-Double with fries animal style; oh, how I missed thee.

Next, with the exception of Larry’s keynote on Wednesday, I skipped the rest of keynotes.  While I wanted to attend Michael Dell’s, I was hungry and decided on breakfast at Mel’s Drive-in instead.  Out of coincidence, I ended up sitting next to Mark Prichard, Senior Principal Product Manager for WebLogic at Oracle; we ended up discussing Tuxedo and some of the features Oracle has been adding to it since the BEA acquisition.  A bit later, I visited the Tuxedo booth to learn more and was quite happy to see Oracle putting more resources into developing such a great product as Tuxedo.

Day-By-Day
On Monday, I attended a couple sessions, including one on TimesTen caching for Oracle, as well as the OTN Night event.  At the event, I had a great discussion with several cool Oracle users and, with the exception of a few incorrect answers to questions for “Oracle Jeopardy”, it was a great night.

Tuesday was a busy day.  Surprisingly, during my session I saw former EnterpriseDB colleague, fellowPostgres developer, and new Google employee Greg Stark.  We discussed several different database-related topics during lunch and then attended the Hybrid Columnar Compression session given by Bill Hodak and Amit Ganesh.  Based on the simplified diagram displayed in the session, my guess is thatHybrid Columnar Compression is implemented using some of the in-block table/row directory functionality with the compression unit header containing the number of blocks in the unit as well as pointers to which block(s) within the compression unit contain which fields.  More to come on that topic in a future blog post 🙂

Tuesday night, I attended the Pythian Bloggers Meetup event and met fellow bloggers Alex Gorbachev,Markus EiseleRobyn SandsArjen VisserFuad ArshadRaimonds SimanovskisVít Špinka, Steve Lemme, and many others.  Unfortunately, I was unable to meet up with Justin KestelynDoug Burns, and Tim Bray.  After the meetup, a few of us went over to Chevy’s and talked shop for awhile.

Wednesday was similarly busy.  Fortunately, before I left, I was able to meet long-time online friend and colleage Jonathan Gennick.  Jonathan invited me to meet him at one of Mogens Nørgaaard’s Oracle Closed World sessions where I was also able to meet Graham Wood and several other well-known Oracle/OakTable guys.  It was at OCW that I was able to watch one of the best, non-marketing-oriented Cloud Computing presentations given by Jeremiah Wilton.  After the OCW session, I enqueued myself to see Larry’s keynote which I enjoyed.

All in all, I spent a great deal of time talking to some of the developers and technical guys at the Oracle database DEMOgrounds including Stephan HaisleyMark Williams, Advanced Security developers, Advanced Compression developers, Optimizer guys, some of the BerkeleyDB guys, and several members of the Data Access group.  While I primarily visited the Exadata exhibit hoping to meet up with Kevin Closson and Greg Rahn, I was able to have a few great discussions regarding the architecture of Exadata as well as watch that machine perform an amazing amount of data processing in a very short amount of time.

In short, OpenWorld was great!  If you didn’t attend, I would suggest that you buy access to Oracle OpenWorld On Demand, there’s a ton of great content in there and it’s definitely worth it.

Lastly, I’d like to thank Oracle for inviting me to speak and I hope to be back next year!