Bug #5390 problems with merge tables
Submitted: 3 Sep 2004 14:58 Modified: 18 Jan 2006 19:12
Reporter: Ramil Kalimullin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0 OS:FreeBSD (FreeBSD 5.1-RELEASE)
Assigned to: Ingo Strüwing CPU Architecture:Any

[3 Sep 2004 14: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 8: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 1: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 20: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 17: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 11: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 10:32] Ingo Strüwing
This should also fix Bug #9686.
[29 Nov 2005 18: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 15: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 18: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 14: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 15: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 12: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 12:44] Ingo Strüwing
Pushed to 4.0.27, 4.1.17, 5.0.19, and 5.1.5.
[6 Jan 2006 15: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 11: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 14: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 19: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 15: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 18: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 11: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 14: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 17: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 12: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 19: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.