tungsten-replicator: Provisioning generates different column-index than a normal insert event

Time for another post about tungsten replicator. It has worked well since my last post, we thought. Until we suddenly found out that table-columns were shifted right for some reason.

After some testing, we found this:

*A blob-column was left out by us (defined in tungsten.tables and chunks.csv) * The missing column generated a leap in the column index by provisioning-inserts

* The missing column DID NOT generate a leap in the column index by normal inserts and updates

This would trigger a column-count error when inserting during provisioning, and when a “dummy”-field was added, the same column would make all columns afterwards shift right.

The solution

What we ended up with, was a custom javascript filter which reset the column index on every insert.

index-fixer.js

// Prepare the filter and setup structures
function prepare()
{
 
}
// Perform the filter process; function is called for each event in the THL
function filter(event)
{
    // Get the array of DBMSData objects
    data = event.getData();
    // Iterate over the individual DBMSData objects
    for(i=0;i<data.size();i++) {
        // Get a single DBMSData object
        d = data.get(i);
 
        if (d instanceof com.continuent.tungsten.replicator.dbms.RowChangeData) {
            // Get an array of all the row changes
            rows = d.getRowChanges();
            // Iterate over row changes
            for(j=0;j<rows.size();j++) {
                // Get the single row change
                rowchange = rows.get(j);
 
                if (rowchange.getAction() == 'INSERT') {
                    var colSpec = rowchange.getColumnSpec();
                    var isOutOfIndex = false;
                    var lastIndex = null;
                    for (x = 0; x &lt; colSpec.size(); ++x) {
                        var cs = colSpec.get(x);
 
                        lastIndex = cs.getIndex();
                        //if index diverts from a sequetial numbering, correct it!
                        if (cs.getIndex() != (x+1)) {
                            isOutOfIndex = true;
 
                            //set correct index
                            cs.setIndex(x+1);
                        }
                    }
                    if (isOutOfIndex) {
                        logger.info('Correcting index on '+rowchange.getTableName()+' size/lastindex: '+colSpec.size()+'/'+lastIndex);
                    }
                }
            }
        }
    }
}

What we didn’t try

* Removing columns from chunks.csv to see if that made an impact.

tungsten-replicator: Oracle to MySQL schema converter using ddlscan [PATCH]

The last couple of weeks we’ve been playing around with tungsten for the purpose of a full scale Oracle to MySQL replication. The newest provision feature in 2.2.1 was especially helpful – great work!

We now have a full scale replication running, and the latest “minor” obstacle we met was converting existing scheme to MySQL scheme as automatically as possible. Since this might be updated more often than first thought, we were looking for an automatic approach. Thanks to ddlscan and its features – it solved the issue for us.

The only problem with ddlscan was the inability to convert decimal precision to MySQL, hence we made som modification to the database layer of tungsten to accommodate our needs. What we essentially lacked, was the ability to convert NUMBER(14,2) to DECIMAL(14,2), and be sure that NUMBER(10, 0) could be converted to INT(11).

This is the first draft of a patch, which adds decimal precision to the Column-object, and adds the appropriate information in AbstractDatabase. I also attached a patch to the ddl-oracle-mysql Velocity-script to illustrate the use of the new feature.

See attached patch:


diff '--exclude=.svn' '--exclude=build' -ur replicator/samples/extensions/velocity/includes/ddl-oracle-mysql-table.vm sources/replicator/samples/extensions/velocity/includes/ddl-oracle-mysql-table.vm
--- replicator/samples/extensions/velocity/includes/ddl-oracle-mysql-table.vm	2014-05-06 13:55:14.000000000 +0200
+++ sources/replicator/samples/extensions/velocity/includes/ddl-oracle-mysql-table.vm	2014-05-06 15:24:26.000000000 +0200
@@ -37,7 +37,7 @@
 #else
 DECIMAL($col.getLength())##
 #end
- /* $col.getTypeDescription()($col.getLength(), ?) */##
+ /* $col.getTypeDescription()($col.getLength(), $col.getDecimalPrecision()) */##
 #elseif ( $col.getTypeDescription().startsWith("FLOAT") )
 $col.getTypeDescription()($col.getLength())##
 #if( $col.getLength() > 53 )
@@ -67,4 +67,4 @@
 #if( $colReserved ) /* WARN: changed column name because of reserved word ($col.getName()) */#end##
 #if( $foreach.hasNext ),
 #end
-#end
\ No newline at end of file
+#end
diff '--exclude=.svn' '--exclude=build' -ur replicator/src/java/com/continuent/tungsten/replicator/database/AbstractDatabase.java sources/replicator/src/java/com/continuent/tungsten/replicator/database/AbstractDatabase.java
--- replicator/src/java/com/continuent/tungsten/replicator/database/AbstractDatabase.java	2014-05-06 13:55:12.000000000 +0200
+++ sources/replicator/src/java/com/continuent/tungsten/replicator/database/AbstractDatabase.java	2014-05-06 15:19:59.000000000 +0200
@@ -986,12 +986,14 @@
                 String typeDesc = rsc.getString("TYPE_NAME").toUpperCase();
                 // Issue 798. Mimicking MySQLApplier.
                 boolean isSigned = !typeDesc.contains("UNSIGNED");
+		int decimalPrec = rsc.getInt("DECIMAL_DIGITS");
 
                 Column column = new Column(colName, colType, colLength,
                         isNotNull, valueString);
                 column.setPosition(rsc.getInt("ORDINAL_POSITION"));
                 column.setTypeDescription(typeDesc);
                 column.setSigned(isSigned);
+		column.setDecimalPrecision(decimalPrec);
                 table.AddColumn(column);
                 cm.put(column.getName(), column);
             }
diff '--exclude=.svn' '--exclude=build' -ur replicator/src/java/com/continuent/tungsten/replicator/database/Column.java sources/replicator/src/java/com/continuent/tungsten/replicator/database/Column.java
--- replicator/src/java/com/continuent/tungsten/replicator/database/Column.java	2014-05-06 13:55:12.000000000 +0200
+++ sources/replicator/src/java/com/continuent/tungsten/replicator/database/Column.java	2014-05-06 14:45:24.000000000 +0200
@@ -45,6 +45,7 @@
                                                        // NULL column
     Serializable              value;
     int                       valueInputStreamLength;
+    int decimalPrecision;
     private int               position;
     private boolean           blob;
     private String            typeDescription;
@@ -109,6 +110,16 @@
         return this.type;
     }
 
+    public int getDecimalPrecision()
+    {
+	return this.decimalPrecision;
+    }
+
+    public void setDecimalPrecision(int precision)
+    {
+	this.decimalPrecision = precision;
+    }
+
     public long getLength()
     {
         return this.length;