Waterline truncated a join table - this is a MAJOR issue

[mdarveau]

I had an issue on a production server where all data from a join table was dropped. After days of investigation, I found out that waterline generated a delete statement without any where clause on that table.

Repro steps:

• Create a Model with a many to many collection of elements. eg: Team <--> Player
• Insert Team T1 with Players P1 and P2. Insert Team T2 with Players P3 and P4. This will generate two rows in the Team table, 4 rows in the Player table and 4 row in the Team_Player join table.
• Issue two delete statements for team T1 at the same time. This is important and is probable with HTTP double post.
• Waterline will generate the following (extract from my postgres log):

2015-01-27 21:57:02 EST LOG:  execute <unnamed>: DELETE  FROM "team" AS "team"  WHERE "team"."id" = $1  RETURNING *
2015-01-27 21:57:02 EST DETAIL:  parameters: $1 = '12'
2015-01-27 21:57:02 EST LOG:  execute <unnamed>: DELETE  FROM "team" AS "team"  WHERE "team"."id" = $1  RETURNING *
2015-01-27 21:57:02 EST DETAIL:  parameters: $1 = '12'
2015-01-27 21:57:02 EST LOG:  execute <unnamed>: DELETE  FROM "team_players__user_teams" AS "team_players__user_teams"  WHERE "team_players__user_teams"."team_players" IN ($1)  RETURNING *
2015-01-27 21:57:02 EST DETAIL:  parameters: $1 = '12'
2015-01-27 21:57:02 EST LOG:  statement: DELETE  FROM "team_players__user_teams" AS "team_players__user_teams"   RETURNING *

See the last delete statement that does not have a where clause. This will in fact truncate the table thus removing players from all games.

I traced the code and the following change created this issue: balderdashy/waterline@388473f

I tried commenting the if and while I get an exception because the second delete does not return any result (var mappedValues = result.map(function(vals) { return vals[pk]; }); will not contain anything), I don't get any dataloss. Do I need to say that it is better to have an error because of concurrency instead of a silent truncation of all data?

[mdarveau]

Might also be related to https://github.com/balderdashy/waterline/issues/548.

[luislobo]

It seems it is related to this #799 issue too

[luislobo]

Just posted a PR in #815 for this

[erkstruwe]

Also related to / duplicate of https://github.com/balderdashy/sails-mysql/issues/174

[mdarveau]

Wow! So this is a known issue for more than a month now. I had a major data loss in production (lost one day since i had to restore a nightly backup) that could have been avoided by simply disclosing the issue on the sails list.

That's it, i'm done with waterline. Thanks for the kick start but, i'm rewriting the codebase to use another lib.

[particlebanana]

@mackeeper I'm so sorry about that, it sounds awful and I'm truly sorry waterline was the cause of this.

This should now be fixed in 0.10.17 thanks to balderdashy/waterline#815

[devinivy]

Closely related, also urgent: #858.