INNODB
-- Thanks Regards, veerabhadra rao narra, +91-988-556-5556
any suodo columns are there Like Rowid, rownum in mysql
any suodo columns are there Like Rowid, rownum in mysql -- Thanks Regards, veerabhadra rao narra, +91-988-556-5556
Password for Root
Hi people, I have a small problem. I forgot the password for user root in my personal MySQL instance. I'm a Linux user and running Debian Sarge on my computer. I have few other DBs which I created and which I have access to (still I remember the passwords) but are not having access to MySQL database. Is there any way to recover from this situation. If I reinitialize the DB (somehow) how can I port my existing information back in to the initialized DB (without exporting and importing). Any Debian friends who can help me. Cheers, Kosala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Password for Root
Hi Restart mysqld with the --skip-grant-tables option More info : http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html --Praj On Mon, 10 Apr 2006 12:57:32 +0600 Kosala Atapattu [EMAIL PROTECTED] wrote: Hi people, I have a small problem. I forgot the password for user root in my personal MySQL instance. I'm a Linux user and running Debian Sarge on my computer. I have few other DBs which I created and which I have access to (still I remember the passwords) but are not having access to MySQL database. Is there any way to recover from this situation. If I reinitialize the DB (somehow) how can I port my existing information back in to the initialized DB (without exporting and importing). Any Debian friends who can help me. Cheers, Kosala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining if a trigger exists
Adrian Co wrote: Hi, Yes, the syntax is not valid. I have tried it. :-) I was hoping for maybe a workaround to simulate the described functionality. There might also be something wrong with the structure of my script. Maybe I shouldn't be creating triggers there? But I was thinking if the CREATE DATABASE and CREATE TABLE have a IF NOT EXISTS functionality, why should the triggers be any different? Maybe this is just an oversight, maybe the CREATE TRIGGER should have a IF NOT EXISTS functionality also? or I'm just doing something very wrong. Maybe someone can point me in the right direction? Hi Adrian you can check whether a trigger exists by querying the information schema database: http://dev.mysql.com/doc/refman/5.0/en/triggers-table.html Hope that helps. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Password for Root
At 02:57 PM 4/10/2006, Kosala Atapattu wrote: Hi people, I have a small problem. I forgot the password for user root in my personal MySQL instance. I'm a Linux user and running Debian Sarge on my computer. I have few other DBs which I created and which I have access to (still I remember the passwords) but are not having access to MySQL database. Is there any way to recover from this situation. If I reinitialize the DB (somehow) how can I port my existing information back in to the initialized DB (without exporting and importing). Any Debian friends who can help me. Cheers, Kosala -- HI Kosala, You can try to use this documentation. Thanks, Ehrwin MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] MySQL: changing forgotten root password (mysqld) Applicable to: Red Hat Linux Systems Updated: Mar 18, 2004 This Sheet describes the procedure how to change the root password of MySQL server. * Stop MySQL server if its running. * # service mysqld stop * Check that MySQL daemon has stopped * # ps -jef | grep mysqld * Start MySQL as root * # su - * # /usr/libexec/mysqld -Sg --user=root * Go back into MySQL with the client: * # mysql * mysql use mysql * Now change the MySQL root password * mysql UPDATE user SET password=password(newpassword) WHERE user=root; mysql flush privileges; mysql exit; * Stop MySQL server. * # killall mysqld * Verify that MySQL daemon is not running * # ps -jef | grep mysqld * Start MySQL the normal way, and all is good. For Red Hat this is: * # service mysqld start * Verify if MySQL daemon is running * # ps -jef | grep mysqld That's it. Jett Tayer and Ehrwin Mina Jett Tayer and Ehrwin Mina
Re: what is this? -- errno=2006 errmsg=Server gone
Martin Olsson wrote: Hi, I get this weird error message: ErrNo: 2006 Error: MySQL server has gone away. What does it mean? I couldn't find anything useful on google and the error message isn't exactly verbose.. :) Haha, yeah i like that one. Somone stole it haha :D I'm not quite sure but: It's just that a connection which was opened has been broken due to mysql crash. So the deamon is gone. Or: The Master Server died on Master-Slave Mysql runs. But i think it was the first one. Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timestamp not null
Hi! I created a table and, into it, a timestamp field: ... EXPIRES TIMESTAMP NOT NULL, ... When I issue the command describe it shows the field expires allows nulls and defaults to CURRENT_TIMESTAMP. Also, each time I update a field other than expires in this table, expires gets updated to the current timestamp. Does anybody know how can I make a timestamp field be not null? Lots of thanks to you all. Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help with count and join on same table I think
2wsxdr5 wrote:
I have a table of people. Some of the people in this table are
related. You can find out who is related by comparing a familyID
number. I have a query to select a certain group of people from the
table and I want to also select anyone who is related to them, even
though those who are related will not match the other criteria. So my
table is something like this
people{
PID,
Name
FamilyID,
BirthDate,
Sex
Address
. . .
)
I have a query like this
Select * from people where BirthDate 1987 and Birthday 1950 and
address = xyz
I need to change it so it includes everyone that has the same Family ID
as each person that query will return and group it by Family ID
Hi Chris,
if you are using a new enough version (4.1 or newer) you can use a subquery.
If I understand your intention correctly, the following statement should do
what you need:
select familyid, count(PID) from people where FamilyID = (select FamilyID
from people where BirthDate 1987 and Birthday 1950 and address = xyz)
group by FamilyId
If your version does not support sub-queries, have at look at
http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html
Cheers
Frank
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp not null
Hi, I created a table and, into it, a timestamp field: ... EXPIRES TIMESTAMP NOT NULL, ... When I issue the command describe it shows the field expires allows nulls and defaults to CURRENT_TIMESTAMP. Also, each time I update a field other than expires in this table, expires gets updated to the current timestamp. Does anybody know how can I make a timestamp field be not null? Lots of thanks to you all. If you want to store date/time values, do not use the TIMESTAMP datatype. What is it that you're trying to do? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AS in a statement
Best group member, I do this query SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par, score-par AS overpar FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id It gives error: [localhost] ERROR 1054: Unknown column 'score' in 'field list' I know where the error comes from (the tables does not have score in them), but I want to simplify the query using the AS property, and continue in the query us it. This works: SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par, tour_player_score.strokes - tour_scorecard_hole.par AS overpar FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id But I do not want to use the long name... anyone with comments? Best regards, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AS in a statement
Best group member, I do this query SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par, score-par AS overpar FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id It gives error: [localhost] ERROR 1054: Unknown column 'score' in 'field list' I know where the error comes from (the tables does not have score in them), but I want to simplify the query using the AS property, and continue in the query us it. This works: SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par, tour_player_score.strokes - tour_scorecard_hole.par AS overpar FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id But I do not want to use the long name... anyone with comments? First things first... IMO, you should ALWAYS use full tables names or table aliasses before columns as soon as you're using more than 1 table in your query. eg: select tps.strokes, tsh.par from tour_player_score tps join tour_scorecard_hole tsh on (tsh.id = tps.scorecard_hole_id) where tps.tour_player_id = 175 As you can see, you can alias your table names and things should be easier for you. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: any suodo columns are there Like Rowid, rownum in mysql
from the mysql 5.0.18 manual section 13.1.5. CREATE TABLE Syntax If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements. Regards Keith In theory, theory and practice are the same; in practice they are not. On Mon, 10 Apr 2006, Veerabhadra rao Narra wrote: To: mysql@lists.mysql.com From: Veerabhadra rao Narra [EMAIL PROTECTED] Subject: any suodo columns are there Like Rowid, rownum in mysql any suodo columns are there Like Rowid, rownum in mysql -- Thanks Regards, veerabhadra rao narra, +91-988-556-5556 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AS in a statement
Peter Lauri wrote: Best group member, I do this query SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par, score-par AS overpar FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id It gives error: [localhost] ERROR 1054: Unknown column 'score' in 'field list' I know where the error comes from (the tables does not have score in them), but I want to simplify the query using the AS property, and continue in the query us it. This works: SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par, tour_player_score.strokes - tour_scorecard_hole.par AS overpar FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id But I do not want to use the long name... anyone with comments? Best regards, Peter You could use the HAVING statement for this. Not tested: SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par FROM `tour_player_score`, tour_scorecard_hole HAVING score-par AS overpar WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id Hmm was having before or after where (hmm) test that also please: SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id HAVING score-par AS overpar Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do if and elseif and other calculations
Best groupmember,
I have this query
SELECT count(*) AS number_of_holes, tour_player_score.strokes -
tour_scorecard_hole.par AS overpar
FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND
tour_scorecard_hole.id=tour_player_score.scorecard_hole_id GROUP BY overpar
It gives me number_of_holes and overpar.
Right now I do this with PHP, but would like to move it to sql directly:
$diff = 0;
While($Row = mysql_fetch_array()) {
If($Row['overpar'])0) $diff = $diff - $Row['overpar']*
$Row['number_of_holes'];
Elseif($Row['overpar']=3) diff = $diff - $Row['overpar']*
$Row['number_of_holes'] + 2;
}
Anyway to move this to MySQL and just have one row containing the diff?
Best regards,
Peter Lauri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do if and elseif and other calculations
Peter Lauri wrote:
Best groupmember,
I have this query
SELECT count(*) AS number_of_holes, tour_player_score.strokes -
tour_scorecard_hole.par AS overpar
FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND
tour_scorecard_hole.id=tour_player_score.scorecard_hole_id GROUP BY overpar
It gives me number_of_holes and overpar.
Right now I do this with PHP, but would like to move it to sql directly:
$diff = 0;
While($Row = mysql_fetch_array()) {
If($Row['overpar'])0) $diff = $diff - $Row['overpar']*
$Row['number_of_holes'];
Elseif($Row['overpar']=3) diff = $diff - $Row['overpar']*
--^
You have an error here.
You miss a ´$´
$Row['number_of_holes'] + 2;
}
Anyway to move this to MySQL and just have one row containing the diff?
Sorry can't give any more comments to that.
Looking forward to see the comments on this.
Greets
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting up phpmyadmin problem
[EMAIL PROTECTED] schrieb: http://www.blue-fly.co.uk/screen.jpg I cannot seem to add a server..anyone shed any light on it? Just edit config.inc.php It has good documentation comments, so it should not be a problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting up phpmyadmin problem
I did. The server is windows could this be the problem? The config.inc.php is in the main phpmyadmin folder not in the config folder. Is this ok? Ross - Original Message - From: Dominik Klein [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 10, 2006 11:25 AM Subject: Re: setting up phpmyadmin problem [EMAIL PROTECTED] schrieb: http://www.blue-fly.co.uk/screen.jpg I cannot seem to add a server..anyone shed any light on it? Just edit config.inc.php It has good documentation comments, so it should not be a problem. -- 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: Do if and elseif and other calculations
Haha, yes I know that. I just created the code in the email editor. :)
-Original Message-
From: Barry [mailto:[EMAIL PROTECTED]
Sent: Monday, April 10, 2006 5:19 PM
To: mysql@lists.mysql.com
Subject: Re: Do if and elseif and other calculations
Peter Lauri wrote:
Best groupmember,
I have this query
SELECT count(*) AS number_of_holes, tour_player_score.strokes -
tour_scorecard_hole.par AS overpar
FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND
tour_scorecard_hole.id=tour_player_score.scorecard_hole_id GROUP BY
overpar
It gives me number_of_holes and overpar.
Right now I do this with PHP, but would like to move it to sql directly:
$diff = 0;
While($Row = mysql_fetch_array()) {
If($Row['overpar'])0) $diff = $diff - $Row['overpar']*
$Row['number_of_holes'];
Elseif($Row['overpar']=3) diff = $diff - $Row['overpar']*
--^
You have an error here.
You miss a ´$´
$Row['number_of_holes'] + 2;
}
Anyway to move this to MySQL and just have one row containing the diff?
Sorry can't give any more comments to that.
Looking forward to see the comments on this.
Greets
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
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: timestamp not null
I need to create a commands table. A program will periodically check on this table whether there's a pending command for it to execute or not. Whatever the reason, this program might read a command but not acknowledge it's execution. Other program will check out whether the command timeout has expired or not and so act accordingly. I guess I can use some sort of integer in order to represent it as a unix timestamp, but I would prefer to use a timestamp. Any suggestions? Kind regards Mensaje original De: [EMAIL PROTECTED] Recibido: 10/04/2006 11:51 Para: [EMAIL PROTECTED], mysql@lists.mysql.com Asunto: Re: timestamp amp; not null Hi, I created a table and, into it, a timestamp field: ... EXPIRES TIMESTAMP NOT NULL, ... When I issue the command describe it shows the field expires allows nulls and defaults to CURRENT_TIMESTAMP. Also, each time I update a field other than expires in this table, expires gets updated to the current timestamp. Does anybody know how can I make a timestamp field be not null? Lots of thanks to you all. If you want to store date/time values, do not use the TIMESTAMP datatype. What is it that you're trying to do? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.19, fails to start, no log entries!
Greetings- I have a fresh install of 5.0.19, and when I go to start the database (using the init script), it fails to start. Odd enough, it only logs the start and end lines in the .err log. Is anyone familiar with this? The only thing different about the box is that it has dell's openmanage stuff on it. Nothing is on 3306, so it's not a bind problem. Thanks- Peter J. Milanese, System Administrator Information Technology Group The New York Public Library [EMAIL PROTECTED] - 212.621.0203 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do if and elseif and other calculations
You can do something like this: SET @diff = 0; SELECT count(*) AS number_of_holes, tour_player_score.strokes - tour_scorecard_hole.par AS overpar, IF(overpar0, @diff-(overpar*number_of_holes), ELSEVALUE) FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id GROUP BY overpar; And instead of ELSEVALUE you can insert another IF... There is also a CASE in MySQL... you can also use that one... What i don't see here is a DEFAULT value... do you have any ? -- Gabriel PREDA Senior Web Developer
Re: timestamp not null
I need to create a commands table. A program will periodically check on this table whether there's a pending command for it to execute or not. Whatever the reason, this program might read a command but not acknowledge it's execution. Other program will check out whether the command timeout has expired or not and so act accordingly. I guess I can use some sort of integer in order to represent it as a unix timestamp, but I would prefer to use a timestamp. As I said -- to store date/time values, you should NOT use the TIMESTAMP datatype, cause it isn't supposed to be used to store custom date/time values. I suggest you read the documentation on MySQL Date/Time datatypes -- it has a fine explanation of what to use and what to use the TIMESTAMP datatype for. Don't bother going the Unix integer timestamp thingy route. Just read this: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error with java
I'm trying to understand this error: java.sql.SQLException: Server connection failure during transaction. Attempted reconnect 3 times. Giving up. the java program is trying to connect to MySQL (5.0.19 under Mac OSX/ PPC), with user=root, from localhost. the grant privileges seems to be ok (I'm able to connect via terminal with 'mysql -u root -h 127.0.0.1' and with 'mysql -u root -h localhost') on mysql's logs there are no particular info about the connection attempt. on a different machine, with the same configuration and grants all works. what I have to check? where is the problem? thank you in advance bye bye! MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error with java
marco stagno wrote: I'm trying to understand this error: java.sql.SQLException: Server connection failure during transaction. Attempted reconnect 3 times. Giving up. the java program is trying to connect to MySQL (5.0.19 under Mac OSX/ PPC), with user=root, from localhost. the grant privileges seems to be ok (I'm able to connect via terminal with 'mysql -u root -h 127.0.0.1' and with 'mysql -u root -h localhost') on mysql's logs there are no particular info about the connection attempt. on a different machine, with the same configuration and grants all works. what I have to check? where is the problem? thank you in advance bye bye! MAS! Ports closed? Wrong ports configured? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.19, fails to start, no log entries!
FYI- I am a moron, and SELinux was enforced, although I thought I had disabled it. Thanks- Peter J. Milanese, System Administrator Information Technology Group The New York Public Library [EMAIL PROTECTED] - 212.621.0203 [EMAIL PROTECTED] g To 04/10/2006 08:09 mysql@lists.mysql.com AM cc Subject MySQL 5.0.19, fails to start, no log entries! Greetings- I have a fresh install of 5.0.19, and when I go to start the database (using the init script), it fails to start. Odd enough, it only logs the start and end lines in the .err log. Is anyone familiar with this? The only thing different about the box is that it has dell's openmanage stuff on it. Nothing is on 3306, so it's not a bind problem. Thanks- Peter J. Milanese, System Administrator Information Technology Group The New York Public Library [EMAIL PROTECTED] - 212.621.0203 -- 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: Social Network, linking members
--- Martin Gallagher [EMAIL PROTECTED] wrote:
of course you have the problem where john has Joe as a friend but
Joe
doesn't have john as a friend. This seeming inconsistency, may or
may not
be a problem depending on exactly what kind of a relationship you are
trying
to define.
You've just hit the nail on the head! That's exactly the problem.
I think I might just have to grin and bear what I already have :-(
-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED]
Sent: 07 April 2006 15:11
To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: Social Network, linking members
Martin Gallagher wrote:
Hi,
I'm trying to find the most efficient way of linking members to
one
another in a social networking application.
Currently I link them using 2 separate fields for the members: id1,
id2.
So,
to find people in your network you would do:
I'm not sure exactly what it is you are doing but I think this may be
it. You have a table of people and you want to know who is friends
with
who. I know 'friend' may not be the best term to use but it is
easier
to type. So I have my people table.
People{
*PID,
Name,
. . .
}
Then the Friend Table,
Friend{
*PID,
*FID
}
If you have person, John, with ID 234, and you want to know all his
friends you can do this...
SELECT f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID = 234
of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend. This seeming inconsistency, may or
may
not be a problem depending on exactly what kind of a relationship you
are trying to define.
--
Chris W
KE5GIX
Gift Giving Made Easy
Get the gifts you want
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com
No, you don't need to keep what you have. What everyone is trying to
help you do is to normalize your data by splitting your relationship
information into it's own table. The new table (most of the responses
have given it just three columns but it can have MORE... more on that
later) is the fastest way to model the many-to-many relationships
between friends as it allows full usage of available indexes.
The directionality of the relationship is determined by whose ID is
in the from column and whose ID is in the to column. Using Jad's
example:
Relation table
rel_id
user_id
friend_id
The from column is `user_id` and the to column is `friend_id`. That
defines the relationship in one direction. If the friendship is
symmetrical there will be a second entry where the `user_id` and
`friend_id` values are reversed.
Each row in this table represents a relationship between two
people. You go on to describe a status value and possibly a
type of relationship. All this requires is a couple more columns to
the Relation table
Relation table
rel_id
user_id
friend_id
group
flag
This way each user only needs one record to exist in your system. Each
relationship between each pair of users takes either one (asymmetrical
friendship) or two (symmetrical friendship) relationship records. If
all of the fields in this relationship table are numeric, then every
row of data will be the exact same length (as stored on disk). This
fixed-width type of table is EXTREMELY fast for searching and
retrievals.
I honestly encourage you to attempt to use a second table in your
design.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error with java
Ports closed? Wrong ports configured? I don't think so, since even with telnet localhost 3306 or telnet 127.0.0.1 3306 I'm able to see the server prompt.. and I'm using the standard MySQL port for sure (it's a clean installation); and there are no firewall active... any other suggestion!? thanks :) marco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error with java
I don't think so, since even with telnet localhost 3306 or telnet 127.0.0.1 3306 I'm able to see the server prompt.. and I'm using the standard MySQL port for sure (it's a clean installation); and there are no firewall active... any other suggestion!? thanks :) Wild stab in the dark here - Java I/O permissions not set correctly on your machine to allow it? If I recall, Java's sandbox feature means you have to supply a permissions file for I/O. Perhaps that file already exists on the other machine that works? -- Jason Teagle [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error with java
Wild stab in the dark here - Java I/O permissions not set correctly on your machine to allow it? If I recall, Java's sandbox feature means you have to supply a permissions file for I/O. Perhaps that file already exists on the other machine that works? I don't know so much Java, but I didn't changed anything in the 2 machine from the default installation (Java 1.5xx) if you can tell me more, I can check thank you again!! MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem running multi master replication
--- Leonardus Setiabudi [EMAIL PROTECTED] wrote: let me re-explain my situation .. the mysql server was on /home/mysql/mysql4111 and the data directory was on /home/mysql/mysql4111/data in data directory, there are 4 databases, db1, db2, db3, db4 all configurations resided in /home/mysql/replicate.. those are my.cnf, replicate1.cnf, replicate2.cnf and replicate3.cnf servers, run with this command : /home/mysql/mysql4111/bin/mysqld --defaults-file=/home/mysql/replicate/my.cnf /home/mysql/mysql4111/bin/mysqld --defaults-file=/home/mysql/replicate/replicate1.cnf /home/mysql/mysql4111/bin/mysqld --defaults-file=/home/mysql/replicate/replicate2.cnf /home/mysql/mysql4111/bin/mysqld --defaults-file=/home/mysql/replicate/replicate3.cnf server 1 is just a plain server, not a master nor a slave other are slaves to each of their own master (hence the different configuration files) so if i draw it it would be something like this --- | | db1 |--- server 1 port 3306 |D| | | | db2 | |- server 2 |A| | | | db3 | |- server 3 |T| | | | db4 | |- server 4 |A| | | --- server 2, only replicates db2 from its master, runs on port 3307 server 3, only replicates db3 from its master, runs on port 3308 server 4, only replicates db4 from its master, runs on port 3309 when i log into server 2 using port 3307, i can see the data in db2 updated, which means the replication succeed (show slave status tells the same thing), and the same thing with server 3, i can see db3 updated, also with server 4 where db4 updated but when i log into server1, where i can access all the dbs, db2-4 were never updated (it should be, its the same file) in this time, if i run repair table on server1 to all tables in db2-4 .. bingo, the data appears .. with the info, found xxx rows of 0 rows so, to be clear ... server1 is NOT a SLAVE, its just plain server server2-4 runs well, and replicate well ... the problem lies within server1, where it supposed to be able to read the FILE (tables) without the need to issue repair command (at least in my assumption :) ) i hope this will bring more detail .. thanks - leo I understand now. You have 3 slave server processes replicating independently from their own separate master servers. You have a fourth MySQL server process that shares the same data files as your 3 slaves but it does not know when replication occurs and when it hasn't (for MANY reasons) so it doesn't see the updated information. First rule: Do NOT share data files between server processes. Nothing should directly interact with a datafile other than the server to which it belongs. This includes other server processes as well as direct user actions or actions from a third-party program. There are structures and procedures that each MySQL process maintains that assumes that each process has exclusive control over every data file it is managing. Sharing files between two processes is highly discouraged. However, if you are using 5.0 or newer, you can FEDERATE the files from Servers 2-4 to Server 1. That way #1 never needs to directly read from those files and you will avoid the myriad of problems that your setup can create. Your data will always be up-to-date and you won't have to worry about file caching, table caching, index caching, file locking, read/write contentions or any of those other problems. Please try shifting your table definitions on Server1 from direct-read to FEDERATED. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert speed on table with 7M rows but small row size.
--- Alexey Polyakov [EMAIL PROTECTED] wrote: On 4/8/06, Kevin Burton [EMAIL PROTECTED] wrote: Is there anyway I can tune MySQL to improve this operation? There's a fundamental problem with B-trees - inserts in a big B-tree can be very slow, cause it may require tree rebalancing. One workaround for this problem is partitioning. MySQL 5.1 will have built-in partitioning support, but the idea itself is very simple, and you can achieve the same effect by manually spreading data among tables. -- Alexey Polyakov If B-trees are part of the problem, you may be able to change your PRIMARY KEY to use a HASH index instead. If you are using a MyISAM table, then you can get non-blocking inserts if you have no deletion gaps in your data (simultaneous inserts and reads). Use the OPTIMIZE TABLE command to re-organize your existing table to eliminate the current deletion gaps. Those are the two ideas off the top of my head. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Effective-dating
--- Douglas Sims [EMAIL PROTECTED] wrote:
Does anyone know of a thorough discussion of effective dating on the
web, particularly with respect to MySQL, or have any opinions you
could share?
I've worked with effective-dated tables in MS SQL Server and never
been particularly awe-struck by how well it works. I can think of
three ways of doing it:
1) Store a Begin date and an End date for each row and then
reference the row with 'WHERE {transaction date} BETWEEN {begin} AND
END. This inevitably winds up with overlapping rows that shouldn't
be or gaps where you don't want them, and also requires an extra date
column, but the select queries are simplest. Also, what about
indexing the dates?
2) Store an Expires date with each row, but then to find the actual
row you have to do a subselect or some messy joins and I'm not at all
confident this will be optimized reasonably.
3) Store an Effective as of date with each row but this has
essentially the same problem as 2.
None of the SQL books on my shelf even mentions this, including
Jeremy Zawodny's Hi-Performance MySQL and the MySQL Reference
Manual.
This page is interesting but doesn't explain the different options
nor try to analyze which is best and under what circumstances:
http://
llamasery.com/forums/showthread.php?p=34945
Strangely enough, most of what I find by googling the topic
effective dating has to do with meeting girls efficiently - which
is also interesting, but outside the scope of this list and not
immediately relevant to the system I'm working on.
Douglas Sims
[EMAIL PROTECTED]
I have also heard of this style of design called chonological
database. Doing this Google returned some more relevant information:
http://www.google.com/search?num=100hl=enlr=q=chronological+effective+date+%2BdatabasebtnG=Search
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 09.04.2006 23:40 (+0100), Jorrit Kronjee wrote: You seem to be best off with a setup where you've got the MySQL5 UNIX socket disabled, MySQL5 bound to one specific IP address, MySQL4 listening on 127.0.0.1 and a simple port forwarding rule to MySQL4. I'm missing the part to connect to MySQL 4.0 via mysql4.mydomain and to MySQL 5.0 via mysql5.mydomain... But I'll try to do it by restricting access to the primary hostname/IP for now. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MUL when describe table
Hello, When I do a table describe, I see a MUL on a column I have marked as a foreign-key. I tried searching for MUL within the docs, but do not see a concise explanation of what that is? Anyone? Thanks, Scott
Re: Restrict MySQL server 4/5 to single IP
Yves Goergen wrote: On 09.04.2006 23:40 (+0100), Jorrit Kronjee wrote: You seem to be best off with a setup where you've got the MySQL5 UNIX socket disabled, MySQL5 bound to one specific IP address, MySQL4 listening on 127.0.0.1 and a simple port forwarding rule to MySQL4. I'm missing the part to connect to MySQL 4.0 via mysql4.mydomain and to MySQL 5.0 via mysql5.mydomain... But I'll try to do it by restricting access to the primary hostname/IP for now. Yves, I'm not entirely sure what you mean. Are `mysql4.mydomain' and `mysql5.mydomain' hostnames? - Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MUL when describe table
--- Scott Purcell [EMAIL PROTECTED] wrote: Hello, When I do a table describe, I see a MUL on a column I have marked as a foreign-key. I tried searching for MUL within the docs, but do not see a concise explanation of what that is? Anyone? Thanks, Scott It's there: http://dev.mysql.com/doc/refman/4.1/en/describe.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do if and elseif and other calculations
That did work very well, thank you. The DEFAULT value was 0. I am getting closer in my attempt to generate a leaderboard without php :) From: Gabriel PREDA [mailto:[EMAIL PROTECTED] Sent: Monday, April 10, 2006 7:16 PM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: Do if and elseif and other calculations You can do something like this: SET @diff = 0; SELECT count(*) AS number_of_holes, tour_player_score.strokes - tour_scorecard_hole.par AS overpar, IF(overpar0, @diff-(overpar*number_of_holes), ELSEVALUE) FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id GROUP BY overpar; And instead of ELSEVALUE you can insert another IF... There is also a CASE in MySQL... you can also use that one... What i don't see here is a DEFAULT value... do you have any ? -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Making result rows to one row
Best group member, Many of you probably do not understand the question; neither would I if someone ask me, so I will explain a little bit more. This is what I have: SELECT tps.strokes FROM tour_player_score tps join tour_scorecard_hole tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY tsh.hole_number; This generates this: +-+ | strokes | +-+ | 6 | | 4 | | 5 | | 3 | | 5 | | 4 | | 4 | | 3 | | 6 | +-+ I would like to retrieve it in one row instead of many rows with one per row. s1 s2 s3 s4 s5 s6 s7 s8 s9 6 4 5 3 5 4 4 3 6 Can this be done? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Making result rows to one row
Can you tell us more about the use case? Why does this need to be in a single row? Maybe instead of figuring out how to get this into a single row we could instead figure out how to solve the problem that requires it to be in a single row. -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Monday, April 10, 2006 10:45 AM To: mysql@lists.mysql.com Subject: Making result rows to one row Best group member, Many of you probably do not understand the question; neither would I if someone ask me, so I will explain a little bit more. This is what I have: SELECT tps.strokes FROM tour_player_score tps join tour_scorecard_hole tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY tsh.hole_number; This generates this: +-+ | strokes | +-+ | 6 | | 4 | | 5 | | 3 | | 5 | | 4 | | 4 | | 3 | | 6 | +-+ I would like to retrieve it in one row instead of many rows with one per row. s1 s2 s3 s4 s5 s6 s7 s8 s9 6 4 5 3 5 4 4 3 6 Can this be done? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is this? -- errno=2006 errmsg=Server gone
Hi Martin, all, Martin Olsson wrote: Hi, I get this weird error message: ErrNo: 2006 Error: MySQL server has gone away. What does it mean? I couldn't find anything useful on google and the error message isn't exactly verbose.. :) AIUI, this message is issued if the client gets an error reported while sending a command to the server. Again AIUI, this need not be a crash, it might also be the server having shut down in an orderly fashion while the client is still connected. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Making result rows to one row
Peter, Best group member, Many of you probably do not understand the question; neither would I if someone ask me, so I will explain a little bit more. This is what I have: SELECT tps.strokes FROM tour_player_score tps join tour_scorecard_hole tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY tsh.hole_number; This generates this: +-+ | strokes | +-+ | 6 | | 4 | | 5 | | 3 | | 5 | | 4 | | 4 | | 3 | | 6 | +-+ I would like to retrieve it in one row instead of many rows with one per row. s1 s2 s3 s4 s5 s6 s7 s8 s9 6 4 5 3 5 4 4 3 6 Can this be done? It can be done roughly with something like ... SELECT ..., GROUP CONCAT(LPAD(strokes,3,' ') SEPARATOR '') AS ' 1 2 3 4 5 6 7 8 9' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id WHERE tour_player_id=175 GROUP BY tsh.id or more thoroughly with a crosstab (eg http://www.artfulsoftware.com/queries.php#36) PB - Best regards, Peter Lauri No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.0/306 - Release Date: 4/9/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.0/306 - Release Date: 4/9/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 10.04.2006 18:32 (+0100), Jorrit Kronjee wrote: I'm not entirely sure what you mean. Are `mysql4.mydomain' and `mysql5.mydomain' hostnames? Yes, as I have explained earlier in this thread. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] This message represents the official view of the voices in my head. http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Network, linking members
2006/4/8, Philippe Poelvoorde [EMAIL PROTECTED]: Hi, 2006/4/8, Dan Buettner [EMAIL PROTECTED]: Seems like what you're looking for is a way to query your database more efficiently/quickly, and still find all links in either direction. I think the use of a UNION statement should allow this. Basically write your query twice, joining on id1 the first time and id2 the second time, with UNION in the middle. To select reciprocal friends of Dan (that is a friends relationship in both ways), I would do that : select p.name from friends f1, friends f2, people p where p.peopleid=f1.id1 and f1.id2=1 AND f1.id2=f2.id1 AND f1.id1=f2.id2 UNION select p.name from friends f1, friends f2, people p where p.peopleid=f1.id2 and f1.id1=1 AND f1.id1=f2.id2 AND f1.id2=f2.id1; +--+ | name | +--+ | Matt | +--+ But I don't think it's the best solution in term of performance :) Sorry, can anyone comment this query ? How would you find the list of symetric friend of Dan ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error with java
Can you post few lines of the code where you are making the connection? On 4/10/06, marco stagno [EMAIL PROTECTED] wrote: Wild stab in the dark here - Java I/O permissions not set correctly on your machine to allow it? If I recall, Java's sandbox feature means you have to supply a permissions file for I/O. Perhaps that file already exists on the other machine that works? I don't know so much Java, but I didn't changed anything in the 2 machine from the default installation (Java 1.5xx) if you can tell me more, I can check thank you again!! MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- http://chatter.mirislam.com/
Re: select all events from (today-N) days
Joerg: are you saying I need to compute the start time and end time in epoch values and use it in select count(*) from EVENT_DATA where utime start_time and utime end_time ? Can you provide some pointers and what to search on. I have searched and can't seem to find any leads on how to get the start_time and end_time values. Thanks Ravi --- Joerg Bruehe [EMAIL PROTECTED] wrote: Hi Ravi, all! Ravi Malghan wrote: Ok. I found something. But wondering if this is most efficient Events for yesterday: select count(*) from EVENT_DATA where FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() - INTERVAL 1 DAY); Events for last week select count(*) from EVENT_DATA where FROM_UNIXTIME(utime,'%U') = (DATE_FORMAT(CURDATE(),'%v')-1); TIA Ravi --- Ravi Malghan [EMAIL PROTECTED] wrote: Hi: I have a date/time field (utime) which has unix time in epoch time. I want to select events from yesterday and another statement for all events from previous week. No, that is not the most efficient way. If you have any sizable amount of data, you need an index to allow your where condition to be evaluated without accessing all records (also called table scan). For the efficient use of that index, you should ensure that the where condition is of the form column_value comparison_operator expression It does not matter whether expression is complicated, it needs to be computed only once, but column_value should just be the column name and not a function / expression using it. So what you need is select count(*) from EVENT_DATA where utime (seconds of your period start) Sorry, I lack the time to scan the manual for the correct expression to calculate that start value. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to pass parameters in MYSQL
Hi: I have a statement such as follows SELECT EVENT_DATA.dstport WHERE FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() - INTERVAL 1 DAY) ORDER BY EVENT_DATA.utime; Instead of using a value of 1 for interval, I want to pass parameters so it will ask for the value when the sql statement is run. I have tried searching in google and www.mysql.org and have not had any luck. Does mysql use different definitions? or can someone provide some pointers on the web which explains how to pass parameters with SQL statement. Thanks Ravi __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is this? -- errno=2006 errmsg=Server gone
--- Joerg Bruehe [EMAIL PROTECTED] wrote: Hi Martin, all, Martin Olsson wrote: Hi, I get this weird error message: ErrNo: 2006 Error: MySQL server has gone away. What does it mean? I couldn't find anything useful on google and the error message isn't exactly verbose.. :) AIUI, this message is issued if the client gets an error reported while sending a command to the server. Again AIUI, this need not be a crash, it might also be the server having shut down in an orderly fashion while the client is still connected. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com IIRC, this message can also occur whenever you transmit a packet larger than the server's max_allowed_packet setting. What were you doing when you got the message? Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Network, linking members
--- Philippe Poelvoorde [EMAIL PROTECTED] wrote: 2006/4/8, Philippe Poelvoorde [EMAIL PROTECTED]: Hi, 2006/4/8, Dan Buettner [EMAIL PROTECTED]: Seems like what you're looking for is a way to query your database more efficiently/quickly, and still find all links in either direction. I think the use of a UNION statement should allow this. Basically write your query twice, joining on id1 the first time and id2 the second time, with UNION in the middle. To select reciprocal friends of Dan (that is a friends relationship in both ways), I would do that : select p.name from friends f1, friends f2, people p where p.peopleid=f1.id1 and f1.id2=1 AND f1.id2=f2.id1 AND f1.id1=f2.id2 UNION select p.name from friends f1, friends f2, people p where p.peopleid=f1.id2 and f1.id1=1 AND f1.id1=f2.id2 AND f1.id2=f2.id1; +--+ | name | +--+ | Matt | +--+ But I don't think it's the best solution in term of performance :) Sorry, can anyone comment this query ? How would you find the list of symetric friend of Dan ? Assuming that we have this table pseudo-structure CREATE TABLE person ( id name ) CREATE TABLE friends ( id from_person_id to_person_id ) And the following data: person _ _ID_|_NAME___ 1 | Alpha 2 | Bravo 3 | Charlie 4 | Delta 5 | Echo friends __ _ID _|_FROM_|_TO__ 1 |1 | 1 1 |1 | 2 1 |1 | 3 1 |1 | 4 1 |1 | 5 1 |2 | 1 1 |3 | 1 1 |4 | 1 This represents a graph of Alpha being friends with all of the other people but only Bravo, Charlie, and Delta return the favor (symmetric friendships). The relationship with Echo is asymmetrical. A query to pick out only those relationships that are symmetrical would look like: SELECT p1.name, p2.name FROM person p1 INNER JOIN friends f1 on f1.from_person_id = p1.id INNER JOIN friends f2 on f2.to_person_id = p1.id AND f2.from_person_id = f1.to_person_id INNER JOIN person p2 ON p2.id = f1.to_person_id; There are many ways to make that faster but this is the general form of the query. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select all events from (today-N) days
Hi Ravi, all, Ravi Malghan wrote: Joerg: are you saying I need to compute the start time and end time in epoch values and use it in select count(*) from EVENT_DATA where utime start_time and utime end_time ? need to? This is not required in order for the command to work (at all), but IMNSHO this is the proper way to write an SQL statement to achieve good performance. (Mathematically spoken, it is not sufficient, but it may be necessary; and it definitely is portable to all SQL systems.) (Aside: Are sufficient and necessary the terms used when discussing mathematics and logic in English? Just curious.) Can you provide some pointers and what to search on. I have searched and can't seem to find any leads on how to get the start_time and end_time values. Currently, you do ... WHERE FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() - INTERVAL 1 DAY); IMO, you should do something like ... WHERE utime BETWEEN UNIX_TIMESTAMP (DATE_SUB (CURDATE(), INTERVAL 1 0:0:0 DAY_SECOND)) AND UNIX_TIMESTAMP (DATE_SUB (CURDATE(), INTERVAL 0 SECOND)) DATE_SUB() converts from DATE to DATETIME if the interval has a component with finer granularity than days, and it maps a date to its beginning (00:00:00), according to the manual. Disclaimers: 1) not tested. 2) I cannot guarantee that a 0 second interval is not optimized away. If that happens, you may need to use 1 second, and the result will be inexact around midnight. 3) Note the hint in the manual about the lossy conversion, especially at the start and end of daylight saving time. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select all events from (today-N) days
--- Ravi Malghan [EMAIL PROTECTED] wrote: Joerg: are you saying I need to compute the start time and end time in epoch values and use it in select count(*) from EVENT_DATA where utime start_time and utime end_time ? Can you provide some pointers and what to search on. I have searched and can't seem to find any leads on how to get the start_time and end_time values. Thanks Ravi --- Joerg Bruehe [EMAIL PROTECTED] wrote: Hi Ravi, all! Ravi Malghan wrote: Ok. I found something. But wondering if this is most efficient Events for yesterday: select count(*) from EVENT_DATA where FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() - INTERVAL 1 DAY); Events for last week select count(*) from EVENT_DATA where FROM_UNIXTIME(utime,'%U') = (DATE_FORMAT(CURDATE(),'%v')-1); TIA Ravi --- Ravi Malghan [EMAIL PROTECTED] wrote: Hi: I have a date/time field (utime) which has unix time in epoch time. I want to select events from yesterday and another statement for all events from previous week. No, that is not the most efficient way. If you have any sizable amount of data, you need an index to allow your where condition to be evaluated without accessing all records (also called table scan). For the efficient use of that index, you should ensure that the where condition is of the form column_value comparison_operator expression It does not matter whether expression is complicated, it needs to be computed only once, but column_value should just be the column name and not a function / expression using it. So what you need is select count(*) from EVENT_DATA where utime (seconds of your period start) Sorry, I lack the time to scan the manual for the correct expression to calculate that start value. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com There is a whole section on date/time functions: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html I think the one you need is called UNIX_TIMESTAMP() Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to pass parameters in MYSQL
--- Ravi Malghan [EMAIL PROTECTED] wrote: Hi: I have a statement such as follows SELECT EVENT_DATA.dstport WHERE FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() - INTERVAL 1 DAY) ORDER BY EVENT_DATA.utime; Instead of using a value of 1 for interval, I want to pass parameters so it will ask for the value when the sql statement is run. I have tried searching in google and www.mysql.org and have not had any luck. Does mysql use different definitions? or can someone = Different than.(what)??? provide some pointers on the web which explains how to pass parameters with SQL statement. Thanks Ravi In order to have parameters, you have to have do things procedurally. That means that to have replaceable values in your SQL (parameterized queries and statements) you have to use a STORED PROCEDURE, a FUNCTION, a prepared statement, or a user-defined function to encapsulate your code. All of those features are new to MySQL as of v5.0. Also, if your utime field is already a unix_timestamp value why are you converting it to a date in order to compare it to the results of another calculation? In order to possibly use an index, you need to leave it as a unix_timestamp value and convert your calculation to match ... WHERE utime = UNIX_TIMESTAMP(CURRDATE() - interval 1 day) ... That gets everything for the last 24 hours (exactly). The conversions all belong on one side of the comparison. Your fields should not be part of a conversion or function if you ever hope to use an index during the lookup phase. http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select all events from (today-N) days
Joerg: thank you. That worked and much faster too. WHERE EVENT_DATA.utime UNIX_TIMESTAMP(SUBDATE(CURDATE(), 5)) AND EVENT_DATA.utime UNIX_TIMESTAMP(SUBDATE(CURDATE(), 4)) Ravi --- Joerg Bruehe [EMAIL PROTECTED] wrote: Hi Ravi, all, Ravi Malghan wrote: Joerg: are you saying I need to compute the start time and end time in epoch values and use it in select count(*) from EVENT_DATA where utime start_time and utime end_time ? need to? This is not required in order for the command to work (at all), but IMNSHO this is the proper way to write an SQL statement to achieve good performance. (Mathematically spoken, it is not sufficient, but it may be necessary; and it definitely is portable to all SQL systems.) (Aside: Are sufficient and necessary the terms used when discussing mathematics and logic in English? Just curious.) Can you provide some pointers and what to search on. I have searched and can't seem to find any leads on how to get the start_time and end_time values. Currently, you do ... WHERE FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() - INTERVAL 1 DAY); IMO, you should do something like ... WHERE utime BETWEEN UNIX_TIMESTAMP (DATE_SUB (CURDATE(), INTERVAL 1 0:0:0 DAY_SECOND)) AND UNIX_TIMESTAMP (DATE_SUB (CURDATE(), INTERVAL 0 SECOND)) DATE_SUB() converts from DATE to DATETIME if the interval has a component with finer granularity than days, and it maps a date to its beginning (00:00:00), according to the manual. Disclaimers: 1) not tested. 2) I cannot guarantee that a 0 second interval is not optimized away. If that happens, you may need to use 1 second, and the result will be inexact around midnight. 3) Note the hint in the manual about the lossy conversion, especially at the start and end of daylight saving time. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to pass parameters in MYSQL
Ravi Malghan wrote: Hi: I have a statement such as follows SELECT EVENT_DATA.dstport WHERE FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() - INTERVAL 1 DAY) ORDER BY EVENT_DATA.utime; Instead of using a value of 1 for interval, I want to pass parameters so it will ask for the value when the sql statement is run. You can put it in a prepared statement (http://dev.mysql.com/doc/refman/5.1/en/sqlps.html), eg set @sql = SELECT EVENT_DATA.dstport WHEREFROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() -INTERVAL ? DAY) ORDER BY EVENT_DATA.utime; PREPARE stmt FROM @sql; set @x = 7; EXECUTE stmt USING @x; DROP PREPARE stmt; and you can put all that in a stored procedure, passing in the number of days as a param. PB I have tried searching in google and www.mysql.org and have not had any luck. Does mysql use different definitions? or can someone provide some pointers on the web which explains how to pass parameters with SQL statement. Thanks Ravi __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.0/306 - Release Date: 4/9/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
counting keywords
Hi all, I am trying to find out whether it is possible to return the count of keywords found in a text field, does anyone know? For example; ColdFusion or Java is entered in the search string and 20 records are found that match, I need to count how many times ColdFusion and Java appears in each match, add those two and than sort descending on that total. I was actually using verity for the search, but the client insists he sees the number of keywords found, which Verity does not do. The text searched are Résumé's, I initially thought that MySQL could search the résumé's when stored as binary data, but I was wrong, so I now have the CV's converted to HTML and then store them in the DB as VARCHAR Any help would be much appreciated, I am having a hard time coming from a MS SQL background ;-) Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au/ http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 * Web Design and Development * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years!
Re: counting keywords
At 7:37 AM +1000 4/11/06, Taco Fleur wrote:
Hi all,
I am trying to find out whether it is possible to return the count of
keywords found in a text field, does anyone know?
For example; ColdFusion or Java is entered in the search string and 20
records are found that match, I need to count how many times ColdFusion
and Java appears in each match, add those two and than sort descending on
that total.
I was actually using verity for the search, but the client insists he sees
the number of keywords found, which Verity does not do.
The text searched are Résumé's, I initially thought that MySQL could search
the résumé's when stored as binary data, but I was wrong, so I now have the
CV's converted to HTML and then store them in the DB as VARCHAR
Any help would be much appreciated, I am having a hard time coming from a MS
SQL background ;-)
There's no function that I know of to do that directly; however, you
could do something like this:
select
(length(your_text_field)-length(replace(your_text_field,
'coldfusion','')))/length('coldfusion')
as wordcount
from
your_table
That is, it removes all instances of 'coldfusion' from your string,
gets the difference in length from the unaltered string, and divides
that by the number of characters in your search string. I've used
this method several times.
Of course, if you want to avoid matching against terms like
'javalike' or 'coldfusionista' then you've gotta do some additional
checking, for example:
select
(length(your_text_field)-length(replace(concat('
',your_text_field,' '), ' coldfusion ','')))/length(' coldfusion ')
as wordcount
from
your_table
See
http://dev.mysql.com/doc/refman/4.1/en/string-functions.html
for more info.
If you're doing this alot, it might be more efficient to build a word
index table like:
wordchar(32) not null # or whatever your max word
length is likely to be
word_count integer unsigned not null
document_id integer unsigned not null
where document_id is a foreign key pointing at the table containing
your fulltext. This would be easier to extend to handle synonym
handling too, and you could do all the suffix handling/stemming you
need (eg; to take care of plurals). I've done something like that as
well, and included an extra field for the metaphone version of the
word, to match approximate spellings. If the text fields were all in
plain text, you could even include character positions like
word char(32) not null
word_position integer unsigned not null
document_id integer unsigned not null
then you could get word counts by doing a select count(word).
steve
Kind regards,
Taco Fleur
Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox http://www.pacificfox.com.au/ http://www.pacificfox.com.au an
industry leader with commercial IT experience since 1994
*
Web Design and Development
*
SMS Solutions, including developer API
*
Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!
--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem running multi master replication
Shawn Green wrote: I understand now. You have 3 slave server processes replicating independently from their own separate master servers. You have a fourth MySQL server process that shares the same data files as your 3 slaves but it does not know when replication occurs and when it hasn't (for MANY reasons) so it doesn't see the updated information. First rule: Do NOT share data files between server processes. Nothing should directly interact with a datafile other than the server to which it belongs. This includes other server processes as well as direct user actions or actions from a third-party program. There are structures and procedures that each MySQL process maintains that assumes that each process has exclusive control over every data file it is managing. Sharing files between two processes is highly discouraged. However, if you are using 5.0 or newer, you can FEDERATE the files from Servers 2-4 to Server 1. That way #1 never needs to directly read from those files and you will avoid the myriad of problems that your setup can create. Your data will always be up-to-date and you won't have to worry about file caching, table caching, index caching, file locking, read/write contentions or any of those other problems. Please try shifting your table definitions on Server1 from direct-read to FEDERATED. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I know it was out of standard, just trying to find other methods than scheduling ... but your idea about FEDERATED tables sound interesting .. i've only read a little about it, but i think it should do the tricks... one problem though, i can not switch to 5 easily since there are many joins in the client side that would broke (mix of LEFT JOIN and ',' operator) and it would take much effort to replace the codes (although it was on the road map of the application) thanks for your help Shawn :) best regards - Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Making result rows to one row
-Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Monday, April 10, 2006 10:45 AM To: mysql@lists.mysql.com Subject: Making result rows to one row Best group member, Many of you probably do not understand the question; neither would I if someone ask me, so I will explain a little bit more. This is what I have: SELECT tps.strokes FROM tour_player_score tps join tour_scorecard_hole tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY tsh.hole_number; This generates this: +-+ | strokes | +-+ | 6 | | 4 | | 5 | | 3 | | 5 | | 4 | | 4 | | 3 | | 6 | +-+ I would like to retrieve it in one row instead of many rows with one per row. s1 s2 s3 s4 s5 s6 s7 s8 s9 6 4 5 3 5 4 4 3 6 Can this be done? Best regards, Peter Lauri -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 12:47 AM To: Peter Lauri; mysql@lists.mysql.com Subject: RE: Making result rows to one row Can you tell us more about the use case? Why does this need to be in a single row? Maybe instead of figuring out how to get this into a single row we could instead figure out how to solve the problem that requires it to be in a single row. * My goal is to create a leaderboard for golf in just one query. I want one row to be for one player. The final result will be something like this: | Peter Lauri 4 3 4 6 5 4 2 5 3 36 Tiger Woods 5 5 4 4 4 4 4 2 4 37 | The information for the rows I get with this queries: //Hole scores (one resultset with 9 rows) SELECT tps.strokes FROM tour_player_score tps join tour_scorecard_hole tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY tsh.hole_number; //Sum of all holes (one resultsit with 1 rows) SELECT sum(tps.strokes) FROM tour_player_score tps join tour_scorecard_hole tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY tsh.hole_number; Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Student question answer schema
Does anyone happen to know where a basic schema for tracking questions and answers from tests that a student has completed. I don't know why I am having difficulty with this, but I can't seem to figure out how to set up the tables correctly to store this information. The basics N number of students N number of learning modules Each learning module has multiple questions Each question has multiple answers. I need to figure out the tables to track when a student has taken a module (easy)and which questions they got wrong in each module and then be able to run various kinds of reports on questions that students got wrong in various ways. It seems like this should be simple, but I'm struggling with it. Does anyone know where an example of this type of schema would be? Thanks for your help in advance! -Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for advice on how to store and query some data
Hi, I'm storing data against a bunch of people and want to track how it changes. So, I have a person table where everyone has a person ID and a results table a bit like this: | personID | classification | date | | 1| 0 | 2005-11-10 | | 2| 3 | 2005-11-10 | | 3| 1 | 2005-11-10 | | 4| 0 | 2005-11-10 | | 1| 3 | 2005-12-01 | | 4| 2 | 2005-12-03 | | 1| 2 | 2005-12-23 | | 5| 1 | 2006-01-03 | | 2| 2 | 2006-12-03 | This lets me see how things change as a pattern, for example comparing a a SELECT classification WHERE DATE '2006-01-01' GROUP BY classification and comparing it to: SELECT classification WHERE DATE = '2006-01-01' AND DATE = '2006-01-31' GROUP BY classification But is there a way I can select every personID's most recently expressed preference? I hope this makes sense - in the table above, person 1 had a classification of 0 at 10th November, but this changed to 2 on 23rd Decembner. Can I write a query to select personID once together with their latest preference, or indeed their preference as expressed at a specific point in time? Thanks as ever for any suggestions. Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Student question answer schema
Haven't you answered your own question? From my reading of this question, there are four entity tables (student, module, question, answer) and three or more relation tables (association tables) student-module, module-question, question-answer. There are probably two of the last one - one which holds correct answers (the test key as it were) question-answer and one which holds students actual answers (the fact record) student-module-question-answer. Which relation(s) you need depends on things like whether you can take a module without answering any question(s). I.e., only the fact record is necessary if you must answer at least one question to be interesting. Seems almost like a text book example (The solution is left as an exercise for the reader ;-) Have I missed something? Tim -Original Message- From: Brian Menke [mailto:[EMAIL PROTECTED] Sent: Monday, April 10, 2006 10:38 PM To: mysql@lists.mysql.com Subject: Student question answer schema Does anyone happen to know where a basic schema for tracking questions and answers from tests that a student has completed. I don't know why I am having difficulty with this, but I can't seem to figure out how to set up the tables correctly to store this information. The basics N number of students N number of learning modules Each learning module has multiple questions Each question has multiple answers. I need to figure out the tables to track when a student has taken a module (easy)and which questions they got wrong in each module and then be able to run various kinds of reports on questions that students got wrong in various ways. It seems like this should be simple, but I'm struggling with it. Does anyone know where an example of this type of schema would be? Thanks for your help in advance! -Brian -- 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: counting keywords
Thanks Steve,
Much appreciated, I was hoping there was something a little simpler, but I
will have a go at it.
Anyway of doing this with RegEx, would that simplify things?
Kind regards,
Taco Fleur
Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox http://www.pacificfox.com.au an industry leader with commercial
IT experience since 1994
*
Web Design and Development
*
SMS Solutions, including developer API
*
Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!
-Original Message-
From: Steve Edberg [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 11 April 2006 9:50 AM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: counting keywords
At 7:37 AM +1000 4/11/06, Taco Fleur wrote:
Hi all,
I am trying to find out whether it is possible to return the count of
keywords found in a text field, does anyone know?
For example; ColdFusion or Java is entered in the search string and
20 records are found that match, I need to count how many times
ColdFusion
and Java appears in each match, add those two and than sort
descending on that total.
I was actually using verity for the search, but the client insists he
sees the number of keywords found, which Verity does not do.
The text searched are Résumé's, I initially thought that MySQL could
search the résumé's when stored as binary data, but I was wrong, so I
now have the CV's converted to HTML and then store them in the DB as
VARCHAR
Any help would be much appreciated, I am having a hard time coming from
a MS SQL background ;-)
There's no function that I know of to do that directly; however, you could
do something like this:
select
(length(your_text_field)-length(replace(your_text_field,
'coldfusion','')))/length('coldfusion')
as wordcount
from
your_table
That is, it removes all instances of 'coldfusion' from your string, gets
the difference in length from the unaltered string, and divides that by the
number of characters in your search string. I've used this method several
times.
Of course, if you want to avoid matching against terms like 'javalike' or
'coldfusionista' then you've gotta do some additional checking, for example:
select
(length(your_text_field)-length(replace(concat('
',your_text_field,' '), ' coldfusion ','')))/length(' coldfusion ')
as wordcount
from
your_table
See
http://dev.mysql.com/doc/refman/4.1/en/string-functions.html
for more info.
If you're doing this alot, it might be more efficient to build a word index
table like:
wordchar(32) not null # or whatever your max word
length is likely to be
word_count integer unsigned not null
document_id integer unsigned not null
where document_id is a foreign key pointing at the table containing your
fulltext. This would be easier to extend to handle synonym handling too, and
you could do all the suffix handling/stemming you need (eg; to take care of
plurals). I've done something like that as well, and included an extra field
for the metaphone version of the word, to match approximate spellings. If
the text fields were all in plain text, you could even include character
positions like
word char(32) not null
word_position integer unsigned not null
document_id integer unsigned not null
then you could get word counts by doing a select count(word).
steve
Kind regards,
Taco Fleur
Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox
http://www.pacificfox.com.au/ http://www.pacificfox.com.au an
industry leader with commercial IT experience since 1994
*
Web Design and Development
*
SMS Solutions, including developer API
*
Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!
--
+--- my people are the people of the dessert,
+---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork
++
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
~performance issues~
Hi, I have a master/slave setup ( replication enabled ) for mysql in two different geographic locations ( one master/slave set up in each location). In one location i have configured the sync_binlog=1 . And the other location does not have the same. My problem is, when i run similar update processes on both the master servers, the server with sync_binlog=1 is very slower in terms of completing the update query as compared to the machine having sync_binlog=0. Is that a cause for slow performance ? Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql on tmpfs
Atle Veka wrote: Anyone have any experience to share about running mysql on a linux tmpfs (using memory)? tmpfs is swappable. I would use ramfs which is not swappable. For us it's worked out pretty well and is normally operating lightning fast compared to an identical SCSI based system. However, even though there is plenty of free memory linux makes weird decisions from time to time, temporarily killing performance, swapping in/out to make room. Without more information it's difficult to say what's causing it. How do you check swapping activity? Werner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~performance issues~
Hi Abdul, When sync_binlog is set to 1, innodb fsyncs the binary log to disk after every single write to binary log, but not in the case of sync_binlog=0. From MySQL manual: snip If the value of this variable is positive, the MySQL server synchronizes its binary log to disk (fdatasync()) after every sync_binlog writes to this binary log. Note that there is one write to the binary log per statement if in autocommit mode, and otherwise one write per transaction. The default value is 0 which does no sync'ing to disk. A value of 1 is the safest choice, because in case of crash you lose at most one statement/transaction from the binary log; but it is also the slowest choice (unless the disk has a battery-backed cache, which makes sync'ing very fast). This variable was added in MySQL 4.1.3. /snip --Ravi On Tuesday 11 April 2006 10:22, Mohammed Abdul Azeem wrote: Hi, I have a master/slave setup ( replication enabled ) for mysql in two different geographic locations ( one master/slave set up in each location). In one location i have configured the sync_binlog=1 . And the other location does not have the same. My problem is, when i run similar update processes on both the master servers, the server with sync_binlog=1 is very slower in terms of completing the update query as compared to the machine having sync_binlog=0. Is that a cause for slow performance ? Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~performance issues~
Hi Ravi, Since the sync'ing is done to disk with sync_binlog=1, the update queries to server are slower compared to the server having sync_binlog=0 rite ? Thanks, Abdul. On Tue, 2006-04-11 at 10:51 +0530, Ravi Prasad LR wrote: Hi Abdul, When sync_binlog is set to 1, innodb fsyncs the binary log to disk after every single write to binary log, but not in the case of sync_binlog=0. From MySQL manual: snip If the value of this variable is positive, the MySQL server synchronizes its binary log to disk (fdatasync()) after every sync_binlog writes to this binary log. Note that there is one write to the binary log per statement if in autocommit mode, and otherwise one write per transaction. The default value is 0 which does no sync'ing to disk. A value of 1 is the safest choice, because in case of crash you lose at most one statement/transaction from the binary log; but it is also the slowest choice (unless the disk has a battery-backed cache, which makes sync'ing very fast). This variable was added in MySQL 4.1.3. /snip --Ravi On Tuesday 11 April 2006 10:22, Mohammed Abdul Azeem wrote: Hi, I have a master/slave setup ( replication enabled ) for mysql in two different geographic locations ( one master/slave set up in each location). In one location i have configured the sync_binlog=1 . And the other location does not have the same. My problem is, when i run similar update processes on both the master servers, the server with sync_binlog=1 is very slower in terms of completing the update query as compared to the machine having sync_binlog=0. Is that a cause for slow performance ? Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Any limits on Database Size?
Hi All, We used to use Interbase which required a new file to be assigned for every 4 gig of data stored in a DB. Is there any issues like this in mySQL? Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Any limits on Database Size?
Hi Jim - see this for your answers. 1.4.4. How Large MySQL Tables Can Be http://dev.mysql.com/doc/refman/5.0/en/table-size.html Regards Keith In theory, theory and practice are the same; in practice they are not. On Tue, 11 Apr 2006, Jim wrote: To: mysql@lists.mysql.com From: Jim [EMAIL PROTECTED] Subject: Any limits on Database Size? Hi All, We used to use Interbase which required a new file to be assigned for every 4 gig of data stored in a DB. Is there any issues like this in mySQL? Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
