MySQL ERROR 1017 (HY000): Can't find file: './"DatabaseName/Table.frm' (errno: 13)

Question

Can you create a copy of a table by simply making a duplicate of .frm file? I intend to do something like this:

Suppose my database's name is mydb and it has a table called mytab. Browse to the folder named mydb in the file system and it has a mytab.frm file. Then copy the contents of mytab.frm into a file called copy.frm Then i login to mysql and run the following commands:

use mydb;  //Selects the database mydb
show tables; //To see the list of tables. I can see the table named copy.
select * from copy; //This throws the error mentioned in the title.

So what am I missing? What files do you copy to take the backup of a database? I know that table can be copied by a couple of sql statements But i want to learn something knew so I am experimenting around. Thanks! :)

Duplicated. Have a look at this question with the right answer: http://stackoverflow.com/questions/2415855/run-mysql-create-table-by-select-another-and-copied-the-indexes-automatically — itinance, Apr 11 '15 at 07:02
@itinance It''s not a duplicate. I am not using sql commands to create a copy. I am copying a file into another in file system to create a copy of the table. — Sonia Saxena, Apr 11 '15 at 07:04
errno 13 is permission denied. Check the permissions. And, also, stop doing that. Use sql commands or mysql utilities to copy tables. — Mat, Apr 11 '15 at 07:05
here's a better idea. `create table copy as select * from mytab` — pala_, Apr 11 '15 at 07:20
`CREATE TABLE AS SELECT` is better than hacking files but it does not produce an exact copy of the table. The field types are inferred (`CHAR` and `VARCHAR` fields are shrunk to the minimum, null-able and`DEFAULT` properties are lost and so on) and the new table does not have any index. Use `CREATE TABLE LIKE` instead. — axiac, Apr 11 '15 at 09:48

pala_ · Accepted Answer · 2015-04-11T08:08:48.147

Just throwing this down as an answer:

You can't do what you have tried for InnoDB tables. InnoDB stores all table data in a single file - ibdata. You can modify this with the innodb_file_per_table setting in my.cnf, but it isn't retroactive, it will only apply to new tables. Even if you DO have file per table setup, you still shouldnt try and just copy the data files, because innodb may not have flushed all changes from the ib_logfile's to the ibdata / .ibd file, so you could well end up with corrupt data.

You can do it for MyISAM tables but you shouldn't (and there are also other files that to be copied as well, as the .FRM is only the table definition. The .MYD file contains the data and the .MYI file contains the indexes). Why? because you are entrusting your data to a database, you should be using database tools to duplicate it. The only time you should be touching the data files directly is during data recovery, and only when the server is not running - you dont want to be copying the files as they're being written to.

To duplicate a table, simply do this:

create table new_table as select * from old_table

To backup an entire database, use mysqldump or one of the other available backup tools.

There you go. That's got to be the strangest downvote i've ever seen. — pala_, Apr 11 '15 at 08:07
This answers clears everything up! Great! Thanks. I don't know who downvoted you but I upvote your answer and select it as my solution! :D — Sonia Saxena, Apr 11 '15 at 08:51
`CREATE TABLE AS SELECT` does not create an exact copy of the table. Only the field names of the new table are copied from the old table (in fact, they are copied from the resultset produced by `SELECT`); the types of the fields are roughly the same as on the source table but the details (signed/unsigned, length, null-able, default value) are not copied (because they do not appear in the source result set). No indexes are created for the new table. — axiac, Apr 11 '15 at 09:59

axiac · Answer 2 · 2015-04-11T10:00:31.137

TL;DR

Copying/moving MySQL tables by altering the underlying files is possible in some conditions but it is highly unrecommended.

Always use MySQL commands to do it.

The .frm file contains only the table definition. The data and the indexes are stored in other files and they depend on the storage engine of the table.

Several excerpts from the official documentation:

`MyISAM`

15.2 The MyISAM Storage Engine

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

`InnoDB`

14.1 Introduction to InnoDB

By default, with the innodb_file_per_table setting enabled, each new InnoDB table and its associated indexes are stored in a separate file. When the innodb_file_per_table option is disabled, InnoDB stores all its tables and indexes in the single system tablespace, which may consist of several files (or raw disk partitions).

14.2.15.1 Role of the .frm File for InnoDB Tables

MySQL stores its data dictionary information for tables in .frm files in database directories. Unlike other MySQL storage engines, InnoDB also encodes information about the table in its own internal data dictionary inside the tablespace. When MySQL drops a table or a database, it deletes one or more .frm files as well as the corresponding entries inside the InnoDB data dictionary. You cannot move InnoDB tables between databases simply by moving the .frm files.

14.12 InnoDB Startup Options and System Variables

innodb_file_per_table

When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table in a separate .ibd file, rather than in the system tablespace.

MySQL Glossary

system tablespace

One or more data files (ibdata files) containing the metadata for InnoDB-related objects (the data dictionary), and the storage areas for the undo log, the change buffer, and the doublewrite buffer. Depending on the setting of the innodb_file_per_table, when tables are created, it might also contain table and index data for some or all InnoDB tables. The data and metadata in the system tablespace apply to all the databases in a MySQL instance.

Prior to MySQL 5.6.7, the default was to keep all InnoDB tables and indexes inside the system tablespace, (...) In MySQL 5.6.7 and higher, the default is file-per-table mode, where each table and its associated indexes are stored in a separate .ibd file.

Let's draw some (partial) conclusion

Before anything else you have to stop the MySQL server (to be sure all the data is safely stored into files).

If the table you want to copy uses the MyISAM engine then you need to copy/rename the .frm, .MYD and .MYI files having the same name as the table.

If the table uses the InnoDB engine and at the moment when it was created the innodb_file_per_table setting was ON then you need to copy/rename the .frm and .ibd files having the same name as the table.

If the table uses the InnoDB engine and it was created while the innodb_file_per_table setting was OFF then you cannot copy or move the table data from outside MySQL.

If the table uses the MEMORY table then it's enough to copy the .frm file and restart the server. The table data and indexes are stored in memory, there is no file for them and the source table will be empty after the server restart, so you get an exact copy of an empty table ;-)

But wait, there is more!

MySQL implements several other storage engines that are probably less used than the ones mentioned above. Each of them has its own rules of storing the data in files.

And more

If the server you want to hack this way is part of a replication cluster the changes you do either are ignored (do not propagate to the other servers in the cluster if you change a slave server) or break the replication (the slave servers are required to query and update a table they don't have, if you change the master server).

The conclusion

Even if, in certain conditions, copying or moving a table by changing the underline files is possible, it is strongly not recommended.

The correct (and many times the only) way to copy a table is to use the commands provided by MySQL.

13.1.14 CREATE TABLE Syntax

Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:

CREATE TABLE new_tbl LIKE orig_tbl;

The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table.

You can then use:

INSERT INTO `new_tbl` SELECT * FROM `orig_tbl`

to copy the data.

Another way

An alternative way to copy a table without writing SQL commands is to export the table definition and data using mysqldump, open the export file in a text editor, change the table name in all the places where it appears, save the file and import it into the database using the mysql command line tool (or other MySQL client).

score -1 · Answer 3 · answered Apr 11 '15 at 07:19

-1

You have to copy 3 files: copy.frm copy.MYD copy.MYI
Make privileges for files, owner and group
chown mysql.mysql copy.*
chmod 660 copy.*
and refresh tables in mysql :
mysql DATABASE
mysql> flush tables;

and voila!

answered Apr 11 '15 at 07:19

badjack

1

unless its an innodb table. – pala_ Apr 11 '15 at 07:19
I think innodb use one file for database – badjack Apr 11 '15 at 07:22
@badjack There are no .MYD or .MYI files. It's an innodb table. There is a .opt file anyhow. Can you tell me what exactly are the contents of .MYD and .MYI files and why should I be copying them? – Sonia Saxena Apr 11 '15 at 07:28
MYD is the table data for a MyISAM table, MYI is the index file for a MyISAM table. FRM is the table definition file for both. Innodb defaults to all tables in a single file, but there's a db setting to change it to one file per table. It isn't retroactive. The only way to do what you want with an innodb table is with my comment against your original question. – pala_ Apr 11 '15 at 07:39
@pala_ I was just experimenting around so I wanted to avoid using sql statements! But thanks for the info. :) – Sonia Saxena Apr 11 '15 at 07:42
@SoniaSaxena what os are you using for mysql? – Altmish-E-Azam Apr 11 '15 at 07:43
The SQL is the correct way (and many times the only way) to do it. – axiac Apr 11 '15 at 07:44
@axiac Hahaha...sounds deep! :P – Sonia Saxena Apr 11 '15 at 07:46
@MiyaG I use ubuntu 12.04. I haven't upgraded my OS yet. The latest version is ubuntu 14.10 or something. – Sonia Saxena Apr 11 '15 at 07:47
@SoniaSaxena as in your question `Can you create a copy of a table by simply making a duplicate of .frm file? I intend to do something like this:` yes you can create a duplicate table with copying of `.frm, .MYI, .MYD' in MyISAM db. – Altmish-E-Azam Apr 11 '15 at 07:49
@MiyaG But I get the error as mentioned in the title when I just copy the .frm file! Could it be an issue with permissions? – Sonia Saxena Apr 11 '15 at 07:54
@SoniaSaxena... what method are you using to create duplicate table.. e.g. mysql command OR directly from database directory? – Altmish-E-Azam Apr 11 '15 at 07:57