Trouble with Average
I have a log file that captures data from various sensors every minute that
we use to draws graphs on our website.
The table looks like this:
CREATE TABLE `log` (
`id` int(14) NOT NULL auto_increment,
`VarName` varchar(255) NOT NULL,
`TimeString` datetime NOT NULL,
`VarValue` decimal(25,6) NOT NULL,
`Validity` int(1) NOT NULL,
`Time_ms` decimal(25,6) NOT NULL,
PRIMARY KEY (`id`),
KEY `timestamp` (`TimeString`),
KEY `name` (`VarName`),
KEY `nametimevalue` (`VarName`,`VarValue`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
My existing query which works just fine for our purposes:
SELECT CONCAT('[', ((UNIX_TIMESTAMP(TimeString)+(.$tz_offset.*3600))) *
1000, ' ,', TRUNCATE(VarValue,0), ']') AS value FROM log WHERE VarName =
'04_Set21_SWOS\_085_1300CI' AND SUBSTR(TimeString,1,10) = CURDATE() ORDER
BY TimeString ASC
The only issue is that VarValue tends to vary every minute and create a
graph that is not as smooth as we would like. Therefore, instead of
retuning VarValue for each minute in the above query, we want to return the
average of the last 5 values for that VarName.
I have been searching around the web all morning and haven't hit on the
proper solution. Help would be much appreciated.
Thanks.
mysql listed as attach page by google?
I don't suppose I am the first to notice this, but most of the pages on dev.mysql.com have been listed by google as attack pages, e.g http://dev.mysql.com/downloads/. Has there been a problem, or is google being overzealous? Thanks! -Mike
Getting a Value and an Average Value of Previous 20 Records in One Query
I have been struggling with this issue most of the day. I can get the result
I need by using 2 queries, but that takes way too long. I'm trying to see if
there is a way to get the same result within a single query.
Here's the table
CREATE TABLE `log` (
`id` int(14) NOT NULL auto_increment,
`VarName` varchar(255) NOT NULL,
`TimeString` varchar(255) NOT NULL,
`VarValue` decimal(25,6) NOT NULL
)
The log table has 1 row added each minute of the day. For each VarValue I
also need the average value of the 20 previous rows.
My 2 step solution looks like this:
$phs = $db-get_results(SELECT VarValue, TimeString FROM log WHERE VarName
= 'xyz' AND SUBSTR(TimeString,1,10) = CURDATE() ORDER BY TimeString ASC);
foreach($phs as $ph) {
$myvalue = $db-get_var(SELECT AVG(VarValue) FROM log WHERE VarName = 'xyz'
AND TimeString = '.$ph-TimeString.' ORDER BY TimeString DESC LIMIT 20);
}
I have tried to figure a way using join as well as subselects, but haven't
hit on the right solution yet.
I appreciate some direction.
Thanks.
Al
Join Suddenly Failing
I have a website that gets used once a year for a soccer tournament. It has been working fine since 2006. No script changes since it was last used in 2009. All of a sudden the following script started throwing an error. SELECT contactinfo.contactdata, contactinfo.ContactID FROM team, person, teamperson LEFT JOIN personcontact ON person.PersonID = personcontact.PersonID LEFT JOIN contactinfo ON personcontact.ContactID = contactinfo.ContactID WHERE team.teamID = 22 AND team.TeamID = teamperson.TeamID AND teamperson.PersonID = person.PersonID AND person.PeopleTypeID =5 AND contactinfo.ContactTypeID =2 Error: Unknown column 'person.PersonID' in 'on clause' (1054) There are several of these type scripts and all are giving a similar error. The server version is 5.0.87. I suspect the hosting company may have upgraded to a new version of mysql. Thanks. Albert
Re: Join Suddenly Failing
Scott, Thanks. That appears to be the solution. Albert On Fri, Jan 22, 2010 at 10:42 AM, Scott Swaim sc...@tmcclinic.com wrote: I ran into this when I upgraded from 4.0.xx to 5.0.xx There was a change in the logic for the Joins. I determined that the FROM clause needs to be in parenthesis. i.e.FROM (team, person, teamperson) this allows all of the fields in all of the tables to be used. The change was made in mysql so that only the last table (i.e. teamperson) was used for your JOIN Scott Swaim I.T. Director Total Care / Joshua Family Medical Care (817) 297-4455 Website: www.totalcareclinic.com NOTICE: The information contained in this e-mail is privileged and confidential and is intended for the exclusive use of the recipient(s) named above. If you are not the intended recipient or his or her agent, you are hereby notified that you have received this document in error and that any use, disclosure, dissemination, distribution, or copying of this message is prohibited. If you have received this communication in error, please notify the sender immediately by e-mail, and delete the original message -Original Message- From: Albert Padley [mailto:ap3des...@gmail.com] Sent: Friday, January 22, 2010 11:37 AM To: mysql@lists.mysql.com Subject: Join Suddenly Failing I have a website that gets used once a year for a soccer tournament. It has been working fine since 2006. No script changes since it was last used in 2009. All of a sudden the following script started throwing an error. SELECT contactinfo.contactdata, contactinfo.ContactID FROM team, person, teamperson LEFT JOIN personcontact ON person.PersonID = personcontact.PersonID LEFT JOIN contactinfo ON personcontact.ContactID = contactinfo.ContactID WHERE team.teamID = 22 AND team.TeamID = teamperson.TeamID AND teamperson.PersonID = person.PersonID AND person.PeopleTypeID =5 AND contactinfo.ContactTypeID =2 Error: Unknown column 'person.PersonID' in 'on clause' (1054) There are several of these type scripts and all are giving a similar error. The server version is 5.0.87. I suspect the hosting company may have upgraded to a new version of mysql. Thanks. Albert
Update with select
I am trying to update a field on a record in a table. Here is the statement I created: UPDATE pdata SET pvalue = ( SELECT pvalue FROM pdata WHERE pentrytime =1207022400 ) WHERE pentrytime =117540; However, I get the following error: |#1093 - You can't specify target table 'pdata' for update in FROM clause What can I use to fix this? | -- Albert E. Whale, CHS CISA CISSP Sr. Security, Network, Risk Assessment and Systems Consultant ABS Computer Technology, Inc. http://www.ABS-CompTech.com - Email, Internet and Security Consultants SPAMZapper http://www.Spam-Zapper.com - No-JunkMail.com http://www.No-JunkMail.com - *True Spam Elimination*. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connections limit
Is there a limit of connections (open and close) that mysql can carry? or a limit by second? I have a big memory crash (double free or corruption) in my program and I smell that it could be mysql, thanks a lot, Albert
How do I change the Collation Variables?
How do I change: | collation_connection| latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server| latin1_swedish_ci to latin1_bin? Is this in my.cnf? or is this another way to do this? -- Albert E. Whale, CHS CISA CISSP Sr. Security, Network, Risk Assessment and Systems Consultant ABS Computer Technology, Inc. http://www.ABS-CompTech.com - Email, Internet and Security Consultants SPAMZapper http://www.Spam-Zapper.com - No-JunkMail.com http://www.No-JunkMail.com - *True Spam Elimination*.
Slow seach - Possible better query
I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley
Re: Slow seach - Possible better query
Dan, On Oct 31, 2006, at 3:41 PM, Dan Buettner wrote: Albert, it seems like the first query could be simplified, like so: SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) ORDER BY updated DESC LIMIT 50 This query won't work. The table contains threaded messages. The thread_id refers back to the id of the first message in the thread. The above query would return a list of all messages in the last 48 hours when all I want is to return the original message in the thread. This might help it hit the index you've created on the UPDATED column. I know there have been bugs here and there with the optimizer and IN subqueries not hitting indices, especially in earlier 4.1.x releases. If you have a large number of rows with recent values for the UPDATED column, MySQL may be doing a table scan. This is an instance where test/development scenarios don't always work quite as well as real data. However, 6-12 seconds for a 5000 row table does seem slow ... In our test database it's probably less than 50 rows that have been updated in the last 48 hours. Can you post the output of EXPLAIN query ? That will help us see how MySQL is planning to run your query, and may also reveal a little bit about your data. 1 PRIMARY mrldisc index updated 4 4888Using where 2 DEPENDENT SUBQUERY mrldisc index_subquery thread_id,updated thread_id 5 func 8 Using index; Using where Thanks. Al Thanks, Dan On 10/31/06, Albert Padley [EMAIL PROTECTED] wrote: I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow seach - Possible better query PROBLEM SOLVED
Dan, Actually you were on the right track. I changed your suggested query to the following and it seems to work and is a lot quicker. SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW (), INTERVAL 48 HOUR) AND mainthread = 'T' ORDER BY updated DESC LIMIT 50 Thanks. Al On Oct 31, 2006, at 4:01 PM, Albert Padley wrote: Dan, On Oct 31, 2006, at 3:41 PM, Dan Buettner wrote: Albert, it seems like the first query could be simplified, like so: SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) ORDER BY updated DESC LIMIT 50 This query won't work. The table contains threaded messages. The thread_id refers back to the id of the first message in the thread. The above query would return a list of all messages in the last 48 hours when all I want is to return the original message in the thread. This might help it hit the index you've created on the UPDATED column. I know there have been bugs here and there with the optimizer and IN subqueries not hitting indices, especially in earlier 4.1.x releases. If you have a large number of rows with recent values for the UPDATED column, MySQL may be doing a table scan. This is an instance where test/development scenarios don't always work quite as well as real data. However, 6-12 seconds for a 5000 row table does seem slow ... In our test database it's probably less than 50 rows that have been updated in the last 48 hours. Can you post the output of EXPLAIN query ? That will help us see how MySQL is planning to run your query, and may also reveal a little bit about your data. 1 PRIMARY mrldisc index updated 4 4888Using where 2 DEPENDENT SUBQUERY mrldisc index_subquery thread_id,updated thread_id 5 func 8 Using index; Using where Thanks. Al Thanks, Dan On 10/31/06, Albert Padley [EMAIL PROTECTED] wrote: I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sort Problem
I have the following query that has worked fine for displaying standings for a soccer league. SELECT * FROM standings WHERE division = 'BU10' AND pool = '1' ORDER BY tpts DESC, spts DESC, w DESC, ga ASC, team_number ASC As I said, works fine. Now, however, the league wants a slightly different sort order. They only want to sort on the spts column if the difference between 2 teams is greater than 9 in the spts column. All other sort criteria remain the same. So, the ORDER BY would be tpts DESC, spts DESC (but only if the difference is 9), w DESC, ga ASC, team_number ASC. If it is possible to form such a query, I need help in what is would look like. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
On Sep 15, 2006, at 12:56 PM, Chris W wrote: Albert Padley wrote: I have the following query that has worked fine for displaying standings for a soccer league. SELECT * FROM standings WHERE division = 'BU10' AND pool = '1' ORDER BY tpts DESC, spts DESC, w DESC, ga ASC, team_number ASC As I said, works fine. Now, however, the league wants a slightly different sort order. They only want to sort on the spts column if the difference between 2 teams is greater than 9 in the spts column. All other sort criteria remain the same. So, the ORDER BY would be tpts DESC, spts DESC (but only if the difference is 9), w DESC, ga ASC, team_number ASC. if spts is an integer so that 9 is the same as saying = 10 then you could sort by a rounded version of spts like this... ORDER BY tpts DESC, ROUND(spts,-1) DESC, w DESC, ga ASC, team_number ASC by putting the -1 there it rounds to the nearest 10's before doing the sort. Of course the output is not rounded. The following are a few examples of the output of the round statement. ROUND(23.632, 2) = 23.63 ROUND(23.632, 1) = 23.6 ROUND(23.632, 0) = 24 ROUND(23.632, -1) = 20 -- Chris W KE5GIX Yes, that seems to work well. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help please, help please
I am not able to install Mysql on my 2003 server at home. error 1045!! I get an accesd denied for user [EMAIL PROTECTED] my firewall is off and I know it is not the port becuase when I configure it as 'anonymous it works like a charm. Please help me - Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.
Re: Problem With Join Syntax
Keith, I agree that would be a good option to change the table. Unfortunately, that's not an option at this point. I don't control the schema. Thanks for the suggestion anyway. Albert Padley On Jun 14, 2006, at 3:59 PM, Keith Roberts wrote: Hi Chris. I cannot see how it can be done with the current table schema. Maybe you need to redeclare your table so the values in the value column are more distinct? What is value supposed to contain anyway? First name, last name and email address? What about a structure like: id | userid | ipf_1 | ipf_2 | ipf_3 1 2 JohnSmith email_addy Which will allow you to retrieve all the values you want from the table as one row without having to repeat the userid column? HTH Keith Roberts In theory, theory and practice are the same; in practice they are not. On Wed, 14 Jun 2006, Chris White wrote: To: mysql@lists.mysql.com From: Chris White [EMAIL PROTECTED] Subject: Re: Problem With Join Syntax On Wednesday 14 June 2006 10:55 am, Albert Padley wrote: A typical set of data looks like this: id | inputfieldid | userid | value 1 1 2 John 2 2 2 Smith 3 3 2 [EMAIL PROTECTED] I am trying to come up with a query to return all the `values` of a single userid in a single row. I've checked my books, the manual and tried every type of join I can think of without success. I'd appreciate some direction. This sounds like somewhat of a strange requirement. Why do they need to be in a single row? There MIGHT be a way to do it with stored procedures, I'm just not sure how.. Thanks. Albert Padley -- Chris White PHP Programmer/DB Fighter Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem With Join Syntax
I have the following table schema in MySQL 4.1.18 which I didn't create, but have to work with. CREATE TABLE `phplog_userinput` ( `id` int(11) NOT NULL auto_increment, `inputfieldid` int(11) NOT NULL default '0', `userid` int(11) NOT NULL default '0', `value` varchar(150) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 A typical set of data looks like this: id | inputfieldid | userid | value 1 1 2 John 2 2 2 Smith 3 3 2 [EMAIL PROTECTED] I am trying to come up with a query to return all the `values` of a single userid in a single row. I've checked my books, the manual and tried every type of join I can think of without success. I'd appreciate some direction. Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With Join Syntax
Dan, Thanks. I'll take a further look at GROUP_CONCAT. Albert On Jun 14, 2006, at 1:16 PM, Dan Buettner wrote: Albert, MySQL's GROUP_CONCAT function might work for you: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html In your case something like this: SELECT userid, GROUP_CONCAT(value) GROUP BY userid HTH, Dan Albert Padley wrote: I have the following table schema in MySQL 4.1.18 which I didn't create, but have to work with. CREATE TABLE `phplog_userinput` ( `id` int(11) NOT NULL auto_increment, `inputfieldid` int(11) NOT NULL default '0', `userid` int(11) NOT NULL default '0', `value` varchar(150) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 A typical set of data looks like this: id | inputfieldid | userid | value 1 1 2 John 2 2 2 Smith 3 3 2 [EMAIL PROTECTED] I am trying to come up with a query to return all the `values` of a single userid in a single row. I've checked my books, the manual and tried every type of join I can think of without success. I'd appreciate some direction. Thanks. Albert Padley --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Totals Across Multiple Records
Sol and Peter, Thanks for your feedback. Both of your suggestions got me going in the right direction and I was able to solve the problem using temporary tables and left joins. Thanks again. Albert Padley On Jul 23, 2005, at 11:00 AM, sol beach wrote: create table count_temp1 select id, count(id) count_id from table ss; create table count_temp 2 select id, count(tt) from ss where id = sd1 or id = sd2; should get you closer. yes? On 7/22/05, Albert Padley [EMAIL PROTECTED] wrote: I would be grateful if those of you around this weekend could help me figure out if what I'm after is possible. I've already spent hours with the manual, the archives and my books. I've looked at JOINS and TEMP TABLES but still can't come up with a solution. THE ENVIRONMENT: MySQL Version 4.0.24 (so subselects are not available) THE TABLE CREATE TABLE `ss` ( `tt` INT NOT NULL AUTO_INCREMENT , `zz` INT( 3 ) NOT NULL , `sd1` INT( 3 ) NOT NULL , `sd2` INT( 3 ) NOT NULL , PRIMARY KEY ( `id` ) ); THE ISSUE: 1. xx, sd1 and sd2 all contain id numbers. These numbers are unique within each record. 2. I need to scan the table and create a table row for each id number in zz that contains: a. how many times each id appears in zz (This is easy using COUNT) b. how many times each id appears in sd1 plus sd2. (If a total is not possible, then a separate listing for sd1 and sd2 would suffice (just like we have for zz) c. The final table should list each id number with the following columns: id number total times id number appears in zz column total times id number appears in sd1 sd2 combined I sure hope this makes sense. Oh, one more thing. I can't change the table structure because I have simplified it here for finding a solution. The above columns are part of a much larger table that is currently in use for other purposes. Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Totals Across Multiple Records
I would be grateful if those of you around this weekend could help me figure out if what I'm after is possible. I've already spent hours with the manual, the archives and my books. I've looked at JOINS and TEMP TABLES but still can't come up with a solution. THE ENVIRONMENT: MySQL Version 4.0.24 (so subselects are not available) THE TABLE CREATE TABLE `ss` ( `tt` INT NOT NULL AUTO_INCREMENT , `zz` INT( 3 ) NOT NULL , `sd1` INT( 3 ) NOT NULL , `sd2` INT( 3 ) NOT NULL , PRIMARY KEY ( `id` ) ); THE ISSUE: 1. xx, sd1 and sd2 all contain id numbers. These numbers are unique within each record. 2. I need to scan the table and create a table row for each id number in zz that contains: a. how many times each id appears in zz (This is easy using COUNT) b. how many times each id appears in sd1 plus sd2. (If a total is not possible, then a separate listing for sd1 and sd2 would suffice (just like we have for zz) c. The final table should list each id number with the following columns: id number total times id number appears in zz column total times id number appears in sd1 sd2 combined I sure hope this makes sense. Oh, one more thing. I can't change the table structure because I have simplified it here for finding a solution. The above columns are part of a much larger table that is currently in use for other purposes. Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Generating Soccer Standings
First, I have spent hours searching the web and the list archives and can't find anything helpful. Second, I'm using MySql 4.0.24 Third, I'm still a novice at query formulation, so be kind. The Problem--- I'm working with a new youth soccer league. I need to generate weekly standings that includes total wins, losses and draws for each team. Additionally, I also need to calculate the standings. In most soccer leagues, including this one, a winning team receives 3 points, a tie gets 1 point and a loss get 0 points. The pertinent table structure follows: CREATE TABLE `games` ( `id` int(11) NOT NULL auto_increment, `hcoach` varchar(20) NOT NULL default '', `vcoach` varchar(20) NOT NULL default '', `hscore` tinyint(4) default NULL, `vscore` tinyint(4) default NULL, `hpts` tinyint(4) default NULL, `vpts` tinyint(4) default NULL, PRIMARY KEY (`id`), KEY `hscore` (`hscore`,`vscore`,`hpts`,`vpts`) ) TYPE=MyISAM AUTO_INCREMENT=4162 ; I located the following query. Unfortunately, it uses subselects which aren't available in 4.0.24 SELECT team, SUM( wins ) , SUM( losses ) FROM ( ( SELECT hcoach AS team, SUM( IF ( hscore vscore, 1, 0 ) ) AS wins, SUM( IF ( vscore hscore, 1, 0 ) ) AS losses FROM games GROUP BY team ) UNION ( SELECT vcoach AS team, SUM( IF ( vscore hscore, 1, 0 ) ) AS wins, SUM( IF ( hscore vscore, 1, 0 ) ) AS losses FROM games GROUP BY team ) GROUP BY team ORDER BY losses I would think this would be a fairly common issue. However, I don't know enough to even know where to start looking. How do I accomplish this? Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Generating Soccer Standings
Shawn, Thank you. I've been working with what you provided learning as I figure out the why of each step. One thing I don't understand is the syntax of these 2 lines: , sum(if(`standingpts`=3,1,0)) as wins , sum(if(`standingpts`=3 AND `home_vis` = 'H',1,0) as wins_at_home Specifically the =3,1,0 in the first line and the = 'H',1.0 in the second line. Thanks. Albert Padley On Jul 11, 2005, at 1:13 PM, [EMAIL PROTECTED] wrote: SELECT `coach` , sum(`standingpts`) as standings , count(`game_id`) as games , sum(if(`standingpts`=3,1,0)) as wins , sum(if(`standingpts`=3 AND `home_vis` = 'H',1,0) as wins_at_home , sum(`points`) as total_points , avg(`points`) as avg_points FROM `gamestats` GROUP BY `coach` ORDER BY `standings` DESC; It became simple because we normalized the data. Here is how to get your original view of the data: SELECT game_id as id , max(if(home_vis='H',coach,null)) as hcoach , max(if(home_vis='V',coadh,null)) as vcoach , max(if(home_vis='H',points,null)) as hscore , max(if(home_vis='V',points,null)) as vscore , max(if(home_vis='H',standingpts,null)) as hpts , max(if(home_vis='V',standingpts,null)) as hpts FROM gamestats GROUP BY game_id; Can you see the patterns? We are creating what is called pivot tables or crosstab queries (depending on who you ask). It's the flexible way of computing the statistics you want to keep. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Generating Soccer Standings
OK. I'm responding to my own post since I figured out the syntax. This allowed me to figure out how to compute the ties as well as the wins. How to I compute the losses. I can't use the same technique because I'd be looking for '0' in the standingpts column and that column defaults to '0'. Thus it would also be counting the games that have yet to be played. It would seem to involve simple arithmetic of games - wins - ties = losses, but I can't seem to get the syntax correct. One final question. We get the total goals scored in the season by sum (`points`) as total_points. How would I find the total goals scored against a team in the context of the query below? Thanks. Albert Padley On Jul 11, 2005, at 1:13 PM, [EMAIL PROTECTED] wrote: SELECT `coach` , sum(`standingpts`) as standings , count(`game_id`) as games , sum(if(`standingpts`=3,1,0)) as wins , sum(if(`standingpts`=3 AND `home_vis` = 'H',1,0) as wins_at_home , sum(`points`) as total_points , avg(`points`) as avg_points FROM `gamestats` GROUP BY `coach` ORDER BY `standings` DESC; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Problem building 4.1.12 on HP-UX 10.20
I'm trying to build mysql-4.1.12 on HP-UX 10.20 with gcc-3.4.3. HP-UX
10.20 has CMA threads.
I have the following build failure:
gmake[4]: Entering directory `/opt/build/mysql-4.1.12/innobase/srv'
gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include -I./../../include
-I../../include -I/opt/TWWfsw/readline50/include
-I/opt/TWWfsw/zlib11/include -I/opt/TWWfsw/ncurses54/include
-I/opt/TWWfsw/ncurses54/include/ncurses -D_REENTRANT -DDBUG_OFF
-DDBUG_OFF -O2 -march=1.1 -D_REENTRANT -DHAVE_BROKEN_SNPRINTF
-DSIGNALS_DONT_BREAK_READ -DDO_NOT_REMOVE_THREAD_WRAPPERS -DHPUX10
-DSIGNAL_WITH_VIO_CLOSE -DHAVE_BROKEN_PTHREAD_COND_TIMEDWAIT
-DHAVE_POSIX1003_4a_MUTEX -DDEBUG_OFF -DUNIV_MUST_NOT_INLINE
-DUNIV_HPUX -DUNIV_HPUX10 -c srv0srv.c
srv0srv.c: In function `srv_suspend_thread':
srv0srv.c:675: error: aggregate value used where an integer was expected
srv0srv.c: In function `srv_release_threads':
srv0srv.c:739: error: aggregate value used where an integer was expected
gmake[4]: *** [srv0srv.o] Error 1
gmake[4]: Leaving directory `/opt/build/mysql-4.1.12/innobase/srv'
The errant code:
if (srv_print_thread_releases) {
fprintf(stderr,
Suspending thread %lu to slot %lu meter %lu\n,
(ulong) os_thread_get_curr_id(), (ulong) slot_no,
(ulong) srv_meter[SRV_RECOVERY]);
}
The prototype for os_thread_get_curr_id() is:
os_thread_id_t
os_thread_get_curr_id(void);
os_thread_id_t is defined as:
typedef struct CMA_T_HANDLE {
cma_t_address field1;
short int field2;
short int field3;
} cma_t_handle;
typedef cma_t_handle cma_t_thread;
typedef cma_t_thread pthread_t;
typedef pthread_t os_thread_t;
typedef os_thread_t os_thread_id_t;
So, gcc is complaining about the cast from cma_t_handle to ulong. What
should I do?
--
albert chin ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem building 4.1.12 on HP-UX 10.20
On Mon, Jun 06, 2005 at 04:19:16PM -0500, Dan Nelson wrote:
In the last episode (Jun 06), Albert Chin said:
I'm trying to build mysql-4.1.12 on HP-UX 10.20 with gcc-3.4.3. HP-UX
10.20 has CMA threads.
I have the following build failure:
gmake[4]: Entering directory `/opt/build/mysql-4.1.12/innobase/srv'
gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include -I./../../include
[...]
-DUNIV_HPUX -DUNIV_HPUX10 -c srv0srv.c
srv0srv.c: In function `srv_suspend_thread':
srv0srv.c:675: error: aggregate value used where an integer was expected
srv0srv.c: In function `srv_release_threads':
srv0srv.c:739: error: aggregate value used where an integer was expected
gmake[4]: *** [srv0srv.o] Error 1
gmake[4]: Leaving directory `/opt/build/mysql-4.1.12/innobase/srv'
The errant code:
if (srv_print_thread_releases) {
fprintf(stderr,
Suspending thread %lu to slot %lu meter %lu\n,
(ulong) os_thread_get_curr_id(), (ulong) slot_no,
(ulong) srv_meter[SRV_RECOVERY]);
}
Shouldn't os_thread_get_curr_id() be
os_thread_pf(os_thread_get_curr_id()) ? Try that, or just remove the
fprintfs; they are debugging code that assumes that a pthread_t is a
printable type (there is no such guarantee).
Thanks. Patch below.
--
albert chin ([EMAIL PROTECTED])
-- snip snip
--- innobase/srv/srv0srv.c.orig Mon Jun 6 17:07:35 2005
+++ innobase/srv/srv0srv.c Mon Jun 6 17:09:10 2005
@@ -672,8 +672,8 @@
if (srv_print_thread_releases) {
fprintf(stderr,
Suspending thread %lu to slot %lu meter %lu\n,
- (ulong) os_thread_get_curr_id(), (ulong) slot_no,
- (ulong) srv_meter[SRV_RECOVERY]);
+ (ulong) os_thread_pf(os_thread_get_curr_id()),
+ (ulong) slot_no, (ulong) srv_meter[SRV_RECOVERY]);
}
slot = srv_table_get_nth_slot(slot_no);
@@ -735,7 +735,8 @@
if (srv_print_thread_releases) {
fprintf(stderr,
Releasing thread %lu type %lu from slot %lu meter %lu\n,
- (ulong) slot-id, (ulong) type, (ulong) i,
+ (ulong) os_thread_pf(slot-id),
+ (ulong) type, (ulong) i,
(ulong) srv_meter[SRV_RECOVERY]);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN Problem
I have the following 2 tables: CREATE TABLE `division_info` ( `id` int(11) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `spots` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `division` (`division`), KEY `spots` (`spots`) ) TYPE=MyISAM CREATE TABLE `team_info` ( `id` int(14) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `application` varchar(9) NOT NULL default 'No', PRIMARY KEY (`id`), KEY `division` (`division`), ) TYPE=MyISAM I'm running the following query: SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division This query runs fine. However, it only returns divisions where there is at least 1 ACCEPTED team. I also need to show divisions where there are spots but not teams have yet been ACCEPTED. A little direction would be appreciated. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN Problem
On Feb 17, 2005, at 10:34 AM, [EMAIL PROTECTED] wrote: Albert Padley [EMAIL PROTECTED] wrote on 02/17/2005 12:08:31 PM: I have the following 2 tables: CREATE TABLE `division_info` ( `id` int(11) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `spots` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `division` (`division`), KEY `spots` (`spots`) ) TYPE=MyISAM CREATE TABLE `team_info` ( `id` int(14) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `application` varchar(9) NOT NULL default 'No', PRIMARY KEY (`id`), KEY `division` (`division`), ) TYPE=MyISAM I'm running the following query: SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division This query runs fine. However, it only returns divisions where there is at least 1 ACCEPTED team. I also need to show divisions where there are spots but not teams have yet been ACCEPTED. A little direction would be appreciated. Thanks. Al Padley You are very, very close. You used the LEFT JOIN (correct choice) but you eliminated all of the rows from your division table without any accepted teams when you said WHERE application='ACCEPTED'. That's why you aren't getting a good count across all of your divisions. What I think you were trying to do was to tell how many teams have accepted within a division, across all divisions. That means you want to count 'ACCEPTED' teams but not teams that do not exist or teams that have some other application status, right? I have reworked your query a bit and I think I answered the question you had and I also tried to demonstrate how to get at some other information at the same time. SELECT d.division AS 'division' , d.spots as 'spots' , COUNT(t.division) AS 'total_team_count' , SUM(IF(t.application = 'ACCEPTED',1,0)) as 'teams_accepted' , SUM(IF(t.application 'ACCEPTED',1,0)) as 'teams_not_accepted' FROM division_info d LEFT JOIN team_info t ON d.division = t.division GROUP BY d.division, d.spots Using the aggregating functions like COUNT() and SUM() in this way, we are building a crosstab query (also called a pivot table). There are many other articles in this thread's archive that can help you understand how to build those types of queries with MySQL. By eliminating your WHERE clause and moving your condition into a SUM(IF()), we allowed all of the rows from the RIGHT side of the LEFT JOIN (even those with all null values) to appear in the results and thanks to the IF() we only count (by adding up the 1's) those rows with the values we want to find. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn, Thanks. This was just right. Once again, you have gone beyond the initial question and not only provided the correct answer, but an explanation that helps me better understand the why behind the query. Much appreciated. Thanks again. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
Nothing? Not even a You're out of luck? Thanks. Albert On Oct 21, 2004, at 9:48 PM, Albert Padley wrote: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- 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: Sort Problem SOLVED
Jeff and Shawn, Thanks for coming up with similar solutions. Jeff, I have used yours because it was more complete. I wasn't aware of the Case statement in mysql. I guess I still have a lot to learn. Thanks again. Albert On Oct 22, 2004, at 10:10 AM, Jeff Burgoon wrote: This will solve your problem and remove the need for the PHP correction. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN game_pts + 15 ELSE game_pts + ref_pts + adjust_ref_pts END AS total_pts, CASE WHEN ref_pts + adjust_ref_pts 15 THEN 15 ELSE ref_pts + adjust_ref_pts END AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC Albert Padley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Nothing? Not even a You're out of luck? Thanks. Albert On Oct 21, 2004, at 9:48 PM, Albert Padley wrote: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- 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: Sort Problem
Juan, Good question. ref_pts are given for completing referee assignments and are tracked throughout the season and can exceed 15 for tracking purposes. However, only a max of 15 total_ref_pts are used in determining the standings. adust_ref_pts (up or down) are given by the Referee Assignor for situations not covered in the normal automatic system. Again, they want to track this total throughout the season, but only use a max of 15 total_ref_pts for computing the standings. In your example, they want to know the true values of 10, 9 and 7, but total_pts would only add up to 25 for computing the standings (10 game_pts and 15 total_ref_pts). Make sense? Albert On Oct 22, 2004, at 11:46 AM, Juan M. Quiroz wrote: I have some questions. Is the information on those fields used as their true value? Meaning are you always have to manipulate the data, then why not enter the correct information instead of manipulating. If this is a special case I can see is necessary. so if the values for game_pts, ref_pts, adjust_ref_pts are 10, 9, 7 can it take its true add of 26 or should it be 25? Then why not have the right values to begin with. Just curious. Juan Albert Padley on 10/21/04 at 9:48 PM -0600 wrote about: Sort Problem I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- 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]
Sort Problem
I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus adjust_ref_pts cannot exceed 15. Here is the current query which obviously allows total_ref_pts to exceed 15 and for total_pts to possibly be incorrect. These potential errors are handled by PHP after the query is run and results in correct numbers and totals being displayed. SELECT lname, teamno, game_pts, sport_pts, ref_pts, adjust_ref_pts, (game_pts + ref_pts + adjust_ref_pts) AS total_pts, (ref_pts + adjust_ref_pts) AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC The problem is that the sort order will occasionally be incorrect because of total_pts being incorrect. Can the query be fixed to handle this? If so, how? If not, that is important to know also. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
php version
What is the best version of PHP to use with MySQL on Win32 to interact with the MySQL database in web-enabled forms with submit button? Thank you for your help. Albert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- 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: insert ... select .. order by, problem
Stefan, Indeed, and my mistake (semantics). I meant what you explained. It is clear to me that the order in the table remains in the manner the data were entered, and that cannot be changed, unless a record is deleted and then re-entered, which would place it elsewhere (at the end). This does not really have any benefits IMHO. And yes, I understand that it is the output that is sorted based on the query. Thanks for clarifying this, and as I read my question, I should have seen the difference myself. Mea culpa! : =) I am quite familiar with SQL Server 2000, but need to use MySQL for a project for the University I am at (Devry Alpharetta, Atlanta), to capture the input from a student survey of the classes and the Faculty members. There are several fields: semester (char), course(char), courseID (int), Faculty (varchar[30] - if that is acceptable in that format - and the answers to 18 questions, all alpha characters (char) or numeric char (int), and one Boolean (yes/no or 1,0). I need to figure out how to best structure this, e.g. create tables on the fly (if that is possible using ASP/ADO and SQL with ODBC connector), or create tables with many to many relationships and store the data for each course survey in a separate table. The tables with many to many relationships would hold all the courses, courseID's, and Faculty members, and the answers to the survey would create links between those and the results from the surveys. A typical class unique identification would look like this: sum03_FBaah_CIS_349 The cols would be 1 through 18 + a calculation col for the average of questions 1 to 18 and a col for the average of all answers to question 1, question 2, etc ... Mind you I may export the answers to an excel spreadsheet and do the calculations there rather than in the DB itself. Anyway this is a long answer to your response but I wanted those who read this to get an idea of what I am working with. Any suggestions are welcomed. Albert - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:10 AM Subject: Re: insert ... select .. order by, problem Hi Albert, you are not misunderstanding me :-) Tables can indeed not be sorted, it's output which gets sorted. The difference is not academic, but important: It's not the table which gets an order, but the output. Take a command like: insert into x ... select from y ... order by z. Here the output of select gets sorted and inserted into the table x. In this table, there is not order, so if you do then a select from x, the order is arbitrary again and you need to do select from x order by z. And this means you could have dropped the order by in insert totally. Hope it became clear. Stefan Am Tuesday 02 September 2003 13:20 schrieb Albert: Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL
Re: insert ... select .. order by, problem
Roger, Thanks for the additional clarification Albert Atlanta - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Albert [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:00 AM Subject: Re: insert ... select .. order by, problem * Albert Stefan, I'm Roger, but I reply anyway. :) Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. In relational database theory the order of rows within the table is undefined, i.e. it is up to the server, and the server can re-organize a table at any time. If you want an ordered result, you have to use ORDER BY in your SELECT statement. However, the MySQL server has some features that can be used to achieve exactly what you ask. It is possible to sort a table physically, in order to do faster reads later. The order is however destroyed if you do additional inserts. See the ORDER BY option of the ALTER TABLE statement: ORDER BY allows you to create the new table with the rows in a specific order. Note that the table will not remain in this order after inserts and deletes. In some cases, it may make sorting easier for MySQL if the table is in order by the column that you wish to order it by later. This option is mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you may be able to get higher performance. URL: http://www.mysql.com/doc/en/ALTER_TABLE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show database problem
When I enter : mysql\binSHOW GRANTS FOR uma; I get a response that show is not a command etc.. This is on Win32 version 4.x The same happens when I try it from mysql and also after I run mysqld Any ideas what is wrong here? Thanks Albert - Original Message - From: Joris Beckers [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 11:19 AM Subject: Show database problem I've got a user, admFrederic He got the grants shown below. Those are correct and I configured them that way. mysql SHOW GRANTS FOR admFrederic; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'admFrederic'@'%' IDENTIFIED BY PASSWORD '6ddf5e1a1cc75e8a' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `cehdb`.* TO 'admFrederic'@'%' | +--- + But, when I log in using that username. That user can access the main database... mysql show databases; +--+ | Database | +--+ | cehdb| | mysql| +--+ Does anyone know how to fix this? Only cehdb should be accessable. Also, the user can run SQL commands on the mysql database. GRANT commands etc. are not possible. This is the version info. mysql status; -- mysql Ver 11.18 Distrib 3.23.53, for Win95/Win98 (i32) Connection id: 351 Current database: Current user: [EMAIL PROTECTED] Server version: 4.1.0-alpha-max-nt Protocol version: 10 Connection: nemesis via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: Uptime: 1 hour 6 min 14 sec Thanks, Joris -- 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: Abt Mysqldump
Matthew (UK), I have two questions regarding this: 1. are you using the tick that is under the ~ sign on US keyboards, or the ' which is under the quotes on US keyboards? I tried both and get an error executing the following query in mysql client gui screen GRANT ALL mysql.* TO USER 'albert' mysql client tells me I have an SQL error I tried the other ` (the one under the ~) and that did not work either. 2. I have tried to enter passwords for users including root, yet mysql does not take them in, even though they are listed in my.ini (in clear which is odd) When I use the password in conjunction with the user I get an error that says access denied with password YES or sometimes with password NO. Any clues? Thanks for the help Albert Atlanta - Original Message - From: Matthew Smith [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:29 AM Subject: RE: Abt Mysqldump Hi The only problem I get with mysqldump is that if I have used a reserved word as a column name, then the create starement fails. (eg CREATE TABLE fred ( KEY int(10) not null default '0' ); will fail (but as produced by mysqldump) However, if you edit the file and put ` characters either side of the column name (ie KEY above) then it then works. You can sort this in the first instance if you use the '--quote-names' or '-Q' option to mysqldump (OK, so I've just read the man page for the first time as well) Regards Matthew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 September 2003 18:39 To: [EMAIL PROTECTED] Subject: Re: Abt Mysqldump use mysql database-name backupfilename I think , the backup file name should have .sql extension , like backup.sql Hoeven, Maarten van der wrote: What are the errors? For example, is the error like unable to create the tables, because the tables still exist? See the dumpfile if tables are created (by default). If so, delete the tables first, and redump back the dumpfile. -Original Message- From: Uma Shankari T. [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 1:33 PM To: [EMAIL PROTECTED] Subject: Abt Mysqldump Hello, I have dumped the database contents as one txt file by using this command mysqldump databasename -uusername -ppasswd textfilename. but while redumping back to mysql it is giving some errors in the textfile..is there any possibility to redump the contents without any error.. Regards, Uma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Zie ook/see also: http://www.knmi.nl/maildisclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] . -- Vidhya CS . -- 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]
Sorting and use of tables
Stefan, Indeed, and my mistake (semantics). I meant what you explained. It is clear to me that the order in the table remains in the manner the data were entered, and that cannot be changed, unless a record is deleted and then re-entered, which would place it elsewhere (at the end). This does not really have any benefits IMHO. And yes, I understand that it is the output that is sorted based on the query. Thanks for clarifying this, and as I read my question, I should have seen the difference myself. Mea culpa! : =) I am quite familiar with SQL Server 2000, but need to use MySQL for a project for the University I am at (Devry Alpharetta, Atlanta), to capture the input from a student survey of the classes and the Faculty members. There are several fields: semester (char), course(char), courseID (int), Faculty (varchar[30] - if that is acceptable in that format - and the answers to 18 questions, all alpha characters (char) or numeric char (int), and one Boolean (yes/no or 1,0). I need to figure out how to best structure this, e.g. create tables on the fly (if that is possible using ASP/ADO and SQL with ODBC connector), or create tables with many to many relationships and store the data for each course survey in a separate table. The tables with many to many relationships would hold all the courses, courseID's, and Faculty members, and the answers to the survey would create links between those and the results from the surveys. A typical class unique identification would look like this: sum03_FBaah_CIS_349 The cols would be 1 through 18 + a calculation col for the average of questions 1 to 18 and a col for the average of all answers to question 1, question 2, etc ... Mind you I may export the answers to an excel spreadsheet and do the calculations there rather than in the DB itself. Anyway this is a long answer to your response but I wanted those who read this to get an idea of what I am working with. Any suggestions are welcomed. Albert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show database problem
This is the error I get and yes uma is a user and listed in my.ini file actually C:\mysql\binSHOW GRANTS FOR uma; 'SHOW' is not recognized as an internal or external command, operable program or batch file. C:\mysql\bin Albert - Original Message - From: Fortuno, Adam [EMAIL PROTECTED] To: 'Albert' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:38 AM Subject: RE: Show database problem Albert, This may sound minuscule. You're certain that 'uma' is a user account and not a database or table? Normally, you use SHOW GRANTS FOR user_account. See example: mysql SHOW GRANTS FOR test_usr; +--+ | Grants for [EMAIL PROTECTED] +--+ | GRANT USAGE ON *.* TO 'test_usr'@'% | GRANT ALL PRIVILEGES ON 'tempdb'.* TO 'test_usr'@'%' | GRANT ALL PRIVILEGES ON 'test'.* TO 'test_usr'@'%' +--+ However, if you attempt to show grants on an object with SHOW GRANTS FOR object_name you get the following error. See example: mysql SHOW GRANTS FOR tempdb; ERROR 1141: There is no such grant defined for user 'tempdb' on host '%' Albert, one suggestion. Next time provide the exact error message. It makes it easier to assist. Regards, Adam -Original Message- From: Albert [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:46 AM To: [EMAIL PROTECTED] Subject: Re: Show database problem When I enter : mysql\binSHOW GRANTS FOR uma; I get a response that show is not a command etc.. This is on Win32 version 4.x The same happens when I try it from mysql and also after I run mysqld Any ideas what is wrong here? Thanks Albert - Original Message - From: Joris Beckers [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 11:19 AM Subject: Show database problem I've got a user, admFrederic He got the grants shown below. Those are correct and I configured them that way. mysql SHOW GRANTS FOR admFrederic; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'admFrederic'@'%' IDENTIFIED BY PASSWORD '6ddf5e1a1cc75e8a' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `cehdb`.* TO 'admFrederic'@'%' | +--- + But, when I log in using that username. That user can access the main database... mysql show databases; +--+ | Database | +--+ | cehdb| | mysql| +--+ Does anyone know how to fix this? Only cehdb should be accessable. Also, the user can run SQL commands on the mysql database. GRANT commands etc. are not possible. This is the version info. mysql status; -- mysql Ver 11.18 Distrib 3.23.53, for Win95/Win98 (i32) Connection id: 351 Current database: Current user: [EMAIL PROTECTED] Server version: 4.1.0-alpha-max-nt Protocol version: 10 Connection: nemesis via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: Uptime: 1 hour 6 min 14 sec Thanks, Joris -- 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: Show database problem
Stefan, Indeed, and my mistake (semantics). I meant what you explained. It is clear to me that the order in the table remains in the manner the data were entered, and that cannot be changed, unless a record is deleted and then re-entered, which would place it elsewhere (at the end). This does not really have any benefits IMHO. And yes, I understand that it is the output that is sorted based on the query. Thanks for clarifying this, and as I read my question, I should have seen the difference myself. Mea culpa! : =) I am quite familiar with SQL Server 2000, but need to use MySQL for a project for the University I am at (Devry Alpharetta, Atlanta), to capture the input from a student survey of the classes and the Faculty members. There are several fields: semester (char), course(char), courseID (int), Faculty (varchar[30] - if that is acceptable in that format - and the answers to 18 questions, all alpha characters (char) or numeric char (int), and one Boolean (yes/no or 1,0). I need to figure out how to best structure this, e.g. create tables on the fly (if that is possible using ASP/ADO and SQL with ODBC connector), or create tables with many to many relationships and store the data for each course survey in a separate table. The tables with many to many relationships would hold all the courses, courseID's, and Faculty members, and the answers to the survey would create links between those and the results from the surveys. A typical class unique identification would look like this: sum03_FBaah_CIS_349 The cols would be 1 through 18 + a calculation col for the average of questions 1 to 18 and a col for the average of all answers to question 1, question 2, etc ... Mind you I may export the answers to an excel spreadsheet and do the calculations there rather than in the DB itself. Anyway this is a long answer to your response but I wanted those who read this to get an idea of what I am working with. Any suggestions are welcomed. Albert - Original Message - From: Fortuno, Adam [EMAIL PROTECTED] To: 'Albert' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:38 AM Subject: RE: Show database problem .com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show database problem
OK I think I got it, in Win32 it has to be done from the mysql client screen and not from the command prompt. Either that or use another GUI client as the front end for MySQL I guess and I have had some suggestions on that one. I will try doing so. Thanks for the help Albert - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Albert [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:36 AM Subject: Re: Show database problem * Albert When I enter : mysql\binSHOW GRANTS FOR uma; I get a response that show is not a command etc.. This is on Win32 version 4.x The same happens when I try it from mysql and also after I run mysqld Any ideas what is wrong here? When you say you try it from mysql do you mean standing in the mysql directory in the command promt? In that case, that would be the explanation. When you see mysql on this list, it means the mysql SQL shell, AKA the mysql command line, AKA the mysql monitor, AKA the standard client. It is an executable in the mysql/bin directory, named mysql.exe on win32. Execute this program, and then enter your SQL commands. URL: http://www.mysql.com/doc/en/mysql.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: No valid command found
Does anyone know what prompt the message below to be sent by the list? I have now been getting a few of these and cannot figure out why. Thanks for any input Uma From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 11:39 AM Subject: No valid command found Your message does not contain a valid command for this mail server to process. No action has been taken. Message-Id: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Time-Stamp: Tue, 2 Sep 2003 11:03:33 -0400 : Message contains [1] file attachments -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: repeated Show Status query
You should be able to change that ins sqlsysadmin (the traffic light) under the section options if I remember correctly and change default 10 to whatever you wish Albert - Original Message - From: Pam Vermeer [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: Pam [EMAIL PROTECTED] Sent: Monday, September 01, 2003 7:57 AM Subject: repeated Show Status query I recently upgraded to MySQL 4.0.12 and I logged all queries for a little while, just to see what was happening. When I looked at the log, I saw that there was a Show Status query happening every 10 seconds. This happens both on my server which runs WIndows 2000 Server and on my laptop which runs Windows 2000 Professional. Is this normal? Pam -- 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: CPU Usage and MySQL...
Can this tool be used on Windows and if so what version do I need to DL and does it need anything else besides the software (e.g. Perl - which I see listed for the nix versions. Thanks Albert - Original Message - From: K Old [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, August 31, 2003 12:02 AM Subject: Re: CPU Usage and MySQL... On Sat, 2003-08-30 at 23:18, [EMAIL PROTECTED] wrote: Hello, I have a strange problem with my mysql server. The mysql process seems to be using between 60 and 70 percent of the CPU at all times. Even when it is seemingly idle (not active queries). I'm not sure what to look for to see what it is doing other than truss'ing the process (I'm running it on Sparc Solaris 9 Ultra III procs). I have included the status and variables out put just incase something obvious pop's up there. Thanks alot! Hello, I ran across this tool the other day and it is awesome. Basically it is like the top utility for *nix, but it's for mysql. It basically gives you a live look into the database and what queries it's processing, etc in real time. Give it a try http://jeremy.zawodny.com/mysql/mytop/ Kevin -- K Old [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: uniques, keys, etc?
I have a similar problem but not with the clicking but with filling out a survey that is captured in mysql, twice. Any help would be appreciated Albert - Original Message - From: Joseph [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 31, 2003 2:07 AM Subject: uniques, keys, etc? I have a table I use to keep track of referred users on my top list. Below you will find the four columns in my table. What I need to do is keep entries (rows) unique based on the linkid, ipaddress, and the clicktype so that a user is not counted twice if they enter my site. Can someone tell me what I need to do? historyid int(11) primary key linkid int(11) ipaddress varchar(15) clicktype char(3) __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.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]
compile mysql meet problem
Dear, Now i'm trying to install MySQL 4.0.14 through binary source install. I'm using Redhat 7.2 and 8.0 I'm trying like this.. ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-charset=big5 make so far there is no problems. but when i try like 'make install',i got this error message and Installation is stopped. ... /usr/bin/install -c copy-db /usr/local/mysql/sql-bench/copy-db /usr/bin/install: `copy-db' and `/usr/local/mysql/sql-bench/copy-db' are the same file make[2]: *** [install-benchSCRIPTS] error1 make[2]: exit `/usr/local/mysql/sql-bench' directory make[1]: *** [install-am] error 2 make[1]: exit `/usr/local/mysql/sql-bench' directory make: *** [install-recursive] error 1 [EMAIL PROTECTED] mysql]# I have found any topic about this error in Google and Yahoo, they display many this message but no any documents, reply or helpdest to explain it. Therefore, I would like you to suggestion some guide or solutions for me. best regards, Albert Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compile mysql meet problem
Dear, Now i'm trying to install MySQL 4.0.14 through binary source install. I'm using Redhat 7.2 and 8.0 I'm trying like this.. ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-charset=big5 make so far there is no problems. but when i try like 'make install',i got this error message and Installation is stopped. ... /usr/bin/install -c copy-db /usr/local/mysql/sql-bench/copy-db /usr/bin/install: `copy-db' and `/usr/local/mysql/sql-bench/copy-db' are the same file make[2]: *** [install-benchSCRIPTS] error1 make[2]: exit `/usr/local/mysql/sql-bench' directory make[1]: *** [install-am] error 2 make[1]: exit `/usr/local/mysql/sql-bench' directory make: *** [install-recursive] error 1 [EMAIL PROTECTED] mysql]# I have found any topic about this error in Google and Yahoo, they display many this message but no any documents, reply or helpdest to explain it. Therefore, I would like you to suggestion some guide or solutions for me. best regards, Albert Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compile mysql meet problem
Dear, Now i'm trying to install MySQL 4.0.14 through binary source install. I'm using Redhat 7.2 and 8.0 I'm trying like this.. ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-charset=big5 make so far there is no problems. but when i try like 'make install',i got this error message and Installation is stopped. ... /usr/bin/install -c copy-db /usr/local/mysql/sql-bench/copy-db /usr/bin/install: `copy-db' and `/usr/local/mysql/sql-bench/copy-db' are the same file make[2]: *** [install-benchSCRIPTS] error1 make[2]: exit `/usr/local/mysql/sql-bench' directory make[1]: *** [install-am] error 2 make[1]: exit `/usr/local/mysql/sql-bench' directory make: *** [install-recursive] error 1 [EMAIL PROTECTED] mysql]# I have found any topic about this error in Google and Yahoo, they display many this message but no any documents, reply or helpdest to explain it. Therefore, I would like you to suggestion some guide or solutions for me. best regards, Albert Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compile mysql meet problem
Dear, Now i'm trying to install MySQL 4.0.14 through binary source install. I'm using Redhat 7.2 and 8.0 I'm trying like this.. ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-charset=big5 make so far there is no problems. but when i try like 'make install',i got this error message and Installation is stopped. ... /usr/bin/install -c copy-db /usr/local/mysql/sql-bench/copy-db /usr/bin/install: `copy-db' and `/usr/local/mysql/sql-bench/copy-db' are the same file make[2]: *** [install-benchSCRIPTS] error1 make[2]: exit `/usr/local/mysql/sql-bench' directory make[1]: *** [install-am] error 2 make[1]: exit `/usr/local/mysql/sql-bench' directory make: *** [install-recursive] error 1 [EMAIL PROTECTED] mysql]# I have found any topic about this error in Google and Yahoo, they display many this message but no any documents, reply or helpdest to explain it. Therefore, I would like you to suggestion some guide or solutions for me. best regards, Albert Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Terminology question
I've read it now a few times around here and was curious what innodb means. Is it a table-type for MySQL or a completely different DBMS? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems starting mysql - malloc?
Hi all, I've had a mysql db server running fine for a few months now, and all of a sudden, I'm having trouble with it. when starting the service, the error log contains the following 88888 021119 09:58:56 mysqld started /usr/sbin/mysqld: Can't read dir of '/root/tmp/' (Errcode: 13) 021119 9:58:58 bdb: malloc: Cannot allocate memory: 10485760 021119 9:58:58 Can't init databases 021119 09:58:58 mysqld ended 88888 I have no idea why it it looking in root/tmp for anything any ideas on where to start?? Thanks Albert ___ http://www.webmail.co.za the South-African free email service NetWiseGurus.Com Portal - Your Own Internet Business Today! - 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: Memory limit issue with 64 bit mysql.3.23.46
On Wed, Feb 06, 2002 at 06:12:22PM +0200, Sinisa Milivojevic wrote: Albert Chin writes: MySQL 3.23.46 was built with the Sun C++ compiler: $ CC -V CC: Sun WorkShop 6 update 2 C++ 5.3 Patch 111685-03 2001/10/19 It was built as follows: CC=cc CFLAGS=-mr -Qn -xstrconst -xO2 -xtarget=generic -xarch=v9 \ CXX=CC CXXFLAGS=-xO2 -xtarget=generic -xarch=v9 \ CPPFLAGS=-I/opt/TWWfsw/readline42a/include \ -I/opt/TWWfsw/zlib11/include -I/opt/TWWfsw/ncurses5/include \ -I/opt/TWWfsw/ncurses5/include/ncurses \ LDFLAGS=-L/opt/TWWfsw/readline42a/lib/sparcv9 \ -L/opt/TWWfsw/zlib11/lib/sparcv9 -L/opt/TWWfsw/ncurses5/lib/sparcv9 \ -L/opt/TWWfsw/readline42a/lib/sparcv9 \ -R/opt/TWWfsw/readline42a/lib/sparcv9:\ /opt/TWWfsw/zlib11/lib/sparcv9:/opt/TWWfsw/ncurses5/lib/sparcv9 \ -xarch=v9 ./configure --localstatedir=/var/opt/TWWfsw/mysql32346 \ --sysconfdir=/etc/opt/TWWfsw/mysql32346 --enable-shared \ --enable-static --without-debug --with-innodb --without-bench \ --without-readline --enable-strcoll --with-libwrap \ --with-berkeley-db --enable-thread-safe-client \ --prefix=/opt/TWWfsw/mysql32346 -- albert chin ([EMAIL PROTECTED]) Please tell me a value for this #define in config.h and my_config.h : SIZEOF_LONG $ grep SIZEOF_LONG config.h #define SIZEOF_LONG 8 #define SIZEOF_LONG_LONG 8 $ grep SIZEOF_LONG incldue/my_config.h #define SIZEOF_LONG 8 #define SIZEOF_LONG_LONG 8 -- albert chin ([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: Memory limit issue with 64 bit mysql.3.23.46
On Wed, Feb 06, 2002 at 07:17:13PM +0200, Sinisa Milivojevic wrote: Albert Chin writes: $ grep SIZEOF_LONG config.h #define SIZEOF_LONG 8 #define SIZEOF_LONG_LONG 8 $ grep SIZEOF_LONG incldue/my_config.h #define SIZEOF_LONG 8 #define SIZEOF_LONG_LONG 8 -- albert chin ([EMAIL PROTECTED]) If SIZEOF_INT is 4, then it could be our bug. However, according to http://docs.sun.com/ab2/coll.45.13/SOL64TRANS/%40Ab2PageView/1865;td=5?Ab2Lang=C Ab2Enc=iso-8859-1, in the LP64 (64-bit) environment, int is 32-bits and long is 64-bits so it would appear SIZEOF_INT == 4 is correct above. Could MySQL be doing something else odd? -- albert chin ([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: Memory limit issue with 64 bit mysql.3.23.46
On Wed, Feb 06, 2002 at 08:04:23PM +0200, Sinisa Milivojevic wrote: Albert Chin writes: On Wed, Feb 06, 2002 at 07:17:13PM +0200, Sinisa Milivojevic wrote: Yes, SIZEOF_INT is 4: $ grep SIZEOF_INT config.h #define SIZEOF_INT 4 $ grep SIZEOF_INT include/my_config.h #define SIZEOF_INT 4 However, according to http://docs.sun.com/ab2/coll.45.13/SOL64TRANS/%40Ab2PageView/1865;td=5?Ab2Lang=CAb2Enc=iso-8859-1, in the LP64 (64-bit) environment, int is 32-bits and long is 64-bits so it would appear SIZEOF_INT == 4 is correct above. -- albert chin ([EMAIL PROTECTED]) It is correct. It is a smnall problem in our code. Ok. Guess we'll wait for a fix. Any idea when a fix for MySQL will be available? -- albert chin ([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: Memory limit issue with 64 bit mysql.3.23.46
On Wed, Feb 06, 2002 at 02:57:51PM +0200, Sinisa Milivojevic wrote: Franklin, Kevin writes: We are running an extremely large instance of mysql version 3.23.46 on Solaris 2.8. We are attempting to use a software version compiled 64 bit and have been experiencing memory related server crashes. The behavior suggests that we are still unable to use more than 4G of memory. If the server is started with the following settings: key_buffer=4608M max_allowed_packet=1M table_cache=1024 record_buffer=1M sort_buffer=1M thread_cache=12 thread_concurrency=12 myisam_sort_buffer_size=512M We observe the following symptoms / problems: (1) A mysqladmin variables call shows that the key_buffer is set to be 1 G, not 4.5G. (2) Under any sort of database load, the server crashes with the following error output: mysql got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=4831834112 record_buffer=1044480 sort_buffer=1048568 max_used_connections=0 max_connections=512 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 5765112 K bytes of memory Hope that's okay, if not, decrease some variables in the equation. If the server is started with more modest settings (e.g., key_buffer_size=2G), mysql operates correctly. Additionally, mysqladmin variables shows a correct key_buffer value. System memory is not a constraint ( 6 G real memory and 14.5 G swap). Hi! This is a problem that we would like to take a look at !! Please provide us with details on how have you built MySQL. For the start ... MySQL 3.23.46 was built with the Sun C++ compiler: $ CC -V CC: Sun WorkShop 6 update 2 C++ 5.3 Patch 111685-03 2001/10/19 It was built as follows: CC=cc CFLAGS=-mr -Qn -xstrconst -xO2 -xtarget=generic -xarch=v9 \ CXX=CC CXXFLAGS=-xO2 -xtarget=generic -xarch=v9 \ CPPFLAGS=-I/opt/TWWfsw/readline42a/include \ -I/opt/TWWfsw/zlib11/include -I/opt/TWWfsw/ncurses5/include \ -I/opt/TWWfsw/ncurses5/include/ncurses \ LDFLAGS=-L/opt/TWWfsw/readline42a/lib/sparcv9 \ -L/opt/TWWfsw/zlib11/lib/sparcv9 -L/opt/TWWfsw/ncurses5/lib/sparcv9 \ -L/opt/TWWfsw/readline42a/lib/sparcv9 \ -R/opt/TWWfsw/readline42a/lib/sparcv9:\ /opt/TWWfsw/zlib11/lib/sparcv9:/opt/TWWfsw/ncurses5/lib/sparcv9 \ -xarch=v9 ./configure --localstatedir=/var/opt/TWWfsw/mysql32346 \ --sysconfdir=/etc/opt/TWWfsw/mysql32346 --enable-shared \ --enable-static --without-debug --with-innodb --without-bench \ --without-readline --enable-strcoll --with-libwrap \ --with-berkeley-db --enable-thread-safe-client \ --prefix=/opt/TWWfsw/mysql32346 -- albert chin ([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: Memory limit issue with 64 bit mysql.3.23.46
On Wed, Feb 06, 2002 at 06:12:22PM +0200, Sinisa Milivojevic wrote: Albert Chin writes: MySQL 3.23.46 was built with the Sun C++ compiler: $ CC -V CC: Sun WorkShop 6 update 2 C++ 5.3 Patch 111685-03 2001/10/19 It was built as follows: CC=cc CFLAGS=-mr -Qn -xstrconst -xO2 -xtarget=generic -xarch=v9 \ CXX=CC CXXFLAGS=-xO2 -xtarget=generic -xarch=v9 \ CPPFLAGS=-I/opt/TWWfsw/readline42a/include \ -I/opt/TWWfsw/zlib11/include -I/opt/TWWfsw/ncurses5/include \ -I/opt/TWWfsw/ncurses5/include/ncurses \ LDFLAGS=-L/opt/TWWfsw/readline42a/lib/sparcv9 \ -L/opt/TWWfsw/zlib11/lib/sparcv9 -L/opt/TWWfsw/ncurses5/lib/sparcv9 \ -L/opt/TWWfsw/readline42a/lib/sparcv9 \ -R/opt/TWWfsw/readline42a/lib/sparcv9:\ /opt/TWWfsw/zlib11/lib/sparcv9:/opt/TWWfsw/ncurses5/lib/sparcv9 \ -xarch=v9 ./configure --localstatedir=/var/opt/TWWfsw/mysql32346 \ --sysconfdir=/etc/opt/TWWfsw/mysql32346 --enable-shared \ --enable-static --without-debug --with-innodb --without-bench \ --without-readline --enable-strcoll --with-libwrap \ --with-berkeley-db --enable-thread-safe-client \ --prefix=/opt/TWWfsw/mysql32346 -- albert chin ([EMAIL PROTECTED]) Please tell me a value for this #define in config.h and my_config.h : SIZEOF_LONG $ grep SIZEOF_LONG config.h #define SIZEOF_LONG 8 #define SIZEOF_LONG_LONG 8 $ grep SIZEOF_LONG incldue/my_config.h #define SIZEOF_LONG 8 #define SIZEOF_LONG_LONG 8 -- albert chin ([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: Memory limit issue with 64 bit mysql.3.23.46
On Wed, Feb 06, 2002 at 08:04:23PM +0200, Sinisa Milivojevic wrote: Albert Chin writes: On Wed, Feb 06, 2002 at 07:17:13PM +0200, Sinisa Milivojevic wrote: Yes, SIZEOF_INT is 4: $ grep SIZEOF_INT config.h #define SIZEOF_INT 4 $ grep SIZEOF_INT include/my_config.h #define SIZEOF_INT 4 However, according to http://docs.sun.com/ab2/coll.45.13/SOL64TRANS/%40Ab2PageView/1865;td=5?Ab2Lang=CAb2Enc=iso-8859-1, in the LP64 (64-bit) environment, int is 32-bits and long is 64-bits so it would appear SIZEOF_INT == 4 is correct above. -- albert chin ([EMAIL PROTECTED]) It is correct. It is a smnall problem in our code. Ok. Guess we'll wait for a fix. Any idea when a fix for MySQL will be available? -- albert chin ([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
Accessing large memory with 64-bit MySQL on Solaris
We've built MySQL 3.23.46 on Solaris 8/SPARC as a 64-bit executable using the Sun C compiler. Everything built OK. We built against ncurses, readline, and zlib, all built as 64-bit libraries. Are there any special settings to get MySQL to work properly in this environment or should things work as expected. If the server is started with the following settings: key_buffer=4608M max_allowed_packet=1M table_cache=1024 record_buffer=1M sort_buffer=1M thread_cache=12 thread_concurrency=12 myisam_sort_buffer_size=512M then mysqladmin variables shows key_buffer as 1G, not 4.5G. The Solaris 8 machine this is running on has 6GB real memory and 14.5GB of swap. -- albert chin ([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
innodb and vbulletin 1.1
Hello, I have a few questions. First I work on a very high traffic site that uses vbulletin 1.1 to run its bb. The bb is very high traffic around 60-200 concurrent users. We are starting to run into some serious locking issues, and I am thinking about converting the high use tables(maby all) to innodb. My first question is will this break anything in the application layer? Second we may be moving this to a new serve where I could have 6+ disks. I have done some testing and found that after all the importing into innodb all the data is around 1gb. Would it be helpful to add these extra disks creating 1+gb raw partition on each one to spread the io across the disks and controllers. And how does innodb distribute the data through the table spaces? Thanks for any help Byron [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: Server Problems
You may want to check your /tmp partion. it may be creating some temp tables. -t, --tmpdir=path Path for temporary files. It may be useful if your default /tmp directory resides on a partition too small to hold temporary tables. Byron [EMAIL PROTECTED] wrote: Hello All, Background: -Site has 8000 uniques per day, average of 300 online at once hammering the forums. -POST table has over 350,000 entries When doing a backup with mysqldump and then trying to re-import the data to a test server, we keep getting: ERROR 1030 at line 207477: Got error 28 from table handler - No space left on device Is there an way to remedy this problem? Obviously we have a setup error or something as there is a 20gig drive in the mySQL server. mysql database Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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
group by and make_set
Is there a way to have SELECT return sets for rows that were grouped together? So if I had a table like: Table: ID DATA 1a 1b 2X 2Y 310 320 I want to do something like this: SELECT MAKE_SET(-1,DATA) as LIST FROM Table GROUP BY ID ID DATA 1a,b 2X,Y 310,20 - 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
autocommit question
Just started using mysql and was wondering if there's a way to specify the autocommit mode on a per database rather on a per server. I know I can using the BEGIN/COMMIT to turn off autocommit for a particular call. But it would be nice to have a coarser granularity. Thanks, Albert - 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
newbie question regarding BDB
How does one tell if mysql is using BDB? Do I have to somehow specify BDB when creating a new database? I've installed BerkeleyDB and mysql, yet it doesn't look like it's using bdb. Unable to rollback any insert's. Thanks, Albert - 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
thread_cache_size on linux
I am trying to tune mysql on linux for a web based application. Reading throw the docs I found thread_cache_size I checked what it was set to by default and found that it was 0. In the docs it says That it doesn't make much difference if you have a good thread implementation. So my major question is does linux have a good thread implementations and ether way will this make a difference? The only reason That I am kinda hesitant to set this up really high is that it would keep the threads living for a long time witch may cause memory leaks. I would appreciate any help and also if any one has good links other than the manual for tuning mysql. Thanks Byron - 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
