Re: Fulltext search question: words with numbers (ie DB2)
Brent Baisley wrote: The other thing to consider is the 50% rule. If more than 50% of the records match, the search is consider irrelevant and no records are returned. So if you have 120 records and 61 have DB2 in them, you won't get a result set. But the 50% rule is overridden when the search is in boolean mode, isn't it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search question: words with numbers (ie DB2)
I have a table with the following:
CREATE TABLE foo (
...
description text,
...
FULLTEXT INDEX (description),
...
);
select count(*) from foo where description like '%db2%';
returns 61 rows. Checking them confirms that the word db2 exists as a
standalone word separated either by punctuation or spaces in several of
those documents
using MATCH(description) AGAINST('+db2' IN BOOLEAN MODE) returns no results.
Is this expected behaviour? If so, is there a way to circumvent it?
TIA
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search question: words with numbers (ie DB2)
Richard Davey wrote:
Hello Shane,
Wednesday, March 31, 2004, 5:43:10 AM, you wrote:
SA using MATCH(description) AGAINST('+db2' IN BOOLEAN MODE) returns no results.
SA Is this expected behaviour? If so, is there a way to circumvent it?
By default, the full text indexing engine doesn't include words with
less than 4 characters in. If you are using MySQL 4 you can change the
minimum length via the ft_min_word_len variable. On MySQL 3.x
there's nothing you can do short of changing the actual source code
and recompiling.
apologies, I forgot to mention that.
I was aware of this, and just finished changing the ft_min_word_length
to 2 and rebuilding my indexes.
searches for 'SAS' or 'net' yield results as expected, but DB2 still
does not.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT query format question
I've read through the boolean mode fulltext docs, and they address all
my questions well except how searches containing exact phrases are
handled when there is more than one. I believe the following will work
as I expect, but was wondering if anyone can confirm it for me:
Given the following search strings, are the following MATCH AGAINST
(BOOLEAN) going to operate as the search strings suggest?
Based on a discussion with a coworker, we've determined in the old
search strings that OR has a higher precedence than AND (the goal of the
first being either ambulatory pediatrics or ambulatory obgyn, for
example)
obgyn OR pediatrics AND ambulatory:
MATCH (jobdescription) AGAINST('+(obgyn pediatrics) +(ambulatory)'
IN BOOLEAN MODE)
Training AND Documentation OR Technical Writer:
MATCH (jobdescription) AGAINST('+(Training) +(Documentation
Technical Writer)' IN BOOLEAN MODE)
art OR graphic OR publishing:
MATCH (jobdescription) AGAINST('+(art graphic publishing)' IN
BOOLEAN MODE)
web design AND web development AND webmaster:
MATCH (jobdescription) AGAINST('+(web design) +(web development)
+(webmaster)' IN BOOLEAN MODE)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maybe easy, maybe hard SELECT puzzle :)
At 09:24 PM 11/10/2003, Leo wrote: notice the _and_ ? *grin* good point. I guess my reply (which I found out later was unnecessary since the question had already been answered) would have been better stated had I just pointed out the distinct keyword rather than constructing a sample query. :) ah well -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maybe easy, maybe hard SELECT puzzle :)
At 07:45 PM 11/7/2003, Jonathan Terhorst wrote:
snip
One way I have found to implement this is
SELECT Table1.id from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='A01' OR Features.FeatureCode='B02' GROUP BY Table1.id
HAVING COUNT(*)=2;
e.g. counting the duplications of id and selecting those equal to the number
of FeatureCodes I'm searching for. But this seems somehow inelegant, and I'm
justing wondering if there's a better way that's staring me in the face.
SELECT DISTINCT f.id FROM Table1 AS t, Features AS f
WHERE f.id = t.id AND f.FeatureCode IN ('A01', 'B02');
HTH
mysql, query, sql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB errors
I've searched the archives and was unable to find anything that seemed pertinent. Earlier today I had to stop and start mysql. When I issued mysqladmin shutdown, however, the process did not die cleanly (reason unknown), and I ended up having to kill -9 mysqld and mysqld_safe. When the DB came back up, I now have the following in my log: 031024 11:06:04 mysqld started 031024 11:06:05 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 697473956 InnoDB: Doing recovery: scanned up to log sequence number 0 697473956 031024 11:06:06 InnoDB: Flushing modified pages from the buffer pool... 031024 11:06:06 InnoDB: Started 031024 11:06:06 Found an entry in the 'db' table with empty database name; Skipped 031024 11:06:06 Found an entry in the 'db' table with empty database name; Skipped ... 031024 11:07:53 mysqld started 031024 11:07:54 InnoDB: Started 031024 11:07:54 Found an entry in the 'db' table with empty database name; Skipped 031024 11:07:54 Found an entry in the 'db' table with empty database name; Skipped as you can see, on the second restart, I did not get warnings about having to recover the tables, but I still get the 'empty database name' errors. How can I determine what the cause of this error is an get it fixed? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bug in FOUND_ROWS
Running 4.0.15a. The second select's FOUND_ROWS() returns the wrong number of rows. To test yourself: create database test1234; \r test1234 create table test (id int, primary key (id)); insert into test values (1), (2), (3), (4), (5); select SQL_CALC_FOUND_ROWS * from test where id 3 limit 0, 1; select FOUND_ROWS(); select SQL_CALC_FOUND_ROWS * from test where id 3 AND 1=2 limit 0, 1; select FOUND_ROWS(); select SQL_CALC_FOUND_ROWS * from test where id 6 limit 0, 1; select FOUND_ROWS(); My results: mysql create database test1234; Query OK, 1 row affected (0.01 sec) mysql \r test1234 Connection id:122 Current database: test1234 mysql create table test (id int, primary key (id)); Query OK, 0 rows affected (0.01 sec) mysql insert into test values (1), (2), (3), (4), (5); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql select SQL_CALC_FOUND_ROWS * from test where id 3 limit 0, 1; ++ | id | ++ | 4 | ++ 1 row in set (0.00 sec) mysql select FOUND_ROWS(); +--+ | FOUND_ROWS() | +--+ |2 | +--+ 1 row in set (0.00 sec) mysql select SQL_CALC_FOUND_ROWS * from test where id 3 AND 1=2 limit 0, 1; Empty set (0.00 sec) mysql select FOUND_ROWS(); +--+ | FOUND_ROWS() | +--+ |2 | +--+ 1 row in set (0.00 sec) mysql select SQL_CALC_FOUND_ROWS * from test where id 6 limit 0, 1; Empty set (0.00 sec) mysql select FOUND_ROWS(); +--+ | FOUND_ROWS() | +--+ |0 | +--+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT feature requests
The only reason that we are unable to use a stock build of mysql is because 1) we use GWS_FREQ as our GWS_IN_USE, not GWS_PROB 2) we have a custom stopword list Is there any reason that these two settings, like FT_MIN_WORD_LENGTH, cannot be moved to runtime-configurable params? Obviously, changing either setting would require a rebuild of the FT index(es), but then, so does FT_MIN_WORD_LENGTH... TIA -- Shane Allen [EMAIL PROTECTED] http://www.emphasys.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions abou innodb
We're considering switching to InnoDB tables for a couple of tables in our database where we need transactions. We're complete newbies with reference to innodb, although we are seasoned veterans with regards to MySQL/MyISAM and general concepts. We have come up with a couple questions that we haven't been able to find answers on elsewhere. If the answers are available online somewhere, please let me know. 1) can you join a MyISAM table to an innodb table in a select? 2) Under 'Restrictions...' in the manual, it says: When you restart the MySQL server, InnoDB may reuse an old value for an AUTO_INCREMENT column. Under what circumstances does this occur? 3) Are there any replication issues? 4) Any issues regarding a mixed MyISAM/InnoDB environment that we should worry about? 5) Anyone who has done this in the past -- how much of a pain was it? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with build process
I've actually noticed this in the past several releases; I apologize for not mentioning it sooner. I run: BUILD/compile-pentium --with-other-libc=/usr/local/mysql-glibc-2.2.5 --prefix=/usr/local --with-extra-charsets=none --without-innodb --without-isam and part-way through, it fails. Specifically in mysys/Makefile, the line: EXTRA_FLAGS=-all-static needs to be changed to: EXTRA_FLAGS=-static I've no experience with autoconf/automake and the like, so I don't even know where to begin looking, but thought you might like to know. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Modifying table causes insert delayed queries to fail
Description This may be by design, but I figured I'd bring it up because it caused less-than-desirable behaviour for me. I executed an alter table query, designed to change the length of a fixed width char field to 128 from 255. The alter occured fine, with no problems, except that there were apparently some insert delayed queries that I guess were queued for execution before the alter, and tried to execute after. Since I did not remove any columns, and none of the char data that was being inserted was over 20 chars, I do not understand why the inserts failed... I noticed the problem thanks to my monitoring system telling me that the slave had stopped. How-To-Repeat: CREATE TABLE test ( id int(10) unsigned AUTO_INCREMENT, test char(255) NOT NULL default '', PRIMARY KEY(id) ); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); ALTER TABLE test change column text text char(128) NOT NULL default ''; If you do it all quickly enough, the problem will occur. It may not be evident unless you have a slave to fail. The slave will stop with the following error: Slave: query 'INSERT DELAYED INTO searches ( terms,category,date_created,id,modifiers ) VALUES ( 'jacob and co',0104,NOW(),0,'cat:1-4' )' partially completed on the master and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; Fix: Either: 1) Execute all insert delayed queries before executing the alter table command, or 2) allow the query to execute normally after the alter IF the tables structure is not fundamentally changed. By fundamentally changed, I mean that columns that are necessary for the insert were not removed, and they didn't change type. In the case of my insert, I consider that there was no fundamental change because in a char field if I try to insert greater than char(128) characters, it is truncated and the query completes. Submitter-Id: submitter ID Originator:root Organization: MySQL support: none Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-4.0.11a-gamma (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.0.11a-gamma, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.11a-gamma-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 18 days 19 hours 30 min 14 sec Threads: 101 Questions: 9558361 Slow queries: 8453 Opens: 22074 Flush tables: 1 Open tables: 262 Queries per second avg: 5.881 C compiler:gcc (GCC) 3.1.1 C++ compiler: gcc (GCC) 3.1.1 Environment: IBM Netfinity 4500, Debian Potato System: Linux 2.4.19pre1 #2 SMP Sun Jun 30 03:54:52 CDT 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.1.1/specs Configured with: /mnt/data/gcc-3.1/gcc-3.1-3.1.1ds3/src/configure -v --enable-languages=c,c++,java,f77,proto,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.1 --enable-shared --with-system-zlib --enable-long-long --enable-nls --without-included-gettext --enable-clocale=gnu --enable-__cxa_atexit --enable-threads=posix --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.1.1 Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Nov 24 15:27 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root
Feature Request: Return AUTO_INCREMENT on INSERT
How about this: mysql CREATE TABLE tablename (value1 int(10) AUTO_INCREMENT, value2 timestamp(14), PRIMARY KEY (value1)); Query OK, 0 rows affected (0.00 sec) mysql INSERT SQL_RETURN_INSERT_ID INTO tablename (value1, value2) VALUES (0, NOW()); +-+ | INSERT_ID() | +-+ | 1 | +-+ 1 row in set (0.01 sec) mysql INSERT SQL_RETURN_INSERT_ID INTO tablename (value1, value2) VALUES (0, NOW()), (0, NOW()); +-+ | INSERT_ID() | +-+ | 2 | | 3 | +-+ 2 rows in set (0.01 sec) I'd just *love* it if inserting and retrieving the auto_increment value was atomic. Thoughts? - 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
replication only works for user x from host x, not host y
This is a repost of my previous message with a new and improved title that will hopefully catch more eyes and generate a response. :) We have two users, lets call them test and test2. When we log in as test from our front end web servers, we can execute queries and they replicate. When we log in as test from our back end db servers (say to run a cleanup script by hand), the queries replicate. When we log in as test2 from our back end db servers (as part of a periodic process), it replicates the queries. When we log in from our front end web servers as test2, the queries do not replicate. They are never added to the binary update log. Otherwise, everything appears to work fine; the master db server runs the queries and updates the tables appropriately. If necessary I can upload a copy of our grant table, but I'm hoping someone has a quick suggestion for me to try. Thanks - 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
replication problems
We have two users, lets call them test and test2. When we log in as test from our front end web servers, we can execute queries and they replicate. When we log in as test from our back end db servers (say to run a cleanup script by hand), the queries replicate. When we log in as test2 from our back end db servers (as part of a periodic process), it replicates the queries. When we log in from our front end web servers as test2, the queries do not replicate. They are never added to the binary update log. Otherwise, everything appears to work fine; the master db server runs the queries and updates the tables appropriately. If necessary I can upload a copy of our grant table, but I'm hoping someone has a quick. easy fix. :) Thanks - 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: slow query...
On Thu, Oct 17, 2002 at 08:50:43PM +0200, Corin Langosch wrote: the table has about 12.000 rows total but the query should return a max of about 100. mysql should split the query into two queries and merge the results. may be this could be done by joining the same table two times and query each table only for one of the OR expression. i'll try but for future versions of mysql, this should be internally done i think... ;) Thursday, October 17, 2002, 4:07:01 PM, you wrote: CL are normal indexes. Unluckily a query like: CL -- CL EXPLAIN SELECT * FROM `telegramme` WHERE to_id = 3157 or owner_id = 3157 CL -- CL returns very bad results: CL -- CL table type possible_keys key key_len ref rows Extra CL telegramme ALL to_id,owner_id NULL NULL NULL 11488 where used CL -- CL how could this be optimized or is this a bug of mysql, because the CL indexes exist but are not used (even if specified with use index(..)) ?! Sounds like you should either: ALTER TABLE telegramme ADD INDEX combined (to_id, owner_id); or SELECT * FROM telegramme WHERE to_id = 3157 UNION SELECT * FROM telegramme WHERE owner_id = 3157; HTH -- Shane Allen [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
Error running autoheader on a new clone of src tree for 4.0
I am trying to build MySQL from a new clone of the bitkeeper repository, and am
running into some difficulty...
I am running Debian Woody, and am running unstable. Following are the relevant
utilities/versions installed on my system:
===
root@local-dbmaster-test:/usr/local/src/temp/mysql-4.0# autoconf --version
autoconf (GNU Autoconf) 2.54
Written by David J. MacKenzie and Akim Demaille.
Copyright 2002 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
root@local-dbmaster-test:/usr/local/src/temp/mysql-4.0# automake --version
automake (GNU automake) 1.5
Written by Tom Tromey [EMAIL PROTECTED].
Copyright 1994, 1995, 1996, 1997, 1998, 1999, 2000, 2001
Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
root@local-dbmaster-test:/usr/local/src/temp/mysql-4.0# libtool --version
ltmain.sh (GNU libtool) 1.4.2a (1.922.2.100 2002/06/26 07:25:14)
root@local-dbmaster-test:/usr/local/src/temp/mysql-4.0# m4 --version
GNU m4 1.4
===
Following is the output of autoheader --verbose (run after aclocal)
===
root@local-dbmaster-test:/usr/local/src/temp/mysql-4.0# autoheader --verbose
WARNING: Using auxiliary files such as `acconfig.h', `config.h.bot'
WARNING: and `config.h.top', to define templates for `config.h.in'
WARNING: is deprecated and discouraged.
WARNING: Using the third argument of `AC_DEFINE' and
WARNING: `AC_DEFINE_UNQUOTED' allows to define a template without
WARNING: `acconfig.h':
WARNING: AC_DEFINE([NEED_MAIN], 1,
WARNING: [Define if a function `main' is needed.])
WARNING: More sophisticated templates can also be produced, see the
WARNING: documentation.
autoheader: autoheader: running /usr/bin/autom4te --language=autoconf --verbose
to trace from configure.in
autoheader: running: /usr/bin/autom4te --language=autoconf --verbose --trace A
C_CONFIG_HEADERS:'$$config_h ||= $1;' --trace AH_OUTPUT:'$$verbatim{$1} = \
$2;' --trace AC_DEFINE_TRACE_LITERAL:'$$symbol{$1} = 1;' configure.in /tmp/a
hOhFqbK/traces.pl
autom4te: the trace request object is:
$VAR1 = bless( [
'0',
0,
[
'/usr/share/autoconf'
],
[
'--reload-state=/usr/share/autoconf/autoconf/autoconf.m4f',
'aclocal.m4',
'configure.in'
],
{
'AC_HEADER_STAT' = 1,
'AC_FUNC_STRFTIME' = 1,
'AC_PROG_RANLIB' = 1,
'AC_FUNC_WAIT3' = 1,
'AC_FUNC_SETPGRP' = 1,
'AC_HEADER_TIME' = 1,
'AC_FUNC_SETVBUF_REVERSED' = 1,
'AC_HEADER_SYS_WAIT' = 1,
'AC_TYPE_UID_T' = 1,
'AM_CONDITIONAL' = 1,
'AC_CHECK_LIB' = 1,
'AC_PROG_LN_S' = 1,
'AC_FUNC_MEMCMP' = 1,
'AC_FUNC_FORK' = 1,
'AC_FUNC_GETGROUPS' = 1,
'AC_HEADER_MAJOR' = 1,
'AC_FUNC_STRTOD' = 1,
'AC_HEADER_DIRENT' = 1,
'AC_FUNC_UTIME_NULL' = 1,
'AC_CONFIG_FILES' = 1,
'AC_FUNC_ALLOCA' = 1,
'AC_C_CONST' = 1,
'include' = 1,
'AC_FUNC_OBSTACK' = 1,
'AC_FUNC_LSTAT' = 1,
'AC_STRUCT_TIMEZONE' = 1,
'AC_FUNC_GETPGRP' = 1,
'AC_DEFINE_TRACE_LITERAL' = 1,
'AC_CHECK_HEADERS' = 1,
'AC_TYPE_MODE_T' = 1,
'AC_CHECK_TYPES' = 1,
'AC_PROG_YACC' = 1,
'AC_TYPE_PID_T' = 1,
'AC_FUNC_STRERROR_R' = 1,
'AC_STRUCT_ST_BLOCKS' = 1,
'AC_PROG_GCC_TRADITIONAL' = 1,
'AC_TYPE_SIGNAL' = 1,
'AM_PROG_LIBTOOL' = 1,
'AC_FUNC_FNMATCH' = 1,
'AC_PROG_CPP' = 1,
'AC_FUNC_STAT' = 1,
'AC_PROG_INSTALL' = 1,
'AM_GNU_GETTEXT' = 1,
'AC_FUNC_STRCOLL' = 1,
'AC_LIBSOURCE' = 1,
'AC_C_INLINE' = 1,
'AC_FUNC_CHOWN' = 1,
'AC_PROG_LEX' = 1,
'AH_OUTPUT' = 1,
'AC_HEADER_STDC' = 1,
'AC_FUNC_GETLOADAVG' = 1,
BUG: Only half of the results shown when using order by on unindexed field
I haven't seen any traffic on this thread, so I figured I'd repost it with a slightly more catchy subject. :) Has anyone looked into this? On Tue, Oct 08, 2002 at 09:12:13PM -0500, Shane Allen wrote: Description: See How-To-Repeat How-To-Repeat: I uploaded a copy of the tables to support.mysql.com/pub/mysql/secret. Each of the tables below contains many rows (from 50-100k per table). We were unable to duplicate with a smaller result set, CREATE TABLE listing ( id int(10) unsigned NOT NULL default '0', seller_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (id), KEY seller_id (seller_id) ) TYPE=MyISAM; CREATE TABLE question ( id int(10) unsigned NOT NULL auto_increment, listing_id int(10) unsigned NOT NULL default '0', buyer_id int(10) unsigned NOT NULL default '0', last_modified timestamp(14) NOT NULL, PRIMARY KEY (id), KEY buyer_id (buyer_id), KEY listing_id (listing_id) ) TYPE=MyISAM; CREATE TABLE user ( id int(10) unsigned NOT NULL default '0', username char(15) NOT NULL default '', PRIMARY KEY (id) ) TYPE=MyISAM; SELECT COUNT(*) FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.last_modified DESC; +--+ | COUNT(*) | +--+ |4 | +--+ SELECT question.id AS question_id, listing.id AS listing_id, ub.id AS buyer_id, ub.username AS buyer_name, us.id AS seller_id, us.username as seller_name FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.last_modified DESC; +-++--++---+-+ | question_id | listing_id | buyer_id | buyer_name | seller_id | seller_name | +-++--++---+-+ |6850 | 12909 | 2346 | user2346 | 4879 | user4879| |4823 | 39039 | 2346 | user2346 | 14831 | user14831 | +-++--++---+-+ 2 rows in set (0.00 sec) # Adding SQL_CALC_FOUND_ROWS shows correct results SELECT SQL_CALC_FOUND_ROWS question.id AS question_id, listing.id AS listing_id, ub.id AS buyer_id, ub.username AS buyer_name, us.id AS seller_id, us.username as seller_name FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.last_modified DESC; +-++--++---+-+ | question_id | listing_id | buyer_id | buyer_name | seller_id | seller_name | +-++--++---+-+ |6850 | 12909 | 2346 | user2346 | 4879 | user4879| |4823 | 39039 | 2346 | user2346 | 14831 | user14831 | |6084 | 49920 | 2346 | user2346 | 14704 | user14704 | |6223 | 48014 | 2346 | user2346 | 7386 | user7386| +-++--++---+-+ 4 rows in set (0.00 sec) # Ordering by a indexed column shows correct results SELECT question.id AS question_id, listing.id AS listing_id, ub.id AS buyer_id, ub.username AS buyer_name, us.id AS seller_id, us.username as seller_name FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.id DESC; +-++--++---+-+ | question_id | listing_id | buyer_id | buyer_name | seller_id | seller_name | +-++--++---+-+ |6850 | 12909 | 2346 | user2346 | 4879 | user4879| |6223 | 48014 | 2346 | user2346 | 7386 | user7386| |6084 | 49920 | 2346 | user2346 | 14704 | user14704 | |4823 | 39039 | 2346 | user2346 | 14831 | user14831 | +-++--++---+-+ 4 rows in set (0.00 sec) Also, adding an index to the order by column fixes the problem. In other testing, adding a combined index: ALTER TABLE question ADD INDEX combined (last_modified, listing_id, buyer_id); fixed the problem, however, adding: ALTER TABLE question ADD INDEX combined (buyer_id, listing_id, last_modified); did not. Fix: Adding indexes (See How-To-Repeat) Submitter-Id: Originator: Shane Allen [EMAIL PROTECTED] Organization: sell.com MySQL support: none
Only half of the results shown when using order by on unindexed field
Description: See How-To-Repeat How-To-Repeat: I uploaded a copy of the tables to support.mysql.com/pub/mysql/secret. Each of the tables below contains many rows (from 50-100k per table). We were unable to duplicate with a smaller result set, CREATE TABLE listing ( id int(10) unsigned NOT NULL default '0', seller_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (id), KEY seller_id (seller_id) ) TYPE=MyISAM; CREATE TABLE question ( id int(10) unsigned NOT NULL auto_increment, listing_id int(10) unsigned NOT NULL default '0', buyer_id int(10) unsigned NOT NULL default '0', last_modified timestamp(14) NOT NULL, PRIMARY KEY (id), KEY buyer_id (buyer_id), KEY listing_id (listing_id) ) TYPE=MyISAM; CREATE TABLE user ( id int(10) unsigned NOT NULL default '0', username char(15) NOT NULL default '', PRIMARY KEY (id) ) TYPE=MyISAM; SELECT COUNT(*) FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.last_modified DESC; +--+ | COUNT(*) | +--+ |4 | +--+ SELECT question.id AS question_id, listing.id AS listing_id, ub.id AS buyer_id, ub.username AS buyer_name, us.id AS seller_id, us.username as seller_name FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.last_modified DESC; +-++--++---+-+ | question_id | listing_id | buyer_id | buyer_name | seller_id | seller_name | +-++--++---+-+ |6850 | 12909 | 2346 | user2346 | 4879 | user4879| |4823 | 39039 | 2346 | user2346 | 14831 | user14831 | +-++--++---+-+ 2 rows in set (0.00 sec) # Adding SQL_CALC_FOUND_ROWS shows correct results SELECT SQL_CALC_FOUND_ROWS question.id AS question_id, listing.id AS listing_id, ub.id AS buyer_id, ub.username AS buyer_name, us.id AS seller_id, us.username as seller_name FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.last_modified DESC; +-++--++---+-+ | question_id | listing_id | buyer_id | buyer_name | seller_id | seller_name | +-++--++---+-+ |6850 | 12909 | 2346 | user2346 | 4879 | user4879| |4823 | 39039 | 2346 | user2346 | 14831 | user14831 | |6084 | 49920 | 2346 | user2346 | 14704 | user14704 | |6223 | 48014 | 2346 | user2346 | 7386 | user7386| +-++--++---+-+ 4 rows in set (0.00 sec) # Ordering by a indexed column shows correct results SELECT question.id AS question_id, listing.id AS listing_id, ub.id AS buyer_id, ub.username AS buyer_name, us.id AS seller_id, us.username as seller_name FROM question, listing, user ub, user us WHERE question.listing_id = listing.id AND question.buyer_id = ub.id AND listing.seller_id = us.id AND (ub.id = 2346 OR us.id = 2346) ORDER BY question.id DESC; +-++--++---+-+ | question_id | listing_id | buyer_id | buyer_name | seller_id | seller_name | +-++--++---+-+ |6850 | 12909 | 2346 | user2346 | 4879 | user4879| |6223 | 48014 | 2346 | user2346 | 7386 | user7386| |6084 | 49920 | 2346 | user2346 | 14704 | user14704 | |4823 | 39039 | 2346 | user2346 | 14831 | user14831 | +-++--++---+-+ 4 rows in set (0.00 sec) Also, adding an index to the order by column fixes the problem. In other testing, adding a combined index: ALTER TABLE question ADD INDEX combined (last_modified, listing_id, buyer_id); fixed the problem, however, adding: ALTER TABLE question ADD INDEX combined (buyer_id, listing_id, last_modified); did not. Fix: Adding indexes (See How-To-Repeat) Submitter-Id: Originator: Shane Allen [EMAIL PROTECTED] Organization: sell.com MySQL support: none Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-4.0.4-beta (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.37 Distrib 4.0.4-beta, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY
Re: Additional Information: Replication issue in 4.0.4
On Sat, Oct 05, 2002 at 03:47:18PM +0300, Heikki Tuuri wrote: Shane, 021004 12:57:00 Slave: error 'Incorrect key file for table: 'listing_text'. Try to repair it' on query 'UPDATE sell.listing_text SET description='blah' WHERE listing_id=221112', error_code=1034 So, I issued `mysqlcheck -r sell listing_text`, and eventually Lost connection to MySQL server during query. Inspection of the error log yields the following: mysqld: mf_iocache.c:1106: _flush_io_cache: Assertion `info-end_of_file == my_tell(info-file,(myf) (0))' failed. Number of processes running now: 1 mysqld process hanging, pid 790 - killed did you try to run the command line repair tool on a table while the mysqld server was up? The server must be shut down if you use the command line tool. Otherwise the indexes of the table may be cached in the key_buffer of the mysqld process. Heikki, If I attempt to run mysqlcheck -r db table while the server is down, I get: mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect It is my understanding that mysqlcheck's repair mechanism relies on MySQL's built-in REPAIR statement, at least in the case of a MyISAM table. Thanks -- Shane Allen [EMAIL PROTECTED] interesting side-note: mutt's list-reply misses my send-hooks... :) - 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
Additional Information: Replication issue in 4.0.4
On Thu, Oct 03, 2002 at 06:39:26PM -0500, Shane Allen wrote: We have a 4.0.4-beta master and slave. The slaving process starts correctly, but randomly (and frequently) has issues. When the server starts, it will slave, but eventually will hit one of two conditions: - Duplicate key insert error (we re-sync'd the slave by hand during the install, copying databases by hand, etc, so they're known good) - Signal 8. The database will hit Signal 8, die, and be restarted. Immediately upon restarting, it dies, and restarts, ad naseum. I upgraded in testing to 4.0.4 again, built with debugging enabled, and got a slightly different result. This time, it eventually hit a query and the slaving stopped. This time the error was: 021004 12:57:00 Slave: error 'Incorrect key file for table: 'listing_text'. Try to repair it' on query 'UPDATE sell.listing_text SET description='blah' WHERE listing_id=221112', error_code=1034 So, I issued `mysqlcheck -r sell listing_text`, and eventually Lost connection to MySQL server during query. Inspection of the error log yields the following: mysqld: mf_iocache.c:1106: _flush_io_cache: Assertion `info-end_of_file == my_tell(info-file,(myf) (0))' failed. Number of processes running now: 1 mysqld process hanging, pid 790 - killed presumably the debugging code that is built in is what is preventing the signal 8 behaviour that I experienced last night. On the master database I have been able to issue successful repair statements several timesi against this table, and the databases that I am attempting to repair have been copied over from the master, so I'm not sure what is causing the repairs to fail... -- Shane Allen [EMAIL PROTECTED] sell.com : Buy Sell Anything http://www.sell.com - 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: SQL Query Help
On Fri, Oct 04, 2002 at 12:36:30PM -0700, David McInnis wrote: Can someone please help me with the following? Normally I would do this with a nested select, but since this is not available in MySQL I think I need help. Here is what I have: An order table with sales tax total and an orderdetail table with ordered, itemid and qty. What I need to do is form a sql query that will allow me to pull get the tax amount on all orders where product id is 1, 2 or 3 for example. The problem that I have is when I do a straight join on select tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I can get multiple tax amounts where an order has multiple matching records in orderdetail. I know that I can group by order.id, but what I eventually need to do is pull sum(tax) and not just tax. I'm not certain if I understand what you're after... If you want total tax per order, try select sum(tax) as tax, orders.id from orders, orderdetail where orders.id = orderdetail.orderid and productid in (1,2,3) group by orderid; Otherwise, please clarify what you want in your desired result set. - 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
Replication issue in 4.0.4
We have a 4.0.4-beta master and slave. The slaving process starts correctly, but randomly (and frequently) has issues. When the server starts, it will slave, but eventually will hit one of two conditions: - Duplicate key insert error (we re-sync'd the slave by hand during the install, copying databases by hand, etc, so they're known good) - Signal 8. The database will hit Signal 8, die, and be restarted. Immediately upon restarting, it dies, and restarts, ad naseum. The Dupkicate Key insert doesn't have any decent or recognizable accompanying errors, however the signal 8 leaves the following in the logs: 021003 18:04:03 mysqld restarted /usr/local/libexec/mysqld: ready for connections 021003 18:04:03 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'dbmaster1-bin.002' at position 110748 mysqld got signal 8; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402649088 read_buffer_size=1044480 sort_buffer_size=1048568 max_used_connections=0 max_connections=2500 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 130 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x82d4d00 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x5c190f98, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80940d7 0x81798fb 0x8095aa5 0x8056129 0x8057109 0x804b7d2 0x80b513a 0x80cd4ad 0x809f16d 0x80a2d8c 0x80db5a5 0x81183c6 0x8117147 0x8177394 0x81aa79a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x82d0ba6 = UPDATE /* sess_read:update timestamp */ admin.login SET login.last_modified = NOW(), login.mumbojumbo = FLOOR(RAND()*) WHERE login.uid IN( 0, 27 ) AND login.session = 'fb103efea43b37b049cb1d54f400724d' thd-thread_id=2 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 2 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 I have run a backtrace, and came up with the following: 0x80940d7 _Z15handle_segfaulti + 503 0x81798fb sem_timedwait + 95 0x8095aa5 rnd + 53 0x8056129 _ZN13Item_func_mul3valEv + 41 0x8057109 _ZN15Item_func_floor7val_intEv + 41 0x804b7d2 _ZN4Item13save_in_fieldEP5Field + 226 0x80b513a _Z11fill_recordR4ListI4ItemES2_ + 106 0x80cd4ad _Z12mysql_updateP3THDP13st_table_listR4ListI4ItemES6_PS4_P8st_orderm15enum_duplicates13thr_lock_type + 2093 0x809f16d _Z21mysql_execute_commandv + 4829 0x80a2d8c _Z11mysql_parseP3THDPcj + 300 0x80db5a5 _ZN15Query_log_event10exec_eventEP17st_relay_log_info + 293 0x81183c6 _Z22process_io_create_fileP14st_master_infoP21Create_file_log_event + 166 0x8117147 _Z10next_eventP17st_relay_log_info + 423 0x8177394 pthread_handle_free + 72 0x81aa79a __nss_lookup_function + 686 Reverting the slave to 4.0.3 fixed the instability AFAICT (it's only been reverted for a brief period). The master has remained at 4.0.4 Let me know if you need further info. Thanks -- Shane Allen [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
signal 11 when SELECT DISTINCT blah FROM table LEFT JOIN blah...
Description: selecting distinct when executing a left join causes mysql to die How-To-Repeat: CREATE TABLE test1 ( i1 int(10) unsigned NOT NULL default 0, s1 varchar(30), PRIMARY KEY (i1) ); CREATE TABLE test2 ( i1 int(10) unsigned NOT NULL default 0, s1 varchar(30), PRIMARY KEY (i1) ); insert into test1 values (1,'test'); insert into test1 values (2,'test'); insert into test2 values (1,'test'); insert into test2 values (2,'test'); select distinct * from test1 t1 left join test2 t2 on (t1.i1=t2.i1); Fix: None known Submitter-Id: submitter ID Originator:Shane Allen (shane at sell dot com) Organization: sell.com MySQL support: none Synopsis: select distinct with a left join crashes mysql 4.0.3 Severity: critical Priority: low Category: mysql Class: sw-bug Release: mysql-4.0.3-beta (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.37 Distrib 4.0.3-beta, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.3-beta-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 56 sec Threads: 2 Questions: 13 Slow queries: 0 Opens: 7 Flush tables: 1 Open tables: 1 Queries per second avg: 0.232 Environment: System: Linux local-dbmaster-test 2.4.19 #1 SMP Mon Sep 2 15:38:05 CDT 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.1.1/specs Configured with: /mnt/data/gcc-3.1/gcc-3.1-3.1.1ds3/src/configure -v --enable-languages=c,c++,java,f77,proto,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.1 --enable-shared --with-system-zlib --enable-long-long --enable-nls --without-included-gettext --enable-clocale=gnu --enable-__cxa_atexit --enable-threads=posix --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.1.1 Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Jul 30 16:49 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1153784 Jul 18 06:53 /lib/libc-2.2.5.so -rw-r--r--1 root root 2390922 Jul 18 06:53 /usr/lib/libc.a -rw-r--r--1 root root 178 Jul 18 06:53 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --enable-local-infile --with-other-libc=/usr/local/mysql-glibc-2.2.5 --prefix=/usr/local --with-extra-charsets=none --without-innodb - 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: signal 11 when SELECT DISTINCT blah FROM table LEFT JOIN blah...
Apologies, I thought I had done a decent search to see if anyone else had reported it, but apparently my search skills are lacking, I see that it was fixed in 4.0.4. On Wed, Sep 25, 2002 at 11:34:07PM -0500, Shane Allen wrote: Description: selecting distinct when executing a left join causes mysql to die How-To-Repeat: CREATE TABLE test1 ( i1 int(10) unsigned NOT NULL default 0, s1 varchar(30), PRIMARY KEY (i1) ); CREATE TABLE test2 ( i1 int(10) unsigned NOT NULL default 0, s1 varchar(30), PRIMARY KEY (i1) ); insert into test1 values (1,'test'); insert into test1 values (2,'test'); insert into test2 values (1,'test'); insert into test2 values (2,'test'); select distinct * from test1 t1 left join test2 t2 on (t1.i1=t2.i1); Fix: None known Submitter-Id:submitter ID Originator: Shane Allen (shane at sell dot com) Organization: sell.com MySQL support: none Synopsis:select distinct with a left join crashes mysql 4.0.3 Severity:critical Priority:low Category:mysql Class: sw-bug Release: mysql-4.0.3-beta (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.37 Distrib 4.0.3-beta, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version4.0.3-beta-log Protocol version 10 ConnectionLocalhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 56 sec Threads: 2 Questions: 13 Slow queries: 0 Opens: 7 Flush tables: 1 Open tables: 1 Queries per second avg: 0.232 Environment: System: Linux local-dbmaster-test 2.4.19 #1 SMP Mon Sep 2 15:38:05 CDT 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.1.1/specs Configured with: /mnt/data/gcc-3.1/gcc-3.1-3.1.1ds3/src/configure -v --enable-languages=c,c++,java,f77,proto,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.1 --enable-shared --with-system-zlib --enable-long-long --enable-nls --without-included-gettext --enable-clocale=gnu --enable-__cxa_atexit --enable-threads=posix --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.1.1 Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Jul 30 16:49 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1153784 Jul 18 06:53 /lib/libc-2.2.5.so -rw-r--r--1 root root 2390922 Jul 18 06:53 /usr/lib/libc.a -rw-r--r--1 root root 178 Jul 18 06:53 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --enable-local-infile --with-other-libc=/usr/local/mysql-glibc-2.2.5 --prefix=/usr/local --with-extra-charsets=none --without-innodb - 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 -- Shane Allen [EMAIL PROTECTED] sell.com : Buy Sell Anything http://www.sell.com - 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
Crashing under high load
I recently read an article (on PHPBuilder; it's fairly old) that spoke of MySQL dying under high load. Recently (since I built 3.23.51 and on my test build of 4.0.2) I have had problems with MySQL dying (got signal 11, et al.) under extreme high load. For example, if I set up a 60-thread spider, and run it in our test environment (1 front end web server, 1 database server, mysql 4.0.2 alpha compiled with a custom glib-2.2.5 + MySQL patch to linuxthreads)(heavily database driven), I can crash MySQL after only 30 minutes or so. The sad part is, we haven't hit the 2500 connection limit that I've set. I've tried checking the physical resources on the boxen, and MySQL doesn't appear to have consumed all the resources on the box at this point, although the load average when MySQL dies is usually around 15. I've been tweaking values, and found that increasing the table_cache from our previous setting of 400 to 3000 has caused *vastly* improved stability, however I cannot imagine that a table_cache adjustment should prevent a crash... I digress... at this point, the question that plagues me is: Is it normal for MySQL to die under high load? Additionally, I'd love to know: Is it likely that something in my build is causing mysqld to crash (given that there are no other problems whatsoever with the build)? Since I increased the table cache it has become more stable -- is this the expected behaviour? Should the increased table_cache have made any difference at all? *** I don't want to ask leading questions, but... is it possible that we are hitting, say 200 processes, and when they all start using their 5M sort buffers (see below) that we're running out of memeory and causing MySQL to die? If so, is there any way to avoid having MySQL die? I'd rather a query take 3 minutes than kill the daemon... *** Below is my MySQL config, and some other pertinent data. Please let me know if I can provide any further information to help answer above questions. Also, if anyone has advice on how to make MySQL more stable, please let me know. Thanks in advance. --Other stuff-- MySQL built with: root@local-dbmaster-test:/usr/local/src/mysql-4.0.2-alpha/BUILD# ./compile-pentium --with-other-libc=/usr/local/mysql-glibc-2.2.5 --prefix=/usr/local glibc-2.2.5 built with: root@local-dbmaster-test:/usr/local/src/glibc-2.2.5# ./configure --enable-add-ons=linuxthreads --prefix=/usr/local/mysql-glibc-2.2.5 --enable-kernel=2.4.18 gcc-2.95.4 (debian stable package) was used to build both kernel is running patched to enable a higher open file limit per process (up to 4096) --/etc/my.cnf-- [mysqld] skip-locking set-variable= max_connections=2500 set-variable= query_cache_size=64M set-variable= wait_timeout=3600 set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=3000 # NOTE: Record buffer and Sort buffer are NOT shared among threads # They are per-thread memory buffers. set-variable= sort_buffer=5M set-variable= record_buffer=5M set-variable= thread_cache=8 set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M set-variable= long_query_time=4 tmpdir=/data/tmp user=mysql #log=/usr/local/var/mysqld.log log-slow-queries=/usr/local/var/slow-queries.log pid-file=/usr/local/var/mysqld.pid server-id=1 #master-host=host #master-user=user #master-password=secret #log-bin #binlog-do-db=db [mysql] set-variable= max_allowed_packet=1M [mysqldump] set-variable= max_allowed_packet=1M [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout [safe_mysqld] open-files-limit=8192 err-log=/usr/local/var/mysqld.err -- Shane Allen [EMAIL PROTECTED] sell.com : Buy Sell Anything http://www.sell.com - 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: Crashing under high load
On Fri, Aug 02, 2002 at 10:59:18PM +0200, Georg Richter wrote: On Friday, 2. August 2002 20:48, Shane Allen wrote: I digress... at this point, the question that plagues me is: Is it normal for MySQL to die under high load? you have a lot of programs, which prefer to die under heavy load. The main question is not why they die, the question is why is the load so high? I hope you already analyzed your system, your mysql-server and your queries and can could give us some more additional information. Well, most programs, in my experience, will keep running, even if load is 200, they just run slowly since they aren't getting processor load. As for why things are busy on the box, well, we run a fairly popular site... we have a primary database server that handles a large majority of our queries. Discussions about setting up a cluster or some other such thing aside, this server handles something to the tune of 3000 queries every five minutes during our off hours. During peak times, it's hit over 100k queries in five minutes, where quires == selects + updates + inserts, etc. We have spent a good deal of time lately optimizing what few queries were not already as good as they can be, but because of the large amounts of data we're dealing with, we sometimes join across 5 tables -- not much we can do about that. We're also performing a fairly extensive audit of our processes to reduce the number of necessary queries. We're also running MySQL databases on all of our front-end webservers that store non-changing data, like ISO country codes and such. All these things are intended to keep the load as low as possible. So, clustering aside (it's not an option right now), I am concerned about why MySQL dies... syslog and ntpd, for example, doesn't die under high load. On our front-end webservers, neither Apache nor cron die. So why is MySQL dying? Which takes me back to my original set of questions... The gentleman on PHPBuilder (http://www.phpbuilder.com/columns/tim2705.php3) (again, I acknowledge this is an old article) says flat out that MySQL tends to die under high load. Also, when I say high load, I am talking 15-30. I have yet to see an application (other than MySQL when it's loaded down with queries) die under this level of load. So, if there is additional information that you would like in order to help us solve this problem, I will be happy to provide it. What would you like to know. Again, thanks in advance for any help you can provide. -- Shane Allen [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: Build errors with 3.23.51
After much testing, I have managed to overcome the limits on file descriptors and such
on linux; and have a mysql daemon that is fairly lean and able to support several
thousand simultaneous connections.
I still, though, would like to see what kind of improvment gcc-3.0.4 makes as opposed
to 2.95.
This build error that I had before (it misdetected the gethostbyname_r function) still
plagues me, and I have been unable to find a work around for it. Can anyone lend
assistance? Attached below (they've also come through the list before on this thread)
are the relevant lines from my config.log with regards to the gethost* function(s)
Thanks
On Sat, Jun 22, 2002 at 02:40:38AM -0500, Shane Allen wrote:
configure:12730: checking style of gethost* routines
configure:12773: gcc -c -O -DDBUG_OFF -O3 -felide-constructors -fno-exceptions
-fno-rtti
-fpermissive -I/usr/local/mysql-glibc-2.2.2/include conftest.cc 5
gcc: installation problem, cannot exec `cc1plus': No such file or directory
configure:12776: $? = 1
configure: failed program was:
#line 12750 configure
#include confdefs.h
#undef inline
#if !defined(SCO) !defined(__osf__) !defined(_REENTRANT)
#define _REENTRANT
#endif
#include pthread.h
#include sys/types.h
#include sys/socket.h
#include netinet/in.h
#include arpa/inet.h
#include netdb.h
int
main ()
{
int skr;
struct hostent *foo = gethostbyaddr_r((const char *) 0,
0, 0, (struct hostent *) 0, (char *) NULL, 0, skr); return (foo == 0);
;
return 0;
}
configure:12792: result: other
configure:12813: checking style of gethostname_r routines
configure:12854: gcc -c -O -DDBUG_OFF -O3 -felide-constructors -fno-exceptions
-fno-rtti
-fpermissive -I/usr/local/mysql-glibc-2.2.2/include conftest.cc 5
gcc: installation problem, cannot exec `cc1plus': No such file or directory
configure:12857: $? = 1
configure: failed program was:
#line 12830 configure
#include confdefs.h
#undef inline
#if !defined(SCO) !defined(__osf__) !defined(_REENTRANT)
#define _REENTRANT
#endif
#include pthread.h
#include sys/types.h
#include sys/socket.h
#include netinet/in.h
#include arpa/inet.h
#include netdb.h
int
main ()
{
int skr;
skr = gethostbyname_r((const char *) 0,
(struct hostent*) 0, (char*) 0, 0, (struct hostent **) 0, skr);
;
return 0;
}
configure:12873: result: other
configure:12892: checking 3 argument to gethostname_r routines
configure:12932: gcc -c -O -DDBUG_OFF -O3 -felide-constructors -fno-exceptions
-fno-rtti
-fpermissive -I/usr/local/mysql-glibc-2.2.2/include conftest.cc 5
gcc: installation problem, cannot exec `cc1plus': No such file or directory
configure:12935: $? = 1
configure: failed program was:
#line 12909 configure
#include confdefs.h
#undef inline
#if !defined(SCO) !defined(__osf__) !defined(_REENTRANT)
#define _REENTRANT
#endif
#include pthread.h
#include sys/types.h
#include sys/socket.h
#include netinet/in.h
#include arpa/inet.h
#include netdb.h
int
main ()
{
int skr;
skr = gethostbyname_r((const char *) 0, (struct hostent*) 0, (struct hostent_data*)
0);
;
return 0;
}
On Sat, Jun 22, 2002 at 03:09:15AM +0200, Jocelyn Fournier wrote:
Hi,
It seems to be linked with the gethostname_r detection
checking style of gethostname_r routines... other
I think you should have the following line instead :
checking style of gethostname_r routines... glibc2
Can you take a look at the config.log file and tell us what is wrong ?
(in my case I have the following lines :
configure:15261: checking style of gethostname_r routines
configure:15309:
gcc -c -O3 -DDBUG_OFF -Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcommen
t -W -Wchar-subscripts -Wformat -Wparen
theses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wre
order -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-con
structors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-poi
nter -fno-implicit-templates -fno-exceptions -fno-rtti
-DUSE_MYSYS_NEW -Werror conftest.cc 5
configure:15312: $? = 0
configure:15315: test -s conftest.o
configure:15318: $? = 0
configure:15328: result: glibc2
)
Regards,
Jocelyn
-
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: Build errors with 3.23.51
Below are what I believe to be the relevant lines from the config.log...
Running Debian, 2.4.19pre1 kernel, gcc 3.0.4, glib-2.2.2 with linuxthreads
and the mysql-supplied linuxthreads patch.
After re-reading my original post as well, I realize that I forgot to mention that I
compiled the glibc libraries with gcc 2.95; configure would fail saying that gcc 3.0.4
was a bad compiler, and I'm not good enough with autoconf to hack the check out of the
configure script (despite several attempts to do so). I don't know if that is
information worth noting or not; but it can't hurt I suppose.
Thanks again.
configure:12730: checking style of gethost* routines
configure:12773: gcc -c -O -DDBUG_OFF -O3 -felide-constructors -fno-exceptions
-fno-rtti
-fpermissive -I/usr/local/mysql-glibc-2.2.2/include conftest.cc 5
gcc: installation problem, cannot exec `cc1plus': No such file or directory
configure:12776: $? = 1
configure: failed program was:
#line 12750 configure
#include confdefs.h
#undef inline
#if !defined(SCO) !defined(__osf__) !defined(_REENTRANT)
#define _REENTRANT
#endif
#include pthread.h
#include sys/types.h
#include sys/socket.h
#include netinet/in.h
#include arpa/inet.h
#include netdb.h
int
main ()
{
int skr;
struct hostent *foo = gethostbyaddr_r((const char *) 0,
0, 0, (struct hostent *) 0, (char *) NULL, 0, skr); return (foo == 0);
;
return 0;
}
configure:12792: result: other
configure:12813: checking style of gethostname_r routines
configure:12854: gcc -c -O -DDBUG_OFF -O3 -felide-constructors -fno-exceptions
-fno-rtti
-fpermissive -I/usr/local/mysql-glibc-2.2.2/include conftest.cc 5
gcc: installation problem, cannot exec `cc1plus': No such file or directory
configure:12857: $? = 1
configure: failed program was:
#line 12830 configure
#include confdefs.h
#undef inline
#if !defined(SCO) !defined(__osf__) !defined(_REENTRANT)
#define _REENTRANT
#endif
#include pthread.h
#include sys/types.h
#include sys/socket.h
#include netinet/in.h
#include arpa/inet.h
#include netdb.h
int
main ()
{
int skr;
skr = gethostbyname_r((const char *) 0,
(struct hostent*) 0, (char*) 0, 0, (struct hostent **) 0, skr);
;
return 0;
}
configure:12873: result: other
configure:12892: checking 3 argument to gethostname_r routines
configure:12932: gcc -c -O -DDBUG_OFF -O3 -felide-constructors -fno-exceptions
-fno-rtti
-fpermissive -I/usr/local/mysql-glibc-2.2.2/include conftest.cc 5
gcc: installation problem, cannot exec `cc1plus': No such file or directory
configure:12935: $? = 1
configure: failed program was:
#line 12909 configure
#include confdefs.h
#undef inline
#if !defined(SCO) !defined(__osf__) !defined(_REENTRANT)
#define _REENTRANT
#endif
#include pthread.h
#include sys/types.h
#include sys/socket.h
#include netinet/in.h
#include arpa/inet.h
#include netdb.h
int
main ()
{
int skr;
skr = gethostbyname_r((const char *) 0, (struct hostent*) 0, (struct hostent_data*)
0);
;
return 0;
}
On Sat, Jun 22, 2002 at 03:09:15AM +0200, Jocelyn Fournier wrote:
Hi,
It seems to be linked with the gethostname_r detection
checking style of gethostname_r routines... other
I think you should have the following line instead :
checking style of gethostname_r routines... glibc2
Can you take a look at the config.log file and tell us what is wrong ?
(in my case I have the following lines :
configure:15261: checking style of gethostname_r routines
configure:15309:
gcc -c -O3 -DDBUG_OFF -Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcommen
t -W -Wchar-subscripts -Wformat -Wparen
theses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wre
order -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-con
structors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-poi
nter -fno-implicit-templates -fno-exceptions -fno-rtti
-DUSE_MYSYS_NEW -Werror conftest.cc 5
configure:15312: $? = 0
configure:15315: test -s conftest.o
configure:15318: $? = 0
configure:15328: result: glibc2
)
Regards,
Jocelyn
- Original Message -
From: Shane Allen [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, June 22, 2002 3:05 AM
Subject: Build errors with 3.23.51
Running Debian, 2.4.19pre1 kernel, gcc 3.0.4, glib-2.2.2 with linuxthreads
and the mysql-supplied linuxthreads patch.
I'm using the following configure line:
CFLAGS=-O3 -march=pentiumpro CXX=gcc
CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti
/configure --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-sta
tic --enable-thread-safe-client --with-other-libc=/usr/local/mysql-glibc-2.2
.2
I have not successfully built against this glibc yet, however I did not
receive any unusual warnings, and no errors occured during any part of the
build.
Following is the output of the configure:
checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking target system type... i686-pc-linux-gnu
checking
Build errors with 3.23.51
=\/usr/local/share/mysql\ -DDONT_USE_RAID -I./../include -I../include -I./.. -I.. -I.. -O3 -DDBUG_OFF -O3 -march=pentiumpro -I/usr/local/mysql-glibc-2.2.2/include -c libmysql.c -fPIC -DPIC -o .libs/libmysql.lo libmysql.c: In function `mysql_real_connect': libmysql.c:1324: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type libmysql.c:1324: too few arguments to function `gethostbyname_r' libmysql.c:1325: warning: assignment makes pointer from integer without a cast make[2]: *** [libmysql.lo] Error 1 make[2]: Leaving directory `/usr/local/src/mysql-3.23.51/libmysql_r' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/local/src/mysql-3.23.51' make: *** [all-recursive-am] Error 2 root@dbmaster:/usr/local/src/mysql-3.23.51# Let me know if I need to provide any further info. TIA -- Shane Allen [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
Question about Fulltext searches/GWS_IDF
I checked the archives for this list and saw that in 2000 Sergei recommended changing GWS_IN_USE to GWS_IDF to disable the 50% weighting mechanism. The MySQL manual, however says to change it from GWS_PROB to GWS_FREQ. I'm inclined to go with the developer instead of documentation ;), but I'm wondering why the discrepancy? -- Shane Allen [EMAIL PROTECTED] sell.com : Buy Sell Anything http://www.sell.com - 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
