From: Benjamin Pflugmann
Date: September 4 2001 10:12pm
Subject: Re: adding INSERT support to MERGE tables
List-Archive: http://lists.mysql.com/internals/1671
Message-Id: <20010905001210.K24343@spin.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hello.

On Sat, Sep 01, 2001 at 12:15:05PM +0300, monty@stripped wrote:
[...]
> One good solution would be to do this the following way:
> 
> add the function:
> 
> myrg_write(MYRG_INFO *file, char *record, enum_ha_write_place where_to_write)
> 
> to the myisammrg interface and then MySQL specify in
> ha_myisammrg::write_row() the value of 'where_to_write' based on
> either the SQL command or from information stored in the .frm file.

I see.

Meanwhile, I have a running version where /myisammrg/ handles all the
magic and where_to_write is passed via the MYRG_INFO structure.
Mainly, because I did not know where to place the info in the .frm
file, so I placed it in the .MRG file, like this:

#INSERT_METHOD=FIRST

So it's a comment and wouldn't disturb older versions.

However I would like it to rewrite it to use the .frm file and just
need a pointer where to place the information. I think this is in
sql/table.cc:openfrm() and create_frm() just like e.g. raid_type?


I have another problem. My implementation works fine and passes all
existing tests. But it fails on the following test with ALTER TABLE:

drop table if exists t1,t2,t3;
create table t1 (a int not null, b int not null, key(a,b));
create table t2 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1) INSERT_METHOD=FIRST;
insert into t1 values (1,1);
insert into t2 values (2,1);
select * from t1;
select * from t2;
show create table t2;
alter table t2 INSERT_METHOD=LAST;
show create table t2;
insert into t1 values (1,2);
insert into t2 values (2,2);
select * from t2;
select * from t1;
flush table t1;
select * from t1;

"select * from t1" shows one row missing and after the flush table, it
magically appears. A blind guess would be some problem with obsolete
caches, but I don't have a clue where to start to look.

I would be glad for any pointer how to resolve this. If you need to
see the patches for this, please tell so. I didn't include them, as I
didn't found time yet to make a clean diff.

And here the complete test with results:


mysql> drop table if exists t1,t2,t3;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int not null, b int not null, key(a,b));
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1) INSERT_METHOD=FIRST;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values (2,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 1 |
+---+---+
2 rows in set (0.00 sec)

mysql> select * from t2;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 1 |
+---+---+
2 rows in set (0.00 sec)

mysql> show create table t2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` int(11) NOT NULL default '0',
  `b` int(11) NOT NULL default '0',
  KEY `a` (`a`,`b`)
) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1) |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table t2 INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` int(11) NOT NULL default '0',
  `b` int(11) NOT NULL default '0',
  KEY `a` (`a`,`b`)
) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1) |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values (2,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
+---+---+
4 rows in set (0.00 sec)

mysql> select * from t1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
+---+---+
3 rows in set (0.00 sec)

mysql> flush table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
+---+---+
4 rows in set (0.01 sec)