Bug #5390 problems with merge tables
Submitted: 3 Sep 2004 16:58 Modified: 18 Jan 2006 20:12
Reporter: Ramil Kalimullin
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:4.0 OS:FreeBSD (FreeBSD 5.1-RELEASE)
Assigned to: Ingo Strüwing Target Version:

[3 Sep 2004 16:58] Ramil Kalimullin 
Description:
Run the following queries:

How to repeat:
drop table if exists t1, t2, t3;
create table t1(a int);
create table t2(a int);
insert into t1 values (0), (1);
insert into t2 values (2);
create table t3 (a int) engine=merge union=(t1, t2) insert_method=first;

insert t3 select * from t1;
insert t1 select * from t3;
create table if not exists t1 select * from t3;
[8 Sep 2004 10:17] Ramil Kalimullin 
What I got is:

mysql> insert t3 select * from t1; 
ERROR 1030 (00000): Got error 127 from storage engine

mysql> insert t1 select * from t3;
ERROR 1030 (00000): Got error 127 from storage engine

mysql> create table if not exists t1 select * from t3;
this query just hangs;  mysqladmin displays 'Locked' status for the thread.
[2 Oct 2004 3:42] Matthew Lord 
I was able to repeat this using 4.1.5-gamma on linux: 2.4.21 #12 SMP i386 i686

I was also able to repeat this using 4.0.20 on windows 2000 smp.

When I say repeated, I mean that I got the same results as Ramil had.

Best Regards
[19 Oct 2004 22:41] Ingo Strüwing 
At the beginning of INSERT SELECT, a check is done, if the destination table is the same
as one of the source tables. In that case the select option SQL_BUFFER_RESULT is
implicitly added. This means that the select result is put into a temporary table first.

In the present case, the check fails, because some tables are hidden below MERGE tables.
The check does not descend into MERGE and does not check the underlying tables. It is
possible, to fix this for INSERT SELECT. After all tables are opened, we can access the
underlying tables and check them for duplicates.

The case CREATE TABLE IF NOT EXISTS SELECT is more tricky. The table is opened (and
locked!) in a function, where the other tables are not accessible. The thread waits for a
write lock to become available, while it holds a read lock on the same table. A possible
solution might be to convert the read lock into a write lock. But this might easily
become an incubator for other deadlocks. And it does not solve the problem, in which
point of operation to check for the MERGE tables. Another solution might be to test for
the existence of the table in advance and convert the query into an INSERT SELECT, which
is already solved in principle. This is a design decision too. I need advice.
[27 Jun 2005 19:55] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26457
[29 Sep 2005 13:17] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30502
[18 Oct 2005 12:32] Ingo Strüwing 
This should also fix Bug #9686.
[29 Nov 2005 19:17] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32835
[6 Dec 2005 16:53] Sergei Golubchik 
It happens out that "insert ... select" and "create ... select" queries expose two
different problems, that require different fixes. This bug deals only with "insert ...
select" part. See bug#15522 for the  "create ... select" part.
[7 Dec 2005 19:54] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/33157
[19 Dec 2005 15:51] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/254
[20 Dec 2005 16:35] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/284
[22 Dec 2005 13:52] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/363
[28 Dec 2005 13:44] Ingo Strüwing 
Pushed to 4.0.27, 4.1.17, 5.0.19, and 5.1.5.
[6 Jan 2006 16:40] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/704
[10 Jan 2006 12:47] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/849
[18 Jan 2006 15:43] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1269
[18 Jan 2006 20:12] Mike Hillyer 
Added to changelogs:

   <listitem>
        <para>
          An <literal>INSERT ... SELECT</literal> statement between
          tables in a <literal>MERGE</literal> set can return errors
          when statement involves insert into child table from merge
          table or vice-versa. (Bug #5390)
        </para>
      </listitem>
[23 Jan 2006 16:40] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1507
[23 Jan 2006 19:12] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1516
[20 Feb 2006 12:28] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2901
[20 Feb 2006 15:24] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2915
[20 Feb 2006 18:05] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2925
[5 Apr 2006 14:39] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4498
[11 Apr 2006 21:05] Ingo Strüwing 
The performance improvement, as proposed and basically implemented by Monty is pushed to
4.0.27, 4.1.19, 5.0.21, 5.1.10.