Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is the use of ON COMPLETION PRESERVE clause while creating the event?
As we know that an event is automatically dropped when it is expired and we would not be able to see it from SHOW EVENTS statement. To change such kind of behavior we can use ON COMPLETION PRESERVE while creating the event. It can be understood from the following example −
Example
mysql> Create table event_messages(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, MESSAGE VARCHAR(255) NOT NULL, Generated_at DATETIME NOT NULL); Query OK, 0 rows affected (0.61 sec)
The below query will create an event without the use of ON COMPLETION PRESERVE hence it would not be seen in the output of SHOW EVENTS FROM db_name query.
mysql> CREATE EVENT testing_event_without_Preserves ON SCHEDULE AT CURRENT_TIMESTAMP DO INSERT INTO event_messages(message,generated_at) Values('Without Preserve',NOW());
Query OK, 0 rows affected (0.00 sec)
mysql> Select * from event_messages;
+----+------------------+---------------------+
| ID | MESSAGE | Generated_at |
+----+------------------+---------------------+
| 1 | Without Preserve | 2017-11-22 20:32:13 |
+----+------------------+---------------------+
1 row in set (0.00 sec)
mysql> SHOW EVENTS FROM query\G
*************************** 1. row ***************************
Db: query
Name: testing_event5
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 2017-11-22 17:09:11
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: DISABLED
Originator: 0
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)The below query will create an event with the use of ON COMPLETION PRESERVE hence it would be seen in the output of SHOW EVENTS FROM db_name query.
mysql> CREATE EVENT testing_event_with_Preserves ON SCHEDULE AT CURRENT_TIMESTAMP ON COMPLETION PRESERVE DO INSERT INTO event_messages(message,generated_at) Values('With Preserve',NOW());
Query OK, 0 rows affected (0.00 sec)
mysql> Select * from event_messages;
+----+------------------+---------------------+
| ID | MESSAGE | Generated_at |
+----+------------------+---------------------+
| 1 | Without Preserve | 2017-11-22 20:32:13 |
| 2 | With Preserve | 2017-11-22 20:35:12 |
+----+------------------+---------------------+
2 rows in set (0.00 sec)
mysql> SHOW EVENTS FROM query\G
*************************** 1. row ***************************
Db: query
Name: testing_event5
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 2017-11-22 17:09:11
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: DISABLED
Originator: 0
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
Db: query
Name: testing_event_with_Preserves
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 2017-11-22 20:35:12
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: DISABLED
Originator: 0
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)
Updated on: 22-Jun-2020
2K+ Views
Advertisements