Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Friday, October 12, 2012

MYSQL Event Schedular

MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler.

Scheduled tasks of this type are also sometimes known as “temporal triggers”, implying that these are objects that are triggered by the passage of time. While this is essentially correct, we prefer to use the term events to avoid confusion with triggers of the type discussed in Section 19.3, “Using Triggers”. Events should more specifically not be confused with “temporary triggers”. Whereas a trigger is a database object whose statements are executed in response to a specific type of event that occurs on a given table, a (scheduled) event is an object whose statements are executed in response to the passage of a specified time interval.

The below queries will explain you, how to get select, create and drop a event with interval bases.

Create a Table

To enable a event setting

Create a event to delete a table for every 1 hour

We can also select the event list using below query

Thursday, May 31, 2012

Can't connect to local MySQL server through socket

In mysql we might get this problem while fresh installation of mysql.

"Can't connect to local MySQL server through socket"

The below line can help to over come a problem.

Step:1
sudo mkdir /var/run/mysqld/

Step:2
sudo ln -s /opt/lampp/var/mysql/mysql.sock /var/run/mysqld/mysqld.sock


I think this will help you overcome the problem of mysql sock layer.

Friday, May 11, 2012

Query to know particular table status in MySql

In Mysql, we can execute several queries. Here, i am going to demonstrate about important query. This query will show you the properties belongs to particular table.

Query :         

SHOW TABLE STATUS LIKE TableName

The above query will return following coulmns,
  1. Name
  2. Engine
  3. Version
  4. Row_format
  5. Rows
  6. Avg_row_length
  7. Data_length
  8. Max_data_length
  9. Index_length
  10. Data_free
  11. Auto_increment
  12. Create_time
  13. Update_time
  14. Check_time
  15. Collation
  16. Checksum
  17. Create_option
  18. Comment
To get next "Auto Increament" id of table is most of realtime usage of this query. Also, They can know about engine name of the table.