Disk representation of the different MySQL engines

readme.md

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 :-)