#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171

    What happenes when mysql crashes during insert, update or delete


    Hi;

    I wonder what happenes when Mysql ( with either MyIsam or InnoDb storage engine) crashes while it is updating a table with millions of records. As an example this query below takes about 8 seconds for me to run, I wonder what happens when its 4 seconds is and the server looses power or shuts down for any reasons.
    Does it treat the update, insert and delete as transations? Or commits changes as it goes without waiting for everything to take place?
    Code:
     INSERT INTO allotments_new
                (package_id,
                 room_id,
                 hotel_id,
                 date_added,
                 day_date,
                 day_rate,
                 number_of_rooms,
                 status,
                 supplier_id,
                 day_of_the_week)
    SELECT packages.id,
           packages.room_id,
           packages.hotel_id,
           Curdate(),
           annual_calendar.day_date,
           low,
           '0',
           'b',
           hotels.beta_supplier,
           Dayname(annual_calendar.day_date)
    FROM   packages
           INNER JOIN hotels
                   ON hotels.hotel_id = packages.hotel_id
           INNER JOIN hotelrooms
                   ON hotelrooms.room_id = packages.room_id
           LEFT OUTER JOIN calendars_new
                        ON calendars_new.hotel_id = hotels.hotel_id
                           AND calendars_new.day_date BETWEEN
                               '2013-04-22' AND '2014-04-22'
           LEFT OUTER JOIN allotments_new
                        ON allotments_new.package_id = packages.id
                           AND allotments_new.day_date BETWEEN
                               '2013-04-22' AND '2014-04-22'
                           AND calendars_new.day_date = allotments_new.day_date
           LEFT OUTER JOIN annual_calendar
                        ON annual_calendar.day_date = calendars_new.day_date
                           AND annual_calendar.day_date BETWEEN
                               '2013-04-22' AND '2014-04-22'
    WHERE  allotments_new.day_date IS NULL
  2. #2
  3. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171
    Hello;

    Still no answer I try again

    Are update, inset and edit queries treated as transactions?

    Or they could be executed partially?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    348
    Originally Posted by zxcvbnm
    Does it treat the update, insert and delete as transations?
    InnoDB does, MyISAM does not.

    Or commits changes as it goes without waiting for everything to take place?
    MyISAM works that way, InnoDB doesn't
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  6. #4
  7. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171
    Originally Posted by shammat
    InnoDB does, MyISAM does not.
    MyISAM works that way, InnoDB doesn't
    Thank you

IMN logo majestic logo threadwatch logo seochat tools logo