Why does InnoDB create MYD and MYI files?

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Why does InnoDB create MYD and MYI files?

Postby spookybathtub » 21. June 2018 16:49

I'm running XAMPP 5.6.36 on Windows, and I notice in the mysql data directory, every table has three files: FRM, MYD, and MYI. All my databases are using the default InnoDB engine. My understanding is that MYD and MYI files are only used for MyISAM, so why do these files exist? I have read several questions on Stack Overflow (like this one), where people assumed the poster was asking about MyISAM just because these files exist, and therefore gave risky advice about backing up.

Also, I just want to make sure I understand this correctly: MyISAM tables can be selectively restored from a backup simply by copying those three files. But for InnoDB, since all databases are stored in one "ibdata1" file, you can't easily restore just one db or table, you have to restore the entire data folder at once. Is that right?
spookybathtub
 
Posts: 17
Joined: 02. June 2013 04:19
XAMPP version: 5.6.33
Operating System: 10.13.4

Re: Why does InnoDB create MYD and MYI files?

Postby Nobbie » 21. June 2018 19:10

spookybathtub wrote:My understanding is that MYD and MYI files are only used for MyISAM, so why do these files exist?


Your understanding is OK and on my Xampp installation everything works as expected. You dont tell anything about your test scenario, i assume that your test somehow contains ENGINE (or TYPE=) MyISAM.

I simply start the mysql interpreter from the commandline, then i entered:

Code: Select all
USE test;


That makes MySQL using Database "TEST".

Then i entered a simple create table statement:

Code: Select all
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);


MySQL replies with "OK" etc.; finally i logged out via "QUIT" and then i watched my disk for files "Persons.MYD" etc. - nothing!
Nobbie
 
Posts: 10607
Joined: 09. March 2008 13:04

Re: Why does InnoDB create MYD and MYI files?

Postby spookybathtub » 21. June 2018 23:03

Ah ok! My mistake, these tables are actually using MyISAM. When I enter show engines, it says that InnoDB is the default, but I guess my application is overriding that and creating MyISAM tables anyway. Thanks for the advice.
spookybathtub
 
Posts: 17
Joined: 02. June 2013 04:19
XAMPP version: 5.6.33
Operating System: 10.13.4


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 85 guests