CODEWITHSUNDEEP
mysqllinuxcron
user1260928
user1260928

Reputation: 3429

Mysql - Linux : Insert a record in a table at a specific date and time

I need to insert into a MySql table a record each year on 31st December 11:59pm.

The MySql database is stored in Ubuntu system.
I don't know if it can be done directly in MySql or if I need to create a shell script in Ubuntu.

If anyone has an idea about this...

Thanks.

Upvotes: 0

Views: 103

Answers (4)

Puneet Pandey
Puneet Pandey

Reputation: 960

  1. Best you can do with MySQL is to use a Trigger if you want to insert the data on some event in DB.

  2. You can do it programmatically by using some scheduler framework like Quartz which would call your insert DAO method.

  3. And the simplest of all would be to have a script with Cron job/script in place which directly connects to your DB and inserts the data in specified time interval.

Upvotes: 1

Leo128
Leo128

Reputation: 163

If I am not mistaken, starting from MySQL 5.1 we are provided event scheduling.

So, given your_db is usable, you could do:

CREATE DATABASE IF NOT EXISTS your_db;

USE your_db;

CREATE TABLE `some_table` (
  `ev` varchar(100) NOT NULL,
  PRIMARY KEY (`ev`)
);

SET GLOBAL event_scheduler = ON;

CREATE EVENT your_event ON SCHEDULE EVERY 1 MINUTE STARTS '2014-08-20 00:10:00' ON COMPLETION PRESERVE ENABLE
DO
  INSERT INTO your_db.some_table (ev) VALUES (CONCAT_WS(' ', 'En Event Occurred at', NOW()));

To schedule a simple event occurring every 1 minute.

if it works (it does on my ubuntu 14.04 box), then I guess you'd just customize this snippet. You can change 'MINUTE' to 'YEAR'.

If it doesn't , please let us know.

For your reference:

https://dev.mysql.com/doc/refman/5.1/en/events.html

BTW: I'd rather not stick to a crontab script... in order to configure crontab you are required different set of privileges that those required for accessing mysql. Even so, it might still be a good choice in some occasions.

Upvotes: 1

Michał Szkudlarek
Michał Szkudlarek

Reputation: 1502

You can use scheduler for that: https://dev.mysql.com/doc/refman/5.1/en/create-event.html

Simple scheduler event:

DELIMITER $$
CREATE EVENT my_event
ON SCHEDULE EVERY '1' YEAR
STARTS '2015-12-31 23:59:00'
DO 
BEGIN
 -- insert your stuff
END$$

DELIMITER ;

I think you have to enable it by setting event_scheduler to ON.

Upvotes: 3

Devart
Devart

Reputation: 121912

Create an event - CREATE EVENT. Set interval - YEAR, and set data and time when to start.

Upvotes: 1

Related Questions