Re: node-mysql & beanstalkd

[klrumpf]

just ever so slightly off topic but I was playing with koajs and generators last week to do transactions, well, read for yourself two examples below, haven't checked disconnect in the koajs version but I'm sure it will catch it too,


regards, karl

Example 1)

-------- Original Message --------
Subject:     Fwd: Re: koajs + co .. how do I read the values returned by "co"
Date:     Tue, 12 Aug 2014 01:28:20 +0200
From:     klrumpf <klr...@gmail.com>
To:     ko...@googlegroups.com

 after playing around a bit more it looks like this

a) co-mysql/node-mysql will not work with connection pool *and* transactions, if I use a single connection it is fine and merrily works thru the SQLs in sequence.

b) the second co function is not necessary in this setup (?)

c) the new code is somewhat shorter than the callback pattern of nested functions
    q1 = ...
      if good q2() else return

     q2 = ...
       if good q3() ...

     q1()

     certainly more readable/maintanable/ACID as no more errs can slip through
     Now I can recycle my express3/coffee/coffekup sources with very few serious changes.

     Thanks for a nice tool!

***************** OUTPUT CONSOLE ************************
DEV$ coffee --nodejs --stack_size=3072 --nodejs --harmony main.coffee

time before @body = yield sqlSequence =  1407798073551
start BEGIN WORK =  1407798073552

start a =  1407798073575
a =  [ { fieldCount: 0,
    affectedRows: 1,
    insertId: 0,
    serverStatus: 35,
    warningCount: 0,
    message: '(Rows matched: 1  Changed: 1  Warnings: 0',
    protocol41: true,
    changedRows: 1 },
  undefined ]

start b =  1407798073582
b =  [ { fieldCount: 0,
    affectedRows: 1,
    ...

start c =  1407798073585
c =  [ { fieldCount: 0,
    affectedRows: 1,
     ...

start d =  1407798073587
d =  [ [ { keyId: 52, buComments: 'thirdWriteOp' } ],
  [ { catalog: 'def',
      db: 'budeso01',
      ...
      protocol41: true },
    { catalog: 'def',
      ...
      protocol41: true } ] ]

start COMMIT WORK =  1407798073597

time after @body = yield sqlSequence =  1407798073621

************* JS CODE from CS ********************

168   app.get('/testing', function*() {
169     var client, sqlSequence, start;
170     client = mysql.createConnection(mysqlOptions);
171     sqlSequence = co(function*() {
172       var a, b, bad, beginWork, c, commitWork, d, e, start;
173       try {
174         start = new Date * 1;
175         beginWork = yield client.query('BEGIN WORK');
176         console.log("start BEGIN WORK = ", start);
177       } catch (_error) {
178         e = _error;
179         console.log("error begin work = ", e.code);
180         client.end;
181         return;
182       }
183       try {
184         start = new Date * 1;
185         a = yield client.query("UPDATE cust01 SET buComments = 'firstWriteOp
    ' WHERE custNum = 10028");
186         console.log("start a = ", start);
187         console.log("a = ", a);
188       } catch (_error) {
189         e = _error;
190         console.log("error dummy = ", e.code);
191         bad = yield client.query('ROLLBACK WORK');
192         client.end;
193         return;
194       }
195       try {
196         start = new Date * 1;
197         b = yield client.query("UPDATE cust01 SET buComments = 'secondWriteO
    p' WHERE custNum = 10028");
198         console.log("start b = ", start);
199         console.log("b = ", b);
200       } catch (_error) {
201         e = _error;
202         console.log("error b = ", e.code);
203         bad = yield client.query('ROLLBACK WORK');
204         client.end;
205         return;
206       }
207       try {
208         start = new Date * 1;
209         c = yield client.query("UPDATE cust01 SET buComments = 'thirdWriteOp
    ' WHERE custNum = 10028");
210         console.log("start c = ", start);
211         console.log("c = ", c);
212       } catch (_error) {
213         e = _error;
214         console.log("error c = ", e.code);
215         bad = yield client.query('ROLLBACK WORK');
216         client.end;
217         return;
218       }
219       try {
220         start = new Date * 1;
221         d = yield client.query('SELECT keyId,buComments FROM cust01 WHERE cu
    stNum = 10028');
222         console.log("start d = ", start);
223         console.log('d = ', d);
224         body = render(function() {
225           layout(function() {});
226           return ul(function() {
227             li('Bergamont');
228             li('Chamomile');
229             return span({
230               id: "spanPointer"
231             }, d[0][0].buComments);
232           });
233         });
234         start = new Date * 1;
235         commitWork = yield client.query('COMMIT WORK');
236         console.log("start COMMIT WORK = ", start);
237         client.end;
238         return body;
239       } catch (_error) {
240         e = _error;
241         console.log("error with d = ", e.code);
242         bad = yield client.query('ROLLBACK WORK');
243         client.end;
244         return;
245       }
246     });

          /* I skipped second co func and yield directly to this.body */

294     start = new Date * 1;
295     console.log("time before @body = yield sqlSequence = ", start);
296     this.body = yield sqlSequence;
297     start = new Date * 1;
298     console.log("time after @body = yield sqlSequence = ", start);
299   });

Example 2) ******
 
(the other way is to use a connection pool like this, coffeescript this example

119 #MYSQL connection routine
  120 client = mysql.createPool(mysql_options)
  122 getSql = (sqlQuery, callback) -> #'universal' mysql function MYSQL
  123   if TESTING then console.log 'TESTING SQLQUERY: ',sqlQuery
  124   client.query sqlQuery, (err, rows, fields) ->
  125     if TESTING then console.log "MYSQL error getSql = ",err
  126     if err  #mysql error handling (disconnect remote db)
  127       if err.code isnt 'PROTOCOL_CONNECTION_LOST'
  128         console.log 'APP Mysql Error <> Conn.Lost: ',err.stack
  129         # catch error instead of throw err, so server recovers
  130         process.on "uncaughtException", (err) ->
  131           console.log 'APP SQL ERROR (if err): ',sqlQuery
  132           console.log "APP getSql Mysql Error Uncaught Exception: ", err.s
      tack
  133           return
  134       else  #reconnect
  135         setTimeout ->
  136           console.log 'APP Connection Lost; Re-connecting lost client (res
      tart delay): ',err.stack
  137           client = mysql.createPool(mysql_options)
  138           return
  139         , 700
  140         return
  141     client.release
  142     callback rows,err
  143
and call it like this

             sql = "update  ...bla bla..."
441       getSql sql,(results,err) ->
  442         if err
  443           console.log "error = ", err.stack
  444           res.send "Error /: " + err.code
                   do stuff
  445         else
  446           q20(results)  #.. call next function



  144 #sometimes helps: primitive KEEPALIVE to overcome mysql's wait_timeout
  145 keepalive = ->
  146   client.query "select 1", [], (err, result) ->
  147     console.log 'APP keepalive err = ',err  if err

Karl-L. Rumpf
klr...@gmail.com
Málaga, Spain

On 13/08/14 11:41, idomyjob wrote:

Hi everyone,

I'm currently developing a worker.js script, that watches a beanstalkd tube in order to get an id. When an id is available, we reserve the job, and we can call mysql query.

The problem is that i'd like to avoid mysql server connection error, and also keep my worker going on with a max try of 5, and without deleting the current job where mysql crashes. (In the example below, clientDeleteJob must be call only on mysql query success i guess).

What's the best way to implement this solution ?

Regards,

// Requirements
var bs_client       = "ip:port";
var dest_tube       = "dest_tube";
var nodestalker     = require('nodestalker');
var src_tube        = "src_tube";
var sql_try         = 0;
// Database
var mysql           = require("mysql");
var connection   = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database : 'db',
  socketPath : '/ApplicationsMAMP/tmp/mysql/mysql.sock'
});
connection.on('error', function(err)
{
  console.log(err);
  connection.end();
  //
  getJob();
});
function process(job, callback)
{
  setTimeout(function() {
    callback();
  }, 1000);
}
/*
 *
 */
function getJob()
{
  var client       = nodestalker.Client(bs_client);


  // Watch tube (not dogs)
  client.watch(src_tube).onSuccess(function(data)
  {


    // Reserve current job
    client.reserve().onSuccess(function(job)
    {


      var campaign = JSON.parse(job.data);
      console.log('campaign id : ' + campaign.campaignId);


      getJob();


      process(job, function()
      {


        // Run the query
        connection.query("SELECT * FROM my_table WHERE id = " + connection.escape(campaign.campaignId));


        client.deleteJob(job.id).onSuccess(function(delete_message)
        {
          console.log("delete job : " + job + " message : " + delete_message);
          client.disconnect();
        });


      });


    });


  });


}


// Working
getJob();

--
You received this message because you are subscribed to the Google Groups "node-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-mysql+...@googlegroups.com.
To post to this group, send email to node-...@googlegroups.com.
Visit this group at http://groups.google.com/group/node-mysql.
For more options, visit https://groups.google.com/d/optout.