Replication from Oracle to MariaDB the simple way - Part 3

In this third installment in this series, I'll explain why the smart solution I described in the previous post actually wasn't that good, and then I go on to explain how to fix it, and why that fix wasn't such a smart thing after all. So, this was the design we ended with last time:

We have Oracle replicating to a Materialized View, this to ensure that we can run triggers when the is a commit, and then triggers on this Materialized View updates MariaDB by sending a UDP message to a server that in turn is connected to MariaDB.

The issue with the above thingy was that a Materialized View by default is refreshed in it's entirety when there is a refresh, so if the table has 10.000 rows and 1 is inserted, then there will be 20.001 messages sent to MariaDB (10.000 rows deleted, 10.001 inserted). Not fun. And it seems that Materialized Views in Oracle aren't so smart, but I was sure they were this dumbed down, if they were, noone would be using them. So I rush for the Oracle documentation, yiihaa!

The default way of updating a Materialized View is not that fast, but there is a supposedly fast, alternative, method, appropriately named FAST (that the default method isn't called something like AWFULLY CRAZY SLOW is beyond me). So the materialized view using FAST REFRESH for the orders table should really be created like this:
CREATE MATERIALIZED VIEW orders_mv
  REFRESH FAST ON COMMIT
  AS SELECT *
  FROM orders;
But this gives an error from Oracle:
ORA-23413: table "SYSTEM"."ORDERS" does not have a materialized view log
Well the, let's create a MATERIALIZED VIEW LOG for table ORDERS then, that's no big deal:
CREATE MATERIALIZED VIEW LOG ON t1_mv;
But again I get an error, and this time indicating that old Larry has run out of gas in his MIG-21 and need my money to fill it up again, so he can fly off to his yacht:
ORA-00439: feature not enabled: Advanced replication

Grrnn (this is a sound I make when I get a bit upset)! Yes, if you want Materialized Views to work properly, they way the were designed, you need to part with some $$$, and as the cheap person I am, I run Oracle Express instead of SE or EE editions, as I rather spend my hard earned money on expensive beer than on Larrys stupid MIG-21. So, as they say, "Close, but no cigar".

But I'm not one to give up easily, as you probably know. And fact is, I don't need the whole Materialized View thing, all I want is a TRIGGER to execute on COMMIT. Hmm this requires a huge box of prescription drugs to fix, and I am already on the case. Sorry Larry, but you'll have to park your MIG-21 and have someone else buy you some gas.

More details on how I tricked Larry in the next part of this series on replication from Oracle to MariaDB.

/Karlsson

Replication from Oracle to MariaDB the simple way - Part 2

The theme for this series of posts is, and indicated in the previous post, "Try and try, again", and there will be more of this now when I start to make this work by playing with Oracle, with PL/SQL and with the restrictions of Oracle Express (which is the version I have available).

So, what we have right now is a way of "sending" SQL statements from Oracle to MariaDB, the question is when and how to send them from Oracle. The idea for this was then to use triggers on the Oracle tables to send the data to MariaDB, like this, assuming we are trying to replicate the orders table from Oracle to MariaDB:

In Oracle, and assuming that the extproc I have that created to send UDP messages is called oraudp, then I would do something like this:
CREATE OR REPLACE TRIGGER orders_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
   CALL oraudp('INSERT INTO orders VALUES '||
     :new.order_id||', '||:new.customer_id||', '||
     :new.amount||')');
END;
/

CREATE OR REPLACE TRIGGER orders_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
   CALL oraudp('UPDATE orders SET order_id = '||:new.order_id||
     ', customer_id = '||:new.customer_id||', amount = '||:new.amount||
     ' WHERE order_id = '||:old.order_id);
END;
/

CREATE OR REPLACE TRIGGER orders_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    CALL oraudp('DELETE FROM orders WHERE order_id = '||:old.order_id);
END;
/
 As I noted in my previous post though, DML TRIGGERs execute when the DML statement they are defined for executes, not when that DML is committed, which means that if I replicate to MariaDB in a trigger and then roll back, the data will still be replicated to MariaDB. So the above design wasn't such a good one after all. But the theme is, as I said, try and try again, so off to think and some googling.

Google helped me find a workaround, which is by using Oracle MATERIALIZED VIEWs. And I know what you think, you think "What has dear old Karlsson been smoking this time, I bet it's not legal", how is all this related to MATERIALIZED VIEWs? Not at all, actually, but this is a bit of a kludge, it does actually work, and this is the nature of a kludge (I've done my fair share of these over the years). There are two attributes of Oracle MATERIALIZED VIEWs that makes these work for us:

• A MATERIALIZED VIEW can be updated ON COMMIT, which is one of the two ways a MATERIALIZED VIEW is updated (the other is ON DEMAND). So when there is a COMMIT that affects the tables underlying the MATERIALIZED VIEW, we have something happening.
• And the above would be useless if you couldn't attach a TRIGGER to a MATERIALIZED VIEW, but you can!

So, to make this work, I can define a MATERIALIZED VIEW, one that I don't really use for any other purpose, on the table I want to replicate. So then the design gets something like this, again using the orders table as an example:

In practice, if we continue to use theorders table, this is what I would do in Oracle:
CREATE MATERIALIZED VIEW orders_mvREFRESH ON COMMIT
AS SELECT order_id, customer_id, amount
FROM orders;

CREATE OR REPLACE TRIGGER orders_mv_insert
AFTER INSERT ON orders_mv
FOR EACH ROW
BEGIN
   CALL oraudp('INSERT INTO orders VALUES '||
     :new.order_id||', '||:new.customer_id||', '||
     :new.amount||')');
END;
/

CREATE OR REPLACE TRIGGER orders_mv_update
AFTER UPDATE ON orders_mv
FOR EACH ROW
BEGIN
   CALL oraudp('UPDATE orders SET order_id = '||:new.order_id||
     ', customer_id = '||:new.customer_id||', amount = '||:new.amount||
     ' WHERE order_id = '||:old.order_id);
END;
/

CREATE OR REPLACE TRIGGER orders_mv_delete
AFTER DELETE ON orders_mv
FOR EACH ROW
BEGIN
    CALL oraudp('DELETE FROM orders WHERE order_id = '||:old.order_id);
END;
/ 

Now we have a solution that actually works, and it also works with Oracle transactions, nothing will be sent to MariaDB if a transaction is rolled back. There is a slight issue with this though, you might think it's no big deal, but fact is that performance sucks when we start adding data to the orders table, and the reason for this is that the default way for Oracle to refresh a materialized view is to delete all rows and then add the new ones. In short, Oracle doesn't keep track of the changes to the base table, except writing them to the base table just as usual, so when the MATERIALIZED VIEW is to be refreshed, all rows in the materialized view will first be deleted, and then all the rows in the base table inserted. So although this works, it is painfully slow.

So it's back to the drawing board, but we are getting closer, now it works at least, we just have a small performance problem to fix. I'll show you how I did that in the next post in this series, so see you all soon. And don't you dare skip a class...

/Karlsson

Replication from Oracle to MariaDB the simple way - Part 1

Yes, there is a simple way to do this. Although it might not be so simple unless you know how to do it, so let me show you how this can be done. It's actually pretty cool. But I'll do this over a number of blog posts, and this is just an introductory blog, covering some of the core concepts and components.

But getting this to work wasn't easy, I had to try several things before I got it right, and it's not really obvious how you make it work at first, so this is a story along the lines of "If at first you don't succeed mr Kidd" "Try and try again, mr Wint" from my favorite villains in the Bond movie "Diamonds are forever":

So, I had an idea of how to achieve replication from Oracle to MySQL and I had an idea on how to implement it, and it was rather simple, so why not try it.

So, part 1 then. Oracle has the ability to let you add a UDF (User Defined Procedure) just like MariaDB (and MySQL), but Oracle calls then extproc. So my first idea was an extproc that would send a message over UDP, any generic message. In this specific case though, I was to have a server that received that UDP message and then sent it to MariaDB as a query. So this server was connected to MariaDB and a message came in though UDP, and this message would of course be some DML statement. Simple and efficient, right? Of course UDP is not a reliable protocol, but for this usage it should be OK, right?

All that was needed after this was a set of triggers on the Oracle tables that would send a message to the server which would then send these to MySQL, and this message would be based on the changed data, as provided by the trigger, like a corresponding INSERT, UPDATE or DELETE statement. This should work. OK, let's get to work.

My first attempt at the server meant that a lot of UDP messages were lost. The reason for this was that there were just to many messages coming in bursts (this is how extprocs works in Oracle, nothing wrong with Oracle here, it's just the nature of the beast). The solution was to have the server be multithreaded, with one tight thread receiving messages on the UDP port, and then having a queue and another thread processing the queue and send the messages to MariaDB?

Well, this didn't work out either. The reason was that the means I did synchronization between the threads was using Mutexes, and this also slowed things down. It worked OK for a while, but some burst could easily mean that UDP messages were lost, and really not much was necessary in terms of load for this to happen.

So, what do we do now? I had two choices, either I skip UDP and go for a connected TCP solution instead. The issue with this was that partly the extra overhead this would cause on the Oracle end of things, and partly that this would increase the complexity of my extproc and server, and above all the API to all this. The other option was to go for a lock-less queue. Some googling and some testing allowed me to figure out how to do this with gcc and I could move on and test this. Fact is, it worked fine. And as UDP messages can't be routed, using UDP messages in and of themselves made things a bit more secure.

But then there was the issue with the TRIGGERs calling the extproc. A trigger is called on a DML operation, but that operation might later be rolled back, and in that case I would break the consistency of data. And for some reason, for all the enhancement to standard SQL that Oracle provides, (including DDL triggers), there are no ON COMMIT triggers. But there just had to be a way around that one, right?

So Try and try again, mr Wint. Yes, I will solve all this, but the story is to be continued in another blog post. Also, I will eventually provide with the code for all this. GPL code that you can use out of the box to replicate from Oracle to MariaDB. But not just yet.

So don't touch that dial!

/Karlsson