Re: How dangerous is OPTIMIZE TABLE?
Hi, AFAIK, it may cause problem if used on table for which you are using INSERT DELAYED. http://www.mysql.com/doc/B/u/Bugs.html Regards, Jocelyn Fournier - Original Message - From: "Benjamin Pflugmann" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "Mark" <[EMAIL PROTECTED]> Sent: Saturday, May 11, 2002 7:24 PM Subject: Re: How dangerous is OPTIMIZE TABLE? > Hi. > > This is more intended to be an second answer to the original post, but > I don't have it anymore. > > On Fri, May 10, 2002 at 11:00:56AM -0700, [EMAIL PROTECTED] wrote: > > On Fri, May 10, 2002 at 12:55:39PM +0200, Mark wrote: > > > How dangerous is OPTIMIZE TABLE? > > > > > > I have been reading through the FAQ, and came to the section on OPTIMIZE > > > TABLE. > > > > > > http://www.mysql.com/doc/O/P/OPTIMIZE_TABLE.html > [...] > > > > I use it pretty often and have never experienced any problems. > > Usually when there are problems with data corruption, it is something > > outside of MySQL (OS or hardware problems). > > I had a look at the comment and the cited error number is simply a > "disk full" condition. And on the second cited error number is a "last > repair failed" error. > > So it is not dangerous in any way. Copying back the original files > (which MySQL renamed) would have solved the problem at once. > > Regards, > > Benjamin. > > -- > [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 > > - 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
Which Index is better ?
Hi, I wonder which index will be the most efficient for this query : SELECT numreponse FROM searchhardwarefr7 WHERE pseudo='joce' AND date>'2001-01-01 00:00:00'; I tested a compound index on (pseudo,date,numreponse), and explain return : mysql> EXPLAIN SELECT numreponse FROM searchhardwarefr7 WHERE pseudo='joce' AND date>'2001-01-01 00:00:00'; +---+---+---++-+--+- -+-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+---+---++-+--+- -+-+ | searchhardwarefr7 | range | pseudo| pseudo | 43 | NULL |1 | where used; Using index | +---+---+---++-+--+- -+-+ 1 row in set (0.01 sec) But I also tested an index on (pseudo,numreponse,date), and explain return : mysql> EXPLAIN SELECT numreponse FROM searchhardwarefr7 WHERE pseudo='joce' AND date>'2001-01-01 00:00:00'; +---+--+---+--+-+---+--- ---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+--- ---+-+ | searchhardwarefr7 | ref | pseudo_2 | pseudo_2 | 35 | const | 1 | where used; Using index | +---+--+---+--+-+---+--- ---+-+ 1 row in set (0.00 sec) It seems ref type is better than range type, but I was thinking the (pseudo,date,numreponse) index was better the second one, as it matched exactly the query (or perhaps explain simply doesn't write the first ref match made before the range match). It's why I need in this case some clarification about indexes :) Thank you in advance, 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: Duplicate Keys
Hi, Try ALTER IGNORE TABLE your_table ADD UNIQUE (column_where_you_dont_want_duplicates); Regards, Jocelyn Fournier - Original Message - From: "Oswaldo Castro" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, April 26, 2002 8:20 PM Subject: Duplicate Keys > Hi List > > Sorry for the newbie question. > > Im new to MySQL. I have a table that I don't know how many duplicate records > it has, so how do I query and delete all the duplicate (or many multiple) > records from a MySQL table ? > > Thanks in advance > > Oswaldo Castro > > > > - > 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: How does DISTINCT really work ?
Hi, So I assume in this case the only way to have distinct results is to use "GROUP BY topic" clause ? Regards, Jocelyn - Original Message - From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, April 26, 2002 4:45 PM Subject: Re: How does DISTINCT really work ? > Fournier Jocelyn [Presence-PC] writes: > > Hi, > > > > I have the latest 4.0.2 source installed, and the problem is still here > > AFAIK : > > > > mysql> \s > > -- > > mysql Ver 12.1 Distrib 4.0.2-alpha, for pc-linux-gnu (i686) > > > > -=[root@anedroide]=(/usr/local/mysql-4.0)# bk export -tpatch > > <(13:29:52) > > # This is a BitKeeper generated patch for the following project: > > # Project Name: MySQL - fast and reliable SQL database > > # This patch format is intended for GNU patch command version 2.5 or higher. > > # This patch includes the following deltas: > > # ChangeSet1.1235 -> 1.1236 > > # Docs/manual.texi1.865 -> 1.866 > > # > > # The following is the BitKeeper ChangeSet Log > > # > > # 02/04/15 [EMAIL PROTECTED] 1.1236 > > # Documentation fix. > > # > > > > mysql> SELECT DISTINCT topic FROM > > forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE > > searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND > > (mot='les') ORDER BY date DESC LIMIT 0,40; > > +---+ > > | topic | > > +---+ > > | 11462 | > > > > | 12313 | > > | 9673 | > > | 11462 | > > > > | 12282 | > > | 9673 | > > | 12224 | > > +---+ > > 40 rows in set (0.06 sec) > > > > Hi! > > Thank you for posting about the above problem and for uploading the tables. > > The above is not a bug. > > Due to the presence of date column in ORDER BY clause, MySQL (and any > other RDBMS) has to do a silent addition to the select list, so your > query truly looks like this (I had to change table names to check the > ones you have sent us): > > > SELECT DISTINCT topic, date FROM searchmainhardwarefr8,searchjoinhardwarefr8 WHERE > searchmainhardwarefr8.numreponse=searchjoinhardwarefr8.numreponse AND > (mot='les') ORDER BY date DESC LIMIT 0,40; > > And a true output is truly like this : > > topic date > 2171 2002-04-19 20:13:10 > 2126 2002-04-19 04:54:38 > 2146 2002-04-18 21:39:12 > 2147 2002-04-18 12:44:30 > 2148 2002-04-18 09:41:56 > 2148 2002-04-17 15:13:44 > 2148 2002-04-16 15:59:50 > 1873 2002-04-15 10:02:20 > 2132 2002-04-13 18:18:44 > 2133 2002-04-13 15:56:57 > 2129 2002-04-12 17:43:29 > 2121 2002-04-12 17:00:03 > 2085 2002-04-12 15:07:49 > 2121 2002-04-12 13:26:14 > 1664 2002-04-12 00:45:30 > 2106 2002-04-10 00:33:03 > 741 2002-04-09 14:50:31 > 2093 2002-04-09 09:12:46 > 2045 2002-04-08 11:59:09 > 2089 2002-04-07 18:29:08 > 2089 2002-04-07 16:53:36 > 2067 2002-04-07 15:05:28 > 2079 2002-04-05 16:57:08 > 2021 2002-04-05 01:51:33 > 2054 2002-04-03 11:03:42 > 2049 2002-03-29 11:42:59 > 1993 2002-03-28 01:28:08 > 2040 2002-03-27 16:50:19 > 2021 2002-03-24 12:29:28 > 2005 2002-03-21 16:55:01 > 2005 2002-03-20 17:58:37 > 1935 2002-03-19 21:06:12 > 1935 2002-03-19 18:31:35 > 1998 2002-03-19 17:51:20 > 1990 2002-03-18 21:22:52 > 1934 2002-03-18 10:50:45 > 1983 2002-03-18 00:06:59 > 1934 2002-03-17 22:57:50 > 1968 2002-03-15 16:41:51 > 1971 2002-03-14 14:00:35 > > > So, simply, everything is as it should be. > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ 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
Re: select first or last row
Hi, For the first question you can make it in two step : SELECT MIN(id) FROM names; SELECT * FROM names WHERE id = min ; SELECT MAX(id) FROM names; SELECT * FROM names WHERE id = max ; Regards, Jocelyn Fournier PresencePC - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, April 21, 2002 4:50 AM Subject: select first or last row > Hi > > MySql Details My server is running MySQL 3.23.45 and I have > 4.0.0-alpha-nt on my local test machine. > > > > What is the fastest way to select the first or the last row from a database? > > I have tried queries like this > > (id is an auto incrementing primary key) > select * from names order by id desc limit 1 > select * from names order by id asc limit 1 > select * from names limit 1 > > > if I run the following sql queries with the explain command it returns the > following results > > > table type possible_keys key key_len ref rows Extra > - - --- --- --- - > names index NULL PRIMARY 4 NULL 61368 > * > > No matter what I do, and what indexes I use, I cannot seem to find a way of > selecting the last or first row without scanning the whole table. > > > A few times recently I've wished for a new table type, a table that acts > more like a pipe or stack just to solve this problem. > > Thanks > Pete Kelly > > > Other Items on my MySql wish list. > > 1 . It would also be nice to combine a select and a delete command together > to form a whole new command, (Retrive data and auto delete the row) > 2 . Another new command I would like to see is "Show Processlist History", > basically this would show the last 30 or so queries run on the server. > 3. I often run queries like this > > SELECT id FROM users WHERE name = 'pete' > if the row exists > UPDATE users SET visits=visits+1 WHERE name = 'pete' > else > INSERT INTO users (name) VALUES ('pete') > > It would be nice to make this type of code quicker. > > > > > > > > > > - > 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: How does DISTINCT really work ?
Hi, Sorry finally there is no lock with 'use database', it only takes a huge amont of time because of the number of table in my database :) Regards, Jocelyn - Original Message ----- From: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Heikki Tuuri" <[EMAIL PROTECTED]> Sent: Monday, April 15, 2002 1:35 PM Subject: Re: How does DISTINCT really work ? > Hi, > > I have the latest 4.0.2 source installed, and the problem is still here > AFAIK : > > mysql> \s > -- > mysql Ver 12.1 Distrib 4.0.2-alpha, for pc-linux-gnu (i686) > > -=[root@anedroide]=(/usr/local/mysql-4.0)# bk export -tpatch > <(13:29:52) > # This is a BitKeeper generated patch for the following project: > # Project Name: MySQL - fast and reliable SQL database > # This patch format is intended for GNU patch command version 2.5 or higher. > # This patch includes the following deltas: > # ChangeSet1.1235 -> 1.1236 > # Docs/manual.texi1.865 -> 1.866 > # > # The following is the BitKeeper ChangeSet Log > # > # 02/04/15 [EMAIL PROTECTED] 1.1236 > # Documentation fix. > # > > mysql> SELECT DISTINCT topic FROM > forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE > searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND > (mot='les') ORDER BY date DESC LIMIT 0,40; > +---+ > | topic | > +---+ > | 11462 | > > | 12313 | > | 9673 | > | 11462 | > > | 12282 | > | 9673 | > | 12224 | > +---+ > 40 rows in set (0.06 sec) > > > BTW I have a problem with the latest mysql client (12.1) : > > It seems to be locked when I make a 'use database' : > > > mysql> use forum > Reading table information for completion of table and column names > You can turn off this feature to get a quicker startup with -A > > > > Regards, > > Jocelyn > - Original Message - > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Monday, April 15, 2002 1:06 PM > Subject: Re: How does DISTINCT really work ? > > > > Hi! > > > > Ok, Monty said that this DISTINCT ... ORDER BY bug has already been fixed > in > > 4.0. > > > > Regards, > > > > Heikki > > Innobase Oy > > > > - Original Message - > > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > > To: "SDiZ (UHome)" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Monday, April 15, 2002 9:06 AM > > Subject: Re: How does DISTINCT really work ? > > > > > > > Hi! > > > > > > - Original Message - > > > From: "SDiZ (UHome)" <[EMAIL PROTECTED]> > > > To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > > Sent: Monday, April 15, 2002 7:51 AM > > > Subject: Re: How does DISTINCT really work ? > > > > > > > > > > I think every thing are documented. > > > > > > > > http://www.mysql.com/doc/B/u/Bugs.html > > > > a.. The optimiser may handle DISTINCT differently if you are using > > > 'hidden' > > > > columns in a join or not. In a join, hidden columns are counted as > part > > of > > > > the result (even if they are not shown) while in normal queries hidden > > > > columns doesn't participate in the DISTINCT comparison. We will > probably > > > > change this in the future to never compare the hidden columns when > > > > executing DISTINCT An example of this is: > > > > SELECT DISTINCT mp3id FROM band_downloads > > > >WHERE userid = 9 ORDER BY id DESC; > > > > > > > > and > > > > SELECT DISTINCT band_downloads.mp3id > > > >FROM band_downloads,band_mp3 > > > >WHERE band_downloads.userid = 9 > > > >AND band_mp3.id = band_downloads.mp3id > > > >ORDER BY band_downloads.id DESC; > > > > > > > > In the second case you may in MySQL Server 3.23.x get two identical > rows > > > > in the result set (because the hidden 'id' co
Re: How does DISTINCT really work ?
t; > > > > > > How-To-Repeat > > > > > > > > - Original Message - > > > > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > > > > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > > > Cc: <[EMAIL PROTECTED]> > > > > Sent: Sunday, April 14, 2002 9:14 PM > > > > Subject: Re: How does DISTINCT really work ? > > > > > > > > > > > > > Jocelyn, > > > > > > > > > > the below shows a bug in the MySQL DISTINCT operation. If you > specify > > > > > DISTINCT, you should not get duplicate rows. > > > > > > > > > > I have forwarded this email to Monty. > > > > > > > > > > Have you tried myisamcheck on the tables? > > > > > > > > > > If the tables are small, could you please upload them to the > 'secret' > > > > > directory in the MySQL support ftp server. I do not remember the > > > directory > > > > > exactly, but you have reported so many bugs in MySQL that maybe you > > > > remember > > > > > :). > > > > > > > > > > Best regards, > > > > > > > > > > Heikki Tuuri > > > > > Innobase Oy > > > > > --- > > > > > Order technical MySQL/InnoDB support at https://order.mysql.com/ > > > > > See http://www.innodb.com for the online manual and latest news on > > > InnoDB > > > > > > > > > > > > > > > - Original Message - > > > > > From: ""DL Neil"" <[EMAIL PROTECTED]> > > > > > Newsgroups: mailing.database.mysql > > > > > Sent: Sunday, April 14, 2002 8:01 PM > > > > > Subject: Re: How does DISTINCT really work ? > > > > > > > > > > > > > > > > Hi Jocelyn, > > > > > > > > > > > > I don't have a quick/sure answer for you, but am wondering about > the > > > > > > relationship between the date and topic columns - I note that the > > > '429s > > > > > > are interspersed by other values. > > > > > > > > > > > > I'm a great believer in the KISS principle (simple things amuse > > simple > > > > > > minds!) > > > > > > May I suggest taking out the last three clauses (ORDER BY, DESC, > and > > > > > > LIMIT) and trying that. > > > > > > Then if it works, put the clauses back, one at a time (from left > to > > > > > > right), observing the effect. > > > > > > > > > > > > Please let me know how you get on, > > > > > > =dn > > > > > > > > > > > > > > > > > > > So why does I obtain duplicates numbers for 'topic' with the > first > > > > > > query > > > > > > > with DISTINCT ? (perhaps a bug ?) > > > > > > > > > > > > > > Regards, > > > > > > > > > > > > > > Jocelyn Fournier > > > > > > > > > > > > > > - Original Message - > > > > > > > From: "DL Neil" <[EMAIL PROTECTED]> > > > > > > > To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; > > > > > > > <[EMAIL PROTECTED]> > > > > > > > Sent: Sunday, April 14, 2002 5:54 PM > > > > > > > Subject: Re: How does DISTINCT really work ? > > > > > > > > > > > > > > > > > > > > > > Hi Jocelyn, > > > > > > > > > > > > > > > > > I wonder how works DISTINCT when using it with the following > > > join. > > > > > > > > > Ex : > > > > > > > > > > > > > > > > > > mysql> SELECT DISTINCT topic FROM > > > > > > > > > searchmainhardwarefr7,searchjoinhardwarefr7 WHERE > > > > > > > > > > > > searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse > > > > > > AND > > > > > > > > > (mot='test') AND date >= '2002-03-24' ORDER BY date DESC > LIMIT > > > >
Re: How does DISTINCT really work ?
In fact I couldn't suppress the LIMIT as it would return a lot of rows ;) However the order by doesn't seem to have any effect. - Original Message - From: "DL Neil" <[EMAIL PROTECTED]> To: "Carsten Gehling" <[EMAIL PROTECTED]>; "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, April 14, 2002 7:52 PM Subject: Re: How does DISTINCT really work ? > Well done Carsten! > Which may bring us back to the relationships between the data/columns > being used to control the query. > Jocelyn: Did you try out my previous suggestions? > Please advise, > =dn > > ----- Original Message - > From: "Carsten Gehling" <[EMAIL PROTECTED]> > To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; "DL Neil" > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: 14 April 2002 18:46 > Subject: SV: How does DISTINCT really work ? > > > > > Fra: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] > > > Sendt: 14. april 2002 19:22 > > > > > In fact what it's odd is when I use EXPLAIN, I don't see anywhere > DISTINCT > > > in the EXPLAIN result : > > > > That's because the MySQL optimizer converts your distinct into a group > by. > > Read more here: > > > > http://www.mysql.com/doc/D/I/DISTINCT_optimisation.html > > > > - Carsten > > > > > > > > - 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 does DISTINCT really work ?
Yes but when it's written "DISTINCT is converted to a GROUP BY on all columns", which columns are included in "all columns" ? :) Thanks, Jocelyn - Original Message - From: "Carsten Gehling" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; "DL Neil" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, April 14, 2002 7:46 PM Subject: SV: How does DISTINCT really work ? > > Fra: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] > > Sendt: 14. april 2002 19:22 > > > In fact what it's odd is when I use EXPLAIN, I don't see anywhere DISTINCT > > in the EXPLAIN result : > > That's because the MySQL optimizer converts your distinct into a group by. > Read more here: > > http://www.mysql.com/doc/D/I/DISTINCT_optimisation.html > > - Carsten > > > > - > 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: How does DISTINCT really work ?
In fact what it's odd is when I use EXPLAIN, I don't see anywhere DISTINCT in the EXPLAIN result : mysql> EXPLAIN SELECT DISTINCT topic FROM searchmainhardwarefr7,searchjoinhardwarefr7 WHERE searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND date >='2002-03-24' AND (mot='test') ORDER BY date DESC LIMIT 0,20; +---++++ -+--+--+ --+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---++++ -+--+--+ --+ | searchmainhardwarefr7 | ref| PRIMARY,numreponse | PRIMARY| 30 | const| 3597 | where used; Using index; Using temporary; Using filesort | | searchjoinhardwarefr7 | eq_ref | PRIMARY,numreponse | numreponse | 4 | searchmainhardwarefr7.numreponse |1 | where used | +---++++ -+--+--+ --+ 2 rows in set (0.00 sec) However if I remove the AND mot='test', DISTINCT comes back in the EXPLAIN result : mysql> EXPLAIN SELECT DISTINCT topic FROM searchmainhardwarefr7,searchjoinhardwarefr7 WHERE searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND date >='2002-03-24' ORDER BY date DESC LIMIT 0,20; +---+---+++- +--++--- ---+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+---+++- +--++--- ---+ | searchjoinhardwarefr7 | range | PRIMARY,numreponse | PRIMARY| 8 | NULL | 174020 | where used; Using index; Using temporary; Using filesort | | searchmainhardwarefr7 | ref | numreponse | numreponse | 4 | searchjoinhardwarefr7.numreponse | 9 | Using index; Distinct | +---+---+++- +--++--- ---+ 2 rows in set (0.00 sec) Jocelyn - Original Message ----- From: "DL Neil" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, April 14, 2002 6:58 PM Subject: Re: How does DISTINCT really work ? > Hi Jocelyn, > > I don't have a quick/sure answer for you, but am wondering about the > relationship between the date and topic columns - I note that the '429s > are interspersed by other values. > > I'm a great believer in the KISS principle (simple things amuse simple > minds!) > May I suggest taking out the last three clauses (ORDER BY, DESC, and > LIMIT) and trying that. > Then if it works, put the clauses back, one at a time (from left to > right), observing the effect. > > Please let me know how you get on, > =dn > > > > So why does I obtain duplicates numbers for 'topic' with the first > query > > with DISTINCT ? (perhaps a bug ?) > > > > Regards, > > > > Jocelyn Fournier > > > > - Original Message - > > From: "DL Neil" <[EMAIL PROTECTED]> > > To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]> > > Sent: Sunday, April 14, 2002 5:54 PM > > Subject: Re: How does DISTINCT really work ? > > > > > > > Hi Jocelyn, > > > > > > > I wonder how works DISTINCT when using it with the following join. > > > > Ex : > > > > > > > > mysql> SELECT DISTINCT topic FROM > > > > searchmainhardwarefr7,searchjoinhardwarefr7 WHERE > > > > searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse > AND > > > > (mot='test') AND date >= '2002-03-24' ORDER BY date DESC LIMIT > 0,20; > > > > ++ > > > > | topic | > > > > ++ > > > > | 108499 | > > > > | 108341 | > > > > | 108425 | > > > > | 108425 | > > > > | 108425 | > > > > | 108429 | > > > > | 108429 | &g
Re: How does DISTINCT really work ?
Hi, So why does I obtain duplicates numbers for 'topic' with the first query with DISTINCT ? (perhaps a bug ?) Regards, Jocelyn Fournier - Original Message - From: "DL Neil" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, April 14, 2002 5:54 PM Subject: Re: How does DISTINCT really work ? > Hi Jocelyn, > > > I wonder how works DISTINCT when using it with the following join. > > Ex : > > > > mysql> SELECT DISTINCT topic FROM > > searchmainhardwarefr7,searchjoinhardwarefr7 WHERE > > searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND > > (mot='test') AND date >= '2002-03-24' ORDER BY date DESC LIMIT 0,20; > > ++ > > | topic | > > ++ > > | 108499 | > > | 108341 | > > | 108425 | > > | 108425 | > > | 108425 | > > | 108429 | > > | 108429 | > > | 108429 | > > | 108429 | > > | 108427 | > > | 108427 | > > | 108429 | > > | 108429 | > > | 108429 | > > | 108429 | > > | 108429 | > > | 108429 | > > | 108429 | > > | 108429 | > > | 108429 | > > ++ > > 20 rows in set (0.08 sec) > > > > In this case I was expecting only distinct topic number list, like > with the > > following query : > > > > mysql> SELECT topic FROM searchmainhardwarefr7,searchjoinhardwarefr7 > WHERE > > searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND > > (mot='test') AND date >= '2002-03-24' GROUP BY topic ORDER BY date > DESC > > LIMIT 0,20; > > ++ > > | topic | > > ++ > > | 108499 | > > | 108429 | > > | 108427 | > > | 108425 | > > | 108417 | > > | 108341 | > > | 108312 | > > | 108303 | > > | 108275 | > > | 108268 | > > | 108167 | > > | 108233 | > > | 108231 | > > | 108172 | > > | 108052 | > > | 108102 | > > | 107835 | > > | 108122 | > > | 108096 | > > | 106794 | > > ++ > > 20 rows in set (0.10 sec) > > > > Does DISTINCT implicitely check columns included in the WHERE clause ? > > Thanks you. > > > AFAIK the WHERE clause (join) is carried out earlier (etc) and the > DISTINCT carried out on the resultset, almost at the end of the process. > > Regards, > =dn > > > - > 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
How does DISTINCT really work ?
Hi, I wonder how works DISTINCT when using it with the following join. Ex : mysql> SELECT DISTINCT topic FROM searchmainhardwarefr7,searchjoinhardwarefr7 WHERE searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND (mot='test') AND date >= '2002-03-24' ORDER BY date DESC LIMIT 0,20; ++ | topic | ++ | 108499 | | 108341 | | 108425 | | 108425 | | 108425 | | 108429 | | 108429 | | 108429 | | 108429 | | 108427 | | 108427 | | 108429 | | 108429 | | 108429 | | 108429 | | 108429 | | 108429 | | 108429 | | 108429 | | 108429 | ++ 20 rows in set (0.08 sec) In this case I was expecting only distinct topic number list, like with the following query : mysql> SELECT topic FROM searchmainhardwarefr7,searchjoinhardwarefr7 WHERE searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND (mot='test') AND date >= '2002-03-24' GROUP BY topic ORDER BY date DESC LIMIT 0,20; ++ | topic | ++ | 108499 | | 108429 | | 108427 | | 108425 | | 108417 | | 108341 | | 108312 | | 108303 | | 108275 | | 108268 | | 108167 | | 108233 | | 108231 | | 108172 | | 108052 | | 108102 | | 107835 | | 108122 | | 108096 | | 106794 | ++ 20 rows in set (0.10 sec) Does DISTINCT implicitely check columns included in the WHERE clause ? Thanks you. Regards, Jocelyn Fournier Presence-PC - 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: Attachments & Variables....
Hi, > 5:40 a.m. > > MYSQL SQL MYSQL SQL MYSQL SQL MYSQL SQL MYSQL SQL MYSQL SQL MYSQL SQL MYSQL SQL > MYSQL SQL MYSQL SQL MYSQL SQL MYSQL SQL MYSQL SQL MYSQL SQL MYSQL SQL MYSQL SQL > > (is that enough, to get past the filter ?) > (sent this via the website, & got a reply re the filter...) > Perhaps because it's not MySQL, but PHP related... > Original #2 message :- > > > 1) PROBLEM :- > > Ive sent this message several times, from my 'Eudora' Email programme to the > group & a message was received advising my message was rejected because i had > an ATTACHMENT. > > i'm NOT using/sending any attachments... > Perhaps you try to send the mail in HTML format instead of PLAIN TEXT ? > EG :- > echo("Showing all the tables within database : > ".$database.""); > > However, both these commands :- show the same thing... > > echo("Showing all the tables within database : $database"); > echo("Showing all the tables within database : > ".$database.""); > > Are there any reasons / Rules in which you can put a variable name within the > double-quotes, instead of closing the quotes & putting the variable > after/before a full-stop. > > Just curious - No worries. > > G. A small example : You have a variable $database and you want to print $database followed by some letters ('forumtest' for exemple)... In this case you have to use echo "blabla".$database."forumtest"; Regards, Jocelyn Fournier - 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: Group by?
- Original Message - From: "Tobias K. Tobiasen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, April 04, 2002 6:46 PM Subject: Group by? > Hello > > I think the gruop by is behaving strange. Could someone please explain > this or maybe point me to a definition of the group by. (Inspired by > http://www.mysql.org/doc/E/x/Examples.html). > > > CREATE TABLE shop ( > article INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL, > dealer CHAR(20) DEFAULT '' NOT NULL, > price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, > PRIMARY KEY(article, dealer)); > > INSERT INTO shop VALUES > (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69), > (3,'D',1.25),(4,'D',19.95); > > mysql> SELECT * FROM shop; > > +-++---+ > | article | dealer | price | > +-++---+ > |0001 | A | 3.45 | > |0001 | B | 3.99 | > |0002 | A | 10.99 | > |0003 | B | 1.45 | > |0003 | C | 1.69 | > |0003 | D | 1.25 | > |0004 | D | 19.95 | > +-++---+ > 7 rows in set (0.00 sec) > > mysql> select * from shop GROUP BY article; > +-++---+ > | article | dealer | price | > +-++---+ > |0001 | A | 3.45 | > |0002 | A | 10.99 | > |0003 | B | 1.45 | > |0004 | D | 19.95 | > +-++---+ > 4 rows in set (0.00 sec) > > QUESTION: I had expected 7 rows - where did the rest go? Try SELECT * FROM shop GROUP BY article,dealer; (same thing for the rest of queries) Regards, Jocelyn Fournier Presence-PC - 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
Myisamchk question
Hi, As I'd got not answer, I repost my question ;) I'm currently testing the BACKUP TABLE function, and to reconstruct the MYI files, I'm performing the following action : For exemple : dd if=/home/mysql/Hardwarefr/cookie.MYI bs=16k count=1 of=/home/mirror/mysql/Hardwarefr /cookie.MYI (to restore the valid first 16 K block) [root@forum] /home/mirror/mysql/Hardwarefr> ls -la cookie.MYI -rw-r--r--1 root root16384 Mar 12 01:52 cookie.MYI Then I execute : myisamchk -r cookie.MYI - recovering (with sort) MyISAM-table 'cookie' Data records: 95290 - Fixing index 1 Data records: 95289 [root@forum] /home/mirror/mysql/Hardwarefr> ls -la cookie.MYI -rw-r--r--1 root root 879616 Mar 12 01:55 cookie.MYI [root@forum] /home/mirror/mysql/Hardwarefr> myisamchk -e cookie Checking MyISAM file: cookie Data records: 95289 Deleted blocks: 0 - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check records and index references Things seems to be perfect here, but : myisamchk -o cookie - recovering (with keycache) MyISAM-table 'cookie' Data records: 95289 [root@forum] /home/mirror/mysql/Hardwarefr> ls -la cookie.MYI -rw-r--r--1 root root 1577984 Mar 12 01:56 cookie.MYI Why does the MYI is know nearly twice as big as the old cookie.MYI file ? Thanks you ! Jocelyn Fournier Presence-PC - 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: Optimizing tables
Hi, What about taking a look at the manuel before ? ;) http://www.mysql.com/doc/ Regards, Jocelyn Fournier - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, March 22, 2002 12:14 PM Subject: Optimizing tables > > Placed At : MAA > > > Vijay T@SATYAM > 03/22/2002 04:44 PM > > Hi > > how do i optimize mysql tables > > vijay > > > > - > 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: MySQL - check for existing data
Hi, Just set a unique index on the right column and you won't have anymore duplicate in your table :) ALTER TABLE yourtable ADD UNIQUE(your_column); Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Craig Westerman" <[EMAIL PROTECTED]> To: "MySQL List" <[EMAIL PROTECTED]> Sent: Saturday, March 16, 2002 3:38 PM Subject: MySQL - check for existing data > I have a db table that receives weather data several times a day via a php > script and cron job. Sometimes the data is duplicated for a certain time > window. > > 11:50 44 degree > 11:50 44 degree > 11:40 45 degree > 11:30 46 degree > 11:20 46 degree > 11:10 45 degree > > I do not want the duplicate data for the 11:50 time slot to be inserted into > my table. Is there a common method used to check if data already exists in a > db table and if so to cancel the data insertion? What is this called? Is > this demonstrated in the MySQL book or documentation. If I knew what this > was called, I might have better luck finding solution. > > Thanks > > Craig ><> > [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 > > - 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
Myisamchk question
Hi, I'm currently testing the BACKUP TABLE function, and to reconstruct the MYI files, I'm performing the following action : For exemple : dd if=/home/mysql/Hardwarefr/cookie.MYI bs=16k count=1 of=/home/mirror/mysql/Hardwarefr /cookie.MYI (to restore the valid first 16 K block) [root@forum] /home/mirror/mysql/Hardwarefr> ls -la cookie.MYI -rw-r--r--1 root root16384 Mar 12 01:52 cookie.MYI Then I execute : myisamchk -r cookie.MYI - recovering (with sort) MyISAM-table 'cookie' Data records: 95290 - Fixing index 1 Data records: 95289 [root@forum] /home/mirror/mysql/Hardwarefr> ls -la cookie.MYI -rw-r--r--1 root root 879616 Mar 12 01:55 cookie.MYI [root@forum] /home/mirror/mysql/Hardwarefr> myisamchk -e cookie Checking MyISAM file: cookie Data records: 95289 Deleted blocks: 0 - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check records and index references Things seems to be perfect here, but : myisamchk -o cookie - recovering (with keycache) MyISAM-table 'cookie' Data records: 95289 [root@forum] /home/mirror/mysql/Hardwarefr> ls -la cookie.MYI -rw-r--r--1 root root 1577984 Mar 12 01:56 cookie.MYI Why does the MYI is know nearly twice as big as the old cookie.MYI file ? Thanks you ! Jocelyn Fournier Presence-PC - 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: Changing datatypes "on the fly"?
Hi, Take a look here : http://www.mysql.com/doc/A/L/ALTER_TABLE.html Regards, Jocelyn Fournier - Original Message - From: "Jarkko Toivonen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, March 09, 2002 9:59 PM Subject: Changing datatypes "on the fly"? > I have a column defined as varchar(255) in my MySql-database. Now I realized > that there's not enough room for my inserts. Is there any chance to change > it's datatype to tinytext without making an entirely new database and > inserting data from the old one to it? > > -Jarkko > > > _ > Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. > > > - > 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 with heap table ??
I believe the problem occurs only when I execute the DELETE when someone is inserting a records at the same time. Perhaps the hash index is not updated properly whereas the data are deleted properly, which entails a mismatch between data and index ? (I believe this because the following behaviour : mysql> INSERT INTO online1 SELECT * FROM online; Query OK, 139 rows affected (0.01 sec) Records: 139 Duplicates: 0 Warnings: 0 mysql> DELETE FROM online1 WHERE date < 1015111; Query OK, 139 rows affected (0.01 sec) mysql> SELECT * FROM online1; Empty set (0.00 sec) mysql> DELETE FROM online WHERE date < 1015111; Query OK, 141 rows affected (0.00 sec) mysql> SELECT * FROM online; ERROR 1030: Got error 124 from table handler ) online1 table is not used by anyone, I create it for the testcase, but the online table is heavily used and updated. - Original Message - From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, March 02, 2002 3:31 PM Subject: Re: Bug with heap table ?? > Fournier Jocelyn [Presence-PC] writes: > > Hi, > > > > I encounter a strange problem on one of my server with a lot of heap table. > > (I'm using latest mysql-4.0.2 version) > > Here is the error : > > > > mysql> use forum; > > Database changed > > mysql> SELECT * FROM online; > > ERROR 1030: Got error 124 from table handler > > mysql> SELECT * FROM online; > > ERROR 1030: Got error 124 from table handler > > mysql> SELECT * FROM online; > > +-++-+---++ > > | pseudo | date | cat | forum | search | > > +-++-+---++ > > | wipe0ut | 1015040309 | 1 | 1 | 0 | > > | thoomis | 1015040303 | 4 | 1 | 0 | > > +-++-+---++ > > 2 rows in set (0.00 sec) > > > > How could this be possible with a heap table ?? > > > > Thank you :) > > > > Regards, > > > > Jocelyn Fournier > > Presence-PC > > > > This looks like our bug. > > I am not going to ask you to upload a table ...;o;) > > However, can you make us a test case. > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ 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 with heap table ??
Hi, In fact it seems to be really hard to reproduce and to provide you a testcase, because I've no idea of what could be the reason of this error. I've an other site with exactly the same table structure, and it runs without a glitch. The only clue I have is that the server which return the error contains far more heap table (about 433 heap tables vs only 14 heap tables). I don't think it could be memory corruption problem, the server works like a charm without any other errors. Anyway, here is the table structure : mysql> DESCRIBE online; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | pseudo | char(35)| | PRI | | | | date | bigint(20) unsigned | | MUL | 0 | | | cat| tinyint(4) unsigned | | | 0 | | | forum | tinyint(3) unsigned | | MUL | 0 | | | search | tinyint(1) unsigned | | | 0 | | ++-+--+-+-+---+ 5 rows in set (0.00 sec) Oddly it seems to occur only when there is not so much activity in the forum (late in the night in fact). Oh, I've just find out how to reproduce the bug (I just think that late in the night the table could be empty ;)) : mysql> SELECT * FROM online; +-++-+---++ | pseudo | date | cat | forum | search | +-++-+---++ | wiser 2002 | 1015081015 | 0 |80 | 0 | | Doc13 | 1015081086 | 0 |32 | 0 | | Homertrix | 1015081112 | 13 | 4 | 0 | | Beleg | 1015081005 | 4 |48 | 0 | | RdTeCh | 1015081096 | 9 | 4 | 0 | | guidodo | 1015081073 | 2 | 1 | 0 | | Texla | 1015081067 | 10 | 4 | 0 | | smailer | 1015081046 | 4 |22 | 0 | | Yop | 1015081071 | 5 |42 | 0 | | Estrope | 1015081043 | 6 |89 | 0 | | vicber | 1015081102 | 10 | 1 | 0 | | RuShK | 1015081114 | 4 | 1 | 0 | | Kobbe | 1015081067 | 5 |99 | 0 | | mAzee | 1015081108 | 4 | 1 | 0 | | Yoko54 | 101508 | 5 |74 | 0 | | Prin| 1015081039 | 10 | 4 | 0 | | jojolapatate| 1015081118 | 2 | 1 | 0 | | BestBarn@b | 1015081087 | 4 | 1 | 0 | | FLo14 | 1015081025 | 4 | 1 | 0 | | coco666 | 1015081081 | 3 |32 | 0 | | yonnar tsewarac | 1015081090 | 0 |48 | 0 | | Kangol | 1015081117 | 4 | 1 | 0 | +-++-+---++ 22 rows in set (0.00 sec) mysql> DELETE FROM online WHERE date < 1001511; Query OK, 21 rows affected (0.01 sec) mysql> SELECT * FROM online; ERROR 1030: Got error 124 from table handler So the problem is here when the table is empty. (I've just reproduce exactly the same problem on my other server :)) I doesn't succeed in producing a real testcase right now, but I will try ASAP. Regards, Jocelyn Fournier - Original Message - From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, March 02, 2002 3:31 PM Subject: Re: Bug with heap table ?? > Fournier Jocelyn [Presence-PC] writes: > > Hi, > > > > I encounter a strange problem on one of my server with a lot of heap table. > > (I'm using latest mysql-4.0.2 version) > > Here is the error : > > > > mysql> use forum; > > Database changed > > mysql> SELECT * FROM online; > > ERROR 1030: Got error 124 from table handler > > mysql> SELECT * FROM online; > > ERROR 1030: Got error 124 from table handler > > mysql> SELECT * FROM online; > > +-++-+---++ > > | pseudo | date | cat | forum | search | > > +-++-+---++ > > | wipe0ut | 1015040309 | 1 | 1 | 0 | > > | thoomis | 1015040303 | 4 | 1 | 0 | > > +-++-+---++ > > 2 rows in set (0.00 sec) > > > > How could this be possible with a heap table ?? > > > > Thank you :) > > > > Regards, > > > > Jocelyn Fournier > > Presence-PC > > > > This looks like our bug. > > I am not going to ask you to upload a table ...;o;) > > However, can you make us a test case. > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / //
Bug with heap table ??
Hi, I encounter a strange problem on one of my server with a lot of heap table. (I'm using latest mysql-4.0.2 version) Here is the error : mysql> use forum; Database changed mysql> SELECT * FROM online; ERROR 1030: Got error 124 from table handler mysql> SELECT * FROM online; ERROR 1030: Got error 124 from table handler mysql> SELECT * FROM online; +-++-+---++ | pseudo | date | cat | forum | search | +-++-+---++ | wipe0ut | 1015040309 | 1 | 1 | 0 | | thoomis | 1015040303 | 4 | 1 | 0 | +-++-+---++ 2 rows in set (0.00 sec) How could this be possible with a heap table ?? Thank you :) Regards, Jocelyn Fournier Presence-PC - 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: myismchk messages (too small length)
Hi, Perhaps try to suppress -e and -f switch ? Regards, Jocelyn Fournier - Original Message - From: "Eric Mayers" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, March 02, 2002 12:27 AM Subject: myismchk messages (too small length) I'm running myisamchk (when the database is not running) after a dirty shutdown and getting what appear to be errors it doesn't fix... bash-2.04# ./myisamchk -o -s -e -f /usr/local/var/ccdata/*/*.MYI Found block with too small length at 56452; Skipped bash-2.04# ./myisamchk -o -s -e -f /usr/local/var/ccdata/*/*.MYI Found block with too small length at 56452; Skipped Can someone tell me what this means? Why can't myisamchk can't fix this (is it a problem?)? How can I fix it? I'm trying to set it up to fix itself if possible. Thanks, Eric Mayers Software Engineer I Captus Networks sql! - 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: Please Help! MySql 4.01/PhP 4.06 looking for Libmysqlclient.so.10?
Hi, Try to upgrade PHP to the latest version (4.1.2) , it should run fine. Regards, Jocelyn Fournier - Original Message - From: "Ronald Arenas" <[EMAIL PROTECTED]> To: "Mysql" <[EMAIL PROTECTED]> Sent: Friday, March 01, 2002 9:18 PM Subject: Please Help! MySql 4.01/PhP 4.06 looking for Libmysqlclient.so.10? > I have installed MySql 3.23.49, PHP 4.06, and Apache 1.3.20 successfully on > my RH 7.2 box. Now I try to install MySql 4.01 (after removing MySql > 3.23.49) I get a "Php-mysql.4.xx requires libmysqlclient.so.10". My attempt > to install MySql 4.01 ends there. > > I did some reading and found that "libmysqlclient.so.10" comes with MySql > 3.23.xx. I surfed my directories to find that MySql 4.01 installs > "libmysqlclient.so.11" > > I installed theses packages using the RH RPM Manager. > > Am I missing something? > > Ron Arenas > > > - > 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: Indexing fails, myisamchk cannot repair, no error msg.
Hi, The "warning: Found 22079089 partsShould be: 0 parts" is a known error of myisamchk in MySQL 4.0.1, and is already fixed in MySQL 4.0.2 : Fixed new bug in myisamchk where it didn't correctly update number of ``parts'' in the MyISAM index file. (http://www.mysql.com/doc/N/e/News-4.0.2.html) Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Steve Rapaport" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, March 01, 2002 12:19 AM Subject: Indexing fails, myisamchk cannot repair, no error msg. > >Description: > Copy existing file to text using mysqldump > Read mysqldump onto new machine using 'source' > Resulting database has incomplete indexes (cardinality NULL) (why?) > Try to fix resulting indexes using myisamchk -r > Everything appears to work, takes about 1 hour for 22M records > Attempt to check resulting table with myisamchk > Error results. See actual shell log below. > >How-To-Repeat: > > -- MySQL dump 8.19 > -- > -- Host: localhostDatabase: elenco2g > - > -- Server version 4.0.1-alpha > > -- > -- Table structure for table 'White' > -- > > CREATE TABLE White ( > rec_no int(10) unsigned NOT NULL auto_increment, > phone_no varchar(16) NOT NULL default '', > prefix smallint(5) unsigned zerofill NOT NULL default '0', > title varchar(20) NOT NULL default '', > first_name varchar(50) NOT NULL default '', > last_name varchar(140) NOT NULL default '', > street_no varchar(20) NOT NULL default '', > street_type enum('','Autostrada','A.','Casella > Postale','C.P.','Corso','C.','Galleria','G.','Largo','LG.','Piazza','P.','Pi azzale','PL.','Privato','PR.','Rua','R.','Strada','S.','Via','V.','Viale','V L.','Vicolo','VC.') > NOT NULL default '', > street_name varchar(70) NOT NULL default '', > street_short varchar(70) NOT NULL default '', > city varchar(70) NOT NULL default '', > province char(2) NOT NULL default '', > postal_code varchar(5) NOT NULL default '', > listed tinyint(1) NOT NULL default '0', > hide tinyint(1) NOT NULL default '0', > PRIMARY KEY (rec_no), > KEY phone_no (phone_no(8)), > KEY loc (city(4),street_name(2)), > KEY postal_code (postal_code), > KEY clf (city(6),last_name(6),first_name(1)), > KEY plf (province,last_name(7),first_name(2)), > KEY pref (prefix,city(1)) > ) TYPE=MyISAM; > > /*!4 ALTER TABLE White DISABLE KEYS */; > > // == > > root@db1 elenco2g]# ls -l > total 3690476 > -rw-rw1 mysqlmysql1964830232 Feb 28 21:27 White.MYD > -rw-rw1 mysqlmysql1810504704 Feb 28 21:47 White.MYI > -rw-rw1 mysqlmysql9159 Feb 28 18:15 White.frm > [root@db1 elenco2g]# myisamchk -r White; > - recovering (with sort) MyISAM-table 'White' > Data records: 16777169 > - Fixing index 1 > - Fixing index 2 > myisamchk: Error writing file '/tmp/STzMlliO' (Errcode: 28) > myisamchk: error: 28 when fixing table > MyISAM-table 'White' is not fixed because of errors > Try fixing it by using the --safe-recover (-o) or the --force (-f) option > [root@db1 elenco2g]# myisamchk -r --tmpdir=/var/lib/mysql/tmp White; > - recovering (with sort) MyISAM-table 'White' > Data records: 22079089 > - Fixing index 1 > - Fixing index 2 > - Fixing index 3 > - Fixing index 4 > - Fixing index 5 > - Fixing index 6 > - Fixing index 7 > [root@db1 elenco2g]# myisamchk White; > Checking MyISAM file: White > Data records: 22079089 Deleted blocks: 0 > - check file-size > - check key delete-chain > - check record delete-chain > - check index reference > - check data record references index: 1 > - check data record references index: 2 > - check data record references index: 3 > - check data record references index: 4 > - check data record references index: 5 > - check data record references index: 6 > - check data record references index: 7 > - check record links > myisamchk: warning: Found 22079089 partsShould be: 0 parts > MyISAM-table 'White' is usable but should be fixed > [root@db1 elenco2g]# ls -l > total 3433832 > -rw-rw1 mysqlmysql1964830232 Feb 28 22:40 White.MYD > -rw-rw1 mysqlmysql1547953152 Feb 28 23:22 White.MYI > -rw-rw1 mysqlmysql9159 Feb 28 18:15 White.frm > [root@db1 elenco2g]# myisamchk -r --tmpdir=/var/lib/mysql/tmp White; > - recovering (with sort) MyISAM-table 'White' > Data records: 22079089 > - Fixing index 1 > - Fixing index 2 > - Fixing index 3 > - Fixing index 4 > - Fixing index 5 > - Fixing index 6 > - Fixing index 7 > [root@db1 elenco2g]# myisamchk White; > Checking MyISAM file: White > Data records: 22079089 Deleted blocks: 0 > - check file-size > - check key delete-chain > - check record delete-chain > - check index reference > - check data record references index: 1 > - check data record references index: 2 > - check data recor
Re: [PHP] Next and Preview Row
Yes, but how to get ONLY the 3 records you need ? Because often in an application, you don't care about the other records. In this case AFAIK, there is no other solution than issuing at least two queries : SELECT * FROM table WHERE field<='ID00025' ORDER BY field DESC LIMIT 2 SELECT * FROM table WHERE field>'ID00025' ORDER BY field ASC LIMIT 1 - Original Message - From: "Chris Boget" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; "Raymond Gubala" <[EMAIL PROTECTED]>; "Mark Lo" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, February 22, 2002 9:17 PM Subject: Re: [PHP] Next and Preview Row > > It seems worst to me because in your case mysql has to retrieve all the > > rows. If it's a table with 1 million records or more, this should hurt ;) > > As I said, it was pseudo code. Now, imagine that you were just > getting the records for a particular user? a particular application? > Where there won't be millions and millions of rows? > > Chris > > > - > 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: [PHP] Next and Preview Row
Hi, It seems worst to me because in your case mysql has to retrieve all the rows. If it's a table with 1 million records or more, this should hurt ;) Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Chris Boget" <[EMAIL PROTECTED]> To: "Raymond Gubala" <[EMAIL PROTECTED]>; "Mark Lo" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, February 22, 2002 4:45 PM Subject: Re: [PHP] Next and Preview Row > > The solution I have been using is to do three queries similar to the below > > SELECT * FROM table WHERE field='ID00025' > > SELECT * FROM table WHERE field<'ID00025' ORDER BY field DESC LIMIT 0,1 > > SELECT * FROM table WHERE field>'ID00025' ORDER BY field ASC LIMIT 0,1 > > If you whish more row returned change the number in the LIMIT > > Why bother with 3 queries? It's a waste of resources, especially if > you are working with the same record set... > > Example (pseudo)code: > > > $query = "SELECT * FROM table"; > $result = mysql( $dbname, $query ); > >for( $i = 0; $i < mysql_num_rows( $result ); $i++ ) { > echo "Previous field: " . mysql_result( $result, ( $i - 1 ), "field" ); > echo "Current field: " . mysql_result( $result, $i, "field" ); > echo "Next field: " . mysql_result( $result, ( $i + 1 ), "field" ); > > } > > ?> > > Chris > > > - > 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: Got error 124 from table handler.
Hi, Yes, just write : perror 124 >perror 124 >Error code 124: Wrong medium type >124 = Wrong index given to function Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Christian M. Stamgren" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, February 22, 2002 11:49 AM Subject: Got error 124 from table handler. > Hi list - > > Is there a list off MySQL error-codes avalible? > Something like Oracle's oerr. program? > > We are having trouble with one of our MySQL databases. The error is: > > "Got error 124 from table handler." > > We get this error randomly on some of our MyISAM tables. > "Optimize table" seems to fix the problem temporary. > > MySQL: > Server version 4.0.1-alpha > > OS: > Splack Linux (Slackware port for sparc) running on a Sun Enterprise 4500. > > > Best regards, > > -- > Christian M. Stamgren > Senior System Developer > > Cention Group AB > Lästmakargatan 15 > Box 7494 SE-103 92 Stockholm > +46 (0)8 578 420 86 | direct > +46 (0)8 578 420 99 | fax > +46 (0)703 41 44 27 | mobile > [EMAIL PROTECTED] > www.centiongroup.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: SELECT and UPDATE in one query?
Hi, I think you can try to use REPLACE INTO table (a,b,c,d) SELECT e,f,g,h FROM table2 WHERE field=what_you_want. Best Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Felix Richter" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, February 20, 2002 3:01 PM Subject: SELECT and UPDATE in one query? Hi! I need to SELECT some rows using a lengthy WHERE-clause and then to UPDATE these rows just to set a flag. What is the best way to do this (performance-wise)? Being able to do that in one single query would be great because otherwise I had to use transactions to be sure to match _exactly_ the selected rows. Felix - 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: Warning, Found xxx parts Should be: 0 parts
Hi, This is a bug in myisamchk. The problem is already fixed in mysql-4.0.2 : http://www.mysql.com/doc/N/e/News-4.0.2.html "Fixed new bug in myisamchk where it didn't correctly update number of ``parts'' in the MyISAM index file. " Best Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Amir Aliabadi" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, February 18, 2002 6:58 AM Subject: Re: Warning, Found xxx parts Should be: 0 parts > I had a typo I was trying to use myisamchk -r not -f to fix it which did > not fix it. > > myisamchk -o seems to fix the problem. > > The real question is why am i getting this when creating index(s). > Although I'm using the Alpha version, I don't get a warm fuzzy > > Any responses on what could be causing this would be great. > > On Sun, 2002-02-17 at 20:36, [EMAIL PROTECTED] wrote: > > Description: > > After inserting 1062351 records into a table I create 2 index on the table. > > It seems like after creating the indexs (test have shown that the error appears after creating the first index) I get the following warning when i do a CHECK TABLES mytablename or a mysisamchk -a mytablename: > > > > Found 1062351 partsShould be: 0 parts > > > > I have tried to fix with myisamchk -f mytablename but it does not correct the problem > > > > How-To-Repeat: > > I cannot include the data set I am loading. > > the basic step are: > > DROP mytablename IF EXISTS mytablename; > > CREATE TABLE mytablename > > ( myid1 int, > > title text, > > description text, > > classid int, > > distribution int > > ); > > LOAD DATA CONCURRENT INFILE "/usr/mydataset/mydata.dat" INTO TABLE mytablename FIELDS TERMINATED BY '~~'; > > ALTER TABLE mytablename ADD INDEX idx_myid1 (myid1); > > ALTER TABLE mytablename ADD INDEX idx_dist (distribution); > > CHECK TABLES mytablename; > > > > after the above step i get the warning: > > Found 1062351 parts Should be: 0 parts > > > > The number of record in mytablename is 1062351 > > > > Fix: > > This is a warning. I don't have a fix > > > > >Submitter-Id: > > >Originator: [EMAIL PROTECTED] > > >Organization: > > > > >MySQL support: none > > >Synopsis: Warning: Found 1062351 parts Should be: 0 parts after creating index > > >Severity: non-critical > > >Priority: low > > >Category: mysql > > >Class: sw-bug > > >Release: mysql-4.0.1-alpha (Official MySQL RPM) > > >Server: /usr/bin/mysqladmin Ver 8.23 Distrib 4.0.1-alpha, 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.1-alpha > > Protocol version 10 > > Connection Localhost via UNIX socket > > UNIX socket /var/lib/mysql/mysql.sock > > Uptime: 4 days 16 hours 14 min 1 sec > > > > Threads: 2 Questions: 1582 Slow queries: 87 Opens: 409 Flush tables: 6 Open tables: 6 Queries per second avg: 0.004 > > >Environment: > > > > System: Linux bigbone 2.4.16 #3 SMP Thu Dec 13 21:15:14 PST 2001 i686 unknown > > Architecture: i686 > > > > Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc > > GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs > > gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) > > Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-ex ceptions -fno-rtti -mpentium' LDFLAGS='' > > LIBC: > > lrwxrwxrwx1 root root 13 Dec 23 09:39 /lib/libc.so.6 -> libc-2.2.4.so > > -rwxr-xr-x1 root root 5723311 Dec 8 06:04 /lib/libc-2.2.4.so > > -rw-r--r--1 root root 27314296 Dec 8 06:02 /usr/lib/libc.a > > -rw-r--r--1 root root 178 Dec 8 06:02 /usr/lib/libc.so > > Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-clien t-ldflags=-all-static --without-berkeley-db --with-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/us r/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-embe dded-server --enable-thread-safe-client '--with-comment=Official MySQL RPM' > > > > > > > > - > > 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: information on mysql 4.0.2
Hi, No problem so far with mysql 4.0.x, no crash at all on a production server with a really high load. If you want to use mysql 4.0.2, take a look here : http://www.mysql.com/doc/I/n/Installing_source_tree.html Best Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Jean-Francois Dionne" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, February 16, 2002 2:03 AM Subject: information on mysql 4.0.2 > I see people speaking about mysql 4.0.2... but where you get it? they write > some thing about it on web page but 4.0.2 is not in the download list... > maybe someone can say if mysql 4 is stable? I mean if I can use it without > 100 crash in 1 hour :) > Sorry for bad english :) > Jean-Francois Dionne > > > - > 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: Request for help in testing new replication code in 4.0.2
Hi, In myisamchk.c replace the following line : if (argument && *argument == '0') DBUG_POP(); else DBUG_PUSH(argument ? argument : "d:t:o,/tmp/myisamchk.trace"); by if (argument && *argument == '0') { DBUG_POP(); } else { DBUG_PUSH(argument ? argument : "d:t:o,/tmp/myisamchk.trace"); } Like this it works perfectly for me :) Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Sasha Pachev" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, February 10, 2002 10:20 PM Subject: Re: Request for help in testing new replication code in 4.0.2 > On Sat, Feb 09, 2002 at 09:41:25PM -0700, Sasha Pachev wrote: > > Dear MySQL users, > > > > I have just pushed my latest changes in the replication code in our > > 4.0 development tree, which change the slave to use two threads - > > I/O thread that gets the data from the master and logs it, and SQL > > thread which processes the logged data. I have tested it > > extensively, and it has passed all of my tests. However, as we all > > know, field testing tends to find nasty bugs that have not been > > caught in regression tests and visual code examination. > > Excellent. I'm ready to test... > > > Testing the new code is relatively easy, and does not require > > risking the stability of your application by running alpha > > code. Here is what you need to do: > > > > Find a machine that will act as a test server. With the availablity > > of Linux and FreeBSD, this can be done cost-effectively by > > sacrificing some useless dust-gathering PC in your office. In some > > case, you may even try it with your own desktop. > > Got it. > > > If you want to do it this very moment, read the instructions at > > http://www.mysql.com/doc/I/n/Installing_source_tree.html and install > > a pre-release 4.0.2 MySQL on your test server. You may also wait > > until 4.0.2 is released, but in that case, replication in 4.0.2 may > > have a bug you could have caught on your system and it will not be > > fixed until 4.0.3. > > I just updated my bitkeeper tree and have been having a hell of a time > getting it to build. I've checked that I have automake 1.4 and > autoconf 2.13. > > I run `BUILD/compile-pentium --prefix=/home/mysql` and it fails after > several minutes. The output is large, so I've posted it here: > > http://public.yahoo.com/~jzawodn/mysql-build.log > > (the good stuff is at the end, of course...) > > I believe I've followed the directions properly, but let me know if it > looks like I've missed anything. > > Thanks, > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > > MySQL 3.23.47-max: up 3 days, processed 112,608,902 queries (396/sec. avg) > > - > 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]> > > - 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: Duplicate Records
Hi, You can try to use ALTER IGNORE TABLE syntax : ALTER IGNORE TABLE your_table ADD UNIQUE(Id); Regards, Jocelyn Fournier - Original Message - From: "Rich" <[EMAIL PROTECTED]> To: "MySql" <[EMAIL PROTECTED]> Sent: Sunday, February 10, 2002 5:31 PM Subject: Duplicate Records > How does one go about removing one of two identical records in a MySQL > database? My mistake in an earlier database was not applying a unique > number to each record (1, 2, 3, ---). I know it's possible to use the > DISTINCT operator to show only one of the identical records in a result, > but that does not remove one of them from the database. > > One thought that I had would be to add a unique number to each record, > and that could probably be done manually, one record at a time, but is > there a way to automate the process so that unique numbers could be > assigned with one command? > > TIA > > Rich > > -- > [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 > > - 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: Request for help in testing new replication code in 4.0.2
Hi, In myisamchk.c replace the following line : if (argument && *argument == '0') DBUG_POP(); else DBUG_PUSH(argument ? argument : "d:t:o,/tmp/myisamchk.trace"); by if (argument && *argument == '0') { DBUG_POP(); } else { DBUG_PUSH(argument ? argument : "d:t:o,/tmp/myisamchk.trace"); } Like this it works perfectly for me :) Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Sasha Pachev" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, February 10, 2002 10:20 PM Subject: Re: Request for help in testing new replication code in 4.0.2 > On Sat, Feb 09, 2002 at 09:41:25PM -0700, Sasha Pachev wrote: > > Dear MySQL users, > > > > I have just pushed my latest changes in the replication code in our > > 4.0 development tree, which change the slave to use two threads - > > I/O thread that gets the data from the master and logs it, and SQL > > thread which processes the logged data. I have tested it > > extensively, and it has passed all of my tests. However, as we all > > know, field testing tends to find nasty bugs that have not been > > caught in regression tests and visual code examination. > > Excellent. I'm ready to test... > > > Testing the new code is relatively easy, and does not require > > risking the stability of your application by running alpha > > code. Here is what you need to do: > > > > Find a machine that will act as a test server. With the availablity > > of Linux and FreeBSD, this can be done cost-effectively by > > sacrificing some useless dust-gathering PC in your office. In some > > case, you may even try it with your own desktop. > > Got it. > > > If you want to do it this very moment, read the instructions at > > http://www.mysql.com/doc/I/n/Installing_source_tree.html and install > > a pre-release 4.0.2 MySQL on your test server. You may also wait > > until 4.0.2 is released, but in that case, replication in 4.0.2 may > > have a bug you could have caught on your system and it will not be > > fixed until 4.0.3. > > I just updated my bitkeeper tree and have been having a hell of a time > getting it to build. I've checked that I have automake 1.4 and > autoconf 2.13. > > I run `BUILD/compile-pentium --prefix=/home/mysql` and it fails after > several minutes. The output is large, so I've posted it here: > > http://public.yahoo.com/~jzawodn/mysql-build.log > > (the good stuff is at the end, of course...) > > I believe I've followed the directions properly, but let me know if it > looks like I've missed anything. > > Thanks, > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > > MySQL 3.23.47-max: up 3 days, processed 112,608,902 queries (396/sec. avg) > > - > 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]> > > - 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: Request for help in testing new replication code in 4.0.2
Hi, In myisamchk.c replace the following line : if (argument && *argument == '0') DBUG_POP(); else DBUG_PUSH(argument ? argument : "d:t:o,/tmp/myisamchk.trace"); by if (argument && *argument == '0') { DBUG_POP(); } else { DBUG_PUSH(argument ? argument : "d:t:o,/tmp/myisamchk.trace"); } Like this it works perfectly for me :) Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Sasha Pachev" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, February 10, 2002 10:20 PM Subject: Re: Request for help in testing new replication code in 4.0.2 > On Sat, Feb 09, 2002 at 09:41:25PM -0700, Sasha Pachev wrote: > > Dear MySQL users, > > > > I have just pushed my latest changes in the replication code in our > > 4.0 development tree, which change the slave to use two threads - > > I/O thread that gets the data from the master and logs it, and SQL > > thread which processes the logged data. I have tested it > > extensively, and it has passed all of my tests. However, as we all > > know, field testing tends to find nasty bugs that have not been > > caught in regression tests and visual code examination. > > Excellent. I'm ready to test... > > > Testing the new code is relatively easy, and does not require > > risking the stability of your application by running alpha > > code. Here is what you need to do: > > > > Find a machine that will act as a test server. With the availablity > > of Linux and FreeBSD, this can be done cost-effectively by > > sacrificing some useless dust-gathering PC in your office. In some > > case, you may even try it with your own desktop. > > Got it. > > > If you want to do it this very moment, read the instructions at > > http://www.mysql.com/doc/I/n/Installing_source_tree.html and install > > a pre-release 4.0.2 MySQL on your test server. You may also wait > > until 4.0.2 is released, but in that case, replication in 4.0.2 may > > have a bug you could have caught on your system and it will not be > > fixed until 4.0.3. > > I just updated my bitkeeper tree and have been having a hell of a time > getting it to build. I've checked that I have automake 1.4 and > autoconf 2.13. > > I run `BUILD/compile-pentium --prefix=/home/mysql` and it fails after > several minutes. The output is large, so I've posted it here: > > http://public.yahoo.com/~jzawodn/mysql-build.log > > (the good stuff is at the end, of course...) > > I believe I've followed the directions properly, but let me know if it > looks like I've missed anything. > > Thanks, > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > > MySQL 3.23.47-max: up 3 days, processed 112,608,902 queries (396/sec. avg) > > - > 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]> > > - 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: Duplicate Records
Hi, You can try to use ALTER IGNORE TABLE syntax : ALTER IGNORE TABLE your_table ADD UNIQUE(Id); Regards, Jocelyn Fournier - Original Message - From: "Rich" <[EMAIL PROTECTED]> To: "MySql" <[EMAIL PROTECTED]> Sent: Sunday, February 10, 2002 5:31 PM Subject: Duplicate Records > How does one go about removing one of two identical records in a MySQL > database? My mistake in an earlier database was not applying a unique > number to each record (1, 2, 3, ---). I know it's possible to use the > DISTINCT operator to show only one of the identical records in a result, > but that does not remove one of them from the database. > > One thought that I had would be to add a unique number to each record, > and that could probably be done manually, one record at a time, but is > there a way to automate the process so that unique numbers could be > assigned with one command? > > TIA > > Rich > > -- > [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 > > - 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: Strange MyISAMCHK warning
Of course not ;) I only run myisamchk -a on several table, and I noticed sometime a similar message (although the tables seem to work perfectly) Jocelyn - Original Message - From: "Gerald Clark" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, January 24, 2002 3:33 PM Subject: Re: Strange MyISAMCHK warning > Since you said "tables" ( plural ), I hope you are not running myisamchk > while the server is running. > > Fournier Jocelyn [Presence-PC] wrote: > > >Hi, > > > >myisamchk shows me the following warning message on some of my tables : > > > >myisamchk -a searchjoinhardwarefr2.MYI > > > >Checking MyISAM file: searchjoinhardwarefr2.MYI > >Data records: 264494 Deleted blocks:1288 > >- check file-size > >- check key delete-chain > >- check record delete-chain > >- check index reference > >- check data record references index: 1 > >- check data record references index: 2 > >- check data record references index: 3 > >- check data record references index: 4 > >- check record links > >myisamchk: warning: Found 265789 partsShould be: 1723 > >parts > > > >What does this mean ? (I see only myisamchk -o can fixed this in my case) > > > >Thank you :) > > > >Best Regards, > > > >Jocelyn Fournier > >Presence-PC > > > > > >- > >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
Strange MyISAMCHK warning
Hi, myisamchk shows me the following warning message on some of my tables : myisamchk -a searchjoinhardwarefr2.MYI Checking MyISAM file: searchjoinhardwarefr2.MYI Data records: 264494 Deleted blocks:1288 - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check data record references index: 3 - check data record references index: 4 - check record links myisamchk: warning: Found 265789 partsShould be: 1723 parts What does this mean ? (I see only myisamchk -o can fixed this in my case) Thank you :) Best Regards, Jocelyn Fournier Presence-PC - 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: Version number after '!' : what is the good syntax ?
Hi, Ok, it's 40001 according to #define MYSQL_VERSION_ID 40001 ;) Regards, Jocelyn - Original Message - From: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, January 19, 2002 3:03 PM Subject: Version number after '!' : what is the good syntax ? > Hi, > > I'm wondering what is the good syntax with version number after '!' with > MySQL 4 : > > Should I use : > > /*!40001 SQL_CALC_FOUND_ROWS */ > > or > > /*!401 SQL_CALC_FOUND_ROWS */ > > Thank you ! > > Regards, > > Jocelyn Fournier > Presence-PC > > > - > 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
Version number after '!' : what is the good syntax ?
Hi, I'm wondering what is the good syntax with version number after '!' with MySQL 4 : Should I use : /*!40001 SQL_CALC_FOUND_ROWS */ or /*!401 SQL_CALC_FOUND_ROWS */ Thank you ! Regards, Jocelyn Fournier Presence-PC - 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: Column Alias Bug??
Hi, Try : SELECT a,COUNT(*) AS z FROM aa GROUP BY a HAVING z>1; Regards, Jocelyn - Original Message - From: "Rick Emery" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 18, 2002 10:30 PM Subject: Column Alias Bug?? > Is this a bug? I can't find an answer to this question in FAQs or archives. > > CREATE TABLE aa ( a int); > INSERT INTO aa VALUES (1),(2),(3),(2),(4),(5),(1),(6),(3); > > the following : > mysql> select a,count(*) as z from aa where z>1 group by a; > > displays this error: > ERROR 1054: Unknown column 'z' in 'where clause' > > Why isn't z recognized as a column identifier? > > > - > 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: Why does DISTINCT take so long time ??
I understand it has to re-iterate if the number of rows in the result without DISTINCT is greater than the limit clause, but if the result without DISTINCT is lower, it should be faster to perform the DISTINCT on the result directly (or perhaps I'm missing something ? ;)). - Original Message - From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, January 18, 2002 4:27 PM Subject: Re: Why does DISTINCT take so long time ?? > Fournier Jocelyn [Presence-PC] writes: > > Hi, > > > > Yes it is 4.0.1. > > But the first query has also to make a temporary table with 2 million rows, > > it's why I don't understand the delta between the query without DISTINCT and > > the query with DISTINCT. > > The "remove duplicates" doesn't occurs after the join was performed ?? (it > > should be really fast in this case) > > > > Regards, > > > > Jocelyn > > DISTINCT simply has to re-iterate. > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ 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: Why does DISTINCT take so long time ??
Hi, Yes it is 4.0.1. But the first query has also to make a temporary table with 2 million rows, it's why I don't understand the delta between the query without DISTINCT and the query with DISTINCT. The "remove duplicates" doesn't occurs after the join was performed ?? (it should be really fast in this case) Regards, Jocelyn - Original Message - From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, January 18, 2002 3:57 PM Subject: Re: Why does DISTINCT take so long time ?? > Fournier Jocelyn [Presence-PC] writes: > > Hi, > > > > I've notice sometimes DISTINCT clause take a really high amount of time to > > remove duplicates whereas it should be really quick (I assume it should be > > ;)) > > > > [skip] > > > > > Why does it take so much time to remove duplicates in only 58 rows ?? > > > > Thank you :) > > > > Regards, > > > > Jocelyn Fournier > > Presence-PC > > > > > Hi! > > Because it has to make first a temporary table with 2 million rows. > > And is it 4.0.1 ? > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ 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
Why does DISTINCT take so long time ??
Hi, I've notice sometimes DISTINCT clause take a really high amount of time to remove duplicates whereas it should be really quick (I assume it should be ;)) My first query is : mysql> SELECT COUNT(*) as count, numreponse FROM searchhardwarefr3 GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100; it returns : +---++ | count | numreponse | +---++ | 2 | 111239 | | 2 | 108183 | | 2 | 73 | | 2 | 111383 | | 2 | 111239 | | 2 | 111760 | | 3 | 109166 | | 2 | 09 | | 3 | 109166 | +---++ 58 rows in set (14 min 51.15 sec) My second query is : mysql> SELECT DISTINCT COUNT(*) as count, numreponse FROM searchhardwarefr3 GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100; Well I'm not enough patient to wait, but when I stop the querie, it has been running for more than 3500 seconds... (and more than 45mn in 'Removing duplicates' state...) mysql> EXPLAIN SELECT DISTINCT COUNT(*) as count, numreponse FROM searchhardwarefr3 GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100; +---+---+---+-+-+--+ -+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra| +---+---+---+-+-+--+ -+--+ | searchhardwarefr3 | index | NULL | PRIMARY | 75 | NULL | 2026032 | Using index; Using temporary | +---+---+---+-+-+--+ -+--+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT COUNT(*) as count, numreponse FROM searchhardwarefr3 GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100; +---+---+---+-+-+--+ -+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra| +---+---+---+-+-+--+ -+--+ | searchhardwarefr3 | index | NULL | PRIMARY | 75 | NULL | 2026032 | Using index; Using temporary | +---+---+---+-+-+--+ -+--+ 1 row in set (0.00 sec) Why does it take so much time to remove duplicates in only 58 rows ?? Thank you :) Regards, Jocelyn Fournier Presence-PC - 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: Optimization
Hi, It's already done since MySQL 4.0.1. Take a look here : http://www.mysql.com/doc/Q/u/Query_Cache.html Regards, Jocelyn - Original Message - From: "Ioakim Spyros" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 17, 2002 6:52 PM Subject: FW: Optimization Hello all, I'm having real trouble trying to optimize MySQL cause I can't believe that MSSQL is faster. My configurations are as follows: MSSQL 2000 on W2K server. PIII 733 - 512 MB memory. MySQL-3.23.47-1 on Redhat 7.2. Dual PIII 1000 - 1.128 GB memory I have a PHP script that runs on a Redhat 7.1 - PIII 500 640 MB memory. The php script takes a username from a mysql table and runs a query for each of the usernames on another table. The test is that I have 2 different versions of the script that do the exactly same thing but one queries the MSSQL server and the other the MySQL. The MSSQL version takes 28 secs while the MySQL takes 34 secs. As you can see the MSSQL is much more slower with less RAM. I said what the heck I will use the my-huge.cnf to see if it makes any difference. Unfortunately nothing changed and then I started panicking.. It can't be true! I noticed that MSSQL caches the queries while MySQL doesn't. In my script I might have this: select emails from dbo_Company where username=''; come up 5 or even 10 times. If I run it on mysql It takes always 0.26 secs while it appears the MSSQL caches the result and doesn't take any time at all. If I get the same query in the script 10 times I'm losing immediately 2.34 secs with MySQL. Is there a way to get the same behavior in MySQL.. Regards, Spyros - 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: "Too Many Connections" error
I agree with you, sometimes my server experiences some DoS and because MySQL cannot handle all that connections, the load goes really high and we can't do anything :( - Original Message - From: "Dave Dyer" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, January 16, 2002 11:17 PM Subject: "Too Many Connections" error > > Mysql in a shared environment is prone to punish all clients > with "too many connections" errors if one client is hyperactive. > There seems to be no way to deal with this within the standard > framework. > > This could be dealt with by adding "max_connections_per_ip" and "max_connections_per_user" to protect against deliberate or accidental > denial of service by slurping all the available connections. > > > - > 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: Stability problems on 4-way server
Hi, What about a 2.4.17 with 'aa' patch, as it seems to have much less VM problem than vanilla kernel or rmap-10c patched kernel... (I have personnaly installed 2.4.17rc2 + patch rc2aa2 with no problem : http://www.kernel.org/pub/linux/kernel/people/andrea/kernels/v2.4/2.4.17rc2a a2.bz2 ) Best Regards, Jocelyn Fournier Presence-PC www.presence-pc.com - Original Message - From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, January 09, 2002 3:05 PM Subject: Re: Stability problems on 4-way server Jörgen Winqvist writes: > I run RH supported kernel 2.4.7-10enterprise on such a server with > similar problems. All four CPU's runs off with a lot off system. I can > provoke this behaviour by generating disk i/o. A 'cp' might be it. I > often get it when the full backup is running. I've also tested 2.4.9-13 > with even worse result. I previously used the 2.4.8 vanilla kernel whith > good results. I was planning to test 2.4.17 next week but now i don't > know... > > I use MySql 4.01 > If you use our binary, 2.4.17 should be just fine ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ 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: How to make use of mysql++ with gcc 2.96
Hi, I have a question about gcc 3.0.3 : since I have installed it, it refuses to compile because it detect c++ as a crosscompiler : checking whether the C++ compiler (c++ -Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment - W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int -Wparent heses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline -W sign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-construc tors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -g ) is a cross-compiler... yes And then it failed : checking return type of sprintf... configure: error: can not run test program while cross compiling Any idea of what I have to do to run the compilation successfully ? Thank you in advance, Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, December 25, 2001 2:02 PM Subject: Re: How to make use of mysql++ with gcc 2.96 > luhar-123 writes: > > Hi guys, > >i have installed red hat linux 7.2 and mysql server version 3.23.42. but i need mysql++ also.but it seems that gcc version 2.96 will give lot of problems for mysql++. can any one pls give me a solution to use mysql++ on linux 7.2. > > Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com > > > > Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in > > Yes, install gcc 2.95.2 or 3.0.3 > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ 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: 4.0.1?
Hi, It should be really soon, Monty wrote me today he was building a distribution with the current 4.0.1 code... Best Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Franklin Schmidt" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, December 14, 2001 1:41 AM Subject: 4.0.1? > Does anyone know when 4.0.1 will be released? > > > > > > > dear mail filter, use SQL to QUERY DATABASE TABLEs > > - > 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: [PHP] Performance
Yes, If you have a submit button or if you reload the page, the link is closed, excepted if you are using mysql_pconnect. (be aware of the timeout with mysql_pconnect - wait_timeout parameter in the my.cnf file - ) Jocelyn - Original Message - From: "markus|lervik" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, December 13, 2001 11:15 PM Subject: Re: [PHP] Performance > On Thu, 2001-12-13 at 23:56, Fournier Jocelyn [Presence-PC] wrote: > > Hi, > > > > You absolutely don't have to reconnect to the database each time a PHP-block > > ends ! (it would be completely awfull !!). > > Don't you forget to specify the mysql link in you 'mysql_query' ?? > > Nope, I always specify the mysql link in my queries. But the problem > remains. As I'm at home for the moment, I can't try out all the > suggestions, but I have tried the p_connect, and it most certanly didn't > work. I'll have to try to pinpoint the problem more closely tomorrow at > work. > > It just might be (came to think of it now), that php closes the > connection every time I use a submit-button and it reloads the page. > Technically speaking, that would be "ending a script", right? > > -Markus > > -- > Markus Lervik > Linux administrator with a kungfoo grip > Vasa City Library - Public Library > > > - 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: [PHP] Performance
Hi, You absolutely don't have to reconnect to the database each time a PHP-block ends ! (it would be completely awfull !!). Don't you forget to specify the mysql link in you 'mysql_query' ?? Regards, Jocelyn Fournier Presence-PC - Original Message - From: "markus|lervik" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, December 13, 2001 9:29 PM Subject: RE: [PHP] Performance > On Thu, 2001-12-13 at 22:16, Ron Jamison wrote: > > I'm fairly sure there's no difference in performance when using PHP > tags > > mixed with HTML blocks as you are doing. > > > > Think of it as PHP knowing that it should send everything not in > tags > > directly to the browser. Like an echo or a print, but without needing > to > > specify calls to those functions. > > > > > > > > > > > > > > > > > > I'm not -that- worried about performance loss for switching between, > php and html, what annoys me (and would probably slow down performance > quite a bit), is that every time I have to do a database query, I have > to use mysql_connect again, because as Prottoss pointed out, PHP drops > the connection every time a PHP-block ends. > > Not that it is that a big issue for my particular application, but > knowing a workaround to this problem would be quite useful in the > future. > > > > Cheers, > Markus > > -- > Markus Lervik > Linux administrator with a kungfoo grip > Vasa City Library - Public Library > > > > - > 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
Status information in .err file ?
Hi, I'm wondering what are those informations in the mysql .err file which sometimes appear : Status information: Current dir: /home/mysql/ Current locks: lock: 868e93c: read read : 868f0b8 (79951:1); lock: 8615e94: lock: 85ba4bc: lock: 856b104: lock: 8521ad4: lock: 84d84a4: lock: 8448af4: lock: 83ff334: lock: 83b5d04: lock: 836c6cc: lock: 8323094: lock: 82d9a5c: lock: 829042c: lock: 828975c: lock: 827dec4: key_cache status: blocks used: 4734 not flushed: 0 w_requests: 0 writes: 0 r_requests: 0 reads: 0 handler status: read_key:0 read_next: 0 read_rnd 0 read_first: 0 write: 0 delete 0 update: 0 Table status: Opened tables: 0 Open tables:1 Open files: 2 Open streams: 0 Thanks in advance, Jocelyn Fournier Presence-PC - 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: Problem with bk://work.mysql.com ?
Hi, Take a look here : http://www.mysql.com/doc/I/n/Installing_source_tree.html Best Regards, Jocelyn Fournier - Original Message - From: "Alexander Skwar" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, December 12, 2001 10:47 PM Subject: Re: Problem with bk://work.mysql.com ? So sprach »Fournier Jocelyn [Presence-PC]« am 2001-12-12 um 15:04:13 +0100 : > When I try to do a "bk pull", I have the following unexpected message : What's a "bk pull"? Alexander Skwar -- How to quote: http://learn.to/quote (german) http://quote.6x.to (english) Homepage: http://www.iso-top.de | Jabber: [EMAIL PROTECTED] iso-top.de - Die günstige Art an Linux Distributionen zu kommen Uptime: 5 days 9 hours 9 minutes - 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: High load problem with 3.23.45
Hi, I have a similar load problem when I start MySQL-4.0 and there is high number of apache process requesting MySQL connection. I had to shutdown and restart apache otherwise the load often goes up to 200 and more (!!). (there are more than 250 visitors always connected to the server, so it generates high stress for the server). This behaviour seems to occur since we upgrade the server from 2.2.x kernel to 2.4.x kernel (actually a 2.4.12-ac6 kernel). I'm wondering if this issue couldn't be linked to the new VM in 2.4.x kernel... If someone have any idea about how to solve this problem... Thanks in advance :) Jocelyn Fournier Presence-PC - Original Message - From: "Arndt Jenssen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, December 12, 2001 8:27 PM Subject: High load problem with 3.23.45 > Maybe some coding/mysql master out there knows an answer to this tricky > problem... > > We've recently migrated all our mysql servers from 3.23.42 to 3.23.45 without > any problems except for one server. > That linux server shows after startup of mysql after 1 or 2 minutes an > exceptionally high load and the load will rise until the server ends up in > nirvana (I've seen a load of 80 and more) > > Some infos on the server: > Distribution: Suse 7.2 > kernel: 2.4.4 > glibc: 2.2.2 > gcc: 2.9.53 > > We've profiled mysqld with following results (grpof): > > *** > Flat profile: > > Each sample counts as 0.01 seconds. > % cumulative self self total > time seconds secondscalls us/call us/call name > 48.10 4.69 4.69 3682 1273.76 1273.76 _nl_get_alt_digit > 23.90 7.02 2.33 getpid > 20.92 9.06 2.04 regex_compile > 4.10 9.46 0.40 708096 0.56 0.56 __array_type_info > type_info function > 1.44 9.60 0.14 lock_update_insert > 0.31 9.63 0.03 12 2500.00 2500.09 chk_del > 0.21 9.65 0.02 28869.4469.44 find_best(JOIN *, > unsigned long, unsigned int, double, double) > 0.21 9.67 0.02 __mktime_internal > 0.10 9.68 0.01 285302 0.04 0.04 sort_get_next_record > 0.10 9.69 0.0121066 0.47 0.47 _IO_vfscanf > 0.10 9.70 0.01 4106 2.44 9.73 > mysql_execute_command(void) > *** > As you can see most of the time is spend in the functions _nl_get_alt_digit, > getpid and regex_compile. > > The profile log of another server running mysql 3.23.45 looks completely > different. Most of the time on this server is spend in the select method what > seems to be just perfect. > > Here is the profile log of the functioning server: > *** > Flat profile: > > Each sample counts as 0.01 seconds. > % cumulative self self total > time seconds secondscalls us/call us/call name > 13.92 0.11 0.11 select > 11.39 0.20 0.091 9.00 9.33 buf_pool_create > 7.59 0.26 0.06 memset > 6.33 0.31 0.05 __libc_accept > 6.33 0.36 0.05 __mcount_internal > 6.33 0.41 0.05 chunk_alloc > 6.33 0.46 0.05 vfprintf > 5.06 0.50 0.04 336311.8911.89 init_io_cache > 5.06 0.54 0.04 > pthread_handle_sigrestart > 2.53 0.56 0.02 2721 7.35 7.35 create_new_thread(THD *) > 2.53 0.58 0.02 12 1666.67 1666.67 hash_create > 2.53 0.60 0.021 2.00 21666.67 > recv_apply_hashed_log_recs > 2.53 0.62 0.02 __pthread_alt_unlock > ** > > We've compiled mysql on both servers with following configure options: > > CFLAGS="-O3 -p -mpentiumpro" CXX=gcc CXXFLAGS="-O3 -p -mpentiumpro > -felide-constructors -fno-exceptions -fno-rtti" ./configure > --prefix=/usr/local/mysql --enable-assembler --with-debug > --with-mysqld-ldflags="-all-static -p" --localstatedir=/usr/local/mysql/da ta > -with-innodb > > And both servers are running the same system and (hopefully) the same > configuration. > > Any ideas > > Thanks in advance for your help. > > - > 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 posti
Problem with bk://work.mysql.com ?
Hi, When I try to do a "bk pull", I have the following unexpected message : >bk pull bk://work.mysql.com:7001: No route to host What's wrong ? Best Regards, Jocelyn Fournier Presence-PC - 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: Using UNION and UNION ALL on a database running 4.0
Hi, Try : SELECT * FROM tbl1 UNION ALL SELECT * FROM tbl2 UNION SELECT * FROM tbl3 UNION SELECT * FROM tbl15 WHERE title LIKE 'something' ORDER BY date_created DESC Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Deryck Henson" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Sunday, November 04, 2001 2:26 AM Subject: Using UNION and UNION ALL on a database running 4.0 > Hi all, > > I have looked throught the manual and found that I could use a UNION syntax > to join a bunch of tables into 1 query. How the heck can I do this with 15 > tables, order the records by date_created desc and have there be WHERE title > LIKE 'something'. I have those other parts (order, etc) just need the union > syntax. here is my current non-working query: > > SELECT * FROM tbl1 UNION ALL WHERE title LIKE 'something' ORDER BY > date_created DESC > > Basicly, I want every single record in the database. Thanx ppl! And by the > way, the MySQL Manual is NO HELP AT ALL. too confusing and doesn't even > explain it fully. > > - Deryck Henson > - http://www.sourcecodecafe.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
Bug with Insert Delayed in MySQL 4.0 ?
Hi, A quite strange bug just occurs with MySQL 4.0 : When I execute show processlist, I get : mysql> show processlist; +---+-+---+++--+ ---+ --+ | Id| User| Host | db | Command| Time | State | Info | +---+-+---+++--+ ---+ --+ | 1684 | DELAYED | localhost | Hardwarefr | Delayed_insert | 656 | Waiting on cond | searchhardwarefr10 | | 1942 | DELAYED | localhost | Hardwarefr | Delayed_insert | 486 | Waiting on cond | searchhardwarefr11 | | 2035 | DELAYED | localhost | Hardwarefr | Delayed_insert | 909 | Waiting on cond | searchhardwarefr9 | | 2406 | DELAYED | localhost | Hardwarefr | Delayed_insert | 543 | Waiting on cond | searchconthardwarefr1 | | 4460 | DELAYED | localhost | Hardwarefr | Delayed_insert | 574 | Waiting on cond | searchconthardwarefr6 | | 4526 | DELAYED | localhost | Hardwarefr | Delayed_insert | 1396 | Waiting on cond | searchconthardwarefr10 | | 6105 | DELAYED | localhost | Hardwarefr | Delayed_insert | 1579 | Waiting on cond | searchconthardwarefr11 | | 8127 | DELAYED | localhost | Hardwarefr | Delayed_insert | 5053 | Waiting on cond | searchhardwarefr12 | | 20544 | DELAYED | localhost | Hardwarefr | Delayed_insert | 1802 | Waiting on cond | searchconthardwarefr9 | | 25776 | DELAYED | localhost | Hardwarefr | Delayed_insert | 2670 | Waiting on cond | searchhardwarefr8 | | 39954 | mysql | localhost | Hardwarefr | Query | 420 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date >= '2001-10-08'| | 39979 | DELAYED | localhost | Hardwarefr | Delayed_insert | 23 | Waiting on cond | searchhardwarefr7 | | 39984 | mysql | localhost | Hardwarefr | Query | 405 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date >= '2001-10-08'| | 40114 | mysql | localhost | Hardwarefr | Query | 311 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date >= '2001-10-08'| | 40149 | mysql | localhost | Hardwarefr | Query | 311 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='chipset' OR mot='i810' AND date >= '2000-10-08' | | 40165 | DELAYED | localhost | Hardwarefr | Delayed_insert | 72 | Waiting on cond | searchhardwarefr4 | | 40219 | mysql | localhost | Hardwarefr | Query | 277 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date >= '2001-10-08'| | 40224 | mysql | localhost | Hardwarefr | Query | 277 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date >= '2001-10-08'| | 40281 | mysql | localhost | Hardwarefr | Query | 210 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date >= '2001-10-08'| | 40283 | mysql | localhost | Hardwarefr | Query | 202 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='1.2' AND date >= '2001-01-08'| | 40303 | mysql | localhost | Hardwarefr | Query | 208 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date >= '2001-10-08'| | 40361 | mysql | localhost | Hardwarefr | Query | 189 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='antec' AND date >= '2001-08-08' | | 40363 | root| localhost | NULL | Query | 0| NULL | show processlist | | 40388 | mysql | localhost | Hardwarefr | Query | 171 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='celeron' AND date >= '2001-10-08'| | 40399 | mysql | localhost | Hardwarefr | Query | 174 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='antec' AND date >= '2001-08-08' | | 40417 | mysql | localhost | Hardwarefr | Query | 148 | Waiting for table | SELECT topic FROM searchhardwarefr8 WHERE mot='Leadtek' OR mot='GeForce' OR mot='III' OR mot='Ti200' | | 40448 | mysql | localhost | Hardwarefr | Query | 162 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='antec' AND date >= '2001-08-08' | | 40486 | mysql | localhost | Hardwarefr | Query | 147 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='celeron' AND date >= '2001-10-08'| | 40519 | DELAYED | localhost | Hardwarefr | Delayed_insert | 138 | Waiting on cond
Re: Indexing Problem
Hi, Your record_buffer and sort_buffer seem far too high. Don't forget MySQL could eat up ((sort_buffer + record_buffer) * max_connections + key_buffer) Mo of memory. Even with a max_connections set to 1, MySQL could eat in your case up to 7 Go of memory =) - Original Message - From: "Wai Lee" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, October 26, 2001 4:18 PM Subject: Indexing Problem > Hi all, > > Can someone tell me how to speed up the index creation??? > > I am trying to build an index for a 13,875,354 records(13 million) table > with 176,322 distinct vendor_id(VARCHAR(40)) in the table. > > the existing size of the transaction table: > transaction.MYD = 2,128,954,624 bytes > transaction.MYI = 1,096,510,464 bytes (there are already 5 indexes built in > this table) > transaciton.frm = 9,450 bytes > > I dig through the manual and changed any possible settings > > set-variable= max_heap_table_size=2000M > set-variable= key_buffer=2500M > set-variable= max_allowed_packet=1M > set-variable= table_cache=512 > set-variable= sort_buffer=2500M > set-variable= join_buffer=2500M > set-variable= record_buffer=2500M > set-variable= myisam_sort_buffer_size=2500M > set-variable= myisam_max_sort_file_size=2500M > set-variable= myisam_max_extra_sort_file_size=2500M > set-variable= thread_cache=8 > > > We are running mySQL in a 4G memory linux box, I believe the buffer size I > allocated to mySQL should cache the whole table. Instead, I am wrong, while > building a new index, I saw there was temporary files created for the > transaction table (the write disk activities were very source consuming) and > the single index creation took 4.5 hours to finished > Unbelievable > > Any suggestions will be greatly helpful!!! > > Lee > Zeborg Inc. > > > - > 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
Problem compiling MySQL 4.0 with GCC 3.0.1
Hi, I know this problem has already been reported (but I don't remember the solution :( ) : I encounter the following error after installing gcc 3.0.1 and trying to compile MySQL 4.0 : mysqlbinlog.o(.gnu.linkonce.d._ZTV9Log_event+0x14): undefined reference to `__cxa_pure_virtual' mysqlbinlog.o(.gnu.linkonce.d._ZTV9Log_event+0x18): undefined reference to `__cxa_pure_virtual' mysqlbinlog.o(.gnu.linkonce.d._ZTV9Log_event+0x30): undefined reference to `__cxa_pure_virtual' collect2: ld returned 1 exit status gmake[2]: *** [mysqlbinlog] Error 1 What should I do to make MySQL compile ? Thank you ! Jocelyn Fournier Presence-PC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql 4.0.0-alpha and php-4.0.6 and Apache
Oups sorry, bad news :( In fact it still doesn't works, I compiled PHP with the option --with-mysql, so PHP used his internal mysql client :( BTW how can I compile mysql-4.0 with the USE_OLD_FUNCTIONS option ? Thank you, Jocelyn Fournier - Original Message - From: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> To: "Willem Scholten" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, October 21, 2001 12:31 AM Subject: Re: Mysql 4.0.0-alpha and php-4.0.6 and Apache > Hi, > > I have the solution for this problem. > Before compiling php, edit the /ext/mysql/php_mysql.c file in the php > distribution. > > Then remove all the reference to mysql_connect, mysql_create_db and > mysql_drop_db : > > function_entry mysql_functions[] = { > //PHP_FE(mysql_connect, > NULL) > PHP_FE(mysql_pconnect, > NULL) > PHP_FE(mysql_close, > NULL) > PHP_FE(mysql_select_db, > NULL) > //PHP_FE(mysql_create_db, > NULL) > //PHP_FE(mysql_drop_db, > NULL) > PHP_FE(mysql_query, > NULL) > PHP_FE(mysql_unbuffered_query, > NULL) > PHP_FE(mysql_db_query, > NULL) > PHP_FE(mysql_list_dbs, > NULL) > PHP_FE(mysql_list_tables, > NULL) > PHP_FE(mysql_list_fields, > NULL) > PHP_FE(mysql_error, > NULL) > > /* for downwards compatability */ > PHP_FALIAS(mysql, mysql_db_query, > NULL) > PHP_FALIAS(mysql_fieldname, mysql_field_name, > NULL) > PHP_FALIAS(mysql_fieldtable,mysql_field_table, NULL) > PHP_FALIAS(mysql_fieldlen, mysql_field_len, > NULL) > PHP_FALIAS(mysql_fieldtype, mysql_field_type, > NULL) > PHP_FALIAS(mysql_fieldflags,mysql_field_flags, NULL) > PHP_FALIAS(mysql_selectdb, mysql_select_db, > NULL) > //PHP_FALIAS(mysql_createdb,mysql_create_db, > NULL) > //PHP_FALIAS(mysql_dropdb, mysql_drop_db, > NULL) > > Indeed, this function are not used anymore in mysql 4.0 : > > http://www.mysql.com/doc/U/p/Upgrading-from-3.23.html : > > "The old C API functions mysql_drop_db, mysql_create_db and mysql_connect > are not supported anymore, unless one compiles MySQL with USE_OLD_FUNCTIONS. > Instead of doing this, one should change the client to use the new 4.0 API. > " > > > Best Regards, > > Jocelyn Fournier > Presence-PC > > - Original Message - > From: "Willem Scholten" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, October 17, 2001 9:20 PM > Subject: Mysql 4.0.0-alpha and php-4.0.6 and Apache > > > > After I have build Mysql-4.0.0-alpha from source on a linux 7.1 box as > well > > as PHP-4.0.6 from source, which both build with out errors and MySQL is > > definitely functioning, if I however start Apache the following error > > occurs: > > > > [root@linux}# /etc/rc.d/init.d/httpd start > > Starting httpd: Syntax error on line 260 of /etc/httpd/conf/httpd.conf: > > Cannot load /etc/httpd/modules/libphp4.so into server: > > /etc/httpd/modules/libphp > > 4.so: undefined symbol: mysql_module_entry > > [FAILED] > > > > When I do a /sbin/ldconfig -v clearly the new client libraries are there > for > > MySQL as well as when I do a ldd libphp4.so the output seems normal > (indeed > > ld.so.conf is updated and sees the latest MySQL client) > > > > ldd libphp4.so > > libpam.so.0 => /lib/libpam.so.0 (0x40113000) > > libdl.so.2 => /lib/libdl.so.2 (0x4011b000) > > libyaz.so.1 => /usr/lib/libyaz.so.1 (0x4011f000) > > libmysqlclient.so.11 => /usr/local/lib/mysql/libmysqlclient.so.11 > > (0x4017b000) > > libcrypt.so.1 => /lib/libcrypt.so.1 (0x4019c000) > > libresolv.so.2 => /lib/libresolv.so.2 (0x401ca000) > > libm.so.6 => /lib/i686/libm.so.6 (0x401dd000) > > libnsl.so.1 => /lib/libnsl.so.1 (0x40202000) > > libc.so.6 => /lib/i686/libc.so.6 (0x40219000) > > libz.so.1 => /usr/lib/libz.so.1 (0x40349000) > > /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x2000) > > > > Anyone have ideas? Compile and install of the latest 3.23 version is > without > > a hitch... > > > > By the way this was on purpose on a virgin system! > > > > Thanks for your help > > > > -Willem > > > > - > > Before p
Re: Mysql 4.0.0-alpha and php-4.0.6 and Apache
Hi, I have the solution for this problem. Before compiling php, edit the /ext/mysql/php_mysql.c file in the php distribution. Then remove all the reference to mysql_connect, mysql_create_db and mysql_drop_db : function_entry mysql_functions[] = { //PHP_FE(mysql_connect, NULL) PHP_FE(mysql_pconnect, NULL) PHP_FE(mysql_close, NULL) PHP_FE(mysql_select_db, NULL) //PHP_FE(mysql_create_db, NULL) //PHP_FE(mysql_drop_db, NULL) PHP_FE(mysql_query, NULL) PHP_FE(mysql_unbuffered_query, NULL) PHP_FE(mysql_db_query, NULL) PHP_FE(mysql_list_dbs, NULL) PHP_FE(mysql_list_tables, NULL) PHP_FE(mysql_list_fields, NULL) PHP_FE(mysql_error, NULL) /* for downwards compatability */ PHP_FALIAS(mysql, mysql_db_query, NULL) PHP_FALIAS(mysql_fieldname, mysql_field_name, NULL) PHP_FALIAS(mysql_fieldtable,mysql_field_table, NULL) PHP_FALIAS(mysql_fieldlen, mysql_field_len, NULL) PHP_FALIAS(mysql_fieldtype, mysql_field_type, NULL) PHP_FALIAS(mysql_fieldflags,mysql_field_flags, NULL) PHP_FALIAS(mysql_selectdb, mysql_select_db, NULL) //PHP_FALIAS(mysql_createdb,mysql_create_db, NULL) //PHP_FALIAS(mysql_dropdb, mysql_drop_db, NULL) Indeed, this function are not used anymore in mysql 4.0 : http://www.mysql.com/doc/U/p/Upgrading-from-3.23.html : "The old C API functions mysql_drop_db, mysql_create_db and mysql_connect are not supported anymore, unless one compiles MySQL with USE_OLD_FUNCTIONS. Instead of doing this, one should change the client to use the new 4.0 API. " Best Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Willem Scholten" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, October 17, 2001 9:20 PM Subject: Mysql 4.0.0-alpha and php-4.0.6 and Apache > After I have build Mysql-4.0.0-alpha from source on a linux 7.1 box as well > as PHP-4.0.6 from source, which both build with out errors and MySQL is > definitely functioning, if I however start Apache the following error > occurs: > > [root@linux}# /etc/rc.d/init.d/httpd start > Starting httpd: Syntax error on line 260 of /etc/httpd/conf/httpd.conf: > Cannot load /etc/httpd/modules/libphp4.so into server: > /etc/httpd/modules/libphp > 4.so: undefined symbol: mysql_module_entry > [FAILED] > > When I do a /sbin/ldconfig -v clearly the new client libraries are there for > MySQL as well as when I do a ldd libphp4.so the output seems normal (indeed > ld.so.conf is updated and sees the latest MySQL client) > > ldd libphp4.so > libpam.so.0 => /lib/libpam.so.0 (0x40113000) > libdl.so.2 => /lib/libdl.so.2 (0x4011b000) > libyaz.so.1 => /usr/lib/libyaz.so.1 (0x4011f000) > libmysqlclient.so.11 => /usr/local/lib/mysql/libmysqlclient.so.11 > (0x4017b000) > libcrypt.so.1 => /lib/libcrypt.so.1 (0x4019c000) > libresolv.so.2 => /lib/libresolv.so.2 (0x401ca000) > libm.so.6 => /lib/i686/libm.so.6 (0x401dd000) > libnsl.so.1 => /lib/libnsl.so.1 (0x40202000) > libc.so.6 => /lib/i686/libc.so.6 (0x40219000) > libz.so.1 => /usr/lib/libz.so.1 (0x40349000) > /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x2000) > > Anyone have ideas? Compile and install of the latest 3.23 version is without > a hitch... > > By the way this was on purpose on a virgin system! > > Thanks for your help > > -Willem > > - > 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: Features in 4.0
Subselects are not yet supported in the 4.0 release. - Original Message - From: "Kyle Hayes" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Wednesday, September 26, 2001 4:40 PM Subject: Re: Features in 4.0 > > I did not see subselects in this list. Will MySQL support subselects in 4.x? > > What exactly does the "LOAD TABLE FROM MASTER" 'automagically' comment mean? > Does that command sync the slave with the master at that point? If I have > three tables, A, B, and C, and I want to load them at runtime from the master > and start replication do I have to lock them before doing "LOAD TABLE FROM > MASTER"? > > We are searching for a solution to enable us to load a large amount of data > without stopping the master for more than a couple of seconds. So far, we > have been able to write our own complex code to do this, but it is far from > the level of automation that we want. > > Could someone from the MySQL team please give us some details on subselects > and the new load table functionality? > > Best, > Kyle > > On Tuesday 25 September 2001 14:29, Fournier Jocelyn [Presence-PC] wrote: > > Take a look at this :) > > > > http://www.mysql.com/doc/N/e/News-4.0.0.html > > > > I'm currently using MySQL 4.0 on two servers, and it works great :) > > Union seems also to work really fine now although it remains some features > > to be added before beeing perfect. > > > > Jocelyn Fournier > > Presence-PC > > -- > Quicknet has just released the following new products: > Internet SwitchBoard v5.5 and MicroTelco Gateway 2.0. We > have also added a new low cost carrier, Blue Star Telecom > to our award winning MicroTelco services. For more > information visit: www.quicknet.net or www.microtelco.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: Features in 4.0
Take a look at this :) http://www.mysql.com/doc/N/e/News-4.0.0.html I'm currently using MySQL 4.0 on two servers, and it works great :) Union seems also to work really fine now although it remains some features to be added before beeing perfect. Jocelyn Fournier Presence-PC - Original Message - From: "Deryck Henson" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Tuesday, September 25, 2001 11:15 PM Subject: Features in 4.0 > What will this thing have? i know Union tables will be present :) . What > else? I'm very anctious to get my hands on it. thanx ^_^ > > ** > - Deryck Henson > - http://www.comp-u-exchange.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
Threads problem on a new server :(
Hi, I encounter a problem with a new freshly installed server : it seems MySQL-4.0 doesn't want to create more than 254 threads. Once I have reached this number, MySQL displays this error : ERROR 1135: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug. A debian distribution is installed on the server with a 2.4.7 kernel. Of course I'm not out of available memory :) I'm running mysql with skip-locking enabled. Any idea of what could be wrong with the OS configuration ? Thank you ! Jocelyn Fournier Presence-PC - 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
Many locked thread with MySQL-4.0 ?
Hi, I'm using MySQL 4.0.0-alpha, and I'm wondering why sometimes I have so many lock in my processlist (I don't see any non-locked INSERT which could cause all the lock). mysql> show processlist; +---+---+---++-+--+--+-- + | Id| User | Host | db | Command | Time | State| Info | +---+---+---++-+--+--+-- + | 39767 | mysql | localhost | Hardwarefr | Sleep | 94 | | NULL | | 40364 | mysql | localhost | Hardwarefr | Sleep | 44 | | NULL | | 40479 | mysql | localhost | Hardwarefr | Sleep | 13 | | NULL | | 40586 | mysql | localhost | Hardwarefr | Sleep | 15 | | NULL | | 40709 | mysql | localhost | Hardwarefr | Query | 14 | Locked | INSERT INTO threadhardwarefr7 (numeropost,icone,contenu,pseudo,date,signature,ip) VALUES (48128,16,' | | 40723 | mysql | localhost | Hardwarefr | Sleep | 123 | | NULL | | 40757 | mysql | localhost | Hardwarefr | Query | 7| Locked | SELECT COUNT(*) FROM threadhardwarefr7 | | 40773 | mysql | localhost | Hardwarefr | Sleep | 70 | | NULL | | 40805 | mysql | localhost | Hardwarefr | Sleep | 27 | | NULL | | 40851 | mysql | localhost | Hardwarefr | Sleep | 13 | | NULL | | 40874 | mysql | localhost | Hardwarefr | Sleep | 122 | | NULL | | 40878 | mysql | localhost | Hardwarefr | Sleep | 36 | | NULL | | 40881 | mysql | localhost | Hardwarefr | Sleep | 9| | NULL | | 40893 | mysql | localhost | Hardwarefr | Sleep | 120 | | NULL | | 40966 | mysql | localhost | Hardwarefr | Sleep | 12 | | NULL | | 40970 | mysql | localhost | Hardwarefr | Sleep | 2| | NULL | | 40979 | mysql | localhost | Hardwarefr | Sleep | 9| | NULL | | 40995 | mysql | localhost | Hardwarefr | Sleep | 14 | | NULL | | 40997 | mysql | localhost | Hardwarefr | Sleep | 109 | | NULL | | 41009 | mysql | localhost | Hardwarefr | Sleep | 195 | | NULL | | 41014 | mysql | localhost | Hardwarefr | Query | 11 | Locked | SELECT 1 FROM threadhardwarefr7 WHERE numeropost=48069 AND pseudo='OTH' LIMIT 1 | | 41034 | mysql | localhost | Hardwarefr | Sleep | 17 | | NULL | | 41052 | mysql | localhost | Hardwarefr | Sleep | 51 | | NULL | | 41063 | mysql | localhost | Hardwarefr | Sleep | 8| | NULL | | 41064 | mysql | localhost | Hardwarefr | Query | 9| Locked | SELECT 1 FROM threadhardwarefr7 WHERE numeropost=48124 AND pseudo='Tchou' LIMIT 1| | 41069 | mysql | localhost | Hardwarefr | Sleep | 6| | NULL | | 41109 | mysql | localhost | Hardwarefr | Sleep | 69 | | NULL | | 41123 | mysql | localhost | Hardwarefr | Sleep | 33 | | NULL | | 41128 | mysql | localhost | Hardwarefr | Sleep | 49 | | NULL | | 41131 | mysql | localhost | Hardwarefr | Sleep | 31 | | NULL | | 41135 | mysql | localhost | Hardwarefr | Query | 12 | Locked | SELECT icone,contenu,pseudo,date,numreponse,signature,INET_NTOA(ip) FROM threadhardwarefr7 WHERE num | | 41136 | mysql | localhost | Hardwarefr | Sleep | 55 | | NULL | | 41204 | mysql | localhost | Hardwarefr | Sleep | 11 | | NULL | | 41205 | mysql | localhost | Hardwarefr | Query | 14 | Locked | SELECT 1 FROM threadhardwarefr7 WHERE numeropost=48128 AND pseudo='LYLO' LIMIT 1 | | 41207 | mysql | localhost | Hardwarefr | Sleep | 66 | | NULL | | 41214 | mysql | localhost | Hardwarefr | Query | 14 | Locked | SELECT icone,contenu,pseudo,date,numreponse,signature,INET_NTOA(ip) FROM threadhardwarefr7 WHERE num | | 41215 | mysql | localhost | Hardwarefr | Query | 13 | Locked | SELECT icone,contenu,pseudo,date,numreponse,signature,INET_NTOA(ip) FROM threadhardwarefr7 WHERE num | | 41216 | mysql | localhost | Hardwarefr | Sleep | 111 | | NULL | | 41221 | mysql | localhost | Hardwarefr | Sleep | 15 | | NULL | | 41257 | mysql | localhost | Hardwarefr | Sleep | 10 | | NULL | | 41326 | mysql | localhost | Hardwarefr | Sleep | 75 | | NULL | | 41329 | mysql | localhost | Hardwarefr | Sleep | 87 | | NULL | | 41331 | mysql | localhost | Hardwarefr | Sleep | 74 | | NULL | | 41352 | mysql | localhost | Hardwarefr | Sleep | 71 | | NULL | | 41366 | mysql | localhost | Hardwarefr | Q
Re: limit and order by issuse
This is fixed in PhpMyAdmin 2.2.0 - Original Message - From: "Henning Schroeder" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 06, 2001 12:23 AM Subject: RE: limit and order by issuse > > >sh> Here is my query: > > > >sh> SELECT > >sh> articleId,arttitle,artsourceId,artstatus,artauthorId,arteventdate,artpre > >sh> ss,artpageno,artrankId,artabstract, > >sh> artfulltext,artisdisplay,arteditionId,arttypeid,artsubjectid , > >sh> asubjectId,asubjectname , FLOOR((TO_DAYS(CURRENT_DATE()) - > >sh> TO_DAYS(arteventdate))/365.25) as dif , atypeId, atypename, authorId, > >sh> autname , sourceId, srcname ,usrfname ,usrlname from article ,asubject > >sh> ,atype ,author, source left join user on user.userId = article.user > >sh> where artstatus!='N' and artsourceId = sourceId and authorId = > >sh> artauthorId and arttypeid = atypeId and asubjectId=artsubjectid and > >sh> artauthorid=145 order by articleId desc limit 1 , 20 > > > > > >sh> - When my application in php is getting data from mysql it gets some > >sh> rows. And show no error. [ mysql_error() show no error] > > > >sh> - When I run same query on phpmyadmin query prompt, it gives an error > >sh> with no description. > > > >Technically there should be no big difference running the query in > >php or phpmyadmin, so I don't understand why this would return > >different answers. > > Yes there is, and it has caused me some grief a while ago. phpmyadmin (at > least my installation) seems to add "limit 0,30" to every select query you > enter into the input field. i think this is to make sure that the result > page is not extremely long. anyway, one *cannot* enter limit clauses in a > phpmyadmin query. yes, it sucks. > > henning schroeder. > > > > - > 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: Problem while compiling mysql-4.0
Sorry, I've just seen the problem has been already fixed by tonu in the mysql-4.0 tree. - Original Message - From: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, September 02, 2001 1:00 AM Subject: Problem while compiling mysql-4.0 > Hummm I forgot one important thing : > > mysqld.cc: In function `int main(int, char **)': > mysqld.cc:1994: `ssl_acceptor_fd' undeclared (first use this function) > mysqld.cc:1994: (Each undeclared identifier is reported only once > mysqld.cc:1994: for each function it appears in.) > > > - Original Message - > From: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Saturday, September 01, 2001 11:46 PM > Subject: problem while compiling mysql-4.0 > > > > Hi, > > > > I've just encoutered an error while compiling MySQL-4.0 : > > > > >-DDEFAULT_MYSQL_HOME="\"/var/lib/mys > > ql\"" -DDATADIR="\"/var/lib/mysql/var\"" > /var/lib/mysql/share/mysql\"" -DHAVE > _C > > > ONFIG_H -I./../include -I./../regex-I. -I../include -I.. > > -I. -O3 -DDBUG_OFF -Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -W > tr > > > igraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wim > > > plicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual > > -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual- > dt > > > or -felide-constructors -fno-exceptions -fno-rtti -O3 -fno-omit-frame-point > > er -fno-implicit-templates -fno-exceptions -fno-rtti -c sql_yacc.cc > > make[3]: *** [mysqld.o] Error 1 > > make[3]: *** Waiting for unfinished jobs > > make[3]: *** Waiting for unfinished jobs > > make[3]: Leaving directory `/usr/local/mysql-4.0/sql' > > make[2]: *** [all-recursive] Error 1 > > make[2]: Leaving directory `/usr/local/mysql-4.0/sql' > > make[1]: *** [all-recursive] Error 1 > > make[1]: Leaving directory `/usr/local/mysql-4.0' > > make: *** [all-recursive-am] Error 2 > > ./BUILD/compile-pentium 372.92s user 97.04s system 146% cpu 5:20.54 total > > > > I'm using bk pull / bk -r edit / ./BUILD/compile-pentium standard > procedure. > > I've got the error on my two servers. > > > > What's wrong ? > > > > Regards, > > > > Jocelyn Fournier > > Presence-PC > > > > > > - > > 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]> > > - 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
Problem while compiling mysql-4.0
Hummm I forgot one important thing : mysqld.cc: In function `int main(int, char **)': mysqld.cc:1994: `ssl_acceptor_fd' undeclared (first use this function) mysqld.cc:1994: (Each undeclared identifier is reported only once mysqld.cc:1994: for each function it appears in.) - Original Message ----- From: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, September 01, 2001 11:46 PM Subject: problem while compiling mysql-4.0 > Hi, > > I've just encoutered an error while compiling MySQL-4.0 : > > -DDEFAULT_MYSQL_HOME="\"/var/lib/mys > ql\"" -DDATADIR="\"/var/lib/mysql/var\"" > -DSHAREDIR="\"/var/lib/mysql/share/mysql\"" -DHAVE _C > ONFIG_H -I./../include -I./../regex-I. -I../include -I.. > -I. -O3 -DDBUG_OFF -Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -W tr > igraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wim > plicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual > -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual- dt > or -felide-constructors -fno-exceptions -fno-rtti -O3 -fno-omit-frame-point > er -fno-implicit-templates -fno-exceptions -fno-rtti -c sql_yacc.cc > make[3]: *** [mysqld.o] Error 1 > make[3]: *** Waiting for unfinished jobs > make[3]: *** Waiting for unfinished jobs > make[3]: Leaving directory `/usr/local/mysql-4.0/sql' > make[2]: *** [all-recursive] Error 1 > make[2]: Leaving directory `/usr/local/mysql-4.0/sql' > make[1]: *** [all-recursive] Error 1 > make[1]: Leaving directory `/usr/local/mysql-4.0' > make: *** [all-recursive-am] Error 2 > ./BUILD/compile-pentium 372.92s user 97.04s system 146% cpu 5:20.54 total > > I'm using bk pull / bk -r edit / ./BUILD/compile-pentium standard procedure. > I've got the error on my two servers. > > What's wrong ? > > Regards, > > Jocelyn Fournier > Presence-PC > > > - > 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
problem while compiling mysql-4.0
Hi, I've just encoutered an error while compiling MySQL-4.0 : gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME="\"/var/lib/mys ql\"" -DDATADIR="\"/var/lib/mysql/var\"" -DSHAREDIR="\"/var/lib/mysql/share/mysql\"" -DHAVE_C ONFIG_H -I./../include -I./../regex-I. -I../include -I.. -I. -O3 -DDBUG_OFF -Wimplicit -Wreturn-type -Wid-clash-51 -Wswitch -Wtr igraphs -Wcomment -W -Wchar-subscripts -Wformat -Wimplicit-function-dec -Wim plicit-int -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dt or -felide-constructors -fno-exceptions -fno-rtti -O3 -fno-omit-frame-point er -fno-implicit-templates -fno-exceptions -fno-rtti -c sql_yacc.cc make[3]: *** [mysqld.o] Error 1 make[3]: *** Waiting for unfinished jobs make[3]: *** Waiting for unfinished jobs make[3]: Leaving directory `/usr/local/mysql-4.0/sql' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/usr/local/mysql-4.0/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/local/mysql-4.0' make: *** [all-recursive-am] Error 2 ./BUILD/compile-pentium 372.92s user 97.04s system 146% cpu 5:20.54 total I'm using bk pull / bk -r edit / ./BUILD/compile-pentium standard procedure. I've got the error on my two servers. What's wrong ? Regards, Jocelyn Fournier Presence-PC - 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: CHAR transformed to VARCHAR in CREATE TABLE
Hi, This behaviour is normal. As long as you have varchar, blob or text field, the row format used is "dynamic", and so char are converted to varchar type. If you want to convert you all your varchar to char, just execute ALTER TABLE your_table ROW_FORMAT=fixed Take a look at : http://www.mysql.com/doc/D/y/Dynamic_format.html http://www.mysql.com/doc/S/t/Static_format.html Jocelyn Fournier Presence-PC - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, August 19, 2001 12:23 AM Subject: CHAR transformed to VARCHAR in CREATE TABLE > >Description: > In a CREATE TABLE statement, if coexists CHAR data > and VARCHAR data, the CHAR data is "promoted" to VARCHAR. > >How-To-Repeat: > CREATE DATABASE bug_3_23_41; > > USE bug_3_23_41; > > CREATE TABLE charAccepted ( > Id int (10) unsigned NOT NULL auto_increment, > SID char(32) NOT NULL, > PRIMARY KEY (Id), > UNIQUE SID (SID) > ); > > CREATE TABLE charTransformedToVarchar ( > sessionId int (10) unsigned NOT NULL auto_increment, > SID char(32) NOT NULL, > remoteAddr varchar (23), > PRIMARY KEY (sessionId), > UNIQUE SID (SID) > ); > >Fix: > > > >Submitter-Id: > >Originator: Roberto Bertolusso > >Organization: > > >MySQL support: none > >Synopsis: CHAR transformed to VARCHAR in CREATE TABLE > >Severity: serious > >Priority: > >Category: mysql > >Class: > >Release: mysql-3.23.41 (Source distribution) > > >Environment: > > System: Linux pluton.rbsoft 2.4.7 #2 Tue Jul 31 16:25:07 ART 2001 i686 unknown > Architecture: i686 > > Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc > GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs > gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) > Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' > LIBC: > lrwxrwxrwx1 root root 13 May 11 15:27 /lib/libc.so.6 -> libc-2.2.2.so > -rwxr-xr-x1 root root 1236396 Apr 6 18:58 /lib/libc-2.2.2.so > -rw-r--r--1 root root 26350254 Apr 6 16:27 /usr/lib/libc.a > -rw-r--r--1 root root 178 Apr 6 16:27 /usr/lib/libc.so > Configure command: ./configure --prefix=/opt/web/mysql --localstatedir=/sql/mysql --with-mysql d-user=mysql > > > - > 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: MySQL-MAX 3.23.41 download link is dead... (need a download)
Hi, Try : http://ftp.sunet.se/pub/unix/databases/relational/mysql/Downloads/MySQL-Max- 3.23/ http://ftp.sunet.se/pub/unix/databases/relational/mysql/Downloads/MySQL-Max- 3.23/mysql-max-3.23.41-pc-linux-gnu-i686.tar.gz Jocelyn Fournier Presence-PC - Original Message - From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, August 17, 2001 3:00 AM Subject: MySQL-MAX 3.23.41 download link is dead... (need a download) > The link here: > > http://www.mysql.com/downloads/mysql-max-3.23.html > > points here: > > http://www.mysql.com/Downloads/MySQL-Max-3.23/mysql-max-3.23.41-pc-linux-gnu -i686.tar.gz > > But that URL 404s. > > Does anyone have the latest 3.23.41 I could grab a copy of? > > Thanks, > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > > MySQL 3.23.29: up 61 days, processed 622,471,510 queries (116/sec. avg) > > - > 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
Table corruption at MySQL 4.0.0 shutdown ?
Hi, I noticed an annoying behaviour with MySQL 4.0.0. I have 11 tables with a lot of rows (about 1 Million and more) which are constantly updated by a php script. It seems after a shutdown of mysql (mysqladmin -uroot -p shutdown) and a restart, some tables are corrupted (and not the same, it randomly occurs), and I have to run a myisamchk to repair them. Here is the structure of the table : CREATE TABLE searchhardwarefr9 ( mot varchar(30) NOT NULL, topic mediumint(8) unsigned DEFAULT '0' NOT NULL, date date DEFAULT '-00-00' NOT NULL, pseudo varchar(35) NOT NULL, numreponse int(10) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (mot, pseudo, date, numreponse, topic), KEY pseudo (pseudo, date, numreponse, topic), KEY numreponse (numreponse, topic), KEY topic (topic) ); -=[root@s15]=(/usr/local/var/Hardwarefr)# myisamchk -o searchhardwarefr9.MYI <(17:31:25) - recovering (with keycache) MyISAM-table 'searchhardwarefr9.MYI' Data records: 977549 Duplicate key 1 for record at 30977704 against new record at 30946580 Duplicate key 1 for record at 30977736 against new record at 30946612 Duplicate key 1 for record at 30977768 against new record at 30946644 Duplicate key 1 for record at 30977796 against new record at 30946672 Duplicate key 1 for record at 30977828 against new record at 30946704 Duplicate key 1 for record at 30977860 against new record at 30946736 Duplicate key 1 for record at 30977896 against new record at 30946772 Duplicate key 1 for record at 30977928 against new record at 30946804 Duplicate key 1 for record at 30977960 against new record at 30946836 Duplicate key 1 for record at 30977992 against new record at 30946868 Duplicate key 1 for record at 30978024 against new record at 30946900 Data records: 977538 myisamchk: warning: 11 records have been removed Any idea ? (hardware related or mysql related ?) Thank you :) Jocelyn Fournier Presence-PC
Annoying Too Many Connection error...
Hi, I've just seen when the maximum connection number is reached and you try to shutdown MySQL with mysqladmin shutdown, it couldn't be executed because of "too many connections" error. It would be smarter if mysqladmin could shutdown MySQL whatever the number of connections, even if the maximum connection ratio is reached. What do you think about it ? Jocelyn Fournier Presence-PC www.presence-pc.com
Fw: Bug with not_flushed_delayed_rows in mysql-4.0 ?
- Original Message - From: Fournier Jocelyn [Presence-PC] To: [EMAIL PROTECTED] Sent: Tuesday, July 31, 2001 10:42 AM Subject: Bug with not_flushed_delayed_rows in mysql-4.0 ? Hi, I have more than 200 Delayed_insert running at the same time on my database (waiting on cond state right now), and I just noticed a strange thing with the not_flushed_delayed_rows variable : | Not_flushed_delayed_rows | 4294967295 | It would be quite annoying if it was true, but no, I've not inserted so many rows :D Is there anything wrong with it ? (I unfortunately don't succeed in reproducing this bug on my second server) Thank you. Jocelyn Fournier Presence-PC www.presence-pc.com
Bug with not_flushed_delayed_rows in mysql-4.0 ?
Hi, I have more than 200 Delayed_insert running at the same time on my database (waiting on cond state right now), and I just noticed a strange thing with the not_flushed_delayed_rows variable : | Not_flushed_delayed_rows | 4294967295 | It would be quite annoying if it was true, but no, I've not inserted so many rows :D Is there anything wrong with it ? (I unfortunately don't succeed in reproducing this bug on my second server) Thank you. Jocelyn Fournier Presence-PC www.presence-pc.com
Re: Bug report: FULLTEXT index corrupts the index with too many TEXT fields
Take a look at : http://www.mysql.com/doc/I/n/Installing_source_tree.html - Original Message - From: "Simon Green" <[EMAIL PROTECTED]> To: "'Fournier Jocelyn [Presence-PC]'" <[EMAIL PROTECTED]>; "Carsten Gehling" <[EMAIL PROTECTED]>; "Sergei Golubchik" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 2:55 PM Subject: RE: Bug report: FULLTEXT index corrupts the index with too many TEXT fields > Hi > Where can we get V4.0? > > Thanks Simon > > -Original Message- > From: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] > Sent: 20 July 2001 12:30 > To: Carsten Gehling; Sergei Golubchik > Cc: [EMAIL PROTECTED] > Subject: Re: Bug report: FULLTEXT index corrupts the index with too many > TEXT fields > > > Hi, > > I've just tested with MySQL 4.0, no error, but strange result : > > mysql> insert into visitkort (kategori_id) values (108); > Query OK, 1 row affected (0.00 sec) > > mysql> update visitkort set navn = 'test5' where id = last_insert_id(); > Query OK, 1 row affected (0.00 sec) > Rows matched: 1 Changed: 1 Warnings: 0 > > mysql> update visitkort set tekst1 = 'bla bla' where id = last_insert_id(); > Query OK, 1 row affected (0.00 sec) > Rows matched: 1 Changed: 1 Warnings: 0 > > The latest update seems to have been successful, but if you look at the > table you can see the tekst1 field has not been updated : > > ++-+---+---+-++-+-+---+- > -+-+-+---+--+--+-+ -- > --++++ > | id | kategori_id | aktiv | navn | adresse | postnr | tlf | fax | email | > password | url | beskrivelse | visitkort | skabelon | logo | billede | > tekst1 | tekst2 | tekst3 | tekst4 | > ++-+---+---+-++-+-+---+- > -+-+-+---+--+--+-+ -- > --++++ > | 1 | 108 | 0 | test5 | || | | | > | | | 0 |0 |0 | 0 || > ||| > ++-+---+---+-++-+-+---+- > -+-+-+---+--+--+-+ -- > --++++ > > Regards, > > Jocelyn Fournier > Presence-PC > > - Original Message - > From: "Carsten Gehling" <[EMAIL PROTECTED]> > To: "Sergei Golubchik" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Friday, July 20, 2001 12:06 PM > Subject: Re: Bug report: FULLTEXT index corrupts the index with too many > TEXT fields > > > > I'm going to Spain today and cannot respond to any questions in the next > > week. I was going to wait with this until I got home again, but what the > > heck ;-) > > > > Run the following script through your MySQL on an empty database with > > > > mysql -uusername -ppassword dbname > > > and the last command should produce the following error: > > > > ERROR 1034 at line 31: Incorrect key file for table: 'visitkort'. Try to > > repair it > > > > I've let 3 people besides myself test it, and they all get the same error. > > It has now been tested on: > > > > Win2k server SP2, MySQL 3.23.32 > > > > Win2k server SP2, MySQL 3.23.39 > > > > Win2k Pro (Danish), MySQL 3.23.33 (normal version) > > > > Win2k Pro SP2 (English), MySQL 3.23.38-MAX > > > > Debian GNU/Linux, MySQL 3.23.39 (bniary .deb package) > > > > Some other Linux (didn't get the distro name), MySQL 3.23.39 > > > > All of the above produces the error. So something must be wrong. > > > > Sincerily, > > - Carsten > > > > Here's the script: > > > > ## > > CREATE TABLE `visitkort` ( > > `id` int(10) unsigned NOT NULL auto_increment, > > `kategori_id` int(10) unsigned NOT NULL default '0', > > `aktiv` tinyint(3) unsigned NOT NULL default '0', > > `navn` varchar(60) NOT NULL default '', > > `adresse` varchar(150) NOT NULL default '', > > `postnr` varchar(5) NOT NULL default '', > > `tlf` varchar(20) NOT NULL default '', > > `fax` varchar(20) NOT NULL default '', > > `email` varchar(60) NOT NULL default '', > > `password` varchar(20) NOT NULL default '', > &g
Re: Bug report: FULLTEXT index corrupts the index with too many TEXT fields
Hi, I've just tested with MySQL 4.0, no error, but strange result : mysql> insert into visitkort (kategori_id) values (108); Query OK, 1 row affected (0.00 sec) mysql> update visitkort set navn = 'test5' where id = last_insert_id(); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update visitkort set tekst1 = 'bla bla' where id = last_insert_id(); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 The latest update seems to have been successful, but if you look at the table you can see the tekst1 field has not been updated : ++-+---+---+-++-+-+---+- -+-+-+---+--+--+-+-- --++++ | id | kategori_id | aktiv | navn | adresse | postnr | tlf | fax | email | password | url | beskrivelse | visitkort | skabelon | logo | billede | tekst1 | tekst2 | tekst3 | tekst4 | ++-+---+---+-++-+-+---+- -+-+-+---+--+--+-+-- --++++ | 1 | 108 | 0 | test5 | || | | | | | | 0 |0 |0 | 0 || ||| ++-+---+---+-++-+-+---+- -+-+-+---+--+--+-+-- --++++ Regards, Jocelyn Fournier Presence-PC - Original Message - From: "Carsten Gehling" <[EMAIL PROTECTED]> To: "Sergei Golubchik" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 12:06 PM Subject: Re: Bug report: FULLTEXT index corrupts the index with too many TEXT fields > I'm going to Spain today and cannot respond to any questions in the next > week. I was going to wait with this until I got home again, but what the > heck ;-) > > Run the following script through your MySQL on an empty database with > > mysql -uusername -ppassword dbname > and the last command should produce the following error: > > ERROR 1034 at line 31: Incorrect key file for table: 'visitkort'. Try to > repair it > > I've let 3 people besides myself test it, and they all get the same error. > It has now been tested on: > > Win2k server SP2, MySQL 3.23.32 > > Win2k server SP2, MySQL 3.23.39 > > Win2k Pro (Danish), MySQL 3.23.33 (normal version) > > Win2k Pro SP2 (English), MySQL 3.23.38-MAX > > Debian GNU/Linux, MySQL 3.23.39 (bniary .deb package) > > Some other Linux (didn't get the distro name), MySQL 3.23.39 > > All of the above produces the error. So something must be wrong. > > Sincerily, > - Carsten > > Here's the script: > > ## > CREATE TABLE `visitkort` ( > `id` int(10) unsigned NOT NULL auto_increment, > `kategori_id` int(10) unsigned NOT NULL default '0', > `aktiv` tinyint(3) unsigned NOT NULL default '0', > `navn` varchar(60) NOT NULL default '', > `adresse` varchar(150) NOT NULL default '', > `postnr` varchar(5) NOT NULL default '', > `tlf` varchar(20) NOT NULL default '', > `fax` varchar(20) NOT NULL default '', > `email` varchar(60) NOT NULL default '', > `password` varchar(20) NOT NULL default '', > `url` varchar(150) NOT NULL default '', > `beskrivelse` varchar(200) NOT NULL default '', > `visitkort` tinyint(3) unsigned NOT NULL default '0', > `skabelon` tinyint(3) unsigned NOT NULL default '0', > `logo` tinyint(3) unsigned NOT NULL default '0', > `billede` tinyint(3) unsigned NOT NULL default '0', > `tekst1` text NOT NULL, > `tekst2` text NOT NULL, > `tekst3` text NOT NULL, > `tekst4` text NOT NULL, > PRIMARY KEY (`id`), > FULLTEXT KEY `ft` > (`navn`,`beskrivelse`,`tekst1`,`tekst2`,`tekst3`,`tekst4`) > ) TYPE=MyISAM; > > insert into visitkort (kategori_id) values (108); > > update visitkort set navn = 'test5' where id = last_insert_id(); > > update visitkort set tekst1 = 'bla bla' where id = last_insert_id(); > ## > > > > > > - > 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 > 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 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange behaviour (bug ?) when closing MySQL.
Hi, I just experienced a strange bug in MySQL 3.23.39 I couldn't explain (and couldn't reproduce, it's why I post it on this list ;)) I misconfigured my my.cnf, so MySQL didn't found the host.frm. The strange behaviour is just after the normal shutdown of mysql : 010717 00:01:19 mysqld started 010717 0:01:19 /var/lib/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010717 0:01:19 /var/lib/mysql/libexec/mysqld: Normal shutdown mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist 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=268431360 record_buffer=8384512 sort_buffer=8388600 max_used_connections=0 max_connections=1000 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 4059220 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 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=0xbf5ff638, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x806add4 0x810fdd8 0x806a2b7 0x806de2b 0x810d70e 0x8143b5a 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 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 Fatal signal 11 while backtracing 010717 00:01:19 mysqld ended I retried several times to reproduce the bug, no way :( The server is perfectly stable and was running for days MySQL without a glitch, it's why I don't think the hardware could be involved in this issue. Do you have any idea of how it could have been happened ? Thank you, Jocelyn Fournier Presence-PC
Error while trying to compile MySQL 4.0
Hi, Compilation fail with the following error message : makeinfo -I . --no-headers --no-split --output manual.txt manual.texi Fabrication du fichier text «manual.txt» à partir de «manual.texi». manual.texi:56: Commande inconnue «setcontentsaftertitlepage».. manual.texi:5157: itemize exige un argument: le formateur pour @item. manual.texi:20066: «.» ou «,» doit suivre une référence croisée, non pas a. manual.texi:30568: itemize exige un argument: le formateur pour @item. manual.texi:30980: itemize exige un argument: le formateur pour @item. manual.texi:44531: itemize exige un argument: le formateur pour @item. makeinfo: Enlèvement du fichier de sortie «manual.txt» à cause d'erreurs; utiliser l'option --force pour préserver. make[2]: *** [manual.txt] Erreur 2 make[2]: Quitte le répertoire `/usr/local/mysql-4.0/Docs' make[1]: *** [all-recursive] Erreur 1 make[1]: Quitte le répertoire `/usr/local/mysql-4.0' make: *** [all-recursive-am] Erreur 2 What should I do to fix the error ? Thank you, Jocelyn Fournier Presence-PC
HELP ! Myisamchk doesn't seem to see error in my table :(
Hi, I crash one table, and some datas are completely corrupted. However, myisamchk doesn't seem to see any errors (I try -e, -o and -r option)... Here is an extract of the table : mysql> SELECT * FROM forumconthardwarefr6 order by date desc limit 0,20; +--+-++-+-+---+---+-+-+-+-+ | titre| |numeropost | ouvert | date| auteur | | icone | nbrep | maxnumrep | vue | lastauteur | | sondage | +--+-++-+-+---+---+-+-+-+-+ | Vds Barette EDO pour moins j| |8387233509356368993 | 0 | /-/-0--)0 26:25:28 | ssonneur_de_vie | | 117 | 0 | 723859610131712 | 27016 | | | 10 | | artitionz| |7300902383298871296 |100 | J521-99-51 55:87:56 | hork | | 108 | 0 | 8289623147154535528 | 7696240 | fman | | 118 | | city | |8320800813073630040 |117 | p706-83-13 97:69:41 | inary Finary | | 108 | 105 | 8241943594372651008 | 5138548 | et | | 0 | | et | |661841128741497964 | 7 | Å.()-*(-+. 79:64:86 | | | 101 | 0 | 270336 | 24 | AZIZDELAZUP | | 0 | The date column is DATETIME, and as you can see, the datas are completely corrupted, but myisamchk doesn't see anything wrong :( Do you have any idea how I can recover the table ? Thanks a lot ! Jocelyn Fournier Presence-PC
Urgent problem : recovering a table with no MYI file ?
Hi, I'm administrating a machine which has just badly crashed, and a MYI file has disappeared for one table. Is this possible to repair this table ? BTW, REPAIR TABLE in mysql report a wrong message : mysql> REPAIR TABLE threadhardwarefr1; +--++--+-+ | Table| Op | Msg_type | Msg_text | | +--++--+-+ | Hardwarefr.threadhardwarefr1 | repair | error| Can't find file: |'threadhardwarefr1.MYD' (errno: 2) | +--++--+-+ 1 row in set (0.00 sec) MYD file is here, but MYI is missing... Thank you ! Jocelyn Fournier Presence-PC
Fw: Annoying "Aborting Connection" in .err file
- Original Message - From: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> To: "Tonu Samuel" <[EMAIL PROTECTED]> Sent: Friday, July 06, 2001 6:16 PM Subject: Re: Annoying "Aborting Connection" in .err file > In fact pconnect aren't close at the end of the PHP Script. The MySQL > process stay available for a potential futur connexion, and this prevent the > overhead generated by closing and opening the process. If nobody use the > process during the time defined in the wait_timeout variable in my.cnf, then > the process dead, and the "connection aborted" is written in the .err > file... But I don't care about this message, and I don't want to use connect > instead of pconnect :) > > ----- Original Message - > From: "Tonu Samuel" <[EMAIL PROTECTED]> > To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> > Sent: Friday, July 06, 2001 6:12 PM > Subject: Re: Annoying "Aborting Connection" in .err file > > > > Fournier Jocelyn [Presence-PC] wrote: > > > > > I'm using pconnect, so the connections are not close at the end of my > > > scripts... > > > > Then this is an PHP issue I think. Connections should be normally closed. > > Other possibility is that PHP-s timeout is longer than MySQL one and then > of course MySQL will close connection. > > > > Tõnu > > > > > - 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
Annoying "Aborting Connection" in .err file
Hi, Is there any mean to not log the "Aborting Connection" line in the .err file ? Thank you Jocelyn Fournier Presence-PC www.presence-pc.com
Question about DELETE and Index.
Hi, Does DELETE take advantage of index to locate more quickly row to be deleted ? Thank you :) Jocelyn Fournier Presence-PC
OPTIMIZE TABLE / ANALYZE TABLE behaviour ?
Hi, I notice a strange behaviour with OPTIMIZE TABLE and ANALYZE TABLE (on MyISAM table) : Before analysing my table, mysql show me : mysql> show index from forumcontpresencepc6; +--++---+--+-+---+-+--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | |Collation | Cardinality | Sub_part | Packed | Comment | +--++---+--+-+---+-+--++-+ | forumcontpresencepc6 | 0 | PRIMARY |1 | numeropost | A | |NULL | NULL | NULL | | | forumcontpresencepc6 | 1 | date |1 | date| A | |NULL | NULL | NULL | | | forumcontpresencepc6 | 1 | maxnumrep |1 | maxnumrep | A | |NULL | NULL | NULL | | +--++---+--+-+---+-+--++-+ 3 rows in set (0.00 sec) After analysing the table, this became : mysql> show index from forumcontpresencepc6; +--++---+--+-+---+-+--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | |Collation | Cardinality | Sub_part | Packed | Comment | +--++---+--+-+---+-+--++-+ | forumcontpresencepc6 | 0 | PRIMARY |1 | numeropost | A | | 217 | NULL | NULL | | | forumcontpresencepc6 | 1 | date |1 | date| A | | 217 | NULL | NULL | | | forumcontpresencepc6 | 1 | maxnumrep |1 | maxnumrep | A | | 217 | NULL | NULL | | +--++---+--+-+---+-+--++-+ 3 rows in set (0.00 sec) The odd thing is when I execute an optimize table query. My table then become in the same state before analysing the table : +--++---+--+-+---+-+--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | |Collation | Cardinality | Sub_part | Packed | Comment | +--++---+--+-+---+-+--++-+ | forumcontpresencepc6 | 0 | PRIMARY |1 | numeropost | A | |NULL | NULL | NULL | | | forumcontpresencepc6 | 1 | date |1 | date| A | |NULL | NULL | NULL | | | forumcontpresencepc6 | 1 | maxnumrep |1 | maxnumrep | A | |NULL | NULL | NULL | | +--++---+--+-+---+-+--++-+ 3 rows in set (0.00 sec) Is this behaviour OK ?? (I was assuming OPTIMIZE TABLE request wasn't resetting the cardinality information) Thanks, Jocelyn Fournier Presence-PC www.presence-pc.com