Re: InnoDB 4.1.3: count(*) and number of rows does not match
Thanks Mike, I've always ignored CHECK TABLE because I always thought it was just for MyISAM. I ran CHECK TABLE. It told me that my table was corrupt. I then dumped the table with mysqldump and recreated it. After that CHECK TABLE said it was OK (and in comparing values with the master server it appears to have all data intact as well). Unlike CHECK TABLE, REPAIR TABLE only works on MyISAM tables, so I can't use that. Then, I decided to run CHECK TABLE on all my tables (which for the ones with 125 million rows will probably be running for a while). The problem now is that all of my larger tables are reported as being corrupt--every single table with more than say 500,000 records is reported as corrupt. Wow! Could this be true? The tables all access fine and only these two smaller tables had these count(*) mismatch problems (and were the only two smaller tables that came up corrupt). How did this happen? I've never gotten an error in my .err file, never had a hardware access failure in the system logs, and have done very little with this server beyond initially loading it (by replaying mysqldump output in the first place) and letting it stay up to date with replication. It's going to take me a week to reload these tables if they are all corrupt. Based on what little this system has done so far, I wonder how long it will be before they're corrupt again. Is there a faster way to fix these corruptions than to dump and reload the tables? -keith Hi Marc, Thanks for you response. In answer to your questions, there are no embedded newlines and I did look at index issues. I did not try rebuilding the index, which would be easy to do in this case since the tables are small (unlike a couple of my other tables that have 125+ million rows and changing indexes is measured in days instead of minutes). I forgot to mention in my first message that the select count(*)... is the one that is wrong. Counting all returned elements isn't too high, the count(*) is too low. Doing the count(*) on the same exact table on a 4.0.17 system (the replication master) gives the correct count. -keith Keith, Try doing a Check Table tablename. See http://dev.mysql.com/doc/mysql/en/CHECK_TABLE.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FreeBSD and MySQL - mysqld eats CPU alive
I have several MySQL and FreeBSD installs across a few different sites, and I consistently have problems with mysqld. It will begin to eat up all of the CPU and eventually become unresponsive (or the machine will just burn). I can't seem to manually reproduce this, but given enough time a FreeBSD box with mysqld will go down. For what it's worth, we had the exact OPPOSITE happen: We were using OpenBSD for a long time, and when we switched to FreeBSD, MySQL CPU usage dropped from like 75% to 5%. It's been running amazingly fast ever since, for a database-driven website that gets 10,000 unique visitors a day. (100,000 page views a day, all MySQL-generated.) Just using a stock install from FreeBSD ports -- although we did choose the OPTIMIZED settings in ports install that it shows you when you first go to install MySQL server. Choose linux threads, too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE insists on being LOCAL
Somewhere about Sat, 31-Jul-2004 at 11:17AM -0400 (give or take), Michael Stassen
wrote:
| With LOCAL, the *client* reads the file on the client's machine.
| Without LOCAL, the *server* reeads the file on the server's
| machine. Even though the client and server machines are the same
| in your case, those are still different operations. There are
| restrictions on having the server do the work, for good reason.
| This is documented in the manual
| http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html:
I'm pretty sure I understand the reasons.
| : For security reasons, when reading text files located on the server,
| : the files must either reside in the database directory or be readable
| : by all.
Looks to me the mysql user should have no trouble with it:
-rw-rw-r--1 pat pat 332 Jun 28 20:42 Orders.txt
| : Also, to use LOAD DATA INFILE on server files, you must have
| : the FILE privilege. See section 5.5.3 Privileges Provided by MySQL.
Think we can count that one out as the problem since LOCAL which would
have the same requirement does work.
I can't be absolutely sure but I seem to remember I did not have this
problem when I used 3.23.47 before I 'rpm -U'ed to 4.0.18. With the
Redhat distro version, I could *not* use LOAD DATA LOCAL unless I
started the client with --local-infile[=1] which seems to fit my
understanding of the docs. With 4.0.18, it's unnecessary which was
another surprise to me. Is there something I'm missing here?
|
| Michael
Thanks Michael.
--
___ Patrick Connolly
{~._.~}
_( Y )_ Good judgment comes from experience
(:_~*~_:) Experience comes from bad judgment
(_)-(_)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug-Report: mysqld 4.1.3 crashes on startup
Hi! On Aug 01, Helge Jung wrote: Description: When I start up my fresh compiled mysqld it crashes immediately, the error log file says: It was reported just a few hours ago at bugs.mysql.com (which is the recommended way to report bugs, by the way :) you may follow the progress using http://bugs.mysql.com/4844 Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB 4.1.3: count(*) and number of rows does not match
Hi! On Aug 01, Keith Thompson wrote: Thanks Mike, I've always ignored CHECK TABLE because I always thought it was just for MyISAM. Then, I decided to run CHECK TABLE on all my tables (which for the ones with 125 million rows will probably be running for a while). The problem now is that all of my larger tables are reported as being corrupt--every single table with more than say 500,000 records is reported as corrupt. Wow! Could this be true? The tables all access fine and only these two smaller tables had these count(*) mismatch problems (and were the only two smaller tables that came up corrupt). How did this happen? I've never gotten an error in my .err file, never had a hardware access failure in the system logs, and have done very little with this server beyond initially loading it (by replaying mysqldump output in the first place) and letting it stay up to date with replication. Just a thought - if you upgraded, be sure to read all changelog entries carefully, there were few bugfixes that would require to dump/reload innodb tables (otherwise they'll be corrupted). Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB 4.1.3: count(*) and number of rows does not match
Hi Sergei, I did come from 4.0.17 to 4.1.3 with a complete dump/reload. Then, all changes after that forward came via replication. I didn't read anything about corruption problems as a replication slave. -keith Hi! On Aug 01, Keith Thompson wrote: Thanks Mike, I've always ignored CHECK TABLE because I always thought it was just for MyISAM. Then, I decided to run CHECK TABLE on all my tables (which for the ones with 125 million rows will probably be running for a while). The problem now is that all of my larger tables are reported as being corrupt--every single table with more than say 500,000 records is reported as corrupt. Wow! Could this be true? The tables all access fine and only these two smaller tables had these count(*) mismatch problems (and were the only two smaller tables that came up corrupt). How did this happen? I've never gotten an error in my .err file, never had a hardware access failure in the system logs, and have done very little with this server beyond initially loading it (by replaying mysqldump output in the first place) and letting it stay up to date with replication. Just a thought - if you upgraded, be sure to read all changelog entries carefully, there were few bugfixes that would require to dump/reload innodb tables (otherwise they'll be corrupted). Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
better performance with optimize!?? (jboss)
I use Mysql with JBOSS as applicationserver. i have strange response-time differences, which i can't explain. 1) after reboot the computer, it takes about 300ms to read 12 entities (cmp, read ahead, 2 rows each entity) One entity is accessed by primary key, the others by foreign key. 2) when i reboot the computer and run mysql optimize first, it takes only about 80ms to read the 12 entities!! 3) when i do the same with pure java / jdbc (outside jboss), it takes only 30ms!!! it doesn't matter, if i run optimize or not! does anybody knows, why there is a difference if run optimize first (java/jdbc is always fast with/without optimize) i use mysql 4.0, jconnector mysql-connector-java-3.0.14-production-bin.jar (i hope you understand my english... :o)) Thanks for your help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connectiing
Hi
Sorry if I'm off topic but I am a touch desperate
We make use of a mysql 4.x hosted by a third party and we use ASP to access
this data
Our Connection string looks like this:
xDb_Conn_Str=DRIVER=org.gjt.mm.mysql.Driver;URL={jdbc:mysql://xxx/x
xx};uid=xxx;pwd=xx;
This has worked fine for months but for some reason we I use the ASP pages
we now get Cannot Find Server
I can access the Data Source using MySQL Control Center
I have replaced the url parameter with the ip address but still no joy
Any pointers really appreciated.
Regards
John Berman
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connectiing
- Original Message -
From: John Berman [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, August 01, 2004 7:27 AM
Subject: Connectiing
Hi
Sorry if I'm off topic but I am a touch desperate
We make use of a mysql 4.x hosted by a third party and we use ASP to
access
this data
Our Connection string looks like this:
xDb_Conn_Str=DRIVER=org.gjt.mm.mysql.Driver;URL={jdbc:mysql://xxx/x
xx};uid=xxx;pwd=xx;
This has worked fine for months but for some reason we I use the ASP pages
we now get Cannot Find Server
I can access the Data Source using MySQL Control Center
I have replaced the url parameter with the ip address but still no joy
Any pointers really appreciated.
Regards
John Berman
[EMAIL PROTECTED]
Just starting with it myself, but...
the docs say the name of the class is com.mysql.jdbc.Driver
FWIW,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB 4.1.3: count(*) and number of rows does not match
At 01:21 AM 8/1/2004, Keith Thompson wrote: Thanks Mike, I've always ignored CHECK TABLE because I always thought it was just for MyISAM. I ran CHECK TABLE. It told me that my table was corrupt. I then dumped the table with mysqldump and recreated it. After that CHECK TABLE said it was OK (and in comparing values with the master server it appears to have all data intact as well). Unlike CHECK TABLE, REPAIR TABLE only works on MyISAM tables, so I can't use that. Then, I decided to run CHECK TABLE on all my tables (which for the ones with 125 million rows will probably be running for a while). The problem now is that all of my larger tables are reported as being corrupt--every single table with more than say 500,000 records is reported as corrupt. Wow! Could this be true? Yup. But it could just be that the table counts that are off. The tables all access fine and only these two smaller tables had these count(*) mismatch problems (and were the only two smaller tables that came up corrupt). How did this happen? I've never gotten an error in my .err file, never had a hardware access failure in the system logs, and have done very little with this server beyond initially loading it (by replaying mysqldump output in the first place) and letting it stay up to date with replication. In my case, I can corrupt tables by not shutting down the server properly. For example, if the server crashes then it could cause problems with the files because the tables are not flushed prior to closing the tables so the counts can be off. Or if you Kill a process like REPAIR TABLE or OPTIMIZE TABLE can lead to a corrupt table. Or if you are using --delay-key-writes in your .cnf file can cause problems if you're not careful. There is a Shutdown command you can execute to shut down the server that automatically flushes the tables and closes everything in an orderly fashion. http://dev.mysql.com/doc/mysql/en/Server_Shutdown.html http://dev.mysql.com/doc/mysql/en/Crashing.html It's going to take me a week to reload these tables if they are all corrupt. Based on what little this system has done so far, I wonder how long it will be before they're corrupt again. A week? That must be a lot of large tables. (Of course InnoDb takes longer than MYISAM tables to load.) I think everyone needs to write a check and repair script for their MySQL database. Run the Check script daily to see if and when the error occurs or twice a day if you're really paranoid. Then have a recovery procedure written down so someone can follow it. And of course log the error report in a file so you can try and determine what caused it and how often it occurs. Is there a faster way to fix these corruptions than to dump and reload the tables? I'm not sure because I don't use InnoDb. I'm sure Heikki would know. Mike -keith Hi Marc, Thanks for you response. In answer to your questions, there are no embedded newlines and I did look at index issues. I did not try rebuilding the index, which would be easy to do in this case since the tables are small (unlike a couple of my other tables that have 125+ million rows and changing indexes is measured in days instead of minutes). I forgot to mention in my first message that the select count(*)... is the one that is wrong. Counting all returned elements isn't too high, the count(*) is too low. Doing the count(*) on the same exact table on a 4.0.17 system (the replication master) gives the correct count. -keith Keith, Try doing a Check Table tablename. See http://dev.mysql.com/doc/mysql/en/CHECK_TABLE.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE insists on being LOCAL
Patrick Connolly wrote: Somewhere about Sat, 31-Jul-2004 at 11:17AM -0400 (give or take), Michael Stassen wrote: | With LOCAL, the *client* reads the file on the client's machine. | Without LOCAL, the *server* reeads the file on the server's | machine. Even though the client and server machines are the same | in your case, those are still different operations. There are | restrictions on having the server do the work, for good reason. | This is documented in the manual | http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html: I'm pretty sure I understand the reasons. | : For security reasons, when reading text files located on the server, | : the files must either reside in the database directory or be readable | : by all. Looks to me the mysql user should have no trouble with it: -rw-rw-r--1 pat pat 332 Jun 28 20:42 Orders.txt Every piece of the path to this file must be executable by mysql, as well. | : Also, to use LOAD DATA INFILE on server files, you must have | : the FILE privilege. See section 5.5.3 Privileges Provided by MySQL. Think we can count that one out as the problem since LOCAL which would have the same requirement does work. The FILE privilege is not required with LOCAL. It is required without LOCAL, and for SELECT INTO OUTFILE. My bet would be that you don't have the FILE privilege. You can check with SHOW GRANTS FOR [EMAIL PROTECTED] or by inspecting the File_priv column in the mysql.user table. I can't be absolutely sure but I seem to remember I did not have this problem when I used 3.23.47 before I 'rpm -U'ed to 4.0.18. With the Redhat distro version, I could *not* use LOAD DATA LOCAL unless I started the client with --local-infile[=1] which seems to fit my understanding of the docs. With 4.0.18, it's unnecessary which was another surprise to me. Is there something I'm missing here? How LOAD DATA LOCAL works was changed in 3.23.49 and 4.0.2. This is documented here http://dev.mysql.com/doc/mysql/en/LOAD_DATA_LOCAL.html. | | Michael Thanks Michael. You're welcome. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a question/issue...
hi... i'm testing an app where i want a parent app to create the mysql db connection link/handle, and basically pass this handle off to child procesess. is there any reason why this can't be done? are there any examples of this already being accomplished that you might provide??? as far as i can tell/see, it should be possible. as long as the parent is still running, the connection link/handle should still be valid. i'm going to ultimately be working in perl/php/c but if i can get it working in one language, i can get it in the others as well any thoughts/comments/criticisms/etc thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT difficulties
SELECT offerid, resellerid, price FROM A WHERE pricece = MIN(price) GROUP BY resellerid; - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Daniel Lahey [EMAIL PROTECTED] Cc: Haitao Jiang [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, July 30, 2004 7:33 PM Subject: Re: SELECT difficulties That won't work. For each resellerid, you'll get the minimum price and an effectively random offerid. Michael Daniel Lahey wrote: You need to use the 'GROUP BY' clause: SELECT offerid, resellerid, MIN(price) FROM A GROUP BY resellerid; Cheers, Dan On Jul 30, 2004, at 2:37 PM, Haitao Jiang wrote: Hi, there Maybe this question is not MySQL specific, but I just wondering if MySQL has any way to doing this: I have a table A like following: offerId resellerId price -- 1r1 5 2r1 10 3r2 12 4r2 4 -- How can I select only one row from each reseller with the the lowest price from this reseller? i.e. the result should be 1 r1 5 4 r2 4 Thanks in adavnce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizer Index Weirdness
David Griffiths wrote: Really? I had no idea. I am pretty sure that only Day will have a range. Our index is currently, (yearmonth, day, stem_base), so I need to drop and recreate it. I think part of the slowness is the size of the table as well. We have data going back years in there. I am thinking about breaking the table up by yearmonth (ie all data for 200407 goes into a table by that name). Most people obviously query for recent data, so most of the time just a single table would be in use. This would mean the indexes would be more efficient, not having to sort through 38 million rows that with out a doubt do not have data that the query requires. If someone requests data that spans a few months, a UNION would do the trick. Thanks for the reply. BTW, where did you come across how MySQL uses indexes; this is pretty detailed info, and it would be great if it was documented somewhere. David How mysql uses indexes is documented in the manual. See http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html http://dev.mysql.com/doc/mysql/en/Query_Speed.html I first saw the book/chapter/page analogy, or something like it, in a long ago thread on this list. Size may not matter that much so long as your queries can take full advantage of indexing. Unless you do a lot of deleting, rows with similar yearmonths will be more or less in the same place on disk. If most of your queries are for recent dates, I'd expect mysql to cache that part of the index and the OS to cache the disk where those rows live. With proper indexing and sufficient memory, then, you may find you get acceptable speed without breaking up the table. There may be some other optimizations you could do, however. The manual recommends http://dev.mysql.com/doc/mysql/en/Data_size.html making your data as small as possible. According to your first message, you have: day INT yearmonth INT stem_base VARCHAR(100) If I understand correctly, day is in the range 1 to 31. If so, you could save 3 bytes per row by changing day from INT (4 bytes) to TINYINT (1 byte). Similarly, you could save 1 byte per row by changing yearmonth from INT to MEDIUMINT. 4 bytes per row * 38 million rows = about 150 Mb saved. Smaller rows make disk reads faster, and require less memory to process and cache. Also, smaller columns make for smaller indexes. Finally, you should drop any unnecessary indexes. Whichever multi-column index you pick, you should drop the single column index on the column which comes first in the multi-column index. Unless you sometimes query by day without regard to other columns, you won't need an index on day. I suspect that an index on (stem_base, yearmonth, day) and one on (yearmonth, day) are the only ones you need. The first will work for your typical query, as well as for selects by just stem_base and selects by stem_base and yearmonth. The second will work for selects by yearmonth and selects by yearmonth and day, without regard to stem_base. If you never select just by date, you don't even need the second. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation Problem with 4.0.20
I've just downloaded 4.0.20 for windows Installed on the D: drive. Set up an .ini pointing at D: drive Ran D:\mysql\bin\mysqld When I then ran mysqlshow I only had the test database, not the mysql database as the documentation said I should. I then deleted everything and installed again, and got the same result. Does this mean the installation has failed ? Dave UK
perpetual connecting to master
I just set up another reeplication slave for one of my servers - it's not something new to me. I'm using 4.1.3-beta on a new server which will eventually take over as the master. I set up a server id, did change master to... and started the slave - but the slave seems to perpetually stay in a state of Connecting to master I've done thie before, but I've never encounterred this - can anyone give pointers on how to troubleshoot? Thanks, Issac -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB 4.1.3: count(*) and number of rows does not match
Hey Mike, Thanks again for your suggestions. I understand (and agree) with your comments about a clean shutdown. I'm always careful to do that. In this case, the mysql server has only been shutdown a couple times and it was a clean shutdown in each case. You suggest running table checks daily. That would be impossible here. The tables are large, there are several of them, and data loads around the clock. It takes several hours to do a CHECK TABLE on any of the larger tables alone. It would require quite a bit of application change to load data in (and make it available from) a staging area while the tables were checking. It may come down to that, but it's certainly not a picture I like. Thanks -keith At 01:21 AM 8/1/2004, Keith Thompson wrote: Thanks Mike, I've always ignored CHECK TABLE because I always thought it was just for MyISAM. I ran CHECK TABLE. It told me that my table was corrupt. I then dumped the table with mysqldump and recreated it. After that CHECK TABLE said it was OK (and in comparing values with the master server it appears to have all data intact as well). Unlike CHECK TABLE, REPAIR TABLE only works on MyISAM tables, so I can't use that. Then, I decided to run CHECK TABLE on all my tables (which for the ones with 125 million rows will probably be running for a while). The problem now is that all of my larger tables are reported as being corrupt--every single table with more than say 500,000 records is reported as corrupt. Wow! Could this be true? Yup. But it could just be that the table counts that are off. The tables all access fine and only these two smaller tables had these count(*) mismatch problems (and were the only two smaller tables that came up corrupt). How did this happen? I've never gotten an error in my .err file, never had a hardware access failure in the system logs, and have done very little with this server beyond initially loading it (by replaying mysqldump output in the first place) and letting it stay up to date with replication. In my case, I can corrupt tables by not shutting down the server properly. For example, if the server crashes then it could cause problems with the files because the tables are not flushed prior to closing the tables so the counts can be off. Or if you Kill a process like REPAIR TABLE or OPTIMIZE TABLE can lead to a corrupt table. Or if you are using --delay-key-writes in your .cnf file can cause problems if you're not careful. There is a Shutdown command you can execute to shut down the server that automatically flushes the tables and closes everything in an orderly fashion. http://dev.mysql.com/doc/mysql/en/Server_Shutdown.html http://dev.mysql.com/doc/mysql/en/Crashing.html It's going to take me a week to reload these tables if they are all corrupt. Based on what little this system has done so far, I wonder how long it will be before they're corrupt again. A week? That must be a lot of large tables. (Of course InnoDb takes longer than MYISAM tables to load.) I think everyone needs to write a check and repair script for their MySQL database. Run the Check script daily to see if and when the error occurs or twice a day if you're really paranoid. Then have a recovery procedure written down so someone can follow it. And of course log the error report in a file so you can try and determine what caused it and how often it occurs. Is there a faster way to fix these corruptions than to dump and reload the tables? I'm not sure because I don't use InnoDb. I'm sure Heikki would know. Mike -keith Hi Marc, Thanks for you response. In answer to your questions, there are no embedded newlines and I did look at index issues. I did not try rebuilding the index, which would be easy to do in this case since the tables are small (unlike a couple of my other tables that have 125+ million rows and changing indexes is measured in days instead of minutes). I forgot to mention in my first message that the select count(*)... is the one that is wrong. Counting all returned elements isn't too high, the count(*) is too low. Doing the count(*) on the same exact table on a 4.0.17 system (the replication master) gives the correct count. -keith Keith, Try doing a Check Table tablename. See http://dev.mysql.com/doc/mysql/en/CHECK_TABLE.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT difficulties
That won't work either. You cannot use an aggregate function in the WHERE clause. The purpose of the WHERE clause is to determine which rows to look at, but you won't know the MIN(price) until after you've looked at them. You can use aggregate functions in the HAVING clause, but that filters rows at the end, long after the random offerid has been chosen, so it won't have the effect you intend. Once you use aggregate functions, you aren't really selecting rows anymore. Rather, you are selecting results of the aggregate function(s) labeled by values from the grouped column(s). As a convenience, mysql allows you to use other column values to label your groups, but that's all they are - labels. If the other column has different values within groups, however, this won't be useful. From the manual: MySQL extends the use of GROUP BY so that you can use columns or calculations in the SELECT list that don't appear in the GROUP BY clause. This stands for any possible value for this group... Do not use this feature if the columns you omit from the GROUP BY part are not unique in the group! You will get unpredictable results. http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html To get the rows which match the groupwise max/min, you must use a subquery, a temporary table, or the max-concat trick, as described in the manual http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. Michael João Paulo Freire wrote: SELECT offerid, resellerid, price FROM A WHERE price = MIN(price) GROUP BY resellerid; - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Daniel Lahey [EMAIL PROTECTED] Cc: Haitao Jiang [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, July 30, 2004 7:33 PM Subject: Re: SELECT difficulties That won't work. For each resellerid, you'll get the minimum price and an effectively random offerid. Michael Daniel Lahey wrote: You need to use the 'GROUP BY' clause: SELECT offerid, resellerid, MIN(price) FROM A GROUP BY resellerid; Cheers, Dan On Jul 30, 2004, at 2:37 PM, Haitao Jiang wrote: Hi, there Maybe this question is not MySQL specific, but I just wondering if MySQL has any way to doing this: I have a table A like following: offerId resellerId price -- 1r1 5 2r1 10 3r2 12 4r2 4 -- How can I select only one row from each reseller with the the lowest price from this reseller? i.e. the result should be 1 r1 5 4 r2 4 Thanks in adavnce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a question/issue...
[note: This discussion went on off-list, but I thought the info might be useful in list context, so I am reposting it to the list. Sorry about the lack of individual messages. I wish gmail had an option to automatically add an address to a reply when you are replying to a message that has been assigned a label. I've requested the feature in fact. ] I didn't say it can't be done. I said you have to be _very_ careful. You can't use the connection in both the parent and the client. If you connect in the parent, then don't do anything with that connection handle. Let the client do all the work with that connection. If you don't do that you have an absolute recipe for disaster. You need to have one connection per child. It simply won't work otherwise. That isn't to say you can't create one connection for each child in the parent process and pass it through fork (more on that below). Just don't cross the streams, it will be _very bad_. Close the connection after the child ends. Don't close the connection in the child. The same logic applies to other file descriptors. If you pass a socket to a child process, then you close the child in the parent and you don't try to do anything else with it. If you don't close it, you will eventually run out of file descriptors. The difference is that closing a database involves more than just closing a file descriptor. Database connections, well actually the database access layers, such as PHP and PERL/DBI, do a lot of background work that is obfuscated from the user. This includes automatically closing file handles, database connections, statement handles, etc. If you close your handle in the client, and the client library automatically issues a COMMIT and closes the connection, then you kill the child and the parent also tries to close the connection (probably because the connection has gone out of scope) then you are going to have problems. This is why I said, when you use Perl/DBI you need to make sure InactiveDestroy is set correctly in the parent and the child. This prevents that background voodoo from going on in DBI and ensures that you don't run into problems. You will need to do similar synchronization in other languages to ensure that your children and your parent get along. My philosophy is that you should destroy the connection in the same thread/process/whatever that created the connection. This ensures that resources are released properly. Now, all that said, I truely believe that the best way to do things is probably the easiest way. In general it is much easier, and you are goign to have less bugs, if you just connect in the child. If you are connecting in the parent, because the child doesn't normally know what database to connect to, then just set a variable with the connection details (dsn/connect string/etc) and let the child connect on it's own using that. On Sun, 1 Aug 2004 12:02:30 -0700, bruce [EMAIL PROTECTED] wrote: since you're saying you can't share the connection, could you please tell me where in the docs it states you can't, and the reason(s) for not being able to 'share'. i would believe that once you make the connection, you should be able to 'use' it between any process that can get 'access' to it. so my initial question still stands, how can it be accomplished, or why it can't be done. i'm inclined to believe that if you can create a db connection handle, then any other process should be able to use it, as long as the creating process/parent process is still alive/running -bruce -Original Message- From: Justin Swanhart [mailto:[EMAIL PROTECTED] Sent: Sunday, August 01, 2004 11:53 AM To: [EMAIL PROTECTED] Subject: Re: a question/issue... In general, it is probably a bad idea to inherit database connections from a parent in a fork()'ed child process. What is your reasoning behind not permitted the children to make their own connection? If you can not connect from the child and you must inherit a database connection from the parent, make sure you create a new connection for each child. You can't share the same connection between the parent and the child. If you use the connection in the parent do not use it in the child and vice versa. If you are using perl DBI then you need to set InactiveDestroy where it makes sense to do so. See the DBI manual for details. You will need to take similar measures in other environments. .. On Sun, 1 Aug 2004 09:22:21 -0700, bruce [EMAIL PROTECTED] wrote: hi... i'm testing an app where i want a parent app to create the mysql db connection link/handle, and basically pass this handle off to child procesess. is there any reason why this can't be done? are there any examples of this already being accomplished that you might provide??? as far as i can tell/see, it should be possible. as long as the parent is still running, the connection link/handle should still be valid. i'm going to ultimately be
Purpose of (was Re: mysqld_safe)
faild and locked up. I'm hoping it is no. 1 that happened. if so, how do I get a command prompt after I start the server? Did you end the command with a character? I've been looking through the doc, the --help file, and the messages in the archives, and I can't find a reference to what this character _means_. The first reference in the online hlep I've found was 2.3.1 Source Installation Overview shell bin/mysqld_safe --user=mysql and the second was 2.4.2 Unix Post-Installation Procedures Verify that you can restart the server. Do this by using mysqld_safe or by invoking mysqld directly. For example: shell bin/mysqld_safe --user=mysql --log But the purpose wasn't explained. Signed, Whil, perpetually anal-retentive Moving to Linux: Freedom, Choice, Security, Opportunity http://www.hentzenwerke.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Purpose of (was Re: mysqld_safe)
Whil Hentzen wrote: faild and locked up. I'm hoping it is no. 1 that happened. if so, how do I get a command prompt after I start the server? Did you end the command with a character? I've been looking through the doc, the --help file, and the messages in the archives, and I can't find a reference to what this character _means_. The first reference in the online hlep I've found was 2.3.1 Source Installation Overview shell bin/mysqld_safe --user=mysql and the second was 2.4.2 Unix Post-Installation Procedures Verify that you can restart the server. Do this by using mysqld_safe or by invoking mysqld directly. For example: shell bin/mysqld_safe --user=mysql --log But the purpose wasn't explained. The tells bash to fork the process into the background so you get your console / xterm back. If you don't use the at the end, the mysqld_safe process will run in the foreground and will remain 'tied' to the console you started it from ... so if you close the console / xterm you may kill the mysqld_safe process. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Purpose of (was Re: mysqld_safe)
hi... within the linux/unix world, the '' at the end of a shell/command normally has meant to run the command as a background process for further information, search google for ' background process linux' etc... -bruce -Original Message- From: Whil Hentzen [mailto:[EMAIL PROTECTED] Sent: Sunday, August 01, 2004 3:33 PM To: [EMAIL PROTECTED] Subject: Purpose of (was Re: mysqld_safe) faild and locked up. I'm hoping it is no. 1 that happened. if so, how do I get a command prompt after I start the server? Did you end the command with a character? I've been looking through the doc, the --help file, and the messages in the archives, and I can't find a reference to what this character _means_. The first reference in the online hlep I've found was 2.3.1 Source Installation Overview shell bin/mysqld_safe --user=mysql and the second was 2.4.2 Unix Post-Installation Procedures Verify that you can restart the server. Do this by using mysqld_safe or by invoking mysqld directly. For example: shell bin/mysqld_safe --user=mysql --log But the purpose wasn't explained. Signed, Whil, perpetually anal-retentive Moving to Linux: Freedom, Choice, Security, Opportunity http://www.hentzenwerke.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Purpose of (was Re: mysqld_safe)
I've been looking through the doc, the --help file, and the messages in the archives, and I can't find a reference to what this character _means_. The tells bash to fork the process into the background so you get your console / xterm back. If you don't use the at the end, the mysqld_safe process will run in the foreground and will remain 'tied' to the console you started it from ... so if you close the console / xterm you may kill the mysqld_safe process. Ah, it's not part of the mysqld command itself. Got it. Thanks, all -- Whil Moving to Linux: Freedom, Choice, Security, Opportunity http://www.hentzenwerke.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB 4.1.3: count(*) and number of rows does not match
At 12:55 PM 8/1/2004, Keith Thompson wrote: Hey Mike, Thanks again for your suggestions. I understand (and agree) with your comments about a clean shutdown. I'm always careful to do that. In this case, the mysql server has only been shutdown a couple times and it was a clean shutdown in each case. You suggest running table checks daily. That would be impossible here. The tables are large, there are several of them, and data loads around the clock. It takes several hours to do a CHECK TABLE on any of the larger tables alone. It would require quite a bit of application change to load data in (and make it available from) a staging area while the tables were checking. It may come down to that, but it's certainly not a picture I like. Thanks -keith Keith, You may want to post another message asking how other people check their large databases when the system is running 24/7. I'm sure a lot of people have solved the problem. Perhaps use load balancing and take the master down for checking while they use the other database. Send the updates to the working database and when the master comes back on, update the master with the new changes. Mike At 01:21 AM 8/1/2004, Keith Thompson wrote: Thanks Mike, I've always ignored CHECK TABLE because I always thought it was just for MyISAM. I ran CHECK TABLE. It told me that my table was corrupt. I then dumped the table with mysqldump and recreated it. After that CHECK TABLE said it was OK (and in comparing values with the master server it appears to have all data intact as well). Unlike CHECK TABLE, REPAIR TABLE only works on MyISAM tables, so I can't use that. Then, I decided to run CHECK TABLE on all my tables (which for the ones with 125 million rows will probably be running for a while). The problem now is that all of my larger tables are reported as being corrupt--every single table with more than say 500,000 records is reported as corrupt. Wow! Could this be true? Yup. But it could just be that the table counts that are off. The tables all access fine and only these two smaller tables had these count(*) mismatch problems (and were the only two smaller tables that came up corrupt). How did this happen? I've never gotten an error in my .err file, never had a hardware access failure in the system logs, and have done very little with this server beyond initially loading it (by replaying mysqldump output in the first place) and letting it stay up to date with replication. In my case, I can corrupt tables by not shutting down the server properly. For example, if the server crashes then it could cause problems with the files because the tables are not flushed prior to closing the tables so the counts can be off. Or if you Kill a process like REPAIR TABLE or OPTIMIZE TABLE can lead to a corrupt table. Or if you are using --delay-key-writes in your .cnf file can cause problems if you're not careful. There is a Shutdown command you can execute to shut down the server that automatically flushes the tables and closes everything in an orderly fashion. http://dev.mysql.com/doc/mysql/en/Server_Shutdown.html http://dev.mysql.com/doc/mysql/en/Crashing.html It's going to take me a week to reload these tables if they are all corrupt. Based on what little this system has done so far, I wonder how long it will be before they're corrupt again. A week? That must be a lot of large tables. (Of course InnoDb takes longer than MYISAM tables to load.) I think everyone needs to write a check and repair script for their MySQL database. Run the Check script daily to see if and when the error occurs or twice a day if you're really paranoid. Then have a recovery procedure written down so someone can follow it. And of course log the error report in a file so you can try and determine what caused it and how often it occurs. Is there a faster way to fix these corruptions than to dump and reload the tables? I'm not sure because I don't use InnoDb. I'm sure Heikki would know. Mike -keith Hi Marc, Thanks for you response. In answer to your questions, there are no embedded newlines and I did look at index issues. I did not try rebuilding the index, which would be easy to do in this case since the tables are small (unlike a couple of my other tables that have 125+ million rows and changing indexes is measured in days instead of minutes). I forgot to mention in my first message that the select count(*)... is the one that is wrong. Counting all returned elements isn't too high, the count(*) is too low. Doing the count(*) on the same exact table on a 4.0.17 system (the replication master) gives the correct count. -keith Keith, Try doing a Check Table tablename. See http://dev.mysql.com/doc/mysql/en/CHECK_TABLE.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a question/issue...
On Sun, Aug 01, 2004 at 09:22:21AM -0700, bruce wrote: hi... i'm testing an app where i want a parent app to create the mysql db connection link/handle, and basically pass this handle off to child procesess. is there any reason why this can't be done? are there any examples of this already being accomplished that you might provide??? as far as i can tell/see, it should be possible. as long as the parent is still running, the connection link/handle should still be valid. i'm going to ultimately be working in perl/php/c but if i can get it working in one language, i can get it in the others as well any thoughts/comments/criticisms/etc Can you establish a connection in the parent, fork a child, and then let the child use it? Yes, you can do that. But you need to be careful. If the parent and child *both* try to use it, chaos will ensue. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: better performance with optimize!?? (jboss)
On Sun, Aug 01, 2004 at 01:42:24PM +0200, Marc wrote: I use Mysql with JBOSS as applicationserver. i have strange response-time differences, which i can't explain. 1) after reboot the computer, it takes about 300ms to read 12 entities (cmp, read ahead, 2 rows each entity) One entity is accessed by primary key, the others by foreign key. 2) when i reboot the computer and run mysql optimize first, it takes only about 80ms to read the 12 entities!! Caching? In case #2, MySQL may have cached large amounts of the table during the OPTIMIZE. 3) when i do the same with pure java / jdbc (outside jboss), it takes only 30ms!!! it doesn't matter, if i run optimize or not! JBoss overhead? I dunno, I'm not a Java guy at all. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: a question/issue...
jeremy... hey.. how's yahoo!! anyway, i know you should be able to do this... do you have any examples of code or can you point me to an example that demonstrates how to do it... i've been trying to accomplish this with perl for 2-3 days now with no luck.. so i know there must be something i'm missing!!! thanks i can provide you with the initial sample test code... it's only 15-20 lines of code for both the parent/child.. but i'm trying to get a feel for the process to see how this might work. thanks! -bruce -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Sunday, August 01, 2004 6:26 PM To: bruce Cc: [EMAIL PROTECTED] Subject: Re: a question/issue... On Sun, Aug 01, 2004 at 09:22:21AM -0700, bruce wrote: hi... i'm testing an app where i want a parent app to create the mysql db connection link/handle, and basically pass this handle off to child procesess. is there any reason why this can't be done? are there any examples of this already being accomplished that you might provide??? as far as i can tell/see, it should be possible. as long as the parent is still running, the connection link/handle should still be valid. i'm going to ultimately be working in perl/php/c but if i can get it working in one language, i can get it in the others as well any thoughts/comments/criticisms/etc Can you establish a connection in the parent, fork a child, and then let the child use it? Yes, you can do that. But you need to be careful. If the parent and child *both* try to use it, chaos will ensue. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Purpose of (was Re: mysqld_safe)
At 18:29 -0500 8/1/04, Whil Hentzen wrote: I've been looking through the doc, the --help file, and the messages in the archives, and I can't find a reference to what this character _means_. The tells bash to fork the process into the background so you get your console / xterm back. If you don't use the at the end, the mysqld_safe process will run in the foreground and will remain 'tied' to the console you started it from ... so if you close the console / xterm you may kill the mysqld_safe process. Ah, it's not part of the mysqld command itself. Got it. Right. It's a process-control aspect of how the shell itself works. The genesis of this in Unix is described in the Process Control section of this document by Dennis Ritchie: http://cm.bell-labs.com/cm/cs/who/dmr/hist.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Purpose of (was Re: mysqld_safe)
Ah, it's not part of the mysqld command itself. Got it. Right. It's a process-control aspect of how the shell itself works. And it's also rather confusing! *s* I really thought it was just another part of the command, not a structure like a pipe or a redirector. Oh, these Windows newbies trying to learn Linux. Aren't they fun to watch *s* The genesis of this in Unix is described in the Process Control section of this document by Dennis Ritchie: http://cm.bell-labs.com/cm/cs/who/dmr/hist.html Super. Thanks! -- Whil Moving to Linux: Freedom, Choice, Security, Opportunity http://www.hentzenwerke.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation Problem with 4.0.20
Which mysql username did you use to log in? I've not used mysql on Windows, but I expect that if you simply ran mysqlshow on a new install, you'd connect as the anonymous user. The anonymous user only has rights to the test db, so that's all you'd see. Try mysqlshow -u root or, if you've set the root password, mysqlshow -u root -p instead. Michael [EMAIL PROTECTED] wrote: I've just downloaded 4.0.20 for windows Installed on the D: drive. Set up an .ini pointing at D: drive Ran D:\mysql\bin\mysqld When I then ran mysqlshow I only had the test database, not the mysql database as the documentation said I should. I then deleted everything and installed again, and got the same result. Does this mean the installation has failed ? Dave UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full Text Search Score calculations.
Hello everyone, I recently decided to perform an experiment. I was using Java to do string comparison on sometimes large data sets (this would sometimes take days). A friend of mine at work suggested that I use MySQL's full text searching. I quickly made the change over and I've found that it is great, extremely fast and I can see that the scoring is working perfectly. Well, almost perfectly. My problem is that I find the scores hard to read. When comparing these two strings 5600 10th Ave and 5600 10th Ave I get a score of 5.40898323059082. When I compare these two strings Greenacres Grand Slam and Greenacres Grand Slam, I get a score of 9.09278202056885, which I assume to be a perfect match. However, these scores are hard to program for. I need the scores to be in a percentage form (0-100, 0-1.0, etc...). I read through the documentation and I found Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word. I figure I can use this to take the returned score and calculate a percentage, but I don't know how. Does anyone know of a simple way to do this? Is there a function provided for full text searching that I am missing? If not, does anyone know of a formula I can use to get the percentage of the matches? Thanks for any help anyone can provide. Sincerely, Eric Berry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
