9.5.2 FP2 critical bug: mass data clearance across cubes

[harrytm1]

Hi all,

Wish to check if you have experienced the following bug which happened recently.

We have a budgeting model with HR, Capex, Profit and Loss and Balance Sheet cubes etc. The number of users accessing TM1 concurrently can be as high as 20 or more as they are using it for budget submission. The total user base is more than 50 contributors. It is currently on TM1 version 9.5.2 FP2.

The main methods of submission are through DBSend templates and data spreading in cube views.

It was reported that massive amount of data was missing across multiple cubes such as Headcount, New Capex, P&L. It spans not only to the budget version that they are using now, but also on actuals.

We looked t the transaction logs and found that data was deleted (both numeric and string) in several cubes at the exact same time by one User ID. This user belongs to an User Group that has access to the affected cubes and he was using DBSend templates to send capex data just seconds before that.

From the logs, his ID was writing data in the New Capex cube and then suddenly, at the same timestamp, it began to clear both numeric and string data in the same cube, as well as other cubes. Obviously, he couldn't have physically done so as there are more than 600 thousands of transactions under his ID at exact same time.

We still couldn't find the cause the triggered this mass data deletion event. In the end, we had to painstakingly use the "Backout" function in Transaction Query GUI to restore the deleted data batch by batch.

Has anyone experienced similar issue in 9.5.2 FP2 or even 10.1/10.2 versions? Any idea if this was fixed in later version, especially 10.2? I couldn't find this bug in the fix lists unless I missed it. I will push for an upgrade to 10.2 if it is confirmed that it is fixed in 10.2.

We are at wit's end, so any suggestion and advice will be greatly appreciated! Many thanks in advance!

harry

[tomok]

I highly doubt there is a bug in 9.5.2 FP2 that somehow magically clears data in cubes, without warning. If there was then we would have already heard about it. More likely this was something done by the user and they are just too embarrased to admit what they have done. All it takes is a right-click and then Data Spread, Clear, and Boom!, you're data is gone. I've seen it happen many times before. That's why I'm not a big fan of the Data Spreading functionality. I love it's functionality but fear it's power.

[David Usherwood]

Or just press C

[Alan Kirk]

The main methods of submission are through DBSend templates and data spreading in cube views.

{Snip...}

Obviously, he couldn't have physically done so as there are more than 600 thousands of transactions under his ID at exact same time.

Yes he could. You answered your own question above. Tomok and David are right, it's no great leap to guess that that's exactly what happened, and the symptoms that you describe match that scenario perfectly. The critical bug only existed between the chair and keyboard, though it'll probably always be prefaced with "But I didn't! I'm sure I didn't!".

[harrytm1]

Hi all,

Thanks for the reply.

In my earlier post, I stated the both numeric and string data were deleted at the same time across multiple cubes. I doubt we can clear string data through cube view spreading. Also, I doubt we can clear data across multiple cubes at the same time. The timestamps shown in the log are exactly the same.

The only possibility of it not being a bug is that the user uses a DBSend template that covers over a large set of data for multiple cubes and versions. However, this would mean that the DBS template has to contain more than 600 thousand of DBS cells as this is the amount of data being cleared.

[tomok]

Since 9.5.2 FP2 has been out around 4 years now I think if there was a big that was mysteriously wiping out data we would know about it. If you want to believe it's a bug then go right ahead. All IBM is going to tell you is you need to upgrade to a newer version.

BTW, choosing Data Spread, Clear, will wipe out all data in the cell highlighted, whether string or numeric, as well as all data below the chosen nodes so it is possible to wipe out an entire cube in one action. Data Spread also works in Excel, not just cube views so it is possible to really do some damage to your data when you use it in a worksheet. I don't know for sure but it might wipe out multiple cube data if you are referencing several cubes in the same sheet, under certain circumstances. In any case, Data Spread is very powerful AND dangerous. Use it with caution.

[harrytm1]

Thanks, tomok.

I'm hoping to check if anyone in this forum experienced similar issue or have any knowledge that IBM had stated in the fix list. I'm going through the list right now.

Also, I'm gathering more info from the user directly.

As to clearing string data in a cube view, I still have my doubts. A string measure cannot be added to a C-element in the dimension. Hence, it should not be possible to clear data through one consolidated cell point by typing "C" in the consolidated cell.

Unless the user selected a series of consolidated cells in a cube view, including string measures and select "Delete" through the menu that is opened by right-click. But this will mean the cube view must contain thousands of string cells at various level.

[tomok]

A string measure cannot be added to a C-element in the dimension.

Wherever did you get that idea? You can absolutely enter strings in C level cells if the measure is defined as string.

[Alan Kirk]

I'm hoping to check if anyone in this forum experienced similar issue or have any knowledge that IBM had stated in the fix list. I'm going through the list right now.

Also, I'm gathering more info from the user directly.

As to clearing string data in a cube view, I still have my doubts. A string measure cannot be added to a C-element in the dimension. Hence, it should not be possible to clear data through one consolidated cell point by typing "C" in the consolidated cell.

Unless the user selected a series of consolidated cells in a cube view, including string measures and select "Delete" through the menu that is opened by right-click. But this will mean the cube view must contain thousands of string cells at various level.

I'd concur that no method seems to allow adding a string element to a consolidation; not dimension editor, not .xdi, not TI. Also the Data Spread menu option does not seem to be active on an S level string cell (regardless of whether the other elements are consolidated or not).

However it may be unwise to jump to the conclusion that one single action alone resulted in all of the changes. The existence of the same timestamps doesn't necessarily imply causality. They may have done more than one thing that triggered the clearing of both.

Spreading to multiple cubes is a different matter; it can happen, though as Tomok suggested it does not always happen consistently. For example (and yes, I did test this) if you have a slice which consists of DBRWs for two different cubes and do a repeat spread from a zero base to some value across the formulas for both of the cubes, the spread will work. If you then repeat spread to make it a different number, it won't; the respread will stop at the barrier between the two cubes. The same is true of a clear right (or clear down; both were checked) BUT the fact that it works at all is a warning bell; the fact that I didn't find a magic formula to do multi-cube clearing (with a whole 5 minutes of testing) does not imply that there isn't one. (Especially as there was different dimensionality in the cubes that I tested. It may be different if that's not the case, or if only the measures dimension is different but I don't have time to test that now.)

If it isn't spreading (and I agree with Tomok, I can't believe that 9.5.2 has been out for as long as it has been without this showing up before if it really is a bug, unless you've maybe applied a hotfix that you haven't told us about) then there's one other thing that can run with similar speed. Which raises the question:
(a) Does the user have permission to run ANY TI processes; and
(b) What, exactly, do those TI processes do and does it involve clearing data?

[Alan Kirk]

A string measure cannot be added to a C-element in the dimension.

Wherever did you get that idea? You can absolutely enter strings in C level cells if the measure is defined as string.

You're both talking about different things.
- If the last dimension's element is an S type and one or more of the other elements are a C type, then yes, you can enter a string to that cell. (What you're talking about.)
- If an element is an S element, you can't add it to a C element in that dimension. (What he's talking about.)

[harrytm1]

Thanks, Alan, for replying to tomok on my behalf.

Yes, I do agree that if this is indeed a bug, it should have surfaced long ago. No hotfix was ever applied, only FP2.

The said user does not have access to any TI. There is also no TI that clears data over the affected cubes in one fell swoop.

Just like you, I suspected data spreading initially through either cube view or DBS. However, the string data part makes it unlikely, coupled with the sheer number of data points that were cleared.

Once again, appreciate all the comments so far.

[Alan Kirk]

Thanks, Alan, for replying to tomok on my behalf.

Not so much that, I just wanted to prevent things spinning off onto a tangent.

Just like you, I suspected data spreading initially through either cube view or DBS. However, the string data part makes it unlikely, coupled with the sheer number of data points that were cleared.

I wouldn't entirely give up on the theory. If TI is taken out of the equation the only thing I can think of that can do that volume of data points is a spread, in one form or another. The reason being that the number that you describe suggests something that is processed as a server side action, not a client side one. Had it been happening on the client side (eg through straight DBRWs) the number of transactions would be reduced by the need to pump the values through the network.

Out of curiosity, are you using sandboxing and job queuing at all?

[harrytm1]

JobQueueThreadPoolSize=3

Don't think the user knows how to use the sandbox feature.

[qml]

I can absolutely confirm there was a bug with some 9.5 versions where a user clicking Undo in Cube Viewer could inadvertently and unknowingly kick off a rollback of all transactions for all cubes that the user had any access to (and in these cubes only the cells that the user had access to; in our case it was a read-only user, BTW). The TM1 server would scan through all the closed and timestamped transaction log files available in the logging directory, even if they went weeks/months back and undo them. I struggled with this massive, painful and unbelievable bug affecting our production environment until IBM confirmed and fixed the issue. I will try and dig up some more details about the affected versions and the fix if I can. I have a vague feeling I posted something about it on the forum about 1-2 years ago.

[harrytm1]

OMG! Thanks, Kamil! Please post the info on how you and IBM resolved this issue.

Any idea if this is fixed in version 10 onwards? Is it possible to disable the Undo function via tm1p.ini or tm1s.cfg, just like what we can do to Sandbox? I will look up the parameter guide later.

[qml]

Unfortunately I am not working for that customer anymore, so I don't have access to all the details. However, I am pretty sure we were on some version of 9.5.2 then (can't remember the FP/HF number, it's likely it was a bare 9.5.2 or FP1 at most).

The issue as I remember it was as follows.

First some background. When a user clicks the Undo button, they invoke the TM1ChangeSetUndo(hPool, voServer, voChangeIdStr) API function. Now, voChangeIdStr is meant to be the string identifier of the transaction that is to be rolled back. This identifies what is called a ChangeSet and is the same alphanumeric string you can see in the first column of a transaction log and can be assigned to one or multiple changes done to data (e.g. a spread operation is a single ChangeSet, even if it affects millions of cells).

Now, what we started suspecting after it happened for the second time in our prod environment, and later got a confirmation directly from an IBM developer, that the bug cosnisted of two separate issues:

a) A user (in our case with read-only access which helped in proving the case) was somehow able to click Undo in Perspectives, even though they made no changes and the Undo button should have been inactive. At this time the client would issue a TM1ChangeSetUndo call to the server with the ChangeSet identifier parameter voChangeIdStr empty.

b) The server would accept the call with the empty transaction identifier instead of rejecting/ignoring it. This would in turn kick off a rollback of all transactions in the transaction log where the identifier in the first column was also empty. It turns out this field is empty for all transactions that are not user input, so everything loaded e.g. via TI processes was considered a match and was rolled back. This rollback would be based on user security, so if the initiating user had no read access to a given cube/cell, the rollback would not happen for that cube/cell. Also, the TM1 engine would not just go through the current open transaction log tm1s.log, but would actually scan through all the timestamped ones too in reverse order and undo everything it could find.

IBM told us the fix was very simple and consisted of adding an IF clause to the server code. We received a patch fairly quickly once the issue was identified. I cannot find this PMR on the list of fixes for any of the 9.5.2 versions or 10.1 ones, so can't guarantee the patch has made its way into the general releases, but I can't (don't want to?) imagine them not patching a vulnerability like that.

My recommendation is to raise a PMR ASAP, quoting the 'known ChangeSetUndo bug' and asking which version you should upgrade to.

[Wim Gielis]

Kamil, thank you for the useful information, also on what happens behind the scenes with the API calls and stuff.

[hbell]

Kamil

... thanks for sharing this very informative description. We also experienced a similar catastrophic data loss a few months back when we were still on 9.5.1. A READ ONLY user, with no access to TIs, initiated, in the same second, an apparently random blitz across 10 or so unlinked cubes. We could see no pattern in the data hit (some of it was pretty "out of the way" stuff that few users would have known about - including Attributes and well out of date versions). Although a lot of the hits zeroed the data, this was not true of all of them. The activity generated a log file so large (the exact size escapes me now) that we had to split it into 50 to read it in any of the (admittedly limited) text file readers we have available. Like Harry, we were only aware of a problem when we started to get user complaints, firs that the server seemed slow, and then that data was missing

At the time, the IBM helpdesk suggested it might be related to a Sandbox-based bug, but this didn't seem plausible. Our users don't really use Sandboxing, but we switched it off just to be on the safe side. We never did get to the bottom of it, but your explanation sounds much more likely. My only hesitation in accepting it absolutely would be that some of the old versions that got blitzed were very old, and it would surprise me if (but is certainly not impossible) our log files went back that far.

Luckily for us, the remedy was relatively straightforward (starting with defibrillators for the system Admins). We restored a backup from the previous night and the days activity to date had all been TI-based uploads which were easily re-run. Notwithstanding the swift recovery, it was the scariest and most surreal experience I've ever had in 17 years with TM1. The application (and therefore its problems) always used to be so simple. On occasions like this, you wonder whether the clever stuff is worth it.

We've had no recurrence (everything crossed, and touching wood) and are now on 10.1.1 ...........hugh

[harrytm1]

Thanks, Kamil!

You mentioned about IBM providing a simple fix that has to do with an IF clause in the server code. What exactly is that? It will be tough for us to move to 10.2 now, especially when budgeting exercise has started. Also, IBM didn't officially state this in the Fix Lists, so we cannot safely conclude that it is fixed in 10.2 general release.

Once again, appreciate everyone's contributions to this thread!

[qml]

You mentioned about IBM providing a simple fix that has to do with an IF clause in the server code. What exactly is that?

I really don't remember the details, but it would have been as simple as IBM devs preparing a HotFix and sending it to us in the form of a small archive containing some files that we just replaced on the server. I haven't seen the problem since, either with that model/version or with any later ones, even though this issue does not seem to be mentioned on any official Fix List.

Edit: My secret spies have just returned with some intelligence strongly suggestive of this issue having been fixed in 9.5.2 FP3.