Hi!
>>>>> "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes:
Peter> Hello monty,
Peter> I'm trying to use merge table for logging - to have a possibility to
Peter> Rotate log files and to quickly delete old data - for this I'm goint
Peter> to setup a number of merge tables where each contains data for one
Peter> day, and the inserts are done to the last table using real table
Peter> name, therefore the select runs on merged table to cover all data.
Peter> Currently I found the followning problem which may lead to the
Peter> problems:
mysql> create table t1 (val char(10));
Peter> Query OK, 0 rows affected (0.00 sec)
mysql> create table t2 (val char(10));
Peter> Query OK, 0 rows affected (0.00 sec)
mysql> create table test (val char(10)) type=merge union=(t1,t2);
Peter> Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values("a");
Peter> Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values("b");
Peter> Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
Peter> +------+
Peter> | val |
Peter> +------+
Peter> | a |
Peter> | b |
Peter> +------+
Peter> 2 rows in set (0.00 sec)
Peter> as you see the result is correct and merge table reflects all changes:
mysql> insert into t2 values("b");
Peter> Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
Peter> +------+
Peter> | val |
Peter> +------+
Peter> | a |
Peter> | b |
Peter> | b |
Peter> +------+
Peter> 3 rows in set (0.00 sec)
Peter> Dublicates are also wellcome.
Peter> Let's add the key:
mysql> alter table t2 add key(val);
Peter> Query OK, 2 rows affected (0.00 sec)
Peter> Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t2 values("b");
Peter> Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
Peter> +------+
Peter> | val |
Peter> +------+
Peter> | a |
Peter> | b |
Peter> | b |
Peter> +------+
Peter> 3 rows in set (0.00 sec)
Peter> As you see the're starting to get incorrect result. The same thing
Peter> will be if I'll insert other different rows.
This is because the MERGE table is still mapped to the original table.
Bascily MERGE tables is not to be used if you are going to DROP or
ALTER a table that is mapped.
Peter> The only thing to fix this is to flush table test;
Peter> Other thing which also seems to be strange:
mysql> delete from t1;
Peter> Query OK, 0 rows affected (0.00 sec)
mysql> delete from t2;
Peter> Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from test;
Peter> +----------+
Peter> | count(*) |
Peter> +----------+
Peter> | 2 |
Peter> +----------+
Peter> 1 row in set (0.00 sec)
This may see strange, but it isn't.
The problem is that DELETE FROM table (without a WHERE) is currently
mapped to DROP TABLE / CREATE TABLE, in which case you get exactly the
same problem.
In 4.0, we will change that TRUNCATE TABLE will be mapped to DROP +
CREATE while DELETE FROM table (without a WHERE) will be safe.
<cut>
Peter> The last thing is unrepeatable. But I got this once during the tests.
Peter> Other tests show even more strange ting (this seems not to be key
Peter> related):
mysql> alter table t2 drop key val;
Peter> Query OK, 0 rows affected (0.00 sec)
Peter> Records: 0 Duplicates: 0 Warnings: 0
<cut>
Same thing here; ALTER TABLE will produce unexpected results.
I have now updated the documentation to warn about using ALTER TABLE,
DROP TABLE or DELETE without a WHERE on a mapped table.
Regards,
Monty
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
