Re: MERGE table problem
Eric Anderson wrote: I've got a master (Master) with a MERGE table of foo_t (comprising of bar_a, bar_b, bar_c) in database 'Igloo'. There are 5 slaves that replicate the Igloo table, but ignore the Igloo.foo_t table. This setup was working fine. If the Master server crashed or had a prblem for some reason, I could always: STOP SLAVE RESET SLAVE FLUSH TABLES LOAD DATA FROM MASTER START SLAVE The Master's motherboard failed last night, and after getting the server back up, replication seems to be broken. The LOAD DATA FROM MASTER command fails on ALL slaves with: mysql> load data from master\g ERROR 1188 (HY000): Error from master: 'Can't find file: 'foo_t.MRG' (errno: 2)' mysql> $ perror 2 System error: 2 = No such file or directory The foo_t.MRG file doesn't exist. Not sure why that is (don't have any experience with replication), anyone else have some suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MERGE table problem
I've got a master (Master) with a MERGE table of foo_t (comprising of bar_a, bar_b, bar_c) in database 'Igloo'. There are 5 slaves that replicate the Igloo table, but ignore the Igloo.foo_t table. This setup was working fine. If the Master server crashed or had a prblem for some reason, I could always: STOP SLAVE RESET SLAVE FLUSH TABLES LOAD DATA FROM MASTER START SLAVE The Master's motherboard failed last night, and after getting the server back up, replication seems to be broken. The LOAD DATA FROM MASTER command fails on ALL slaves with: mysql> load data from master\g ERROR 1188 (HY000): Error from master: 'Can't find file: 'foo_t.MRG' (errno: 2)' mysql> I've myisamchk'd all the tables on the Master server. I've dropped the 'foo_t' table and recreated it. Still get the above error on ALL slaves.. It seems to me there's definitely a problem on the Master server but I'm just not sure what it is...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
"MERGE Table Problem"
Hello *
reproducable Problem:
Content of UNION:
logs_20040608,logs_20040609,logs_20040611,logs_20040612,logs_20040613,logs_20040614,
logs_20040615
results: 0E0
DBD::mysql::db do failed: Can't open file: '#sql-13c1_12.MRG'. (errno: 144) at
/usr/local/sbin/new_MERGE_table.pl line 276.
Unable to ALTER TABLE logs UNION=(
logs_20040608,logs_20040609,logs_20040611,logs_20040612,logs_20040613,logs_20040614,
logs_20040615 ) INSERT_METHOD=LAST: !Can't open file: '#sql-13c1_12.MRG'. (errno: 144)
Can anyone see a conceptional misunderstandig, i make ?
in pseudocode do the following using perl dbi:
1. FLUSH TABLES
# take out all "old tables from merge in order to compress the most recent
2. ALTER TABLE logs UNION (log_"today) INSERT_METHOD=LAST;
3. FLUSH TABLES; # make mysql use the new merge table
3. mysiampack / mysiamcheck (log_yesterday)
4. FLUSH TABLES # just in case
5. ALTER TABLE logs UNION ( last_tenlogs, log_today) INSERT_METHOD=LAST;
<- here the problem arises, i assume triggered by "yesterdays table" in the list of
last_tenlogs
>@commands = ( "FLUSH TABLES", "ALTER TABLE $LOGTABLE UNION=( $COMMA_TABLE_SET
>$newTABLE ) INSERT_METHOD=LAST");
>for ( @commands){
>$results=$dbh->do($_) or die "Unable to $_: !" . $dbh->errstr . "\n";
(COMMA_TABLE_SET is a list of commaseparated table names, ending with a comma)
TIA
Micha
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge table problem.
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.
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
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
Merge table problem.
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.
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
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
Re: Merge table problem.
On Fri, Feb 16, 2001 at 05:58:27PM +0300, Peter Zaitsev wrote: > 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. > Great. I'm trying something similar. > 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 > Merge tables should have the same definition as the tables underneath. I believe that this includes having identical indexes. Maybe someone can confirm this? Here you have just made one of the tables underneath different from the other and from the merge table. You may want to read the discussion in the MySQL bugs list about merge tables. There are two threads but the discussion shifted into one of them. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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
Merge table problem.
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
