1

I am building a product management platform using node.js and my data is saved in a MySQL database. At this point I needed to use MySQL's event scheduler to perform some actions at specific times and it all goes great when I test it in the MySQL console but when it comes to do it through node.js I am having the following error:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE EVENT IF NOT EXISTS test ON SCHEDULE AT DATE_ADD(NOW(), ' at line 1

I am using node-orm2 to connect to my database and I am doing the following

var query = 'DROP EVENT IF EXISTS test;' +
  ' DELIMITER $$' +
  ' CREATE EVENT IF NOT EXISTS test' +
  '     ON SCHEDULE AT DATE_ADD(NOW(), INTERVAL 10 SECOND) ON COMPLETION PRESERVE ENABLE' +
  '     DO BEGIN' +
  '     SELECT @data_id:=quiz_platforms_has_quiz_data.quiz_data_id,' +
  '             @date_end:=quiz_platforms_has_quiz_data.timestamp_end' +
  '     FROM quiz_platforms_has_quiz_data' +
  '         WHERE quiz_platforms_has_quiz_data.quiz_platforms_id=5' +
  '         AND quiz_platforms_has_quiz_data.timestamp_start >= CURDATE()' +
  '         ORDER BY timestamp_start LIMIT 1;' +
  '     UPDATE quiz_platforms SET quiz_platforms.current_quiz_id=@data_id' +
  '         WHERE quiz_platforms.id=5;' +
  '     IF @date_end>0 THEN' +
  '         ALTER EVENT test ON SCHEDULE AT @date_end;' +
  '         ALTER EVENT test ENABLE;' +
  '     ELSE' +
  '         DROP EVENT test;' +
  '     END IF;' +
  '     END $$' +
  ' DELIMITER ; ';
orm_db.driver.execQuery(query, function(err, result) {
  if (err) {
    console.log(NOW + " - [DB-Event-ERR] " + err);
  }
});

The thing is: I already know neither this library nor node-mysql itself support this and I wanted to know if there is a way.

I also thought about using a cron manager and do this from the server instead of using the database's event schedule but I have no idea what would be the lightest solution.

[EDIT]

I need to use the event scheduler through node.js and I haven't successfully done it. I can't reveal much details because of company policy. Let's just say we have devices that run a certain task during a time interval. I have to update a column in a database table so that it represents which task is currently running in that device.

table devices (columns): id, name, description, current_task;

table tasks(columns): id, name, description;

So if I were to check the device current_task, I would know just by going to the device's table.

I have to be able to update that column's value as soon as the task changes.

ana.arede
  • 698
  • 7
  • 12
  • 1
    Did you see http://stackoverflow.com/questions/14805742/cronjob-or-mysql-event ? – meder omuraliev Mar 17 '16 at 15:01
  • I saw it just now, my coworker found it. Thanks! Even though it lacks performance comparisons that I would like to have... – ana.arede Mar 17 '16 at 15:04
  • You might avoid the need for cron and/or event scheduler if you ask the question about your real problem instead your attempted solution.. – Mjh Mar 17 '16 at 15:08
  • My real problem is there but I will edit it to make it more clear, – ana.arede Mar 17 '16 at 15:12
  • If a device is running a task at some interval, how does the device "know" when to start running the task and which task it should run? Is there an interface that connects these sensors / devices with tasks in MySQL or is it a disconnected system and you're using MySQL purely based on the fact that a device **will** run at specific time? – Mjh Mar 17 '16 at 15:24
  • Each device is a module that runs surveys, lets say. It has sim cards built in and every 30 minutes sends a report. It's also stored in the database which survey is running at that moment so I can know to which survey the reports belong to. This product is for a client and in my part of the project I simply take care of the backoffice that the client will see and manage. The thing is, the client itself defines when and what the surveys will be. From the other side of the project, the technical team needs me to update that value of ''current_survey" so they won't have to dig through many tables – ana.arede Mar 17 '16 at 16:48

0 Answers0