Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to understand the EVENT event of MySQL". In the daily operation, I believe that many people have doubts about how to understand the EVENT event of MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt of "how to understand the EVENT event of MySQL"! Next, please follow the editor to study!

MySQL EVENT (event)

The MySQL event is a task that runs according to a schedule. These events can be called "scheduling events". When an event is created, it is created as a named database object that contains SQL statements (or stored procedures) that are executed at a specific time or repeated at regular intervals. This is conceptually similar to the concept of Linux/UNIX crontab (also known as "cron job") or Windows task scheduler. The syntax and related statements are as follows:

CREATE EVENT event_name ON SCHEDULE schedule DO sql_statement

Event statement:

-SET GLOBAL event_scheduler = {ON | OFF}

-CREATE EVENT

-ALTER EVENT

-DROP EVENT

Event_name: events are schema objects, like tables, stored procedures, and triggers. The event_name must be a valid identifier and can be referenced and / or qualified by the schema name in the normal way. The event name must be unique in the schema.

Schedule: a schedule is a rule that specifies when the MySQL performs the action associated with the event.

Sql_statement: you must include valid SQL statements or stored procedure statements that are executed according to the schedule. This statement is subject to the same restrictions that apply to stored functions and dynamic SQL. For example, this statement cannot return a result set, such as a result set generated by SELECT or SHOW. Procedures are usually called using CALL statements to perform the actual operation.

1.1. Scheduling table

A schedule is a rule that specifies when an action should be performed. Scheduling tables can be specified in the SCHEDULE clause of the CREATE EVENT and ALTER EVENT statements.

There are two types of scheduling tables: one that executes once (using the AT keyword) and one that can be repeated (using the EVERY keyword). For the latter, you must define the frequency of repetition of the event. You can also define a time period to determine the duration of the event that should be repeated.

The syntax of the SCHEDULE clause is as follows:

AT timestamp [+ INTERVAL interval] | EVERY interval [STARTS timestamp [+ INTERVAL interval]] [ENDS timestamp [+ INTERVAL interval]]

The SCHEDULE clause can contain the following variable elements:

An expression of type 0 timestamp:DATETIME or TIMESTAMP

Angular interval: used to specify the duration. The duration is expressed by specifying a number of integers, followed by keywords that define a particular type of duration. Valid keywords include:

-YEAR

-QUARTER

-MONTH

-DAY

-HOUR

-MINUTE

-WEEK

-SECOND

-YEAR_MONTH

-DAY_HOUR

-DAY_MINUTE

-DAY_SECOND

-HOUR_MINUTE

-HOUR_SECOND

-MINUTE_SECOND

The event scheduler is a separate thread in the mysqld process that is responsible for executing scheduled events. The scheduler checks whether the event should be executed; if it should, a new connection is created to perform the operation.

Use events to automatically perform (maintain) tasks on a regular basis, such as updating summary tables or refreshing tables in queries (materialized view emulation), or performing night jobs. For example, handle daytime work, load a data warehouse, or export data to a file.

1.2. Event scheduler and permissions

1) event scheduler

After an event is created, it is stored in the database for execution according to the schedule. The event_scheduler thread monitors the schedule of all events, and when the time in the scheduling table is reached, the thread starts a new thread to execute each event.

By default, the event_scheduler thread is set to OFF. You must explicitly enable the thread by modifying the value of the global event_scheduler server variable and setting it to ON. You can also do this by adding server variables to the options file (so that the changes take effect at startup) or by using SET syntax dynamically. If you start the server with event_scheduler set to DISABLED, you cannot enable it through the SET statement while MySQL is running. Instead, you must stop MySQL and restart MySQL with this option enabled.

When the event_scheduler thread is enabled, you can see it in the output of SHOW PROCESSLIST (and the INFORMATION_SCHEMA equivalent PROCESSLIST).

You can use ALTER EVENT syntax to change EVENT. Each element of the EVENT can be changed through this syntax. Therefore, the syntax model of ALTER EVENT is almost exactly the same as that of CREATE EVENT statements.

2) event scheduling permissions

You must have SUPER permission to set the global event_scheduler variable. You must have EVENT permission to create, modify, or delete events.

Use GRANT to assign permissions (at the mode level only)

Mysql > GRANT EVENT ON myschema.* TO user1@srv1

Mysql > GRANT EVENT ON *. * TO user1@srv1

Use REVOKE to cancel event permissions

REVOKE EVENT ON myschema.* FROM user1@srv1

To revoke EVENT permissions, use the REVOKE statement. Revoking EVENT permissions from a user account does not delete or disable any events that have been created by that account.

The user's EVENT permissions are stored in the Event_priv column of the mysql.user and mysql.db tables. In both cases, the column stores one of the values "Y" or "N". "N" is the default. The mysql.user.Event_priv value for a given user is set to "Y" only if that user has global EVENT permissions. For mode-level EVENT permissions, GRANT creates a row in mysql.db and sets the column values of the row as follows:

L Db: name of the pattern

L User: name of the user

L Event_priv: "Y"

You do not have to manipulate these tables directly because the GRANT EVENT and REVOKE EVENT statements perform the required operations on them.

1.3. Event execution permission

The event is executed with the permissions of the event definer. If the definer does not have permission to perform a task, the event cannot perform the task. Examples are as follows:

User1@srv1 can only create SELECT events for myschema:

CREATE EVENT e_store_ts ON SCHEDULE EVERY 10 SECOND

DO

INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP ())

Due to the lack of INSERT permission, this event does not create any rows in the table. It is important to understand that the event executes with the permissions of its definer and cannot perform any actions that the definer does not have permission to perform. For example, suppose user1@srv1 has EVENT permissions on myschema. Again, assume that the user has SELECT permissions on myschema, but no other permissions for this mode. Although user1@srv1 can create a new event, the event itself cannot perform an INSERT operation because the definer user1@srv1 does not have permission to perform the operation.

Error log

If you look at the MySQL error log (hostname.err), you can see that the event is executing, but the operation that the event attempted to perform failed (indicated by RetCode=0):

...

060209 22:39:44 [Note] EVEX EXECUTING event newdb.e [EXPR:10]

060209 22:39:44 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0

060209 22:39:54 [Note] EVEX EXECUTING event newdb.e [EXPR:10]

060209 22:39:54 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0

060209 22:40:04 [Note] EVEX EXECUTING event newdb.e [EXPR:10]

060209 22:40:04 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0

...

1.4. Check event

SHOW CREATE EVENT event_name this statement shows the CREATE EVENT statement required to recreate a given event. You must provide an event name to view information about the event.

SHOW EVENTS this statement is an MySQL extension. It returns the characteristics of the event, such as the database, name, type, creator, and date of creation and modification. This statement has the advantage of displaying specific events based on the conditions provided in the LIKE schema or the WHERE clause. If no such condition is provided, the SHOW EVENTS statement displays information about all events.

INFORMATION_SCHEMA.EVENTS

EVENT_CATALOG: NULL

EVENT_SCHEMA: myschema

EVENT_NAME: e_store_ts

DEFINER: user1@srv1

EVENT_BODY: SQL

EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES

(UNIX_TIMESTAMP ())

...

1.5. Delete event

You can explicitly delete an event using the following syntax:

DROP EVENT [IF EXISTS] [schema_name.] event_name

Use IF EXISTS to prevent errors that occur when trying to delete events that do not exist. You must have EVENT permission on the database that contains the event you want to delete.

At this point, the study on "how to understand the EVENT event of MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.