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.
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
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
