search

rodrigogs / mysql-events

A node package that watches a MySQL database and runs callbacks on matched events.
BSD 3-Clause "New" or "Revised" License
137 stars 53 forks source link

Reconnection after connection lost #26

Open ubuntutest opened 4 years ago

ubuntutest commented 4 years ago

Hi, congratulations for this module, I use it in production on my platform.

I use pm2 as a process manager.

Sometimes I get the following error:

{ Error: Connection lost: The server closed the connection.
    at Protocol.end (/home/imroot/node_modules/mysql/lib/protocol/Protocol.js:112:13)
    at Socket.<anonymous> (/home/imroot/node_modules/mysql/lib/Connection.js:94:28)
    at Socket.<anonymous> (/home/imroot/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:203:15)
    at endReadableNT (_stream_readable.js:1145:12)
    at process._tickCallback (internal/process/next_tick.js:63:19) fatal: true, code: 'PROTOCOL_CONNECTION_LOST' }

I think it's because my app occasionally disconnects from the database.

I found this and much more, but I'm not sure how to adapt it to your model with this different syntax, could you help me out? it could be useful for many.

Thanks

brijeshpant83 commented 4 years ago

seems I am having same problem, sometime its get disconnected and unable to reconnect. pls suggest.

edilsonlm217 commented 4 years ago

I'm also getting an error like that. I'm watching for those database events to notify users and at some point it stops working.

8secz-johndpope commented 4 years ago

I will detail some designs - but I've introduced a health check in another process that causes a restart on pm2 if the service fails. // var mysqlPool = mysql.createPool(config.db); var connection = mysql.createConnection(config.db);

async function getConnection() { await new Promise ( resolve => mysqlPool.getConnection( async (err, connection) => { if(err){ if (err.code === 'PROTOCOL_CONNECTION_LOST') { console.error('👺 Database connection was closed.') } if (err.code === 'ER_CON_COUNT_ERROR') { console.error('👺 Database has too many connections.') } if (err.code === 'ECONNREFUSED') { console.error('👺 Database connection was refused.') } console.error('👺 mysqlPool.getConnection: ', new Date(), JSON.stringify(err)) // await this.getConnection() } else { console.log('🎃 connected connection:',connection) connection = connection resolve() } })) }

muriloneo commented 3 years ago

does anyone has found a way to keep connected ? I have a problem that the connection can break on the middle of a processing that is activated by a insert trigger.

8secz-johndpope commented 3 years ago

I use dynamodb to pick up from the last known rowid / timestamp that's been processed. https://gist.github.com/8secz-johndpope/4bf52a0d40071bfa75bfea35aea7f5b9 On reconnect - I just flash forward through transactions....