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]
