I recently had to detect whether a MySQL server had any tables that didn't support transactions (mainly MyISAM).
The only problem is that this server had close to 5000 indivitual databases, and my usual query didn't cut it:
SELECT information_schema.TABLES.TABLE_SCHEMA,
information_schema.TABLES.TABLE_NAME,
information_schema.TABLES.ENGINE,
FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA not in ('performance_schema', 'mysql', 'information_schema')
AND information_schema.TABLES.ENGINE not in ('InnoDB', 'XtraDB');
So I had the idea to look at their file representation on disk. Based on the documentation about the default storage engines, I created one of each, in a dummy database:
create database enginetests;
CREATE TABLE InnoDB (i INT) ENGINE = InnoDB; -- Does the capitalized name carry over to the filesystem?
CREATE TABLE myisam (i INT) ENGINE = MyISAM;
CREATE TABLE memory (i INT) ENGINE = MEMORY;
CREATE TABLE csv (i INT not null) ENGINE = CSV;
CREATE TABLE archive (i INT) ENGINE = ARCHIVE;
CREATE TABLE blackhole (i INT) ENGINE = BLACKHOLE;
-- merge
CREATE TABLE t1 (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
message CHAR(20)) ENGINE=MyISAM;
CREATE TABLE t2 (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
message CHAR(20)) ENGINE=MyISAM;
INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
CREATE TABLE merge (
a INT NOT NULL AUTO_INCREMENT,
message CHAR(20), INDEX(a))
ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
-- skipping FEDERATED. not enabled by default
-- skipping EXAMPLE. for MySQL developers
And now I could look at their disk representation:
ls -l /var/lib/mysql/enginetests
8642 Jan 30 21:36 archive.ARZ
8554 Jan 30 21:36 archive.frm
8554 Jan 30 21:36 blackhole.frm
35 Jan 30 21:31 csv.CSM
0 Jan 30 21:31 csv.CSV
8554 Jan 30 21:31 csv.frm
61 Jan 30 21:27 db.opt
8554 Jan 30 21:30 InnoDB.frm # The capitalized name does carry over to the filesystem!
98304 Jan 30 21:30 InnoDB.ibd
8554 Jan 30 21:29 memory.frm
8590 Jan 30 21:38 merge.frm
26 Jan 30 21:38 merge.MRG
8554 Jan 30 21:30 myisam.frm
0 Jan 30 21:30 myisam.MYD
1024 Jan 30 21:30 myisam.MYI
8590 Jan 30 21:38 t1.frm
195 Jan 30 21:38 t1.MYD
2048 Jan 30 21:38 t1.MYI
8590 Jan 30 21:38 t2.frm
195 Jan 30 21:38 t2.MYD
2048 Jan 30 21:38 t2.MYI
So now it was only a question of using find
and parsing through the results :-)