Start with a position and real time updates

[iamchathu]

When use with startAtEnd:true This fires events near realtime. But when if the application crashed or turned off we can not get the events happen between downtime.

If I make startAtEnd:false events doesn't get fired real time it takes very noticeable delay to events fire and get some very old event. can we use position and startAtEnd to have both these cases satisfied?

[rodrigogs]

Check zongji's options: https://github.com/nevill/zongji#zongji-class

You could store your current position: event.binlogName and event.nextPosition. I suggest a redis server to store this information, since it should be super fast.

And then you can pass them as filename and position options in the future.

Code would be like:

  const MySQLEvents = require('@rodrigogs/mysql-events');
  const myCache = require('./my-cache');  

  // Inside and async function
  const filename = await myCache.get('currentFileName');
  const position = await myCache.get('currentBinlogPosition');

  const instance = new MySQLEvents(connection, {
    startAtEnd: true,
    filename, // if both filename and position exists, startAtEnd is overcomed by it
    position,
  });

  ...

  instance.addTrigger({
    name: 'my-trigger',
    expression: '*',
    statement: MySQLEvents.STATEMENTS.ALL,
    onEvent: async (event) => {
      myCache.set('currentFileName', event.binlogName);
      myCache.set('currentBinlogPosition', event.nextPosition);
    },
  });

  ...

I've used this approach before, and I assure it works. Of course, this is a super simple example that I wrote without testing, you should implement your own version of it.

[iamchathu]

Thanks @rodrigogs I tested this approach only issue currently i'm facing it even though i used

excludedSchemas: {
      mysql: true,
    },

When i set trigger as yours with expression *

I keep getting events

{ 
  type: 'UPDATE',
  schema: 'mysql',
  table: 'heartbeat',
  affectedRows: [ { after: [Object], before: [Object] } ],
  affectedColumns: [ 'master_time' ],
  timestamp: 1561523231000,
  nextPosition: 24000938,
  binlogName: 'mysql-bin.23232' 
}

[rodrigogs]

Well, that's strange. I'm out of time to test It right now, but you could open an issue for It.

[iamchathu]

Also when I restart the listener it doesn't get old records. BTW I'm using GCP Cloud SQL MySQL instance.

[iamchathu]

@rodrigogs I have tested this with local MySQL instance and

With this configuration with redis

const instance = new MySQLEvents(connection, {
    startAtEnd: true,
    filename, // if both filename and position exists, startAtEnd is overcomed by it
    position,
  });

I started the sever and waited for few events. Then I stopped event listing app and add more items to database. Start the listening app again but I didn't get any old data.

[rodrigogs]

I'll try to check it later

[iamchathu]

@rodrigogs After reading the documentation of https://github.com/rodrigogs/zongji I found out the code should be corrected as

const instance = new MySQLEvents(connection, {
    startAtEnd: true,
    binlogName:filename, // if both filename and position exists, startAtEnd is overcomed by it
    binlogNextPos: position,
  });

Now it works.