MySQL Bugs: #16802: ERROR 1017 (HY000): Can't find file: 'tablename' (errno: 2)

Bug #16802	ERROR 1017 (HY000): Can't find file: 'tablename' (errno: 2)
Submitted:	26 Jan 2006 10:53	Modified:	10 May 2006 10:39
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22-BK, 5.0.18	OS:	Linux (Linux, Windows)
Assigned to:		CPU Architecture:	Any

Description:
Merge tables don't go.

I view old bugs on Mysql Server 4 and my bugs is the same.

Server write:

ERROR 1017 (HY000): Can't find file: 'bg' (errno: 2)

on select in table

How to repeat:
create bg_2005(int a, int b);
create bg_2006(int a, int b);

create bg(int a, int b)ENGINE= MERGE UNION(bg_2005,bg_2006);

select * from bg;

ERROR 1017 (HY000): Can't find file: 'bg' (errno: 2)

Thank you for a problem report. Sorry, but even after correcting improper syntax I was not able to repeat on latest 4.1.18-BK and 5.0.19-BK builds on Linux:

[openxs@Fedora 4.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bg_2005(int a, int b);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int a, int b)' at line 1
mysql> create table bg_2005(a int, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table bg_2006(a int, b int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table bg(a int, b int)ENGINE= MERGE UNION(bg_2005,bg_2006);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from bg;
Empty set (0.03 sec)

mysql> exit
Bye
[openxs@Fedora 4.1]$ bin/mysqladmin -uroot shutdown
STOPPING server from pid file /home/openxs/dbs/4.1/var/Fedora.pid
060126 13:50:51  mysqld ended

[1]+  Done                    bin/mysqld_safe
[openxs@Fedora 4.1]$ cd ../5.0/
[openxs@Fedora 5.0]$ bin/mysqld_safe &
[1] 32178
[openxs@Fedora 5.0]$ Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bg_2005(int a, int b);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int a, int b)' at line 1
mysql> create table bg_2005(a int, b int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table bg_2006(a int, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table bg(a int, b int)ENGINE= MERGE UNION(bg_2005,bg_2006);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from bg;
Empty set (0.00 sec)

On Windows also with 5.0.18 can't repeat:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bg_2006(a int, b int);
Query OK, 0 rows affected (0.22 sec)

mysql> create table bg_2005(a int, b int);
Query OK, 0 rows affected (0.19 sec)

mysql> create table bg(a int, b int)ENGINE= MERGE UNION(bg_2005,bg_2006);
Query OK, 0 rows affected (0.19 sec)

mysql> select * from bg;
Empty set (0.16 sec)

Hi, I have  server version: 5.0.18, if I make a query like this:

select [whatever] from table1 a, table2 b;

It returns: ERROR 1017: Can't find file: 'b' (errno: 2)

The query worked in the previous server version 4.x.
If I am using just one alias in query:

 select [whatever] from table1 a;

the query returns correctly.

To Szasz Attila:

Sorry, I am not able to repeat with 5.0.18. Look:

mysql> show tables like 'tt%';
+----------------------+
| Tables_in_test (tt%) |
+----------------------+
| tt1                  |
| tt2                  |
| tt3                  |
+----------------------+
3 rows in set (0.00 sec)

mysql> select * from tt1 a, tt2 b;
Empty set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.18-nt |
+-----------+
1 row in set (0.02 sec)

Sorry, I didn't checked the OS... here is Windows, I am using Linux (FC5). Perhaps someone can move my comment to the right place? I cannot find the Linux section.

I have the same problem on windows xp, mysql 5.0.18-nt

mysql> create table det (d date,dt datetime,t timestamp);
Query OK, 0 rows affected (0.17 sec)
mysql> insert into det values(now(),now(),now());
Query OK, 1 row affected (0.08 sec)
mysql> insert into det values(now(),now(),now());
Query OK, 1 row affected (0.05 sec)

mysql> create table det2 (d date, dt timestamp, t timestamp);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into det2 values(now(),now(),now());
Query OK, 1 row affected (0.05 sec)
mysql> insert into det2 values(now(),now(),now());
Query OK, 1 row affected (0.05 sec)

mysql> create table dets(d date, dt timestamp, t timestamp) engine=merge union=(det,det2);
Query OK, 0 rows affected (0.09 sec)

mysql> select * from dets;
ERROR 1017 (HY000): Can't find file: 'dets' (errno: 2)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| 1              |
| aha            |
| det            |
| det2           |
| dets           |
| two aha's      |
+----------------+
6 rows in set (0.00 sec)

mysql> create table det3 like det2;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into det3 select * from det2;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table dett(d date, dt timestamp, t timestamp) engine=merge union=(det3,det2) insert_method=last;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from dett;
ERROR 1017 (HY000): Can't find file: 'dett' (errno: 2)

Please note that table `det`'s dt column is datetime and the two other tables is timestamp.

As addition to my above post, I was able to repeat the error on FreeBSD 6.1 PRERELEASE, mysql 4.1.18 using the same steps I took for the first two tables (det, det2).

mysql> create table dd(d date, dt timestamp, t timestamp) engine=merge union=(d1,d2);
Query OK, 0 rows affected (0.04 sec)

mysql> select * from dd;
ERROR 1016 (HY000): Can't open file: 'dd.MRG' (errno: 143)
mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.18    |
+-----------+
1 row in set (0.01 sec)

It is still not a bug, because, according to the manual (http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html):

"The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information. You cannot merge MyISAM tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order."

Sorry for not testing the 2nd and 3rd table (same table specs) merge on FreeBSD. It works and I am able to retrieve info (select *).
But the one on Windows remains the same. It still says the same error 1017. I re-tested on Windows using the same lines I used on BSD that worked.

And for a change,

mysql> create table s1 (id int);
Query OK, 0 rows affected (0.13 sec)
mysql> create table s2 (id int);
Query OK, 0 rows affected (0.09 sec)
mysql> create table s3 (id int) engine=merge union=(s1,s2);
Query OK, 0 rows affected (0.05 sec)
mysql> select * from s3;
ERROR 1017 (HY000): Can't find file: 's3' (errno: 2)

...winxp

I've found my problem and it could have been the case for the submitter of this bug report. I had my tables default to innodb. Specifying engine=myisam on creation or altering the table to engine=myisam makes Merge work as expected. And it doesn't matter if the merge table has already been created.

I did the same on the first two tables that was not identical and I'm pleased with the helpful error message saying,
ERROR 1168 (HY000): All tables in the MERGE table are not identically defined

Hello Everyone,

To migrate my database faster, I tried copying the raw files (MYD and MYI) files of a database into another machine. All the tables are working fine except two tables that were partitioned. My directory structure looks like this:

table1.frm
table1.MYI
table1.MYD

table2.frm
table2.par
table2#P#p0.MYD
table2#P#p0.MYI
table2#P#p1.MYD
table2#P#p1.MYI

table3.frm
table3.par
table3#P#p0.MYD
table3#P#p0.MYI
table3#P#p1.MYD
table3#P#p1.MYI

The following is producing an error:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| test               | 
+--------------------+
3 rows in set (0.06 sec)

mysql> use test;
Database changed

mysql> show tables;
+---------------------------+
| Tables_in_test            |
+---------------------------+
| table1                    | 
| table2                    | 
| table3                    | 
+---------------------------+
3 rows in set (0.00 sec)

mysql> explain table1;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment | 
| a     | int(11) | YES  |     | NULL    |                | 
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> explain table2;
ERROR 1017 (HY000): Can't find file: 'table2' (errno: 2)

mysql> explain table3;
ERROR 1017 (HY000): Can't find file: 'table3' (errno: 2)

mysql> check TABLE table2;
+--------------------------+-------+----------+--------------------------------------------------+
| Table                    | Op    | Msg_type | Msg_text                                         |
+--------------------------+-------+----------+--------------------------------------------------+
| test.table2              | check | Error    | Can't find file: 'table2' (errno: 2)             | 
| test.table2              | check | error    | Corrupt                                          | 
+--------------------------+-------+----------+--------------------------------------------------+
2 rows in set (0.00 sec)

I checked the permissions and everything looks fine. I tried repair but that didn't seem to work either. Is there anything that can be done?