南大通用GBase 8a数据库定时任务调度功能event测试

发表于2020年8月15日2022年3月30日作者 laozizhu

GBase 8a数据库内部支持定时任务event功能，可以实现内部任务调度。但一般建议还是外部调度，比如通过crontab 或者客户自己的Timer功能等，数据库还是聚焦在数据存储和查询上。

注意：当前版本并没有对大规模event做支持，一般用于内部清理垃圾，做审计日志归档用，所有的event加起来，不要超过10个，因为内部的event线程数好像是10个。超过了就不会有调度了。所以最好多用一次性的，执行完了，再创建下一个。

因为是集群，GBase 8a的定时任务event支持两种模式，本地Local和全局global（默认)。

目录导航

完整语法

创建

CREATE [DEFINER = { user | CURRENT_USER }] EVENT
[IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[global|local]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ... | EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity { YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |WEEK |
SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE
| HOUR_SECOND | MINUTE_SECOND }

修改

修改定时任务的调度参数

gbase> alter event import_audit_log ON SCHEDULE every 10 minute;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)

修改定时任务名字

gbase> alter event import_audit_log rename to import_audit_log2;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

删除

drop event，就不做介绍了。

gbase> drop event drop_temp_table;
Query OK, 0 rows affected (Elapsed: 00:00:00.08)

创建任务表，用于记录测试结果

gbase> create table tevent(d datetime);
Query OK, 0 rows affected (Elapsed: 00:00:00.61)

创建最简单的一次性event

该event只运行一次，且马上运行。

gbase> create event test_event1 on schedule at current_timestamp do insert into tevent values(now());
Query OK, 0 rows affected (Elapsed: 00:00:00.10)

查看运行结果

gbase> select * from tevent;
+---------------------+
| d |
+---------------------+
| 2020-08-15 19:55:53 |
+---------------------+
1 row in set (Elapsed: 00:00:00.02)

立即运行的event可能不会运行

因为创建时间和调度时间的偏差，如上的event,有较大几率出现刚创建完，还没运行，就发现过期了，直接就被删除了，导致event根本没有被执行。请在开始运行时间做精确指定，或者延后一段时间，请参考后面的例子。

gbase> create event test_event2 on schedule at current_timestamp do insert into tevent values(now());
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.10)

gbase> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                        |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1588 | 10.0.2.202:5258 - Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

创建延迟执行的event

通过在current_timestamp增加一部分时间，来延迟执行。如有固定时间，请参考后面的定时执行。

gbase> create event test_event2 on schedule at current_timestamp+interval 10 second do insert into tevent values(now());
Query OK, 0 rows affected (Elapsed: 00:00:00.46)

gbase> select * from tevent;
+---------------------+
| d                   |
+---------------------+
| 2021-10-23 15:35:54 |
| 2021-10-23 15:47:47 |
| 2021-10-23 15:48:50 |
+---------------------+
3 rows in set (Elapsed: 00:00:00.03)

gbase> select * from tevent;
+---------------------+
| d                   |
+---------------------+
| 2021-10-23 15:35:54 |
| 2021-10-23 15:47:47 |
| 2021-10-23 15:48:50 |
| 2021-10-23 15:53:15 |
+---------------------+
4 rows in set (Elapsed: 00:00:00.03)

创建定点运行的event

如下event,使用了绝对时间'2021-10-23 15:56:13'来执行event

gbase> create event test_event2 on schedule at '2021-10-23 15:56:13'  do insert into tevent values(now());
Query OK, 0 rows affected (Elapsed: 00:00:00.10)

gbase> select * from tevent;
+---------------------+
| d                   |
+---------------------+
| 2021-10-23 15:35:54 |
| 2021-10-23 15:47:47 |
| 2021-10-23 15:48:50 |
| 2021-10-23 15:53:15 |
+---------------------+
4 rows in set (Elapsed: 00:00:00.03)

gbase> select now(),t.* from tevent t;
+---------------------+---------------------+
| now()               | d                   |
+---------------------+---------------------+
| 2021-10-23 15:56:25 | 2021-10-23 15:35:54 |
| 2021-10-23 15:56:25 | 2021-10-23 15:47:47 |
| 2021-10-23 15:56:25 | 2021-10-23 15:48:50 |
| 2021-10-23 15:56:25 | 2021-10-23 15:53:15 |
| 2021-10-23 15:56:25 | 2021-10-23 15:56:13 |
+---------------------+---------------------+
5 rows in set (Elapsed: 00:00:00.03)

查看event是否还存在

注意：如果event运行中间报错，则event不会被自动删除。

gbase> show events;
Empty set (Elapsed: 00:00:00.00)

阶段性结论

计划任务执行结果和预期相符。

如果想保留这种一次性的event们可以通过 ON COMPLETION [NOT] PRESERVE指示。否则event在执行完成后，会【自动删除】

gbase> create event test_event1 on schedule at current_timestamp on completion preserve do insert into tevent values(now());
Query OK, 0 rows affected (Elapsed: 00:00:00.14)

gbase> select * from tevent;
+---------------------+
| d                   |
+---------------------+
| 2020-08-15 19:55:53 |
| 2020-08-15 20:02:17 |
+---------------------+
2 rows in set (Elapsed: 00:00:00.04)

gbase> show events;
+--------------+------------------------------------------------------------------+------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+--------------+----------------------+----------------------+--------------------+
| Vc           | Db                                                               | Name                                                             | Definer                                                                       | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status   | Execute mode | character_set_client | collation_connection | Database Collation |
+--------------+------------------------------------------------------------------+------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+--------------+----------------------+----------------------+--------------------+
| vcname000001 | testdb                                                           | test_event1                                                      | root@%                                                                        | +08:00    | ONE TIME | 2020-08-15 20:02:17 | NULL           | NULL           | NULL   | NULL | DISABLED |            1 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+--------------+------------------------------------------------------------------+------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+--------------+----------------------+----------------------+--------------------+
1 row in set (Elapsed: 00:00:00.00)

可以看到event被保留下来了，当然也意味着，如果你下次使用相同名字的event,你需要自行清理。

创建周期性定时任务

gbase> create event test_event_loop on schedule every 2 second ends current_timestamp+interval 10 second  do insert into tevent values(now());
Query OK, 0 rows affected (Elapsed: 00:00:00.33)

设置了每2秒一次，为了避免死循环，我们设置了10秒后结束，且自动删除event。

执行结果


gbase> select * from tevent;
+---------------------+
| d                   |
+---------------------+
| 2020-08-15 19:55:53 |
| 2020-08-15 20:02:17 |
| 2020-08-15 20:11:12 |
| 2020-08-15 20:11:14 |
| 2020-08-15 20:11:16 |
| 2020-08-15 20:11:18 |
| 2020-08-15 20:11:20 |
| 2020-08-15 20:11:22 |
+---------------------+
8 rows in set (Elapsed: 00:00:00.02)

其它说明

global 是默认的，代表在整个集群，有多个管理节点时，只会执行一次。而local表示每个节点各自执行，会出现并发。

参考

GBase 8a集群暂停定时任务event disable

Post Views: 999

2024年11月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30