I am Anders Karlsson, and I have been working in the RDBMS industry for many, possibly too many, years. In this blog, I write about my thoughts on RDBMS technology, happenings and industry, and also on any wild ideas around that I might think up after a few beers.
You can export data from MariaDB using mysqldump and a bunch of other tools, but if you need really flexible output format, this might not be what you need. Instead, give MyMDBDump a try. This tool will export data just like mysqldump, but the output format is a lot more flexible. I urge you to test it and check out the documentation, but some if the features are, in short:
Dynamic column support - Dynamic columns can be exported as binary, JSON or even as SQL, where I think the latter is a pretty unique feature.
Oracle export format - This exports data as INSERT statements, just like mysqldump, but in an Oracle friendly way.
MS SQL / SQL Server format - This exports data as SQL Server friendly INSERTs.
Binary data formats - Supporting plain binary, base64, hex and a number of more formats.
Transaction support.
JSON export support - Including embedding dynamic columns as JSON.
UTF8 support and UTF8 validity checking.
Generated ROWNUM column output
FLOAT and DOUBLE formatting
DATETIME, TIMESTAMP, DATE and TIME custom formating.
I have now released version 1.2 of MyOraDump, my Oracle data extraction tool. This version has one new feature, which is transaction support for MySQL format exports which does speed up loading data a lot! Trust me, it really does! Also I have fixed a bug that did cause a crash at the end of the run, I have no idea why this didn't show up before, but there you go and now it is fixed.
MyOraDump 1.2 can be downloaded sourceforge, and as usual there is also a pdf only download if you want to read up on the tool before using it.
Today I released version 1.1 of myoradump for download from sourceforge. If you don't know what myoradump is, this is a utility for exporting data from an Oracle database in some relevant text format so that it can be imported to some other database.
The main thing in version 1.1 is that I have added a whole bunch of new output formats, so make it even easier to get your data out of expensive Oracle and into something more effective. The new formats supported are:
MySQL - The format of this is a bunch of INSERT statements that you get when you use mysqldump for example and is useful for import into MariaDB (and MySQL). INSERT arrays are supported as a bunch of more options.
JSON - This format is rather obvious, the output is a file consisting of one JSON object per row. To support binary data, which is a no-no in JSON, base64 encoding of binary data is also supported.
JSON Array - The format is similar to JSON, but instead of separate objects per row, this format consists of one or more JSON arrays of JSON objects.
HTML - This format will produce a valid HTML TABLE. This is sometimes useful when you want to view output data that includes UTF8 characters for example.
In additions, this version of myoradump includes a bunch of new features and bug fixes. I will follow up this post with one that includes some specific examples of using myoradump eventually.
I have created a small program to extract data from an Oracle database in a format suitable for importing into MariaDB (and MySQL I guess). It is pretty fast as it is written in C and uses the OCI interface. Also, it supports most of the Oracle basic types, including BLOB, CLOB and LONG. UTF8 is also supported and there are many generic features, as well as some features specific for later import into MariaDB.
Download the 1.0 version from Sourceforge where the programs source, that is using autotools for building, as well as documentation is available. I have not tested to build on any other version of Oracle than 11, but maybe someone could help me there.
Now it's time to release something useful! At least I hope so. I have been going through how I came up with this idea and how I came up with the implementation in a series of blog posts:
But now it's time for the real deal, the software itself. This is an Alpha 1.0 release but it should work OK in the more basic setups. It's available for download from sourceforge, and here you find the source package which uses GNU autotools to build. The manual is part of this download, but is also available as a separately.
You do need MariaDB libraries and includefiles to build it, but no Oracle specific libraries are needed, only Oracle itself of course. There are build instructions in the documentation.
Now it's time to get serious about replicating to MariaDB from Oracle, and we are real close now, right? What I needed was a means of keeping track of what happens in a transaction, such as a LOG table of some kind, and then an idea of applying this log to MariaDB when there is a COMMIT in Oracle. And thing is, these two don't have to be related. So I can have a table which I write to and also have a Materialized View that is refreshed on COMMIT on, and I need a log table or something. And when the Materialized View is refreshed, as there is a COMMIT, then the log can be applied. From a schematic point-of-view, it looks something like this:
This looks more complex than it is, actually, all that is needed is some smart PL/SQL and this will work. I have not done much of any kind of testing, except checking that the basics work, but the PL/SQL needed I have done for you, and the order table triggers and what have you not is also created for you by a shell script that can do this for any table.
As for the DUMMY table that I have to use to get a trigger on COMMIT, this doesn't have to have that many rows, I actually just INSERT into it once per transaction, and then I INSERT the transaction id, which I get from Oracle. This table will have some junk in it after a while, all the transactions that were started and COMMITted will have an entry here. But in my code for this, I have included a simple job that purges this table from inactive transactions.
Best of all is that this works even with Oracle Express, so no need to pay for "Advanced Replication", not that I consider it really advanced or anything. I'd really like to know what you think about these ideas? Would it work? I know it's not perfect, far from it, for for the intent of having a MariaDB table reasonable well syncronized with an Oracle, this should work. Or? The solution is on one hand simple and lightweight, but I have given up on the number of features and possibly also the design affects performance a bit.But it should be good enough for many uses I think?
Let me hear what you think, I'm just about to release this puppy!
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.
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...
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.
A longtimeago, in theearly 1980's, I decidedtochangejobs. I was a youngguy, withno real experienceofcommercialsoftwareoranything like that, rather I was a self-taughtsysadmin for an ancientUNix system. Thecompany I worked for was in theTelcobusiness, so I looked for anotherjobwhere I coulddevelopmyself and atthe same tousemytelcoknowledge.
I found a Telcostartup, privatelyheld. I must saythatthefactthatitwasprivatelyheldmeantnothingtomeatthetime.Nothing.Theywerebuilding a system, the servers wereVAXesrunningVMS, and again a became a sysadmin.
Havingbeensysadminatthiscompany for a while, buildingupthe central datacenter (to be honest, in todayswords, thatwaswhat I wasdoing, butatthetime, I hadno real clue. I wasmosrenthusiastic and readytotakeonanytaskthan I was smart or intelligent orknewwhat I wasdoing, really). But I wantedtodevelopmyself, and atthepreviousjob I hadlearnttocode in C (itwas a Unix system after all), so I slowlymigratedintodatabasedevelopment, managingsysadmindutiesontheside.
Still, I wasn'ttrulyprofessional I think.But I waswillingtowork and I waspersistent and just wouldn'tletgo. I cametotheofficedressed in a pairof Jeans and a T-Shirt, and wasn'treallyawarethatsometimesitwould be a goodideato dress uporsomething (thiswasthe 1980's still, so thatmighthavebeen a goodidea back then).
As a developer, I realizedthatthe system used a database, and a SQLdatabase! I hadnocluewhatsoeverwhatthiswas.But I startedwritingcodecreatingtables and workingmywaythroughthis, learning as I went.Thatyouneededsomethingcalled an "INDEX" becameobvioustomeafter I hadshownmy latest creatingtomycolleagues and thethingswas just sooooslow. In theend, I actuallypickedupthe manual for that "SQLDatabase", whateverTHATwas.
Afterabout a yearatthiscompany, theydecidedtomovetheir operations abroad, and I wantedtostay in Sweden, so I wentlooking for anotherjob.ThecompanybehindtheSQLDatabase I hadusedwaslooking for peopleitseemed, so I applied for a job.Whatitwas like working for a USbasedsoftwarecompanywassomething I hadnoclueabout. I got thejob and turned up for myduties as a support engineerdressed in Jeans and a T-Shirt, and got towork.What a support engineerwasreallysupposedto be doingwasn'tsomething I reallyknew, itwasmorealongthelinesofpeoplecallingmewithquestions, and I triedtohelpthem, as best as I could.
Thecompany in questionwasOracle. And Oraclereallydid support me, and courageme, todevelopmyself, togototrainingclasses (I didn't ask for these, I was just sent awayonthem), totakeonotherjobsinsidetheorganizationtotodevelopmytechnical and businessskills.
For all this, I amgrateful.Oraclelargelyshapedme for myfuturecareeramongdatabasecompanies, and ifthat is a goodor bad thing is up for youtodecide.NowI'm back atOracle, and I still enjoyit. I amawarethat not everyonewillagreewithmehere, but I am glad to be back, afternearly 20 yearsafter I left, and manythingswiththisgeartcompany is still around.
All in all, I'msureOracle is a goodhome for MySQL. Youmaythinkdifferently, but I amhonoredtowork for Oracle, and evenmore so with MySQL atOracle.Frankly, I can'tseethatitcan get any better.
