Chris, From: Chris Nolan ([EMAIL PROTECTED]) Subject: InnoDB questions for all! This is the only article in this thread View: Original Format Newsgroups: mailing.database.myodbc Date: 2003-08-28 16:16:53 PST
> Hi everyone! > > My silly questions for today concern the not-silly-at-all InnoDB table > backend. not silly questions at all. > 1. If I do an ALTER TABLE tbl_name TYPE=InnoDB on an InnoDB table, > Heikki has > indicated that a defrag of that table should happen. Does anyone have > any comments regarding the > result of a power failure while this operation is in progress? MySQL performs an ALTER TABLE tablename by (1) first creating a temporary table with the new definition, and then copying all rows to it. Then it (2) renames the old table, (3) renames the temporary table to 'tablename', and (4) drops the old table. This should ensure the data is never lost, even in a power failure. If the failure happens at a bad time, you may end up with 2 renamed tables and no table of the original name 'tablename'. But no one has ever reported this situation. InnoDB also contains a special mechanism by which you can rename a temporary table #sql.., see http://www.innodb.com/ibman.html#InnoDB_troubleshooting_dict. > 2. If I do a SELECT * .... on an InnoDB table and dump the output to a > file inside a single transaction, > will INSERT statements still complete correctly? I've completed a 3rd > year Database course at a decent uni > and know the theory as well as the practice, but admittedly I don't know > enough about InnoDB's innards to > answer this question for myself. My gut feeling says that INSERTs will > complete successfully while this process > is going on and that UPDATEs may complete, depending on InnoDB's method > of multiversioning. A SELECT ... INTO OUTFILE ... does not set any locks or disturb INSERTs or UPDATEs. The only limitation is that InnoDB cannot purge its history while the SELECT is transaction is active. If the SELECT transaction lasts long, say for hours or days, you might run out of space in the tablespace. > Thanks all! > > Regards, > > Chris Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
