Re: RE - Order By Problem
If surname is a field, then use it without the single quotes ('), otherwise it is treated as a literal string and 0 is the correct result: select locate(' ',surname,1) from advisers andy thomas wrote: Yes, this is the approach I was thinking of using but: select locate(' ','surname',1) from advisers just returns 0 for all records, whether or not they contain the ' ' space substring. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables + SUM + GROUP BY = strange behavior
well, it seems to be fine without SUM and GROUP BY... E.g., SELECT @a:=Charge, @b:=Cost, @[EMAIL PROTECTED] as Margin ... produces expected results. Emmett Bishop wrote: Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... The general rule is to never assign and use the same variable in the same statement. -- Tripp --- Vadim P. [EMAIL PROTECTED] wrote: Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT -LEFT(CallTime,10) AS CallDate, -@a := SUM(Charge), -@b := SUM(Cost), -@a - @b, -@a, -@b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables + SUM + GROUP BY = strange behavior
Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT - LEFT(CallTime,10) AS CallDate, - @a := SUM(Charge), - @b := SUM(Cost), - @a - @b, - @a, - @b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++---+-+-++- -+ | CallDate | @a := SUM(Charge) | @b := SUM(Cost) | @a - @b | @a | @b | ++---+-+-++- -+ . | 2004-03-01 | 621.059 | 249.310 | 30.882 | 39.512 | 8.63 | | 2004-02-29 |54.620 | 17.660 | 30.882 | 39.512 | 8.63 | | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | | 2004-02-27 | 622.282 | 248.920 | 30.882 | 39.512 | 8.63 | | 2004-02-26 | 607.274 | 277.100 | 30.882 | 39.512 | 8.63 | | 2004-02-25 | 709.698 | 308.580 | 30.882 | 39.512 | 8.63 | | 2004-02-24 | 783.210 | 298.560 | 30.882 | 39.512 | 8.63 | | 2004-02-23 | 799.764 | 252.890 | 30.882 | 39.512 | 8.63 | .
User variables + SUM + GROUP BY = strange behavior
Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT - LEFT(CallTime,10) AS CallDate, - @a := SUM(Charge), - @b := SUM(Cost), - @a - @b, - @a, - @b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Killing overactive mysql selects
Start another Mysql shell session; then do show processlist; identify the thread ID of the offending process, then do kill ID. Not sure how graceful this method is, though. I use it to kill overlooked hanging connections from time to time. Cheers, Vadim. Scott Haneda wrote: Every now and then I am not thking and I am working on the mysql shell and I send it a select name from table; Just so happens there are a few hundred K of records. What I meant to do is LIMIT 10; Anyway, how do I get mysql to stop, the best I can do is control-C, but that leaves mysql altogether and I have to log back in again. Also, sometimes I am able to use the tab key to auto-complete field and table names, this is really handy, sometimes it works and sometimes not, how do I get this all the time? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT.. ORDER BY.. DESC - bug??
After a bit of digging, found out that this is indeed a bug: http://bugs.mysql.com/bug.php?id=1274 Strangely enough, it is listed as CLOSED. Now - this is hard to explain, but it looks like MySQL developers have no answer or interest in dealing with this problem. The fact is - adding an index improves SELECT .. ORDER BY .. ASC, but slows down SELECT.. ORDER BY .. DESC at least by a factor of 10. If this is not a bug, what is it? Vadim P. wrote: Hi all, Sorry to be so persistent, but I am bringing this up again since noone from the MySQL development team commented on my previous post, and the issue seems very serious, to the point I may start looking to switching away from MySQL, so - please, please, shed some light on this issue!!! The problem is that the performance of SELECT DISTINCT... query seems to depend on the order the results are sorted, DESC being more than 10x slower than ASC (14.77 sec vs. 1.06 sec). == Here is a more detailed description: The table has over 700,000 records. MySQL 4.0.18 running under OpenBSD 3.4 Intel/PIII 900MHz/2GB RAM mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle DESC; +--+ | billingCycle | +--+ | 2004-04-01 | | 2004-03-01 | | 2004-02-01 | | 2004-01-01 | | 2003-12-01 | | 2003-11-01 | | 2003-10-01 | | 2003-09-01 | | 2003-08-01 | | 2003-07-01 | | 2003-06-01 | | 2003-05-01 | | 2003-04-01 | | 2003-01-01 | +--+ 14 rows in set (14.77 sec) mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle; +--+ | billingCycle | +--+ | 2003-01-01 | | 2003-04-01 | | 2003-05-01 | | 2003-06-01 | | 2003-07-01 | | 2003-08-01 | | 2003-09-01 | | 2003-10-01 | | 2003-11-01 | | 2003-12-01 | | 2004-01-01 | | 2004-02-01 | | 2004-03-01 | | 2004-04-01 | +--+ 14 rows in set (1.06 sec) === Thanks in advance! Vadim. mysql query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT.. ORDER BY.. DESC - bug??
Hi all, Sorry to be so persistent, but I am bringing this up again since noone from the MySQL development team commented on my previous post, and the issue seems very serious, to the point I may start looking to switching away from MySQL, so - please, please, shed some light on this issue!!! The problem is that the performance of SELECT DISTINCT... query seems to depend on the order the results are sorted, DESC being more than 10x slower than ASC (14.77 sec vs. 1.06 sec). == Here is a more detailed description: The table has over 700,000 records. MySQL 4.0.18 running under OpenBSD 3.4 Intel/PIII 900MHz/2GB RAM mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle DESC; +--+ | billingCycle | +--+ | 2004-04-01 | | 2004-03-01 | | 2004-02-01 | | 2004-01-01 | | 2003-12-01 | | 2003-11-01 | | 2003-10-01 | | 2003-09-01 | | 2003-08-01 | | 2003-07-01 | | 2003-06-01 | | 2003-05-01 | | 2003-04-01 | | 2003-01-01 | +--+ 14 rows in set (14.77 sec) mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle; +--+ | billingCycle | +--+ | 2003-01-01 | | 2003-04-01 | | 2003-05-01 | | 2003-06-01 | | 2003-07-01 | | 2003-08-01 | | 2003-09-01 | | 2003-10-01 | | 2003-11-01 | | 2003-12-01 | | 2004-01-01 | | 2004-02-01 | | 2004-03-01 | | 2004-04-01 | +--+ 14 rows in set (1.06 sec) === Thanks in advance! Vadim. mysql query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER DESC vs. ORDER ASC exec time
Hi all, Just noticed that a simple query that returns only 14 rows is 10X slower when ORDER .. DESC is used compared to ORDER .. ASC. The table has about 700,000 records, indexed on the field the table is being ordered by. Is this expected behavior? MySQL 4.0.18 running under OpenBSD 3.4 Intel/PIII 900MHz/2GB RAM = mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle DESC; +--+ | billingCycle | +--+ | 2004-04-01 | | 2004-03-01 | | 2004-02-01 | | 2004-01-01 | | 2003-12-01 | | 2003-11-01 | | 2003-10-01 | | 2003-09-01 | | 2003-08-01 | | 2003-07-01 | | 2003-06-01 | | 2003-05-01 | | 2003-04-01 | | 2003-01-01 | +--+ 14 rows in set (14.77 sec) mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle; +--+ | billingCycle | +--+ | 2003-01-01 | | 2003-04-01 | | 2003-05-01 | | 2003-06-01 | | 2003-07-01 | | 2003-08-01 | | 2003-09-01 | | 2003-10-01 | | 2003-11-01 | | 2003-12-01 | | 2004-01-01 | | 2004-02-01 | | 2004-03-01 | | 2004-04-01 | +--+ 14 rows in set (1.06 sec) mysql mysql query database -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER DESC vs. ORDER ASC exec time
The timing is consistent both ways - have tried both queries back-to-back alternatively many times. I'm pretty sure that it is not caching that causes the discrepancy. So, this turns out to be a known issue after all... Somehow I missed it. Thanks, Donny! Vadim. - Original Message - From: Donny Simonton To: 'Vadim P.' ; [EMAIL PROTECTED] Sent: Monday, March 29, 2004 9:34 PM Subject: RE: ORDER DESC vs. ORDER ASC exec time Personally, it's an unexpected flaw that I hope one day will be fixed. But I'm not holding my breath, even though they seem to be planning for it. But it could also be because of query-cache. To benchmark something like this, you really need to add SQL_NO_CACHE to your select statement to get accurate numbers. Donny -Original Message- From: Vadim P. [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 8:16 PM To: [EMAIL PROTECTED] Subject: ORDER DESC vs. ORDER ASC exec time Hi all, Just noticed that a simple query that returns only 14 rows is 10X slower when ORDER .. DESC is used compared to ORDER .. ASC. The table has about 700,000 records, indexed on the field the table is being ordered by. Is this expected behavior? MySQL 4.0.18 running under OpenBSD 3.4 Intel/PIII 900MHz/2GB RAM = mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle DESC; +--+ | billingCycle | +--+ | 2004-04-01 | | 2004-03-01 | | 2004-02-01 | | 2004-01-01 | | 2003-12-01 | | 2003-11-01 | | 2003-10-01 | | 2003-09-01 | | 2003-08-01 | | 2003-07-01 | | 2003-06-01 | | 2003-05-01 | | 2003-04-01 | | 2003-01-01 | +--+ 14 rows in set (14.77 sec) mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle; +--+ | billingCycle | +--+ | 2003-01-01 | | 2003-04-01 | | 2003-05-01 | | 2003-06-01 | | 2003-07-01 | | 2003-08-01 | | 2003-09-01 | | 2003-10-01 | | 2003-11-01 | | 2003-12-01 | | 2004-01-01 | | 2004-02-01 | | 2004-03-01 | | 2004-04-01 | +--+ 14 rows in set (1.06 sec) mysql mysql query database -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: High load with a few queries
Chris, I would consider loading this script only once and establishing a single connection to the DB server and then making it loop with a time delay waiting for data to be inserted into the DB - that's pretty much what it does, right? The thing is that if you invoke the script every time you have a new portion of data to be uploaded, first you have to start Perl, then have it load the script, then establish a new connection to the DB server - all these steps are totally redundant, but they do overload your box. Hope this helps. Vadim. Chris wrote: Hi Guys, Iv got a script that iv been working on written in perl which interfaces to mysql via the dbi module.. The script only does two selects within the entire script, so I didn't think it would be too taxing on the machine. Turns out that when the script is executed around 100 times a minute, the load on the machine skyrockets (load average around 42.10). Obviously this is not good, so my question is, where can I start on optimizing mysql for high usage (if you can even call it that)? Basically the first select statement goes and selects a column value from a row that matches another column based on a username string. so for example: select data from mytable where username='theuser' then the second statement does: insert into myothertable values('blah','blah') mytable is small, no more than like 140 rows. myothertable is large, and the table where all data gets dumped to. As you can see, this is pretty basic. I never did much with queries being sent this fast (100+ a minute) so any advise is welcome. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER DESC vs. ORDER ASC exec time
This is correct, the index is not packed. Also, exactly the same behavior can be observed if GROUP BY is used instead of DISTINCT - if this makes my case any clearer. The table gets checked for consistensy and optimized every hour. I wonder if any members of MySQL development crew could shed some light on the issue? Just in case someone can catch an obvious mistake, here is the server portion of /etc/my.cnf: [mysqld] key_buffer_size = 32M max_allowed_packet = 8M table_cache = 128 sort_buffer_size = 4M read_buffer_size = 1M net_buffer_length = 32K myisam_sort_buffer_size = 4M max_connections = 200 set-variable = thread_stack=256k Matt W wrote: Hi, MySQL 4+ can use indexes for ORDER BY ... DESC (3.23 can only in some cases) in every case that ASC can. However, reading a packed index in reverse order is slower. I don't think your index is packed, though, if it's a date-type column, unless you've specified PACK_KEYS in your CREATE TABLE. Better check with SHOW INDEX FROM PhoneCalls. That leaves the DISTINCT clause as the suspect. The EXPLAINs look the same with ASC/DESC on an equivalent query I just tried. So maybe something is making DISTINCT + reverse index scan slow even if it's not packed... Matt - Original Message - From: Vadim P. Sent: Monday, March 29, 2004 8:15 PM Subject: ORDER DESC vs. ORDER ASC exec time Hi all, Just noticed that a simple query that returns only 14 rows is 10X slower when ORDER .. DESC is used compared to ORDER .. ASC. The table has about 700,000 records, indexed on the field the table is being ordered by. Is this expected behavior? MySQL 4.0.18 running under OpenBSD 3.4 Intel/PIII 900MHz/2GB RAM = mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle DESC; +--+ | billingCycle | +--+ | 2004-04-01 | | 2004-03-01 | | 2004-02-01 | | 2004-01-01 | | 2003-12-01 | | 2003-11-01 | | 2003-10-01 | | 2003-09-01 | | 2003-08-01 | | 2003-07-01 | | 2003-06-01 | | 2003-05-01 | | 2003-04-01 | | 2003-01-01 | +--+ 14 rows in set (14.77 sec) mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle; +--+ | billingCycle | +--+ | 2003-01-01 | | 2003-04-01 | | 2003-05-01 | | 2003-06-01 | | 2003-07-01 | | 2003-08-01 | | 2003-09-01 | | 2003-10-01 | | 2003-11-01 | | 2003-12-01 | | 2004-01-01 | | 2004-02-01 | | 2004-03-01 | | 2004-04-01 | +--+ 14 rows in set (1.06 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange fulltext behavior
This simply can't be correct - if a match produces 0 score (no relevance), the whole condition part after AND in this example could be reduced to 0 (=FALSE), hence the query *should* produce 0 records: SELECT ISBN,TITLE FROM tBooks WHERE tBooks.ISBN=1876340436 AND 0; ...and here is a real-life example of the correct behavior: mysql select indatetime from archivel where addr=FN20 and match (header) against (not_a_word); Empty set (0.00 sec) mysql select indatetime from archivel where addr=FN25 and match (header) against (reagan); ++ | 1988120101 | | 19990610115359 | | 2515123714 | | 1988112201 | | 1988112201 | ++ 5 rows in set (0.08 sec) Regards, Vadim P. Victoria Reznichenko wrote: Przemyslaw, Wednesday, May 22, 2002, 2:02:52 AM, you wrote: PP SELECT ISBN,TITLE FROM tBooks PP WHERE tBooks.ISBN=1876340436 PP AND MATCH (tBooks.TITLE) AGAINST (britannica) PP ++-+ PP | ISBN | TITLE | PP ++-+ PP | 1876340436 | 2000 Lonely Planet Calendar | PP ++-+ PP 1 row in set (0.00 sec) PP Why is it so? I suspected to get an empty result set. PP MYSQL Ver 11.16 Distrib 3.23.49, for Win95/Win98 (i32) MATCH() returnes you relevance value. So, it is always true in WHERE clause. As a result you get result where ISBN=1876340436. In your case you can re-write you query: SELECT ISBN,TITLE, MATCH (tBooks.TITLE) AGAINST (britannica) as aa FROM tBooks WHERE tBooks.ISBN=1876340436 HAVING aa0; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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 - 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: Bug in fulltext search in latest mysql-4.0.2 source
Sergei Golubchik wrote: rebuild your indexes (the proper way to do it is to issue REPAIR table_name USE_FRM Sergei, would ALTER TABLE.. DROP INDEX.. ADD FULLTEXT... do the same trick? TIA, Vadim. /* sql, mysql to satisfy the spam police */ - 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 load problems - gcc-2.96?
Mike, what kind of problem? What exactly goes wrong with PHP4? Thanks, Vadim Mike Robinson wrote: I am able to reproduce this problem very easily, including on Redhat-7.2. Basically, any app using the libmysqlclient.so compiled with gcc-2.96 connecting to a server compiled with gcc-2.96 will experience problems. This is particularly the case with PHP4. - 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 load problems - gcc-2.96?
Mike, BTW, have you been able to build Mysql 4.0 under RH7.x from the development tree lately? I keep getting some missing definitions messages as of last week, when I performed a regular 'bk resync'. Prior to that, all had been just fine. TIA, Vadim P. Mike Robinson wrote: Hello to all, I've had some severe issues on redhat-7.x boxes and mysql compiled from source with gcc-2.96 much like the warning states on the download page. On these boxes, more than say 20-25 concurrent or near-concurrent connects produced unexpected results with PHP4. Replacing the binaries compiled from source with binaries from mysql.com did not in itself provide the cure. It wasn't until I recompiled PHP with the static client libs provided in the binary download did this problem go away. It seems that the client .so compiled with gcc-2.96 was the problem, but I could be wrong. The gcc-2.96 issue has been around since early 2000. I'm have to mention that I've read the statement put out by gnu on the gcc-2.96 issue and I think its a joke, but I'm wondering if there is any update on the official mysql take is on that whole thing. Since there seems to still be issues with gcc-3.nn, it appears this might get worse, or stay bad for quite a while, before it gets any better. Best Regards Mike Robinson - 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: Help! Error building 4.0.2 under RH Linux 7.2
Jurgen-- Strange thing is, I had been able to build MySQL 4.0 - up until 3 days ago - without any problem. All from the same Mysql development tree. At some point, the whole thing just fell apart after a source update. Also, there's no problem building v3.23 on the same box! It actually looks like some sort of configuration issue, with some config files gotten out of sync, but I can't be certain about this.. Thanks for the tip, though... Actually, I've been using gcc 2.96 for quite some time now, including building MySQL 4.0.x that is in production now (just had to use the new fulltext search features) - without any known issues... That's why I got somewhat too comfortable using v2.96... Regards, Vadim. crashke wrote: Hi Vadim, I don't know but have you read the instructions on the Mysql-website? To determine if you should be concerned about this compiler issue, execute gcc -v from the command prompt on your system. If the compiler reports version 2.96, then there is a problem (this is the case, for example on RH 7.x series or Mandrake 8.x). In this case, you should not try to compile your own binary before downgrading to one of the compilers mentioned above. You should also NOT use the MySQL server provided with your distribution -- as this copy of MySQL was compiled with the same ill-advised compiler version. I've seen your files and its reporting you have a 2.96 compiler greetings Jurgen Campforts Meidoornlaan 2/1 2200 Herentals 0496/60.25.75 http://www.scnoorderwijk.be http://crashke.no-ip.biz -Oorspronkelijk bericht- Van: Vadim P. [mailto:[EMAIL PROTECTED]] Verzonden: donderdag 9 mei 2002 23:07 Aan: [EMAIL PROTECTED] Onderwerp: Re: Help! Error building 4.0.2 under RH Linux 7.2 Just tried building Mysql-3.x.x cloned from the Development Tree on the same machine - works like a charm. Still no luck with 4.0.2 Is there any known issue regarding building 4.0.2 under RedHat7.2? Thanks, Vadim. Vadim P. wrote: The Dev. Tree Source MySQL 4.0 no longer builds on this machine, giving lots of errors. Had been able to build from the same source w/o any problems up until a few days ago, when I 'bk resync'ed the source. Any ideas? Thanks for your help! -- A snapshot of build transcript. Invoked by ./BUILD/compile-pentium. -- [sql]# make Making all in share make[1]: Entering directory `/root/mysql-4.0/sql/share' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/root/mysql-4.0/sql/share' make[1]: Entering directory `/root/mysql-4.0/sql' gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DHAVE_CONFIG_H -I../innobase/include -I./../include -I./../regex -I. -I../include -I. -O3 -DDBUG_OFF -Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -fno-implicit-templates -fno-exceptions -fno-rtti -c mysqld.cc cc1plus: warning: Ignoring command line option '-Wimplicit-function-dec' cc1plus: warning: Ignoring command line option '-Wimplicit-int' mysqld.cc: In function `void *handle_connections_sockets (void *)': mysqld.cc:2425: warning: `my_socket new_sock' might be used uninitialized in this function mysqld.cc: At top level: mysqld.cc:3045: `disconnect_slave_event_count' was not declared in this scope mysqld.cc:3046: `disconnect_slave_event_count' was not declared in this scope mysqld.cc:3050: `abort_slave_event_count' was not declared in this scope mysqld.cc:3050: `abort_slave_event_count' was not declared in this scope mysqld.cc:3053: `max_binlog_dump_events' was not declared in this scope mysqld.cc:3053: `max_binlog_dump_events' was not declared in this scope mysqld.cc:3056: `opt_sporadic_binlog_dump_fail' was not declared in this scope mysqld.cc:3057: `opt_sporadic_binlog_dump_fail' was not declared in this scope mysqld.cc:3299: `OPT_INNODB_MIRRORED_LOG_GROUPS' was not declared in this scope mysqld.cc:3304: `OPT_INNODB_LOG_FILES_IN_GROUP' was not declared in this scope mysqld.cc:3308: `OPT_INNODB_LOG_FILE_SIZE' was not declared in this scope mysqld.cc:3312: `OPT_INNODB_LOG_BUFFER_SIZE' was not declared in this scope mysqld.cc:3316: `OPT_INNODB_BUFFER_POOL_SIZE' was not declared in this scope mysqld.cc:3320: `OPT_INNODB_ADDITIONAL_MEM_POOL_SIZE' was not declared in this scope mysqld.cc:3325: `OPT_INNODB_FILE_IO_THREADS
Help! Error building 4.0.2 under RH Linux 7.2
The Dev. Tree Source MySQL 4.0 no longer builds on this machine, giving lots of errors. Had been able to build from the same source w/o any problems up until a few days ago, when I 'bk resync'ed the source. Any ideas? Thanks for your help! -- A snapshot of build transcript. Invoked by ./BUILD/compile-pentium. -- [sql]# make Making all in share make[1]: Entering directory `/root/mysql-4.0/sql/share' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/root/mysql-4.0/sql/share' make[1]: Entering directory `/root/mysql-4.0/sql' gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DHAVE_CONFIG_H -I../innobase/include -I./../include -I./../regex -I. -I../include -I. -O3 -DDBUG_OFF -Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -fno-implicit-templates -fno-exceptions -fno-rtti -c mysqld.cc cc1plus: warning: Ignoring command line option '-Wimplicit-function-dec' cc1plus: warning: Ignoring command line option '-Wimplicit-int' mysqld.cc: In function `void *handle_connections_sockets (void *)': mysqld.cc:2425: warning: `my_socket new_sock' might be used uninitialized in this function mysqld.cc: At top level: mysqld.cc:3045: `disconnect_slave_event_count' was not declared in this scope mysqld.cc:3046: `disconnect_slave_event_count' was not declared in this scope mysqld.cc:3050: `abort_slave_event_count' was not declared in this scope mysqld.cc:3050: `abort_slave_event_count' was not declared in this scope mysqld.cc:3053: `max_binlog_dump_events' was not declared in this scope mysqld.cc:3053: `max_binlog_dump_events' was not declared in this scope mysqld.cc:3056: `opt_sporadic_binlog_dump_fail' was not declared in this scope mysqld.cc:3057: `opt_sporadic_binlog_dump_fail' was not declared in this scope mysqld.cc:3299: `OPT_INNODB_MIRRORED_LOG_GROUPS' was not declared in this scope mysqld.cc:3304: `OPT_INNODB_LOG_FILES_IN_GROUP' was not declared in this scope mysqld.cc:3308: `OPT_INNODB_LOG_FILE_SIZE' was not declared in this scope mysqld.cc:3312: `OPT_INNODB_LOG_BUFFER_SIZE' was not declared in this scope mysqld.cc:3316: `OPT_INNODB_BUFFER_POOL_SIZE' was not declared in this scope mysqld.cc:3320: `OPT_INNODB_ADDITIONAL_MEM_POOL_SIZE' was not declared in this scope mysqld.cc:3325: `OPT_INNODB_FILE_IO_THREADS' was not declared in this scope mysqld.cc:3329: `OPT_INNODB_LOCK_WAIT_TIMEOUT' was not declared in this scope mysqld.cc:: `OPT_INNODB_THREAD_CONCURRENCY' was not declared in this scope mysqld.cc:3337: `OPT_INNODB_FORCE_RECOVERY' was not declared in this scope make[1]: *** [mysqld.o] Error 1 make[1]: Leaving directory `/root/mysql-4.0/sql' make: *** [all-recursive] Error 1 -- ./configure output: -- loading cache ./config.cache checking host system type... i686-pc-linux-gnu checking target system type... i686-pc-linux-gnu checking build system type... i686-pc-linux-gnu checking for a BSD compatible install... (cached) /usr/bin/install -c checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) yes checking for working aclocal... found checking for working autoconf... found checking for working automake... found checking for working autoheader... found checking for working makeinfo... found checking whether to enable maintainer-specific portions of Makefiles... no checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) yes checking for gawk... (cached) gawk checking for gcc... (cached) gcc checking whether the C compiler (gcc ) works... yes checking whether the C compiler (gcc ) is a cross-compiler... no checking whether we are using GNU C... (cached) yes checking whether gcc accepts -g... (cached) yes checking for c++... (cached) gcc checking whether the C++ compiler (gcc ) works... yes checking whether the C++ compiler (gcc ) is a cross-compiler... no checking whether we are using GNU C++... (cached) yes checking whether gcc accepts -g... (cached) yes checking how to run the C preprocessor... (cached) gcc -E checking C Compiler version... gcc 2.96 checking C++ compiler version... gcc 2.96 checking for ranlib... (cached) ranlib checking for ld used by GCC... (cached) /usr/bin/ld checking if the linker (/usr/bin/ld) is GNU ld... (cached) yes
Re: Help! Error building 4.0.2 under RH Linux 7.2
Just tried building Mysql-3.x.x cloned from the Development Tree on the same machine - works like a charm. Still no luck with 4.0.2 Is there any known issue regarding building 4.0.2 under RedHat7.2? Thanks, Vadim. Vadim P. wrote: The Dev. Tree Source MySQL 4.0 no longer builds on this machine, giving lots of errors. Had been able to build from the same source w/o any problems up until a few days ago, when I 'bk resync'ed the source. Any ideas? Thanks for your help! -- A snapshot of build transcript. Invoked by ./BUILD/compile-pentium. -- [sql]# make Making all in share make[1]: Entering directory `/root/mysql-4.0/sql/share' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/root/mysql-4.0/sql/share' make[1]: Entering directory `/root/mysql-4.0/sql' gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DHAVE_CONFIG_H -I../innobase/include -I./../include -I./../regex -I. -I../include -I. -O3 -DDBUG_OFF -Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -fno-implicit-templates -fno-exceptions -fno-rtti -c mysqld.cc cc1plus: warning: Ignoring command line option '-Wimplicit-function-dec' cc1plus: warning: Ignoring command line option '-Wimplicit-int' mysqld.cc: In function `void *handle_connections_sockets (void *)': mysqld.cc:2425: warning: `my_socket new_sock' might be used uninitialized in this function mysqld.cc: At top level: mysqld.cc:3045: `disconnect_slave_event_count' was not declared in this scope mysqld.cc:3046: `disconnect_slave_event_count' was not declared in this scope mysqld.cc:3050: `abort_slave_event_count' was not declared in this scope mysqld.cc:3050: `abort_slave_event_count' was not declared in this scope mysqld.cc:3053: `max_binlog_dump_events' was not declared in this scope mysqld.cc:3053: `max_binlog_dump_events' was not declared in this scope mysqld.cc:3056: `opt_sporadic_binlog_dump_fail' was not declared in this scope mysqld.cc:3057: `opt_sporadic_binlog_dump_fail' was not declared in this scope mysqld.cc:3299: `OPT_INNODB_MIRRORED_LOG_GROUPS' was not declared in this scope mysqld.cc:3304: `OPT_INNODB_LOG_FILES_IN_GROUP' was not declared in this scope mysqld.cc:3308: `OPT_INNODB_LOG_FILE_SIZE' was not declared in this scope mysqld.cc:3312: `OPT_INNODB_LOG_BUFFER_SIZE' was not declared in this scope mysqld.cc:3316: `OPT_INNODB_BUFFER_POOL_SIZE' was not declared in this scope mysqld.cc:3320: `OPT_INNODB_ADDITIONAL_MEM_POOL_SIZE' was not declared in this scope mysqld.cc:3325: `OPT_INNODB_FILE_IO_THREADS' was not declared in this scope mysqld.cc:3329: `OPT_INNODB_LOCK_WAIT_TIMEOUT' was not declared in this scope mysqld.cc:: `OPT_INNODB_THREAD_CONCURRENCY' was not declared in this scope mysqld.cc:3337: `OPT_INNODB_FORCE_RECOVERY' was not declared in this scope make[1]: *** [mysqld.o] Error 1 make[1]: Leaving directory `/root/mysql-4.0/sql' make: *** [all-recursive] Error 1 -- ./configure output: -- loading cache ./config.cache checking host system type... i686-pc-linux-gnu checking target system type... i686-pc-linux-gnu checking build system type... i686-pc-linux-gnu checking for a BSD compatible install... (cached) /usr/bin/install -c checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) yes checking for working aclocal... found checking for working autoconf... found checking for working automake... found checking for working autoheader... found checking for working makeinfo... found checking whether to enable maintainer-specific portions of Makefiles... no checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) yes checking for gawk... (cached) gawk checking for gcc... (cached) gcc checking whether the C compiler (gcc ) works... yes checking whether the C compiler (gcc ) is a cross-compiler... no checking whether we are using GNU C... (cached) yes checking whether gcc accepts -g... (cached) yes checking for c++... (cached) gcc checking whether the C++ compiler (gcc ) works... yes checking whether the C++ compiler (gcc ) is a cross-compiler... no checking whether we are using GNU C++... (cached) yes checking whether gcc accepts -g... (cached) yes
FULLTEXT search pattern syntax
Hi, It's not quite clear from the manual, but it appears that queries with FULLTEXT search patterns like this: MATCH (some_text_field) AGAINST ('(red pepper) (green bean)') don't produce the result desired, that is, multiple .. elements are not allowed, with or without parentheses... Could someone confirm, please? On a general note, it would be nice to have the formal syntax of FT search patterns in the manual. TIA, Vadim. [sql query - to satisfy the darn spam filter] - 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: FULLTEXT search pattern syntax
Of course, I forgot to mention 'IN BOOLEAN MODE'. ... MATCH (some_text_field) AGAINST ('(red pepper) (green bean)' IN BOOLEAN MODE) ... Vadim P. wrote: Hi, It's not quite clear from the manual, but it appears that queries with FULLTEXT search patterns like this: MATCH (some_text_field) AGAINST ('(red pepper) (green bean)') don't produce the result desired, that is, multiple .. elements are not allowed, with or without parentheses... Could someone confirm, please? On a general note, it would be nice to have the formal syntax of FT search patterns in the manual. TIA, Vadim. [sql query - to satisfy the darn spam filter] - 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: FULLTEXT search pattern syntax
Sergei, here: RH7.2/2.4.9-31/Dual P-III 866MHz/MySQL4.0.2 A snapshot from a live database, sorry, couldn't prepare a more detailed example: -- select tdate, left(header,125) from archivel where match (header) against ('donald rumsfeld' in boolean mode); == 188 rows in set (0.12 sec) -- select tdate, left(header,125) from archivel where match (header) against ('president bush' in boolean mode); == 1979 rows in set (2.40 sec) -- select tdate, left(header,125) from archivel where match (header) against ('president bush donald rumsfeld' in boolean mode); == 1979 rows in set (2.39 sec) -- select tdate, left(header,125) from archivel where match (header) against ('+president bush +donald rumsfeld' in boolean mode); == 1979 rows in set (2.39 sec) -- select tdate, left(header,125) from archivel where match (header) against ('+president bush +donald rumsfeld' in boolean mode); == 1979 rows in set (2.39 sec) -- select tdate, left(header,125) from archivel where match (header) against ('(president bush) (donald rumsfeld)' in boolean mode); == 3522 rows in set (2.61 sec) -- select tdate, left(header,125) from archivel where match (header) against ('+(president bush) +(donald rumsfeld)' in boolean mode); == 1979 rows in set (2.52 sec) -- select tdate, left(header,125) from archivel where match (header) against ('+(president bush) -(donald rumsfeld)' in boolean mode); 1979 rows in set (2.53 sec) -- select tdate, left(header,125) from archivel where match (header) against ('-(president bush) +(donald rumsfeld)' in boolean mode); == 1535 rows in set (2.45 sec) -- Thanks, Vadim. Sergei Golubchik wrote: Hi! On May 06, Vadim P. wrote: Hi, It's not quite clear from the manual, but it appears that queries with FULLTEXT search patterns like this: MATCH (some_text_field) AGAINST ('(red pepper) (green bean)') don't produce the result desired, that is, multiple .. elements are not allowed, with or without parentheses... Could you explain, what result is desired and what result you are getting ? Because, right now I can only say that multiple .. elements ARE allowed, of course. On a general note, it would be nice to have the formal syntax of FT search patterns in the manual. I do not understand, what is missing in the manual right now ? There is a list of operators, you can use any of them in whatever combination you want. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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: FULLTEXT search with hyphens in data
Just a thought - if your serial numbers have fixed format, then programmatically remove the hyphen and store the number without it. Add it back when displaying data on the screen. That way FULLTEXT will work on the whole number without splitting it into two parts. Vadim. Marty McCoy wrote: Hi, I'm performing a FULLTEXT search against varchar columns of one of our tables. One of our columns contains embedded serial numbers such as 02-123456 and 04-234567. The problem that when I do a FULLTEXT search against 02-123456, it brings back results with the following values: ... 99-123456 ... ... 01-123456 ... ... 02-123456 ... ... 05-123456 ... etc. First of all, I would like it only to bring back the exact match. But what's even more puzzling to me is that I would expect that the record containing the 02-123456 would have a higher MATCH value than the rest, since it matched 'exactly' and the others only partially matched. But often times other serial numbers have higher MATCH values than the exact one. I'm assuming that the hyphen is messing up the search, that it, it's using it as a word delimiter or something like that. Is there a way to escape the hyphen, that is, make it a significant part of the search? Is there something that I could edit in myisam/ftdefs.h (or somewhere else) that would do what I would like? Thanks, Marty - 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: Looks like any user can read the mysql db!
Jose-- Are you running mysqld as 'root'? Run it as 'mysql' and change the group membership of the data directory and its contents to 'mysql'. Vadim. Jose de Leon wrote: Here are the perms on the mysql db files /var/lib/mysql/mysql Also, I was logged into mysql with a plain user 'jose' that had access to only his db, 'jose' grant select,insert,create,drop,delete,alter on jose.* to jose@localhost identified by 'jose'; -rw-rw1 mysqlroot0 Aug 10 22:49 columns_priv.MYD -rw-rw1 mysqlroot 1024 Aug 10 22:49 columns_priv.MYI -rw-rw1 mysqlroot 8778 Aug 10 22:49 columns_priv.frm -rw-rw1 mysqlroot 302 Aug 15 13:20 db.MYD -rw-rw1 mysqlroot 3072 Aug 15 13:27 db.MYI -rw-rw1 mysqlroot 8982 Aug 10 22:49 db.frm -rw-rw1 mysqlroot0 Aug 10 22:49 func.MYD -rw-rw1 mysqlroot 1024 Aug 10 22:49 func.MYI -rw-rw1 mysqlroot 8641 Aug 10 22:49 func.frm -rw-rw1 mysqlroot0 Aug 10 22:49 host.MYD -rw-rw1 mysqlroot 1024 Aug 10 22:49 host.MYI -rw-rw1 mysqlroot 8958 Aug 10 22:49 host.frm -rw-rw1 mysqlroot0 Aug 10 22:49 tables_priv.MYD -rw-rw1 mysqlroot 1024 Aug 10 22:49 tables_priv.MYI -rw-rw1 mysqlroot 8877 Aug 10 22:49 tables_priv.frm -rw-rw1 mysqlroot 749 Aug 15 13:31 user.MYD -rw-rw1 mysqlroot 2048 Aug 15 13:31 user.MYI -rw-rw1 mysqlroot 9148 Aug 10 22:49 user.frm == On Wed, 15 Aug 2001 14:51:44 -0400 Andrew Schmidt [EMAIL PROTECTED] wrote: I'm not sure how you were able to do that. Especially since you didn't provide exactly what you did. But here's what I get as ROOT (all privileges): mysql load data infile '/usr/local/var/db/mysql/mysql/user.MYD' into table t; ERROR 1085: The file '/usr/local/var/db/mysql/mysql/user.MYD' must be in the database directory or be readable by all mysql since all mysql datafiles are 660 (not readable by all) and do not sit in the database directory I'm not sure how you would read in those tables. The only thing I was able to read in was the .err file perhaps you can explain in more detail what you did? thanks, -- Andrew - Original Message - From: Jose de Leon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 16, 2001 5:03 AM Subject: Looks like any user can read the mysql db! MySQL 3.23.36 Using the LOAD DATA command, I was able to read the first field of user.MYD. I think I should be able to load the rest of the table with a little bit of playing around... I think with a little bit of ingenious delimeter specification, etc, anybody should be able to load any table they want provided they know the path. - 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 - 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: WHERE OR Syntax
Put parentheses around the ..OR.. part (between WHERE and AND) - AND precedes OR so Also, use 'IN' instead of a series of 'OR' - this will make the query a lot faster. e.g. listings.Area IN ('19','24','25',...) Vadim. JD Daniels wrote: I have this query built from a web form. The form will add to the query depending on what search criteria has been chosen. what happens is if they choose the criteria in the query below, it stops comparing the other criteria such as listed date and price range. Also the = operator does not return any results, and the = operator returns a syntax error at the . I am hopelessly confused. SELECT listings.lid,listings.Price,Area.Area,listings.Description,listings.Sold,lis tings.MLS_Number FROM listings LEFT JOIN Companies ON (listings.cid=Companies.cid) LEFT JOIN Area ON (listings.Area=Area.aid) LEFT JOIN rid ON (listings.rid=rid.rid) LEFT JOIN Property_Type ON (listings.Property_Type=Property_Type.ptid) LEFT JOIN Property_Style ON (listings.Property_Style=Property_Style.psid) WHERE listings.Area = '19' or listings.Area='25' or listings.Area='24' or listings.Area='32' or listings.Area='33' or listings.Area='28' or listings.Area='38' or listings.Area='39' or listings.Area='34' or listings.Area='20' or listings.Area='18' or listings.Area='22' or listings.Area='27' or listings.Area='17' or listings.Area='23' or listings.Area='29' or listings.Area='31' or listings.Area='37' or listings.Area='35' or listings.Area='36' AND TO_DAYS(NOW()) - TO_DAYS(Listed) 7 AND Price75000 It seems as soon as it hits the first OR condition, it ignores the AND condition. I was hoping the brackets would help, but it doesn't seem to matter. It works fine as long as I stick to AND. The numbers are the id for area types in another table that link to the listings table. How else can I check for a number of different area types and still further narrow the search down with other criteria? JD Daniels - 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: Help with MATCH AGAINST
$SQL = SELECT * FROM cvtemplate WHERE employment_type = '$employment_type' AND location = '$location' AND related_discipline = '$related_discipline', MATCH specific_work_profile, wxcdone, wxcdtwo, wxcdthree, wxcdfour AGAINST ('$keywords') AS score FROM cvtemplate HAVING score!=0 ORDER BY score DESC; But I keep getting a sql syntax error. If anyone can help?? -- You have 2 FROM clauses - remove the 2nd one. Also, don't forget that prior to using MATCH...AGAINST you should have created a fulltext index *exactly* the way you list the fields in MATCH(). In your case it would be FULLTEXT(specific_work_profile, wxcdone, wxcdtwo, wxcdthree, wxcdfour). Vadim. - 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: How long should sql-bench/run-all-tests take?
Patrick-- yes, it might take hours, nothing wrong with your setup. Vadim. Patrick Goetz wrote: I recently installed MySQL 2.23.37 from the unstable Debian package on a dual-processor PII 350 w/ 128MB SDRAM running linux 2.4.2 For fun (since I've never tried running any of the benchmarks) I compiled sql-bench and executed run-all-tests as root (didn't feel like setting up the required premissions for test). Now I'm a little concerned, as the test took hours to complete (I don't know the exact amount of time, as I didn't time it and left to do something else before the job finished, but it had been running for well over an hour when I left. Can someone tell me if this is reasonable or is something in my system horribly misconfigured? (I.e. if run-all-tests typically finishes in 10 minutes on a comparable system then I think I might be in trouble.) *** Gallons of oil per day that the proposed drilling of Alaska's Arctic National Wildlife Refuge is projected to yield: 42,000,000 Gallons by which daily U.S. oil consumption would drop if SUVs' average fuel efficiency increased by 3 mpg: 49,000,000 *** _Patrick Goetz _| ~-. Dept. of Mathematics \, *_} The University of Texas at Austin[EMAIL PROTECTED] \( Austin, Texas 78712-1084 Phone: (512) 232-2746 http://www.ma.utexas.edu/users/pgoetz - 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: max FULLTEXT index size?
Sergei, I also have the same problem, but in my case the table size exceeds 2Gb, average record size being around 50Kb. I'm running 3.23.37 (i686) on a dual PIII 866/ 1Gb RAM / 36Mb SCSI RAID5 w/ RedHat 7.1 (kernel 2.4). As I type this message, MySQL is still [fulltext-] indexing one of the tables, for the 6th straight day now. I'm getting desperate. What's the completion state of v.4.0? Will it also improve query performance on big tables? Thanks, Vadim. Sergei Golubchik wrote: Hi! On Apr 19, Braxton Robbason wrote: Hi all, I'm creating a fulltext index on a 400MB table, and the creation process gets very slow. It's fast for the first 50MB of the index, and then it grinds to what seemed like a halt after days. My question is - is there anyone who's created fulltext indexes on this much data who can assure me that it's possible? Merge tables are not an option because you can't query a merge table on a fulltext index. thanks, Braxton Yes, FULLTEXT index works ok for 400 MB tables. In my tests FULLTEXT index was created (with CREATE INDEX) over 400 MB table in 15 minutes with new MySQL-4.0 code (not yet available, sorry). Everything works with 3.23 code too, but index creation is 50-100 times slower (and resulting index is ~10% bigger). Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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: Database size
Erik, You mentioned 40Gig files on Intel platform - what OS/DBMS did you use then? How about the performance? Thanks, Vadim. Terry Katz wrote: Erik, If your running Linux on an Intel machine (which I assume it is), then you've hit Linux's limit for IA32 and 2.2.x kernels .. If you want to go with sizes higher then 2gig you need to upgrade to a 2.4 kernel (and the latest GNU Libc) .. I've had (not mysql databases) 40gig files on Intel boxes work out fine ... -Terry Hi, I have a problem, my database has reached Linux Redhat 7.1 max file size. (2147483647 Bytes). Does eny one know what i can do to make a lager database. (lager filesystem) Thanks Erik Dhiin - 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 - 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