RE: Newbie question - Hopefully not too stupid!
Hi, Have you tried: SELECT Table1.* FROM Table1 , Table2 WHERE Table1.id = Table2.id AND Table2.offon = 'on' > Hello all! > > Here is what I am trying to do. > I am trying to find only records > that all have the same value for a > specific field in a related table. > > A simplified example: > Table1 has 2 fields - id, name > Table2 has 2 fields - id, offon > > Given these sets: > > Table1 > id name > --- > 1 Red > 2 Blue > 3 Green > > Table2 > id offon > > 1 off > 1 on > 2 on > 2 on > 3 off > 3 off > > I want to be able to select > only the Colors that have only > ALL ons relating to them. I > do not want a combination of > off, off or on, off etc... > > The only way I have been able > to accomplish this was to count > the number of instances for each > table and save them in temp tables > and then compare the results. There > must be a better way to do this! > > Also if anyone would like to recommend > an SQL reference or tutororial I would > love to hear about it. > > Thanks in advance. > -Davin > > __ > Do You Yahoo!? > Yahoo! Auctions - buy the things you want at great prices > http://auctions.yahoo.com/ > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: URGENT - daemon dying frequently
I know this is slightly off-topic but it's related to changing the number of file descriptors on FreeBSD: - FreeBSD by Torsten Sturm How do I check my maximum filedescriptors? Do sysctl -a and look for the value of kern.maxfilesperproc. How do I increase them? sysctl -w kern.maxfiles= sysctl -w kern.maxfilesperproc= Warning: You probably want maxfiles > maxfilesperproc if you're going to be pushing the limit. What is the upper limit? I don't think there is a formal upper limit inside the kernel. All the data structures are dynamically allocated. In practice there might be unintended metaphenomena (kernel spending too much time searching tables, for example). -- -- The site is here: http://merlin.bmk.hu/Squid/FAQ-11.html > Running mysql 3.22.32-log on Apache virtual server (FreeBSD) > > I can restart my mysql daemon with ./bin/safe_mysqld - but it > only runs for a few hours before dying. Following hints from > both the manual and the list, I've tried several things... > > I've deleted my mysql database and manually re-installed all > the tables (instead of using the mysql_install_db script which > wanted me to do a make compile) > > I've looked at the error log, and see nothing that shows why > it is dying ( though when it starts it does have two messages - > could they be related?) > > Warning: setrlimit couldn't increase number of open files to > more than 164 > Warning: Changed limits: max_connections: 30 table_cache: 62 > > I've had the logging on but see no queries that should be the > problem. > > Nothing in the cron file > > Any suggestions? - 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: URGENT - daemon dying frequently
Looks like you're running out of the file descriptor to handle the connection etc. I'm not really a FreeBSD person but here's what I've found on this site: http://ircache.nlanr.net/Polygraph/Tips/FreeBSD-3.3/ Try increasing max file descriptors in /sys/conf/param.c #define MAXFILES (16384) This *could* the problem try it and see how you go with it. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, April 10, 2001 9:55 AM > To: [EMAIL PROTECTED] > Subject: URGENT - daemon dying frequently > > > Running mysql 3.22.32-log on Apache virtual server (FreeBSD) > > I can restart my mysql daemon with ./bin/safe_mysqld - but it > only runs for a few hours before dying. Following hints from > both the manual and the list, I've tried several things... > > I've deleted my mysql database and manually re-installed all > the tables (instead of using the mysql_install_db script which > wanted me to do a make compile) > > I've looked at the error log, and see nothing that shows why > it is dying ( though when it starts it does have two messages - > could they be related?) > > Warning: setrlimit couldn't increase number of open files to > more than 164 > Warning: Changed limits: max_connections: 30 table_cache: 62 > > I've had the logging on but see no queries that should be the > problem. > > Nothing in the cron file > > Any suggestions? > > > - > 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: Trojan Horse Virus in MySQL?
Where did you download your MySQL executable from?. Did you download it from MySQL official site or other Mirrors? If you download it from the mirror (or worst from the Official site) and got the Virus then you should let the MySQL Admin know as this is a pretty serious problem. > I've had MySQL server 3.23.25 installed at first on my > Windows2000 machine. > Then Trojan Remover 4.17 some how suspected a "Sky Dance > 3.03" trojan horse > virus in \mysql\bin\mysqlshow.exe. > > Then I download and installed the newer version 3.23.35a > today. There are 2 > more executables that are suspected to have the same virus: > mysqlbinlog.exe > and mysqlimport.exe. Trojan Remover simply renamed these > files so they > cannot be run. > > Do these executables really contain trojan horse virus? Or > it's just a mistake by Trojan Remover? > > - 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: Is sequencing possible in Mysql?
Basically you'd have a DB set up like this: hits_table main_table +--+ +-+ | id | (long int) <- | id | +--+ +-+ | num_hits| (long int)| foobar | +--+ +-+ | date_created | (date/time) | bar | +--+ optional! :)+-+ So when you want to create a new record you'd do: 1) Lock the table "hits_table" 2) Check to see if the ID already exists in the hits_table SELECT * FROM hits_table WHERE id = (your id here) If Id exists, then just update UPDATE hits_table SET num_hits = num_hits + 1 WHERE id = (your id) else INSERT INTO hits_table(id, num_hits) VALUES (123, 1); end if Optionally when you check to see if the ID exists, you might want to check for today's date as well. That is if you wanted to keep the histical records of all your hits. This will come in handy when you do your reporting ie you can get a break down of all hits per day, month, year etc etc. So do that your SQL would be something like SELECT * FROM hits_table WHERE id = (your id here) AND Date(date_created) = CURRDate() If Id exists, then just update UPDATE hits_table SET num_hits = num_hits + 1 WHERE id = (your id) AND TO_DAYS(date_created) = TO_DAYS(NOW()); else INSERT INTO hits_table(id, num_hits, date_created) VALUES (123, 1, NOW()); end if 3) Unlock the table "hits_table" HTH > Opec, > Thanks for the response. I will lock the table. That > would be important > because I have 3 different websites pulling the same info. > Do you have an > example that would help me know how to get started setting > that up or a link > perhaps? > Thanks a ton. > Alan > - 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: Is sequencing possible in Mysql?
Hi, Yes that would be a better solution than to keep inserting a new record. However, you'll have to be careful with the concurrent update of the 2nd field ie if 2 process tries to update the same field at the same time etc. So locking table before update would help. > I have a table that has 2 fields. 1 is an auto-incrementing > field the other > is a profile id that is joined to some other tables to > track how often a > record was accessed. The way we are doing it is that each > time a record is > accessed we also update this table with the profile id and it > auto-increments a new record. this is working for us now, > but I am worried > about scale-ability. Right now to find out how many times a > record was > accessed we just count how many times that id is in the > table. After a month > and a half 95,000 records. > > This has been causing the database to crash and hang, > taking up 98% > resourses... you get the idea. So is it possible to have > two fields with the > profile_id be the primary key and the second be updated by > the database with > a +1 > > > > - > 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: Keeping MySQL from listening to ports to connections
Hi, As far as I know you can make MySQL listen for Unix Socket connection (as well as TCP/IP connection). As to how much more secure Unix socket is to TCP/IP I really can't tell you. But again AFAIK, to connect to Unix socket you have to be on the same machine as MySQL server. So your Apps would have to be running on the same box as MySQL. Check the manual under Configuration and Installation section on Howto set Unix socket up. HTH > -Original Message- > From: Andrew L. Matthews [mailto:[EMAIL PROTECTED]] > Sent: Friday, February 23, 2001 9:41 AM > To: [EMAIL PROTECTED] > Subject: Keeping MySQL from listening to ports to connections > > > Hello. I'm designing a MySQL server, but I want all > connections to MySQL to > be made by a server app. In other words, users communicate > with the app > through a port, and the app communicates with MySQL. The > whole point of > trying to do this is to keep MySQL from listening for > connections on a port, > and thereby eliminate a potential denial of service attack. > Is there any > way to set up MySQL so it communicates through some channel > other than a > port? Is there any way to keep MySQL from effectively listening for > connections on any ports, other than setting up a firewall? > > > > - > 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: XML support under mySQL
*big snip* I agree with Cal, the XML module should really be sperated from the RDMB. XML is really great but, lets face it not everyone is going to use it so why force it down thier troat?. The really great thing about Open Source is that you do have a choice (unlike MS , Oracle). :) You have to choice to install external XML modules if you wish, if not why would you use it? I'm sure if you write the XML modules as an extension to MySQL in C or C++, it'll be just as fast as if it is built in. Not to maintion the fact that it'll be far easier of MySQL developer to put in other really "useful" RDMB related features like ForeinKeys etc etc. instead of "cool" but not critical features XML. And the code base for MySQL wouldn't be bloated either which means we as the users won't have to download 200MB RDMB servers :):) My $0.02 > But you've yet to make a case for extending a database engine to do > something it's not originally designed to do and something > that I argue does > not belong in a RDBMS engine. First, while I agree that XML > is a great > solution for 2 applications to exchange data, it is not a - 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: Display information
> select id,lot_id,lot_type from scrap_table > (part I need help with) >order by id limit 10; > > which should give me records 1932 - 1942 like this... > > +--+--+--+ > | id | lot_id | lot_type | > +--+--+--+ > | 1942 | 0034906 | F| > | 1941 | 0100308 | F| > | 1940 | 0036309 | S| > | 1939 | 0033503 | F| > | 1938 | 0034108E | D| > | 1937 | 0034712 | F| > | 1936 | 0029404 | F| > | 1935 | 0032706 | F| > | 1934 | 0029404 | F| > | 1933 | 0100307 | F| > | 1932 | 0100404 | F| > +--+--+--+ If you simply needed the exact result here's the query SELECT id,lot_id,lot_type FROM scrap_table WHERE id BETWEEN 1932 AND 1942 ORDER BY id DESC - 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
