SQL ETL - On update INSERT-query hits DB before DELETE

[Anton Möller]

Hi!

I'm having issues with SQL ETL to a Postgres DB where when a document is updated, RavenDB sends off the INSERT-query to the DB before the DELETE-query has hit, causing both the existing record and new record to be deleted.

For example:

1. Document/1 is in DB,
2. Document/1 gets updated in RavenDB
   
3. INSERT INTO ... VALUES (Document/1) gets executed
   
4. DELETE WHERE (Document/1) gets executed

whereas the correct order of operations would be to execute the delete well before the insert (as the delete simply looks for the ID column).

With this approach, there's no data integrity guaranteed at all...

[Oren Eini (Ayende Rahien)]

That isn't how it works, we are sending the deletes then the inserts.

See here:

https://github.com/ravendb/ravendb/blob/9bf5578d58acd59d9a731eedf3edb838bc40017c/src/Raven.Server/Documents/ETL/Providers/SQL/RelationalWriters/RelationalDatabaseWriter.cs#L427-L437

--
You received this message because you are subscribed to the Google Groups "RavenDB - an awesome database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/affd815b-e4a4-4cf0-bcb0-bc07a1f60d0bn%40googlegroups.com.

--

Oren Eini CEO   /   Hibernating Rhinos LTD Mobile:  972-52-548-6969 Sales:  sa...@ravendb.net Skype:  ayenderahien Support:  sup...@ravendb.net

[Anton Möller]

...and one final point in case, here are RavenDBs own internal logs showing the INSERT being sent before the DELETE:

2021-10-12T19:05:09.2716797Z, 57, Information, database, Raven.Server.Documents.ETL.Providers.SQL.RelationalWriters.RelationalDatabaseWriter, Insert took: 22ms, statement: INSERT INTO r_test (id, test) VALUES (@id, @test)

2021-10-12T19:05:09.2717588Z, 57, Information, database, Raven.Server.Documents.ETL.Providers.SQL.SqlEtl, [RavenDB to MariaDB/Script_4] Inserted 1 (out of 1) records to 'r_test' table from the following documents: Test/abc

2021-10-12T19:05:09.2943338Z, 57, Information, database, Raven.Server.Documents.ETL.Providers.SQL.RelationalWriters.RelationalDatabaseWriter, Delete took: 22ms, statement: DELETE FROM r_test WHERE id IN ('Test/abc')

2021-10-12T19:05:09.2943807Z, 57, Information, database, Raven.Server.Documents.ETL.Providers.SQL.SqlEtl, [RavenDB to MariaDB/Script_4] Deleted 1 (out of 1) records from 'r_test' table for the following documents: Test/abc

2021-10-12T19:05:09.3195366Z, 57, Information, database, Raven.Server.Documents.ETL.Providers.SQL.SqlEtl, SQL ETL process 'RavenDB to MariaDB/Script_4' processed the following number of items: Document - 1 items, last transformed etag: 48848648 in 00:00:00.1607751 (last loaded etag: 48848648) Batch completion reason: No items to process

2021-10-12T19:05:09.3228967Z, 24, Information, A, Raven.Server.Rachis.RachisConsensus, Apply of UpdateEtlProcessStateCommand with index 128250 was successful.

[Anton Möller]

Yes, I see that your code sends the commands in the right order, but I said that they are received in the wrong order on the server-side. I've tried now with multiple types of RDS (PostgreSQL / MySQL), and they both receive the statements in the wrong order. 

Here is 1 of many excerpts from our log clearly showing the order commands were executed:
2021-10-12 13:04:18 UTC:20.52.47.2(65396):root@ravendb:[1304]:LOG: statement: DISCARD ALL
2021-10-12 13:04:18 UTC:20.52.47.2(65396):root@ravendb:[1304]:LOG: statement: BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED
2021-10-12 13:04:18 UTC:20.52.47.2(65396):root@ravendb:[1304]:LOG: execute <unnamed>: INSERT INTO r_tab ("id") VALUES ($1)
2021-10-12 13:04:18 UTC:20.52.47.2(65396):root@ravendb:[1304]:DETAIL: parameters: $1 = 'Tab/57708-A'
2021-10-12 13:04:19 UTC:20.52.47.2(65396):root@ravendb:[1304]:LOG: execute <unnamed>: DELETE FROM r_tab WHERE "id" IN ('Tab/57708-A')
2021-10-12 13:04:19 UTC:20.52.47.2(65396):root@ravendb:[1304]:LOG: statement: COMMIT

To entirely avoid this, isn't it possible to send the DELETE in its own transaction and commit that before the INSERT is sent?

On Tuesday, 12 October 2021 at 13:23:49 UTC+2 Oren Eini wrote:

[Anton Möller]

Another log excerpt, this time from a fresh MariaDB/MySQL DB receiving the commands in the wrong order:

211012 17:55:01 18 Init DB ravendb

18 Query SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

18 Query BEGIN

18 Query INSERT INTO r_test (id, test) VALUES ('Test/abc', 'def')

18 Query DELETE FROM r_test WHERE id IN ('Test/abc')

18 Query COMMIT

On Tuesday, 12 October 2021 at 13:23:49 UTC+2 Oren Eini wrote:

[Oren Eini (Ayende Rahien)]

What does your ETL script look like? Do you call loadTo() multiple times?

To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/a30926f5-1a5d-493d-a1b9-86f1f2937e03n%40googlegroups.com.

[Anton Möller]

Discussion shifted to https://issues.hibernatingrhinos.com/issue/RavenDB-17326

[Anton Möller]

For reference - this issue was caused by a mismatch between actual table names in the ETL configuration page and method called:

We had named tables "r_test" in the SQL Database as well as in the "table name" section of the ETL configuration, but we called the function "loadToR_Test" instead of "loadTor_test". If your table is called "abcDEFghi" you need to call the method "loadToabcDEFghi()".

Make sure your tables and loadTo´s match (case-sensitive) exactly for functioning SQL ETL. 

[Oren Eini (Ayende Rahien)]

Or wait a few days for the PR that fixes this to land :-)

To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/4a4dd042-3a59-4d4b-a6be-e388222b0c30n%40googlegroups.com.

[Ryan Heath]

Interestingly how casing could cause a change in order of execution, great find!

// Ryan

To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/CAF0G-Zi8Fp8P-ijTYa88OPoEtSFpgcrACm4XpQukQB%3DNbT%3DyvA%40mail.gmail.com.

[Arkadiusz Palinski]

In general we're case insensitive, but in one location we had a case sensitive dictionary with table names. We execute DELETE and INSERT statements per table. Because of the issue we had 2 separate records in the dictionary ( r_test and R_Test). That made that we didn't send DELETE for the first record (just INSERT), while we sent only DELETE for the second record. Although effectively we send it against _the same_ table.

To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/CABcSOXisF49c3dnYMisscsUCZWrFN_6vZ%2BhH5AiBMnBHKzotmg%40mail.gmail.com.

--

Arkadiusz Paliński Team Leader   /   Hibernating Rhinos LTD Sales:  sa...@ravendb.net

[Oren Eini (Ayende Rahien)]

More details:

https://ayende.com/blog/195041-C/finding-a-bug-with-code-that-isnt-there?key=ce6fc4404aef4063b1caf6337258b17f

To view this discussion on the web visit https://groups.google.com/d/msgid/ravendb/CAEUAT1g%2BbQeUYhVF-YCOdCPcsp4sXotaXK1TPP%3DU-yNrkKKqEw%40mail.gmail.com.