TIMESTAMP DEFAULT :- in this when new row get inserted or existing row get updated that time current timestamp get inserted.

CURRENT_TIMESTAMP :- in this when row get inserted that time current timestamp get inserted.no change on update.

ON UPDATE CURRENT_TIMESTAMP :- when row get affected that time current timestamp get inserted.

Regards

Prasanna Shejwal

Introduction – Few days back somebody ask me about “How to swap data in two columns?”. Before that I have never thought about such situation…

1] Login to MySQL Server and create Datatabase –

root@IndianGNU:/home/arun.bagul# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 137 to server version: 5.0.21-Debian_3ubuntu1-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> show databases;
+———————————-+
| Database |
+———————————-+
| information_schema |
| mysql |
+———————————-+
2 rows in set (0.04 sec)

mysql>
mysql> create database arunbagul;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+———————————-+
| Database |
+———————————-+
| information_schema |
| mysql |

| arunbagul |
+———————————+
3 rows in set (0.04 sec)

2 ] Now create Table in ‘arunbagul’ database –

mysql> use arunbagul;
Database changed

mysql> create table student ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY , name varchar(30), surname varchar(30) );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+———————————–+
| Tables_in_arunbagul |
+———————————–+
| student |
+———————————–+
1 row in set (0.00 sec)

mysql>

mysql> desc student;
+———+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| surname | varchar(30) | YES | | NULL | |
+———+————-+——+—–+———+—————-+
3 rows in set (0.01 sec)

mysql>

3 ] Now insert values in ‘student’ table –

mysql> INSERT INTO student VALUES (”,’Arun’ , ‘Bagul’);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> INSERT INTO student VALUES (”,’Yogesh’ , ‘Nikam’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO student VALUES (”,’Nishit’ , ‘Shah’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO student VALUES (”,’Binish’ , ‘Philip’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO student VALUES (”,’Deven’ , ‘Jadhav’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from student;
+—-+——–+—————–+
| id | name | surname |
+—-+———-+—————+
| 1 | Arun | Bagul |
| 2 | Yogesh | Nikam |
| 3 | Nishit | Shah |
| 4 | Binish | Philip |
| 5 | Deven | Jadhav |
+—-+——–+———+
5 rows in set (0.00 sec)

mysql>

4 ] Swap data in two cloumns in ‘student’ table –

mysql> UPDATE student as tab1, student as tab2 set tab1.name = tab1.surname, tab1.surname=tab2.name WHERE tab1.id = tab2.id AND tab2.id = tab1.id;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> select * from student;

+—-+———-+—————+
| id | name | surname |
+—-+———-+—————+
| 1 | Bagul | Arun |
| 2 | Nikam | Yogesh |
| 3 | Shah | Nishit |
| 4 | Philip | Binish |
| 5 | Jadhav | Deven |
+—-+———-+————–+
5 rows in set (0.00 sec)

mysql>

Thank you,

Arun Bagul , Prasnna and Devendra

 when  you loss  mysql  root  pasword. then  stop  mysql  server.. then  follow  below  steps.

 * Method -1

1]  Start  MySQL without  loading  grant access…

root@indiangnu:~# mysqld –skip-grant-tables &
[1] 10197
root@indiangnu:~# 071129  9:41:39  InnoDB: Started; log sequence number 0 43655
071129  9:41:39 [Note] mysqld: ready for connections.
Version: ‘5.0.22-Debian_0ubuntu6.06.3-log’  socket: ‘/var/run/mysqld/mysqld.sock’  port: 3306  Debian Etch distribution
2] Start MySQL client

root@indiangnu:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use  mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> UPDATE user SET Password=PASSWORD(‘password’) WHERE User=’root’;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
root@indiangnu:~#

* Then  kill current  MySQL process service… and  start  normal  MySQL  daemon

* Method -2  

1]  Start  MySQL  in safe mode without  loading  grant access…

root@indiangnu:~# mysqld_safe –skip-grant-tables &
[1] 10216
root@indiangnu:~# Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[10277]: started

2] Start MySQL client
root@indiangnu:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> UPDATE user SET Password=PASSWORD(‘password’) WHERE User=’root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
root@indiangnu:~#

Thank  you,

Arun Bagul

What is GreenSQL?

GreenSQL is an Open Source database firewall used to protect databases from SQL injection attacks. GreenSQL works in a proxy mode and has built in support for MySQL. The logic is based on evaluation of SQL commands using a risk scoring matrix as well as blocking known db administrative commands (DROP, CREATE, etc). GreenSQL is distributed under the GPL license.

How it works?

The GreenSQL database firewall provides database protection using a number of methods:

• Blocking administrative and sensitive SQL commands.
• Calculating risk of each query and blocking queries with high risk.

1) Blocking specific SQL commands

GreenSQL uses a pattern matching engine to find commands that are considered “illegal“. For example GreenSQL blocks database administrative commands. The GreenSQL administrator can decide which commands to block using the GreenSQL web management console. The administrator can also approve a blocked query by adding them to a whitelist.
Risk Calculation

For each query GreenSQL calculates its risk. After the risk is calculated GreenSQL can block the query or just create a warning message. There are a number of factors that are taken into account when calculating risk. For example:

* Access to sensitive tables increases risk query (users, accounts, credit information)
* Comments inside SQL commands increases query risk
* Usage of an empty password string
* Found ‘or’ token inside query
* Found SQL expression that always return true
* Comparison of constant values

How commands are blocked?

When GreenSQL determines that a query should be blocked it will generate an empty result set to send back to the front end so that the application can continue gracefully.
How Whitelist works?

Each time GreenSQL considers a SQL query as a security risk – it is blocked. You can alter this behavior for a specific query by explicitly allowing its pattern.
What is a GreenSQL Listener?

A GreenSQL Listener object is the heart of the GreenSQL Databae Firewall. A Listener is a proxy object used to connect queries from the frontend to a specific backend server. Before passing the query to the backend it is checked by the Listener to determine if it is malicious and if so how it should be handled (block, alert, pass).

Thank you,
Arun Bagul

Introduction – The SQL so far is part of the standard that all SQL implementations. Now let’s about the most useful, but most difficult areas of SQL – built-in SQL functions.

Types of functions –

Like most computer languages, SQL includes the capability to perform functions on data. However, each database vendor has implemented their own set of functions. Not only does each vendor choose which functions they will support, different vendors implement the same functionality using different syntax! This basically means that as soon as you open the door to functions, your database code stops being portable. For traditional database applications, that is less of an issue, but for web-based applications using tools like PHP, it means that the SQL functions you embedded in your web application will break when you move from MySQL to Oracle or to SQL-Server. To make matters worse, SQL functions often provide significant performance gains for all sorts of database manipulation, particularly in the case of web applications.

So what’s a we should to do? My advice is to use functions anywhere you can produce a measurable performance gain and document thoroughly what database the SQL was intended to work with. As we’ll talk about in a future column, moving any function-oriented code to a database stored procedure and essentially “black-boxing” it is an excellent approach — this lets the DBA rewrite the stored procedures for optimal performance — but some databases do not support stored procedures, so this is not a perfect solution. There are a number of functions that are available in a wide range of databases, so you can usually get the functionality you need as long as the functions are not too exotic.

There are four basic types of functions –

1) Numeric functions – for statistical, trigonometric, and other mathematical operations,
2) Text functions – for formatting and manipulating text values,
3) Time/date functions – used to parse date values as well as manipulate the date itself, and
4) System functions – for returning database-specific information (such as a username).

How do you know what functions your database supports? Unfortunately, the only reliable way is to check the documentation. As a general rule of thumb, the more a database costs (or the more widely it is used for open source databases), the more functions it supports. Functions open up a huge range of additional functionality for SQL developers, but at the cost of portability between database platforms. However, functions provide improved functionality and performance for data-intensive applications.

How to use functions?

Functions in SQL are typically used in WHERE clauses, though they generally can be used anywhere you’d use a field name or value. One of the easiest ways to learn functions is to see a few in action. We’ll start with a SELECT statement to find names longer than 12 characters…

Step(1) Create Table – student –

mysql> use SQL_function;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE student ( name varchar(40) );

Query OK, 0 rows affected (0.00 sec)
mysql>

mysql> show tables;
+————————+
| Tables_in_SQL_function |
+————————+
| student |
+————————+
1 row in set (0.00 sec)

mysql> desc student;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| name | varchar(40) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
1 row in set (0.00 sec)

Step(2) INSERT Few records in student table –

mysql> INSERT INTO student VALUES(‘Arun Bagul’);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO student VALUES(‘Sri madhanvan’);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO student VALUES(‘Hari madhanvan’);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT name FROM student;
+—————-+
| name |
+—————-+
| Arun Bagul |
| Sri madhanvan |
| Hari madhanvan |
+—————-+
3 rows in set (0.00 sec)

mysql>

Step(3) Use functions in WHERE clause –

mysql> SELECT name FROM student WHERE length(name) > 12 ;
+—————-+
| name |
+—————-+
| Sri madhanvan |
| Hari madhanvan |
+—————-+
2 rows in set (0.00 sec)

mysql>

Thank you,
Arun Bagul