0

I'm writing an infrastructure library for our mysql servers. One of scenarios I have to handle is a temporary disconnection from the mysql server.

The test scenario is start around 100000 inserts to the DB , and then stop the db and bring it back.

During this scenario I'm obiously getting ER_QUERY_INTERRUPTED error on the queries running during stop. The question is:

Can I assume something about the state of the db (like query not started) or regard this as an undefined behaiviour and drop this problem down the chain to user?

Roman M
  • 8,849
  • 2
  • 15
  • 17
  • As long as you are doing this all inside of a transaction, you can safely re-try the query. A disconnection during a transaction will cause an automatic rollback on the SQL server. If you're outside of a transaction, the query _may have_ committed, or _may not have_. Inserting again could result in duplicate data. – Colin M Jul 18 '13 at 12:29
  • @ColinMorelli: If one is using a transactional storage engine, such as `InnoDB`, then all operations are "inside a transaction" (albeit they may autocommit). – eggyal Jul 18 '13 at 12:33
  • @eggyal What I meant by that is a non-autocommiting transaction. If you don't _explicitly_ send the `COMMIT`, you can't be sure if the query was executed or not during a disconnect. – Colin M Jul 18 '13 at 12:34
  • As an additional note to the OP, I don't believe there is a good way to implement this generically. It really should be something you address for individual pieces of your application. Then you can pick identifiers to use such that you _know_ if data has been inserted or not. If you have no unique constraints in your table (except for the PK), and you use auto-increment, then you're likely to end up with duplicate data at some point. You could generate GUIDs in your application as a solution to that. – Colin M Jul 18 '13 at 12:35
  • @ColinMorelli: Yes, you can. If you don't send a commit (and are not autocommitting), then with a transactional storage engine you can be *certain* that the transaction will have been rolled back. – eggyal Jul 18 '13 at 12:37
  • @eggyal That's what I said in my first comment ("A disconnection during a transaction will cause an automatic rollback on the SQL server") - what I meant is that if _your application_ is not the one to explicitly send the `COMMIT`, then you can't be sure. In other words, if you're auto-committing, you can't be sure. – Colin M Jul 18 '13 at 12:38
  • @ColinMorelli The query is outside the transaction. That why this question arises. – Roman M Jul 18 '13 at 12:46
  • @ColinMorelli basically your anwear is that this error always creates an undefined state to the database. Can be either commited or not. BTW can there be a third stage? (partial insert or something like that...) – Roman M Jul 18 '13 at 12:49

1 Answers1

0

Well after some testing the following answers appeared. We (and most people) use InnoDB whose default mode is transaction . Because of that ER_QUERY_INTERRUPTED is sent to the client, the query on the server is rolled back. Due to this error handling strategy, in this case, we will be just resubmitting all queries with such error.

Zeeshan Hassan Memon
  • 8,105
  • 4
  • 43
  • 57
Roman M
  • 8,849
  • 2
  • 15
  • 17