[URGENT] Table empty after bulk delete query timeout

Data Service
1

As part of a cleanup routine, we ran a bulk delete query from the UI REST Console. The table in question had over 400k rows before running the query.

The query timed out.

APPID: 028AF23A-DE67-970F-FF7B-CE73726F3E00

Query: /data/GameClips?property=Count(%60gameID%60)%20as%20idCount&groupBy=%60gameID%60&having=idCount%3E1

Expected result: Less than 100k objects should have been cleared as a result of the query.

Actual result: Table has been completely cleared.

2

If the table is empty, it means the query deleted the records.

Right now, the table has 737 records. Using precisely the same query as you specified, if you get the number of records matching the query, the response is 737. This means a bulk delete operation using that very query will delete all records.

Here’s how you can validate it:

REST Console - looplay - Backendless 2024-01-26 10-51-11
REST Console - looplay - Backendless 2024-01-26 10-51-111370×497 54.6 KB

If you would like us to restore the records, we can check if there is a backup of the app. If we have, the app will be restored to the state it was at the time when the backup was made.

There is a $250 fee to cover the engineer’s time working on the restore procedure. It takes about 4 hours to complete it.

Regards,
Mark

3

Your query is missing the groupBy, the groupBy would prevent this from including all records would it not?

4

Here’s with groupBy, still returns all records.

REST Console - looplay - Backendless 2024-01-26 11-06-43
REST Console - looplay - Backendless 2024-01-26 11-06-431310×521 38.9 KB

5

but without the ObjectID: count it returns an empty array? I’m not sure what’s the proper result in this case

Shouldn’t my query ONLY return rows with duplicate gameID properties?

I ran this exact query (with a different prop than gameID) on another table and it did not delete all records, the only difference here is that the query timed out.

6

It returns an empty array because the permission to retrieve data (which is different than the count operation) is denied for the RestUser role:

Data Roles Permissions - looplay - Backendless 2024-01-26 11-13-24
Data Roles Permissions - looplay - Backendless 2024-01-26 11-13-24807×748 39.7 KB

7

I see, thanks for explaining that, shouldn’t this query only return rows with duplicate gameID properties?

EDIT: Can we please also check if a backup is available?

EDIT2: The query was run using CloudCode Key and not Rest, so it should have all permissions I believe

8

Hello @Team , any updates?

9

To get the objects where you have two or more records sharing the same gameID, you would need to count objectId and not gameID.

We’re checking the backup and will let you know what we find.

10

There is a backup, it was made this morning at 9:00 UTC.

11

Correct me if I’m wrong but if I use COUNT(objectId) instead of COUNT(gameID) I would be returning all records where objectId is duplicated which is always 0.

In our case, the gameID was not set as unique and the aim was to cleanup records with duplicate gameID values, therefore the query is checking if COUNT(gameID) > 1.

I’m not sure whether you are able to check or not, but we have run this same query on the Games table but with the gameId property and it has successfully found and deleted the correct records.

I have managed to recover most of the data that has been deleted, but I repeat my initial report that this has happened after the query timed out and since I ran the exact same query successfully before, I can only assume a timeout related bug has caused this issue.

For the time being I will no longer be using the bulk delete command as I cannot guarantee that it will not sporadically delete entire tables and I think it might be worth looking into why this has happened.

12

Hi Karim,

Technically, it would be more accurate to count objectId when you group data by some criteria. What the database does is count the number of objects in each group.

For any bulk operation, especially for bulk delete, it is recommended to check what the query returns with a GET request. Whatever is returned by GET will be deleted when you run bulk delete.

As for the timeout, generally, it would not impact the database request. Here’s an example:

  1. The client app invokes a custom API service. The API service has 5 seconds to process the request (unless you purchased an extension from the marketplace).
  2. The API service executes a long-running database query
  3. If the API service is not done in 5 seconds, it will be interrupted and the client will get a timeout exception. However, the request sent to the database will continue running.

Regards,
Mark

13

Just as a preface to my message, we no longer need the backup to be restored, this is just a bug report because I personally love using Backendless and have been for almost a decade now and I would like to help make it better for myself and other developers.

Query: /data/GameClips?property=Count(%60gameID%60)%20as%20idCount&groupBy=%60gameID%60&having=idCount%3E1

The above query DOES NOT return all records of the table, it accurately only returns the records where gameID is found in more than one record. I have tried this on a separate app as well as the app mentioned in a separate table (Games) than the one that got affected by the issue.

Adding count to the ObjectID field in the REST Console does not return the correct value matched by the query.

In our dev app (APP ID 07BA7B74-B465-6D18-FF8C-18F769ED0C00), I have replicated the same situation, we have a GameClips table with only 4 rows, 2 of which have duplicate gameID properties.

Running the query without ObjectID: count returns the expected result:

[
	{
		"___class": "GameClips",
		"idCount": 2
	}
]

But running it with ObjectID: count returns 3 which is incorrect.

I understand that generally the query should have run as expected, but since it deleted all the records in the table when over 300k were unique, I can only assume something went wrong.

I hope you guys can look into this to avoid this happening again in the future.