Altering InnoDB tables

[Robert P Ricci]

I've recently converted from using MyISAM to InnoDB tables, and am
pleased with InnoDB so far. However, the inability to use 'ALTER TABLE'
with InnoDB tables is very problematic, as our software is still under
heavy development and our database schema changes frequently. I'm
working on a way to automate the drop/create/repopulate process to alter
tables, but am running into a few problems:

Is there a way to prevent clients from noticing that the table in
question is temporarily gone? Something like locking the entire database
for writing?

Second, mysqldump doesn't give foreign key constraints in its table
creation commands. Is there any way to retrieve these?

-- 
/---
| Robert P Ricci [EMAIL PROTECTED] || [EMAIL PROTECTED]
| Research Associate, University of Utah Flux Group
| www.cs.utah.edu/flux || www.emulab.net
\---

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Altering InnoDB tables

[Robert P Ricci]

Thus spake John Kemp on Mon, Jan 14, 2002 at 02:37:07PM -0500:
 2) Then I tried mysqldumping the table structure, and changing MyISAM - 
 INNODB, dropped the table, and ran the mysqldump output back into the 
 database. The INSERTS ran horribly slowly - something like 50,000 
 inserts in 4 hours. This extrapolates to a query that would run for 
 several weeks on 5 million rows, so I stopped it again.

Did you try putting 'set autocommit=0;' at the top of your dump, and
'commit;' and at the bottom? That may help, though it sounds like it's
the index creation, and not any transaction overhead, that's causing you
trouble.

Luckily, in my case, our database is sufficiently small (on the order of
tens of thousands of rows spread out across ~50 tables) that this isn't
really an issue.

-- 
/---
| Robert P Ricci [EMAIL PROTECTED] || [EMAIL PROTECTED]
| Research Associate, University of Utah Flux Group
| www.cs.utah.edu/flux || www.emulab.net
\---

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php