MySQL 4.0.2, 4.0.3,4.0.4 replication circle broken.
Hello Victoria, As far as I see bug of muliple log files still not fixed regardless of last change in 4.0.4: 'FLUSH LOGS removed numerical extension for all future update logs.' -- Best regards, Artem mailto:[EMAIL PROTECTED] sql query - 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: MySQL 4.0.2, 4.0.3 replication circle broken.
Hello Victoria, Thursday, August 15, 2002, you wrote: AVR In case of repliacation circle (A-B-A) query 'flush log' on any of AVR this servers create endless loop, with creating new binary log file AVR on each iteration. (about 30 new files every second in my case) Thanks for bug report! I tested it yesterday and also have got thousands of log files :( 4.0.3 Already out, but bug is still exists. Does anybody something to fix this bus? -- Best regards, Artem 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
MySQL 4.0.2, 4.0.3 replication circle broken.
Hello mysql, In case of repliacation circle (A-B-A) query 'flush log' on any of this servers create endless loop, with creating new binary log file on each iteration. (about 30 new files every second in my case) -- Best regards, Artem 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
Re: MySQL 4.0.2 replication circle broken?
Hello all, I was check up 4.0.3 (from the Development Source Tree), bug still exist. Description: aor I use replication circle (A-B, B-A) with 3.23.51 - all ok. aor But I got problem, when upgrade both my servers to 4.0.2. aor Mysqld create 30 new bin-log files every second at both servers: aor --- aor [www2:/opt/mysql-dev/var]:1303# ls -1 www2-bin.* | wc -l aor2268 aor [www2:/opt/mysql-dev/var]:1306# l www2-bin.00* aor -rw-rw1 mysqlmysql 137 Aug 12 16:31 www2-bin.001 aor -rw-rw1 mysqlmysql 62 Aug 12 16:31 www2-bin.002 aor -rw-rw1 mysqlmysql 62 Aug 12 16:31 www2-bin.003 aor -rw-rw1 mysqlmysql 62 Aug 12 16:31 www2-bin.004 aor -rw-rw1 mysqlmysql 62 Aug 12 16:31 www2-bin.005 aor -rw-rw1 mysqlmysql 62 Aug 12 16:31 www2-bin.006 aor -rw-rw1 mysqlmysql 62 Aug 12 16:31 www2-bin.007 aor -rw-rw1 mysqlmysql 62 Aug 12 16:31 www2-bin.008 aor -rw-rw1 mysqlmysql 62 Aug 12 16:31 www2-bin.009 aor [www2:/opt/mysql-dev/var]:1304# ../bin/mysqlbinlog www2-bin.001 aor # at 4 aor #020812 16:29:30 server id 1 log_pos 4 Start: binlog v 3, server v 4.0. aor 2-alpha-log created 020812 16:29:30 aor # at 79 aor #020812 16:31:13 server id 2 log_pos 79Rotate to www2-bin.002 pos: 4 aor # at 118 aor #020812 16:31:13 server id 1 log_pos 118 Stop aor [www2:/opt/mysql-dev/var]:1305# ../bin/mysqlbinlog www2-bin.002 aor # at 4 aor #020812 16:31:13 server id 2 log_pos 4 Rotate to www2-bin.003 pos: 4 aor # at 43 aor #020812 16:31:13 server id 1 log_pos 43Stop aor [www2:/opt/mysql-dev/var]:1307# ../bin/mysqlbinlog www2-bin.003 aor # at 4 aor #020812 16:31:13 server id 2 log_pos 4 Rotate to www2-bin.004 pos: 4 aor # at 43 aor #020812 16:31:13 server id 1 log_pos 43Stop aor [www2:/opt/mysql-dev/var]:1308# aor -- aor and so on. aor Will be fixed this error in 4.0.3? How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:Artem Ryabov Organization: MySQL support: none Synopsis: too many bin-log files Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.2-alpha (Source distribution) Environment: aor System: Linux www2.osp.ru 2.2.19 #2 Wed Aug 15 16:02:45 MSD 2001 i686 unknown aor Architecture: i686 aor Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc aor GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs aor gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-85) aor Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' aor LIBC: aor lrwxrwxrwx1 root root 11 Nov 25 2000 /lib/libc.so.6 - libc-2.2.so aor -rwxr-xr-x1 root root 5155229 Jan 11 2001 /lib/libc-2.2.so aor -rw-r--r--1 root root 24498288 Jan 11 2001 /usr/lib/libc.a aor -rw-r--r--1 root root 178 Jan 11 2001 /usr/lib/libc.so aor configure command: ./configure --with-mysqld-ldflags=-all-static --disable-shared --prefix=/opt/mysql-dev --with-charset=cp1251 -- Best regards, Artemmailto:[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
Re[2]: Bug or feature fulltext search?
Hello Sergei,
Tuesday, July 23, 2002, 10:17:06 PM, you wrote:
SG Hi!
SG On Jul 23, Artem V. Ryabov wrote:
Hello mysql,
see this 2 queries:
why they not equal?
why first skip many records?
mysql select left(ftText,20) from Txt_index where match(ftText)
against('+(wùd200206*) +wù12p' in boolean mode);
3 rows in set (0.36 sec)
mysql select left(ftText,20) from Txt_index where match(ftText)
against('+wùd200206* +wù12p' in boolean mode);
42 rows in set (8.00 sec)
mysql show variables like 'version';
+---+-+
| Variable_name | Value |
+---+-+
| version | 4.0.2-alpha-log |
+---+-+
1 row in set (0.00 sec)
SG Obviously, it's a bug.
SG Could you provide a test case ?
I tried to use 'REPAIR TABLE Txt_index USE_FRM', this query work _very_ slow:
+--++--++
| Table| Op | Msg_type | Msg_text |
+--++--++
| NewWeb.Txt_index | repair | warning | Number of rows changed from 0 to 43390 |
| NewWeb.Txt_index | repair | status | OK |
+--++--++
2 rows in set (2 days 11 hours 21 min 7.35 se2 rows in set (2
But noting change:
mysql select left(ftText,17) from Txt_index where match(ftText)
against('+(wùd200206*) +wù12p' in boolean mode);
3 rows in set (0.35 sec)
mysql select left(ftText,17) from Txt_index where match(ftText) against('+wùd200206*
+wù12p' in boolean mode);
42 rows in set (4.52 sec)
mysql
SG Regards,
SG Sergei
--
Best regards,
Artemmailto:[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
Re[2]: Bug or feature fulltext search?
Hello Sergei,
Tuesday, July 23, 2002, 10:17:06 PM, you wrote:
SG Hi!
SG On Jul 23, Artem V. Ryabov wrote:
Hello mysql,
see this 2 queries:
why they not equal?
why first skip many records?
mysql select left(ftText,20) from Txt_index where match(ftText)
against('+(wùd200206*) +wù12p' in boolean mode);
3 rows in set (0.36 sec)
mysql select left(ftText,20) from Txt_index where match(ftText)
against('+wùd200206* +wù12p' in boolean mode);
42 rows in set (8.00 sec)
mysql show variables like 'version';
+---+-+
| Variable_name | Value |
+---+-+
| version | 4.0.2-alpha-log |
+---+-+
1 row in set (0.00 sec)
SG Obviously, it's a bug.
yes. next query prove it:
mysql select left(ftText,17),match(ftText) against('+(wùd200206*) +wù12p' in boolean
mode) as m from Txt_index where match(ftText) against('+wùd200206* +wù12p' in boolean
mode);
+---+--+
| left(ftText,17) | m|
+---+--+
| wùd20020601 wù12p |1 |
| wùd20020601 wùnw |1 |
| wùd20020604 wù12p |1 |
| wùd20020604 wù12p |1 |
| wùd20020609 wù12p |1 |
| wùd20020609 wù12p |1 |
| wùd20020609 wù12p |1 |
| wùd20020609 wù12p |1 |
| wùd20020609 wù12p |1 |
| wùd20020609 wù12p |1 |
| wùd20020609 wù12p |1 |
| wùd20020609 wù12p |1 |
| wùd20020609 wù12p |1 |
| wùd20020609 wù12p |1 |
| wùd20020611 wù12p |1 |
| wùd20020611 wù12p |1 |
| wùd20020611 wù12p |1 |
| wùd20020611 wù12p |1 |
| wùd20020611 wù12p |1 |
| wùd20020614 wù12p |1 |
| wùd20020614 wù12p |1 |
| wùd20020619 wù12p |1 |
| wùd20020619 wù12p |1 |
| wùd20020619 wù12p |1 |
| wùd20020619 wù12p |1 |
| wùd20020619 wù12p |1 |
| wùd20020625 wù12p |1 |
| wùd20020625 wù12p |1 |
| wùd20020625 wù12p |1 |
| wùd20020625 wù12p |1 |
| wùd20020627 wù12p |1 |
| wùd20020627 wù12p |1 |
| wùd20020627 wù12p |1 |
| wùd20020627 wù12p |1 |
| wùd20020627 wù12p |1 |
| wùd20020627 wù12p |1 |
| wùd20020627 wù12p |1 |
| wùd20020627 wù12p |1 |
| wùd20020627 wù12p |1 |
| wùd20020627 wù12p |1 |
| wùd20020627 wù12p |1 |
| wùd20020627 wù12p |1 |
+---+--+
42 rows in set (3.46 sec)
mysql
SG Could you provide a test case ?
but how?
Table very big:
mysql SHOW TABLE STATUS like 'Txt_index';
+---+++---++-+-+--+---++-+-+-++---+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
|Max_data_length | Index_length | Data_free | Auto_increment | Create_time |
|Update_time | Check_time | Create_options | Comment |
+---+++---++-+-+--+---++-+-+-++---+
| Txt_index | MyISAM | Dynamic| 42744 | 6032 | 257856408 |
|4294967295 |166645760 | 0 | 58071 | 2002-07-11 15:22:31 |
|2002-07-11 15:30:56 | 2002-07-11 15:42:25 || Ïîëíîòåêñòîâûé èíäåêñ |
+---+++---++-+-+--+---++-+-+-++---+
1 row in set (0.03 sec)
mysql show create table Txt_index;
+---++
| Table | Create Table
|
||
+---++
| Txt_index | CREATE TABLE `Txt_index` ( `ID` int(11) NOT NULL auto_increment,
|`ftText` mediumtext NOT NULL, PRIMARY KEY (`ID`), FULLTEXT KEY `ftText`
|(`ftText`)) TYPE=MyISAM COMMENT='Ïîëíîòåêñòîâûé èíäåêñ' |
+---++
1 row in set (0.02 sec)
mysql
short table work without bug:
mysql create table tt
Bug or feature fulltext search?
Hello mysql,
see this 2 queries:
why they not equal?
why first skip many records?
mysql select left(ftText,20) from Txt_index where match(ftText)
against('+(wÝd200206*) +wÝ12p' in boolean mode);
+--+
| left(ftText,20) |
+--+
| wÝd20020601 wÝ12p wÝ |
| wÝd20020604 wÝ12p wÝ |
| wÝd20020604 wÝ12p wÝ |
+--+
3 rows in set (0.36 sec)
mysql select left(ftText,20) from Txt_index where match(ftText) against('+wÝd200206*
+wÝ12p' in boolean mode);
+--+
| left(ftText,20) |
+--+
| wÝd20020601 wÝ12p wÝ |
| wÝd20020601 wÝnwwÝ1 |
| wÝd20020604 wÝ12p wÝ |
| wÝd20020604 wÝ12p wÝ |
| wÝd20020609 wÝ12p wÝ |
| wÝd20020609 wÝ12p wÝ |
| wÝd20020609 wÝ12p wÝ |
| wÝd20020609 wÝ12p wÝ |
| wÝd20020609 wÝ12p wÝ |
| wÝd20020609 wÝ12p wÝ |
| wÝd20020609 wÝ12p wÝ |
| wÝd20020609 wÝ12p wÝ |
| wÝd20020609 wÝ12p wÝ |
| wÝd20020609 wÝ12p wÝ |
| wÝd20020611 wÝ12p wÝ |
| wÝd20020611 wÝ12p wÝ |
| wÝd20020611 wÝ12p wÝ |
| wÝd20020611 wÝ12p wÝ |
| wÝd20020611 wÝ12p wÝ |
| wÝd20020614 wÝ12p wÝ |
| wÝd20020614 wÝ12p wÝ |
| wÝd20020619 wÝ12p wÝ |
| wÝd20020619 wÝ12p wÝ |
| wÝd20020619 wÝ12p wÝ |
| wÝd20020619 wÝ12p wÝ |
| wÝd20020619 wÝ12p wÝ |
| wÝd20020625 wÝ12p wÝ |
| wÝd20020625 wÝ12p wÝ |
| wÝd20020625 wÝ12p wÝ |
| wÝd20020625 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
| wÝd20020627 wÝ12p wÝ |
+--+
42 rows in set (8.00 sec)
mysql show variables like 'version';
+---+-+
| Variable_name | Value |
+---+-+
| version | 4.0.2-alpha-log |
+---+-+
1 row in set (0.00 sec)
--
Best regards,
Artem 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
release 4.0.2
Hello developers, What problem with release 4.0.2? Why you can't release it also as 'alpha' and continue work on next version like 4.0.3. You alredy fix many bugs and add many very nice features, such as query like 'MATCH ... AGAINST(... IN BOOLEAN MODE)'. Release it! We (users) write you all other bugs. -- Best regards, Artem 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
min() bug
Hello mysql, mysql create temporary table _234 select -20020412051754 as b; select min(b) from _234; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 +-+ | min(b) | +-+ | -20020412051744 | +-+ 1 row in set (0.00 sec) -- Best regards, Artem 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
min() bug
Hello bugs, min return not value from table. mysql select min(ord) from cSectText where SectID=932; +-+ | min(ord)| +-+ | -20020412051744 | +-+ 1 row in set (0.00 sec) mysql select ord from cSectText where SectID=932 order by ord limit 10; +-+ | ord | +-+ | -20020412051754 | | -20020412051752 | | -20020412051750 | | -20020412051748 | | -20020412051746 | | -20020412051744 | | -20020412051742 | | -20020412051740 | | -20020412051738 | | -20020412051730 | +-+ 10 rows in set (0.00 sec) mysql select ord from cSectText where SectID=1 order by ord limit 10; +-+ | ord | +-+ | -20020412051794 | | -20020412051754 | | -20020412051752 | | -20020412051750 | | -20020412051748 | | -20020412051746 | | -20020412051744 | | -20020412051742 | | -20020412051740 | | -20020412051738 | +-+ 10 rows in set (0.00 sec) mysql select min(ord) from cSectText where SectID=1; +-+ | min(ord)| +-+ | -20020412051808 | +-+ 1 row in set (0.00 sec) mysql select * from cSectText where ord=-20020412051808 and SectID=1; Empty set (0.00 sec) mysql show create table cSectText; | cSectText | CREATE TABLE `cSectText` ( `SectID` int(11) NOT NULL default '0', `ID` int(11) NOT NULL default '0', `Date` date NOT NULL default '-00-00', `ord` bigint(17) NOT NULL default '0', PRIMARY KEY (`ID`,`SectID`), UNIQUE KEY `d` (`SectID`,`ord`), UNIQUE KEY `a` (`SectID`,`Date`,`ID`) ) TYPE=MyISAM COMMENT='ÓÓÓ' | 1 row in set (0.00 sec) mysql show variables like 'version'; +---++ | Variable_name | Value | +---++ | version | 3.23.49yes-log | +---++ 1 row in set (0.00 sec) -- Best regards, Artem 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
Re[2]: Bad count(*) perfermance on index.
Hello Jeremy, Friday, January 25, 2002, 11:00:43 AM, you wrote: Why query type select count(*) from table_name where key_field between 'min' and 'max' so slow? example: mysql select count(*) from Textes where ID between 7937 and 45061; +--+ | count(*) | +--+ |36360 | +--+ 1 row in set (0.42 sec) JZ How large is your key_buffer? key_buffer_size 8388600 JZ How many rows are in the table? not so many. 36362 rows. but big rows. table size 255Mb. mysql explain select count(*) from Textes where ID between 7937 and 45061; ++---+---+-+-+--+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+---+-+-+--+---+-+ | Textes | range | PRIMARY | PRIMARY | 4 | NULL | 36361 | where used; Using index | ++---+---+-+-+--+---+-+ 1 row in set (0.00 sec) why explain much faster? JZ Because it doesn't actually run the query. why rows from expalain not exactly match count(*)? JZ Because it's just an esitmate which helps MySQL decide the fastest way JZ to execute the query. It's clean. But why index structure don't allow count exactly how many rows between keys? Will It's be fixed in 4.0? JZ Hard to say. We don't know what the problem is yet. AFAIK, in 4.0 index structure will be changed - for allow 'order by ... desc' optimization. Will new structre allow fast count exactly how many rows between keys? it very common query: select count(*) from Textes where path like '/os/%'; select count(*) from Textes where SectID=6; and many other. JZ Can you run ANALYZE TABLE Textes and see if that helps? of course: mysql ANALYZE TABLE Textes; +---+-+--+--+ | Table | Op | Msg_type | Msg_text | +---+-+--+--+ | NewWeb.Textes | analyze | status | OK | +---+-+--+--+ 1 row in set (0.93 sec) JZ Jeremy -- Best regards, Artemmailto:[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
Bad optimization on ORDER BY .. DESC.
Hello All,
When I use order by .. DESC in query, time always more then in
order by .. ASC case.
Explain say Using filesort,in DESC case query.
Why???
Why I can't use key value in select like that:
select * from TableName where KEY(KEYNAME)concat('value1','value2')
or
select * from TableName order by KEY(KEYNAME)
I think in that case query optimizing will be more simple.
example:
CREATE TABLE cSectText (
SectID int(11) NOT NULL default '0',
ID int(11) NOT NULL default '0',
Date date NOT NULL default '-00-00',
PRIMARY KEY (ID,SectID),
UNIQUE KEY SectID (SectID,Date,ID)
) TYPE=MyISAM;
mysql select * from cSectText where SectID=1 order by date desc limit 1;
++---++
| SectID | ID| Date |
++---++
| 1 | 35131 | 2001-07-16 |
++---++
1 row in set (0.15 sec)
mysql select * from cSectText where SectID=1 order by date limit 1;
++---++
| SectID | ID| Date |
++---++
| 1 | 31118 | 1993-02-15 |
++---++
1 row in set (0.00 sec)
mysql explain select * from cSectText where SectID=1 order by date desc limit 1;
+---+--+---++-+---+---+-+
| table | type | possible_keys | key| key_len | ref | rows | Extra
| |
+---+--+---++-+---+---+-+
| cSectText | ref | SectID| SectID | 4 | const | 21892 | where used;
|Using index; Using filesort |
+---+--+---++-+---+---+-+
1 row in set (0.00 sec)
mysql explain select * from cSectText where SectID=1 order by date limit 1;
+---+--+---++-+---+---+-+
| table | type | possible_keys | key| key_len | ref | rows | Extra
| |
+---+--+---++-+---+---+-+
| cSectText | ref | SectID| SectID | 4 | const | 21892 | where used;
|Using index |
+---+--+---++-+---+---+-+
1 row in set (0.00 sec)
--
Best regards,
Artem 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
