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.

9 thoughts on “tungsten-replicator: Provisioning generates different column-index than a normal insert event”

  1. Hello,

    I’m having similar issue, can you show the configuration string to use this custom filter?

    I’ve tried thru properties:

    --property=replicator.filter.indexfixer=com.continuent.tungsten.replicator.filter.JavaScriptFilter \
    --property=replicator.filter.indexfixer.script=/opt/continuent/share/indexfixer.js \

    but I got following error:
    Plugin class name property is missing or null:
    key=replicator.filter.indexfixer

  2. It’s me again, filter was added to the :

    --svc-extractor-filters=replicate,enumtostring,settostring,colnames,pkey,dropcolumn \

  3. Hi Scherbakov,

    what you have to do, is locate your static-properties file.

    Mine is located in /opt/continuent/tungsten/tungsten-replicator/conf and named “static-servicename.properties.

    In that file you have to add the configuration manually.

    First;
    Find “replicator.stage.q-to-dbms.filters” and add the name of your script.

    replicator.stage.q-to-dbms.filters=mysqlsessions,pkey,indexfixer

    Second;
    Add the scripts

    replicator.filter.indexfixer=com.continuent.tungsten.replicator.filter.JavaScriptFilter
    replicator.filter.indexfixer.script=/opt/continuent/tungsten/tungsten-replicator/samples/extensions/javascript/index-fixer.js

    The reason for this workaround, is that these properties isn’t allowed to add through the “tpm”-tool.

    Hope this helps :)

    1. Thank you Kristian!

      I will try. Just one questions, we have master -> slave replication (mysql -> vertica) am I right that this filter should be add into master (extractor from mysql) in our case?

  4. You should apply this filter on your slave.

    This is caused by the master, and must therefor be fixed on your slave.

    So apply it to the Vertica side :)

    1. Hello Kristian, it’s me again :( Sorry to bother you. I’ve added it on slave and I see in logs:


      INFO filter.JavaScriptFilter Correcting index on affiliate size/lastindex: 7/53

      but trepctl status on slave still says:
      pendingError : Stage task failed: stage=q-to-dbms seqno=846 fragno=0
      pendingErrorCode : NONE
      pendingErrorEventId : mysql-bin.000032:0000000000350985;-1
      pendingErrorSeqno : 846
      pendingExceptionMessage: Invalid write to CSV file: name=/tmp/staging/service/staging0/service-affiliate-724.csv table=service.affiliate table_columns=id,ref_by,referral_hit_id,username,email,status,is_own csv_columns=tungsten_opcode,tungsten_seqno,tungsten_row_id,tungsten_commit_timestamp,id,ref_by,referral_hit_id,username,email,status,is_own

      this error says that tungsten trys to save into csv with with wrong index.

      Any ideas?

    2. btw, you may also add to your example:
      // Release method
      function release() {}

      and also fix copypaste issue:
      for (x = 0; x < colSpec.size(); ++x) {
      to
      for (x = 0; x > colSpec.size(); ++x) {

  5. hello!

    can I ask you one more question? can you describe best practise for altering table on master?

    as I see it:
    1) stop extractor
    2) alter table on master mysql
    3) run extractor

    is it safe not to stop applier (slave)?

    and what to do if THL of slave have column we don’t have in vertica (forgot to drop on master) and it fails?

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>