Not getting myslq events

[manojsharma20]

Hi,

I have followed your step and the node server is running and listening, but mysql event not working. It is not listening the mysql event when i insert or update the record.

Here is my server.js code :

var app = require('express')(); var http = require('http').Server(app); var io = require('socket.io').listen(http); // var Redis = require('redis'); var mysql = require('mysql'); var MySQLEvents = require('mysql-events');

var dsn = { host: 'localhost', user: 'root', password: 'DEVtrushield@123' };

http.listen(3000, function(){ console.log('Listening on Port 3000'); });

io.sockets.on('connection', function (socket) { socket.emit('welcome', { hello: 'world' });

socket.on('mysql', function (data) {

    // console.log(data);

    // var connection = mysql.createConnection({
    //     host: 'localhost',
    //     port: 3306,
    //     user: 'root',
    //     password: 'DEVtrushield@123',
    //     database: 'ts_tintel'
    // });

    var myCon = MySQLEvents(dsn);

    var event1 = myCon.add(
      'ts_tintel.ts_intel_last_hour_datas.lasthourdataid.value',
      function (oldRow, newRow) {
        console.log(oldRow);
        console.log(newRow);
        console.log("================================================================================");
      }, 
      'Active'
    );

}); });

Please tell me where i have done mistake.

[spencerlambert]

Do you have MySQL Binary Logging turned on and configured? Does your MySQL user have replication privileges?

https://dev.mysql.com/doc/refman/5.7/en/replication-howto-repuser.html

[manojsharma20]

Hi, I have tried all whatever you have mentioned. Let me explain what i have done so far. I have done fresh installation on oracle virtual machine and i have install mariadb, phpmyadmin, php, nodejs.

After that i have run npm install socket.io express mysql mysql-events command. then i have written below code in index.js file. It is a complete code which i am using right now.

Here is my code:

//var app = require('express')(); //var http = require('http').Server(app); //var io = require('socket.io').listen(http); var MySQLEvents = require('mysql-events');

var dsn = { host: 'localhost', user: 'root', password: 'test' };

//http.listen(3000, function(){ // console.log('Listening on Port 3000'); //});

var myCon = MySQLEvents(dsn); var event1 = myCon.add( 'test.user', function (oldRow, newRow) { console.log(oldRow); console.log(newRow); console.log("================================================================================"); } );

Then, I have enable binary log in my.cnf by writing bin-log in [mysqld] section.

then i have run updatedb and locate mysql-bin command. to check binarry log is enabled or not.

Once i have done with binary log enabling, then i have run this command in mysql "GRANT REPLICATION SLAVE ON . TO 'root'@'localhost';" as you mentioned above.

this so far i have done.

Please let me know if i have miss something.

[spencerlambert]

Did you also restart mariadb? I've personally not tested this on mariadb.

Try creating a different user, rather than using root. I use ...ON . TO... I'm not sure if just . is equivalent.

[manojsharma20]

Yes it equivalent, even it is a substitute for mysql and far better.

[manojsharma20]

I have tried everything, but now i am getting following error :

events.js:160 throw er; // Unhandled 'error' event ^

Error: Connection lost: The server closed the connection. at Protocol.end (/var/www/html/test/node_modules/zongji/node_modules/mysql/lib/protocol/Protocol.js:103:13) at Socket. (/var/www/html/test/node_modules/zongji/node_modules/mysql/lib/Connection.js:88:28) at emitNone (events.js:91:20) at Socket.emit (events.js:185:7) at endReadableNT (_stream_readable.js:974:12) at _combinedTickCallback (internal/process/next_tick.js:74:11) at process._tickCallback (internal/process/next_tick.js:98:9)

Please help me. If i tried to connect with mysql the connection get established and even i am able to insert the record. Please help me i have to do that, its my requirement and i have to that anyhow.

[spencerlambert]

Are you using version 0.0.8? It was just released the other day.

[manojsharma20]

I have tried both 0.0.7 and 0.0.8 but getting same connection lost error.

[spencerlambert]

At one point I created a fork of the Zongi project, to help with database reconnects. I'd suggest giving it a try as well. It's been tested with 0.0.7, but not 0.0.8.

https://github.com/spencerlambert/zongji

[manojsharma20]

Hi,

Thanks for your support. But the issue is with the my.cnf, we have to make binlog-db entry in my.cnf and then only mysql-events module will able to generate event after doing above steps.

I have also another question, i will ask it in new issue.

feel free to close it as resolved.

Thanks.

[spencerlambert]

If you look at my very first reply, I said that MySQL needs to be configured for binary logging....

[malkitsingh]

I am facing this same issue, I have configured binary logs and these logs are seems to be working. I logged mysqlEventWatcher object and this is being logged.

{ started: true, zongji: ZongJi { options: { startAtEnd: true, includeEvents: [Object], includeSchema: [Object] }, domain: null, _events: { error: [Object], binlog: [Function] }, _eventsCount: 2, _maxListeners: undefined, ctrlConnection: Connection { domain: null, _events: [Object], _eventsCount: 2, _maxListeners: undefined, config: [Object], _socket: [Object], _protocol: [Object], _connectCalled: true, state: 'disconnected', threadId: null }, ctrlCallbacks: [ [Function] ], connection: Connection { domain: null, _events: [Object], _eventsCount: 2, _maxListeners: undefined, config: [Object], _socket: undefined, _protocol: [Object], _connectCalled: false, state: 'disconnected', threadId: null }, tableMap: {}, ready: false, useChecksum: false, binlogName: null, binlogNextPos: null }, databases: [], tables: { traccar: [ 'devices' ] }, columns: {}, events: [ 'tablemap', 'writerows', 'updaterows', 'deleterows' ], triggers: [ { trigger: 'traccar.devices', callback: [Function] } ], dsn: { host: 'localhost', user: 'root', password: '' }, connect: [Function], add: [Function], remove: [Function], stop: [Function], reload: [Function], includeSchema: [Function] }

From these logs, Zongji is working that is confirm. But nothing is being triggered after any event on this given table. ( I am using 0.8 version and Mysql version is 5.6.17

Can anyone help me on this? Thanks

[spencerlambert]

@malkitsingh Seeing your code could be helpful. Maybe you have a typo in the database table name. What events are you looking for, deletes, inserts, updates?

[malkitsingh]

@spencerlambert here is my code

var MySQLEvents = require('mysql-events');
var dsn = {
    host:     'locahost',
    user:     'root',
    password: '',
};
var mysqlEventWatcher = MySQLEvents(dsn);

var watcher =mysqlEventWatcher.add(
    'traccar.devices',
    function (oldRow, newRow) {
        console.log('something is happening');
        //row inserted
        if (oldRow === null) {
            //insert code goes here
        }

        //row deleted
        if (newRow === null) {
            //delete code goes here
        }

        //row updated
        if (oldRow !== null && newRow !== null) {
            //update code goes here
        }
    }

);
console.log(mysqlEventWatcher);

I am trying to capture insert events into devices table.

[spencerlambert]

@malkitsingh

Typically I wouldn't use root as the replication user and by default it's not setup as a replication user.

Try creating a fresh user just for replication.

https://dev.mysql.com/doc/refman/5.7/en/replication-howto-repuser.html

Also, make sure mysql has been restarted sense you changed my.conf.

[malkitsingh]

@spencerlambert thanks for the help. The problem was due to typo in localhost, I mistakenly left it locahost. Also, I made new user and gave him required permissions and things are working fine now. Great package (Y)

[spencerlambert]

Good catch. The one with the L and without look so much a like, because of the H. That's a hard typo to spot.

[happilymarrieddad]

@spencerlambert I seem to be having the same problem.. here are my files and pictures

[happilymarrieddad]

I thought it might be the regex thing so I removed it and same results

[spencerlambert]

Did you restart MySQL after adding binary logging?

[happilymarrieddad]

yes

[happilymarrieddad]

sudo service mysql restart. I'm on Linux Mint 17.3

[happilymarrieddad]

Is there some sort of function or error I can check somewhere where it would tell me that it couldn't make a connection or something?

[spencerlambert]

Is fusion the database name?

[happilymarrieddad]

Yes, here is a copy of the object after .add()

[happilymarrieddad]

[happilymarrieddad]

I pointed it at a staging server to test it as well and I didn't get anything. I'm using Node 6.9.1 and npm 3.8.... something lol.

[happilymarrieddad]

I know for sure the replication is working on staging because we have a replicated cluster that's working at the moment with customers on it.

[happilymarrieddad]

Before / After

Bin logging is on.

[spencerlambert]

May be there is a bug when watching the whole database for changes. Try adding a dot table name after fusion.

[happilymarrieddad]

lol wow still not working.. I'm not sure what I'm doing wrong. This kind of sucks. I really want to use this package on our system.

[spencerlambert]

What's the MySQL Query you are running to trigger an event?

[happilymarrieddad]

oh I just console.log(reason) inside

function zongjiManager(dsn, options, onBinlog) {
  var newInst = new ZongJi(dsn, options);
    newInst.on('error', function(reason) {
        console.log(reason)

and I got this

{ Error: ER_SPECIFIC_ACCESS_DENIED_ERROR: Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
    at Query.Sequence._packetToError (/home/nick/Projects/Nodejs/fusionDbHandler/node_modules/zongji/node_modules/mysql/lib/protocol/sequences/Sequence.js:48:14)
    at Query.ErrorPacket (/home/nick/Projects/Nodejs/fusionDbHandler/node_modules/zongji/node_modules/mysql/lib/protocol/sequences/Query.js:83:18)
    at Protocol._parsePacket (/home/nick/Projects/Nodejs/fusionDbHandler/node_modules/zongji/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/home/nick/Projects/Nodejs/fusionDbHandler/node_modules/zongji/node_modules/mysql/lib/protocol/Parser.js:73:12)
    at Protocol.write (/home/nick/Projects/Nodejs/fusionDbHandler/node_modules/zongji/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/home/nick/Projects/Nodejs/fusionDbHandler/node_modules/zongji/node_modules/mysql/lib/Connection.js:96:28)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    --------------------

Did I not create the user correctly inside MySQL earlier? Can you please scroll up and verify for me?

[spencerlambert]

You also need to give the user at least SELECT privileges too. I think that is what you are missing.

[happilymarrieddad]

Yes!!! Just got it with this.

GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT on *.* TO 'repl'@'%';

Thanks man it works!