[MCOL-1118] Create Events on Columnstore Created: 2017-12-17  Updated: 2017-12-22  Resolved: 2017-12-22

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.0.7
Fix Version/s: Icebox

Type: Bug Priority: Minor
Reporter: BOBY PETER Assignee: David Thompson (Inactive)
Resolution: Done Votes: 0
Labels: None
Environment:

Mariadb Columnstore 1.0.7,
Red Hat 4.8.5-11 (GCC),
/usr/local/mariadb/columnstore/mysql/bin/mysql Ver 15.1 Distrib 10.1.21-MariaDB

Attachments: Text File Mariadb_EventTest.sql    

 Description   

Mariadb Columnstore supports creation of a simple event with just inserting current date, hour and second to a columnstore table. However, if I try to insert future dates as shown below, it doesn't insert.

  1. event_scheduler is ON

create table eventtest
(
Id int,
dates date,
hours int,
minutes int
)engine=columnstore;

create event testing
on schedule every 1 minute
do
insert into eventtest(id,dates,hours,minutes)
select 1 as id,t.dates,t.hours,t.minutes
from
(
select current_date as dates,10 as hours, 0 as minutes union
select adddate(current_date,1) as dates,10 as hours, 0 as minutes union
select adddate(current_date,2) as dates,10 as hours, 0 as minutes union
select adddate(current_date,3) as dates,10 as hours, 0 as minutes union
select adddate(current_date,4) as dates,10 as hours, 0 as minutes union
select adddate(current_date,5) as dates,10 as hours, 0 as minutes union
select adddate(current_date,6) as dates,10 as hours, 0 as minutes union
select adddate(current_date,7) as dates,10 as hours, 0 as minutes union
select adddate(current_date,8) as dates,10 as hours, 0 as minutes
) t left join
dataset.eventtest et on cast(t.dates as date) = cast(et.dates as date) and t.hours = et.hours
where et.dates is null
;

 Comments   
Comment by David Thompson (Inactive) [ 2017-12-19 ]

I tried running this and it creates future dates for me:
select * from eventtest;
----------------------------+

Id dates hours minutes

----------------------------+

1 2017-12-20 10 0
1 2017-12-18 10 0
1 2017-12-19 10 0
1 2017-12-21 10 0
1 2017-12-24 10 0
1 2017-12-25 10 0
1 2017-12-22 10 0
1 2017-12-26 10 0
1 2017-12-23 10 0

----------------------------+

I don't know if you are asking if it should populate on future invocations every minute? As the insert stands it won't and will only trigger new entries after 10am since you have hour hardcoded to 10 and don't join on minutes.

Comment by BOBY PETER [ 2017-12-19 ]

I don't know if you are asking if it should populate on future invocations every minute?
--- No. This is actually required to run every month/week. For testing purpose I put "minute".

As the insert stands it won't and will only trigger new entries after 10am since you have hour hardcoded to 10 and don't join on minutes.
— My eventtest table is empty even though the SELECT statement is returning correct results (join is correct). Since I'm running it every minute and not specifically any hour, it should at least populate 9 records; but it is empty.

If I alter my EVENT as shown below, it populates the table every minute. This is good...
_alter event testing
on schedule every 1 minute
do
insert into eventtest(id,dates,hours,minutes)
select 1 as id,current_date,hour(current_timestamp),minute(current_timestamp);_

----------------------------+

Id dates hours minutes

----------------------------+

1 2017-12-19 12 10
1 2017-12-19 12 9

----------------------------+
2 rows in set (0.14 sec)

BUT

If I alter the same event as the one I mentioned very first OR the one below, the table no longer gets populated.

_alter event testing
on schedule every 1 minute
do
insert into eventtest(id,dates)
select 1 as id,adddate(max(dates),1) as dates from eventtest;_

Additional issue found
=================
event_scheduler was set to ON. Next day something turned if off. I set it ON again. – Not sure why this happened.

----------------------+

Variable_name Value

----------------------+

event_scheduler ON

----------------------+

Please find the attached.....
Mariadb_EventTest.sql

Comment by David Thompson (Inactive) [ 2017-12-21 ]

Sorry, i completely missed you were meaning using events. Yes i can see this. Currently as you may or may not know we have a fork of the mariadb server and we have some modifications to the parser to layer in columnstore execution. My guess (which needs to be confirmed) is that the event scheduler is a feature / path that is not currently supported. The 2nd case is not referencing any columnstore tables which is probably why it works.

This might be an easy fix or it might not we'll need to analyze but i'll need to consider this as more of an improvement for now.

If you are using this to maintain a dimension / lookup table could you make it an innodb table and use cross engine join?

Comment by BOBY PETER [ 2017-12-22 ]

Yes. Cross engine is a good option. However, I tried a latest version, 1.1.2, which worked fine with events.

BTW
1.0.7 - Event_Scheduler is turned-off by itself.

Thanks for the replies David. New version works just fine.
Please close this bug.

Comment by David Thompson (Inactive) [ 2017-12-22 ]

Ok great - thanks!

Generated at Thu Feb 08 02:26:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.