There is a more robust way if you running MySQL 5
Export this query using mysql client to an SQL script like this
mysql -h<...> -u<...> -p<...> --skip-column-names -A -e"SELECT CONCAT('OPTIMIZE
TABLE ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE
ENGINE='InnoDB'"
Then run the script using mysql client.
Please remember, OPTMIZE TABLE does absolutely nothing if all InnoDB data
resides in the shared space.
Your must create all InnoDB tables as separate entities.
To do this, mysqldump all tables to a dump file.
Shutdown MySQL
add 'innodb_file_per_table' to my.cnf
Delete the ibdata files and the logs
Startup MySQL
Reload dump file.
Each InnoDB will reside in .frm and .ibd files
OPTIMIZE will defragment each tablespace (.ibd) file
----- Original Message -----
From: "Chris White" <[EMAIL PROTECTED]>
To: "Marten Lehmann" <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
Sent: Tuesday, February 6, 2007 1:24:46 PM (GMT-0500) US/Eastern
Subject: Re: innodb madness
Marten Lehmann wrote:
> How can I check which tables are using innodb with sql? How can walk through
> the tables with "show databases" and "show tables". Thanks.
>
This somewhat depends on how the tables were declared. If you used
"ENGINE=InnoDb;" in the CREATE TABLE sequence, you'd be able to loop
through the results of "SHOW TABLES" and run a "SHOW CREATE TABLE" on
each of the resulting tables to get that answer.
While there is no REPAIR TABLE, a strange table locking issue we were
having yesterday was apparently solved by a run of OPTIMIZE TABLE on the
table at hand. Why we're not sure yet, but things are working so can't
complain too much...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
