mysql Archives - Ebhor.com

Setting innodb_lock_wait_timeout

May 20, 2023May 20, 2023 by manish

What is innodb_lock_wait_timeout ? In MySql innodb_lock_wait_timeout is an Innodb transaction wait time in seconds for a row lock. An another transaction has to wait for specified innodb_lock_wait_timeout if a transaction is taking place. innodb-lock-wait-timeout in a system variable in global and session scope this variable can set dynamically to both scopes. We can set … Read more

How to create a table in MySql

May 2, 2023May 2, 2023 by bharti

MySQL is a widely used relational database management system. It is used by developers, database administrators, and data analysts to store and manage data. In this tutorial, we will discuss how to create a table in MySQL using the CREATE TABLE statement. The CREATE TABLE statement is used to create a new table in a … Read more

MySql dump using java

March 19, 2023March 19, 2023 by manish

To take mysql database backup we have Runtime.getRuntime().exec(executeCmd). executeCmd is command that will take backup of mysql database. String executeCmd contains the mysqldump to backup database. filePath variable contains the downloaded sql file.

public class dump {

    public static void main(String s[]) {


        String dbName = "Db";
        String dbUser = "user";
        String dbPass = "pass";
        String filePath = "C:\\myFirstDump.sql";
        int status ;

        String executeCmd = "C:\\wamp\\bin\\mysql\\mysql5.5.8\\bin\\mysqldump -u " + dbUser + " -p" + dbPass + " " + dbName + " -r " + filePath;

        try {
            Process runtimeProcess = Runtime.getRuntime().exec(executeCmd);
            status = runtimeProcess.waitFor();
            if (status == 0) {
                System.out.println("Backup taken successfully");
            } else {
                System.out.println("Could not take mysql backup");
            }
        } catch (Exception e) {
            System.out.println("Exception " + e);
        }
    }
}

public class dump {

public static void main(String s[]) {

String dbName = "Db";

String dbUser = "user";

String dbPass = "pass";

String filePath = "C:\\myFirstDump.sql";

int status ;

String executeCmd = "C:\\wamp\\bin\\mysql\\mysql5.5.8\\bin\\mysqldump -u " + dbUser + " -p" + dbPass + " " + dbName + " -r " + filePath;

try {

Process runtimeProcess = Runtime.getRuntime().exec(executeCmd);

status = runtimeProcess.waitFor();

if (status == 0) {

System.out.println("Backup taken successfully");

} else {

System.out.println("Could not take mysql backup");

}

} catch (Exception e) {

System.out.println("Exception " + e);

}

Give initial auto increment value while table creation

February 2, 2023February 2, 2023 by manish

When we create a table we can give a auto increment value to a primary key filed. This field is auto incremented by one for newly inserted value. To start auto increment from specified number AUTO_INCREMENT = value is used while creating table. Example:

CREATE TABLE students (
   id int(5) not null AUTO_INCREMENT, 
   firstname varchar(30),
   lastname varchar(30),
   class varchar(30),
    PRIMARY KEY (id)
) AUTO_INCREMENT = 1001;

CREATE TABLE students (

id int(5) not null AUTO_INCREMENT,

firstname varchar(30),

lastname varchar(30),

class varchar(30),

PRIMARY KEY (id)

) AUTO_INCREMENT = 1001;

If you have already created a table and then want … Read more

Finding all column name of table

February 2, 2023 by manish

Suppose we have a table user and we want to fetch all its column name.For this we use INFORMATION_SCHEMA. we will use following query to fetch all column name of table

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='social' 
    AND `TABLE_NAME`='user';

SELECT `COLUMN_NAME`

FROM `INFORMATION_SCHEMA`.`COLUMNS`

WHERE `TABLE_SCHEMA`='social'

AND `TABLE_NAME`='user';

Above query will fetch all columns of user table available in social database. Read More Setting innodb_lock_wait_timeout Set time zone in mysql … Read more

Drop command to remove a column from a table.

February 2, 2023February 2, 2023 by bharti

suppose we have a column address in table employee.

create table employee( id int(10) NOT NULL AUTO_INCREMENT,
       name varchar(40),
       department varchar(30),
       address varchar(50),
       PRIMARY KEY(id));

create table employee( id int(10) NOT NULL AUTO_INCREMENT,

name varchar(40),

department varchar(30),

address varchar(50),

PRIMARY KEY(id));

To see the description of table we use desc employee that will produce following result here we can see the description of table.

mysql&gt; desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(10)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(40) | YES  |     | NULL    |                |
| department | varchar(30) | YES  |     | NULL    |                |
| address    | varchar(50) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc employee;

+------------+-------------+------+-----+---------+----------------+

+------------+-------------+------+-----+---------+----------------+

| id | int(10) | NO | PRI | NULL | auto_increment |

+------------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

if we want to eliminate the column address from employee table then we use Drop command. Syntax:

ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

1	ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

Drop query

ALTER TABLE employee DROP COLUMN address;

1	ALTER TABLE employee DROP COLUMN address;

Date and Time

February 2, 2023February 2, 2023 by manish

There are various pre defined function for data and time we will see them one by one. If you want to show current time then we use CURTIME().It returns time in HH:MM:SS string format

mysql>SELECT CURTIME();


+-----------+
| CURTIME() |
+-----------+
| 16:06:23  |
+-----------+
1 row in set (0.06 sec)

mysql>SELECT CURTIME();

+-----------+

| CURTIME() |

+-----------+

| 16:06:23 |

+-----------+

1 row in set (0.06 sec)

below is HHMMSS in numeric format

mysql> select CURTIME()+0;
+-------------+
| CURTIME()+0 |
+-------------+
|      162404 |
+-------------+
1 row in set (0.06 sec)

mysql> select CURTIME()+0;

+-------------+

| CURTIME()+0 |

+-------------+

| 162404 |

+-------------+

1 row in set (0.06 sec)

To select current data we use CURDATE().It returns data in YYYY-MM-DD string format. … Read more

Drop command: To eliminate a column from a table.

February 2, 2023February 2, 2023 by bharti

To eliminate a column from table we use drop command. Suppose we have table employee with an extra field Address and we want to eliminate this.

create table student(id int(11) NOT NULL AUTO_INCREMENT,
                     name varchar(40),
                     address varchar(40),
                  PRIMARY KEY(id));

create table student(id int(11) NOT NULL AUTO_INCREMENT,

name varchar(40),

address varchar(40),

PRIMARY KEY(id));

mysql&gt; desc student;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(40) | YES  |     | NULL    |                |
| address | varchar(40) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc student;

+---------+-------------+------+-----+---------+----------------+

+---------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+---------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

To remove this column we use Drop query. Syntax:

ALTER TABLE table_name drop column_name;

1	ALTER TABLE table_name drop column_name;

ALTER TABLE student drop address;

1	ALTER TABLE student drop address;

mysql&gt; desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc student;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+-------+-------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

creating a new user in mysql

February 2, 2023February 2, 2023 by manish

Here we are creating user with name manish and password for manish is password;

CREATE USER 'manish'@'localhost' IDENTIFIED BY 'password';

1	CREATE USER 'manish'@'localhost' IDENTIFIED BY 'password';

Creating a user and force user to change password at first login for that PASSWORD EXPIRE is used.

CREATE USER 'manish'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE;

1	CREATE USER 'manish'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE;

Creating a user and expiring the password after 180 days, PASSWORD EXPIRE INTERVAL 180 DAY is used

CREATE USER 'manish'@'localhost' IDENTIFIED BY 'password'  PASSWORD EXPIRE INTERVAL 180 DAY;

1	CREATE USER 'manish'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE INTERVAL 180 DAY;

Creating a user … Read more

Changing primary key of table

February 2, 2023February 2, 2023 by manish

We have a table basic profile with following table structure

create table basic_profile(b_id int(10) NOT NULL AUTO_INCREMENT,
                           id1 int(10),
                           id2 int(10),
                          fname varchar(30),
                         lname varchar(30),
                         mailid varchar(50),
                         address varchar(100),
                        primary key(b_id));

create table basic_profile(b_id int(10) NOT NULL AUTO_INCREMENT,

id1 int(10),

id2 int(10),

fname varchar(30),

lname varchar(30),

mailid varchar(50),

address varchar(100),

primary key(b_id));

mysql> desc basic_profile;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| b_id    | int(10)      | NO   | PRI | NULL    | auto_increment |
| id1     | int(10)      | YES  |     | NULL    |                |
| id2     | int(10)      | YES  |     | NULL    |                |
| fname   | varchar(30)  | YES  |     | NULL    |                |
| lname   | varchar(30)  | YES  |     | NULL    |                |
| mailid  | varchar(50)  | YES  |     | NULL    |                |
| address | varchar(100) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
7 rows in set (0.06 sec)

mysql> desc basic_profile;

+---------+--------------+------+-----+---------+----------------+

+---------+--------------+------+-----+---------+----------------+

| b_id | int(10) | NO | PRI | NULL | auto_increment |

| id1 | int(10) | YES | | NULL | |

| id2 | int(10) | YES | | NULL | |

+---------+--------------+------+-----+---------+----------------+

7 rows in set (0.06 sec)

we don’t want to use it as primary key so first we removed its auto increment the we will remove it from primary key queries for these are

//remove auto increment
ALTER TABLE basic_profile MODIFY b_id INT NOT NULL;

//remove from primary key
//ALTER TABLE TABLE_NAME DROP PRIMARY KEY
ALTER TABLE basic_profile DROP PRIMARY KEY;

//remove auto increment

ALTER TABLE basic_profile MODIFY b_id INT NOT NULL;

//remove from primary key

//ALTER TABLE TABLE_NAME DROP PRIMARY KEY

ALTER TABLE basic_profile DROP PRIMARY KEY;

If you want to make primary key to some other column than we can … Read more