Execute SQL script in Linux background - not closed after closing terminal

If is needed to execute some SQL script which may take too long and you want it to continue running even after closing the terminal it can be done via the following:

Create you SQL script.

Once it is loaded into server run the following example command:

nohup mysql -u user-ppassword < script.sql > your_outputfile.out&

Note: there is no whitespace between -p and the password!

Using the above, it will write the output into the file you have decided, in our case your_ouputfile.out

Once this has been executed you get the PID, then to avoid process to die after closing the terminal just use the "disown" command like this: disown -h PID

Afterwards you can safely close your terminal and your process will continue executing in background.

Events in MySQL

Events in MySQL are a very powerful tool for executing scheduled planned tasks you may need.

They are not depenant on you application but on RDMS, therefore it will handle it faster than your application.

Events I find them useful for tasks such as: updating aggregated data, archive old data, etc.

First of all you must make sure the evens scheduler is enabled:

SHOW PROCESSLIST;

And there should appear one process with user "event_scheduler"

If not it can be set up like this, either setting  event_scheduler=ON in MySQL configuration file or with command:

SET GLOBAL event_scheduler = ON;


A simple example of event could be like this:

CREATE DEFINER=`root`@`127.0.0.1` EVENT `calculate_summaries_data` ON SCHEDULE EVERY 10 MINUTE STARTS '2016-05-19 13:25:00' ON COMPLETION PRESERVE ENABLE DO BEGIN

 -- Example SQL, use the one you might need for your requirements

UPDATE  summary_stats s,
    (SELECT 
        country AS c,
            region AS r,
            COUNT(*) AS transactions
    FROM
        transactions_done t
    WHERE
            DATE(transaction_date) = CURDATE()
            
    GROUP BY country,region) AS t 
SET 
    s.amount_transactions = t.transactions_done
WHERE
    s.country = t.c AND s.region = t.r
        AND date = CURDATE();
                
        END


Once created you can check the events which are scheduled by:

show events;

Will display the events which are set up. And to check the definition of a concrete one:

show create event calculate_summaries_data;


Some good references about this:


https://www.sitepoint.com/how-to-create-mysql-events/

https://dev.mysql.com/doc/refman/5.7/en/events-metadata.html

http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

http://stackoverflow.com/questions/11411921/how-do-i-get-multiple-mysql-statements-in-one-scheduled-event

http://stackoverflow.com/questions/3070277/mysql-event-scheduler-on-a-specific-time-everyday

Produce XML format response body

In case you need to produce a response shown as XML format using Spring you can just add to the @RequestMapping annotation the following:

@ResponseBody
@RequestMapping(value = "/url_mappin", method = RequestMethod.GET, produces = "application/xml")