Hello monty,
I'm trying to use merge table for logging - to have a possibility to
Rotate log files and to quickly delete old data - for this I'm goint
to setup a number of merge tables where each contains data for one
day, and the inserts are done to the last table using real table
name, therefore the select runs on merged table to cover all data.
Currently I found the followning problem which may lead to the
problems:
mysql> create table t1 (val char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> create table t2 (val char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> create table test (val char(10)) type=merge union=(t1,t2);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values("a");
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values("b");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+
| val |
+------+
| a |
| b |
+------+
2 rows in set (0.00 sec)
mysql>
as you see the result is correct and merge table reflects all changes:
mysql> insert into t2 values("b");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+
| val |
+------+
| a |
| b |
| b |
+------+
3 rows in set (0.00 sec)
Dublicates are also wellcome.
Let's add the key:
mysql> alter table t2 add key(val);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t2 values("b");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+
| val |
+------+
| a |
| b |
| b |
+------+
3 rows in set (0.00 sec)
As you see the're starting to get incorrect result. The same thing
will be if I'll insert other different rows.
The only thing to fix this is to flush table test;
Other thing which also seems to be strange:
mysql> delete from t1;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t2;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> select * from test;
+------+
| val |
+------+
| zzz |
| zzz |
+------+
2 rows in set (0.00 sec)
The last thing is unrepeatable. But I got this once during the tests.
Other tests show even more strange ting (this seems not to be key
related):
mysql> alter table t2 drop key val;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t1 values("zzz");
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values("zzz");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+
| val |
+------+
| zzz |
+------+
1 row in set (0.00 sec)
mysql>
--
Best regards,
Peter mailto:[EMAIL PROTECTED]
---------------------------------------------------------------------
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
