Error modifying Schedule configuration

[jesus oliver]

HI, 

We are trying to delete subschedules in Schedule configuration but receive the following error message: 

COMMAND

begin multicommand;

drop scheduled event ROOT.'S3215172'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S01_INTERVECION'.'S3215172_INV005_00_FICHEROS_TRATAMIENTOS';

drop schedule ROOT.'S3215172'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S01_INTERVECION';

drop scheduled event ROOT.'S3215172'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S02_DEFAULT'.'S3215172_INV005_00_FICHEROS_TRATAMIENTOS';

drop schedule ROOT.'S3215172'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S02_DEFAULT';

drop schedule ROOT.'S3215172'.'INV005_00_FICHEROS_TRATAMIENTOS';

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_FIX';

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_02_03_REP';

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_02_04_ROD';

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_1_MASK';

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_2_MASK';

drop event 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS';

create or alter schedule ROOT.S0 (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with group = PUBLIC;

create or alter event 'S0_INV005_00_FICHEROS_TRATAMIENTOS' (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with action = submit SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS', group = 'ADMIN';

create or alter schedule ROOT.'S0'.'INV005_00_FICHEROS_TRATAMIENTOS' (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with group = 'ADMIN', inactive, time zone = 'Europe/Berlin';

create or alter interval 'S0_INV005_00_FICHEROS_TRATAMIENTOS_01_FIX' (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with group = 'ADMIN', starttime = '2019-09-13T18:00', endtime = '2019-09-14T11:55';

create or alter schedule ROOT.'S0'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S01_INTERVECION' (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with interval = 'S0_INV005_00_FICHEROS_TRATAMIENTOS_01_FIX', group = 'ADMIN', time zone = 'Europe/Berlin';

create or alter scheduled event ROOT.'S0'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S01_INTERVECION'.'S0_INV005_00_FICHEROS_TRATAMIENTOS' (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with active, group = 'ADMIN', backlog handling = NONE, suspend limit = default, calendar = INACTIVE, horizon = NONE;

create or alter interval 'S0_INV005_00_FICHEROS_TRATAMIENTOS_01_1_MASK' (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with group = 'ADMIN', starttime = NONE, endtime = '2019-09-13T18:00';

create or alter interval 'S0_INV005_00_FICHEROS_TRATAMIENTOS_01_2_MASK' (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with group = 'ADMIN', starttime = '2019-09-14T11:55', endtime = NONE;

create or alter interval 'S0_INV005_00_FICHEROS_TRATAMIENTOS_02_04_ROD' (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with selection = ('T08:00'-'T18:00'), base = NONE, duration = NONE, noinverse, filter = ('S0_INV005_00_FICHEROS_TRATAMIENTOS_01_1_MASK','S0_INV005_00_FICHEROS_TRATAMIENTOS_01_2_MASK'), group = 'ADMIN';

create or alter interval 'S0_INV005_00_FICHEROS_TRATAMIENTOS_02_03_REP' (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with starttime = NONE, endtime = NONE, base = 60 minutes, duration = 60 minutes, noinverse,synctime = '2021-03-03T11:00', filter = ('S0_INV005_00_FICHEROS_TRATAMIENTOS_02_04_ROD'), group = 'ADMIN';

create or alter schedule ROOT.'S0'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S02_DEFAULT' (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with interval = 'S0_INV005_00_FICHEROS_TRATAMIENTOS_02_03_REP', group = 'ADMIN', time zone = 'Europe/Berlin';

create or alter scheduled event ROOT.'S0'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S02_DEFAULT'.'S0_INV005_00_FICHEROS_TRATAMIENTOS' (SYSTEM.'PRE_MOMO'.'INV005_00_FICHEROS_TRATAMIENTOS') with active, group = 'ADMIN', backlog handling = NONE, suspend limit = default, calendar = INACTIVE, horizon = NONE;

end multicommand;

ERRORCODE

04209112049

ERRORMESSAGE

Error in Statement 10 (DropInterval) : Interval in use by Interval(s)

We can't modify the subchedules configuration neither. Maybe something bad in data base?

Any ideas?

Best regards,

Jesus

[Ronald Jeninga]

Hi Jesus,

not long ago I've discovered this bug myself, and it turns out that there was a problem with the cascading delete when dropping some of the objects.

I've fixed this, but I'm not sure I've already created the rpms. So I guess I didn't.

This week I won't have time to create new rpms. Hence if you need rpms to update your system, you'll have to be patient and wait until next week.

But this doesn't resolve your current problem.

Fortunately, with a bit of effort, you should be able to recover from this situation.

We'll have to find out which objects are still hanging around, so we'll have to write back and forth a little. I hope that's OK with you.

First of all, we'll have to find out which Interval exactly is causing the problems.

Throwing away the objects of the old schedule is what the GUI would do anyway, so we can just as well do it ourselves.

If you are working with release 2.8 or older, you'll have to use sdmsh, else you can use the shell from within the GUI.

The idea is that you repeat all the drop statements one by one (starting at the top with the Scheduled Event) and see if the server complains at you, or not.

If not, fine, but if it complains, we've found the culprit.

The next action is then to find out the ID of the offending Interval.

Let me assume, the problematic Interval is called  S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_2_MASK.

You then enter the following select statement in the shell:

select * from sci_intervall where name = 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_2_MASK';

That'll give you a bunch of values, most importantly the ID.

Now we'll have to find out what references the Interval (and shouldn't exist any more).

There are only a few candidates. It could be either the parent_id or child_id in the interval_hierarchy, or the int_id in the schedule.

So you'll have to find out which of these we're talking about:

select * from interval_hierarchy

where <ID> in (parent_id, child_id);

select * from schedule

where int_id = <ID>;

You'll have to replace <ID> with the ID of the Interval you've found, of course.

Now if you post me the non-empty results, I can think about how to proceed further.

(Note: we didn't actually break anything so far in the process, except that we've dropped some objects we wanted to drop anyway).

Best regards,

Ronald

[jesus oliver]

Hi Ronald,

Firstly, many thanks for your prompt response.

Here you are the output of the commands:

Executing drop commands:

drop scheduled event ROOT.'S3215172'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S01_INTERVECION'.'S3215172_INV005_00_FICHEROS_TRATAMIENTOS'

Scheduled Event dropped

drop schedule ROOT.'S3215172'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S01_INTERVECION'

Schedule dropped

drop scheduled event ROOT.'S3215172'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S02_DEFAULT'.'S3215172_INV005_00_FICHEROS_TRATAMIENTOS'

Scheduled Event dropped

drop schedule ROOT.'S3215172'.'INV005_00_FICHEROS_TRATAMIENTOS'.'S02_DEFAULT'

Schedule dropped

drop schedule ROOT.'S3215172'.'INV005_00_FICHEROS_TRATAMIENTOS'

Schedule dropped

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_FIX'

Interval dropped

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_02_03_REP'

Interval dropped

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_02_04_ROD'

Interval dropped

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_1_MASK'

Error 04209112049 executing command

Interval in use by Interval(s)

Searching for ID:

SELECT * FROM SCI_INTERVALL WHERE NAME = 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_1_MASK';

Selected Values

ID       NAME                                               OWNER_ID START_TIME END_TIME DELAY  BASE_INTERVAL BASE_INTERVAL_MULTIPLIER DURATION DURATION_MULTIPLIER SYNC_TIME IS_INVERSE IS_MERGE EMBEDDED_INT_ID SE_ID   OBJ_ID OBJ_TYPE CREATOR_U_ID CREATE_TS               CHANGER_U_ID CHANGE_TS

-------- -------------------------------------------------- -------- ---------- -------- ------ ------------- ------------------------ -------- ------------------- --------- ---------- -------- --------------- ------- ------ -------- ------------ ----------------------- ------------ -----------------------

80679965 S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_1_MASK 81 <null> <null> <null> <null> <null> <null> <null> <null> FALSE FALSE <null> 3215172 <null> <null> 0 2021-03-03 12:35:01.214 0 2021-03-03 12:35:01.214

1 Row(s) selected

Searching for candidate:

select * from INTERVAL_HIERARCHY where 80679965 in (parent_id, child_id);

Selected Values

ID       CHILD_ID PARENT_ID CREATOR_U_ID CREATE_TS     CHANGER_U_ID CHANGE_TS

-------- -------- --------- ------------ ------------- ------------ -------------

80679970 80679965 80679967 0 1614767701214 0 1614767701214

1 Row(s) selected

select * from SCHEDULE where int_id = 80679965;

Selected Values

ID NAME OWNER_ID INT_ID PARENT_ID TIME_ZONE SE_ID ACTIVE CREATOR_U_ID CREATE_TS CHANGER_U_ID CHANGE_TS INHERIT_PRIVS

-- ---- -------- ------ --------- --------- ----- ------ ------------ --------- ------------ --------- -------------

0 Row(s) selected

As a result of executing these commands, we've realized that the shedule has dropped completely as we wanted anyway.

We are running 2.9 version in ubuntu 18.04 OS. I don't know whether we need a rpms in our case, or just a jar file could solve it.

We'll wait for further instructions.

Best Regards,

Jesus

[Ronald Jeninga]

Hi Jesus,

I'll answer to your reply from bottom to top.

Since you are running 2.9 on Ubuntu, you've compiled the software yourself, which I think is great.

Hence if you do a "git pull" and a make again, you'll have the latest checked in version of the 2.9 release and the bug should be fixed.

If you've discarded the sandbox, we'll find a solution "offline".

Dropping the Scheduled Events and the Schedules removed the schedule as was visible in the GUI.

Most of the supporting objects, the intervals have been removed as well.

One supporting object, an Event, is still present. This isn't a huge problem because those objects on themselves don't do anything.

As you can see from the list of statements that would alter the schedule (note that I write names of objects with a starting capital letter; so you create a schedule by creating Scheduled Events, an Event, a number of Schedules and a bunch of Intervals), it would create or alter the Event.

Hence if you just want to change your schedule, you can leave the Event in place, else you can drop it (DROP EVENT S3215172_INV005_00_FICHEROS_TRATAMIENTOS;).

What is left is a number of detached Intervals that reference each other.

This on itself is not dangerous  as they don't do anything themselves, but it's ugly.

And we'd like to remove those objects from the repository, ideally without breaking something.

As you can see from the result of the query, the offending interval still seems to have a parent.

The next step is to investigate the parent, it could be in use by something as well.

Most likely we won't find it, because that's exactly the bug that has been fixed now.

If you check the presence of the parent by selecting it

select * from sci_intervall where id = 80679967;

you'll probably find nothing.

[Ronald Jeninga]

Hi, it's me again. I clicked the wrong button. Sorry for that.

Anyway, if the parent isn't found, you have two options:

1. update the system with a "git pull" and a "make" (and maybe a copy of the resulting BICsuite.jar) and then drop the problematic Interval again.

   The bug fix recognizes the broken Interval_Hierarchy object and removes it.

2. Shut down the server, remove the row from the interval_hierarchy table, start the server again and drop the problematic Interval.

Needless to say that option 1 is the preferred one. But if you are victim of a very bureaucratic and slow release management, option 2 is a kind of workaround.

Having said this, it might be a good idea to check for other dangling Interval_Hierarchy objects too.

Just look for Interval_Hierarchies where the parent doesn't exist.

select *

from interval_hierarchy ih

where not exists (

    select 1

    from intervall

    where id = ih.parent_id

);

I hope this helps. If you feel unsure, please ask before doing things.

Best regards,

Ronald

[jesus oliver]

Hi Ronald,

We have some doubts about the next step to be taken:

In summary, we have executed all drop sentences but the last three in the list: 

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_1_MASK'; (here is where  the error appeared)

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_2_MASK';

drop event 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS';

Now, if we execute the query looking for the parent, we still found it. Here is the output:

select * from SCI_INTERVALL where id = 80679967;

Selected Values

ID       NAME                                               OWNER_ID START_TIME END_TIME DELAY  BASE_INTERVAL BASE_INTERVAL_MULTIPLIER DURATION DURATION_MULTIPLIER SYNC_TIME IS_INVERSE IS_MERGE EMBEDDED_INT_ID SE_ID   OBJ_ID OBJ_TYPE CREATOR_U_ID CREATE_TS               CHANGER_U_ID CHANGE_TS

-------- -------------------------------------------------- -------- ---------- -------- ------ ------------- ------------------------ -------- ------------------- --------- ---------- -------- --------------- ------- ------ -------- ------------ ----------------------- ------------ -----------------------

80679967 S3215172_INV005_00_FICHEROS_TRATAMIENTOS_02_04_DOW 81 <null> <null> <null> WEEK 1 DAY 1 <null> FALSE FALSE <null> 3215172 <null> <null> 0 2021-03-03 12:35:01.214 0 2021-03-03 12:35:01.214

1 Row(s) selected

We've executed a query to search for rows related to S3215172, just in case this could clear out something:

select * from SCI_INTERVALL where name like '%S3215172_%';

Selected Values

ID       NAME                                               OWNER_ID START_TIME END_TIME DELAY  BASE_INTERVAL BASE_INTERVAL_MULTIPLIER DURATION DURATION_MULTIPLIER SYNC_TIME IS_INVERSE IS_MERGE EMBEDDED_INT_ID SE_ID   OBJ_ID OBJ_TYPE CREATOR_U_ID CREATE_TS               CHANGER_U_ID CHANGE_TS

-------- -------------------------------------------------- -------- ---------- -------- ------ ------------- ------------------------ -------- ------------------- --------- ---------- -------- --------------- ------- ------ -------- ------------ ----------------------- ------------ -----------------------

80679965 S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_1_MASK 81 <null> <null> <null> <null> <null> <null> <null> <null> FALSE FALSE <null> 3215172 <null> <null> 0 2021-03-03 12:35:01.214 0 2021-03-03 12:35:01.214

80679966 S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_2_MASK 81 <null> <null> <null> <null> <null> <null> <null> <null> FALSE FALSE <null> 3215172 <null> <null> 0 2021-03-03 12:35:01.214 0 2021-03-03 12:35:01.214

80679967 S3215172_INV005_00_FICHEROS_TRATAMIENTOS_02_04_DOW 81 <null> <null> <null> WEEK 1 DAY 1 <null> FALSE FALSE <null> 3215172 <null> <null> 0 2021-03-03 12:35:01.214 0 2021-03-03 12:35:01.214

3 Row(s) selected

And here is the output, searching for aditional dangling intervals:

select * from INTERVAL_HIERARCHY ih where not exists (select 1 from INTERVALL where id = ih.parent_id);select * from INTERVAL_HIERARCHY ih where not exists (select 1 from INTERVALL where id = ih.parent_id);

Selected Values

ID       CHILD_ID PARENT_ID CREATOR_U_ID CREATE_TS     CHANGER_U_ID CHANGE_TS

-------- -------- --------- ------------ ------------- ------------ -------------

74871181 74871179 74871177 0 1608034461732 0 1608034461732

80756958 80756955 80756953 0 1614859754322 0 1614859754322

80757371 80757368 80757366 0 1614860251091 0 1614860251091

It is not clear to us if we have to execute the "3 remaining drops", and whether the presence of the parent is a problem to create a new schedule. 

Anyway, we had understood that there was no need to shutdown or update the server (maybe wrongly), even though some orphan events could remain in data base.

Updating the server isn't in our mind now, so we opt for the data base cleaning as much as posible (if posible, without restarting the server).

Let us know, please, the next step in order to be able to create a new schedule.

Best Regards,

Jesus

[Ronald Jeninga]

Hi Jesus,

first of all, if you manage to remove objects from the repository with schedulix commands (DROP ...), there's no need to restart the server.

But IF you choose to use SQL, this must be done while the server is down.

And beware, the use of SQL to change things in the repository is VERY DANGEROUS. 

In case you find a dangling Interval_Hierarchy (as you did), the risk of using SQL is halfway acceptable, but if you have the time to wait until you can patch the server (i.e. replace the BICsuite.jar with the newest 2.9 version), that's definitely preferable.

Since the evaluation of intervals is always done from parent to child, there's no risk if some child states that it also has another parent. Hence the ugly superfluous objects don't really harm, until you try to remove the children, which then fails.

I'd try to remove as many of the now unused objects as possible.

At least try to execute

drop interval 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_2_MASK';

drop event 'S3215172_INV005_00_FICHEROS_TRATAMIENTOS';

(the third (i.e. the first from the list) is known to fail)

From the query output it is visible that there is a Day-Of-Week interval defined (S3215172_INV005_00_FICHEROS_TRATAMIENTOS_02_04_DOW) which actually is the parent of our problematic Interval.

Since you don't have a Schedule, an Event and a Scheduled_Event left, it is safe to remove all "connected" Intervals too.

I'd try to execute

drop interval S3215172_INV005_00_FICHEROS_TRATAMIENTOS_02_04_DOW:  /* should remove the parent */

drop interval S3215172_INV005_00_FICHEROS_TRATAMIENTOS_01_1_MASK;  /* might work now */

and see what happens.

If we're lucky both work and your system is clean again (except for the three Interval_Hierarchy objects you've found).

If you're unlucky, you'll have some unused Intervals in your system, which is ugly but not much worse.

In that case it'll be a good idea to  choose another name for your schedule (INV005_00_FICHEROS_TRATAMIENTOS2 or so), in order to prevent potential name clashes

----

A small word on Server restarts: they are nor dangerous at all. In fact you could kill the server with a SIGKILL signal, and the entire system would survive without any problem.

Hence the only downside of a server restart will be a small gap in the availability of the service.

The jobservers don't mind; they'll patiently "wait" until they can reconnect again, and then issue all the commands they wanted to issue in the meantime.

The server itself doesn't mind too. If some schedule would have wanted to launch a batch at the time the server happened to be down, it'll launch the batch shortly after server start.

The only thing that can be annoying is if your server requires a lot of time, say 15 minutes or more, to start up.

 The most common reason for such a behavior is "too much" data in your repository.

This is where the archive properties in the server.conf are for. If configured correctly they will keep your "live tables" small.

Archiving is another topic though, so I won't elaborate that here.

Sorry for the hassle!

Best regards,

Ronald

[Ronald Jeninga]

Hi Jesus,

I forgot to mention that it's safe to create a new schedule for your batch, but give it a (slightly) different name.

We'll still be able to clean up the problematic objects. They won't interfere with the new schedule.

Ronald

[jesus oliver]

Hi Ronald,

Thanks for your help.

All is working now perfectly, and without noise.

Best Regards,

Jesus