tag:blogger.com,1999:blog-91445059590023287892024-03-18T10:27:11.573+01:00Karlsson on databases and stuffI 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.Karlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.comBlogger279125tag:blogger.com,1999:blog-9144505959002328789.post-16910093544359630522024-01-11T16:13:00.002+01:002024-01-11T16:16:37.825+01:00MariaDBTop 1.1 released<p> I have now released version 1.1 of <b>MariaDBTop</b>, it can be downloaded <a href="https://sourceforge.net/projects/mariadbtop/files/mariadbtop%201.1/" target="_blank">here</a>. This version has several bugfixes, a number of small improvements and features and one rather big feature, which is the ability to show measurements in a "semi-graphic" way. The sample configuration file has also been anhanced and the documentation has some additional stuff in it, like an example section.</p><p> The vertical semi-graphics bars looks like this <br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgw5jEmYY53ZXoeZrxqGbFs8AVVRFynzdc0rmNcFN4-hnKElAdzVMDsBm6-FXjryKGEBuIdWw63NAXeDrZCcoUnuEQ-tfY_66CgsFluWT660WoA9uK0cFog7X_Jl8Tt4luEpFQJu_RGNeYqyNinb6ZYtV4PVv1W92Dinu7nEYlHjI_HbFSByS_t6BOQujdj/s976/MariaDBTop_Bar1.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="271" data-original-width="976" height="178" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgw5jEmYY53ZXoeZrxqGbFs8AVVRFynzdc0rmNcFN4-hnKElAdzVMDsBm6-FXjryKGEBuIdWw63NAXeDrZCcoUnuEQ-tfY_66CgsFluWT660WoA9uK0cFog7X_Jl8Tt4luEpFQJu_RGNeYqyNinb6ZYtV4PVv1W92Dinu7nEYlHjI_HbFSByS_t6BOQujdj/w635-h178/MariaDBTop_Bar1.png" width="635" /></a></div><p>Happy SQL'ing<br />/Karlsson<br /></p>Karlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-42537530948140406492023-12-04T16:46:00.000+01:002023-12-04T16:46:27.074+01:00MariaDBTop in action<h1 style="text-align: left;"></h1><p style="text-align: left;"><span> </span><a href="https://sourceforge.net/projects/mariadbtop/" target="_blank">MariaDBTop</a> is a tool I have come to use quite often but it is a bit hard to configure and set up. In this post I will show you have to set it up by using a realistic example. So, what I want to show is monitoring of a simple application that emulated Telco style CDRs (Call Data Records). A CDR is a very common data structure in Telco and network applications where a CDR is a representation of an event in the network such as a call being started, a message is being sent etc. CDRs are never updated, they are just inserted.</p><p style="text-align: left;"><span> </span>The CDR structure is not standardized but similar across the many places where it is used. In this case I have an application that inserts CDRs over multiple threads. The database that these records are recorded in is replicated and we want to keep an eye on the processing on both the primary and the replica.</p><h4 style="text-align: left;">Main configuration section</h4><p style="text-align: left;"><span><span> </span>To begin with we have to configure the main section of the MariaDB configuration. To begin with let's use a decent colour screen. For this type of applications I like green text on a black background and for alerts I want a bright red text colour. We are using the default configuration file <i>mariadbtop.cnf</i>, all the configuration data goes into that in this example, and the first part of this file then has the <i>mariadbtop</i> section then looks like this</span></p><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">[mariadbtop]</span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">default-colour=green</span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">default-bgcolour=black</span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">alert-attribute=normal</span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">alert-colour=black</span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">alert-bgcolour=red</span></div><div><span></span></div><p style="text-align: left;"><span><span> </span>We then provide connection information to the MariaDB Primary server</span></p><p style="text-align: left;"><span><span style="background-color: #eeeeee; color: #444444; font-family: courier;">user=monitoruser<br />password=monitorpwd</span><span style="font-family: courier;"><br /></span></span></p><p style="text-align: left;"><span><span> </span>With this in place we need to define the replica server that we are connecting to as well as the pages that are to be displayed, the actual attributes of these are defined later. We have one server to add, the replica, and three pages</span></p><p style="text-align: left;"><span><span style="background-color: #eeeeee; color: #444444; font-family: courier;">pages=dashboard,replication,innodbstatus<br />servers=replica_server</span><span style="font-family: courier;"><br /></span></span></p><p style="text-align: left;"><span><span> </span>With this in place I also want to refresh the status bit more frequently than usual and write alerts to a file</span></p><p style="text-align: left;"><span><span style="background-color: #eeeeee; color: #444444; font-family: courier;">refresh-time=1000<br />alert-time=10<br />alert-file=alert.log</span></span></p><p style="text-align: left;"><span><span> </span>The above configuration will refresh the screen every second (1000 ms) and will write a record to the altert log file when there is an alert and every 10 seconds for ongoing alerts.</span></p><p style="text-align: left;"><span><span> </span>The above should be pretty much self explanatory</span></p><p style="text-align: left;"><span>Defining the dashboard page <br /></span></p><h4 style="text-align: left;"><span>Defining the replica server attributes</span></h4><p style="text-align: left;"><span><span> </span><span style="font-family: inherit;">Before we go on from here we need to define how to connect to the replica server. The name of this is, as defined above, <i>replica_server</i> which means we need a section called just that with information on how to connect to it. In my case it looks lik</span>e this</span></p><p style="text-align: left;"><span><span style="color: #444444;"><span style="background-color: #eeeeee; font-family: courier;">[replica_server]<br />user=monitoruser<br />password=monitorpwd<br />host=repl1</span><span style="font-family: courier;"><br /></span></span></span></p><div style="text-align: left;"><p style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em; text-align: left;"><br /></p><p style="text-align: left;"><span>The above should be pretty much self explanatory</span></p><div style="text-align: left;"><h4 style="text-align: left;">Defining the dashboard page</h4></div></div><div style="text-align: left;"> Now we get to the interesting bit, the actual data items that we are to monitor and the first is the <i>dashboard</i> page.</div><div style="text-align: left;"><p style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">[dashboard]<br />pagetitle=Dashboard<br />pagekey=d</span></p><div style="text-align: left;"><p style="text-align: left;"><span style="color: #444444; font-family: inherit;"><span> </span></span><span style="font-family: inherit;"><span>This above defines the title displayed at the top of the dashboard page and the key used to navigate to it. The next thing we want to do is to display the number of rows in the table we work with, this is called <i>cdr</i> and it also resides in the <i>cdr</i> database and for this we could do a SELECT COUNT(*) but I decide against that and that will slow down the actual processing going on, so instead I run a SHOW TABLE STATUS command which gives a less exact result but doesn't intrude into the normal processing as much. I end up with this setting</span></span></p><p style="text-align: left;"><span style="color: #444444; font-family: courier;"><span><span style="background-color: #eeeeee;">sql1=SHOW TABLE STATUS IN cdr LIKE 'cdr'<br />vertical1<br />hide1<br />name-prefix1=primary_ts_</span></span></span></p><p style="text-align: left;"><span style="font-family: inherit;"><span><span> </span>This will run the command above, the <i>vertical</i> setting will cause a row/value pair for each column and the <i>hide</i> attrbute will ensure that this data is fetched but not displayed, we will look at that later on. Finally the <i>name_prefix</i> attribute will prefix each column name fetched with <i>primary_ts_</i>.</span></span><span style="color: #444444; font-family: inherit;"><span></span><span> Following this I need to get the same data from the replica server, like this</span></span></p><p style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;"><span>sql2=SHOW TABLE STATUS IN cdr LIKE 'cdr'<br />vertical2<br />hide2<br />server2=replica_server<br />name-prefix2=replica_ts_</span></span><span style="background-color: #eeeeee; color: #444444; font-family: courier;"><span><br /></span></span></p><p style="text-align: left;"><span style="font-family: inherit;"><span><span> </span>The only attribute different here is the server, as we are getting this from the replica. Also, we run the same comman so we need to use a different prefix so we don't userwrite the key / value pairs gotten from the previous command.</span><span> We have now defined how to get the data</span></span><span style="font-family: inherit;"><span> we want to display, but we haven't actually displayed it. What we want to show is three things, the number of rows on the Primary and the Replica and the difference between the two. This is how this is done</span></span></p><p style="text-align: left;"><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;">expr10=this.primary_ts_Rows<br />name10=Primary_rows<br />expr11=this.replica_ts_Rows<br />name11=Replica_rows<br />expr12=this.primary_ts_Rows - this.replica_ts_Rows<br />name12=Replica rows behind</span></span></span></p><p style="text-align: left;"><span style="font-family: inherit;"><span><span> </span>The difference may also be calculated like this</span></span></p><p style="text-align: left;"><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;">expr12=this.Primary_rows - this.Replica_rows<br />name12=Replica rows behind</span></span></span></p><h4 style="text-align: left;">The Replication status page<br /></h4><p style="text-align: left;"><span style="font-family: inherit;"><span> <span> </span>Now it is time to display the replication status, page, and we start by the page specific data</span></span></p><p style="text-align: left;"><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;">[replication]<br />pagetitle=replication<br />pagekey=e</span></span></span></p><p style="text-align: left;"><span style="font-family: inherit;"><span><span> </span>With this in place we are ready to define how to get the data, on the replica we run a SHOW REPLICA STATUS command, and this has to be vertical as we want all the columns from that command, and on the master all we want at this point is the BINLOG GTID. This looks like this</span></span></p><p style="text-align: left;"><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;">sql1=SHOW REPLICA STATUS<br />vertical1<br />server1=replica_server<br />name-prefix1=replica_<br />hide1<br />sql2=SELECT @@GLOBAL.GTID_BINLOG_POS<br />name2=master_gtid_pos<br />hide2</span></span></span></p><p style="text-align: left;"><span style="font-family: inherit;"><span><span> </span>Again, we have so far only gotten data that is not displayed, the data that we do want to dipslay now is the GTID pos on the master and the replica and the Seconds_Behind_Master column from the SHOW REPLICA STATUS command. This looks like this</span></span></p><p style="text-align: left;"><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;">expr10=this.replica_Seconds_Behind_Master<br />name10=Seconds behind<br />expr11=this.replica_Gtid_IO_Pos<br />name11=Replica GTID<br />expr12=this.master_gtid_pos<br />name12=Master GTID</span></span></span></p><p style="text-align: left;"><span style="font-family: inherit;"><span><span> </span>And that completes the replication page.</span></span></p><h4 style="text-align: left;"><span style="font-family: inherit;"><span>The InnoDB status page</span></span></h4><p style="text-align: left;"><span style="font-family: inherit;"><span><span> </span>We start the InnoDB status page with the usual page details and then getting InnoDB status using SHOW GLOBAL STATUS.<br /></span></span></p><p style="text-align: left;"><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;">[innodbstatus]<br />pagetitle=InnoDB Status<br />pagekey=i<br />sql1=SHOW GLOBAL STATUS LIKE 'Innodb%'<br />value-col1=Value<br />name-col1=Variable_name<br />name-prefix1=pgs_<br />hide1<br />sql2=SHOW GLOBAL STATUS LIKE 'Innodb%'<br />value-col2=Value<br />name-col2=Variable_name<br />name-prefix2=r1gs_<br />server2=replica_server<br />hide2</span></span></span></p><p style="text-align: left;"><span style="font-family: inherit;"><span><span> </span>Now, we want to process the data we got above to something we can display. Let start with the InnoDB cache hit ratio, which is an expression based on two other values returned from SHOW GLOBAL STATUS, so for this we use these two</span></span></p><p style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;"><span>expr3=round(this.pgs_Innodb_buffer_pool_read_requests / \ (this.pgs_Innodb_buffer_pool_reads + \ this.pgs_Innodb_buffer_pool_read_requests) * 100, 2) \<br />name3=Primary InnoDB Cache hit ratio<br />expr4=round(this.r1gs_Innodb_buffer_pool_read_requests / \ (this.r1gs_Innodb_buffer_pool_reads + \ this.r1gs_Innodb_buffer_pool_read_requests) * 100, 2)<br />name4=Replica InnoDB Cache hit ratio</span><span><br /></span></span></p><p style="text-align: left;"><span style="font-family: inherit;"><span><span> </span>In addition to this we also like to get some other InnoDB data displayed, like this</span></span></p><p style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;"><span>expr5=this.pgs_Innodb_rows_read<br />name5=Primary Rows read<br />expr6=this.r1gs_Innodb_rows_read<br />name6=Replica Rows read<br />expr7=this.pgs_Innodb_rows_inserted<br />name7=Primary Rows inserted<br />expr8=this.r1gs_Innodb_rows_inserted<br />name8=Replica Rows inserted<br />expr9=this.pgs_Innodb_rows_updated<br />name9=Primary Rows updated<br />expr10=this.r1gs_Innodb_rows_updated<br />name10=ReplicaRows updated<br />expr11=this.pgs_Innodb_rows_deleted<br />name11=Primary Rows deleted<br />expr12=this.r1gs_Innodb_rows_deleted<br />name12=Replica Rows deleted</span><span><br /></span></span></p><p style="text-align: left;"><span style="font-family: inherit;"><span><span> </span>And that completes the configuration, we now have three pages of data</span></span></p><h4 style="text-align: left;"><span style="font-family: inherit;"><span>The result</span></span></h4><p style="text-align: left;"><span style="font-family: inherit;"><span><span> </span>With that in place the dashboard looks like this <br /></span></span></p><p style="text-align: left;"><span style="font-family: inherit;"></span></p><p style="text-align: left;"><span style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="" height="111" src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAuoAAACCCAYAAAAQX+24AAAgAElEQVR4Xu2dT4gfx5XHaw4TsHWQcDKBHSHrkrVIEDnY2CSW9hiCIx9mIdFBYCxY2NyUMezMyUezh5kFK3Nbw6I1ggi0AXkPclhyXFnGK2wWr+UQ715k2bMHxSAvRD7oMPtedddvXldX1av+df/+dPf3Z37WzFR3ddXnVVV/6/WrrpWHDx8eGHxAAARAAARAAARAAARAAASWisAKhPpS2QOFAQEQAAEQAAEQAAEQAAFLYOWAPmABAiAAAiAAAiAAAiAAAiCwXAQg1Duwx8rKSjIXzIU6gIwsBksA/SdtWvAZbNPvpGJjbx9jr38njSiRCfi2I6zx03Jn/QihrlHKSNcMAaGeARGHjJYA+g+E+mgbfwcVH3v/GXv9O2hCySzAtx1hjZ+WO4S6RigzXTMEhHomSBw2SgLoPxDqo2z4HVV67P1n7PXvqBlFswHfdoQ1flruEOoaocx0zRAQ6pkgcdgoCaD/QKiPsuF3VOmx95+x17+jZgShPiOQWvvULttAqO+bO3s7ZvfW/iTP9bMXzPalDbOuXUWk39k7b67cN+bImctmd6PJmQ0uQofe2Noydx8bc3p712RdZv+G2dq5ax6vnjbbu83qxCXTDKEL9ZZ87+yZ87u3JpBOXpgtX2mN/RtbZvPaPfuns1vXzaXnm9lq/kffMXvnd42ldfKCuU72nvlngfaZpm796j/Cnq6yM7LrvMYv32ZdjC83aPy+Jsbv1SdPmvW1M2Z3Hu1/mkY49TkL6N9eWfvVf/h+ed6UQ7ioybo5e2HbXMq6gU5trE5ObNU/+N6/ec0UdzC6h22cNbfeObyXZt3T5jy+h9rXnRt75sq1W+ZPZT0q+mxR+sZjS3TN1vUfm/fd/deW9aS5cDlTp7VsLcs6fmvVyhbqd/a2SGA/IoF9kQT282afGubm7h8p/1ME/pLpQpvd2NozX1COP9ptmR93mptfGfPUy+Z6A9XIRixOay42Ww0UVGfbgAjnt05t0eTnefMlid/ddx8ac+ycudxg4sCieYdmKKunt2c6EfIbFl/3KhnviR/t9kCoc+npZr71Pv37tLk0R6GyKPtoA0ElvYf9x5a/vBk9enjPnPr+MfP0K80G/87Gn0aw8w5uO77Y/knjwhM0LrDw2r9zw+xcuWtWj5zuTKgvF7/F9G9rzV72H57c3DRfmafMy/Z+vm94Ynf3m1VzumE/ymvR3R7VSf+Q968pbTiX8T1UNvrbVql5di8dJ9ttmnfJIXpMOEQXp2+KvvgN3WtfmWgZal9bV83/mSfMT0jvzc5l2207mzY3rX1q+eYJdb4BFirM7Arh6xqlvUd+XHjaefb54/fPk+e9uPTEs5uacdZmXYfFzprNerVk78DtR8acuCgFNzcM8ijdc08E1syaeUD/2UIWXtUWs07NEEmPenldUteVm6brWE98+6T58FYx31+lLz0oKOHS7NSb1AQHigrfYvb6wgeHXvA8r3Ld429Wj5qTa8fMGXpqwfldvfvQ/OHjrw2hr5ePhMHelWvmlpvuE/2zFy6SaKBbgvDIx+vHnZ284M7tcXTNmK+t9Yo29sKXZm+H0g8f+JiqR4HOr6ST/b9zypw6ogt1toN4WGFO/tVz5t6/f3jY6rj9nD+I1k82z5B9/CcSwf6z75ffVOtX3lirHtOj5DE911iIxfqP/0RN2t863RbVf0rAk8nit0+Y+3/6huZgr4jJaqL9XnzRvPf3hx41aa/J+KN6zAphI/mvUbu4SOPK8az2nR6qW40v1jQ8gaeeefqi2aY+V78xxsvPThjZB9Z++Jw5+PjDwnN3ksag106Y68IjGeSXrh6XMMpPvT6PgbX+4fVvpf8k69fQcRS9/0Tax8TJtdD+4wv1os1MnC8mPn4Xpm05Prccv7roHxVHky+G1fG3oBAa3w+fVjxpjtLd8WvXF9bPmgvbl/Ke+Iv+E25fXgcLabYFti929BbziNKRxxOL98UY3bp/1seP1SfF/W/Jx29teMwT6j5Ul6v4+2sHb1Y9qpFzUjPObjwy9QGHi1t9VFTetD/6szEkZA5DH/jcK+a+OWLONHwU02agiDLx+G6+S8L0yLNmizq3+S0/4XhMTziqnvOcvIqQo2JG+wXNaHOeYBQd7TF1NPLIPU/nc8faPGRVCH8WzlS+y6Hy7Zt9K6LXzTqd7s/urVhN1M/a79Fj0hnbZuM42XOHPAZkvmPnihCfG3t7NDs35ieXytk5l+8qecyfICFOHoa9LfIWPSZvEbF7fj1m/3h3qXGteDD4Vpuun8s5Zp/aEwmv/6Trt1ETYuzV2yxizBqG9oT7j2b/4un4YvpPeYsUHpqfU/f4PXlwjk48ODnlzxl/YvarPYq2Nx7yIq0WXiQqXLJ9awN1m/HF8eGJ1hWayU4ejT9HoYuvUJgf2U4rf2FeDq/7iH44YtafI8H/07LU3yXhb/OY/olou+vTcPhmun9r/afQmun6aTYq0nPuPzxcVNtHMXFaZP8JeNTJsXX3MXnUbfhoenxrNz63H7/a9g/pLJF2do7GrPbDZo080WZxbW9v33d9rtn997BM4fblt81wORbYvryJA4/HhU4vnnpm8U30T0P6QzoiYve/ZR2/tbElS6jniL+lEerWe3ybvM4nzDY9wrMDYMhjHfFiZ81WA1TbDBTz4esNDLHJV7DF6I+RNaHJNtjbIY+68Hg/SU7xZ8unHsnzX/jArh+oPHGQ5ef0iEfPxsRtGXNdzt5jbSLZW/ZJ7F8l8fcECa9LNC54ayCU+rmspxLqWv3so2rPo0UXXCOPzUU7MdGGAZEe6j8NwoQW0X+cyDp89Es0Kh4cvf1yHjlCM/rEKvDEUd6M7BMn79F6xaOkmKjN+BLK2oYuuoncawfBJ6b+zZRvfqky5/ALVjPyxDb7+qFyyfGd65caH1zoZqMxMWKw2P1HaR8uDHxh/cdOEsp1O5OqUYz6lnPMJMbv48X6rqnH5w7Gr7b9Q73/5LSfpFCvr5mbKlw0OD574/fOu+bB4yN0by1tJ5IX176EM4CfYP6zaC+JiIoipr0MhU72z7z737KO39odOkuoW6EbGWjc44yff5F3I5q5R72HQj04kWD/ioiZ74Lv4Y32p+bF9/4t25vu4rmrMWbVppUW6hSWYp9UfMucoYF/g5Sjf7w6UKZuBBuUfyreXLuRZ8aoV0IrPivWQBShYM5TEa9fK6Gu1a/mTiHv1++vmk2OMzrW0KOeEOop+7siLOpG4IcnTZCc3aJ1KiYQI1kfGnOE5rIO9Ppidb++wjN3mUJXcoRkX4X6Lj9Ry1iPMnqhLmPUZXtRxjdNqM9h/JqpUG9Q/myPbSnqG6/r0oS62kdDYcGaTOziZRlW0NiJ/uP/+1/zF0dXxXq2PEeK5iiY1IIe3cfuf8s6fmsWyBPqlnF1Mekdb7EjP9p1ixhfO/6B2dm9ZuO/1723j6SF+uGs84UP9szOtWKx6sQzrtWmcK15i2KKk5Y59MWWWllMynxzPHIpvi7c5e7Dz829k0+bY6dlDG8arr9aep8e3V6lmO/PKWD+FHnFkxMJ6xEvn3Jw2M6XN8xVilf/nDZz5XNZ62oe+Yr9OGzHC33x22e1NjwQtAt9KfIrBpT7+/9ljlJs/uSNQtZ7l65fjlBP9Z90/Yr2fZtCg06UXhT2mO7QLHp1MpnI6jzR/qPZv1i6sqhHq/HFSi60iwaFytum/PbL5ZdtLDb+ZN+IA6EvOf03ZqV2QqQutHznS27oSdqjPv343e76ev/W+k/RvdNPDPJ6UM79h3Ja+tAXUduM8a3d+Nx+/GrXP/T7T1b7YbMuSehLuK0uanye3P3o/rljPtp/aI6tP0tv1ztcRJrFN9E/c+9/yzp+a2NLtlAPLfZJLtarLPb7G3PiN/9UvA6v9jl8tGHFBa3am0RH0KN7jsdu9OjeivLMxaT+YsIFLrZgvrHXX9r4VvHuLP/1UScv5PF1N6PzU72tp75Yg2IrzAUShi988aZSvsuGQ6NkHdZOHjUP7vGy03Vz7pwxN2+6VaL112MVcYLsNa8uJv0OTTaOTCYbgcWCFFuz/izF0pIKWw8tNvMXE2u9pRyIN4uVypU3CvkxjrJ+Fy6fN59v+o+V3cXK9u+Xz18sS2HOtcWcon4fUHzw7QcfmXuPJkt5ebVpt/3HWwzn7M9PSOxnEf2n9tiUF0v7vPlvr5mD31YXe/rlj48/obAAz36JxZByMSmfVe+/+qtU2wmR4gb9R1rG9uBwKXqxEHSyUDK1mDNef7nYv9343fL6av9+IdF/KMa9FvZR2LfTlxks8WLS+usZq6/MS49vHGfshR7Q+NVkfG47frXpH37dwv0zNf7G248L3fjCrrGihaQHD8y9w/cndjg+25mmCF8KvPJwEeOzd0+NOxJT92+9f/LT0Pj9b/nHb016NBDq1AzYi1572aqIY9OuNq90f8V28Lr1xy2Le33RvMBwX+7CazTH8kYuxW3xCr1u7sicX0O5+JrPoQRZ/adejlH0nzngD12ijRBZUJHHe1n0H6sV5jk+L3v/aPxe/VTvQfvq3diitU+tQo2EupbZMqWnOkYtnpVfr8cLjha64dFs6dVjeOe3yUBXNfO9PtNsuNVVWYaeT+MbSwtvDbPUBrLmMdjDshD49MueY+w/ixyfl7l/1J5WdLAZ2xjbV79GgGpptfap1W2wQl2reNfpmiHGLjS65o38hkUA/SdtT/AZVnvvujZjbx9jr3/X7cnPD3zbEdb4ablDqGuEMtM1Q0CoZ4LEYaMkgP4DoT7Kht9Rpcfef8Ze/46aUTQb8G1HWOOn5T4R6m0z0i6EdBAAARAYKgFMxNtNNIbaLlAvEAABEGhLAEK9LUGcDwIgMHoCEOoQ6qPvBAAAAiAwEwIQ6jPBikxBAATGRABCHUJ9TO0ddQUBEJgfAQj1+bHGlUAABAZKAEIdQn2gTRvVAgEQWDABCPUFGwCXBwEQ6D+Bv/vFL8zhll1lfabccMqnITdkCW3Ac/jq1WV55arYYKR8FR3WQPW/jaMGILBQAi/S1c/TlzZINDfpuxkpzd/S37folb/fM+bgf+jnv2xQ6v8uzuPPwa8D19DSU5ei8q+8Vz3ggHZnn3wS6RDqDWyIQ0EABEAgRODgP35tzt/8ypinXi52rC23XX+0esJcnOz+OT07FutXvzDmiR/tGs7e/zR+r/L0Rck8s7qhHIR6JjYcBgIgkCbAQvwHARHNZ10uT71O/95uCJJEuNml71v05UnB2+J3zkpL1y7Heb5O359FDkykQ6hrcJEOAiAAAgqBmlCn4yvbZR//wOxduWZuue3DzZo5e+GiubThVPe+ueFtMb/65FGzvnbO7O5u2Lyu3n1o/vDx1+aRK8vJs2arnARYof7oIW1R/nVki/J6/mvk7b5IedsS3LmRLJ/cMG3th8+Zg48/NLYqrgz7JMx3ds2tfVe4NbP2nVPm1JGnzSW6BoQ6uhAIgEAnBGJCXRPC5cVXDoofKt7sUMFY9H9KXxbuDdKj+Wvlg1DvpHkgExAAARAIEggJdVPZ6vu42bcidt2sr5Mu3jtvCgf8deshL0Q9SfDTF802i3c6d/PKfWOOnDHXS6G+ee0Bnf+s2bp8yZjf0hbt9x9T8rbZ3Vg3dvdDzn/tgrm8vUFXYeF803y1+pR5mcT8FyzkH1P227uGDqcLcvr75pvVp80rlP+62U+Wz1aa67P7Ef1wxKw/R+X8aYniu1ScN+laj+la25fM8+v7dOiO2f3oz1Sec7b8EOroOCAAAp0QiAl1/vsGfSnUZRL28ir97nnWs4S686jHwmYS6SmhLkNfbFiOLJ8X+iLT4VHvpOUgExAAgTETUIX6z78gYUwe9YnH2Zgn10h2XyyEOqlgs7dFHmkR6L5GMe4XrfAthHwl9IVE89b73xjz9CulUPeEOGtxd84zL5r7/2njZsyuiJu5s7dliixIvBvyqCfLVwh1ec2JvUN/t6E/d41ZPW2fCECoj7l3oO4g0CGBmFAnD/jKr7zYcvaKx2LZY0VqIdIb1bKBhx1CvRFZHAwCIAACdQLp0JeXzInf/Ku5b75lzmxtmw1S3smYc3K97//+qtnkOJljhx712Qn18+bzzSt6+SDU0fRBAAQWTSAh1O1CU+kFf5d+f6PuVY9WgYX9M/SNxZFr6U3ZcNx7arFrmQ6h3hQsjgcBEAABj0B9MakIPXnthLm+c9s8NifMNnnIzZc3zFWKV/+cVvyfKj3qHGN++xEdcXGbPOwk5EkU71BszCotTmUvuO5Rr4a+8DV2rtA1jxSLWZOhL68dmDeV8tnqxoS6fRqA0Bd0ChAAgTkQSC0mDS349IRwNDSFRf1n9HUeeD9GXUsvqx7Nn8sm31bji/5EOoT6HNoVLgECIDBsAtrrGeUrFpnE2smj5sE9Xha6bi5c3jUHb+6Z2w8+MvceTZaKUhItFiVhf/yDLbN57TAm5uzGWXPrnVsToGsnDeXFvx6l/76m/8pP5fWQ6cWk6fKxx53CcgImnLwuMrSY1Dyg/+hDi1b/5R/+etgNALUDARCYLQESyisvVS9Re/1iGbbSOEY98GpEvtLBL+l/5Vtg/FcrVtJFsZIx8KIOB7+jk3zPfSQdQn22TQu5gwAIjIAANjxKGxkx6iPoBKgiCIDATAhAqM8EKzIFARAYEwEIdQj1MbV31BUEQGB+BCDU58caVwIBEBgoAQh1CPWBNm1UCwRAYMEEINQXbABcHgRAoP8EINQh1PvfilEDEACBZSQAob6MVkGZQAAEQAAEQAAEQAAERk8AQn30TQAAQAAEQAAEQAAEQAAElpEAhPoyWgVlAgEQAAEQAAEQAAEQGD0BCPXRNwEAAAEQAAEQAAEQAAEQWEYCh0L9dyv2ZfIHv6Zi8s5MtGvSyq+KIgdfzL6MtUGZ+knA22zggHZs5I/bOMC2wTP0v9tK9WjHspV/LNusa8fLRIR2HuONGORn0t/mWU5/h7V5Xjt0rSHYP7Bhht2M49VAu9X4ext7TDbdcOy09EXbE9cHARAAARDojEDVo843EN5u1W25Wu7yNPm9s8siIxAIEOAtdL3tfk3obxo83pqXP24rYO34Oabz5MNNROxluc+9ERBzcyzT0lyq7/bnra036Ot2m5tm/NTO0dKXxpgoCAiAAAiAQBcE6kL9M8r2mfJm498U+Ea0degVlN5AK0DYg0Qfu30rbb3K3s3KFq9ie1c+Dp76Lkw4oDxYtL5DX96ylz/c3n5AXye4E+2vQiEk1MsnRBPvZOmVrHi059A+a0Kdy/ppWWdZJu5H9EQru//IrYf5aQLlOXm6wP2SJ0A5TxxkPu48hpvDj4/zt0CWE3+tqfbd/r5QL7nZanMb1vhrW2Rr6Vr7bdO+nP1p22u3jXdt+27f/uX9YPJUQSuf1j6QDgIgAAIjJFAX6uzde52+/C9/3i5v8g4OD7YuBEHeWMtHvzZE4XwpMvhnzsuJLz7eeZs4P86L0+XfRmgEVLkk4LeHkLc51v4kxJhHXYpiPt6fCMyhfdaEOnuRZXhEKebsJNb1QS4r9zmtfH69Q8KR84rx8cviT9Q1fqHr08Q++4lc3+0f4q2Jd7/zax7zVLrWPso2bx0o07avc6K9+iE8/kRL3hNy2i8GQhAAARAAgRqBsFDnw1igs4CQQt33iLDHZJe+7AGVNxB5c3I3909IvL8XtkAlFABGGjcBJxZdG5ShMKn211aoB2KMXZZdtk8Zd8/51+KP/cmDK0Ru+WT4iC+8XV4hoR6bNMvJkibUOX8Rhx+N0U618D7bf5FCPbd9tGlfmv1TYWq55Rv36IfagwAIgECmUGfvh7uZswelFEvWG0ghLZPQBDlwa0Kdxfw08cYw2rgIOCHBtXYhISWBZPtrK9RLkZnt/Z3SKjWPup9PTEjlls/1ST5exktrfLoS6mJiwU/WjBg/spD12f653vPUGoo2HvWc8bVN+2oj1HPbb1YjwUEgAAIgMB4CYY+6C23hgZ8/LNTdDcQ9pi/jhbM96izUtbcdjIc7apoiINudFH7uKQ+3T7/9aUKU06VAciEm8u0wc2ifrYR6bvkcv9AbR3wOkltO6AsfL+KtKzH+fL57wsbHxUJvtNbfV/vnLiadlVDPaR8poa6drwl1/3x3z3BtQstfaxdIBwEQAIEREqi9npEZTG6+Tgy58INyQZE9hmMcPzuMRXdhLfbvFJNu4yBZBNHHLopznnh/sRrflBGjPsKml6hyIsa88spQ0f44htsPK3FXmISuiNAZG5Zxs2ybnlivLJbrsn16r2f0XzkZKn/q1Xy2DqHyMb9n6v1K5cPAYotJOU3jx2XhOtJrXu0YEXs9odba+2j/QGiHX3+Vf+j1nTyeuvFRSw/ZT7SPVu1Ljv08lvO4HnoVqt9+5MRNKZ/WLJAOAiAAAmMkgA2Pxmh11BkEQAAEQAAEQAAEQGDpCUCoL72JUEAQAAEQAAEQAAEQAIExEoBQH6PVUWcQAAEQAAEQAAEQAIGlJwChvvQmQgFBAARAAARAAARAAATGSABCfYxWR51BAARAAARAAARAAASWngCE+tKbCAUEARAAARAAARAAARAYIwEI9TFaHXUGARAAARAAARAAARBYegIQ6ktvIhQQBEAABEAABEAABEBgjASqQj20oYbcDCaXEHagyyWF4yQBrf1p6SIvuwOo3CzGpckNWfz00KY1KwETlRuBrXyv3NTHbQimWTOn/Knyufy166fSRRkqu4py3ryh0Xn6nqMvbQZldyDFpzsCGl8tPaf9cGlj9s9t393VGDmBAAiAAAi0JFDzqNe2OGfR/QZ9edt2fEBglgR4F0Upev0dKrV0Vza3lf0G/UHueuvn52+JzkLpde8cv74uj+tT9Amt/Fr5uCza9VPpjstblI+/vbusZ2qb+Vnafyx5a3xj6Vr70dpHTvseiw1QTxAAARDoCQFdqEsxU3r7rCeOPrydu90m24krusEEt5XmY9nDycfyz+yJpG2o+Vj/fLNVpPOn5vFLXV94iyZbs5fbXleukTKMVj8+N7bFeuntqpTZ5ScZ+R5TZpfrke1Jo+qsmL4w8TMOpfPE8h36fkJfX3T7wtwXxpqQ0dKbVjwkvD6lTFhI82fW5fN5uPJrQrJpPXF8lYDGV0t3ufntR2ufWjrsBAIgAAIgsHQEdKHON4NX6es86qUYt2EF7Gl3H+lx9wUGH1MKaSui6fG6Ffn8M4spFlZOnPDNxOXlRJdL43xS1w/d4DSx55sklb/PwnklWWjHboLyiYTPpXxEDaEe6BeaqAilM99n6Mte9Nj5bEMpdKTH3QsNsBM8r+0b9tKTvSdhLzK9SfeepnzcXlLX19Jl+WTbDfQB8wP6I0Jfmlg0/1hNiGvp5Xham4hq9tfad34NcCQIgAAIgMCcCASFurw2e74nIroUyupNPCLUzduFyLExlC4sQXr1SvEw8aizUNpteH0pzHNueLkiJUeIu2s7Mch19ScKMkbYF4JzMnovLhPz9rrCh9LF0wp3WOUJB9vwtGhPnEcqhMW3uXtCI9dtcB7TCNpQ+bXyadfX0h2UlEjP7eO9aETzLSQ/NQx9Dvx1Dtq4pKXzRULtJ9f+sh1ooV7zRYirgQAIgAAIeAR0j3qukJXHTSnUbXiMnBiEbkbaTUymN/Wmp0RKjlB33nOekPC1P6Ov8/D6HDk/t3APoS/1jqnZTksP2ctfb6G1JS6VvA63R15oKe017RqOWNiOXA/il0+7vpbuBF6sTTor5HDBUDo9AY2vlu63S1eSHPv7pdb60fS1xJkgAAIgAAIdEFgeoc4xxSxwXSiBCwtp6lF3NzF+awV/mno7UzfJVOgLX8s9KWCBzrHGFG9feSIgF/PJ42X4RQdG7X0W04S9+JWOhcakYsDZPvJtJzKUxuUfWpDZdKIVq58/MQ1NeLXrp9L9tzEhRn0xXUUT4lp6qn+k7J/TvhdDBFcFARAAARCIEEi+nnGyKLM8OfRoV3rAU49+XZqNbaeYdLuQVC5KZU86xcVy7Dp/7HEkeCex7BS3rl1/UkcXZx56tV6iKWTlH1tMyvnKOPyyvBWGfKPk0JeXyjoi9CVsjWnCXmRO0kb+60W11x9q6SI8qxbDnjvMpOrX9vqx8nnxya6olSdYodAhuRA6t344LkxA46ulu1xT7Udrn1r7gu1AAARAAASWisAwNzzSPFJLZQIUBgRAAARAAARAAARAAATqBIYl1D2PlP9EAA0ABEAABEAABEAABEAABPpCYFhCvS/UUU4QAAEQAAEQAAEQAAEQUAiMQ6hH4nMlm9or1NB0QAAEQAAEQAAEQAAEQGCBBMYh1BcIGJcGARAAARAAARAAARAAgWkIQKhPQw3ngAAIgAAIgAAIgAAIgMCMCUCozxgwsgcBEAABEAABEAABEACBaQhAqE9DDeeAAAiAAAiAAAiAAAiAwIwJHAr1X67YTYjkx2461OWumeVGRHwNu9lRbNdQfwfFGUPoRfblqyenfuUkbZJiN4/ijaXeUmrcEX/eQMpuCtR0585yV9qV70XOT6VrG7rwhlOUb7QNltuw22vH2qhWPq1BtSl/aUdb/lD/TNVf1N0VsVJH3iznPH3P0Vfu0KrVB+l5BBrwtX0nNv5q7S+WHlhUj0X0eabDUSAAAiCwKAJVj7q/ZXlHgq1WudDW6Isi0Kfr8s6mr9KXdj2d6qPt+DlVpspJXOYmQt21jeuReqbSS4EyYcTC6DR93cQktL36rkj3N8ri3/kjJzZa+TSGqfP9fuHbK5TO13MTXu183xaxfogNwzQrtkvX+Lp2ukGX8R0lWvtLpXN/eD2QZ7va4GwQAAEQAIEZEkgLdX9gF9tTc5kqHp/SW8ceOvbc2nT2poaEZUIgOK9+1JspPYacP3/cNYQnyV4/5bUPQXVeaz6PPtYD7XuE/es7ESrqPxFOLj+Zh+/x5PNzhay7gW8VXuEa3yu/4j0AAAvpSURBVJR9uEIl94p95LVTTzycR568fCsvCfvK8/3rs/eeyppdP01IaOmhiUhqouAfzxPTN+jrJkKh9t9G6DQtvzah1YS5dn6MjSYkZzggjSLrFF9ug+/Q9xP6+m1Naz9t00cBH5UEARAAgX4RiAv1UnQZ6XHkm4j08Pg3Br7x81eKnZAY0ARELN3dxJyHs3yUWwkH4TI5oeUfn2ObUqzaSQjXw304T9+j7RixWI3dJKX48+vlPMBNhDqXx01M5PX575p9nNg+UzKKPTGJ8S/DQibX98/3+XB6k4kI82AvIp0TnIjkpPPEwPFx9V0JGN5n5/j5Qv3tsg6crl1fa1855zND9+GfQ6FnblIYCo3IOZ/zT4k6CHXNku3SY3y5fz1T2jxkH639aOle6EvUkdKudjgbBEAABECgQwI1oe68rXyNSjxzYtOgSZxjKDQj5OWcVqhrYRS+R5c92XKikQMudhPNEeKufE4ssUj1yyxjpGNPHGLlTPElD9zKe+ETJ/bxbZESDJyVC6lw2abOD+UVEsMpGzhhLZ+E8DVFaId9yhFL57zLPGz7ZSEbmijEyhXyqEuhrpVPa1/a+X6oDtc9FgLk2uNngk+T80P90pUfQl2zZDCd48pDn1oceIyveNrm8vHberL9a+3LL5zmgZ+KAk4CARAAARDokkDcox7y9mpCecFC3S7AkoslU2IkRrGNUHdCj8Uds2AR5TxkoZukW7jXxKPuhxLJOmr26YFQtwsZJQ//iUQqPWTTUFx2zCaBJzaV8AP3RCFWPq1nauf7EwVNMIeeqMgnAqnzU21Fu65WT6SnCeTwDYlorf1o6Tn9A7YDARAAARBYKgLpGHX5KJaLrS0unUfoi+9BZFHMXnOO6eSfvXj1zjzqXP9U6Aunu0fPXMZP6cthGNKj72LMXeiOOz73zTrOU+/X0QlHzT6zFOohPk1DX1wejlnI8y0Zah57X3j7fHwenB9PnpwHPxQ61eT6MWEUq59fHv/Jk99+uHz8ce1HO9+VR/Ok5gjJpRrGelaYHL4xG2ntL5XOafJtPv743jOMKC4IgAAIjIFA8PWMlQWUMoyDifiLKWUcrbtJRBY7ao+GtXRrEP/6Ugj7YQ8kmO2jYheTrVg0dP3a6wxji0k5by9m3nr45bXLiUxlMWbuW1zcwlR6YjBZcBsKnYnZR7Lhpw708RfuJvlnnG/jnmmyNHn9YVnWRq9oFHkEY2hT6eX6Aq5baKFtKDSoZl9Zz9BiZK182qihne8vNg6sCZm0n6avZ3Rliz1pCoVeTPN6TY3BWNNz+co24LdBrf1o6an2NVa7oN4gAAIgsMQEut3wKBT6ssSVR9FAAARAAARAAARAAARAYFkJdCfU/UWSuXHXy0oG5QIBEAABEAABEAABEACBBRLoTqgvsBK4NAiAAAiAAAiAAAiAAAgMjQCE+tAsivqAAAiAAAiAAAiAAAgMggCE+iDMiEqAAAiAAAiAAAiAAAgMjQCE+tAsivqAAAiAAAiAAAiAAAgMggCE+iDMiEqAAAiAAAiAAAiAAAgMjQCE+tAsivqAAAiAAAiAAAiAAAgMgkBwwyNXs4PQhiptqi03pAltJuPy1nbYbFMGnLu8BNzOoOeoiHIHRVnicpt03lTpILAZjL+p0cFKoLq8M6TclMu9SrTcsEqeUTs/dX2NbNv6cf7yNah+/UXapA/LY2ZdP63+Y0hP2Yfrr204pLUvLX0MjFFHEAABEBgRgapH3d+yfFaC2b/OiICjqhkEYlus+3/n3/nzVvlvbNt1X+jz79fpe9sri3a+dv2MqtlDpq1faHt4uTMvp8v9C/x+Nq/65XIY2nGafcoJonG7EbM9Tov2q7UvLX1oPFEfEAABEAABkxbq/o3d3yJeetxLTxJ7OVd+VZANbgHPCTGhnuNxlx4p3t6cP+7GJzyl9vopr33I+GXe9jz6cD1sHaT48a/v0kT9zWaZuctP5uF71Ph8bA5VtUZMyPLE8Q36OoEdap+vU/rPIj1bE6paunb93AFl2vr5+XM/+pS+bqLip/vCfV71y+Uw9ON8+4TsJW2ktS8tfeg8UT8QAAEQGCGBuFAvRbmRHju+UUgR5N/4+abDXymmfLHAkDWPeiydr/+OECblo/yDM554c0LOPz7HwOVkwYb9cD3ch/PkurhJAf/dMWKhHRNB8ubq18t52CDUq5ZpImTfplMjoSu1iSLnu1Ecb0NneALl2VOGztTSQ0JJXj+nffEx09ZP5i/bXui6ofbohb7MrH65HIZ8XMg+vkedxgPrDHDhWVr70tKHzBN1AwEQAIGREqgJdecNZx4Hv6T/ibACP/7XMZvcaHwhyweEvEjTCvWQ6A+IFxZhtvwsxOREI8fIMRGVI8Rd+fhf/rCA9MssY1h9oZhTvjEc04WQZU6+zZwwkk9auC26JyA+W//8roRS2/ppIj3W7+ZVvzG00VQdU/Yp26Adn9gZIJ+oae1LSx87d9QfBEAABAZIIO5RD3l7NaG8YKG+cuBNLrTQgJBB2wh1dyNlLyuz+Iy+z9A3FIohFxbCo161RErIek9UTCrUhXOVbbZciOeHMlWeAPltwg9NaHr9Jm0s8MSoVj+uQ6xNyWtpfdUdO4v6DXCgzK5Srn2m4Z/TPrILigNBAARAAAT6QCAdo+7fdLTFpfMIfWHx6zygMjznE/o7C2QvXr0zj7oTfbHQF053oRVcRo4dpjeLVK4vF5vJ42Mx1X1oQbMoY2qydF7Y3xcuzFe+LSYkmkIL/sQ6g+T5bnIl1iBUQrFyWUxbP7//xSaisac/Gp+u6pfLYWjH5drH1TskvFPtG/YZWotBfUAABEBAJRB8PWNlAaUM4+Ds/MWUnO6EphNB8tV3Qtiyxzv0caEzWro917++DG3xHyuTYLYxoDKGPYEkdP1K+E/o+tIb7sXMWw+/vHY5kVl5qShEdLGtaraBHiBs62pYW8wrbRxaLOwv1vUnQWJBdJC/dr52/ZRp2tTPiy+f8JHhae6PqSdJs6zfQJtlVrVy7CMXy8fC3rT2paVnFRYHgQAIgAAI9IVAtxsehUJf+kIC5QQBEAABEAABEAABEACBJSLQnVD3F0ki7nqJzIyigAAIgAAIgAAIgAAI9I1Ad0K9bzVHeUEABEAABEAABEAABEBgiQmMQ6hH4kelXYJbzS+x4VA0EAABEAABEAABEACBYRMYh1Aftg1ROxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESgFAfoFFRJRAAARAAARAAARAAgf4TgFDvvw1RAxAAARAAARAAARAAgQESYKH+/6MXLdSz3ziEAAAAAElFTkSuQmCC" width="635" /></span><span style="font-family: inherit;"><span><br /><br /></span></span></p><h4 style="text-align: left;"><span style="font-family: inherit;"><span></span></span></h4><p style="text-align: left;"><span style="font-family: inherit;"><span><br /> </span></span><span style="color: #444444; font-family: courier;"></span></p></div><p style="text-align: left;"><br /></p><p style="text-align: left;">And the InnoDB page like this <br /></p></div><div style="text-align: left;"><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"></p><p style="text-align: left;"><img alt="" src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAuYAAADyCAYAAAABQQD3AAAgAElEQVR4Xu2dT6wkxZ3n4x16JMwB5JleadvCfcGgQa052ALZbvY4GuH2oQ+zfWgJgbTS+oabw+sTRzSHfgfjvoG0YhHSIrUs0Zf2aDXH5Y9YND54gZWZvWBw74GxhEdafODwNn5RGfV+GRUZv8jKrKqsrE9ZZV51ZEb84hORkd/45S8yjr766qtTxwcCEIAABCAAAQhAAAIQ2CmBo1P/2akFFA4BCEAAAhCAAAQgAAEIOIT5gE5wdHRUPJs5zwC4nDp7Alw/5SaGz+wvgUEVPPT+cej1H9R5Kk6GbwWkwiEWv1LuCPMB7C3wCPMBcDl19gS4fhDms+/kG6zgoV8/h17/DXatkDV8hxG2+CHMh/HtPNsCjzDfEHiynQUBrh+E+Sw68o4qcejXz6HXf9PdDr7DCFv8EObD+CLMN8SPbA+bgDVwHfrEFj6HfX1YtT/0/nHo9bf6x9B0+A4jaPEbIMzvuw9v33In79xf5nHh6evu5gtX3YUeNn94+5p7/XPnHrz8iju52ufMHoX4Q98+PnYff+PcpZsnrqqY+2+741sfu2/OXXI3T/rVaZxHPQP5fnjbXTt5Zwnp4vXN8tWtcf/tY3fjrc/CPz19fMe98GS/ttr+0R+629dOXKB18bq749t7458dts86dduv60e1Z6zshtp1W+NX2mbWwG5PXO67t/34/ZYav89966K7cP6yO9lG/1+nE659zg6u78TW/bp+5H55zTVDuKrJBff09Zvuhaob6NqNNcqJg64PufffeMst7mD+Hnb1affO3bN7adU9bcvje65/ffj2bff6W++4f23q0dJnu9I3CVtP1x3f+ZF7P95/g60X3fVXKnXawN4y1fG7VK1ijPmHt4+9oP7aC+rnvaB+0t33HfHGye98fo970C+4MbTY28e33Rc+xx+eDMxPLpJ7f3Tu2z91d3qoRGm0xWn9xeWggcHXOXQYj/MvHj/2k50n3R+82D359VfOPXzFvdJjoiAi+ZafkZy7dHOjE5+0I0m5b/rGe+CHJ3sgzMV6f/M+ft//97vuhS0Kk121T6/xbA+vn1C/5ubz9Vefucf/+mH33Wf7DfajjT+9YNcdPHR8CdenHxce8OOCCK37H77tbr3+sTv34KXRhPm0+O3m+g6tuZfXj0xm7rk/um+7n4b7+WIi9/Gfz7lLPa+juh497lGjXB/6/rVmG25lfM/Z5v/tuNE8Jy98x7fdDfdr7wB9WDlAd6dvFtfin/299tmllvH96/hN92/uAfe3Xu9tzkU7bj9bNzerf64nzOWGt1Bd7kQJ3dgJwz3xtwtPuswuf/T+Ne9ZXxS19NyWZpQrs6ozM6tmq0mtZPb/3tfOPfK8FtjSEbzH6LPo8T/vzrsv/f+CkQuv6YBZpQW+6NFqyvVqunWTjBfSA3950f3zO4v5/Dn/9Q8CGrh+9plMYrIDQ4vvYnb61AdnXu46r/GqR9+de8hdPP+wu+yfSkh+b378lfvfv/2T8+hX7fNC4Pbrb7l34nTe03/6+vNeJPhbgPK4d9dPLm7v5Y5ujYfOO/en0HqLPvbUH9ztWz797IGOa3sM/PmtdN/+f/W4e/xBW5hLO6iHEe7if/iB++x//PNZr5P+c+20s366e+baJ33ikL1+7qf2u3b9mhtp2yP6kPeIXuktvLqun/SJmW7/4FTb1fXTAF5ODv/yEff5v/7Zz7meVZPTQv99/sfu3X8485jp9lqOP6ZHbNUjfd73i+f9uPKdqv5dGpqHL75a9Dt/ZV563t3019zqjbDbfnG66Gvg/N/8wJ3+9p8XnrmLfgx68RF3R3kcs/zK1ZPOs+LRj/zM8mUMXLk+kuvbuH6K9evpKOq8/yRPLHT9Ap6dXj+pMBdzlLPFdY/fi6YdOD4PHL8G3X8D+sSxlIpfc/xdUMiN72dPI77lHvJ3xz/Fa+HC0+76zRfqnuir6yffv5ILLKfZdti/xLG7mDc0jjuZSLyvxujB12fuiaC6/018/C4Nj90e8xRizEX9+4unv2h7TDvOKc0ox/G4rA4wYm770U9zk/7N/3POC5ezUAY593X3uXvQXe75aGXIwNDJJOF749deiD74fXfsL2b3K3mC8Y1/gtH2jNfktQghWsxYv/Az1ponFIsL6xt/YXmP25P+fLmQbpyxWgh9Ecrevldy9t1394NovuAu+NPT2XsQp4X6hfb7+huvK266q9/x7XnLewR88z18ZRGy8/bt23727dzfvtDMvsW+N71H/AEvvL0H4fax9wZ9471Bnt2TF7rav/vyWOHa8lCIdCjXL+bc1T65G4MeuMr1u9rcEM6El3jtbixixnqG6uSvH6v9F0+7d3P9NLdE5YH5e395/JP30Dy09NDU2F8z/nS138qj5XCj8V6icwsvkTeu2L8t3TpkfIl8ZGL1up+5Lh91/8CHIj7rw/Z821n2L5pXwuV+4/940F34gRf4f9dY/e+80A95rP/Ec1j5fjj8Rfn6tq6fhbYs189qo0V6zf1Hhot2/1hMlHZ5/WQ85t6R9fE33mMewkHL49uw8Xn4+DX0+tDOEd3O0bFY1X+kWTueWIuYDre3v47XXL/775lN+f6V9s28HTvsX8lEQcbjhS5fPNWs4lu4Pp3XH9rx0HX/m+r4XRpbOoV5jdibjDAP3uH3vFf5EXfTP5ILA17OI93hpa6ajWYoDhkYtsM3GQi6JlvZHmI/FraEpbTB7VveY6482t/yTu/vN081iuc/9UGI/289UdD2S3qHxy7EtB07d0fPzrv6RPHOe9+L+ze92HvAC60X/DiQrGEw6hezXkuYW/ULj54Tj5Uv8Lz3yDwfJiJ1kiIclbt+eoT97OL6iaLq7FGup9Hy0Nj9V/KoEZadT6QyTxT1zSc8UUoelbc8RkYTDRlfclmHUMQ4cXvxNPtENL15ys2uZHMNv2w1O57IVpefs0uP71K/0vgQQzF7jYkdDdZ1/zH6Rwzj3tn1EyYFzbqbZdV8jPlxdMQUxu/vLNZnrT0+jzB+Db0+zPtPTf8pCvPVNW8rZdYM09nxWZ0Y+v2v3ZffPOjvrU3bqeTd9S81+ZcnlP9V9ZdCxMQiJr0JbS5en3X3v6mO32sJ83Cz7hhY4uOJv/+i7sazcY/5Hgrz7MRB/Ccq5n0Mvmc31r9zP373v1d7y2M8djtGrN2VysLch5mEJxF/4S77gf6qV4rp8ebAWBr4r/r8S/Hi1o27Msa8FSrx6WINwyK0K3oiuus3SJhb9Vtxl3jv1j+96W5I3NDDPT3mBWFeav9owq4G/jTcaInk6WO/zsRlYhxXh8IaYTnVgd1e/JnWV3neXvGhKDXCcV+F+Yk8MatYT3LwwlzHmOv+YoxvljDfwvi1UWHew/5qj2wj4nuvy7KEuXmN5sJ87RnBUL5L54l3kH3zb//X/fuHzqn1aHWOE8sxsKyFfzTfdf+b6vi9njAPIrG9+PPDZHGiPKqNiw5f/M4H7tbJWyF++0LydpCyMD+bVT71wW13663F4tKl59vuP41IWh1gphzKItWyFn8K3xqPW4lvDF/5+Kvfu88uftc9fEnH4JbhpquZ7/tHsW/6mO3f+4D3x73XuzhxCB7v5imGhOH84W33po83/73fLFXOFW1redxb7SdhOEkoS9o/27WRC39YKMsiv8UA8vn9/+Ue8rH1yzf+BC9FuX41wrx0/ZTrt/BGvOdDfR5pvCTiEb3lZ83nlpOHqoun8/qx2n+x9GRXj0q7FxfFUC0/M2y9DSrtv2K/7mNd40/1jTcTylJz/Xa10rAb46qwSp0ttaEkZY/5+uP3sPLt69u6frRwaK9NqL1u4nFzCWVR9a4Y34aNz8PHr2HXh33/qeo/jdjOvXxh26Es+V67q/F5effz989b7jf3v3IPX/i+f/vd2aLPKr6FSUft/W+q4/fawjy3OKe4uK61OO8/uUf+239ZvJ5u5XP2qCKICb/Kbhnt4B/FSzx1r0fxPv/qxZ/p4r8dLo4Qvl2vowzxqepdVunrnC5er+Mbbz7X1nqbzuriCh8r4a57IfjUF78w7HvFSaiTrsP5iw+5Lz+TZaIX3JUrzt27F1d1rr6uahHnJ17x9uLPv/KTiweXk4vM4j4fK3Ph+z4W1quuC7nFYeni34p7cIhFXKwsbr3xJ41R1PW7/so19/sb6WPiWFjT/1P70sWtPkx5ZfGlqt8HPr73vS9/4z77ern0VlaHjnv9JIvXYvvLE5Dw2cX1s/IYVBY3p7zl3150p79qvy4wtb97/Mk95k/ar7B4US/+lLNWr1/71abDhMfihvw7v+zsy7Ol44uFm8uFjaXFl93114vzh43fA8s3r++nCtePj1FfCeNYtO+oLx+Y8OLP1dcltl9hVx7fJE44CSXw41ef8Xno+DXk+kjrlr8+S+Nvd/+JoRhfhDVSfuHn6Zfus7P3GY44PkfHSLzPZF5BuIvxObmndjsOS/dv+/qUp53d97/pj98DhLnvmOIlX3nZqYpDqxA2Wzmk6lVHq49Pdvc6oa1QWRQyxuPaLZrbVZT0xdf9698e3PJrISdQ9c2bUHX9rJpxENfP5ulnSxgiPHZk8uEWy/UTtMI2x+epXx+932tfunroX3s3tlj9c5Aw3ycapQthJR5VXncnC4R2usHQZumuxuBu76X+Y9Us9eqss8HVWLbMPZ/eN5IB3hhhaQ1c/WOo59VC8Nmv9jzE62eX4/OUr4+VpxEjbH52iP1rv0aAtrVW/zwYYb7tRrTAH7qw2HZ7UN5+EeD6KbcXfParP2/b2kPvH4de/033N/gOI2zxQ5gP49t5tgUeYb4h8GQ7CwJcPwjzWXTkHVXi0K+fQ6//prsdfIcRtvgVhblPPB1WPGdDAAIQ2D8CTJyn3WZDbmzTrhnWQQACEOgm4F9ehzCng0AAAodHAGE+7TZHmE+7fbAOAhDYDAGE+Wa4kisEIDBxAgjzaTcQwnza7YN1EIDAZgggzDfDlVwhAIGJE0CYT7uBEObTbh+sgwAENkMAYb4ZruQKAQhMnMD//OV/dH5vs9XP08etjaQ2Uo2VTZKaUuIGUX+47a7ljOu1gdTqBj7nvnXRXTh/2Z2c+N2rJv5BmE+8gTAPApsm8GNfwDX/9RsSunv+e6OjwP/s//3Yv4L3UR+b/X/839/rYdi/LM6Tz+kvO8oYkv8rPv+fN/n/o//vTzK2JfkjzHu0H4dCAALzIRA95t070224rummIc026F+fe8Q973fnlN1D3/zCuQd+eOL8RrZOdtm88frnzj142d2pENZSrzf9hlwP+A25Xrh6wZ//trv1+sfu3IOXlsJcds/zRbgfLncD7VfnoeeXSkOY92sLjobAbAmIcH2iQzR74Rs+d/z3vZ4EvCh3J/77mv/KJOAN9TtmNST/eG6cUKS/pYxM/gjznu3I4RCAwDwIlIS53rL7nK/uN7HKzZb2IppvNDsi59K9jvaf0pbzPjmzm5+eJLx4+osVYX7r3h/duW//1J2IUjc+i7y+du7S8+7m1SfdBX18l8feH7Pckt4L+duvv+XeiduJu/Pu6evPe5Evs4S33fGNt9xnGRvk/L//4oyP/P7R+9eWTycuXn/FnfiJQo7PuW895D36V8LEAWFutTDpEDgQAl3CXMT0S/6b80IrNEfNuwdPRfGWPiKSP/FfEery2UT+km8U6h35I8wPpF9TTQhAoE3A8pgHcf7rL72H+vvu+OYLzv3Kbzn++TfeYX0zCEsrfWWnvvsfutu33nd/Pvdd96wXnhdy22yrf3vlkTtL8X9meZ/de+977X/Lvf7OfRe19YUfXHc3n/VlNyq97PG+7+7fl5IvhONlJ2E/L3Df/umd4MGXT+n84LFXHn+ZiBy//2fnvvvskp+eOEi6fiKAMOeKhQAEAoEuYS7/LlF5PnRlGcbynP+deM6rhHn0mOswmLHyb8JlTtNQlo78Eeb0ewhA4CAJ1AhzS1h2pj/1gTteJLa82x/ePnYLbXrirkoc+ULpnsW0J8K8lX8Q9q+7z92D7vJNf37LBW43YS4UpijMvVf89i3vMQ/ifPH51nnnvv/8OMLcS313+/jEvaPc7ud9DP3zfhL0pK8bwtxuU46AwEEQ6BLmTfx2KzZcvN5dsehdsHKiXI4dK3/JK3rHP1X2deSPMD+IXk0lIQCBlMAUhXkplEXsX/FC92pWL4Sv3XN/dN92P73jxa8/t1uYy7EyCfgLd/n4prvqlXKu7CEe85bp3jV//5/edDckbubhRQw9wrxX43IwBOZLoCDMw8JQ7eX+tf/9sv/WxpuLkH/Mf3PhMJI2NH/dKukEoCN/hPl8uzI1gwAECgQ2Ksy9O7t3KEvwiHvhfM4L58ziT796092+ceJ+5+v0uI/bLoeZrwrrEBeeePG1jU99cNvdeivk7m6+8oj7xa33fGz9I+6mhPH84W33po83/72/Yzze8pgfO7++1F3yHvzW+V74ewA+xv0bd84vPn3xOx+4WydvOR8Y5C40MeZS9ntf+xKe94tTRfj7pwU6hh5hzuULAQgEAqXFn7kFnMlbWTpDWUTEJx7sVoy5lD0kf32u5CXlyUdPAjL5I8zp9xCAwEESKL0uMY3vfvrq0+6du2fvVrzwN15I/vYsxiNNXyxw9NrUx3i/pWJBzl+87t+4ctU9ue7rEn1LnS2eLDXbQpj/zv3Ji+Hl0lV/8tPuWL2BJYhh/1rGZU3U6xj1Algp6fzFh9yXn/nFpD7m/Pori1Ca0vlhInHLh6rEzB/ycTB/Emm+qMOP3/2Ve+/L37jPvpY8m48qH2F+kJcllYbAGQEvZI+eaQNZeR1i44XuHWPuzzt6dxX26c/8v8XFn5K8bv7xXL84NdZhJca8I3+EORcBBCBwkATYYGjazY4wn3b7YB0EILAZAgjzzXAlVwhAYOIEEObTbiCE+bTbB+sgAIHNEECYb4YruUIAAhMngDCfdgMhzKfdPlgHAQhshgDCfDNcyRUCEJg4AYT5tBsIYT7t9sE6CEBgMwQQ5pvhSq4QgAAEIAABCEAAAhDoRQBh3gsXB0MAAhCAAAQgAAEIQGAzBBDmm+FKrhCAAAQgAAEIQAACEOhFAGHeCxcHQwACEIAABCAAAQhAYDMEjvyOSKenv/SZ3/Bfvz3o0c8XBWVfhL4ZG8gVAhAYm0CyecKpTMH9J+6AFq7xy/7/rG2L/Y5rR682Y0IcJ8a2dZf5+V3XZGMK/VmOh9u0K92Bbptl58qaQ//JbCASNid5LtPvLf7JRicrm5BY6btuT8qHAAT2hsCR3yL01Mn2pXEL02aXo+XvvakKhkIAAisEZLvfZHvisMVw+m8WOj9pDx+ZwM/sI5OVOHEJVROR9nJGvM2s3lXV2ff+I1t5+x1Yl1tgr3N/s86x0qtAcxAEIACBBYGFMP/U//VYM3ilg4wMbMdnXiXtTQo3NPFASEbe6yReBPGutbZMVduZynF44ul6ENgiARGZd/03bjEs1/MT/hsFduH6blmZE+bNE7al97DxGrY8zntw/a8Ic6nrJw0zXScZ5/wTxerxTXlRAxOf5/Lpg4ybMjmqeSKh84nnSePU8Jfj9Pn/2JRbOzHb9/6TCvOGW+jbcg1Y/K0twa10q/8P6V+x/X2bLrf81v0jXsBp/5F/j08NLPu2OFRRFAQgsCCwEObiHXrJf+W/8nmjGbwjJbl44yNvPVA3jwrDI/FrzU1L/pa8ohiQ43+icEtekq7/jdaAAAQ2QyC93nLe4K7rW1vU5THXIlaOT4X/Hlz/K8JcvMQ63KERb8GpEMdIqauMiVb9Um45oSh5dfFNbUkdJxb/XPne0VL9xGTf+0+OtyXW0yvR8oiX0q3+0VwzwaG1bv+6ovprGpKTTqz0Pbum/25mVCJXCECgQOBMmMtBIsjlhqSFeTqjlhn5if+KB04PSHqwizeLj7xYfzdfeuvRMU0EAQhsjkAUd/Ea197S0vU9VJhnYnxjllO6/nXcvdi3Ej+cTjZiJWrrp8NBUqEd88oJ8y4nhp5cWcJc8ldx9J0x1qXet8/9Z5fCvLZ/DOlfVvuXwtZq7dvcyETOEIBAhsCZMJfZc7w5yAy8uXkHb5IPUVk+CtcDgSXMRbyvE89KU0EAAuMRiDd+yTGGaDS5F6/vocK8EYXV3tnxatwrpxWPeXp2l3CqrV8cM+V4He9s8R1LmKuJhDzZdGp8rwK1z/2n1jteWkMxxGNec/8b0r+GCPPa/lvVSTgIAhAYi0BbmMcLVf4rwjwOSPGxbhOPWu0xF2FurXYfqybkAwEIdBMQgRCvay3U4lMymZin17clHCVdC5oY8qHf3rIH1/8gYV5bv8g/90aQlKPmXhPKIsereOlWjL+cH59wynFdoTTWtbOv/ad28eemhHlN/ygJc+t8S5in58d7euwTVv5WvyAdAhAYnUB4XaLkuhzM4805Pu5uFpiEYyQG7tOzWPIYphL+3ceUhzg5uSn7T1gkFT3t6eITGeSJMR+9MckQAp0ECjHirVekqutbYqjTMI+Y/zIURYXChDCJe821n4jz1uK0KV3/yesS01dI5upfelVeYJCrn/B/bHXcM/kK8K7Fn5Jm8RdbpI7PLFpurVAWOXEf+08mVCOtv8k/9zpNud/F+5eVnms/1T8G9S99b5Z7rdx3c682TfuPnqgZ9jGiQgAC2yfABkPbZ06JEIAABCAAAQhAAAIQWCGAMKdTQAACEIAABCAAAQhAYAIEEOYTaARMgAAEIAABCEAAAhCAAMKcPgABCEAAAhCAAAQgAIEJEECYT6ARMAECEIAABCAAAQhAAAIIc/oABCAAAQhAAAIQgAAEJkAAYT6BRsAECEAAAhCAAAQgAAEIIMzpAxCAAAQgAAEIQAACEJgAgSO/+cTp0aNtS1o7x9Uaua87iKnNF2JVw4ZJ29gAydoAo9lJUTfBWhuE6A0mmk2i0q3Za5u5dVyzwcXKhitrZZY5qU/++9r/YrU3aX/a/mnfttKt9rTOt9Kt/DedbtlnpVv2Wedb6Vb+pJcJyOZIV/zmO/4+t9a9Db4QgAAEtkggeMxXtqQWkfCy/8o23YfyKW3JvEkG1pbRuS2Xj71BcWdWy7a0LdMtma3zrfTUPuv4vulj5C877dXy6mtfzfG7LD/u5Bu3gpf2v+S/rzWGW+lW/azzrXQr/02nW/ZZ6ZZ91vlWupU/6WUC6Xb38ls+sf/DDwIQgMDECOSFuRZDjTcneBr8R7bvDl7bKHSUVzf1RgTBL8fKeeKt8NsGy5bB6fnOC83otV/xaJTKVx7n5Vba0cuqbayBnhPmMS/vZW5taR3rbqXHcktbaqfCXM7RtuSE+VV/TI1HX0TYS8axjTDo5C/2pPbLv0Wh19i63NY95a62DJfTej+NsPIv9D/Zrvzo3dXG7+Xhr+j/nf23pvyS/TX9x+rbuYmNnihY6bvO3yp/aLpVfyvdKt8630q38ie9TCDnmLDGRJhCAAIQ2CGBvDCXG3cUXmJcIx6CqBJPevxoj3pO2DbCJIjma42ol79lYLzrv9FrIeIt5iUDqU6zyk89InL8Oh7KLo958xh0ySMNObDSU5bRY60mNi4V2lqsN+Jf95HlJMTqODk2uXNK/NP20G0qbRYnJ9Ku8jvlI7/1JKJmsqBttPKPx5aeeKzTH7QNVv+3+m9N+V32W/2npg/IExY1kQqTa7nym2tLJhad6bvO3yp/aHrqsY79DT5DyU7j/K4nhrt8gjYNMlgBAQhMlMBSmLeEn/dstx711Qi8DmHu3vB5ySCYiM1ljHPqURWP60nP8rXwqbE11xglYf6JsifNP+fRfsIff8N/u0SovlnkPOZdrMTuKOz1xKmrc9WwsPhbotKof85jLeYuhaF1YZTy1+duWJi72KapvRY/Od5iKMd0XT85717fUDM1uQuTa7ketTCx0ivaaPnEZBP5W+UPTbfqb6Vb5VvnW+lW/qR3E0CY0zsgAIE9I5D3mKeVqBF4awrzEO6iJwK5R7tW+Tq9RgRNXZhrljkeuX/L1Sn1zmeOMflbPC3hbJ1vXTBW/vH8HQlzk98UhHnK2GoTK91qM+t8K93Kf9Ppln1WumWfdb6VbuVP+hmBzBM/M7wPfhCAAAR2SGC3wvwjX3PxqEfvb3ys3NdjHsXPvYakeKv7fjbhMY92ae+2FcqSpqfCNIaSpE81uuqbhpboxZ81/EvnSyiSJZyHvm3Eyr9WmHeFctT0k66JYfr0oqv/6nCUNFTCsn9oKEtav1yomD6mI10mIPIxn3SsmX9NM2zlmDXtPxg+W2mEEQuRa9SHUYYnmPKx2nfEoskKAhCAwDoEVl6XmMYvxxuOzlx7uHPp8Qa+vFnJ4+27zcJPvYhUxKUPEWg9Bv9UxaL7mGWr/KVdMQ44xobW0lALG+MpywWK+hGz2Oo/sng11E/VI/zOpaubQXbx6BqvS1yW3WfyoeuRhgqlj9ET/vFm1rI/TpwsPl31F7FZs3i1Iv9S/0v7RmAn9a8JA2pONvtfDT+9wDMpv8r+0uJhq58Xyg6nWukJhxVhbp1vpVv2bzrdss9KnzufTfPfRv76GpVxu8/YuQ37KAMCEICAIjCfDYascBeaHQIQgAAEIAABCEAAAhMmsP/CPPF4V7+xZMKNgmkQgAAEIAABCEAAAodHYP+F+eG1GTWGAAQgAAEIQAACEJghAYT5DBuVKkEAAhCAAAQgAAEI7B8BhPn+tRkWQwACEIAABCAAAQjMkADCfIaNSpUgAAEIQAACEIAABPaPAMJ8/9oMiyEAAQhAAAIQgAAEZkgAYT7DRqVKEIAABCAAAQhAAAL7R+DIb3DS7Om3MH65uc5YddEbdJQ2dxi6Q+S69pY2GFo3z9rz1thgqO8GOcEUvUGNbPbkNxFyn/iv7Nw55BN3sazdhbRvWX3y31X/6VunruM3ZX/c+fCKL1h2xs1triK7q/r0o0ebjbPSY6z0sRjsIh/47IL6dsuccyO3PXYAACAASURBVP/dLklKgwAEtkDgyG+pvhDmestiEW5j747WteX9FipZVcSu7JONka76b9wJM27z/r3G6tyW9Mc+LaZblRPB97L/+l1UwyfmH3fvtM630lP7rOP7po+Rv+w0Wsurr301x++6fLGxawOu9N/lt3zipM1Kr6n/PhwDn31opf42Hkr/7U+GMyAAgYkSWBXmItxe8t9EKIo3TT4tj7oXHNHLdvTzJr1ry/Mu4VvjUU+3JJei4rbqMvB6obq0b90tl3P2RY+t9zK3tqRXolnqLUyy6bHRS1uqp8JcztG25IS5FvKljpW2Ze7YGn4l/o2trfbXIriZCGT7T81FYeVf6j+ZJxKhD/fx8Dd1P5V+5T+hvaWPxzqW+NWUv07/X2eS0SU8cxM3ff1b6TVtuA/HwGcfWqm/jYfSf/uT4QwIQGCiBNrCPOdNlYEtinSpRCr2xBsoX+2VzXkILY90V7qUf9d/owevETutHT7FpugRTo+vBd9VfvMYdDkRSEMOrHRhEScRkd8bibBLhbYW683kQFejenfTLrGRMinxs/jHycvlpg1SPlb/sdrHyj+eX+pfQz3WjXgOk1Lp5/Gjn0KU+l9N+V32W/3H4hfT+whP3T+7nrisMzmotXUXx8FnF9Q3X+ah9N/Nk6QECEBgSwSCMI/eTimz5U3s8PiF42TZqHxS4SD/lgs/WFeYW6Im9ciKN3OdMI2SMNfx2OkNPOfRfsLbIKFAXR5rfbPIecwTYd6KB4+TJy32uzpLjTC3+Fn8jfofvZs3btl/rI5eyl+fu2Fh7mKbpvZa/OI1YgnZnP01/cfihzCvI4Qwr+O0b0chzPetxbAXAgdPoO0xbx7Lt+JxLWG2Y2HuF6+2JxPrxiRPSZiXQlm6Jj65rpzGq2eOMflZ7W8JZ+t86xK08o/n70iYm/z2QZgnT6RaoWyZJyatdKv99iW9JMzhsy+tuGrnofTf/W0hLIcABBICqzHmInAe898YvmK9LWIboSx6MaoOt/nI2ymP3ZN488l4zKMo6xPKYi3+jKE8tXHSafv15Vc6X8KLLOFs9R/rkrTyrxXmsQ1iaEx84mOVL+ldoi19ehEntukTGz157Sp/V6Es8a0k8oRHPjkhc83/e1d6Db99OKbUxqX6W/z2oe5ztpH2mXPrUjcIzJLA8nWJrQVtIiTkEx+/p4v/JD0Kd/lbhEizADN9nZ94FHOfGMpgpUex0FpcqYWPisEOMcBexIcFejHm2Wo2Vbd46HKBq85bhLD/HL26+K9eDBh+59KVmMkuDs2ECrX4qYWBuhqh7Ji3VT9J1/VIQ31q+KXtH/lbfLrqr/tPyf6K/Kv6j15g2bU4ucOOXP6tcK8afoXyq+wvLR622j/Xv/Xi1bR/5PqWrmPfvmfZt+t0+Oy6BTZf/pz77+bpUQIEILBlAsM3GMqFsmy5EhQHAQhAAAIQgAAEIACBfScwTJg3r0sMHuPUC7fvZLAfAhCAAAQgAAEIQAACWyQwTJhv0VCKggAEIAABCEAAAhCAwJwJIMzn3LrUDQIQgAAEIAABCEBgbwggzPemqTAUAhCAAAQgAAEIQGDOBBDmc25d6gYBCEAAAhCAAAQgsDcEEOZ701QYCgEIQAACEIAABCAwZwII8zm3LnWDAAQgAAEIQAACENgbAkfuX9zp0aNte3tvYCOnD93hcRfIMhv4LDcX2oU9EygzbDHPqy8n0BIjmiC7il7xm2P563yta3tEUyaZFXwm2SyjGUX7joaSjCAAgc0TCB7zIMb0FuUisl/23/c2b8DOS0i3Qt/HCcbYEGXTqLjr69h5k992CaRbzctv+by2XTMmWxp8Jts0oxhG+46CkUwgAIHtEcgLcxGrnzQ372bLar0FfcujqrcbT7brXnpffVbBW+e3rZct7dPz3fEiXT4rHr1S+WpL+9PLzUSi2X652uubCnOfp3vJf3+iGqFrS/Rmg6WWzdFe7XXW5/9jI3prha/FX+x9Q/GT/LXtcmMq8U3P920kxyPMt3cRbrSkdJKd698bNWDimcNn4g000DzadyBATocABLZNIC/MxWP6XCN0xaJGfIcwD/Gkx4/2qKcCV45phHMQzde8ePy5F97ytwjfu/4bvXYiFmJeMpDqNKv81CMix/fx+Gq7G5HqTpRtKYt4jAjrLpGjbwYpl0Yo9xK+Jf5SlhbiOZtKfNP6SX5St9qJw7Z7LOX1I5ATJn4iR/s2GOHTrz/t29G07761GPZC4OAJLIW5JiGe7daj7pz4TdF1CHPx5gYRIHlcbUSk9sinHlvxNGth3Ahz94T/40ZHe2khXmOrzqbxsMd/atW9RnjHsuW/8pG6phODxrMuycGTryc9NV2wq07qiUGazTI0qcQ3l6+eeNTYxjHTJoAwKbcPfKbdf4daR/sOJcj5EIDAlgnkPeapETVid01hHsJd9ERAi/Zoh1W+Tu/jLZf8td2pN7tGmMeBXyYgUvan/vuY/2ovdqyH5OefHMhCvF4ey1L9jfoW+SLMt3y57aC49AkUoSztRoDPDjrlFoukfbcIm6IgAIExCOxWmH/kqyCCNnqQozDu6zEXEiJQ7zVIujzrOWLphEJ+a2FdCmWR/OKTABHkEpcv8dlpKIz+rZ8c1LZgSZiXFqtG73eJL6Esta2wn8fFyWC8JnKhYvtZs3Gshs84HKeaC+071ZbBLghAoIPAyusSl4somxPE45p+tIc7ly7HSyhFTAux6T5uPCz8lAWi/hPizcVT7kNU5O9wjhznBe4yFt3HnVvlL22Lcdj67TJWs+uFq3qxpg5LkTy6Fn9Kmo6jb+xtMZS8JJTlmaaOPUNZquqf2idlRo+9CtXJ8Q1x8nrxaG6BrsWR9GkT0H0gWaA9bcO3ZB18tgR6R8XQvjsCT7EQgMA6BOazwZAV7rIOHc6BAAQgAAEIQAACEIDAlgjsvzBX3mJhlnr8t8SRYiAAAQhAAAIQgAAEIDCIwP4L80HV52QIQAACEIAABCAAAQhMgwDCfBrtgBUQgAAEIAABCEAAAgdOAGF+4B2A6kMAAhCAAAQgAAEITIMAwnwa7YAVEIAABCAAAQhAAAIHTgBhfuAdgOpDAAIQgAAEIAABCEyDAMJ8Gu2AFRCAAAQgAAEIQAACB07gyG9g09pCKGxCk9tOfl1QehOf0uYmpR0s1y3bOk/ZFg8dvf6WDRNLlw2NTvVmSxOzD3PWJNDsqnv0aNK+cWfEKz5f2Tk3t2uu3sCqa3zoyl/Mld10ff6h7NwYYKWvWeVep8GnF669OngK/WuvgGEsBCCwSwJH/qa5EOZ6y27ZXr7PtvY1NZDBUZdTc842jknt2sUEYRv17FOG7Bz6vT4ncOykCcQ+fsdb6XenzX66NuhKrw/5/Yn/vqZyKeWf5iu/5RPPt9K3ARY+26C8mzKm0L92U3NKhQAE9pTAqjAXD9pL/hu95umW7dpjJlvNN16wo58vCARv63MZAdAlzGs86umW81JQLEN5ukL5fbccT+1K6y+ZpuVH0arqryc2R88kXsnU4yjn1wrf5txQL/8Rzi2Pdql95ASLT3r+z/w5xz3s29OOfzBm5/pzrvIlYa6FeM31ovOXie7LajxI7bHSN91Q8Nk04d3mv+v+tdvaUzoEILCHBNrCvBFp7sTXJHq0ZGDToS3pjUy8q/LVN9+cx9XymHelS/l3lT2+/KN3kx0+xaboCUyPtxpFl5urv9RFTzTiMSKsu27q+maQ1qsRytXCvBHXR6/6OsukSDjHj9TZah85tsQnrZ/k12fiYPElfbcEpL9dXbTpMowlN3HuEuZivfSR+JG/9Xhg5Z8TRm80fUzytNI3Tc+yP5Z/qHw2zX/T+e+6f226fuQPAQjMjkAQ5tHbLbU7FY9pFOWNCM7V+lSWjcabdnqjLz3u7gqR6RLmVlhF6vEVj72eWFhN5sst1b/19CDmpQf7aF8ULyJqU5sbz3rg2/VEoWRnlyioaZ8Sn1y+euJhsSN9+gSa/t16kiTXWnodFvqYu6TGBDlXh8RY+VvCyErfNGHLfkuYy/UyZz6b5r/p/HfdvzZdP/KHAARmR6DtMc95cy1hnHpcBdEWhXlYrKgnE7myS82mJwRp/Ws84nHgFy+gsJD4/Mf8N7eAVi+0qw1lEdstb10hryIfhPnsLuiVCjUL31pPaFKxUupj6bFpn7Hyzzzxak12rfRNt5BlvyXM585n0/w3nf+u+9em60f+EIDA7AisxpjLjUoLS2sx5DZCWfRiVB1u8pG3VQRxEm/e12MeWjV6ENP6l0JZoqCRUAGxUWJxJT5be+zlfP07Pjrv8+abkjAvtU9kVeJDKMvsLuqVCuk+2PVEZN0YcymslH+cjMbrKyeUrqnrr28o2hitB58xKE4zD6v/TdNqrIIABA6YwPJ1ia0FhTosQ+DoxYsSiqHjTONNzQvSXAyreGxznxgKY6WHc9PytdBVoSghBtsL5LBA8rL/u+sNFNEgvfBUvyLQqr/2UCcx78FDrctuJi6yIFQ+fUNZcnxaTwis9rH4ZBZ/hnh2Xpk4n2FBtfFK/1PXVqzwStvr6y/3usRS/pKp7oMdr0tcLh7vu3h7jFaCzxgUp5uH1f+mazmWQQACB0hg+AZDuVCWAwRJlSEAAQhAAAIQgAAEIDCEwDBhni5q7BM3PcRqzoUABCAAAQhAAAIQgMDMCAwT5jODQXUgAAEIQAACEIAABCCwKwII812Rp1wIQAACEIAABCAAAQgoAghzugMEIAABCEAAAhCAAAQmQABhPoFGwAQIQAACEIAABCAAAQggzOkDEIAABCAAAQhAAAIQmAABhPkEGgETIAABCEAAAhCAAAQgcOQ3CzqVjYH053SdTT6sHUKnyFptMBTNC5sU9dmVc1v12ke+a7AJGzSxudEa5DpOiTsfXvHp9/w37sAZD7fS5Thrg6F0E6v0+mm2vQ8bkFkbDBWuv9A3culW/iWaVv2t9Bo++rWyufpbfKUM2ZlVb+KmX01rnT+Ez3g9cbc5lfhZfOG327ajdAgcGIHgMQ83PPkrfkQEvuy/1s6Zc4Alg6589JbhfvfQFQEzh7puqg6yydSY77AfO79N1Xuf8hVh8kShX3elN4LGPdeMByJUL/nva03l0+sn/Z3mK7/lU3t+ZBx3GL7q/0ELfyv/2jbaFJ9ot9S32WHU6Z2LLb5if2R6JzMm5/h/oviOxaeW4xSPK/Gz+MJvii2KTRCYNYG8MJeBLA7ujTcmeLr8J2x3rz2aelv7xBu09H7KeeIt+5n/b7rdu/JkSP4rHrVS+f5Gd/Tuwq7Ty81Nq/HeVXtd0xub3DxfSm7+2iOl6954wlo2R3v1calHS0RsrZAt8I1bnYsX8eiZhkPO21wqX21HHjimHslK/voqkXZeCi8r/zRdzvWewWo+s748R6zcusJTjwXRnNLEKTfR1ZP83PWlq5meL2niKLjrvx/lr82WE8HKvwvpNvlo4WzxteqTnj+U/4hdbhJZWfys9NRJZR0/iUpjBAQgsM8E8sJcbrzRQya1a8RhEG1yk40f7VHP3VAb4RxE87VG1MvfInzlRhu9ZjLYxbziTTimWeXnbqh9PK7a7pxHK2URjxFh3TVI68E850FcR3jm+Aqb5jHrsr3SkBerfDleeyBzdbLav8Tbyj/lK8f3mbjs89W3TdvXFZ6pRzdOfPUTNqlHnKRmJnYrwvmNpo11/bvOl/77WNNHOybNVflbrDfNR8rXY0e0x+Ir6fKUwF8TwbkhE289Njfsl9WT60lfzzlhmeNv8dnXdIuflX7o/Pa13bEbAntMYCnMdR1aHs9GGBcfg0eBKP+NISHpjSgOgHLT0F6e1GMqNx79qLemfC0MrRts2liN0Ij/vOLtTb3ncqAerGPZ8l/5iKhMhaqOMc3dWGs6UEmYlx5dNzfuuI6gdWNXTxxSE1qhTRbTLmFu5Z/LNydeavhwTJmA1YaldHWNhMl518QpCmcdCtZH2HScH58GLa9R/WSuT/4lQpvmU+rXJb5xIqTrLGNBHGcl3zS0SIe8jMVnX68vi5+Vfuj89rXdsRsCe0wg7zFPK2TdtOT4Do+5i96ZDmEewl106EPu0a5Vvk7v4y1P7Y7eqxhmUuMRjwO31FPKFlESPXwpR8nPPzlwshCvNpQl5jFEmMc8cuXX8LL4l/IopSHMtzd0WG1opWtLS22aCtD0CZgVClASsF0e8/TpW24ybZG26m+ll/hor79lh6RrvvGJWLLYc/mUIBWOqZ19+dfYt0/HWPys9EPnt09tja0QmAmB3QpziRkVQRsfzUZh3NdjHm9m8tYJ+WivvdVQqeBNb6KlUBbJO044RJCL51rCVLT9evGXPr7vm1/WFeZW+WnoS46XJUo0ozTUwcqfUBarh46TbrWhlR6tSIVK2r8kXT6xf8fJYLwm+56va58T5lb+tfSs+lvpXXxyoWX6CVdqXy6UTzNOJy6pIyMdJ8biU8txiseV+MV7RxyzU77wm2KLYhMEZk1g5XWJy0WUTbXFo51+tIc7ly7HSyhETAuPv71XKyz81ItIxVPu3xQhC0rDOXKcF7hhganEor93loe2YSXURhL9jTPkn8a+lpqvOSeUnSzqDKdFL5W/WXYurmzCNbS9LYZyU5BQFr04M40RLdhY4hsXfwb7haX/CIPwOz76rik/rZ+KU7XaPxSWckzrV8g/xt0uQ21yC4RLbUhamYBiHw9s9XUr3WrbRiwv+3fH6wyX13j6usCa88Vw3YfSPHQoSN9XvVr1t9JLfDpCuVrjl8VX6q7C/bIx5ppNX/6HcP1Y/Kz0If3rEPhSRwhAYFQC89lgqNajNSo+MoMABCAAAQhAAAIQgMA4BPZfmCcerdTjPw4mcoEABCAAAQhAAAIQgMBmCey/MN8sH3KHAAQgAAEIQAACEIDAVgggzLeCmUIgAAEIQAACEIAABCBQJoAwp4dAAAIQgAAEIAABCEBgAgQQ5hNoBEyAAAQgAAEIQAACEIAAwpw+AAEIQAACEIAABCAAgQkQQJhPoBEwAQIQgAAEIAABCEAAAkd+A5nWFkJhk5++u1KWOOoNNEqbf1g7RG6irZRtMfvR6z+W3bvgM5btPfKRDY1aG+D0OJdDMwTizoVXfJrsjJvuimulS5ayQdaji7yXG1fpokob3NTkrzdwyY0/1gY6VnqpY1j2WelD+cj5zbbwwjjLV45pdkUOx+jN0GrsK50/94tG9d3lGJ/eh6z+Y/XPuTOkfhCAwFYJHPmbwkKY6y2zZXv5Ptva15jctaV8zbmbPCa160AE8KhIZafQuEvqGBmPnd8YNu17HtYGXF3pue3M4/blUVTq8SPdIj5y68o/vf7S340gdXE3WRGil/z3tSZjK7223XbFJy1Xfssn1k8zvuN/+N2Qs5+S/ZFp6fxaTvt2XDqWWP0t7b/W8fvGA3shAIHJE1gV5s0W2UuvudquWGrT8ig33gjx8iy33BZvTm7L+S5hXuNRT7d0F0NiGcqTFOzruyV3aldaf8k0LT+KUFV/PbGR7cm7tj0P/OT8WiFb4tN4ciTP5Zbo2psWu1/qEdLll9pX1T1w9R9p52XdarYct/JP03/mCznuwWfyl9hEDFxXeKbm54TLJ/6gKCQL17l7wh9nTfhzQkjnL/ZosZWbCKwzsdsVH3EEvOy/UXDnxt+XfLr1FLPL/tx4NpEuuRMzckK9pv9GY6fqYNoJTAqFAAQ2QaAtzBuR5LRHTG4c+qaQDvQy0MlX31xyN0ZrQOtKl/Lvqht/IwZbO3yKTfHGlh5vUdPl5uovddETjXiMiNuum56+2XZ5AGuFuXVDaB6DL21MPf5W+Vb7SvnN5CBMKqSd4ycyLwkhK/+UrxzfZ+JitS/pCwJjCE/d9zVXacP4kb9zItIqP05y01CW1CMeJ6OyOibWSyZy8RpN02vb37LPSpdy1uGTE+ZvNNdArN/Vxe9lGEvO8dFln/x7zfm1nPb5uK7xuqb/dvXPfeaB7RCAwCQJBGEevd1i4al4LKP3q8MjGo6LN8ZUWElizou1rjC3vF+px1U8xnpiYWFvbuTxsLT+Luet0jfTaF8c3EVUpjbrGN2uJwoVdoZDUo9jyjp3g+4qv6Z9ozgoeTu72sjKP2drl7ix+JBeJmAJSyu9q13S0BLpj7mQCSt/sT4Kp0+Tfq6u0ewTJyu9pm9Y9lnp6/KxhHmcaOgngcI4HQcKoULhKZd1fg2jfT8md1+q7b+l/rnvXLAfAhCYFIG2xzx6p7Q31xLGOxbmYbGgnkzkBt8Scj1hSOtf4xGPN1bxcgkLERWP+W/Oayj5XfNfWYg3psdcP4otCYhc+Vb7DhHmcm4pf4T59gYDS1haMcpdfToVliXPbU0oS83EzOqzVnqO+q74ZJ4ItpwB8YmYHi9S5qVrtPb87fXE3ZWU6xe1/TdaXdM/d1dDSoYABGZAYDXGXAZyfRO2FkNuI5RFe9B0uMlH3lYRxEm8eV+PeWjH6IFK618KZYk3RHlULDaKQJbH6tpjL+fr3/HRshUzmnauricOlsfcKt9q31ph3hVKYOVPKMt2hpF1hWcuNCqNya2J0e0qP+2fUp58uq4PK1TNSu+ivSs+cbIcx5+c/ZpRlzAs2V9z/nZ64e5K6XKypONnOs727Z+7qyElQwACMyGwfF1ia7GiDsuQivqbRWtxoY4jjQOXF6S5GEjxaOc+MRTGSg/npuUnb4VYLjyV+FQvkMOj28v+7xgDXbgZH726SOxVf+29asI1YnnBg6/LbiYuLX65GNEOG4t89CN8eWrgP8v6xEfXNeUX2jdXfusJhRTqRUGLY1q/Uv9JQ5F8PSQvXpk40gij2MccuxYmr6R3hCKttL9u3zRG3Cq/EUzL6yMTY17sW1bfszBa9pXSx+Aj9unrOLd4XV0joe309WXZL/mXzrf4zCW99CS11H+t/jkXPtQDAhCYDIHhGwzlQlkmUz0MgQAEIAABCEAAAhCAwH4QGCbM00WFfeOm94MRVkIAAhCAAAQgAAEIQGDjBIYJ842bRwEQgAAEIAABCEAAAhA4DAII88NoZ2oJAQhAAAIQgAAEIDBxAgjziTcQ5kEAAhCAAAQgAAEIHAYBhPlhtDO1hAAEIAABCEAAAhCYOAGE+cQbCPMgAAEIQAACEIAABA6DAML8MNqZWkIAAhCAAAQgAAEITJzAkd8y/VQ2BtKf09wmF1ZFrB0erfN3ka42J4nFn6YbnOzCrg2WGTZAkk1KdvRqy12Xv0G008262ZY9bAC2zrU93ZqNYxl8xuE41Vxo36m2DHZBAAIZAsFjHsSS/BU/IrJf9l9r58w5IE23YN7HCUbfdpBNofoI877HW/aMnZ9V3iGnp1u1y2/5vHbIUFTd4TPvjkD7zrt9qR0EZkggL8z19sXNdsXB0+Y/Ybt77XHVW2In3rild1TOE29dbrt1GTiPF+nyWfHolcpXW2KfXm4mEs321tVe4VSYN1swu5+o1k63lI+ittlgqWVztFczSrd8lvNrhHGsi+cWhJRmccP/VuVLuwR+6ZbdmS3vhfey/BL/mi3H0/zTJw5W+TO8qCZVpXSSnevfkzJ4y8bAZ8vAt1wc7btl4BQHAQgMJZAX5uLRfK4RulJCI75DmId40uNHe9RTgSvHNMIuiOZrjaiXv1/y37v+G712IhZiXjKQ6jSr/NQjIsf38chquxsR6U6UbSmLeIwI6y6Ro28GKZdGCFcJc6mLniQ1LNwT/g8R5rGuYqN+wqHrn9ovtqUTA4t/iafkpycxKZOa8lWX4s+RCeSEyRtNHxi5qL3MDj572WzVRtO+1ag4EAIQmAaBpTDX5ohnu/WoOyd+U/s7hLmLIkDyuNqIOC02U4+qeHy1MM6J0bRsLRxrbNXnN17p+E+tutcI71i2/Fc+InpTIdt4tiV5xaNt9YMaYa4nUZJfPEf+1iJefuuJhfq9fGKR498lzDs86qGe0rNybZGWb9Wf9GEEECZlfvAZ1r+mfjbtO/UWwj4IQCAhkPeYp5hqxO6awjyEu+iJQCpExRarfJ3ex1seRaz8VzzQqTe7RpjHgV8mIFL2p/77mP9qL3LkKfn5Jwfuiv/WhLJE+z7xf8SnCymL1COtz5G/DWFexb/EtJSGMN/9gJM+gSKUpd0m8Nl9H92kBbTvJumSNwQgsAECuxXmH/kaiaCNHt8ojPt6zAWMCMR7DaEY5lEDLJ1QyG8trEuhLJJ/fBIgglwEtMRvp6Ew+rd+ctDXvhhSpGP5xb51Q1mi99rirxnEuPe4WNhaLEsoS00rb+6YOBmM10QuVGxzpU8/Z/hMv42GWEj7DqHHuRCAwA4IrLwucbmIsjFGPKrpR3u4c+lyvIQyxLQQm+7jxo9ebRZ3+p9hEal4yr1Hd7lwUY7zAjekNYs5rfKXtkXRqt8uYwHVC1f1Yk0dliJ5dC3+lDQdR+/j5IMHOi5ElfRGOB89szCmdyiLCvUJ5/rJR+ATxbnkL8K/WUBbs/gztEOsrwrlCe2U8A9Gp5zS0JmUj9gUnxhkFn+2yrfaiPThBHQb87rEVZ7wGd7HppwD7Tvl1sE2CEAgITCfDYascJe5Nn0ulGWudaVeEIAABCAAAQhAYMYE9l+YK29t8Ehrb/WMGy5ULV1UWhu3Pncu1A8CEIAABCAAAQjsIYH9F+Z7CB2TIQABCEAAAhCAAAQgkBJAmNMnIAABCEAAAhCAAAQgMAECCPMJNAImQAACEIAABCAAAQhAAGFOH4AABCAAAQhAAAIQgMAECCDMJ9AImAABCEAAAhCAAAQgAAGEOX0AAhCAAAQgAAEIQAACEyBw5DfEaW0hFDaZyW0nv66xenOa0uYm1g6S65ZfOk/ZFg8bvf6bsHtAnmEDJL2Z0oC81jl11+WvY/PenyO72V7xG1M9qjam2vtKjVgB+IwIc4JZ0b4TbBRMggAEuggcuVcaYa637Jbt5ftsa1/DVwZH+YydtDECzAAAD3xJREFUb03ZpWNSu3YxQRhah77ny6ZEfd553vd4y56x87PKO+T0dOMt+S2f1w4Ziqo7fObdEWjfebcvtYPADAmsCnPZQv0l/+3aUl171JsNbmR7eNkmXj6dW853CfMaj3q65bsUFLeFl4G32Y4+lN93y/HUrrT+kmlafhS1qv7LCUdzbMsrrc8XfnJ+jTButpI+/VkjpGLesY41/KU+byy8pYGP5OV5Lcsv8fPnHr272uuX9khSmn/6xMUqf4YX1aSqJBPNl/33vcaqXP+elMFbNgY+Wwa+5eJo3y0DpzgIQGAogbYwb0SUO2mEYCNKW6Et6Y1dvJ/y1Tf/nEfU8ph3pcvAelfZ04jF1g6fYlMUHunxFiFdbq7+6Zb38RgR1l0iR98M0no1QrhKmIvtcv4nqv6pB8jin9ovtqUTA4tfycMt+enQp1z/iJOo2J9qJyZW25FuE8gJEz9Rq+5/dgn7fQR89rv9LOtpX4sQ6RCAwMQIBGEevd1iW+oNzXlMw3GybFQ+qfDLicn4b/LfrlCWLmFuhT2kHlmJn9YTCwt445WOh614g/XTg3iQHuyjffJf+YjoTG1uPNuBm9inhWqFfaYwT/OLYl7yfiJhricWkl7Dr6sNOjzqy/6RTiJUeQhDq+FHSkeYlEHCZ6SONtFsaN+JNgxmQQACXQTaHvOcN9cSxjsW5mExYQz1kFqmHmar7fWEIK1/jUc8DvzihRQWEp//mP/mFtBKftf81y/EqxamNR7zAcK8il+pD5TSEOZW79t8euaJUytUbfMWTLsE+Ey7fYZaR/sOJcj5EIDAlgmsxpiLENTC0loMaYVSxAoNCWXRi1F1uMlHPnMRxEm8eV+PeTAxevLT+pdCWeQ8EZ9XG0EuIScSv6099nK+/h2Pr33zTTJxOHo1iaO3+JdCWSJLi5/OI8a9xycmNf2DUJYtX9aquDgZ1Iu7dWjY7iybRsnwmUY7bMoK2ndTZMkXAhDYEIHl6xJbixV1WIYUnC5+lPQoLKPwbBZgpqEa4pHNfWIojJUezk3L10JXhaKEVx16ES+hOa0Y9C54euGpfoWgVX+9cDOJeQ8e6Mu+wBjz3gjno2cWRvQOZVGhJuHce0391ALQIPw7+K+EqvinC0Hcx/rW8Es5pR76Uv/ILP5slb+hjk22ioBu476Low8BJHzm3cq077zbl9pBYGYEhm8wlAtlmRmkSVcH/pNuHoyDAAQgAAEIQAACtQSGCfN0UWPNKwBrLeM4mwD8bUYcAQEIQAACEIAABPaEwDBhvieVxEwIQAACEIAABCAAAQhMnQDCfOothH0QgAAEIAABCEAAAgdBAGF+EM1MJSEAAQhAAAIQgAAEpk4AYT71FsI+CEAAAhCAAAQgAIGDIIAwP4hmppIQgAAEIAABCEAAAlMngDCfegthHwQgAAEIQAACEIDAQRA48tvInx492q7r6TqbkFg7QE4Rp9o4J5oXNimq3ZVzB3UKGxjpzZAsG/TmQOu0q5V/z/Te9vfMn8MzBGT32Ct+Yyl/na91bc8dKnzm3cK077zbl9pBYGYEgsc8iKW4xbpUUET2y/4bd6+cWaVb1dFb3se6+91DXdzCfIp1l02F+r4zPq1nn3qtU14p/7Hz61OXQzvWT8zcE6o/y2/5vHZoIDrqC595dwTad97tS+0gMEMCeWEuIu6T5ubdbLcePG3+E7a71x7bgkd26R2V88Rbl24HLxnKwNlsJy8/Vzx6pfL9du9H7y7sOr3s/08mEs32y9Ve5VSwyhbyL/mv9pqnW85HUdxs8NOyOdqrGenzxSMv59cK68yW9sJreX6a3uXx7xLmpfMVX933pR2Xws4q37J/hhfVpKqUTrJz/XtSBm/ZGPhsGfiWi6N9twyc4iAAgaEE8sI83ea9Ed8hzEM86fGjPeo54dcIuyCarzWiXv4W4Xs3EXcxLxlIdZqUVSo/9YjI8X08struRkS6E2VbyiIeI8K6S+Tom0HKpZmIVAvztHzJWwt7+a0nEV02dQnzmvNLPK3zLfuH9mDOLxPICZM3mj4Eu9Wng/r6hs/+E6D/738bUgMIHBiBpTDX9W55RBth3HocnoPUIcxdFAEiSK82IlJ75FOPqniatTCuKV8Lx5xQLzVq42GPh6x4g1PvuRyoB/tYtvxXPiKaUyHbeNYlOXjyn/PfmjChXF2SiUF8YpBWsRWaJImFiVMOT+v8LmHe4VEP9ZSeZdl/YBfbTqqLMCljh89OuuXWCqV9t4aagiAAgXEI5D3mad41YndNYR7CXXRohBbt0Q6rfJ3ex1ueCtbUm13jEY8Dv0xApGyJT3/Mf3MLSCU//+RAFuJVecxrhG1tfbs85jXnl44ppdXYP04/JpcuAukTKEJZ2qTgM+9rh/add/tSOwjMkMBuhflHnqgI2uhBjsK4r8dcGkYE4r2mhfos3EwFq/zWwroUyiLFxScBIsglLl/iv9NQGP1bPzmo6VBWKEjt23BKoSzWYldtQ4zhj4uFrfIt+2sYcMz6BOJkMF4TuVCx9XPf/zPhs/9tWKoB7Tvv9qV2EJghgZXXJS4XUTaVFY92+tEe7ly6HC+hDDEtxKb7uPGjV5vFnf5nWEQqnnL/xgj5O5wjx3mRGNKaxZxW+UvbYhy6fruM1WB64aperKnDUiSPrsWfkqbj6H14SngCEBeiSrrkJaEszzR17BPK0uQvk5f4SsvsAtrUPimz8diX2meJp3B+OCbllIbilM7PLP4M/aDPKx+tdiS9TECFa/G6xAwq+Mz7CqJ9592+1A4CMyMwnw2GrHCXmTUc1YEABCAAAQhAAAIQmBeB/RfmylsrTZN6/OfVXNQGAhCAAAQgAAEIQGCuBPZfmM+1ZagXBCAAAQhAAAIQgMBBEUCYH1RzU1kIQAACEIAABCAAgakSQJhPtWWwCwIQgAAEIAABCEDgoAggzA+quaksBCAAAQhAAAIQgMBUCSDMp9oy2AUBCEAAAhCAAAQgcFAEEOYH1dxUFgIQgAAEIAABCEBgqgSO/AY0rS2EwiY/ue3k162B3pzmlz6Trl05rR0k1y2/dJ6yLR42ev1HtjtsYNRnc55a/iPb2ZVdb/u3ZNesi5FdX68sNqlig6FMS8Nn1t3f0b7zbl9qB4GZETjyg9ZCmOstu60t2teB0LUl/Dp5jXlOatcuJgh96yM7e36v50lD+K9TXsm8sfPrieKgDk833pLf8nntoCh0VxY+8+4ItO+825faQWCGBFaFuWyh/pL/Rq95uqW69qjLVvONF+7o5ws6wZubbtkuCV3CsMajm275LvnFMmTgPVZb1pe88rkGTO1K6y/npOVHUazqryc2R88kXm19vvCT82uFdWZLe6nv8vxS++j6dvEvne/Tjt5dhXb6MyXsrPIt+2d4UU2qSjLRfNl/32usyvXvSRm8ZWPgs2XgWy6O9t0ycIqDAASGEmgL80ZEuRMlvGRg06Et6Y1dvJ/y1Tf/nEfU8th2pUv5d9tCUMRia4dPsSkKj/R4i5AuN1d/qYueaMRjRFh3iRx9M0jr1UwkqoV5Wr7krYW91T6x/iW+pfaV80sebqt8y36rfUgfRiAnTN5o+tCwnOdxNnzm0Y5dtaB9592+1A4CMyQQhHn0dkv9Um9ozmMajpNlo1G0pR5yEYGfKDEtx60rzK2wh9QjKx57PbGwGs3bVap/6+lBzEsP9tE++a98RDSnNjee9cCt64lCzs70Mawck0wMzPaJ+eb4d3jEW+0b2zjn4bfOt+y32ob04QQQJmWG8Bnex6acA+075dbBNghAIEOg7THPeXMtYZx6RKMI35IwD4sJdWhFblJQanotWNP613jE48AvXkhhIfH5j/lvbgGt5HfNf/1CvCqPeY2wtdqnJMxLolszK5VRSquxn8tyswQyT5yyk83NWjHd3OEz3bYZwzLadwyK5AEBCGyRwGqMuQhVLSytxZDbCGXRi1F1uMlH3lYRxEm8eV+PeeAdF7+m9S+Fssh5Ij6vNoJcJiMS/6099nK+/h2Pr33zjRUKYrWPJcxrztc2NE8Ylk9MrPMt+7fY2Q+yqDgZ1Iu7dWjYQUJRlYbPvHsA7Tvv9qV2EJghgeXrEluv4NNhGVLpdPGjpEdhGYVnswAzDdUQj3buE4WdlR7OTcvXQleFooRXHXoRL6EprRj0robTC0/1Kwit+uuwjiacI5YXPPiXfYEx5r2ZuMiCUPn0CmWREzKLJ49eLSwulXqo9lmLr25fsSHllIYulfpHjf0zvLAmVSV9jfRdHD2pimzIGPhsCOxEsqV9J9IQmAEBCNQQGL7BUC6UpaZkjoEABCAAAQhAAAIQgAAElgSGCfN0UWPtKwBpAAhAAAIQgAAEIAABCECgRWCYMAcmBCAAAQhAAAIQgAAEIDAKAYT5KBjJBAIQgAAEIAABCEAAAsMIIMyH8eNsCEAAAhCAAAQgAAEIjEIAYT4KRjKBAAQgAAEIQAACEIDAMAII82H8OBsCEIAABCAAAQhAAAKjEECYj4KRTCAAAQhAAAIQgAAEIDCMAMJ8GD/OhgAEIAABCEAAAhCAwCgEEOajYCQTCEAAAhCAAAQgAAEIDCOAMB/Gj7MhAAEIQAACEIAABCAwCgGE+SgYyQQCEIAABCAAAQhAAALDCCDMh/HjbAhAAAIQgAAEIAABCIxCAGE+CkYygQAEIAABCEAAAhCAwDACCPNh/DgbAhCAAAQgAAEIQAACoxBAmI+CkUwgAAEIQAACEIAABCAwjADCfBg/zoYABCAAAQhAAAIQgMAoBBDmo2AkEwhAAAIQgAAEIAABCAwjgDAfxo+zIQABCEAAAhCAAAQgMAoBhPkoGMkEAhCAAAQgAAEIQAACwwggzIfx42wIQAACEIAABCAAAQiMQgBhPgpGMoEABCAAAQhAAAIQgMAwAgjzYfw4GwIQgAAEIAABCEAAAqMQQJiPgpFMIAABCEAAAhCAAAQgMIwAwnwYP86GAAQgAAEIQAACEIDAKAQQ5qNgJBMIQAACEIAABCAAAQgMI4AwH8aPsyEAAQhAAAIQgAAEIDAKAYT5KBjJBAIQgAAEIAABCEAAAsMIIMyH8eNsCEAAAhCAAAQgAAEIjEIAYT4KRjKBAAQgAAEIQAACEIDAMAII82H8OBsCEIAABCAAAQhAAAKjEECYj4KRTCAAAQhAAAIQgAAEIDCMAMJ8GD/OhgAEIAABCEAAAhCAwCgEEOajYCQTCEAAAhCAAAQgAAEIDCOAMB/Gj7MhAAEIQAACEIAABCAwCgGE+SgYyQQCEIAABCAAAQhAAALDCPx/HivopWcH+70AAAAASUVORK5CYII=" /> <br /></p><h4 style="text-align: left;">Configuration summary</h4><p style="text-align: left;">In total, this is what the configuration looks like</p><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">[mariadbtop]</span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">default-colour=green</span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">default-bgcolour=black</span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">alert-attribute=normal</span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">alert-colour=black</span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">alert-bgcolour=red</span></div><div style="text-align: left;"><span><span style="background-color: #eeeeee; color: #444444; font-family: courier;">user=monitoruser<br />password=monitorpwd</span></span></div><div style="text-align: left;"><span><span style="background-color: #eeeeee; color: #444444; font-family: courier;">pages=dashboard,replication,innodbstatus<br />servers=replica_server</span></span></div><div style="text-align: left;"><span><span style="background-color: #eeeeee; color: #444444; font-family: courier;">refresh-time=1000<br />alert-time=10<br />alert-file=alert.log</span></span></div><div style="text-align: left;"><span><span style="background-color: #eeeeee; color: #444444; font-family: courier;"> </span></span></div><div style="text-align: left;"><span><span style="color: #444444;"><span style="background-color: #eeeeee; font-family: courier;">[replica_server]<br />user=monitoruser<br />password=monitorpwd<br />host=repl1</span></span></span></div><div style="text-align: left;"><span><span style="color: #444444;"><span style="background-color: #eeeeee; font-family: courier;"> </span></span></span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;">[dashboard]<br />pagetitle=Dashboard<br />pagekey=d</span></div><div style="text-align: left;"><span style="color: #444444; font-family: courier;"><span><span style="background-color: #eeeeee;">sql1=SHOW TABLE STATUS IN cdr LIKE 'cdr'<br />vertical1<br />hide1<br />name-prefix1=primary_ts_</span></span></span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;"><span>sql2=SHOW TABLE STATUS IN cdr LIKE 'cdr'</span></span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;"><span>vertical2<br /></span></span><span style="background-color: #eeeeee; color: #444444; font-family: courier;"><span>hide2<br /></span></span><span style="background-color: #eeeeee; color: #444444; font-family: courier;"><span>server2=replica_server<br /></span></span><span style="background-color: #eeeeee; color: #444444; font-family: courier;"><span>name-prefix2=replica_ts_</span></span></div><div style="text-align: left;"><span style="color: #444444; font-family: courier;"><span><span style="background-color: #eeeeee;"> </span></span></span></div><div style="text-align: left;"><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;">[replication]<br />pagetitle=replication<br />pagekey=e</span></span></span></div><div style="text-align: left;"><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;">sql1=SHOW REPLICA STATUS<br />vertical1<br />server1=replica_server<br />name-prefix1=replica_<br />hide1<br />sql2=SELECT @@GLOBAL.GTID_BINLOG_POS<br />name2=master_gtid_pos<br />hide2</span></span></span></div><div style="text-align: left;"><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;">expr10=this.replica_Seconds_Behind_Master<br />name10=Seconds behind<br />expr11=this.replica_Gtid_IO_Pos<br />name11=Replica GTID<br />expr12=this.master_gtid_pos<br />name12=Master GTID</span></span></span></div><div style="text-align: left;"><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;"> </span></span></span><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;"> </span></span></span><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;"> </span></span></span></div><div style="text-align: left;"><span style="font-family: courier;"><span><span style="background-color: #eeeeee; color: #444444;">[innodbstatus]<br />pagetitle=InnoDB Status<br />pagekey=i<br />sql1=SHOW GLOBAL STATUS LIKE 'Innodb%'<br />value-col1=Value<br />name-col1=Variable_name<br />name-prefix1=pgs_<br />hide1<br />sql2=SHOW GLOBAL STATUS LIKE 'Innodb%'<br />value-col2=Value<br />name-col2=Variable_name<br />name-prefix2=r1gs_<br />server2=replica_server<br />hide2</span></span></span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;"><span>expr3=round(this.pgs_Innodb_buffer_pool_read_requests
/ \ (this.pgs_Innodb_buffer_pool_reads + \
this.pgs_Innodb_buffer_pool_read_requests) * 100, 2) \<br />name3=Primary InnoDB Cache hit ratio<br />expr4=round(this.r1gs_Innodb_buffer_pool_read_requests
/ \ (this.r1gs_Innodb_buffer_pool_reads + \
this.r1gs_Innodb_buffer_pool_read_requests) * 100, 2)<br />name4=Replica InnoDB Cache hit ratio</span></span></div><div style="text-align: left;"><span style="background-color: #eeeeee; color: #444444; font-family: courier;"><span>expr5=this.pgs_Innodb_rows_read<br />name5=Primary Rows read<br />expr6=this.r1gs_Innodb_rows_read<br />name6=Replica Rows read<br />expr7=this.pgs_Innodb_rows_inserted<br />name7=Primary Rows inserted<br />expr8=this.r1gs_Innodb_rows_inserted<br />name8=Replica Rows inserted<br />expr9=this.pgs_Innodb_rows_updated<br />name9=Primary Rows updated<br />expr10=this.r1gs_Innodb_rows_updated<br />name10=ReplicaRows updated<br />expr11=this.pgs_Innodb_rows_deleted<br />name11=Primary Rows deleted<br />expr12=this.r1gs_Innodb_rows_deleted<br />name12=Replica Rows deleted</span></span></div><div style="text-align: left;"><span><span style="background-color: #eeeeee; color: #444444; font-family: courier;"></span></span><span style="background-color: #eeeeee; color: #444444; font-family: courier;"></span></div><p style="text-align: left;"></p><p style="text-align: left;"><span style="font-family: inherit;">With that, i leave you to try MariaDB Top</span></p><p style="text-align: left;"><span style="font-family: inherit;">Happy SQL'ing<br />/Karlsson </span> <br /></p><p style="text-align: left;"> </p></div>Karlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-71454778279194283862023-11-23T14:59:00.013+01:002023-11-23T19:16:05.762+01:00Disruption, Evolution or Revolution<h2 style="text-align: center;"><span style="font-size: x-large;">Disruption or Revolution</span></h2><div class="separator"><div style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em; text-align: left;"><span> <br /></span></div></div><div class="separator"><h2 style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em; text-align: center;"><img src="https://upload.wikimedia.org/wikipedia/commons/thumb/d/d9/Larry_Ellison_on_stage.jpg/220px-Larry_Ellison_on_stage.jpg" /><span style="font-size: x-large;"> </span></h2></div><div class="separator"></div><div class="separator"><div style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em; text-align: left;"> <br /></div></div><h2 style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em; text-align: left;"><br /></h2><div style="text-align: left;"><span> </span>So let us assume this for a second: <i>Larry Ellison wakes up one morning in his luxury San Francisco house, for once all alone in the building with the rest of the family in the Hawaii house. He gets out of bed and goes to the kitchen to make some breakfast. He decides on a cappuccino and some oatmeal porridge, he is a bit of a health nut after all. The coffee is prepared for him by his automatic machine that he imported from Switzerland and he micros his porridge and sits down by the bar in the kitchen to eat it. He opens the newspaper and starts reading it, but his mind drifts away, he had been dreaming something just when he woke up and he tries to remember what it was, and then, in the middle of half reading the editorial in the San Francisco Chronicle and half thinking about his recent dream he gets it.</i></div><div style="text-align: left;"><i> </i></div><div style="text-align: left;"><i><span> </span>The next step for the huge company that he once founded and now is the CTO of was what he was dreaming about and he now knows where Oracle will head. He feels invigorated in a way that he has not felt for a long time, maybe last time was as far back as 1977 when he decided to co-found a company to build the worlds first commercial SQL-based relational database system.</i></div><div style="text-align: left;"><i> </i></div><div style="text-align: left;"><i><span> </span>Larry finishes his breakfast quickly, leaves his house in his bright read Ferrari and drives along the 101, ever so slightly too fast but still so slow that the V12 equipped Ferrari is merely idling. He gets to the huge Oracle Parkway head office and heads up to his secretary, still carrying his expensive, elegant and lightweight leather briefcase. "Get all available execs to my office. My execs that is." His secretary gives him a puzzled look, what is going on, he looks so upbeat, she thinks, but in a very positive way, he seems to be gleaming with enthusiasm as he continues into his office.</i></div><div style="text-align: left;"><i> </i></div><div style="text-align: left;"><i><span> </span>Two hours later the available team of executives are gathered in Larrys office, some of them who hasn't been here many times before admire the view of the Bay Area through the glass wall behind Larrys back. Larry looks at them with a smile, something the team is not used to so they know something is about to happen. "I have an idea. Actually it is more than an idea, it is a plan and an order. Exactly how this plan will be implemented is up to you, but take it as an order that it is going to happen. If you don't agree with me, that is fine, there are many other companies out there that you can work for."</i></div><div style="text-align: left;"><i> </i></div><div style="text-align: left;"><i><span> </span>Larry now has the attention of everyone in the room, they look at him and wait for the next comment from him. Larry stays quiet for a while, and then he raises his voice ever so slightly. "If you don't want to be part of this big change, the biggest in the history of this company, then you are free to leave the room. There will be no hard feelings, I just want positive vibes, if you feel like being negative you are free to go somewhere else in the Valley. I will not oppose that and I wish you all the best if you don't want this. But make up your mind right now as after I have told you what we are going to do, I expect you to stay around and positively support the journey that we are about to embark on. OK".</i></div><div style="text-align: left;"><i> </i></div><div style="text-align: left;"><i><span> </span>The team of C-level executives look at each other, and then again at Larry, who keeps quiet. Then everyone looks at Larry again and all are still quiet. 30 seconds pass in silence, then Larry talks again, real quiet so everyone has to listen real hard. "SQL is about to die, and we are to ones that will kill it". The execs look at each other in amazement and Larry talks again "It stops right here, right now". Everyone's eyes open wide and Larry raises his voice "SQL sucks. SQL will be gone from all Oracle products within a year. No SQL support in Java, Oracle database will be gone and replaced. All SQL will go away and it starts right now."<br /></i></div><div style="text-align: left;"></div><div style="text-align: left;"><span> </span></div><div style="text-align: left;"><span> </span>The story above might be interesting, but it has very little to do with reality, this would never happen, right? If it DID happen, would it count as <b>Disruption</b>? Not really Evolution I guess no, I would really count this as <b>Revolution </b>and those don't happen often unless you happen to live a country in Central America I guess. But they DO happen even in the world of IT, let's roll back the time to the early 1960's, when the Vietnam war ongoing, JFK was still alive and mini-skirts were just around the corner. At the office of the biggest name in computing, IBM, there was a worry, but sales wasn't one of them, IBMs computers sold like hotcakes. Not, the issue was that IBM at the time had some seven different lines, yes seven, of computers, all incompatible in every conceivable way. It has to be understood that in those days, a computer was to a very large extent bespoke, software was mostly an afterthought and was also incompatible not only between machines from different IBM series of computers but also between different individual computers, if one had a different printer than the other, rewrite your code etc. At the same time, IBM computers were selling and the most popular among IBMs many series of computers was the mid-range 1401 series of which thousands were produced of this machine with roots in the 1950's. A key component was that the 1401 was rather complete, it did not only have CPU and memory but other parts were also part of the system such as tape drives, printers and cupholders (just kidding). This sounds obvious today but not so at the start of the swinging sixties. And a key component of the 1400-series was the 1403 line printer. This printer was the fastest in the world and it came with the 1401 computer. So if you wanted the best and fastest printer in the world, you had to but a 1401 computer, there was no other way. And if you had some other computer and you wanted to print on the 1403, then you had to put that on a tape or something that you brought (physically, by hand) to the 1401 to print on the 1403. A mess.</div><div style="text-align: left;"><br /></div><div style="text-align: left;"><span> </span>Something called the SPREAD group at IBM began to look into this issue of all these different architectures in 1960ish and they were ready by the end of 1961 when they produced a report. This report said something similar to what Larry said in my made up story above, scrap everything we have and start again. And IBM did just that, to everyones surprise. In 1964 the System/360 series was announced and it came to revolutionize computing. From a technology point of view, if wasn't spectacular, but from the point of view of how the technology was applied, is was spectacular. System/360 was a series to computers, small to large and as such could
span the "whole circle" of computing needs, hence the 360-designation. And it came to change and influence IBM to this day and eventually wipe most of its then competitors off the map. <br /></div><div style="text-align: left;"><br /></div><div style="text-align: left;"><div style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em; text-align: right;"><img class="mw-file-element" data-file-height="463" data-file-width="314" height="157" src="https://upload.wikimedia.org/wikipedia/commons/thumb/3/36/Maurice_Vincent_Wilkes_1980_%283%2C_cropped%29.jpg/220px-Maurice_Vincent_Wilkes_1980_%283%2C_cropped%29.jpg" width="106" /></div><span> </span>The "Larry Ellison" at IBM at that time was IBM CEO Thomas Watson Jr, the son of the founder of IBM, Thomas Watson. Watson was not someone you could decide not to involve in decisions like this and Watson was actually all for it, deciding to make all existing, and extremely profitable, IBM (and other) computers obsolete to be replaced by one single series of computers. This isn't 100% true though, but sort of. One innovation of the 360 was the large-scale implementation of "micro programming" where the CPU is actually in itself programmable, an idea that originally was the idea of British IT-pioneer Maurice Wilkes (right). The 360 was microprogrammed, and this allowed it be microprogrammed to run Doom. No, just kidding, System/360 was microprogrammed to emulate a 1401. This is one of those things that seemed like a good idea at the time, it allowed programs from the immensely popular 1401 computer to run on a 360, and not only that, the 360 was rather fast for it's time so with this emulation a 1401 program could actually run orders of magnitude faster on a Commodore 64, no wait, I mean System/360, than on an actual physical 1401.</div><div style="text-align: left;"><br /></div><div style="text-align: left;"><span> </span>So once the 360 was delivered, which took longer than anyone expected after the announcement as, and this was quite unexpected back then, writing the code for the operating system(s) took waaay longer than expected (see the book "The mythical man month" for details), everything was <b>hanky panky </b>(technical term meaning hanky with a slight touch of panky added to it). One minor issue remained, and stayed to this day, the 1401 emulation. Yepp, this turned out to be an issue in the long run as really old programs, dating back to the 1950's would still run. And run. And run. And this is why software that was developed in the 1950's continues to run to this day (I'm not kidding here) and this is also a reason to why the 360 and it's newer siblings continue to be used, to this day.</div><div style="text-align: left;"> </div><div style="text-align: left;"><span> </span>In summary, with the System/360 IBM took a bet, and they more or less bet the company. IBM was largely all computers at that time, the privious profitable punch card business was in quick decline. But the bet paid off, big time.<br /></div><div style="text-align: left;"><br /></div><div style="text-align: left;">/Karlsson</div><div style="text-align: left;">Back from the dust-filled basement of computer history.</div><div style="text-align: left;"><br /></div><div style="text-align: left;">PS: System/360 introduced many new things that we stil use today and take for granted. The "byte" first came around as a term with System/360, which was 32-bit and 8 bits was convenient. That any printer works as well with any computer also started with System/360, although the common technology or printers being the #1 thing to drive sys admins crazy was still established at the time of the System/360 release and is a tradition that continues to this day.<br /></div>Karlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-14860618344255133912023-11-22T11:42:00.001+01:002023-11-23T10:28:38.500+01:00Is the cloud dead? Nope.<p>So, my two cents on if the cloud is dead or not. (If <b>Sun Microsystem</b> was still around I would crack a joke on Sun being good with Clouds. But I don't).<br /></p><p>I read that people start moving off the cloud and back on-prem and saving truckloads of money in the process, maybe enough for a gallon or two of gas so you can fill up your Ferrari (for example see <a href="https://basecamp.com/cloud-exit">https://basecamp.com/cloud-exit</a> and <a href="https://shiftmag.dev/leaving-the-cloud-314/">https://shiftmag.dev/leaving-the-cloud-314/</a>). On the other hand, I also see statistics showing that the cloud is growing, for example here: <a href="https://www.canalys.com/newsroom/global-cloud-services-q2-2023">https://www.canalys.com/newsroom/global-cloud-services-q2-2023</a>. What that last article says is that growth is slowing down, but it is still growing at a healthy rate.</p><p>So, what is all this about? I have some ideas of my own here, really (surprise!) and that what the rest of this artile is about. On one hand, if you move from on-prem to the cloud and think that this will be just like having all your computer cabinets, netwok switches etc. just like before, but have someone else managing the hardware, then you are likely going to fail and will not benefit from the #1 reason you likely went to the cloud to begin with: <b>saving cost! </b>Really, to benefit from the cloud you want to take advantage of how the cloud works, using the services provided by the cloud vendors, create and remove instances and other reasources as they are needed and prepare your application for an infrastructure that is moving and changing at times. If all you want is servers running linux on fixed IPs and have system that assume that all services are always available and are always, 24x7, in the same place and with the same attributes, maybe the cloud isn't for you.</p><p>This is not what happened to most of services that went back on-prem after having been on the cloud for a considerable time. The folks behind these services likely know <b>exactly </b>what they do and how their system work. They have systems that can sustain that services are gone or have moved, they don't assume that everything is always in the same place. They don't assume that scaling necessarily mean getting a bigger box, it can mean getting more boxes or something else. Often they have an application infrastructure largely running on containers (in some cases these smart guys and gals even put their database servers on some more firm ground (VMs) and ignored containers only for these).</p><p>In the latter cases we are talking about people who know stuff really well and actually do not mind managing IT infrastructure on their own. In this cases, there are again limited advantages to the cloud as they know how to do everything that a cloud provider can offer themselves and, just as important, they have the money and resources to do so.</p><p>Inbetween these two extremes, on one end those who has services, organization and / or a state of mind that is just to inflexible to benefit from the cloud and on the other end the super smart and resourceful geeks that can outsmart even Amazon, there is still a bunch of solid applications, companies and infrastructure that can benefit from the cloud. Maybe they are just a bit more flexible than the first group I mentioned and are willing to take the plunge and modernize what they have and to cloudify themselves, or maybe it is a company where IT is run by modern day Albert Einstein types and can outsmart anyone on the planet but they just cannot be bothered to mock around with IT hardware, OS patching and grand scale security implementations (if you ask me, this is the #1 reason to cloudify: having security looked after by someone else, and I am not claiming it gets more secure I just claim that I cannot be bothered with the mess of CA, SSL and Certificates and all that. What is a Certificate even?).</p><p>/Karlsson<br />Trying hard not to get a job that involves IT security<br /></p>Karlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-47241749611400694902017-03-03T12:58:00.001+01:002017-03-03T12:58:51.836+01:00CONNECT BY is dead, long live CTE! In MariaDB 10.2!Yes, you got that right, the old CONNECT BY as used by recursive SQL with Oracle has been replaced by <b>Common Table Expressions</b>, or the WITH statement in SQL:1999 which is now also available in MariaDB 10.2. Now, the SQL WITH construct, using Common Table Expressions or CTE, is useful for other things than just recursive queries, but this is the one feature that WITH enables that was previously very hard to do without some procedural code, the non-recursive use of Common Table Expressions could previously mostly be replaced by using temporary tables.<br />
<br />
This blog post will explain what recursive SQL is all about and why this is useful, and I will show some examples of both CONNECT BY and how the same SQL is written using the WITH clause.<br />
<br />
The most common example for recursive SQL is probably for doing a parts explosion, where we have a table of parts of some component where each part is either a main, top level, part or is a part of another part. For example a car with an engine, where the engine consists of pistons, cylinders and a camshaft, where the latter also includes some camshaft bearings. I think you get the basic idea here. To query this data to create a list of components that make up some other component, you need to recursively visit the data, i.e.. each row is evaluated using conditions from any other row already fetched, except the first row fetched that is.<br />
<br />
Now, let's look at some data first. I assume we have two tables here, one table that contains information on the different parts and then one table that contains information on the individual parts and then one table that contains the hierarchy of the parts, called components. Like this:<br />
<span style="background-color: #cccccc;"><span style="color: #444444;">CREATE TABLE parts(part_id INTEGER NOT NULL PRIMARY KEY,<br /> part_name VARCHAR(60) NOT NULL);<br /><br />CREATE TABLE components(comp_id INTEGER NOT NULL PRIMARY KEY,<br /> comp_name VARCHAR(60),<br /> comp_count INTEGER NOT NULL,<br /> comp_part INTEGER NOT NULL,<br /> comp_partof INTEGER,<br /> FOREIGN KEY(comp_part) REFERENCES parts(part_id));<br />ALTER TABLE components ADD FOREIGN KEY(comp_partof) REFERENCES components(comp_id);</span></span><br />
<br />
The two things to note here is that the components table has a column, <i>comp_partof</i>, that implements the hierarchy and that there is a self-referencing FOREIGN KEY constraint on this table. Given these tables, assuming that we are a small privately held car-manufacturing company in southern Germany, let's insert some data:<br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><span style="color: #444444;">INSERT INTO parts VALUES(1, 'Car');<br />INSERT INTO parts VALUES(2, 'Bolt');<br />INSERT INTO parts VALUES(3, 'Nut');<br />INSERT INTO parts VALUES(4, 'V8 engine');<br />INSERT INTO parts VALUES(5, '6-cylinder engine');<br />INSERT INTO parts VALUES(6, '4-cylinder engine');<br />INSERT INTO parts VALUES(7, 'Cylinder block');<br />INSERT INTO parts VALUES(8, 'Cylinder');<br />INSERT INTO parts VALUES(9, 'Piston');<br />INSERT INTO parts VALUES(10, 'Camshaft');<br />INSERT INTO parts VALUES(11, 'Camshaft bearings');<br />INSERT INTO parts VALUES(12, 'Body');<br />INSERT INTO parts VALUES(13, 'Gearbox');<br />INSERT INTO parts VALUES(14, 'Chassie');<br />INSERT INTO parts VALUES(15, 'Rear axle');<br />INSERT INTO parts VALUES(16, 'Rear break');<br />INSERT INTO parts VALUES(17, 'Wheel');<br />INSERT INTO parts VALUES(18, 'Wheel bolts');<br /><br />INSERT INTO components VALUES(1, '320', 1, 1, NULL);<br />INSERT INTO components VALUES(2, NULL, 1, 6, 1);<br />INSERT INTO components VALUES(3, NULL, 1, 7, 2);<br />INSERT INTO components VALUES(4, NULL, 4, 8, 3);<br />INSERT INTO components VALUES(5, NULL, 4, 9, 3);<br />INSERT INTO components VALUES(6, NULL, 1, 10, 3);<br />INSERT INTO components VALUES(7, NULL, 3, 11, 6);<br />INSERT INTO components VALUES(8, NULL, 1, 12, 1);<br />INSERT INTO components VALUES(9, NULL, 1, 14, 1);<br />INSERT INTO components VALUES(10, NULL, 1, 15, 9);<br />INSERT INTO components VALUES(11, NULL, 2, 16, 10);</span></span></span><br />
<br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><span style="color: #444444;">INSERT INTO components VALUES(12, '323 i', 1, 1, NULL);<br />INSERT INTO components VALUES(13, NULL, 1, 5, 12);</span></span></span> <br />
If you are not into mechanics, let me tell you that there are more parts than this to a car, for example I left out a few critical components, such as the cupholder, the dog that stands on the pickup cargo area and the insulting bumber-sticker, but I think you get the idea. Note that there are two "main" components, the '320' and '323 i' and that these are top level components are indicated by the <i>comp_partof</i> column being set to NULL.<br />
<br />
Now, assume you want to list all the parts that make up a 320. The way this works when using the CONNECT BY syntax, you compose one single SQL statement and provide a CONNECT BY clause to indicate the relationship. Like this:<br />
<span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;">SELECT LPAD('-', level, '-')||'>' level_text, comp_count, NVL(comp_name, part_name) name<br />FROM components c, parts p<br />WHERE c.comp_part = p.part_id<br />START WITH c.comp_name = '320'<br />CONNECT BY PRIOR c.comp_id = c.comp_partof;</span></span></span><br />
<br />
Let me explain this a bit, but there is nothing really magic here. We are selecting from the two tables and joining them just as usual. Then we use the START WITH clause to define the top level component and then the rest of the components are have a <i>comp_partof</i> of a component that matches the <i>comp_id</i> of the START WITH component or a <i>comp_id</i> of any other component that has been fetched.<br />
This way of writing recursive SQL has some advantages, such as it is relatively compact and is easy to understand. The disadvantage is that there are some quirks and limitation to this and that once your queries gets more complex, CONNECT BY gets a bit hairy. One sure sign that CONNECT BY is going away, even though I and many others tend to like it because of the ease of use, is that even Oracle, as of Oracle 11g, also has implemented the WITH construct, or Common Table Expressions or CTE. So looking at the above statement how this would work in MariaDB 10.2, this is what it would look like using the WITH construct:<br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><span style="color: #444444;">WITH RECURSIVE comp(comp_id, comp_name, comp_partof, comp_count) AS (<br /> SELECT comp_id, comp_name, comp_partof, comp_count<br /> FROM components JOIN parts ON comp_part = part_id<br /> WHERE comp_partof IS NULL AND comp_name = '320'<br /> UNION ALL<br /> SELECT c1.comp_id, p.part_name, c1.comp_partof, c1.comp_count<br /> FROM components c1 JOIN parts p ON c1.comp_part = p.part_id<br /> JOIN comp c2 ON c1.comp_partof = c2.comp_id)<br />SELECT comp_count, comp_name FROM comp;</span></span></span><br />
<br />
Comparing this CTE version to the CONNECT BY version as above, this is a bit more complex, but how it works is actually pretty clear once you look at it carefully. To begin with, the top level item or anchor is the first SELECT in the UNION ALL and the following components are fetched using the second SELECT. Then the recursive aspect is handled by this UNION being run until there are no more rows returned from it? As you can see, although this requires more text and more complex SQL to write, it is also a fair bit more flexible. For example, the anchor point is defined by a completely separate SELECT which means it can be whatever SELECT you want, selecting from any odd table. Secondly, the column you use and the conditions for defining the hierarchy can be as complex as you want. And thirdly, there is also the power of that last SELECT which in the case above just gets the data from the UNION, but you can actually apply any kind of filter, ordering or column filter to this query. The result of the query above is this:<br />
<span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;">comp_count comp_name<br />1 320<br />1 4-cylinder engine<br />1 Body<br />1 Chassie<br />1 Cylinder block<br />1 </span></span></span><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"> </span></span></span>Rear axle<br />4 </span></span></span><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"> </span></span></span> Cylinder<br />4</span></span></span><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"> </span></span></span> Piston<br />1 </span></span></span><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"> </span></span></span>Camshaft<br />2</span></span></span><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"> </span></span></span> Rear break<br />3 </span></span></span><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"> </span></span></span>Camshaft bearings</span></span></span><br />
<br />
Before I finish this off, the WITH RECURSIVE statement is somewhat overly complex, in MariaDB 10.2 you can for example skip listing the column names of the recursive table, like this:<br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><span style="color: #444444;">WITH RECURSIVE comp AS (<br /> SELECT comp_id, comp_name, comp_partof, comp_count<br /> FROM components JOIN parts ON comp_part = part_id<br /> WHERE comp_partof IS NULL AND comp_name = '320'<br /> UNION ALL<br /> SELECT c1.comp_id, p.part_name, c1.comp_partof, c1.comp_count<br /> FROM components c1 JOIN parts p ON c1.comp_part = p.part_id<br /> JOIN comp c2 ON c1.comp_partof = c2.comp_id)<br />SELECT comp_count, comp_name FROM comp;</span></span></span><br />
<br />
And although Oracle 11 and up supports the CTEs, it works a bit differently. For one thing, the RECURSIVE keyword isn't supported (it is assumed to be recursive by default) and the way I read the SQL standard, this is actually wrong, for recursive queries you have to use the RECURSIVE keyword. Second, Oracle does require the SELECT-list. So in Oracle, you would see something like this:<br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><span style="color: #444444;">WITH comp(comp_id, comp_name, comp_partof, comp_count) AS (<br /> SELECT comp_id, comp_name, comp_partof, comp_count<br /> FROM components JOIN parts ON comp_part = part_id<br /> WHERE comp_partof IS NULL AND comp_name = '320'<br /> UNION ALL<br /> SELECT c1.comp_id, p.part_name, c1.comp_partof, c1.comp_count<br /> FROM components c1 JOIN parts p ON c1.comp_part = p.part_id<br /> JOIN comp c2 ON c1.comp_partof = c2.comp_id)<br />SELECT comp_count, comp_name FROM comp;</span></span></span><br />
Yes, we are all happily following the same SQL standard. Somewhat...<br />
See the <a href="https://mariadb.com/kb/en/mariadb/common-table-expressions-overview/" target="_blank">MariaDB Knowledge Base</a> for more information on common table expressions.<br />
<br />
Happy SQL'ing<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com23tag:blogger.com,1999:blog-9144505959002328789.post-56696251837377802472017-02-28T18:18:00.000+01:002017-02-28T18:18:37.943+01:00JSON with MariaDB 10.2JSON is fast becoming the standard format for data interchange and for unstructured data, and MariaDB 10.2 adds a range on JSON supporting functions, even though a JSON datatype isn't implemented yet. There are some reasons why there isn't a JSON datatype, but one is that there are actually not that many advantages to that as JSON is a text-based format. This blog post aims to describe JSON and the use cases for it, as well as to describe the MariaDB 10.2 JSON functions and uses for these, as well as showing some other additions to MariaDB 10.2 that are useful for JSON processing.<br />
<br />
So to begin with then, why do we need JSON? Or to put it differently, why do we not store all data in JSON? Well, the reason as I see it is that some data we work with really is best treated as schemaless whereas some other data really should be handled in a more strict way in a schema. Which means that in my mind mixing relational data with unstructured data is what we really want. And using JSON for unstructured data is rather neat, and JSON is even standardized (see <a href="http://json.org/" target="_blank">json.org</a>).<br />
<br />
There are reasons why this hasn't always been so. When the sad old git that is writing this stuff started working in this industry, which I think was during the Harding administration, computers were rare, expensive, handled only by experts (so how I got to work with them is a mystery) and built from lego-bricks, meccano and pieces of solid gold (to keep up the price). Also, they were as powerful as a slide-ruler, except it was feed with punched-cards (and probably powered by steam). Anyway, no one in their right mind would have considered string pictures of cute felines as something to be on a computer, or actually stuff to be stored in the database. The little that would fit was the really important stuff - like price, amount in stock, customer name, billing address and such - and nothing else. And not only that, stuff that was stored had some kind of value, somehow, which meant it had to follow certain rules (and following rules is something I am good at? I wonder how I ended up in this business. Again). Like, a price had to be a number of some kind, with a value 0 or higher and some other restrictions. As you see, these were hard and relentless times.<br />
<br />
And then time moved on and people started buying things on the internet (whatever the internet is. I think it is some kind of glorified, stylish version of punched cards) and stuff such as Facebook and Google came around. The issue with computer storage was now not how to fit all that nicely structured data in it, but rather once we have filled that hard drive on your desktop with all the product, customers and transactions from Amazon (I think Amazon has something to do with Internet, but I am not sure) and a full 17.85% of that drive is now occupied by that old-style structured data, what more do we put in there? Maybe we could put some more information on the items for sale in that database, and some general information on who is buying it? That should fill up that disk nicely, right? Well, yes, but that new data, although related to the structured data I already have, is largely unstructured. Say, for example, you write a review of a product on Amazon late in the morning after a good deal of heavy "partying" (which is not an Internet thing, I think), the contents of that would hardly be considered "structured". If you didn't like the product (which you probably didn't), then the appropriate terms for large parts of that review would probably be "profanity" or "foul language").<br />
<br />
The way to deal with the above is a mix of structured and unstructured data, with some kind of relation between the two. Like a column of unstructured data in each relational database table (or should I say "relation", just to show my age? Or maybe I should pretend to be really young, modern and cool, possibly sporting a hipster beard and all that, by calling it a "collection").<br />
<br />
With that out of the way, let's consider an example using structured as well as non-structured JSON data. Assume we have a store selling different types of clothing, pants, jackets, shoes and we are to create a table to hold the inventory. This table would have some columns that are always there and which have the same meaning for all rows in the table, like name, amount in stock and price. These are items that are well suited for a relational format. On top of this we want to add attributes that have different meaning for each type of or even each instance of items. Here we have things like colour, width, length and size. These we consider non-relational as the interpretation of these attributes are different depending of the type of garment (like size M or shoe sizes or a "zebra striped" colour) and some garments might have some unique attribute, like designer or recommended by staff or something. Our table might then look something like this:<br />
<span style="background-color: red;"><span style="color: #666666;"><span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "courier new" , "courier" , monospace;">MariaDB> CREATE TABLE products(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,<br /> name VARCHAR(255) NOT NULL,<br /> price DECIMAL(9,2) NOT NULL,<br /> stock INTEGER NOT NULL,<br /> attr VARCHAR(1024));</span></span></span></span></span><br />
In this table we have a few columns that look like columns in any relational database table, and then we have a column, called attr, that can hold any relevant attribute for the garment in question and we will store that as JSON a JSON string. You probably notice that we aren't using a JSON datatype here as that is not present in MariaDB, despite that there are JSON functions, but those JSON functions act on a text-string with JSON content. These functions are introduced in MariaDB 10.2 (which is in Beta as I write this), but there are a few bugs that means you should use MariaDB 10.2.4 or higher, which means as for now we assume that MariaDB 10.2.4 or higher is being used.<br />
<br />
But there is one issue with the above that I don't particularly care for and that is, as the attr column is plain text, any kind of data can be put in the attr column, even non-valid JSON. The good thing is that there is a fix for this in MariaDB 10.2, which is CHECK constraints that actually work, and this is a little discussed feature of MariaDB 10.2. The way this works is that this kind of constraint kicks in whenever a row is INSERTed or UPDATEed, any CHECK constraint runs and validates the data and if the validation fails the operation also fails. Before I show an example I just want to mention one JSON function we are to use here, which is JSON_VALID which takes a string and checks if it is valid JSON. Note that although CHECK constraints are particularly valid here, check constraints can be used for any kind of data validation.<br />
<br />
Armed with this, let's rewrite the statement that creates the table like this:<br />
<span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "courier new" , "courier" , monospace;">MariaDB> CREATE TABLE products(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,<br /> name VARCHAR(255) NOT NULL,<br /> price DECIMAL(9,2) NOT NULL,<br /> stock INTEGER NOT NULL,<br /> attr VARCHAR(1024),<br /> CHECK (JSON_VALID(attr)));</span></span></span><br />
<br />
Let's give this a try now:<br />
<span style="background-color: #cccccc;"><span style="font-family: "courier new" , "courier" , monospace;"><span style="color: #444444;">MariaDB> INSERT INTO products VALUES(NULL, 'Jeans', 10.5, 165, NULL);<br />ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `inventory`.`products`</span></span></span><br />
Ok, that didn't work out. What happens here is that a NULL string isn't a valid JSON value, so we need to rewrite our table definition:<br />
<span style="background-color: #cccccc;"><span style="font-family: "courier new" , "courier" , monospace;"><span style="color: #444444;">MariaDB> CREATE TABLE products(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,<br /> name VARCHAR(255) NOT NULL,<br /> price DECIMAL(9,2) NOT NULL,<br /> stock INTEGER NOT NULL,<br /> attr VARCHAR(1024),<br /> CHECK (attr IS NULL OR JSON_VALID(attr)));</span></span></span><br />
Following this we can try it again:<br />
<span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "courier new" , "courier" , monospace;">MariaDB> INSERT INTO products VALUES(NULL, 'Jeans', 10.5, 165, NULL);<br />Query OK, 1 row affected (0.01 sec)<br />MariaDB> INSERT INTO products VALUES(NULL, 'Shirt', 10.5, 78, '{"size": 42, "colour": "white"}');<br />Query OK, 1 row affected (0.01 sec)<br />MariaDB> INSERT INTO products VALUES(NULL, 'Blouse', 17, 15, '{"colour": "white}');<br />ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `inventory`.`products`</span></span></span><br />
That last statement failed because of malformed JSON (a double quote was forgotten about), so let's correct that:<br />
<span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "courier new" , "courier" , monospace;">MariaDB> INSERT INTO products VALUES(NULL, 'Blouse', 17, 15, '{"colour": "white"}');<br />Query OK, 1 row affected (0.01 sec)</span></span></span><br />
One thing that has yet to be discussed is indexes on JSON values. As the attr column in our example is a plain text, we can of course index it as usual, but that is probably not what you want to do, rather what would be neat would be to create an index on individual attributes in that JSON string. MariaDB doesn't yet support functional indexes, i.e. functions not on values but on computed values. What MariaDB does have though is Virtual Columns, and these can be indexed and as of MariaDB 10.2 these virtual columns don't have to be persistent, (read more on Virtual Columns here: <a href="https://mariadb.com/resources/blog/putting-virtual-columns-good-use" target="_blank">Putting Virtual Columns to good use</a>).<br />
<br />
The easiest way to explain this is with an example. Let's say we want an index on the colour attribute, if such a thing exists. For this we need two things: A virtual column that contains the colour attribute as extracted from the attr column, and then an index on that. In this case we will be using the JSON_VALUE function that takes a JSON value and a path, the latter describing the JSON operation to be performed, somewhat like a query language for JSON. <br />
<br />
We end up with something like this:<br />
<span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "courier new" , "courier" , monospace;">MariaDB> ALTER TABLE products ADD attr_colour VARCHAR(32) AS (JSON_VALUE(attr, '$.colour'));<br />MariaDB> CREATE INDEX products_attr_colour_ix ON products(attr_colour);</span></span></span><br />
With that in place, let's see how that works:<br />
<span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "courier new" , "courier" , monospace;">MariaDB> SELECT * FROM products WHERE attr_colour = 'white';<br />+----+--------+-------+-------+---------------------------------+-------------+<br />| id | name | price | stock | attr | attr_colour |<br />+----+--------+-------+-------+---------------------------------+-------------+<br />| 2 | Shirt | 10.50 | 78 | {"size": 42, "colour": "white"} | white |<br />| 3 | Blouse | 17.00 | 15 | {"colour": "white"} | white |<br />+----+--------+-------+-------+---------------------------------+-------------+<br />2 rows in set (0.00 sec)</span></span></span><br />
And let's see if that index is working as it should:<br />
<span style="background-color: #cccccc;"><span style="color: #444444;"><span style="font-family: "courier new" , "courier" , monospace;">MariaDB<span style="font-family: "courier new" , "courier" , monospace;">></span> EXPLAIN SELECT * FROM products WHERE attr_colour = 'white';<br />+------+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+------------+<br />| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Etra |<br />+------+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+------------+<br />| 1 | SIMPLE | products | ref | products_attr_colour_ix | products_attr_colour_ix | 99 | const | 2 | Uing where |<br />+------+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+------------+<br />1 row in set (0.00 sec)</span></span></span><br />
<br />And just to show that the column attr_colour is a computed column that depends on the attr column, lets try updating the colour for the blouse and make that red instead of white and then search that. To replace a value in a JSON object MariaDB 10.2 provides the JSON_REPLACE functions (for all JSON functions in MariaDB 10.2 see <a href="http://mariadb.com/kb/en/mariadb/json-functions/" target="_blank">MariaDB Knowledge Base</a>).<br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="color: #444444;"><span style="background-color: #cccccc;">MariaDB> UPDATE products SET attr = JSON_REPLACE(attr, '$.colour', 'red') WHERE name = 'Blouse';</span></span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="color: #444444;"><span style="background-color: #cccccc;">Query OK, 1 row affected (0.01 sec)</span></span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="color: #444444;"><span style="background-color: #cccccc;">Rows matched: 1 Changed: 1 Warnings: 0</span></span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="color: #444444;"><span style="background-color: #cccccc;">MariaDB> SELECT attr_colour FROM products WHERE name = 'blouse';</span></span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="color: #444444;"><span style="background-color: #cccccc;">+-------------+</span></span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="color: #444444;"><span style="background-color: #cccccc;">| attr_colour |</span></span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="color: #444444;"><span style="background-color: #cccccc;">+-------------+</span></span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="color: #444444;"><span style="background-color: #cccccc;">| red |</span></span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="color: #444444;"><span style="background-color: #cccccc;">+-------------+</span></span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="color: #444444;"><span style="background-color: #cccccc;">1 row in set (0.00 sec)</span></span></span><br />
<br />
<br />There is more to say about JSON in MariaDB 10.2 but I hope you now have a feel on what's for offer.<br />
<br />
Happy SQL'ing<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com4tag:blogger.com,1999:blog-9144505959002328789.post-63570855723659694092017-01-16T21:46:00.004+01:002017-01-16T21:46:59.200+01:00Released MyMSSQLDump 1.1My program for exporting data from MSSQL and Sybase into a whole bunch of other formats, including:<br />
<ul>
<li>JSON</li>
<li>HTML</li>
<li>CSV</li>
<li>MySQL (mysqldump style)</li>
<li>MYSQL / Sybase INSERT-style</li>
<li>Oracle INSERT-style</li>
</ul>
is now released in version 1.1. There is a whole bunch of new things, most notable Oracle style export format, but also:<br />
<ul>
<li>DATETIME datatype formatting</li>
<li>DATETIMEOFFSET formatting</li>
<li>Other temporal datatype support</li>
<li>Much more flexible formatting in general</li>
<li>More tests</li>
</ul>
As usual this is GPL v2 licenced and is available to download from <a href="https://sourceforge.net/projects/mymssqldump/" target="_blank">sourceforge</a>.<br />
<br />
Happy SQL'ing<br />
/Karlsson Karlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com9tag:blogger.com,1999:blog-9144505959002328789.post-76733750191570461492017-01-05T12:44:00.000+01:002017-01-05T12:44:12.168+01:00Powerful MariaDB exports using MyMDBDumpYou 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:<br />
<ul>
<li>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.</li>
<li>Oracle export format - This exports data as INSERT statements, just like mysqldump, but in an Oracle friendly way.</li>
<li>MS SQL / SQL Server format - This exports data as SQL Server friendly INSERTs.</li>
<li>Binary data formats - Supporting plain binary, base64, hex and a number of more formats.</li>
<li>Transaction support.</li>
<li>JSON export support - Including embedding dynamic columns as JSON.</li>
<li>UTF8 support and UTF8 validity checking.</li>
<li>Generated ROWNUM column output</li>
<li>FLOAT and DOUBLE formatting</li>
<li>DATETIME, TIMESTAMP, DATE and TIME custom formating.</li>
</ul>
And a lot more. Download from <a href="https://sourceforge.net/projects/mymdbdump/" target="_blank">sourceforge</a>.<br />
<br />
Happy SQL'ing<br />
/Karlsson Karlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com146tag:blogger.com,1999:blog-9144505959002328789.post-75910698104131488632016-10-11T10:38:00.001+02:002016-10-12T16:27:31.453+02:00Getting data out of SQL Server in a MariaDB friendly way.I know what you are thinking "Oh my, it's that time again, Karlsson insist that data should get out of some other database and into <b>MariaDB</b>. What is he up to this time?" and that is exactly right, this time data is coming out of <b>SQL Server</b>, out of that expensive, closed source, Windows-only software and into the lightweight, fast and cost effective MariaDB.<br />
<br />
I have already shown how this works when getting data out of Oracle in releasing <a href="https://karlssonondatabases.blogspot.se/2015/08/myoradump-oracle-dump-utility-version-12.html" target="_blank">MyOraDump</a>, so inline with that the corresponding program to get data out of SQL Server is called MyMSSQLDump and it has features along same same lines as MyOraDump. To connect to SQL Server (or Sybase, but this I haven't tested) I use FreeTDS, which is a nice Open Source SQL Server / Sybase driver. The output formats supported are MySQL (i.e. A file with (INSERT INTO...), MSSQL (same as MySQL format but aligned for MSSQL), JSON, JSON Array (similar to JSON but all data in one array) and CSV. All formats are very flexible and the common SQL Server / Sybase datatypes are supported.<br />
<br />
As usual, the code is written in C and use autotools for building. I have only tested building it on CentOS (6 and 7) so far, but there is nothing magic to it so it should work on other platforms too. Download it from <a href="https://sourceforge.net/projects/mymssqldump/" target="_blank">sourceforge</a>.<br />
<br />
Happy SQL'ing<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com14tag:blogger.com,1999:blog-9144505959002328789.post-17156631416343830422016-09-19T14:42:00.001+02:002016-09-26T18:17:05.315+02:00Replication from Oracle to MariaDB - Part 3This is the third part of a series of blogs on how to do Change Data Capture from Oracle in an effort to be able to replicate that data to <a href="http://www.mariadb.com/" target="_blank">MariaDB</a>. <a href="http://karlssonondatabases.blogspot.se/2016/09/replication-from-oracle-to-mariadb-part.html" target="_blank">Part 1</a> dealt with some basics on the Oracle side of things regarding the Oracle redo log which we use as a source for out CDC efforts and <a href="http://karlssonondatabases.blogspot.se/2016/09/replication-from-oracle-to-mariadb-part_16.html" target="_blank">Part 2</a> was about how to get this to work. We left part2 with an example of how to Replicate INSERTs from Oracle to MariaDB, but that is hardly good enough, we also have to deal with UPDATE and DELETE to make it at least somewhat complete, and this is what we will have a look at in this post.<br />
<br />
And I know what you are asking your selves now, you spent all that time explaining the Oracle ROWID and then you never used that. That was an awful waste of time. And no, it wasn't a waste, we will put it to good use now when we look at UPDATEs.<br />
<br />
For an INSERT, things are reasonably simple, right, we just add a row with some columns to the table. As for an UPDATE or a DELETE though, we need a means to identify the row to be updated. To reiterate one thing, the Oracle redo log is not like the MariaDB binlog here, and here I am assuming that the binlog format is STATEMENT. Let's look at an example, and for that let's insert some data into out EMP table (see the previous port for details).<br />
<h6>
SQL> INSERT INTO emp VALUES(2, 'John', 'Developer', 12000, 20);<br />
SQL> INSERT INTO emp VALUES(3, 'Georgina', 'Design', 11000, 20);<br />
SQL> INSERT INTO emp VALUES(4, 'Anne', 'Assistant', 8000, 30);<br />
SQL> INSERT INTO emp VALUES(5, 'Marge', 'HR Mgr', 14000, 30);<br />
SQL> COMMIT;<br />
</h6>
With that in place, let's now ensure that this is also replicated to MariaDB by running the script from the last post:<br />
<h6>
$ sqlplus -S / as sysdba @cdcemp.sql | mysql -u anders -panders anders<br />
</h6>
And to ensure that we got this right, let's check this out in MariaDB now:<br />
<h6>
$ mysql -u anders -panders anders<br />
MariaDB [anders]> SELECT * FROM emp;<br />
+-------+----------+-----------+----------+--------+<br />| empno | ename | job | sal | deptno |<br />+-------+----------+-----------+----------+--------+<br />| 1 | Anders | Sales | 10000.00 | 10 |<br />| 2 | John | Developer | 12000.00 | 20 |<br />| 3 | Georgina | Design | 11000.00 | 20 |<br />| 4 | Anne | Assistant | 8000.00 | 30 |<br />| 5 | Marge | HR Mgr | 14000.00 | 30 |<br />+-------+----------+-----------+----------+--------+<br />5 rows in set (0.00 sec)<br />
</h6>
OK, seems like we got it right so far. Then let's try an UPDATE and raise the salary for the two developers in Department 20:<br />
<h6>
$ sqlplus anders/anders<br />
SQL> UPDATE emp SET sal = sal + 100 WHERE deptno = 20;<br />
</h6>
Now, what does this result in? If this was MariaDB with STATEMENT binlog format, the binlog would have something like this in it:<br />
<h6>
# at 9535<br />#160919 13:00:44 server id 112 end_log_pos 9652 CRC32 0x987f9dea Query thread_id=101 exec_time=0 error_code=0<br />SET TIMESTAMP=1474282844/*!*/;<br />UPDATE emp SET sal = sal + 100 WHERE deptno = 20<br />/*!*/;<br />
</h6>
We've seen this before, no surprises there. Now, let's try this from SQL*Plus and see what the SQL_REDO column in V$LOGMNR_CONTENTS looks like.<br />
<h6>
update "ANDERS"."EMP" set "SAL" = '12100' where "SAL" = '12000' and ROWID = 'AAAE7RAAEAAAAK9AAB';<br />
update "ANDERS"."EMP" set "SAL" = '11100' where "SAL" = '11000' and ROWID = 'AAAE7RAAEAAAAK+AAA';<br />
</h6>
<br />
Whoa, that doesn't look like our UPDATE at all, what's going on? Well, I already told you that the Oracle redo log doesn't work like the binlog. In this case, we get an SQL statement from it, but it is not the same SQL statement that we asked Oracle to execute for us. No, this SQL is reconstructed from the redo log record, which logs updates row by row, and uses the ROWID to identify the row that was changed. We have already discussed that the ROWID is unique in a table and to an extent even acroess tables. The issue now is we don't have a ROWID in MariaDB. But the ROWID in the redo log is available in the ROW_ID column in the V$LOGMNR_CONTENTS table. And if we assume that all tables that we replicate has to have a PRIMARY KEY, then we could use the ROWID to look up that PRIMARY KEY and use that for UPDATE operations in MariaDB, right? Something like this in our script:<br />
<h6>
SELECT empno INTO v_empno FROM anders.emp WHERE rowid = v_row_id;<br />
</h6>
That works but there is one issue with it. We get out data from the redo log, which is historic, so to speak, but we look at the PRIMARY KEY using the ROW ID based on how data looks right now, which means that if you are in the habit of updating your PRIMARY KEYs (don't do this, by the way, this is a bad habit even if we even if we exclude the specific use case we look at here), then this isn't going to fly. Sorry then, I will not fix that for you, if you update a row and then update it's primary key, the first update will fail.<br />
<br />
Then let's look at DELETEs. Can we use the same method as used for UPDATEs, it's tempting, I know, but it is not going to work at all. Think about it, we just determined that we get the ROWID for a row that is historic, but in the case of a DELETE there is no "current row" as we DELETEd it? Right? Whoa, how do we solve that? Are we stuck now, out in the desert, the hot Oracle sun is shining on our unprotected pale skin and it's getting hotter and hotter. Our old Studebaker has broken down by the side of the abandoned dirt road, and you stare into the unforgiving sun and you know this is it?<br />
<br />
Nope. This can be fixed, luckily! The Oracle redo log contains redo data, that is what we have been using so far. The thing is that it also contains undo data! Hey! And then we think about this amazing concept a bit and consider what is necessary to undo a delete? Frankly, the value of ALL columns in the deleted row, right? Our friendly DBMS_LOGMNR.MINE_VALUE function comes to the rescue, but this time not to operate on the redo value but on the undo value!<br />
<br />
Armed with all this, we can now revisit out <i>cdcemp.sql</i> script and add stuff to handle UPDATE and DELETE operations also:<br />
<h6>
SET serveroutput ON<br />
SET linesize 4000<br />SET feedback off<br />DECLARE<br /> v_scn NUMBER;<br /> v_scnstart NUMBER;<br /> v_scnend NUMBER;<br /> v_redo_value NUMBER;<br /> v_undo_value NUMBER;<br /> v_operation VARCHAR2(32);<br /> v_row_id VARCHAR2(18);<br /> v_sqlstmt VARCHAR2(4000);<br /> v_firstcol BOOLEAN;<br /> v_empno NUMBER;<br /> CURSOR cur1 IS<br /> SELECT scn, redo_value, undo_value, operation, row_id<br /> FROM v$logmnr_contents<br /> WHERE seg_owner = 'ANDERS' AND table_name = 'EMP'<br /> AND operation IN('INSERT', 'UPDATE', 'DELETE');<br />BEGIN<br />-- Start LogMiner.<br /> SELECT NVL(MIN(l.scn) + 1, MIN(e.ora_rowscn)) INTO v_scnstart<br /> FROM anders.emp e, scnlog l<br /> WHERE l.table_name = 'EMP';<br /> SELECT MAX(e.ora_rowscn) INTO v_scnend<br /> FROM anders.emp e;<br /> DBMS_OUTPUT.PUT_LINE('-- SCN Range: ' || v_scnstart || ' - ' || v_scnend);<br /> DBMS_LOGMNR.START_LOGMNR(STARTSCN => v_scnstart,<br /> ENDSCN => v_scnend,<br /> OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.CONTINUOUS_MINE +<br /> DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);<br />-- Dummy update to insert a more recent SCN to ensure that we don't have to wait<br />-- for the next update in the following select.<br /> UPDATE scnlog SET table_name = table_name WHERE table_name = 'EMP';<br /> COMMIT;<br /><br />-- Open cursor to get data from LogMiner.<br /> OPEN cur1;<br /><br />-- Loop for all the rows in the redo log since the last time we ran this.<br /> LOOP<br /> FETCH cur1 INTO v_scn, v_redo_value, v_undo_value, v_operation, v_row_id;<br /> EXIT WHEN cur1%NOTFOUND;<br /> IF v_operation = 'INSERT' THEN<br /> v_sqlstmt := 'INSERT INTO emp(EMPNO, ENAME, JOB, SAL, DEPTNO) VALUES(';<br /> v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.EMPNO') || ', ';<br /> v_sqlstmt := v_sqlstmt || '''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.ENAME') || ''', ';<br /> v_sqlstmt := v_sqlstmt || '''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.JOB') || ''', ';<br /> v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.SAL') || ', ';<br /> v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.DEPTNO') || ')';<br /> ELSIF v_operation = 'UPDATE' THEN<br /> v_sqlstmt := 'UPDATE emp SET ';<br /> v_firstcol := TRUE;<br /><br />-- Build UPDATE statement using only column in redo log record.<br /> IF DBMS_LOGMNR.COLUMN_PRESENT(v_redo_value, 'ANDERS.EMP.EMPNO') = 1 THEN<br /> v_sqlstmt := v_sqlstmt || 'EMPNO = ' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.EMPNO');<br /> v_firstcol := FALSE;<br /> END IF;<br /> IF DBMS_LOGMNR.COLUMN_PRESENT(v_redo_value, 'ANDERS.EMP.ENAME') = 1 THEN<br /> v_sqlstmt := v_sqlstmt || CASE WHEN NOT v_firstcol THEN ', ' ELSE '' END;<br /> v_sqlstmt := v_sqlstmt || 'ENAME = ''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.ENAME')<br /> || '''';<br /> v_firstcol := FALSE;<br /> END IF;<br /> IF DBMS_LOGMNR.COLUMN_PRESENT(v_redo_value, 'ANDERS.EMP.JOB') = 1 THEN<br /> v_sqlstmt := v_sqlstmt || CASE WHEN NOT v_firstcol THEN ', ' ELSE '' END;<br /> v_sqlstmt := v_sqlstmt || 'JOB = ''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.JOB')<br /> || '''';<br /> v_firstcol := FALSE;<br /> END IF;<br /> IF DBMS_LOGMNR.COLUMN_PRESENT(v_redo_value, 'ANDERS.EMP.SAL') = 1 THEN<br /> v_sqlstmt := v_sqlstmt || CASE WHEN NOT v_firstcol THEN ', ' ELSE '' END;<br /> v_sqlstmt := v_sqlstmt || 'SAL = ' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.SAL');<br /> v_firstcol := FALSE;<br /> END IF;<br /> IF DBMS_LOGMNR.COLUMN_PRESENT(v_redo_value, 'ANDERS.EMP.DEPTNO') = 1 THEN<br /> v_sqlstmt := v_sqlstmt || CASE WHEN NOT v_firstcol THEN ', ' ELSE '' END;<br /> v_sqlstmt := v_sqlstmt || 'DEPTNO = ' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.DEPTNO');<br /> v_firstcol := FALSE;<br /> END IF;<br /><br />-- Get empno from emp using rowid.<br /> SELECT MAX(empno) INTO v_empno FROM anders.emp WHERE rowid = v_row_id;<br /> IF v_empno IS NULL THEN<br /> CONTINUE;<br /> END IF;<br /> v_sqlstmt := v_sqlstmt || ' WHERE empno = ' || v_empno;<br /> ELSIF v_operation = 'DELETE' THEN<br />-- Get the empno from the undo record.<br /> v_empno := DBMS_LOGMNR.MINE_VALUE(v_undo_value, 'ANDERS.EMP.EMPNO');<br /> IF v_empno IS NULL THEN<br /> CONTINUE;<br /> END IF;<br /> v_sqlstmt := 'DELETE FROM emp WHERE empno = ' || v_empno;<br /> END IF;<br /><br /> DBMS_OUTPUT.PUT_LINE('-- SCN = ' || v_scn);<br /> DBMS_OUTPUT.PUT_LINE(v_sqlstmt || ';');<br /> END LOOP;<br /> IF v_scn IS NOT NULL THEN<br /> UPDATE scnlog SET scn = v_scn WHERE table_name = 'EMP';<br /> COMMIT;<br /> END IF;<br /> CLOSE cur1;<br />END;<br />/<br />EXIT<br />
</h6>
<br />
Let's try this script, and see if the UPDATE to the sal column that I did at the beginning of this post are properly replicated to MariaDB:<br />
<h6>
$ sqlplus -S / as sysdba @cdcemp.sql | mysql -u anders -panders anders<br />
</h6>
And then we check what we have in MariaDB:<br />
<h6>
$ mysql -u anders -panders anders<br />MariaDB [anders]> SELECT * FROM emp;<br />
+-------+----------+-----------+----------+--------+<br />| empno | ename | job | sal | deptno |<br />+-------+----------+-----------+----------+--------+<br />| 1 | Anders | Sales | 10000.00 | 10 |<br />| 2 | John | Developer | 12100.00 | 20 |<br />| 3 | Georgina | Design | 11100.00 | 20 |<br />| 4 | Anne | Assistant | 8000.00 | 30 |<br />| 5 | Marge | HR Mgr | 14000.00 | 30 |<br />+-------+----------+-----------+----------+--------+<br />5 rows in set (0.00 sec)<br />
</h6>
And then see if we can also handle a DELETE. In SQL*Plus we run this:<br />
<h6>
SQL> DELETE FROM emp WHERE ename = 'John';<br />
SQL> COMMIT;<br />
</h6>
The we run out script:<br />
<h6>
$ sqlplus -S / as sysdba @cdcemp.sql | mysql -u anders -panders anders<br />
</h6>
And then we check what we have in MariaDB:<br />
<h6>
$ mysql -u anders -panders anders<br />MariaDB [anders]> SELECT * FROM emp;<br />
+-------+----------+-----------+----------+--------+<br />| empno | ename | job | sal | deptno |<br />+-------+----------+-----------+----------+--------+<br />| 1 | Anders | Sales | 10000.00 | 10 |<br />| 3 | Georgina | Design | 11100.00 | 20 |<br />| 4 | Anne | Assistant | 8000.00 | 30 |<br />| 5 | Marge | HR Mgr | 14000.00 | 30 |<br />+-------+----------+-----------+----------+--------+<br />4 rows in set (0.00 sec)<br />
</h6>
<br />
Bazinga, it worked! Are we done then? I'm afraid not, we still have transactions to take care of, but that has to wait a day or two.<br />
<br />
Happy SQL'ing<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-19215203034906269052016-09-16T16:28:00.000+02:002016-09-16T16:28:38.245+02:00 Replication from Oracle to MariaDB - Part 2In <a href="http://karlssonondatabases.blogspot.se/2016/09/replication-from-oracle-to-mariadb-part.html" target="_blank">part 1</a> on this series of blogs on how to replcate from Oracle to <a href="http://www.mariadb.com/" target="_blank">MariaDB</a>, we looked at how to get data out from Oracle, and in an effort to look more modern than I really am, let's say that what we were attempting was CDC (Change Data Capture). Hey, I feel like I'm 18 again! Modern, cool! Maybe I should start to wear a baseball cap backwards and have my jeans no higher than my knees. Or again, maybe not. But CDC it is.<br />
<br />
When I left you in the previous post we have just started to get some data. reluctantly, out of Oracle. But the SQL statement we got from the LogMiner was hardly useful. But fact is, we can fix that. Before we do that we need to look at a few other functions in LogMiner. The two functions we want to loot at are called <i>DBMS_LOGMNR.COLUMN_PRESENT</i> and <i>DBMS_LOGMNR.MINE_VALUE</i>. These two functions are simple enough, they both take two arguments, first a reference to a <i>redo log</i> record, which is the value of the column <i>redo_value</i> in the <i>V$LOGMNR_CONTENTS</i> table, and the other argument is the fully qualified name of the table in question. The table name of the <i>redo log</i> record is, surprisingly, in the <i>table_name</i> column. The reason we need two functions for this is that the second of these functions can return NULL, both when the column is question doesn't exist and well as when the value is NULL.<br />
<br />
I will for the rest of this post assume that there exists an Oracle user called <i>anders </i>with the password <i>anders</i> and that the same thing exists in MariaDB, where there is also a database called <i>anders</i> (you already guess that, right?) where the user <i>anders</i> has full access.<br />
<br />
So with the table_name of the value of the involved columns, we are ready to go. Sure. let's look at an insert. First we need a table to test this on, and let's use the classic Oracle EMP sample table for this, but I have simplified this a bit to make my example easier to grasp without getting into too many details. So this is what it looks on the Oracle side of things, and let's call this script <i>cretabora.sql</i>:<br />
<h6>
CREATE TABLE anders.emp(<br /> empno NUMBER NOT NULL PRIMARY KEY,<br /> ename VARCHAR2(10) NOT NULL,<br /> job VARCHAR2(9) NOT NULL,<br /> sal NUMBER(7,2) NOT NULL,<br /> deptno NUMBER NOT NULL) TABLESPACE users;<br />
</h6>
What we also need is some way to keep track of processing, in this case we want to keep track of the SCN that was last used, so we know where to start when we get the next chunk of CDCs (He, I used it again. Boy am I young and hot today). And add this to the script above:<br />
<h6>
CREATE TABLE scnlog(<br /> table_name VARCHAR2(30) NOT NULL PRIMARY KEY,<br /> scn NUMBER) TABLESPACE users;<br />INSERT INTO anders.scnlog<br /> SELECT 'EMP', current_scn FROM v$database;<br />
</h6>
I also allocated space for the EMP table and set the SCN to the current database SCN. As I am using the V$DATABASE virtual table, I have to run the above as a privileged user.<br />
<h6>
$ sqlplus / as sysdba @cretabora.sql<br />
</h6>
<br />
OK, now I have some tables to work with. Before to go on let's also create the same objects in a MariaDB database. The minimum thing we need is something is, let's call this script <i>cretabmaria.sql</i>:<br />
<h6>
CREATE TABLE emp(<br /> empno INTEGER NOT NULL PRIMARY KEY,<br /> ename VARCHAR(10) NOT NULL,<br /> job VARCHAR(9) NOT NULL,<br /> sal DECIMAL(7,2) NOT NULL,<br /> deptno INTEGER NOT NULL); <br />
</h6>
I will not explain this in detail, you get it already I think:<br />
<h6>
$ mysql -u root<br />
MariaDB [(none)]> CREATE USER 'anders'@'%' IDENTIFIED BY 'anders';<br />
MariaDB [(none)]> CREATE DATABASE anders;<br />
MariaDB [(none)]> GRANT ALL ON anders.* TO 'anders'@'%';<br />
MariaDB [(none)]> exit<br />
$ mysql -u anders -panders anders < cretabmaria.sql<br />
</h6>
The above really shows that MariaDB is easier to deal with than Oracle, right? But that wasn't what we were looking for here, and we're getting closer now, aren't you excited?<br />
<br />
Let's insert a single row of data in the EMP table, just to get started:<br />
<h6>
$ sqlplus anders/anders<br />
SQL> INSERT INTO emp VALUES(1, 'Anders', 'Sales', 10000, 10);<br />
SQL> COMMIT;<br />
</h6>
<br />
Now we are ready to extract data from the redo log. For this we will run a small script that runs LogMiner, extracts rows from the redo log and converts that to valid MariaDB SQL syntax. Create a script called <i>cdcemp.sql</i> with this content:<br />
<h6>
SET serveroutput ON<br />
SET linesize 4000<br />
SET feedback off<br />
DECLARE<br />
v_scn NUMBER;<br />
v_scnstart NUMBER;<br />
v_scnend NUMBER;<br />
v_redo_value NUMBER;<br />
v_sqlstmt VARCHAR2(4000);<br />
CURSOR cur1 IS<br />
SELECT scn, redo_value<br />
FROM v$logmnr_contents<br />
WHERE seg_owner = 'ANDERS' AND table_name = 'EMP'<br />
AND operation = 'INSERT';<br />
BEGIN<br />
-- Start LogMiner.<br />
SELECT NVL(MIN(l.scn) + 1, MIN(e.ora_rowscn)) INTO v_scnstart<br />
FROM anders.emp e, scnlog l<br />
WHERE l.table_name = 'EMP';<br />
SELECT MAX(e.ora_rowscn) INTO v_scnend<br />
FROM anders.emp e;<br />
DBMS_OUTPUT.PUT_LINE('-- SCN Range: ' || v_scnstart || ' - ' || v_scnend);<br />
DBMS_LOGMNR.START_LOGMNR(STARTSCN => v_scnstart,<br />
ENDSCN => v_scnend,<br />
OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY +<br /> DBMS_LOGMNR.CONTINUOUS_MINE +<br />
DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);<br />
-- Dummy update to insert a more recent SCN to ensure<br />
-- that we don't have to wait for the next update in the<br />
-- following select.<br />
UPDATE scnlog SET table_name = table_name WHERE table_name = 'EMP';<br />
COMMIT;<br />
<br />
-- Open cursor to get data from LogMiner.<br />
OPEN cur1;<br />
<br />
-- Loop for all the rows in the redo log since the last time we ran this.<br />
LOOP<br />
FETCH cur1 INTO v_scn, v_redo_value;<br />
EXIT WHEN cur1%NOTFOUND;<br />
v_sqlstmt := 'INSERT INTO emp(EMPNO, ENAME, JOB, SAL, DEPTNO)'<br />
|| 'VALUES(';<br />
v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value,<br />
'ANDERS.EMP.EMPNO') || ', ';<br />
v_sqlstmt := v_sqlstmt || '''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value,<br />
'ANDERS.EMP.ENAME') || ''', ';<br />
v_sqlstmt := v_sqlstmt || '''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value,<br />
'ANDERS.EMP.JOB') || ''', ';<br />
v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value,<br />
'ANDERS.EMP.SAL') || ', ';<br />
v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value,<br />
'ANDERS.EMP.DEPTNO') || ')';<br />
<br />
DBMS_OUTPUT.PUT_LINE('-- SCN = ' || v_scn);<br />
DBMS_OUTPUT.PUT_LINE(v_sqlstmt || ';');<br />
END LOOP;<br />
IF v_scn IS NOT NULL THEN<br />
UPDATE scnlog SET scn = v_scn WHERE table_name = 'EMP';<br />
COMMIT;<br />
END IF;<br />
CLOSE cur1;<br />
END;<br />
/<br />
EXIT<br />
</h6>
<br />
The first three lines in this script are SQL*Plus specific settings. Then we declare some variables and a cursor for the <i>V$LOGMNR_CONTENTS</i> table. Following that we get the starting and ending SCN that we want and use that to start LogMiner and output some information on what we are doing. Then comes a wacko UPDATE. This seems to be necessary to ensure that the <i>redo log</i> is progressed past the last log record so that we can get the data. Without this the SELECT from the cursor would wait until someone else generated some redo log content. This is a kludge, I know, but it seems to work. Also, Oracle isn't smart enough to skip these kinds of dummy updates.<br />
<br />
Following this the cursor is opened and we get the rows from it and build a suitable SQL statement for MariaDB. Finally when the loop is over I update the SCN log table so I know where to start the next run. This script can be done to generate incremental updates from Oracle to be inserted into MariaDB. And with all that, let's now finally run the script, and here I will run it and extract the data to MariaDB:<br />
<h6>
$ sqlplus -S / as sysdba @cdcemp.sql | mysql -u anders -panders anders<br />
</h6>
<br />
Before we finish, let's check if this worked:<br />
<h6>
$ mysql -u anders -panders anders<br />
MariaDB [anders]> SELECT * FROM emp;<br />
+-------+--------+-------+----------+--------+<br />| empno | ename | job | sal | deptno |<br />+-------+--------+-------+----------+--------+<br />| 1 | Anders | Sales | 10000.00 | 10 |<br />+-------+--------+-------+----------+--------+<br />1 row in set (0.00 sec)<br />
</h6>
<br />
That's it for this time. There are few few more things to look into though, like managing transactions on the MariaDB side for example. And UPDATEs and DELETEs, those require some special care. But we are getting there now, right? So stay tuned for the third post in this series.<br />
<br />
Happy SQL'ing<br />
/Karlsson<br />
<br />
<br />
<br />Karlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com15tag:blogger.com,1999:blog-9144505959002328789.post-75725037454768424852016-09-15T09:27:00.000+02:002016-09-15T09:52:33.897+02:00Replication from Oracle to MariaDB - Part 1If you have read my blog before, you know that I have shown a couple
of ways of getting data from an Oracle database and into something more
approachable, such as a text-file or even, surprise, <a href="http://www.mariadb.com/" target="_blank">MariaDB</a> (or MySQL
for that matter). Not that the Oracle database really likes you doing
this and some tweaking is necessary. One way I have shown before to do
this is by using triggers, a <a href="https://mariadb.com/kb/en/mariadb/user-defined-functions/" target="_blank">UDF</a>, UPD-communication and a simple server,
this was a series of blogposts starting with this one: <a href="https://karlssonondatabases.blogspot.se/2014/09/replication-from-oracle-to-mariadb.html" target="_blank">Replication from Oracle to MariaDB the simple way</a>.
Another means is to exports data from Oracle in some text format,
something Oracle really doesn't like you do it, it really likes to keep
the data to itself, but yours truly worked around this and now you can
use the <a href="https://karlssonondatabases.blogspot.se/2015/06/oracle-dump-utility.html" target="_blank">Oracle dump utility</a> and this has been developed quite a bit and now works really well and for most standard Oracle datatypes.<br />
<br />
But
replicating properly, without triggers or anything, would be nice to do using only Open Source stuff, or maybe we can do without any special tools at all? One way would be to put a
proxy in front of Oracle. Useful, but takes too much time to develop and
requires that one figure out the Oracle communication protocol, which
is not something I am prepared to do (I am lazy, I know).<br />
<br />
So this time around I will try something different, a way to replicate straight out of Oracle. I will eventually show you a practical example of doing this, without too much code, but before we get there I'll explain how it works. The more difficult part here is getting the data replicated properly out from Oracle, so this first installment of a series of blog post will be more on Oracle than MariaDB specifically, but this is all for a good cause (i.e. get stuff done without having to pay) so let's get started.<br />
<br />
To begin with, there is no <a href="https://mariadb.com/kb/en/mariadb/binary-log/" target="_blank"><i>binlog</i></a> in Oracle (nor is there an <i>update log</i>, if you remember that puppy). What Oracle does have though is a <i>redo log</i>, which is also there in <i>InnoDB / XtraDB</i> by the way, and it serves the same purpose, to be able to recover the database should there be a crash. This log is, because of the purpose it is used for, logging on a much lower level compared to the binlog. Looking at the Oracle redo log, there are log records on any modifying event in the database, including a lot of internal Oracle processing, Oracle does do a lot of stuff in the background and manages a lot more stuff that MariaDB instead leaves to the operating system, like file space management and such things. This is not to say Oracle is better than MariaDB in this respect, nor the other way around, but Oracle was born at a time where you had to support so many different operating systems, so some layers were necessary to create some transparency. Also Oracle was born at a time when having 5Mb of disk space was a lot of disk and when real estate was something that you handled conservatively (which is a giveaway of my age. Yes, I was born before "My Boy Lollipop" was a hit with Mille Small. And contrary to popular belief, it is not Rod Stewart that plays the harmonica solo in that song. And that ends todays "useless knowledge" lesson).<br />
<br />
Before we go into looking at the Oracle redo log in detail, there are a couple of things I want to explain first. There is another file in Oracle, a small but important one called the control file. The control file is what keeps track of all the other files and related settings in the database. As for the redo log files, these can be used in two ways. The basic way is what is called <a href="https://docs.oracle.com/cd/B28359_01/server.111/b28310/archredo002.htm" target="_blank">NOARCHIVELOG</a> mode, which means that the log files are reused. This is similar to how the InnoDB / XtraDB logfiles are used, and the purpose of this is to provide a means to recover after a crash. Thinking about it, you realize that these should then also be able to be used for, say, point in time recovery, assuming that we saved them, i.e. starting with a physical backup and then applying the saved files. With MariaDB we instead perform point in time recovery using the binlog. To do a point in time recovery with Oracle, and use the redo log for this purpose, we have to save the redo log files, and this is what happens when you run in ARCHIVELOG mode, in which case a separate Oracle process will "archive", which is Oracle lingo for "saving in some other place", the redo log files when they are full and a switch is done to the next logfile. All of this, the current and archived redo log files, are recorded in the Oracle control file.<br />
<br />
OK, A lot of Oracle stuff, and I warned you, but this is critical to understanding how the code I present later works. There is one more thing I want to discuss briefly which is the Oracle ROWID. A ROWID in oracle is a unique identifier of a row in a table. The ROWID is actually a physical adress and there are cases when the same ROWID can be used for two different tables, which is when the tables are clustered, but for a single table the ROWID uniquely identifies a row. To use the ROWID of a table, one can use the ROWID pseudocolumn, which can be used in the WHERE clause as well as in the SELECT list.<br />
<br />
So, with all those things explained, lets put it all together. The <i>redo log</i> of Oracle contains all the changes to the database, so it really should be possible to use this for the same purpose that we use the <i>binlog</i> in MariaDB, i.e. replication. To do that we first need to be able to read the <i>redo log</i>, and we could either read the redo log file itself, but this is rather complex and I'm not even sure this really is a good idea, but if you insist you can read <a href="https://www.nccgroup.trust/globalassets/our-research/uk/whitepapers/oracle_forensics_part_1._dissecting_the_redo_logs.pdf" target="_blank">this document</a> for example. Instead we are going to use an Oracle utility. For the DML that interests us, all we need is the INSERT, UPDATE and DELETE statements that deal with the table we want to replicate and then the COMMIT when the transaction in question ends. For these operations, some of the data we want that is in the <i>redo log</i> are:<br />
<ul>
<li>The type of operation</li>
<li>An identifier of the table (not the actual table name though) </li>
<li>The changed columns and values</li>
<li>The ROWID of the row in question</li>
<li>And the SCN (System Change Number).</li>
</ul>
And then I have to describe the SCN I guess. The SCN is simple, it's a 48-bit integer value that identified the transaction and that is incrementing for each transaction. You can get the SCN of the last change in a table, which is actually the SCN of the block where the row exists (but this you can change to track the actual ROW. Oracle has sooo many strange options to cater for all the special needs of it's users).<br />
<br />
Before I finish this blog post, I want to show some code on how to get data from the redo log. And before you ask, no, there is no <i>oraredolog</i> utility or something like that, this isn't MariaDB, this is Oracle. A utility in Oracle is mostly not a utility per se, but rather a Oracle PL/SQL package, in this case this is LogMiner, or as a package DBMS_LOGMNR. For LogMiner to work we need to run in archivelog mode, so if you have a fresh Oracle installation, log in as a priviledged user:<br />
<h6>
$ sqlplus / as sysdba<br />
</h6>
And run the commands<br />
<h6>
SQL> SHUTDOWN IMMEDIATE;<br />
SQL> STARTUP MOUNT; <br />
SQL> ALTER DATABASE ARCHIVELOG;<br />
SQL> ALTER DATABASE OPEN;<br />
</h6>
Also, to get all the data we want from LogMiner we have to add "supplemental data" to the redo log. What this means exactly I haven't looked in to but trust me, you want it. So from the same SQL*Plus session, run:<br />
<h6>
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;<br />
</h6>
<br />
With that in place we are ready to start mining the log, this consists of two steps, first we start logminer itself, and then we select from a table that logminer populates. Let's look at how we can start logminer with an sql script, let's call this script <i>startlog.sql</i>:<br />
<h6>
column min_scn new_value startscn<br />column current_scn new_value endscn<br />SELECT MIN(FIRST_CHANGE#) min_scn FROM v$log;<br />SELECT current_scn FROM v$database;<br />EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTSCN => &startscn, -<br /> ENDSCN => &endscn, -<br /> OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY + -<br /> DBMS_LOGMNR.CONTINUOUS_MINE + -<br /> DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);<br />SET ARRAYSIZE 1<br />
</h6>
And then we run this script and as there is no explicit exit in it, you get the SQL*Plus prompt back after running it, which is not how the MariaDB command line works:<br />
<h6>
$ sqlplus / as sysdba @startlog.sql<br />
</h6>
And now we can get some log data, let's say we have a table called T1 that we have done some changes to, then we can get the relevant records like this:<br />
<h6>
SQL> SELECT sql_redo FROM v$logmnr_contents WHERE table_name = 'T1' AND seg_owner = 'ANDERS' AND operation IN ('INSERT', 'UPDATE', 'DELETE');<br />
SQL_REDO<br />--------------------------------------------------------------------------------<br />insert into "ANDERS"."T1"("C1","C2") values ('1','2');<br />insert into "ANDERS"."T1"("C1","C2") values ('2','2');<br />update "ANDERS"."T1" set "C2" = '3' where "C2" = '2' and ROWID = 'AAAE5KAAEAAAAFfAAA';<br />update "ANDERS"."T1" set "C2" = '3' where "C2" = '2' and ROWID = 'AAAE5KAAEAAAAFfAAB';<br />
</h6>
<br />
This hardly looks complete? But there is something in there that we possibly could make some sense from, right? Let's try that in the next blog post in this series. And this post has been all about Oracle, more or less, now in the next post we will get closer to replicating to MariaDB, which is where we want our data to reside anyway? Right?<br />
<br />
Happy SQL'ing<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-13796965946428635872016-08-29T11:18:00.000+02:002016-08-29T16:14:53.523+02:00Creating a MariaDB MaxScale router moduleI wanted to do some tests with MariaDB MaxScale and realized that the two existing routers (beyond the binlog router that is, which is a bit special) didn't do what I wanted them to do. What I was looking for was a simple round-robin feature and none of readconnroute nor readwritesplit could be configured to do this, they are just too smart for my simple experiment.<br />
<br />
Why would you want a round-robin router then? Well, one use case is when you are INSERTing a lot of data and you just want to persist it. You don't have the use case where you have to SELECT data from all servers, but in the case you need it, you just select from all servers until you find what you need. Let's think about log data that you don't care much about but that you for some reason need to retain, maybe for corporate policy reasons or legal reasons. Using round-robin could, in theory, give you better performance, but that would require something way smarter than what I am proposing here. Rather, you get INSERT availability, i.e. you will always have some server to insert into and secondly, you get INSERT sharding, which is basic but useful, you only store so much data on each server.<br />
<br />
So, let's get to work. To begin with you need the MaxScale source tree, place yourself in some directory where you want this and do this:<br />
<h6>
$ git clone https://github.com/mariadb-corporation/MaxScale.git<br />
</h6>
Now you should have a directory called MaxScale, so pop in there are, create a build directory and then run cmake to configure MaxScale itself:
<br />
<h6>
$ cd MaxScale<br />
$ mkdir build<br />
$ cd build<br />
$ cmake ..<br />
$ make<br />
</h6>
These are the quick instructions and you will probably find that you lack some dependencies. The full instructions for how to do this is available as part of the sample code as presented later in this document, and that is available from <a href="https://sourceforge.net/" target="_blank">Sourceforge</a>. Browse to <a href="https://sourceforge.net/projects/mxsroundrobin/files">https://sourceforge.net/projects/mxsroundrobin/files</a> and then click on <i>roundrobin 1.0</i> where you find a pdf with detailed instructions. Also there is a tgz there will all the sourcecode presented late in this blog.<br />
<br />
So, now we have something to work with and the plan is to introduce a new router module in this tree. To begin with pop over to where routers module code is and create a directory for our code there:<br />
<h6>
$ cd ../server/modules/routing<br />
$ mkdir roundrobin<br />
$ cd roundrobin<br />
</h6>
Before we can start building some code, let's look at the basics of what kind of code gets into a module.<br />
<br />
A plugin is a shared object that is loaded by MaxScale core when it starts. Early on when MaxSCale starts it reads the configuration file, <i>/etc/maxscale.cnf</i> by default, and in there each service defines a router. Note that several services can use the same router so our code we write later has to take this into account. Look at this extract of a service section for example:<br />
<h6>
[Read-Write Service]<br />
type=service<br />
router=readwritesplit<br />
</h6>
The router here tells MaxScale to look for a <i>readwritesplit</i> module, or in technical terms, it will load the shared library: <i>libreadwritesplit.so</i>. After loading this library successfully, MaxScale has to figure out a few things about this module, like it's name and version and above all, the entry points for the functions that MaxScale will call when processing a connection. In addition we need to define a few structs that are passed around these different calls to give the different router functions some context. Lets start with a header file <i>rooundrobin.h </i>in the <i>roundrobin </i>directory:<br />
<h6>
#ifndef ROUNDROBIN_H<br />
#define ROUNDROBIN_H<br />
#include <server.h><br />
<br />
typedef struct tagROUNDROBININST *PROUNDROBININST;<br />
<br />
typedef struct tagROUNDROBIN_CLIENT_SES {<br />
SPINLOCK lock;<br />
bool bClosed;<br />
SERVER **pBackends;<br />
SESSION *pSession;<br />
DCB **pdcbClients;<br />
unsigned int nBackends;<br />
unsigned int nCurrBackend;<br />
PROUNDROBININST pRouter;<br />
struct tagROUNDROBIN_CLIENT_SES *pNext;<br />
} ROUNDROBIN_CLIENT_SES, *PROUNDROBIN_CLIENT_SES;<br />
<br />
typedef struct tagROUNDROBININST {<br />
SERVICE *pService;<br />
PROUNDROBIN_CLIENT_SES pConnections;<br />
SPINLOCK lock;<br />
SERVER **pBackends;<br />
unsigned int nBackends;<br />
struct tagROUNDROBININST *pNext;<br />
} ROUNDROBININST;<br />
#endif<br />
</h6>
As you can see, the main thing here is that I define and typedef two structs. As I said, I have mostly been looking at other existing routers and grabbed the stuff in there, so I can't explain all aspects of these structs, but let's look at a few members:<br />
<ul>
<li>These structs are in a linked list and the <i>pNext </i>member is a pointer to the next element in this list. </li>
<li>The <i>lock</i> members is a reference to a spinlock associated with the struct.</li>
<li>The <i>pBackends</i> member is a pointer to an array of pointers to the database SERVERS that this service is attached to.</li>
<li>The <i>pbcdClients</i> member is an array of pointers to DCDs. A DCB is the Descriptor Control Block which is a generic descriptor of a connection inside MaxScale, be it a server or a client. In this case this is the DCBs to the SERVERs in <i>pBackends</i>.</li>
<li>The<i> nBackends</i> is the number of elements in the <i>pBackends</i> and <i>pdcbClients</i> arrays.</li>
<li>The <i>pRouter </i>member is a pointer to the <i>ROUNDROBININST</i> for the connection.</li>
</ul>
That is the most of that, the next step now is to start with the more exiting stuff of the actual code take make up this module. The main source file we work with here is <i>roundrobin.c</i> and we need a few basics in this. Let's have a look the beginning of <i>roundrobin.c</i>:<br />
<h6>
#include <my_config.h><br />
#include <router.h><br />
#include <query_classifier.h><br />
#include <mysql_client_server_protocol.h=><br />
#include "roundrobin.h"<br /><br />/* Macros. */<br />#define ROUNDROBIN_VERSION "1.0.0"<br /><br />/* Globals. */<br />MODULE_INFO info = {<br /> MODULE_API_ROUTER,<br /> MODULE_GA,<br /> ROUTER_VERSION,<br /> "A simple roundrobin router"<br />};<br />static PROUNDROBININST pInstances;<br /><br />/* Function prototypes for API. */<br />static ROUTER *CreateInstance(SERVICE *service, char **options);<br />static void *CreateSession(ROUTER *pInstance, SESSION *session);<br />static void CloseSession(ROUTER *pInstance, void *session);<br />static void FreeSession(ROUTER *pInstance, void *session);<br />static int RouteQuery(ROUTER *pInstance, void *session, GWBUF *queue);<br />static void Diagnostic(ROUTER *pInstance, DCB *dcb);<br />static void ClientReply(ROUTER *pInstance, void *router_session,<br /> GWBUF *queue, DCB *backend_dcb);<br />static void HandleError(ROUTER *pInstance, void *router_session,<br /> GWBUF *errmsgbuf, DCB *backend_dcb, error_action_t action,<br /> bool *succp);<br />static int GetCapabilities();<br /><br />static ROUTER_OBJECT RoundRobinRouter = {<br /> CreateInstance,<br /> CreateSession,<br /> CloseSession,<br /> FreeSession,<br /> RouteQuery,<br /> Diagnostic,<br /> ClientReply,<br /> HandleError,<br /> GetCapabilities<br />};<br />
</h6>
Let's now look at what is going on here. To begin with I include a few necessary files, including <i>roundrobin.h</i> that we created above and then a macro is defined. Then the <i>MODULE_INFO</i> struct follows. The information in this is used by MaxScale to get information on the router, but if you leave this out, currently MaxScale will start anyway. The command <i>show modules</i> in <i>maxadmin </i>will return the information in this struct for the module.<br />
<br />
<br />
Then follows a number of function prototypes, and these are needed here before the <i>ROUTER_OBJECT</i> struct, and this is the key to the router as it provides the entry points for MariaDB itself. Again, I will not specify exactly what all of these do, I have mostly just grabbed code from other routers.<br />
<br />
Following this we need some basic functions that all routers implement, to initialize the module, get the version and a function to return the <i>ROUTER OBJECT</i> defined above:<br />
<h6>
/*<br /> * Function: ModuleInit()<br /> * Initialize the Round Robin router module.<br /> */<br />void ModuleInit()<br /> {<br /> MXS_NOTICE("Initialise roundrobin router module version " ROUNDROBIN_VERSION ".");<br /> pInstances = NULL;<br /> } /* End of ModuleInit(). */<br /><br /><br />/*<br /> * Function: version()<br /> * Get the version of the roundrobin router<br /> */<br />char *version()<br /> {<br /> return ROUNDROBIN_VERSION;<br /> } /* End if version(). */<br /><br /><br />/*<br /> * Function: GetModuleObject()<br /> * Get the object that describes this module.<br /> */<br />ROUTER_OBJECT *GetModuleObject()<br /> {<br /> return &RoundRobinRouter;<br /> } /* End of GetModuleObject(). */<br />
</h6>
With that we have completed the housekeeping code and are ready to look at the functions that implement the actual functionality. We'll look at <i>CreateInstance </i>first which, as the name implies, creates an snstance of RoundRobin. Note that within a running MaxScale there might well be more than one instance, one for each RoundRobin service.<br />
<h6>
/*<br /> * Function: CreateInstance()<br /> * Create an instance of RoundRobing router.<br /> */<br />ROUTER *CreateInstance(SERVICE *pService, char **pOpts)<br /> {<br /> PROUNDROBININST pRet;<br /> PROUNDROBININST pTmp;<br /> SERVER_REF *pSvcRef;<br /> unsigned int i;<br /><br /> MXS_NOTICE("Creating roundrobin router instance.");<br />/* Allocate the RoundRobin instance struct. */<br /> if((pRet = malloc(sizeof(ROUNDROBININST))) == NULL)<br /> return NULL;<br /> pRet->pService = pService;<br /> pRet->pConnections = NULL;<br /> pRet->pNext = NULL;<br /> pRet->nBackends = 0;<br /><br />/* Count the number of backend servers we manage. */<br /> for(pSvcRef = pService->dbref; pSvcRef != NULL; pSvcRef = pSvcRef->next)<br /> pRet->nBackends++;<br /><br />/* Allocate space for the backend servers and add to the instance struct. */<br /> if((pRet->pBackends = calloc(pRet->nBackends, sizeof(SERVER *))) == NULL)<br /> {<br /> free(pRet);<br /> return NULL;<br /> }<br /><br /> spinlock_init(&pRet->lock);<br /><br />/* Set up list of servers. */<br /> for(i = 0, pSvcRef = pService->dbref; pSvcRef != NULL; i++, pSvcRef = pSvcRef->next)<br /> pRet->pBackends[i] = pSvcRef->server;<br /><br />/* Set up instance in list. */<br /> if(pInstances == NULL)<br /> pInstances = pRet;<br /> else<br /> {<br /> for(pTmp = pInstances; pTmp->pNext != NULL; pTmp = pTmp->pNext)<br /> ;<br /> pTmp->pNext = pRet;<br /> }<br /><br /> MXS_NOTICE("Created roundrobin router instance.");<br /> return (ROUTER *) pRet;<br /> } /* End of CreateInstance(). */<br />
</h6>
Again, nothing really exiting is happening, I create a struct that defines the instance, initialize it and add it to the linked list of instances that I maintain. Also I get references to the backend servers that this instance use and set up the array for the and I also initialize the spinlock. With that, we are done. Then there is the issue of creating a session, and this function gets called when a client connects to MaxScale through the port that is linked to RoundRobin.<br />
<h6>
/*<br /> * Function: CreateSession()<br /> * Create a session in the RoundRobin router.<br /> */<br />void *CreateSession(ROUTER *pInstance, SESSION *session)<br /> {<br /> PROUNDROBIN_CLIENT_SES pRet;<br /> PROUNDROBIN_CLIENT_SES pTmp;<br /> PROUNDROBININST pRoundRobinInst = (PROUNDROBININST) pInstance;<br /> unsigned int i;<br /><br />/* Allocating session struct. */<br /> if((pRet = malloc(sizeof(ROUNDROBIN_CLIENT_SES))) == NULL)<br /> return NULL;<br /> spinlock_init(&pRet->lock);<br /> pRet->pNext = NULL;<br /> pRet->nCurrBackend = 0;<br /> pRet->pSession = session;<br /> pRet->pRouter = pRoundRobinInst;<br /> pRet->nBackends = pRoundRobinInst->nBackends;<br /><br />/* Allocating backends and DCBs. */<br /> if((pRet->pBackends = calloc(pRet->nBackends, sizeof(SERVER *))) == NULL)<br /> {<br /> free(pRet);<br /> return NULL;<br /> }<br /> if((pRet->pdcbClients = calloc(pRet->nBackends, sizeof(DCB *))) == NULL)<br /> {<br /> free(pRet->pBackends);<br /> free(pRet);<br /> return NULL;<br /> }<br /><br />/* Set servers and DCBs. */<br /> for(i = 0; i < pRet->nBackends; i++)<br /> {<br /> pRet->pBackends[i] = pRoundRobinInst->pBackends[i];<br /> pRet->pdcbClients[i] = NULL;<br /> }<br /><br />/* Place connecting last in list of connections in instance. */<br /> spinlock_acquire(&pRoundRobinInst->lock);<br />
if(pRoundRobinInst->pConnections == NULL)<br /> pRoundRobinInst->pConnections = pRet;<br /> else<br /> {<br /> for(pTmp = pRoundRobinInst->pConnections; pTmp->pNext != NULL; pTmp = pTmp->pNext)<br /> ;<br /> pTmp->pNext = pRet;<br /> }<br /> spinlock_release(&pRoundRobinInst->lock);<br /><br /> return (void *) pRet;<br /> } /* End of CreateSession(). */<br />
</h6>
This is also pretty basic stuff, the server pointers are copied from the instance (do I need to do this you ask? Answer is, I don't know but I do know that what I do here works). I also clear the DCB pointers, these are created on an as-needed base later in the code.<br />
<br />
Following this are a couple of basic housekeeping functions that I am not showing here, actually I'm just going to show one more function, which is <i>RouteQuery</i>. This is, as the name implies, the function that gets called to do what we are actually writing this code for, routing queries. Before I show that code, I have to explain that this is very simplistic code. To being with, it doesn't implement "session commands", these are commands that really should be run on all backends, like setting the current database, handling transactions and such things. As I said, I do not implement this and this is one of the major shortcomings on this code that makes it much less generally applicable. But it still has use cases. Secondly, I have tried to make sure that the code works, more than optimizing it to death, so maybe I grab the spinlock too often and maybe I am too picky with allocating/deallocating the DCBs, I let others answer that.<br />
<br />
The role of the function at hand is to handle an incoming query and pass it along to one of the servers defined for the service in question. In the general case, the most complicated part of this is selection of which server to route the query to and handling of session commands. I have simplified this by only having a very simple routing algorithm where I store the index of the last used backed for a connection in the <i>nCurrBackend</i> member, and for each query this is incremented until <i>nBackends</i> is reached where it is reset to 0. And for the complexity of session commands, I just don't implement them.<br />
<br />
So, lets have a look at what the <i>RouteQuery </i>function looks like:<br />
<h6>
/*<br /> * Function: RouteQuery()<br /> * Route a query in the RoundRobin router.<br /> */<br />int RouteQuery(ROUTER *instance, void *session, GWBUF *queue)<br /> {<br /> PROUNDROBIN_CLIENT_SES pSession = (PROUNDROBIN_CLIENT_SES) session;<br /> DCB *pDcb;<br /> int nRet;<br /> unsigned int nBackend;<br /><br /> MXS_NOTICE("Enter RoundRobin RouteQuery.");<br /> queue = gwbuf_make_contiguous(queue);<br /><br /> spinlock_acquire(&pSession->lock);<br />/* Check for the next running backend. Set non-running backend DCBs to NULL. */<br /> for(nBackend = pSession->nCurrBackend; nBackend < pSession->nBackends; nBackend++)<br /> {<br />/* If this server is up, then exit this loop now. */<br /> if(!SERVER_IS_DOWN(pSession->pBackends[nBackend]))<br /> break;<br /><br />/* If the server is down and the DCB is non-null, then free the DCB and NULL it now. */<br /> if(pSession->pdcbClients[nBackend] != NULL)<br /> {<br /> dcb_close(pSession->pdcbClients[nBackend]);<br /> pSession->pdcbClients[nBackend] = NULL;<br /> }<br /> }<br />/* If I couldn't find a backend after the current, then look through the ones before. */<br /> if(nBackend >= pSession->nBackends)<br /> {<br /> for(nBackend = 0; nBackend <= pSession->nCurrBackend; nBackend++)<br /> {<br /> if(!SERVER_IS_DOWN(pSession->pBackends[nBackend]))<br /> break;<br /> if(pSession->pdcbClients[nBackend] != NULL)<br /> {<br /> dcb_close(pSession->pdcbClients[nBackend]);<br /> pSession->pdcbClients[nBackend] = NULL;<br /> }<br /> }<br /><br />/* Check that I really found a suitable backend. */<br /> if(nBackend > pSession->nCurrBackend)<br /> {<br /> spinlock_release(&pSession->lock);<br /> MXS_NOTICE("No suitable RoundRobin running server found in RouteQuery.");<br /> return 0;<br /> }<br /> }<br /><br /> pDcb = pSession->pdcbClients[nBackend];<br />/* If backend DCB wasn't set, then do that now. */<br /> if(pDcb == NULL)<br /> pDcb = pSession->pdcbClients[nBackend] = dcb_connect(pSession->pBackends[nBackend],<br /> pSession->pSession,<br /> pSession->pBackends[nBackend]->protocol);<br /> spinlock_release(&pSession->lock);<br /><br />/* Route the query. */<br /> nRet = pDcb->func.write(pDcb, queue);<br /><br />/* Move to next dcb. */<br /> pSession->nCurrBackend = nBackend;<br /> if(++pSession->nCurrBackend >= pSession->nBackends)<br /> pSession->nCurrBackend = 0;<br /><br /> MXS_NOTICE("Exit RoundRobin RouteQuery.");<br /> return 1;<br /> } /* End of RouteQuery(). */<br />
</h6>
So, what is going on here? First I check for a backend, first the ones starting with the current one (which is badly named, this is actually the one after the current) and then until I find a server that is running. If I find a non-Running server I skip that one, after having closed the associated DCB. If I can't find a server after the current one, I start again from the first, processing servers in the same way.<br />
<br />
Following this I should have a server, then I check if the DCB is open, and if not I open it now. After that I do the actual routing of the query, move not the next backend and then return. Simple as that. As I have stated, this is a very simple router, but it does work, within the given limitations, and it should be good enough as a crude example.<br />
<br />
Before I can test my code, I have to set it up for inclusion in the build process and do a few other mundane tasks, but that is all documented in the pdf that comes with the code, download the package from <a href="https://sourceforge.net/projects/mxsroundrobin">Sourceforge</a>.<br />
<br />
Happy SQLing<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-75835209411392863182016-08-10T20:39:00.000+02:002016-08-10T20:39:30.468+02:00Installing MariaDB MaxScale the hard wayIf you are like me (let's for everyones sake hope you are not, though) you like to do things the hard way, in particular when it comes to testing things. For example when installing things on your Linux box, just to try them out, you might not want to do a <i>yum install</i> an <i>rpm -ivh</i> or an <i>apt-get</i> to have some files spread all over your system, instead you want to <i>tar xvf</i> some tarball and possibly, if you are in a good mood or you want to be a nice so you get some gifts for christmas or maybe because it is just that day, you unpack that tarball in <i>/usr/local</i> instead of in <i>/home/bofh/junk</i>. And this will usually get you in some trouble, but as we have already determined that we are truly bad (maybe we should get a tattoo or two also, or is the right to death-metal antics reserved for IT security personel only? Sure seems so) we can ignore that and get to work.<br />
<br />
Here I will show you how to install <a href="https://mariadb.com/products/mariadb-maxscale" target="_blank">MariaDB MaxScale</a> from a tar-ball and get it running, without touching any system directories or anything if you want to test it or if you, even in production, want to install it in some non-standard location (like <i>/usr/local</i>. I actually like to have stuff there, I don't know what's so wrong with that. I'm a rebel, I know).<br />
<br />
To begin with, let's download MariaDB MaxScale tarball (rpm's are for wussies), for example from mariadb.com where you should register and then go to <i>"my portal"->"Downloads"->"MariaDB MaxScale"</i> and download an appropriate <i>.tar.gz</i> for your operating system of choice. In my case I download it for CentOS 6 / RHEL 6 and as the current MariaDB MaxScale version is 1.4.3 I issue the command in my home directory (<span style="font-family: inherit;"><i>/home2/anders</i></span>):<br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">$ wget https://downloads.mariadb.com/enterprise/<my tag>/mariadb-maxscale/1.4.3/rhel/6/x86_64/maxscale-1.4.3-1.rhel.6.x86_64.tar.gz</span></span><br />
With <my tag> replaced by a generated tag on mariadb.com. Following this we are stuck with a tarball named <i>maxscale-1.4.3-1.rhel.6.x86_64.tar.gz</i> and we unpack that as usual and then create a more readable link to the created directory:<br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">$ tar xvfz maxscale-1.4.3-1.rhel.6.x86_64.tar.gz</span></span><br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">$ ln -s maxscale-1.4.3-1.rhel.6.x86_64 maxscale143</span></span><br />
So far nothing magic has happened. The next step is to create a few directories in our new maxscale143 directory where MariaDB MaxScale will keep temporary, stuff, logs etc:<br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">$ cd maxscale143</span></span><br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">$ mkdir cache data log</span></span><br />
<br />
The next step after this is to create a MariaDB MaxScale configuration file. There is a template for this in the <i>etc</i> subdirectory so we just have to copy that:<br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">$ cp etc/maxscale.cnf.template etc/maxscale.cnf</span></span><br />
The supplied config file will start MariaDB MaxScale with just 1 server defined, and unless you have this server running on a non-standard port or on another machine than the one where MariaDB MaxScale itself is running, you can leave this configuration file alone, and if not you have to edit the <i>[server1]</i> section appropriately.<br />
<br />
Another thing to look for is iptables / firewalld settings, but this you already know about I guess. You might want to turn them off (which is not recommended at all) or configure it appropriately. As per the default configuration with MariaDB MaxScale 1.4.3, ports 4006, 4008 and 6603 will be listened to, so you configure iptables / firewalld appropriately. And don't turn them of, do this the right way for once. I turned iptables off by the way, just to annoy you.<br />
<br />
Now, MariaDB MaxScale will connect to the server we defined in the configuration file above, and we need to allow it to connect and execute a few commands. There are two users that MariaDB MaxScale can use, one to connect and get authentication data, like usernames and passwords, and another separate one to monitor the state of the server. In the supplied configuration template these two users use the same account, namely <i>myuser</i> using <i>mypwd</i> as the password, and this is what I use in the following where are set up the appropriate user and grant in the MariaDB server I am connecting to, and also note that I am assuming that MariaDB MaxScale and the MariaDB server on question run on the same node. So connect to MariaDB and issue the following commands:<br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #666666;">MariaDB> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypwd';</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #666666;">MariaDB> GRANT SELECT ON mysql.user TO 'myuser'@'localhost';</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #666666;">MariaDB> GRANT SELECT ON mysql.db TO 'myuser'@'localhost';</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #666666;">MariaDB> GRANT SELECT ON mysql.tables_priv TO 'myuser'@'localhost';</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #666666;">MariaDB> GRANT SHOW DATABASES ON *.*TO 'myuser'@'localhost';</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #666666;">MariaDB> GRANT REPLICATION SLAVE ON *.* TO 'myuser'@'localhost';</span></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #666666;">MariaDB> GRANT REPLICATION CLIENT ON *.* TO 'myuser'@'localhost';</span></span><br />
<br />
With this in place we are ready to start MariaDB MaxScale, but this is an itsy bitsy more complex than you think. The issue is that the default locations for a lot of stuff that MariaDB MaxScale wants to use is somewhere in the global file system, and they are also not relative to some <i>basedir</i> as is conveniently the case with MariaDB server itself. to support this, instead of putting all this in the global section in the MariaDB MaxScale config file I'll instead put any necessary arguments to get MaxScale going on the command line, and for that I have created three scripts, 1 to set up the environment, one to start MariaDB MaxScale and one to stop it. Let's start with the environment one. This is places in the MariaDB MaxScale home directory (maxscale143) is called <i>maxenv.sh</i> and has the following contents:<br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">#!/bin/bash<br />#<br />MAXSCALE_HOME=$(cd $(dirname $BASH_SOURCE) ; pwd)<br />PATH=$PATH:$MAXSCALE_HOME/usr/bin<br />export LD_LIBRARY_PATH=$MAXSCALE_HOME/usr/lib64/maxscale</span></span><br />
<br />
The next file to create is the script to start MariaDB MaxScale, this is called <i>startmax.sh</i>, is again placed in the MariaDB MaxScale root directory and has this content:<br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">#!/bin/bash<br />#<br />. `dirname $0`/maxenv.sh<br /><br />$MAXSCALE_HOME/usr/bin/maxscale \<br /> --config=$MAXSCALE_HOME/etc/maxscale.cnf \<br /> --logdir=$MAXSCALE_HOME/log \<br /> --language=$MAXSCALE_HOME/var/lib/maxscale \<br /> --datadir=$MAXSCALE_HOME/data \<br /> --libdir=$MAXSCALE_HOME/usr/lib64/maxscale \<br /> --piddir=$MAXSCALE_HOME --syslog=no \<br /> --cachedir=$MAXSCALE_HOME/cache</span></span><br />
<span style="font-family: inherit;">As you can see this invokes <i>maxenv.sh</i> before going on to start MariaDB MaxScale. The only parameter that I really don't have to set here, but which I set anyway, again just to be annoying to the world in general, is --syslog=no as we are only testing things here</span> and logging to syslog is then not really appropriate (but it is the default).<br />
<br />
All we need now is script to stop MariaDB MaxScale, and for this create a file called stopmax.sh in the MariaDB MaxScale home directory with this content:<br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">#!/bin/bash<br />#<br />. `dirname $0`/maxenv.sh<br /><br />if [ -e "$MAXSCALE_HOME/maxscale.pid" ]; then<br /> kill -term `cat $MAXSCALE_HOME/maxscale.pid`<br />fi</span></span><br />
<br />
Following this, the one thing that remains to be done is to make the scripts we just created executable:<br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">$ chmod +x maxenv.sh startmax.sh stopmax.sh</span></span><br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #666666;"><span><span style="background-color: #666666;"></span></span></span><br /></span></span>
Now we are ready to try things, let's start MariaDB MaxScale first:<br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">$ ./startmax.sh</span></span><br />
And then let's see if we can connect to the MariaDB server through MariaDB MaxScale:<br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;">$ mysql -h 127.0.0.1 -P 4006 -u myuser -pmypwd</span></span><br />
<span style="background-color: #666666;"><span style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #666666;">Welcome to the MariaDB monitor. Commands end with ; or \g.<br />Your MySQL connection id is 6950<br />Server version: 10.0.0 1.4.3-maxscale MariaDB Server<br /><br />Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.<br /><br />Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.<br /><br />MySQL [(none)]></span> </span></span><br />
As you can see, I am not connecting as root here, as this is not allowed by MariaDB MaxScale by default. Also, I am not connecting to localhost as that assumes I am connecting using a socket, which is not what we want to do here.<br />
<br />
This is all for today, now I'll need to start my Harley-Davidson and head down town to hang with the other tough guys (OK, I'm really taking my beaten-up Ford and pick up the kids from Kindergarten, I admit it).<br />
<br />
Keep on SQL'ing<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-24718042137401969532016-08-08T10:33:00.001+02:002016-08-08T10:33:38.508+02:00MyQuery 3.5.6 releasedI released version 3.5.6 of MyQuery, and there are quite a number of new features and fixes in there. The #1 bugfix is that the annoying access warnings that poped up from Windows when saving to the registry are gone, as I have now moved the registry to a more Windows 10 acceptable place. Among the new features are:<br />
<ul>
<li>JSON format output when saving results.</li>
<li>More flexible CSV format output with many new options.</li>
<li>Ability to save Dyncol as JSON in CSV and JSON output.</li>
<li>Nicer formatting of numbers in status dialogs.</li>
<li>Auto refresh of status dialogs</li>
</ul>
As this is a version with many new features, I still consider this a Beta. I have built it on Windows 10 and tested it on Windows 10 and 7, 64-bit, although MyQuery itself is still a 32-bit windows application.<br />
<br />
Happy SQL'ing<br />
/Karlsson<br />
<ul>
</ul>
Karlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-85751523694855228752016-02-16T22:48:00.002+01:002016-02-16T22:48:26.090+01:00Loading JSON into MariaDB or even MySQL - mysqljsonimport 2.0 is availableIt was a long time since I updated mysqljsonimport or mysqljsonexport and I had a few things I wanted to do with them. This release is significant enough for me to bump it up to 2.0, and the same is in the works for mysqljsonexport. The one big thing that is now implemented is reasonably advanced support for MariaDB Dynamic Columns, and it is actually pretty flexible, allowing you to load a nested JSON object into a MariaDB Dynamic Column. But don't worry, it will still link and run with MySQL if that is what you want to do (but then you will not have the dynamic column features, for obvious reasons),<br />
<br />
Download from <a href="https://sourceforge.net/projects/mysqljson/files/mysqljsonimport/mysqljsonimport_2.0/" target="_blank">Sourceforge</a> as usual (yes, I know I am oldfashioned and that I should have put it on github). Also as usual is the documentation in pdf format that is also downloadable separately.<br />
<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-7024095106518475562015-12-14T20:27:00.000+01:002015-12-14T20:27:20.065+01:00Getting started with MariaDB on IBM POWER 8IBM POWER 8 is latest generation of the IBM POWER series, and it's a hot one. Above all, for you reading this, POWER 8 is the most Linux friendly so far and IBM really wants you to try this out. Seveal Linux distributions are supporting POWER 8 now, and MariaDB is of course the database of choise. Some cools things with the POWER 8 architecture are the support for CAPI (google for more details) and the fact that POWER 8 machines, due to a vastly superior memory architecture, can grow in memory size, which in general is good news but if you want your own POWER 8, this makes then a bit expensive (although maybe not when you consider the performance you get). IBM has fixed that recently and have announced the LC series of servers which start at $6.600 (see more here: <a href="http://www-03.ibm.com/systems/power/hardware/linux-lc.html" target="_blank">http://www-03.ibm.com/systems/power/hardware/linux-lc.html</a>).<br />
<br />
So, whar about MariaDB then? Well, MariaDB is a standard component in the Linux distributions that support IBM POWER 8, but we here at MariaDB didn't stop there. We have made numerous fixes to MariaDB to make it perfom at it's best and to increase stability even more. To get at those nice additions and enhancements though, you have to run with the latest MariaDB versions and use the binary builds we provide, and here I'll show you how to do that.<br />
<br />
To begin with, you have to register with MariaDB.com, which is free if you want to try MariaDB Enterprise. So surf to MariaDB.com and you should get something like this:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-3ROiuZGS-DA/Vm8RPTJGH0I/AAAAAAAAAKA/iwt_Nv2ixrc/s1600/SugnUp.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://4.bp.blogspot.com/-3ROiuZGS-DA/Vm8RPTJGH0I/AAAAAAAAAKA/iwt_Nv2ixrc/s320/SugnUp.jpg" width="320" /></a></div>
On the top right, as indicated above, are "Login" and "Sign up" links. Select the last of these two and follow the procedure. Then you can revsit this page and log in, and the the links at the top right now say "Logout", "My Portal" and "Profile". Click on the "My Portal" link and you get to a page that looks like this:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-bLz81YsnzB4/Vm8SNhIPdfI/AAAAAAAAAKI/XJk0x9ROPnQ/s1600/Downloads.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="222" src="http://2.bp.blogspot.com/-bLz81YsnzB4/Vm8SNhIPdfI/AAAAAAAAAKI/XJk0x9ROPnQ/s320/Downloads.jpg" width="320" /></a></div>
Above is indicated the "Downloads" tab, click on this and you will be taken to a page with many different download sections. In this case, let's assume you are on Ubuntu, then select this:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-KXS3N6iC6S0/Vm8TyE18kzI/AAAAAAAAAKU/pzn2r-QL79Q/s1600/SelectDistro.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="179" src="http://4.bp.blogspot.com/-KXS3N6iC6S0/Vm8TyE18kzI/AAAAAAAAAKU/pzn2r-QL79Q/s320/SelectDistro.jpg" width="320" /></a></div>
Now it is time to do the actual installation, just follow the steps listed on the page, as I write this, this means I will run:<br />
<pre style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgba(0, 0, 0, 0.14902); color: #333333; display: block; font-family: Monaco, Menlo, Consolas, 'Courier New', monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 300; letter-spacing: normal; line-height: 20px; margin: 0px 0px 10px; orphans: auto; overflow-y: auto; padding: 9.5px; text-align: start; text-indent: 0px; text-transform: none; white-space: pre; widows: 1; word-break: normal; word-spacing: 0px; word-wrap: normal;">wget https://downloads.mariadb.com/enterprise/dnae-wefq/generate/10.0/mariadb-enterprise-repository.deb</pre>
<pre style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgba(0, 0, 0, 0.14902); color: #333333; display: block; font-family: Monaco, Menlo, Consolas, 'Courier New', monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 300; letter-spacing: normal; line-height: 20px; margin: 0px 0px 10px; orphans: auto; overflow-y: auto; padding: 9.5px; text-align: start; text-indent: 0px; text-transform: none; white-space: pre; widows: 1; word-break: normal; word-spacing: 0px; word-wrap: normal;"> dpkg -i mariadb-enterprise-repository.deb</pre>
<pre style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgba(0, 0, 0, 0.14902); color: #333333; display: block; font-family: Monaco, Menlo, Consolas, 'Courier New', monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 300; letter-spacing: normal; line-height: 20px; margin: 0px 0px 10px; orphans: auto; overflow-y: auto; padding: 9.5px; text-align: start; text-indent: 0px; text-transform: none; white-space: pre; widows: 1; word-break: normal; word-spacing: 0px; word-wrap: normal;"> sudo apt-get update</pre>
<pre style="-webkit-text-stroke-width: 0px; background-color: whitesmoke; border-radius: 4px; border: 1px solid rgba(0, 0, 0, 0.14902); color: #333333; display: block; font-family: Monaco, Menlo, Consolas, 'Courier New', monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 300; letter-spacing: normal; line-height: 20px; margin: 0px 0px 10px; orphans: auto; overflow-y: auto; padding: 9.5px; text-align: start; text-indent: 0px; text-transform: none; white-space: pre; widows: 1; word-break: normal; word-spacing: 0px; word-wrap: normal;">sudo apt-get install mariadb-server</pre>
Following all this. MariaDB is now up and running, but I see you asking yourself, what about POWER 8? How do I install MariaDB on that? And fact is, that is exactly what we have dne here. Or Intel x86 for that matter, the procedure is exactky the same, the differences are handles behind the scenes.<br />
<br />
Happy power hacking, I will get back soon with a writeup on MaxScale on POWER 8<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-89699713563647945952015-08-26T18:47:00.000+02:002015-08-26T18:47:04.120+02:00MyOraDump, Oracle dump utility, version 1.2I have now released version 1.2 of <b>MyOraDump</b>, 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.<br />
<br />
MyOraDump 1.2 can be downloaded <a href="https://sourceforge.net/projects/myoradump/" target="_blank">sourceforge</a>, and as usual there is also a pdf only download if you want to read up on the tool before using it.<br />
<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com20tag:blogger.com,1999:blog-9144505959002328789.post-61147228700024771612015-08-07T16:52:00.000+02:002015-08-07T16:52:44.663+02:00Oracle dump utility version 1.1Today I released version 1.1 of <b>myoradump</b> for download from <a href="http://sourceforge.net/projects/myoradump/" target="_blank">sourceforge</a>. 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.<br />
<br />
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:<br />
<ul>
<li><b>MySQL </b>- 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.</li>
<li><b>JSON </b>- 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.</li>
<li><b>JSON Array</b> - 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.</li>
<li><b>HTML </b>- 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.</li>
</ul>
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.<br />
<br />
So, don't touch that dial!<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-46564165371115537582015-06-26T16:19:00.002+02:002015-06-26T16:19:21.381+02:00Oracle dump utilityI 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.<br />
<br />
Download the 1.0 version from <a href="http://sourceforge.net/projects/myoradump/" target="_blank">Sourceforge</a> 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.<br />
<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-16907255123513577022015-06-26T13:24:00.000+02:002015-06-26T13:24:47.033+02:00MariaDB with Galera available on the IBM Power8 platformIt was a very long time since I wrote something in this blob, but I have been very busy this spring with MariaDB on Power mostly. This has been a lot of work, but also a lot of fun. So, what is this MariaDB on Power thing all about, well I wrote an <a href="http://karlssonondatabases.blogspot.se/2014/12/if-you-cant-do-it-with-power-you-can-do.html" target="_blank">introduction to the Power platform</a> late last year. Since then a lot of things has happened though.<br />
<br />
One thing is that several service providers out there has adopted Power8 as a platform. To be honest, this really isn't sexy, but it is useful and as a user of one of these services, you will just see the same old Linux you are used to, but potentially it is more powerful and reliable. One such provider is OVH, whose service is more known as <a href="http://www.runabove.com/" target="_blank">RunAbove</a>. If you want to try it, you can do so for free for 7 7 days, just go there and off you go.<br />
<br />
Another important thing is that MariaDB is now available on Power8 running, RedHat, SUSE or Ubuntu Linux. To get access to this, pop by <a href="http://www.maridb.com/" target="_blank">MariaDB </a>and if you are not yet signed up, then do this now and then go to "My Portal", further to "Downloads" and then select "MariaDB Enterprise and MariaDB Enterprise Cluster". You are now ready to install using the operating system of your choise, but on Power you are, as I said before,limited to SUSE, RedHat and Ubuntu, and if you want to test MariaDB Enterprise Cluster, i.e. MariaDB with Galera, you have to go with Ubuntu.<br />
<br />
Installing MariaDB Enterprise Cluster on Power8 is no more complex than on Intel. There are a few thing to adjust before you can get started with this, after having installed the software. The first node has, as usual, to be configured with <i>wsrep_cluster_adress</i> set to <i>gcomm://</i> to ensure that this first node will bootstrap without having to connect to a cluster. Once the cluster is up and running though, this variable is set to the cluster addresses. In my case, this what the Galera setting look like in /etc/mysql/my.cnf which is the location of this file on Ubuntu.<br />
<span style="background-color: #eeeeee;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;"># Galera<br />wsrep_provider=/usr/lib/galera/libgalera_smm.so<br />wsrep_cluster_name="GaleraPower1"<br />wsrep_cluster_address=gcomm://92.127.22.124<br />wsrep_node_address=92.127.22.121<br />wsrep_node_name=galera3<br />binlog_format=ROW</span></span></span><br />
Note in particular the binlog_format setting. This MUST be set to ROW for Galera to work. But fact is that these setting are not particular to MariaDB on Power, this is the same even on Intel.<br />
<br />
Tf this isn't enough to convice you about the advantages of running MariaDB on IBM Power, then see what Foedus in Italy has to say about this combination in this Video:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/YRhvQnukcQQ/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/YRhvQnukcQQ?feature=player_embedded" width="320"></iframe></div>
<br />
There is more to say about running MariaDB on Power and there is more to come here, I'll look at some performance data, we'll have a look at MaxScale on Power (this is not official yet, but that isn't stopping me) as well as a blog on how to run a Power8 emulation on Intel which I have promissed before.<br />
<br />
So, don't touch that dial!<br />
<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-66457415768398804382014-12-16T14:53:00.002+01:002014-12-16T14:54:20.535+01:00If you can't do it with Power, you can do it with MORE Power<a href="http://www.ibm.com/" target="_blank">IBM </a>has released their <b>Power8 </b>CPU and with that a bunch of servers and also some other news around this, like the OpenPower consortium that has been around for a year or so now. Besides IBM, <a href="http://www.tyan.com/" target="_blank">Tyan</a> and Google have announced that they are building systems around the Power8 CPU, with the a Tyan server being on sale for some $2800, but this is not a server with a high-end configuration, but it is in a 2U rack mount case and is extensible with more disks and RAM.<br />
<br />
There is one thing with the Power8 CPU that sets it apart from the previous Power CPUs that is worth mentioning, which is the byte ordering or endianess of the CPU. The order of the bytes in value in a computer comes in two main flavours, the "Motorola style" AKA "big endian" which is how we write numbers in general, with the most significant digit (or byte in the case of computers) first, and then we have the "Intel style" or "Little endian" where the most significant byte comes last. All of this has little meaning to most of you so far. Thar Big endian is old school mainframe style, but is also used by many RISC CPUs. For those of you with a flair for weird facts one oddball of endianess was PDP-11, where each byte in a 16-bit part in a 32-bit word was swapped (this is called PDP-endian). And by the way, <i>Oddball of Endianess</i> would be a great name for a Country-Rock band (having at least 3 Telecaster equipped guitarists on stage).<br />
<br />
What makes Power8 a bit different is that it supports operating systems using either big endian or little endian. Big endian is used by <b>AIX </b>and some Linuxes, but IBM is trying to get more Linuxes onto the little endian train. Among the Big endian Linuxes on Power8 are Red Hat and derivatives, like Fedora (note that CentOS isn't one of them, so far, as Centos 7 doesn't yet support Power at all, nor is 32-bit Intel supported anymore) as well as Debian and SUSE 11 (which will continue to be supported). The little endian Linuxes on Power8 so far includes SUSE 12 and Ubuntu 14.04. But IBM really wants Linux on Power to run Little endian, so it seems that Debian is on it's way there and Red Hat 7.1 will also support Little endian. But to be honest, we don't have the full story yet.<br />
<br />
There are some really good things with the Power8 architecture, like really good performance (see <a href="https://www-304.ibm.com/partnerworld/wps/servlet/download/DownloadServlet?id=EZ$VjjUKGYkiPCA$cnt&attachmentName=ibm_power_systems_solution_for_mariadb.pdf&token=MTQxODczNDQyNjI4MQ==&locale=en_ALL_ZZ" target="_blank">this one</a> for example by MariaDB and IBM) and also it is a great platform for Virtualization / Cloud infrastructure. And all that fuzz with endianess will hopefully be history soon when we have all Linuxes on Power8, as well as x86_64 of course, running Little endian (thank you). And the Country-Rock band mentioned above could call their first record <i>"Fuzz with Endianess"</i>.<br />
<br />
So, I have convinced you? If so, I guess you want to know what it costs. Well, Power8 machines from IBM starts at around $10.000, which is a lot it seems, but then you have to remember that used the way this type of system is probably mostly used, as a host for numerous virtual machines, then it is actually pretty reasonable. If you really want a less expensive machine, Tyan has one available which should sell for some $3.000 which is looking more reasonable. Eventually I guess Tyan will sell their motherboards, and the components in this, beside the CPU, is pretty standard stuff (DDR RAM, PCI buses, SATA disks etc). But the CPU is pretty expensive. Anyway, there is a way around all this, if you really want to try running Power8, and that is to use emulation, and once you get the hang of it, this works well, although slowly, but no so slow that you cannot test things on it (but too slow for production use I guess).<br />
<br />
I'll show you how to set up an emulated Power8 system in a later blog post, so stay tuned and <b><i>Don't touch that dial</i>!</b><br />
<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-34319948229009236892014-10-02T18:09:00.000+02:002014-10-02T11:32:52.523+02:00OraMySQL 1.0 Alpha released - Replication from Oracle to MariaDB and MySQL!!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:<br />
<ul>
<li><a href="http://karlssonondatabases.blogspot.se/2014/09/replication-from-oracle-to-mariadb.html">Part 1</a></li>
<li><a href="http://karlssonondatabases.blogspot.se/2014/09/replication-from-oracle-to-mariadb_29.html">Part 2</a></li>
<li><a href="http://karlssonondatabases.blogspot.se/2014/09/replication-from-oracle-to-mariadb_65.html">Part 3</a></li>
<li><a href="http://karlssonondatabases.blogspot.se/2014/10/replication-from-oracle-to-mariadb.html">Part 4</a> </li>
</ul>
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 <a href="http://sourceforge.net/projects/oramysql/files/OraMySQL%201.0/">download from sourceforge</a>, 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.<br />
<br />
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.<br />
<br />
Cheers and happy replication folks<br />
<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-92131288913695146352014-10-01T15:56:00.000+02:002014-10-01T16:17:12.334+02:00Replication from Oracle to MariaDB the simple way - Part 4Now 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:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-057bip7nWJ4/VCwCOFDDO2I/AAAAAAAAAJc/1yj6R_S7JmE/s1600/OraMySQL3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-057bip7nWJ4/VCwCOFDDO2I/AAAAAAAAAJc/1yj6R_S7JmE/s1600/OraMySQL3.jpg" height="135" width="400" /></a></div>
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 <i>order</i> table triggers and what have you not is also created for you by a shell script that can do this for any table.<br />
<br />
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.<br />
<br />
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?<br />
<br />
Let me hear what you think, I'm just about to release this puppy!<br />
<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0tag:blogger.com,1999:blog-9144505959002328789.post-21559593308787382742014-09-29T21:16:00.000+02:002014-09-29T21:19:15.530+02:00 Replication from Oracle to MariaDB the simple way - Part 3In this third installment in this series, I'll explain why the smart solution I described in the <a href="http://karlssonondatabases.blogspot.se/2014/09/replication-from-oracle-to-mariadb_29.html">previous post</a> 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:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-d5rjWYuVzEc/VClUmZevTxI/AAAAAAAAAJQ/sm9qiSpi2iA/s1600/OraMySQL2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-d5rjWYuVzEc/VClUmZevTxI/AAAAAAAAAJQ/sm9qiSpi2iA/s1600/OraMySQL2.jpg" height="166" width="400" /></a></div>
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.<br />
<br />
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!<br />
<br />
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 <b>AWFULLY CRAZY SLOW</b> is beyond me). So the materialized view using FAST REFRESH for the <i>orders</i> table should really be created like this:<br />
<span style="background-color: #eeeeee;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;">CREATE MATERIALIZED VIEW orders_mv<br /> REFRESH FAST ON COMMIT<br /> AS SELECT *<br /> FROM orders;</span></span></span><br />
But this gives an error from Oracle:<br />
<i>ORA-23413: table "SYSTEM"."ORDERS" does not have a materialized view log</i> <br />
Well the, let's create a MATERIALIZED VIEW LOG for table ORDERS then, that's no big deal:<br />
<span style="background-color: #eeeeee;"><span style="color: #444444;"><span style="font-family: "Courier New",Courier,monospace;">CREATE MATERIALIZED VIEW LOG ON t1_mv;</span></span></span><br />
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:<br />
<i>ORA-00439: feature not enabled: Advanced replication</i><br />
<br />
<b>Grrnn</b> (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, "<i>Close, but no cigar</i>".<br />
<br />
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.<br />
<br />
More details on how I tricked Larry in the next part of this series on replication from Oracle to MariaDB.<br />
<br />
/KarlssonKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.com0