Partial delete success: expected behavior? workarounds?

[Laura Lark]

Hi,

I'm running into issues with large batch deletes that seem to succeed partially. I have three related tables - A stores child objects of B, B stores child objects of C. Let's call them Pets, People, and Cities - each person tends to have around one pet, but might have several or might have none. Each city has a lot of people.  I'm working on scaling up to larger cities - I'd like to get to millions eventually. Things worked fine up to around 1000 people, but when I got to around 5000 people, I got some weird behavior on deletion.

This is what I do when I delete a city:

My pets refer to my people by foreign key ID, so I have to remove the pets first. My query looks like:

DELETE FROM Pets WHERE person_id IN (SELECT person_id FROM People WHERE city_id=?)

Code looks like:

    PreparedStatement stat = connection.prepareStatement(<above statement>);

    stat.setString(1, city_id);

    return stat.executeUpdate();

Then if that succeeds (returns > 0 is the condition I'm using), I delete all the people (the people refer to city by foreign key ID) with a query like:

DELETE FROM People WHERE city_id=?

and similar code to the pets.

This is where it gets weird. My deletion of pets returns only ~2000 (one time it was 1405, another time 2803) out of the expected 5000 deletions. I don't get an exception or anything. The other ~3000 pets are still in the database - I can go inspect it manually and see them:

MySQL [adam1]> select count(*) from People ppl inner join Pets pts on ppl.person_id=pts.person_id where ppl.city="some city id here";+----------+| count(*) |+----------+| 2197 |+----------+1 row in set (0.16 sec)

Then of course my deletion of people fails because of a foreign key constraint violation. I don't see anything strange about my database at these points - CPU utilization hasn't ever gone over 70%, memory utilization is pretty low, no updates happening simultaneously. I do see tons and tons of messages like "Aborted connection <number> to db: '<db>' user: '<user>' host: 'cloudsqlproxy~<numbers>' (Got an error reading communication packets)", but those happen all the time (many per second, when the db is heavily in use), don't seem to be correlated with this issue.

Is this expected, that a delete partially succeeds? Is there a way for me to detect that it has only partially succeeded besides trying and failing to delete the referenced people?

The project ID is adam-dev-193118, database adam1. The pet, people, and city tables are Runs, Batches, and Projects. Example log is at 2018-02-07 17:43:16.107 EST. Happy to provide additional information.

Any help is appreciated!

-Laura

[Kenworth (Google Cloud Platform)]

Hi Laura, a thread like this is off-topic for Google Groups, and should be posted to StackOverflow. While I'd like to help you, this isn't the place to do it. This forum isn't meant for specific 1-on-1 technical issues, but for general discussion of the platform and its services. With StackOverflow, which we also monitor, you'll be in touch with a much larger user-base of people who are going to help you, and in a format which is designed for that purpose. Check out our community support page for the list of tags we monitor and this StackOverflow How-To-Ask guidelines.

[Laura Lark]

Gotcha, I'll head over there! https://stackoverflow.com/questions/48696614/partial-delete-success-expected-behavior-workarounds for anybody combing the internet after a similar problem.