2

I have a table with >19M rows that I want to create a subtable of (I'm breaking the table into several smaller tables). So I'm doing a CREATE TABLE new_table (SELECT ... FROM big_table). I run the query in MySQL Workbench.

The query takes a really long time to execute so eventually I get a "Lost connection to MySQL server" message. However, after a few minute the new table is there and it seems to contain all the data that was supposed to be copied over (I'm doing a GROUP BY so cannot just check that the number of rows are equal in both tables).

My question is: Am I guaranteed that the query is completed even though I lose connection to the database? Or could MySQL interrupt the query midway and still leave a table with incomplete data?

Dr Ljotsson
  • 462
  • 1
  • 4
  • 14
  • I don't know the answer to your question, but https://stackoverflow.com/questions/15712512/mysql-workbench-how-to-keep-the-connection-alive you might want to use keepalive to address this – erik258 Oct 23 '20 at 14:50
  • Yes, I know that I can increase the timeout limit. But I'm interested in the principal question of whether the query will complete even though the connection is lost. Or can the query itself timeout or silently fail? – Dr Ljotsson Oct 23 '20 at 14:52
  • timeout != keepalive. But yeah, I am interested in seeing the answer to your question. I _suspect_ the answer is "yes, because MySQL is not transactional with DDL and so the CREATE TABLE can't roll back even if the client is no longer connected to receive the response". But I personally would not count on it. – erik258 Oct 23 '20 at 14:57

1 Answers1

0

Am I guaranteed that the query is completed even though I lose connection to the database?

No. There are several reasons other than connection timeout to get lost-connection errors. The server might crash due to used-up disk space or a hardware fault. An administrator might have terminated your session.

"Guarantee" is a strong word in the world of database management. Because other peoples' data. You should not assume that any query ran correctly to completion unless it ended gracefully.

If you're asking because an overnight query failed and you don't want to repeat it, you can inspect the table with stuff like COUNT(*) to convince yourself it completed. But please don't rely on this kind of hackery in production with other peoples' data.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thanks! Would you say this also applies even if the table has been created and contains rows? – Dr Ljotsson Oct 23 '20 at 18:16
  • I recognize the general wisdom of not trusting a query that I did not see succeed. But I still wonder if it is technically possible for MySQL to leave a CREATE TABLE SELECT in an unfinished state? Like, the table was created but only 90% of the rows were added. – Dr Ljotsson Oct 23 '20 at 20:48