MySQL Bugs: #18338: ARCHIVE engine, problems inserting into itself when large

Bug #18338	ARCHIVE engine, problems inserting into itself when large
Submitted:	18 Mar 2006 22:00	Modified:	15 May 2006 16:26
Reporter:	Josh Chamas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.16/4.1BK	OS:	Linux (Linux 2.4.21-32.0.1.EL #1 SMP)
Assigned to:	Brian Aker	CPU Architecture:	Any

Description:
Error when inserting into a table using the ARCHIVE engine when the table is large and inserting into itself.

How to repeat:
Create a large ARCHIVE table, insert into itself, and at some point you get this error.  On the system I was working on, the table was about 1.8GB in size when I hit this error.

# this shows the error
mysql> insert into Click_query_archive select * from Click_query_archive limit 100000;
ERROR 1114 (HY000): The table '' is full

# this shows no problem when inserting from non-ARCHIVE table
mysql> insert into Click_query_archive select * from Click_query limit 100000;
Query OK, 100000 rows affected (10.97 sec)
Records: 100000  Duplicates: 0  Warnings: 0

# this shows the size of the tables
# du -sh /var/lib/mysql/test/*archive*
4.0K    /var/lib/mysql/test/Click_query_archive.ARM
2.2G    /var/lib/mysql/test/Click_query_archive.ARZ
12K     /var/lib/mysql/test/Click_query_archive.frm

# here is the version of MySQL being used
mysql> show variables like '%version%';
+-------------------------+------------------------------------------+
| Variable_name           | Value                                    |
+-------------------------+------------------------------------------+
| protocol_version        | 10                                       |
| version                 | 4.1.16-standard-log                      |
| version_comment         | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | x86_64                                   |
| version_compile_os      | unknown-linux-gnu                        |
+-------------------------+------------------------------------------+
5 rows in set (0.00 sec)

# The following queries show that an insert does really seem to work
mysql> select count(*) from Click_query_archive;
+----------+
| count(*) |
+----------+
| 13020003 |
+----------+
1 row in set (1 min 18.97 sec)

mysql> insert into Click_query_archive select * from Click_query limit 100000;
Query OK, 100000 rows affected (11.46 sec)
Records: 100000  Duplicates: 0  Warnings: 0

mysql> select count(*) from Click_query_archive;
+----------+
| count(*) |
+----------+
| 13120003 |
+----------+
1 row in set (1 min 17.14 sec)

Thank you for the bug report.

mysql> insert into tb_arch1 select * from tb_arch1;       
ERROR 1114 (HY000): The table '' is full
mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.19-debug-log |
+------------------+
1 row in set (0.00 sec)

This looks to be an issue with the MyISAM temp table that is created during the INSERT/SELECT.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".