Mysql Date

Some useful SQL commands to get dates in Mysql server.

select curdate()	3/10/2011
select curdate()-1	20110309
select curdate() - INTERVAL 1 DAY	3/9/2011

Mysql enable logging

mysql > tee output.log

Mysql has default built in function to logging transactions. In Mysql client, type above command. It will enabled logging in transaction. "output.log" file is created in Mysql bin directory.
 

Find process id in linux

Use the below command to find the process id of your application. Here app name is "TestApp.jar".

ps -ef | grep TestApp

Run .jar file with log

Use the following command to run jar file for logging errors.

nohup java -jar -Xms1024m -Xmx2048m Test.jar &

Above command will run the "Test.jar" file in background and produce error file called "nohup" in same directory. ( All system out prints will be redirected into this file). So, developers can catch errors using the "nohup".

Mysql Current date & time

Use the Mysql server curdate() and curtime() functions to get current date & time values from the system.
Below SQL demos, the usage of the functions.

insert into test(cur_date,cur_time) values(curdate(),curtime());

Mysql data unload

Use the following SQL to unload data from Mysql server without executing command. This method is good for very large set of data.

select * from table into outfile "file.txt"

Note :- file.txt will be created at"MySQL/data/database" directory.

Mysql Triggers Example

Mysql triggers are like schedulers run on table changes.  When INSERT, DELETE, UPDATE command executions, these triggers are executed automatically.

Below is a sample of SQL commands to create and test Triggers when insert happens to "test1" table.

Create below four tables and insert values into "test3" & "test4" tables.

Trigger

This Trigger is executed when insert happens into "test1" table. It gets "test1.a1" value and insert it into "test2" table. And, delete it from "test3" table. Update  "test4" b4 value.

Create Tables

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);

Create Trigger

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|

Insertions

delimiter ;

INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Executes following SQL to run the Trigger.

INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);