Sample Dummy Queries for Testing

use temp;
CREATE TABLE IF NOT EXISTS BOOKS (
  BOOK_ID INT(5) NOT NULL AUTO_INCREMENT,
  CATEGORY VARCHAR(30) NOT NULL,
  TITLE VARCHAR(80) NOT NULL,
  DESCRIPTIONS VARCHAR(200) NOT NULL,
  PRIMARY KEY (BOOK_ID)
);

INSERT INTO BOOKS (BOOK_ID, CATEGORY, TITLE, DESCRIPTIONS) VALUES(1, 'Java', 'Concurrency in Practice', 'Java Concurrency Book');
INSERT INTO BOOKS (BOOK_ID, CATEGORY, TITLE, DESCRIPTIONS) VALUES(2, 'Hibernate', 'Hibernate In Action', 'Hibernate In Action Learning');
INSERT INTO BOOKS (BOOK_ID, CATEGORY, TITLE, DESCRIPTIONS) VALUES(3, 'Spring', 'Spring In Action', 'Spring In Action Learning'); 
INSERT INTO BOOKS (BOOK_ID, CATEGORY, TITLE, DESCRIPTIONS) VALUES(4, 'C','Let Us C','C Programming Book');
INSERT INTO BOOKS (BOOK_ID, CATEGORY, TITLE, DESCRIPTIONS) VALUES(5, 'Java','Java for Advanced Learners', 'Java for Advanced Learners by Deepak Modi');
commit;
select * from BOOKS;

use temp;
CREATE TABLE DEPARTMENT(
    DID INTEGER(3) PRIMARY KEY, 
    DNAME VARCHAR(25)
);
CREATE TABLE JOB(
    JOBID INTEGER(3) PRIMARY KEY, 
    DESIGNATION VARCHAR(25)
);

CREATE TABLE EMPLOYEE(
    EID INTEGER(3) PRIMARY KEY, 
    ENAME VARCHAR(25), 
    SALARY INTEGER(8), 
    JOBID INTEGER(3) REFERENCES JOB(JOBID), 
    DID INTEGER(3) REFERENCES DEPARTMENT(DID) ON DELETE SET NULL
);

CREATE TABLE PROJECTS(
    PID INTEGER(3) PRIMARY KEY, 
    TITLE VARCHAR(25), 
    EID INTEGER(3) REFERENCES EMPLOYEE(EID) ON DELETE SET NULL
);

insert into DEPARTMENT values(1, 'MobilePayments');
insert into DEPARTMENT values(2, 'FRM');
insert into DEPARTMENT values(3, '3DS');
insert into DEPARTMENT values(4, 'OPERATIONS');
insert into DEPARTMENT values(5, 'BIZOPS');
insert into DEPARTMENT values(6, 'L1');
insert into DEPARTMENT values(7, 'DBA');
insert into DEPARTMENT values(8, 'PSE');

insert into JOB values(1, 'Developer');
insert into JOB values(2, 'Tester');
insert into JOB values(3, 'ProductionSupport');
insert into JOB values(4, 'Finance');
insert into JOB values(5, 'Banking');
insert into JOB values(6, 'Sales');
insert into JOB values(7, 'Marketing');
insert into JOB values(8, 'PSE');
insert into JOB values(9, 'REPORTS');

insert into EMPLOYEE values(1, 'Deepak Kumar Modi', 125000, 1, 1);
insert into EMPLOYEE values(2, 'Ajay Mahto', 80000, 1, 1);
insert into EMPLOYEE values(3, 'Ajay Ramu', 100000, 9, 4);
insert into EMPLOYEE values(4, 'Navaneeth Kumar', 130000, 9, 5);
insert into EMPLOYEE values(6, 'Manjunath S', 85000, 9, 5);
insert into EMPLOYEE values(7, 'Abhilash', 95000, 9, 4);
insert into EMPLOYEE values(8, 'Pavan K', 145000, 1, 1);
insert into EMPLOYEE values(9, 'Imran Khan', 75000, 8, 3);

insert into PROJECTS values(1, 'PayZapp_Project', 1);
insert into PROJECTS values(2, 'PayApt_Project', 2);
insert into PROJECTS values(3, 'Management_Project', 1);
insert into PROJECTS values(4, 'Report_Sharing_Project', 7);
insert into PROJECTS values(5, 'Prod_Support_Project', 9);

select * from temp.DEPARTMENT;
select * from temp.JOB;
select * from temp.EMPLOYEE;
select * from temp.PROJECTS;

Split Column Data from Mysql

DROP TABLE W2A_FEE;

CREATE TABLE W2A_FEE (CSV_VALUE VARCHAR(100));

INSERT INTO W2A_FEE VALUES('2.85,9,9');

INSERT INTO W2A_FEE VALUES('2.00,9,9');

INSERT INTO W2A_FEE VALUES('2.00,9,9');

INSERT INTO W2A_FEE VALUES('3.5,9,9');

 

SELECT CSV_VALUE FROM W2A_FEE;

 

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(CSV_VALUE, ',', 1), ',', -1) AS Fee,
       SUBSTRING_INDEX(SUBSTRING_INDEX(CSV_VALUE, ',', 2), ',', -1) AS CGST,
       SUBSTRING_INDEX(SUBSTRING_INDEX(CSV_VALUE, ',', 3), ',', -1) AS SGST
FROM   W2A_FEE;

Searching Table Name, Schema name from Mysql Database

Search a Table name, Schema name where table is created in mysql:

SELECT table_schema DBase, table_name TableName FROM information_schema.tables WHERE table_name LIKE '%PARAMETERS%';

MySql configuration for Production Environment

Mysql Configurations for High Volumn of Txns:

Developers knows how to tune Mysql during run time. However some require restart, some not.
The most used commands are (total around 500), samples are given below:

show variables;
show variables like '%tx_isolation%';

mysql> show variables like '%connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 151   |
| max_user_connections | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)

mysql> set global max_connections=200;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 200   |
| max_user_connections | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)


1) SET GLOBAL
2) innodb_buffer_pool_size: This is the first setting to look for InnoDB. The buffer pool is where data and indexes are cached.
    Making it large as much possible will ensure you use memory and not disks for most read operations. 
    Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM).
3) innodb_log_file_size: Size of the redo logs. The redo logs are used to make sure writes are fast and durable and also 
    during crash recovery. Make it 1 Gb for better use. These are two files. innodb_log_file_size = 512M (giving 1GB of redo logs).
4) max_connections: If you are often facing the ‘Too many connections’ error, max_connections is too low. Using a connection pool 
    at the application level or a thread pool at the MySQL level can help here.
    
5) innodb_file_per_table: This setting will tell InnoDB if it should store data and indexes in the shared tablespace 
    (innodb_file_per_table = OFF). Or store in a separate .ibd file for each table (innodb_file_per_table= ON). Having a file per table 
    allows you to reclaim space when dropping, truncating or rebuilding a table. It is also needed for some advanced features such as 
    compression. However it does not provide any performance benefit.    

6) innodb_flush_log_at_trx_commit: the default setting of 1 means that InnoDB is fully ACID compliant. It is the best value when your 
    primary concern is data safety.
7) query_cache_size: The query cache is a well known bottleneck that can be seen even when concurrency is moderate. The best option is 
    to disable it from day 1 by setting query_cache_size = 0 (now the default on MySQL 5.6).
8) For Enabling Logs in Mysql:

    set global general_log=1;
    show variables like '%SQL_LOG%';       //SQL_LOG_OFF should be ON
    set global general_log=ON;             //1 or ON both are same
    show variables like 'GENERAL_LOG%';    //GENERAL_LOG should be ON
    show variables like '%long_query_time%';  
    set @@GLOBAL.long_query_time=1;
    show global variables like '%long_query_time%';
    show session variables like '%long_query_time%';  //Will show the older value. 
                        //Exit Mysql and Re-login and fire the same query.
                        
9) Make sure the database tables are using InnoDB storage engine and READ-COMMITTED transaction isolation level.
   show variables like '%tx_isolation%';   //REPEATABLE-READ becomes slow for insertion, selection.

10) Increase the database server innodb_lock_wait_timeout variable to 500.

MySQLTimeoutException: Statement cancelled due to timeout or client request

Dear Reader,
Recently I was facing this error in Production when my application was connecting to DB.

Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request

I had set Statment.setQueryTimeout(15);  //15 Seconds.
Hence after 15 Seconds, the Application was cancelling the request if the record was not getting inserted in DB.

I tried lot to re-produce this issue as DBA was adamant to accept this as a problem. Finally after lot of R&D I did below.

Complete Error Message:    
Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1757)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2022)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1940)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1925)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at com.enstage.DEEPAK_KUMAR_MODI_JAVA_API_EguardSummary.saveSummary(EguardSummary.java:412)
    ... 5 more

1st Reproduction of Issue:
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("URL");
            st = con.createStatement();            
            
            //Either Throw the exception Manually
            throw new java.lang.Exception(new com.mysql.jdbc.exceptions.MySQLTimeoutException());
        }
        catch (Exception e) {
            e.printStackTrace();
            try {
                con.close();
                st.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        finally{
            System.out.println("Finally");
        }

//Output
java.lang.Exception: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at MySqlTester.main(MySqlTester.java:32)
Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    ... 1 more
Finally



2nd Reproduction of Issue:
Use "SELECT SLEEP(10)"
See below entire program:

//MySqlTester.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*@author Deepak Kumar Modi
*/
public class MySqlTester {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        //SELECT * FROM ACTOR
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://X.X.1.54:3306/deepak_modi?user=root&password=deepakmodi");
            st = con.createStatement();
            System.out.println("Start Date: "+new java.util.Date());
            st.setQueryTimeout(2);    //Max wait is set for 2 Seconds            
            rs = st.executeQuery("SELECT SLEEP(10)");  //DB will wait for 10 Seconds
            int count=0;
            while(rs.next()) {                
                System.out.print("ID: " + rs.getString("actor_id"));
                System.out.print(", FN: " + rs.getString("first_name"));
                System.out.print(", LN: " + rs.getString("last_name"));
                System.out.println();
                count++;
                if(count==5)
                    break;                 
            }
            //Either Throw the exception Manually
            //throw new java.lang.Exception(new com.mysql.jdbc.exceptions.MySQLTimeoutException());
        }
        catch (Exception e) {
            System.out.println("End Date: "+new java.util.Date());
            e.printStackTrace();
            try {
                con.close();
                st.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        finally{
            System.out.println("Finally");
        }
    }
}

//Output (See the exception is thrown just after 2 seconds. Application cancelled the request.)
Start Date: Wed May 17 14:34:11 IST 2017
End Date:   Wed May 17 14:34:13 IST 2017 
com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
Finally
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1622)
    at MySqlTester.main(MySqlTester.java:19)

    
Fix of this issue: Get HOLD of DBA. or Modify Mysql Configuration.    

Read and Write Binary Files to Database in Java

Read and Write Binary Files to Database in Java

Dear reader,
Many times we need to store and read binary files like Images, mp3, mp4 and any non-human readable files
into DB. This is required basically when you work on Case Management System or anything which requires
file storage into Database.

I have written a very simple and complete code with DB script to store and read binary file into DB.
The sequence of completing the tasks are:
1) Create table.
2) Take few files in a directory, which you want to store to DB.
3) Read the content from DB and create a duplicate file into the same directory from where you have read 
   and stored the file into DB.
4) Complete Screenshot for the example.   

-------------------------------------------------------------
Step 1: 
create table FILE_STORE( 
       ID integer(5) not null,
       FILE_NAME varchar(100) not null,  
       USER_NAME varchar(100) not null,  
       BINARY_FILE mediumblob,  
       MOBILE varchar(15),
       primary key (ID) 
); 

--MEDIUMBLOB - 16,777,215 bytes (2^24 - 1)
-------------------------------------------------------------

Step 2:
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SaveBinaryFileToDB {
    static String directoryLocation="E:\\Eguard_Merged_Workspace\\TestProject\\inputFiles\\";
    //static String fileName="Hint_Oracle_History.png";
    static String fileName="Zoobi_Doobi.mp3";
    static File file = new File(directoryLocation+fileName);
    InputStream fis = null;

    public static void main(String[] args) throws SQLException{
        String connectionURL = "jdbc:mysql://192.168.111.111:3306/deepak_temp"; //Change IP address and Port.
        Connection connection = null;
        ResultSet rs = null;  
        PreparedStatement psmnt = null;  
        FileInputStream fis;
        try{
            fis = new FileInputStream(file);
        }
        catch(Exception e){
            e.printStackTrace();
            System.exit(0);
        }

        try {  
            Class.forName("com.mysql.jdbc.Driver").newInstance();  
            connection = DriverManager.getConnection(connectionURL, "root", "root");  
            psmnt = connection.prepareStatement
                    ("INSERT INTO FILE_STORE(ID, FILE_NAME, USER_NAME, BINARY_FILE, MOBILE) values(?,?,?,?,?)");  
            psmnt.setInt(1,1);
            psmnt.setString(2,fileName);  
            psmnt.setString(3,"DeepakModi,Enstage,Bangalore");  
            
            
            fis = new FileInputStream(file);  
            psmnt.setBinaryStream(4, (InputStream)fis, (int)(file.length()));  
            psmnt.setString(5,"+919916473353");  
            int s = psmnt.executeUpdate();  
            if(s>0) {  
                System.out.println("Binary File Uploaded successfully !");  
            }  
            else {  
                System.out.println("Unsucessfull to upload Binary File.");  
            }  
        }  
        catch (Exception ex) {  
            System.out.println("Found some error : "+ex);  
        }  
        finally {  
            connection.close();  
            psmnt.close();  
        }  
    }
}

-------------------------------------------------------------
Step 3:
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ReadBinaryFileFromDB {
    static String directoryLocation="E:\\Eguard_Merged_Workspace\\TestProject\\inputFiles\\";
    //static String fileName="Hint_Oracle_History.png";
    static String fileName="Zoobi_Doobi.mp3";
    static String fileNameSuffix="_Duplicate.mp3";
    //static String fileNameSuffix="_Duplicate.png";
    static File file = new File(directoryLocation+fileName+fileNameSuffix);
    static OutputStream fos = null;
    static InputStream is = null; 

    public static void main(String[] args) throws Exception{
        String connectionURL = "jdbc:mysql://192.168.111.111:3306/deepak_temp"; //Change IP address and Port.
        Connection connection = null;
        ResultSet rs = null;  
        PreparedStatement psmnt = null;  
        try {  
            Class.forName("com.mysql.jdbc.Driver").newInstance();  
            connection = DriverManager.getConnection(connectionURL, "root", "root");
            psmnt = connection.prepareStatement("SELECT BINARY_FILE from FILE_STORE where ID=? and FILE_NAME=?");  
            psmnt.setInt(1,1);
            psmnt.setString(2,fileName);
            rs=psmnt.executeQuery();  
            fos = new FileOutputStream(directoryLocation+fileName+fileNameSuffix);
            
            if(rs.next()){  
                is = rs.getBinaryStream(1);
                System.out.println("Length of re-generated file.."+is.available());                
                byte[] buf = new byte[104];
                int read = 0;
                while ((read = is.read(buf)) > 0) {
                    fos.write(buf, 0, read);
                }
            }
            fos.close();
            is.close();
        }
        catch (Exception ex) {  
            System.out.println("Found some error : "+ex);  
            ex.printStackTrace();
        }
    }
}
-------------------------------------------------------------
Screen Shot: 

Attached Screenshot contains 5 markers, which is meant for below points:
1. Project Name
2. Original Binary File
3. Re-Created Binary File from DB
4. Mysql JDBC Jar
5. DB Script file

Partitioning a table in Mysql5.1.x with Date, Time, Timestamp

This blog describes how to use table partition in Mysql-5.1.x to enhance query execution in Mysql.

Mysql partition for a table having "Timestamp" datatype of a column never works properly.
It will work with DateTime datatype. So you have to change datatype from "Timestamp" to "DATETIME".

So change your "timestamp" datatype to "DateTime" and then following these lines:-
Both datatypes are almost same, however DateTime takes 8 bytes in memory while Timestamp takes 4 bytes.

//Description of table.
mysql> desc Table_Name;
+------------------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------------------+-------+
| Id | int(11) | NO | PRI | NULL | |
| TxnId | int(11) | NO | PRI | NULL | |
| Time | datetime | NO | PRI | 0000-00-00 00:00:00 | |
| Name | varchar(64) | NO | | NULL | |
----------------------------------------------
----------------------------------------------
----------------------------------------------
| BMiningClusterId | int(11) | YES | | NULL | |
+------------------+-------------+------+-----+---------------------+-------+


//Changing datatype from timestamp to DATETIME
1) ALTER TABLE Table_NAME modify Time datetime not null default '0000-00-00 00:00:00';

//Checking partition, change the database_name and Table_name
2) select PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS, TABLE_NAME from information_schema.PARTITIONS where TABLE_SCHEMA='database_name' and TABLE_NAME='TableName';

//Remove existing partitions, data will not be deleted.
3) ALTER TABLE Table_Name REMOVE PARTITIONING;

//Create new partitions.
4) alter table Table_NAME partition by range(to_days(Time)) (
PARTITION p2009_1 values less than (to_days('2009-02-01 00:00:00')),
PARTITION p2009_2 values less than (to_days('2009-03-01 00:00:00')),
PARTITION p2009_3 values less than (to_days('2009-04-01 00:00:00')),
PARTITION p2009_4 values less than (to_days('2009-05-01 00:00:00')),
PARTITION p2009_5 values less than (to_days('2009-06-01 00:00:00')),
PARTITION p2009_6 values less than (to_days('2009-07-01 00:00:00')),
PARTITION p2009_7 values less than (to_days('2009-08-01 00:00:00')),
PARTITION p2009_8 values less than (to_days('2009-09-01 00:00:00')),
PARTITION p2009_9 values less than (to_days('2009-10-01 00:00:00')),
PARTITION p2009_10 values less than (to_days('2009-11-01 00:00:00')),
PARTITION p2009_11 values less than (to_days('2009-12-01 00:00:00')),
PARTITION p2009_12 values less than (to_days('2010-01-01 00:00:00')),


PARTITION p2010_1 values less than (to_days('2010-02-01 00:00:00')),
PARTITION p2010_2 values less than (to_days('2010-03-01 00:00:00')),
PARTITION p2010_3 values less than (to_days('2010-04-01 00:00:00')),
PARTITION p2010_4 values less than (to_days('2010-05-01 00:00:00')),
PARTITION p2010_5 values less than (to_days('2010-06-01 00:00:00')),
PARTITION p2010_6 values less than (to_days('2010-07-01 00:00:00')),
PARTITION p2010_7 values less than (to_days('2010-08-01 00:00:00')),
PARTITION p2010_8 values less than (to_days('2010-09-01 00:00:00')),
PARTITION p2010_9 values less than (to_days('2010-10-01 00:00:00')),
PARTITION p2010_10 values less than (to_days('2010-11-01 00:00:00')),
PARTITION p2010_11 values less than (to_days('2010-12-01 00:00:00')),
PARTITION p2010_12 values less than (to_days('2011-01-01 00:00:00'))
);


//Addition of new partitions
4) alter table Table_NAME add partition(
partition p2011_1 values less than (to_days('2011-02-01 00:00:00')),
partition p2011_2 values less than (to_days('2011-03-01 00:00:00')),
partition p2011_3 values less than (to_days('2011-04-01 00:00:00')),
partition p2011_4 values less than (to_days('2011-05-01 00:00:00')),
partition p2011_5 values less than (to_days('2011-06-01 00:00:00')),
partition p2011_6 values less than (to_days('2011-07-01 00:00:00')),
partition p2011_7 values less than (to_days('2011-08-01 00:00:00')),
partition p2011_8 values less than (to_days('2011-09-01 00:00:00')),
partition p2011_9 values less than (to_days('2011-10-01 00:00:00')),
partition p2011_10 values less than (to_days('2011-11-01 00:00:00')),
partition p2011_11 values less than (to_days('2011-12-01 00:00:00')),
partition p2011_12 values less than (to_days('2012-01-01 00:00:00'))
);

//Again watching partitions that gets created.
5) select PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS, TABLE_NAME from information_schema.PARTITIONS where TABLE_SCHEMA='database_name' and TABLE_NAME='TableName';

//Simple select query.
6) select * from TABLE_NAME where Time>'2009-09-01 00:00:00' and Time<='2009-09-30 23:59:59'; //Checking query execution and advantage of partition, this will show you which partitions get scanned while executing this query. 7) explain partitions select * from TABLE_NAME where Time>'2009-09-01 00:00:00' and Time<='2009-09-30 23:59:59';

-----------------------------------End---------------------------------------

Partitioning a table in Mysql

Partitioning a table makes your query execution faster. As your database will look for a record in near vicinity because you have partitioned the table.
See in this way:

You have 100000 of records in a table call "COLLTable" where Time is a column.
Now if you will look for a record where Time is like this:-'2008-06-12 12:12:23'
Then Database will have to look in all 100000 rows, but if you will do the partition based on (assume)month wise, it will look only in June month table partition.


Partitioning can be done on 4 basis:-
* range
* list
* hash
* key


But remember, Partitioning in mysql is available in version 5.1.x onwards.
Anyway you can check this by firing query:

*) SHOW VARIABLES LIKE '%partition%';

To see the mysql version:-
*) select version();


Now how to achieve partition:-

//If you are creating new table, fire this query, 3-months in one partition, I assume Time is one column here in the table:
*) CREATE TABLE COLLTable(
Id INT not null,
Time Timestamp NULL,
DISK_IOWRITE FLOAT not null,
PRIMARY KEY(Id,Time) )

PARTTIION BY LIST (MONTH(Time)) (
PARTITION p1 VALUES IN (3,4,5),
PARTITION p2 VALUES IN (6,7,8),
PARTITION p3 VALUES IN (9,10,11),
PARTITION p4 VALUES IN (12,1,2)
);


//If you have already a table with "Time" column, add partitions there, use following query:-
*) Alter table COLLTable PARTITION BY LIST (MONTH(Time)) (PARTITION P1 VALUES IN (1),PARTITION P2 VALUES IN (2),PARTITION P3 VALUES IN (3),PARTITION P4 VALUES IN (4),PARTITION P5 VALUES IN (5),PARTITION P6 VALUES IN (6),PARTITION P7 VALUES IN (7),PARTITION P8 VALUES IN (8),PARTITION P9 VALUES IN (9),PARTITION P10 VALUES IN (10),PARTITION P11 VALUES IN (11),PARTITION P12 VALUES IN (12));

//Now check this details, The entry goes to INFORMACTION_SCHEMA database and in PARTITION table:-
*) select TABLE_SCHEMA, TABLE_NAME,TABLE_ROWS, PARTITION_NAME, PARTITION_EXPRESSION from information_schema.PARTITIONS where TABLE_NAME='COLLTable';


//You can drop the partition also, but remember after dropping a partition the data will also be removed from that partition.
//Mysql creates different data files for different partitions, so dropping a partition will drop that file too.
//If you don't have "Time" as column, you can create partition for "Range" that takes integer type of values like:-

CREATE TABLE CollTable ( empId integer not null, salary float ) PARTITION BY RANGE (empId)
(
PARTITION P1 VALUES LESS THAN (1000),
PARTITION P2 VALUES LESS THAN (10000)
);


//End

Mysql Sample Queries

Mysql Sample Queries:

1) Create a duplicate table:
CREATE TABLE DUPLICATE_TABLE LIKE BANK_ID;   
//Will create duplicate empty table with same version of original table storage format. Indexes/Constraints will be copied.

CREATE TABLE DUPLICATE_TABLE as SELECT * FROM BANK_ID;  
//Will create table with data, Indexes/Constraints will not be copied. Need to re-create indexes/constraints.

INSERT into NEW_TABLE SELECT * from OLD_TABLE where Id=999999;

2) Dumping databases:
mysqldump --lock-all-tables -u root -p --all-databases > filename.sql     //For all Databases
mysqldump -u root dbname > filename.sql                                   //For one database without locking tables.

3) Install new version of mysql:
sudo apt-get install mysql-client-5.6 mysql-client-core-5.6
sudo apt-get install mysql-server-5.6

4) Import the dump file to mysql:
mysql -u root -p < filename.sql

5) Stopping/Starting mysql in unix:
service mysql status
service mysql stop
service mysql start
service mysql restart

or

/etc/init.d/mysql status
/etc/init.d/mysql start
/etc/init.d/mysql stop

6) Mysql config file is found in /etc directory in Unix: /etc/my.cnf 

7) Create Databse:
Create database NEW_Database;
use NEW_Database;
show tables;

8) Show variables in mysql:
show variables;
show variables where variable_name like '%connectio%';
show variables where variable_name like '%table_names%';  (lower_case_table_names=1 means case in-sensitive).

9) Setting variable value:
set global max_connections=200;

10) Create new user:
Create user 'root'@'X.X.X.54' identified by 'root';
Grant all privileges on *.* to 'root'@'X.X.X.54';
Grant all privileges on *.* to 'root'@'X.X.X.54' identified by 'root';
Select Host, User, Password from mysql.user;

11) Alter table:
alter table TABLE_NAME add NEW_COLUMN_NAME int not null;
alter table TABLE_NAME add foreign key(NEW_COLUMN_NAME) references OTHER_TABLE(Id);
alter table TABLE_NAME change COL_NAME NEW_COL_NAME float not null;
alter table TABLE_NAME modify COL_NAME bigint not null;
alter table TABLE_NAME add (TimeZoneId varchar(32) not null, TimeOffsetFromGMT int not null);

CREATE TABLE TABLE_NAME (Id int(11) NOT NULL auto_increment, TxnSetId int(11) NOT NULL, PRIMARY KEY (Id)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
---------------END---------------------