Re: Always corrupted after restart server
How are you shutting down the server during the restart.. have you checked the logs? Might you be issuing a kill and crashing it? MyISAM doesnot dealwith crashes very elegantly. Also, what is some reason? Might thereason you need to restart be related? - michael dykman On Tue, Aug 24, 2010 at 11:13 PM, sangprabv sangpr...@gmail.com wrote: Hi List, I have a very big size MyISAM table. For some reason I need to restart the server periodically. But After restarting the server, the table always get corrupt, and always need to run myisamchk. Don't know what cause the problem. But it will be very helpful if somebody can give me some tips to avoid this problem. Thanks alot. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
calculating memory size
Hello, We are having issues with one of our servers sometimes hanging up and when attempting to shutdown the DB, we get cannot create thread errors. This server has 6GB of RAM and no swap. According to some reasearch I was doing I found this formula for calculating memory size: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = (in your case) 384M + (64M + 2M)*1000 = 66384M That come directly from this old post: http://bugs.mysql.com/bug.php?id=5656In our case, the result is just below 6GB and then accounting for other apps, we would certainly exceed that. So, my question is simply: is that forumula accurate for determinning potential mysql memory allocation? Thanks!
Re: How many pager command within mysql command line client?
I know, all the shell command can do this. Thanks. 2010/8/25 Moon's Father yueliangdao0...@gmail.com Hi. For example, entering mysql command line client, mysql pager more ( or pager md5sum and so on.) I want to know how many command the 'pager' follows? Any reply will be big appreciated.
calculating memory size (again)
Re-sending... my mail client or server somehow mangled my original message... sorry. -- Hello, We are having issues with one of our servers sometimes hanging up and when attempting to shutdown the DB, we get cannot create thread errors. This server has 6GB of RAM and no swap. According to some reasearch I was doing I found this formula for calculating memory size: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = (in your case) 384M + (64M + 2M)*1000 = 66384M That come directly from this old post: http://bugs.mysql.com/bug.php?id=5656 In our case, the result is just below 6GB and then accounting for other apps, we would certainly exceed that. So, my question is simply: is that forumula accurate for determinning potential mysql memory allocation? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Complex Select Query
Alternatively, you could just code a recursive function to do the same instead of doing it in SQL or stored procs. On Tue, Aug 24, 2010 at 11:01 AM, Victor Subervi victorsube...@gmail.comwrote: On Tue, Aug 24, 2010 at 1:43 PM, Peter Brawley peter.braw...@earthlink.netwrote: What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? It's a tree. See http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html and http://www.artfulsoftware.com/infotree/treequeryperformance.pdf. How do I cut down the tree? That stuff is *way* too complex for my needs. I just thought I'd make my presentation of data a little cleaner but frankly it ain't worth going through all that learning and experimentation to do it. If there isn't another way I'll just forget about it. Any other suggestions would be nice. TIA. Victor - -Original Message- From: Victor Subervi victorsube...@gmail.com Sent: Aug 24, 2010 1:14 PM To: mysql@lists.mysql.com Subject: Complex Select Query Hi; I have the following query: select * from spreadsheets s join products p on p.Item=s.Item join categories c on p.Category=c.ID where s.Client=%s order by p.category, c.parent; mysql describe products; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | ID | int(4) | NO | PRI | NULL| auto_increment | | Category| int(3) | YES | | NULL|| | Item| varchar(20) | YES | UNI | NULL|| | Description | varchar(255) | YES | | NULL|| | UOM | varchar(20) | YES | | NULL|| | Cost| float(7,2) | YES | | NULL|| +-+--+--+-+-++ 6 rows in set (0.00 sec) mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) | NO | PRI | NULL| auto_increment | | Category | varchar(20) | YES | UNI | NULL|| | Parent | varchar(20) | YES | | NULL|| +--+-+--+-+-++ What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? TIA, Victor
Re: Always corrupted after restart server
On 25/08/2010 8:05 a, Michael Dykman wrote: How are you shutting down the server during the restart.. have you checked the logs? Might you be issuing a kill and crashing it? MyISAM doesnot dealwith crashes very elegantly. Also, what is some reason? Might thereason you need to restart be related? - michael dykman On Tue, Aug 24, 2010 at 11:13 PM, sangprabvsangpr...@gmail.com wrote: Hi List, I have a very big size MyISAM table. For some reason I need to restart the server periodically. But After restarting the server, the table always get corrupt, and always need to run myisamchk. Don't know what cause the problem. But it will be very helpful if somebody can give me some tips to avoid this problem. Thanks alot. To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com Maybe you need to check *why* you have to keep on restarting the server? Cure the disease, not the symptoms? We have a particular mysql server that has been up the last 8 months with no downtime... -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: calculating memory size
On 25/08/2010 8:23 a, Geoff Galitz wrote: Hello, We are having issues with one of our servers sometimes hanging up and when attempting to shutdown the DB, we get cannot create thread errors. This server has 6GB of RAM and no swap. According to some reasearch I was doing I found this formula for calculating memory size: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = (in your case) 384M + (64M + 2M)*1000 = 66384M That come directly from this old post: http://bugs.mysql.com/bug.php?id=5656In our case, the result is just below 6GB and then accounting for other apps, we would certainly exceed that. So, my question is simply: is that forumula accurate for determinning potential mysql memory allocation? Thanks! Are you running a 64bit OS/MySQL? -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Retrieve three columns in sub query
Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age 10 ORDER BY RAND() LIMIT 1 I've looked and tried everything, but nothing seems to work... Cheers Neil
Re: Retrieve three columns in sub query
As far as I know sub-queries intends to be an only one column and row result. -- João Cândido de Souza Neto Tompkins Neil neil.tompk...@googlemail.com escreveu na mensagem news:aanlkti=djkujcsg=kf29sjsp0yllhmhb02mqzdvzd...@mail.gmail.com... Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age 10 ORDER BY RAND() LIMIT 1 I've looked and tried everything, but nothing seems to work... Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Retrieve three columns in sub query
I thought as much, if anyone else can shed some light that would be great. If not, I'm going to have to write an additional query. 2010/8/25 João Cândido de Souza Neto j...@consultorweb.cnt.br As far as I know sub-queries intends to be an only one column and row result. -- João Cândido de Souza Neto Tompkins Neil neil.tompk...@googlemail.com escreveu na mensagem news:aanlkti=djkujcsg=kf29sjsp0yllhmhb02mqzdvzd...@mail.gmail.com... Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age 10 ORDER BY RAND() LIMIT 1 I've looked and tried everything, but nothing seems to work... Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
RE: Retrieve three columns in sub query
select ci.*, ct.* from (select name, countrycode from city) ci, country ct where ci.countrycode = ct.code Regards, m -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, August 25, 2010 1:23 PM To: [MySQL] Subject: Retrieve three columns in sub query Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age 10 ORDER BY RAND() LIMIT 1 I've looked and tried everything, but nothing seems to work... Cheers Neil -- Kup wlasne mieszkanie za 72 tys. zl. Sprawdz najlepsze oferty http://linkint.pl/f27c4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Retrieve three columns in sub query
Not tested, but i think it could work for you: SELECT student_age, (SELECT student_subjects_id FROM student_subjects ORDER BY RAND() LIMIT 1) as subject_id, (SELECT random_mark FROM student_subjects having student_subject_id = subject_id) as random_mark, (SELECT subject FROM student_subjects having student_subject_id = subject_id) as subject FROM students WHERE student_age 10 ORDER BY RAND() LIMIT 1 -- João Cândido de Souza Neto Tompkins Neil neil.tompk...@googlemail.com escreveu na mensagem news:aanlkti=ecibcm3kcj7kizprnrkat=nnasfndw5srm...@mail.gmail.com... I thought as much, if anyone else can shed some light that would be great. If not, I'm going to have to write an additional query. 2010/8/25 João Cândido de Souza Neto j...@consultorweb.cnt.br As far as I know sub-queries intends to be an only one column and row result. -- João Cândido de Souza Neto Tompkins Neil neil.tompk...@googlemail.com escreveu na mensagem news:aanlkti=djkujcsg=kf29sjsp0yllhmhb02mqzdvzd...@mail.gmail.com... Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age 10 ORDER BY RAND() LIMIT 1 I've looked and tried everything, but nothing seems to work... Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Retrieve three columns in sub query
Hi M, This appears to do exactly what I require. Thank you ! Cheers Neil On Wed, Aug 25, 2010 at 1:43 PM, misiaQ mis...@poczta.fm wrote: select ci.*, ct.* from (select name, countrycode from city) ci, country ct where ci.countrycode = ct.code Regards, m -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, August 25, 2010 1:23 PM To: [MySQL] Subject: Retrieve three columns in sub query Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age 10 ORDER BY RAND() LIMIT 1 I've looked and tried everything, but nothing seems to work... Cheers Neil -- Kup wlasne mieszkanie za 72 tys. zl. Sprawdz najlepsze oferty http://linkint.pl/f27c4
Table design question
Hi, I'm creating a application which hosts football matches and I want to record the player appearances, goals etc. I was thinking of having a record for each player as follows : appearance_id season_id player_id team_id competition_id appearance goals yellow_card red_card date_played Is this the sort of standard layout, recommended for such application. From here, I can then count number of appearances, goals scored etc within a season or for a team_id ? Cheers Neil
To join or not to join?
I need a single row from 2 different tables. It is rather trivial to create a join that will join these two tables and give me all the information I want in one query. It is also fairly easy to just execute two different queries with out any join and get the data I need. Since the both single table queries or the join query will always only return a single row, I was wondering if there was a performance hit doing it one way or the other. On one table the where clause is on the primary key on the other table there where clause is on a single column that is indexed but not unique. However in this situation it will be unique. I can't put a unique key on this field in the second table because there are other applications of the second table where I do need it to non unique. Hope that makes sense. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Calculating table standings
Travis Do you think it would be better if I stored the information in a separate table, rather than using unions etc - to make the searching, counting etc easier ? Or is this method a standard way of dealing with this sort of data. Cheers Neil On Tue, Aug 24, 2010 at 5:43 PM, Travis Ard travis_...@hotmail.com wrote: I think your match table has all the information necessary to display the results you want. Since each record contains data for two teams (home and away), you'd probably need to select each separately and union the results together before summarizing. Your query might look something like the following: select seasons_id ,team_id ,count(*) as games_played ,sum(home) as home_games_played ,sum(away) as away_games_played ,sum(won_home) as won_home ,sum(draw_home) as draw_home ,sum(lost_home) as lost_home ,sum(scored_home) as scored_home ,sum(conceded_home) as conceded_home ,sum(won_away) as won_away ,sum(draw_away) as draw_away ,sum(lost_away) as lost_away ,sum(scored_away) as scored_away ,sum(conceded_away) as conceded_away from (select seasons_id ,home_team_id as team_id ,1 as home ,0 as away ,if(home_goals away_goals, 1, 0) as won_home ,if(home_goals = away_goals, 1, 0) as draw_home ,if(home_goals away_goals, 1, 0) as lost_home ,home_goals as scored_home ,away_goals as conceded_home ,0 as won_away ,0 as draw_away ,0 as lost_away ,0 as scored_away ,0 as conceded_away from matches union all select seasons_id ,away_team_id as team_id ,0 as home ,1 as away ,0 as won_home ,0 as draw_home ,0 as lost_home ,0 as scored_home ,0 as conceded_home ,if(away_goals home_goals, 1, 0) as won_away ,if(away_goals = home_goals, 1, 0) as draw_away ,if(away_goals home_goals, 1, 0) as lost_away ,away_goals as scored_away ,home_goals as conceded_away from matches) s1 group by seasons_id, team_id; -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 24, 2010 7:45 AM To: [MySQL] Subject: Calculating table standings Hi, I wondered if anyone can offer me some help with regards the following issue I'm having. Basically, I've the following table structure containing rows of results between two football teams. The fields are match_id seasons_id week_number home_team_id away_team_id home_goals away_goals Based on the above information, I'm wanting to generate a league table listing showing games_played won_home draw_home lost_home scored_home conceded_home won_away draw_away lost_away scored_away conceded_away Finally I also want a tally for the number of points e.g 3 points for win, 1 point for a draw. Do you think this is possible with the basic table I have, or should I consider putting the result data in a leagues table working out the fields I have listed above, and then just calculating it and display it ? Thanks for any advice. Cheers Neil
Re: How many pager command within mysql command line client?
In the last episode (Aug 25), Moon's Father said: Hi. For example, entering mysql command line client, mysql pager more ( or pager md5sum and so on.) I want to know how many command the 'pager' follows? Any reply will be big appreciated. Any command can be used as a pager. Some commands are less useful than others, though :) PAGER set to 'cat -n' mysql select hello; 1 +---+ 2 | hello | 3 +---+ 4 | hello | 5 +---+ 1 row in set (0.01 sec) mysql pager date PAGER set to 'date' mysql select hello; Wed Aug 25 15:43:46 CDT 2010 1 row in set (0.00 sec) mysql -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: calculating memory size (again)
Hi Geoff, This server has 6GB of RAM and no swap. According to some reasearch I was doing I found this formula for calculating memory size: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = (in your case) 384M + (64M + 2M)*1000 = 66384M That come directly from this old post: http://bugs.mysql.com/bug.php?id=5656 In our case, the result is just below 6GB and then accounting for other apps, we would certainly exceed that. So, my question is simply: is that forumula accurate for determinning potential mysql memory allocation? No, it is not at all. It never was, and many people have had trouble with it just as you have. There really is no way to do what that formula tries to do. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Complicated SQL Query
I have a requirement to keep track of a set of data, and all changes that might occur. In order to do this, for each field of the data set, I've created a table that keeps track of the version, the value, the time the change was made, and a linking number that links all the different tables back to a single record. I'm assuming, hoping, and believe this is a very common setup. What I'm having trouble with is queries that aren't nested sub-selects, or joins that won't show NULL data. For example ... select rsi.value, rsi.record_id, ssn.value as serviceseqnum, esn.value as eventseqnum from record_set_id as rsi LEFT JOIN serviceseqnum as ssn ON rsi.record_id = ssn.record_id LEFT JOIN eventseqnum as esn ON ssn.record_id = esn.record_id Will join the tables, but doesn't take the version information into consideration. If I add a where to include the maximum version, to get the most recent value, it won't show anything if one of the values happens to be NULL. Using sub-selects generally causes long query time ... select rsi.value, rsi.record_id ( select value from serviceseqnum where record_id = rsi.record and version = ( select max(version) from serviceseqnum where record_id = rsi.record_id ) ) from record_set_id ) from record_set_id as rsi ... especially when trying to get a dozen values strung together so they appear as one record. Is there a better way to handle these queries that I'm just not thinking of? Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Complicated SQL Query
I found an answer without having to worry about complicated SQL statements - it's more about managing the tables than the SQL. Jacob Quoting Jacob Steinberger trefal...@realitybytes.net: I have a requirement to keep track of a set of data, and all changes that might occur. In order to do this, for each field of the data set, I've created a table that keeps track of the version, the value, the time the change was made, and a linking number that links all the different tables back to a single record. I'm assuming, hoping, and believe this is a very common setup. What I'm having trouble with is queries that aren't nested sub-selects, or joins that won't show NULL data. For example ... select rsi.value, rsi.record_id, ssn.value as serviceseqnum, esn.value as eventseqnum from record_set_id as rsi LEFT JOIN serviceseqnum as ssn ON rsi.record_id = ssn.record_id LEFT JOIN eventseqnum as esn ON ssn.record_id = esn.record_id Will join the tables, but doesn't take the version information into consideration. If I add a where to include the maximum version, to get the most recent value, it won't show anything if one of the values happens to be NULL. Using sub-selects generally causes long query time ... select rsi.value, rsi.record_id ( select value from serviceseqnum where record_id = rsi.record and version = ( select max(version) from serviceseqnum where record_id = rsi.record_id ) ) from record_set_id ) from record_set_id as rsi ... especially when trying to get a dozen values strung together so they appear as one record. Is there a better way to handle these queries that I'm just not thinking of? Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=trefal...@realitybytes.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
