[Maria-developers] How MySQL/MariaDB handles aborted DDL
Hi Philip, all Philip's bug on recovery from a kill -9 during DML reminded me... from my experience, aborting a DDL query (ALTER TABLE - things like adding an index usually) can be tricky and more often than not causes hassles. By abort in this case I mean killing the connection/thread that does the DDL. It would be great to somehow have a few test cases in the suite and it needs to be tested with all storage engines. Provided the table it operates on is big enough, it should be possible to just zap it by timing. Thoughts? Cheers, Arjen.
Arjen, What do you mean by "can be tricky and more often than not causes hassles", and in general, what is the semantics of a DDL KILL operation? For example, are you allowed to leave temporary files and if not, who is responsible for cleaning them up -- the engine or the server? I am afraid that a timing-based method can only be used to test a KILL during the main phase of the DDL, that is, when the temporary table or the new index are being built. However, it would not be possible to reliably hit any of the other phases, such as when the old table is being replaced by the new table, without some explicit instrumentation of the code. Philip Stoev ----- Original Message ----- From: "Arjen Lentz" <arjen@openquery.com> To: "maria-developers" <maria-developers@lists.launchpad.net> Cc: "Philip Stoev" <philip@stoev.org> Sent: Wednesday, July 07, 2010 3:03 AM Subject: How MySQL/MariaDB handles aborted DDL
Hi Philip, all
Philip's bug on recovery from a kill -9 during DML reminded me... from my experience, aborting a DDL query (ALTER TABLE - things like adding an index usually) b. By abort in this case I mean killing the connection/thread that does the DDL.
It would be great to somehow have a few test cases in the suite and it needs to be tested with all storage engines. Provided the table it operates on is big enough, it should be possible to just zap it by timing. Thoughts?
Cheers, Arjen.
Hi Philip On 07/07/2010, at 5:21 PM, Philip Stoev wrote:
What do you mean by "can be tricky and more often than not causes hassles",
"Crashed" and inaccessible tables.
and in general, what is the semantics of a DDL KILL operation? For example, are you allowed to leave temporary files and if not, who is responsible for cleaning them up -- the engine or the server?
See Stewart's answer - engine is responsible for all that.
I am afraid that a timing-based method can only be used to test a KILL during the main phase of the DDL, that is, when the temporary table or the new index are being built. However, it would not be possible to reliably hit any of the other phases, such as when the old table is being replaced by the new table, without some explicit instrumentation of the code.
Sure - but I think it's the main phase... user starting an ALTER TABLE, realising it's going to take a long time (forgot to set a large myisam_sort_buffer_size on a MyISAM table, for instance) and wants to abort, fix up the settings and try again (or wait until a more appropriate time later). Cheers, Arjen.
----- Original Message ----- From: "Arjen Lentz" <arjen@openquery.com> To: "maria-developers" <maria-developers@lists.launchpad.net> Cc: "Philip Stoev" <philip@stoev.org> Sent: Wednesday, July 07, 2010 3:03 AM Subject: How MySQL/MariaDB handles aborted DDL
Hi Philip, all
Philip's bug on recovery from a kill -9 during DML reminded me... from my experience, aborting a DDL query (ALTER TABLE - things like adding an index usually) b. By abort in this case I mean killing the connection/thread that does the DDL.
It would be great to somehow have a few test cases in the suite and it needs to be tested with all storage engines. Provided the table it operates on is big enough, it should be possible to just zap it by timing. Thoughts?
Cheers, Arjen.
-- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget. Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org
participants (2)
-
Arjen Lentz -
Philip Stoev