Re: Small select question...
on 3/20/01 1:20 PM, Bryan Coon at [EMAIL PROTECTED] wrote: Sorry for the ambiguity, I was trying to make a really generic select statement. I just meant that if 'where a = A.a' has no matches, but 'where b = B.b' or 'where c = C.c' does match, the query returns what did match, and just returns null where it didnt. I will look at the CASE WHEN...THEN. How is this not just: ... Where a = A.a or b = B.b or c=C.c ... Thanks, Geoff - 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
Mac OS X Binary libmysqlclient.a problem
I installed 3.23.27 for Mac OS X from the binaries. When installing PHP, I ran into problems with duplicate symbol names between libmysqlclient.a and System.framework. I also got a similar error building DBD::mysql. In both cases, the duplicate symbols were strto* functions. I built PHP with built-in mysql support to resolve it. When I built DBD::mysql and the problem came up again, I decided to try fixing it. I removed strto.o from libmysqlclient.a (along with a few other modules that ranlib complained were empty) and it built properly, tested successfully and seems to work properly. Is this a problem with the binary distribution, or am I missing something? Thanks, Geoff - 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: Mysql Dump
on 3/13/01 12:41 AM, Gilles Dumangin at [EMAIL PROTECTED] wrote: I would like to extract the contents of a database to use it in a ASCII format. It is a addresses database and I want to use it in Excel. I have tried Mysqldump but it gives me the CREATE and INSERT statements all the time. SELECT ... INTO OUTFILE path to nonexistent file on server Will put the data into the file specified in a tab seperated text format. There are other options detailed in the manual. Bear in mind that the file is placed on the server, not the client, and that mysqld must have write permission on the directory you're putting it in. Hope this helps, Geoff - 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: Removing duplicates from a table
on 3/8/01 3:40 PM, Scott Wang at [EMAIL PROTECTED] wrote: select distinct addr from tablename outfile filename.txt But how can I write the data to another table or even replace the information in the table with the output info? insert into another table (addr) select distinct addr from tablename You would probably have to go to a temp table first, then delete from the original table and insert from the temp table back into the original...but someone else may know better! Thanks, Geoff - 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: desperate for help...
on 3/6/01 9:35 PM, Chris Toth at [EMAIL PROTECTED] wrote: Ok, I've been battling this SELECT statement for the better part of the day. The SELECT statement is this: SELECT DISTINCT request.id AS requestid, request.date, request.type, request.status, faculty.f_name, faculty.l_name, action.id AS actionid, faculty.id AS facultyid FROM faculty, request, action WHERE request.id=action.request_id AND request.requested_by=faculty.id AND request.status=0; And the results are here: http://www.geology.ohio-state.edu/test/rfatest/rfaadmin.php?closed=Closed Now, if you look at the table, at the far left cell under RFA#, you'll notice that they are all the same (2). That is because there were 4 actions performed on RFA # 2. However, I only need for the column to be displayed once. When the user clicks under 'last action' then they can see all 4 actions. But for this table, I just need to show the data once. So, could any kind soul help me out? What do you get if you throw in a "group by request.id"? I have used group by to get a sort of "distinct on one column" behavior before with success, but I'm not sure of all the issues involved. Thanks, Geoff - 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: mysqldump question
on 3/7/01 6:39 PM, Cindy at [EMAIL PROTECTED] wrote: How would I copy ONE table from a database over to another? I used this last time: mysqldump -h mysql.io.com -u DB1 -p --opt DB1 ! backup-file.sql mysql -h mysql.io.com -u DB2 -p DB2 backup-file.sql But it copies the whole shebang (all tables in DB1). The situation I have now is that I use DB2 (after previously using DB1 up to two weeks ago), but this afternoon, I dropped one of the tables by accident. Call it Table1. Table1 is now filled with current entries, but is missing all its old entries, which can be found in the copy of Table1 in DB1. I would like to *add* (not replace) all the entries in Table1 of DB1 to Table1 of DB1. I'm guessing this would involve using mysql dump to get the copy of Table1 out of DB1 and then into DB2 under a new name, and then use some command within mysql's command line interface to add the rows from the old table to the current one? Some kind of Merge function? If they're on the same server, try: insert into DB2.table1 select * from DB1.table1 Otherwise, consider selecting INTO OUTFILE on DB1 and then using mysqlimport or LOAD INFILE to bring it in to DB2. Geoff - 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: What is Errcode 2?
on 3/8/01 12:13 AM, Jennifer at [EMAIL PROTECTED] wrote: I'm trying to do a SELECT * INTO OUTFILE and I get an error saying that it can't create the file and Errcode 2. I've tried searching the archives, but for some reason it keeps crashing my browser. On my system (Mac OS X) errcode 2 in this context means a directory somewhere in your path does not exist. I'm not sure how much the numerical errors vary from unix to unix... Remember that INTO OUTFILE expects a path to a file on the _server_, not the client. Also, mysqld must have permission to create the file. On my setup, I have a world-writable directory in my home directory on the server called "Drop Box" (this is standard on OS X) which I write to. Hope this helps, Geoff - 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: TXT to MySQL DB
on 3/5/01 12:28 AM, Cho Bum Rae at [EMAIL PROTECTED] wrote: Do you know how to import TXT file into mysql DB? Because of big size txt file, I cannot input data into DB manually. Is there easy way to solve that problem? Cho: Look at mysqlimport, which comes with mysql. It is well documented in the manual or via: mysqlimport --help Thanks, Geoff - 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: synopsis of the problem (one line)
on 3/3/01 4:54 PM, Jonathan Dugan at [EMAIL PROTECTED] wrote: and I follow what is says: shell mysql -u root mysql ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) It sounds like you've _already_ set a root password, so you need to specify it: mysql -u root -p With the -p parameter it will prompt you for a password on stdin. Supply the root password and you should be in. If you don't specify -p on the command line, it assumes you are attempting to gain access without a password, and your security isn't set up to allow this. This is a bit confusing at first... But the security model in mysql is really very elegant once you understand what's going on... Hope this helps, Geoff - 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: can create, cannot drop...?
on 2/26/01 8:07 AM, Rolf Hopkins at [EMAIL PROTECTED] wrote: Actually, at the file system level, yes, you're right. But database level security, not necessarily. Check you have the right permissions for a start At least in my case, the drop _almost_ succeeds. The table files are deleted. The database's directory is entirely emptied, but it fails to delete the directory itself. Mysql reports that rmdir failed with errno 21. Errno 21 on my system is EISDIR -- 'is a directory', which doesn't seem to be an error at all... And rmdir is not defined to return this error. But, I can use the command rmdir to delete the directory without a problem, so I suspect the error is not being reported correctly. It does not have anything to do with security in mysql...it happens when I drop as the root user with everything turned on in the user table... Thanks, Geoff - 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: can create, cannot drop...?
on 2/26/01 2:59 PM, G. Adam Stanislav at [EMAIL PROTECTED] wrote: At 09:11 26-02-2001 -0700, Geoff Coffey wrote: Errno 21 on my system is EISDIR -- 'is a directory', which doesn't seem to be an error at all... It is an error (that is why it starts with an 'E'). Appendix C of POSIX Programmer's Guide describes it as follows: "Attemp to open a directory for writing or to rename a file to be a directory. Used by: open(), rename()" Sorry, I was not clear. Yes, I know it is an error, but I don't think it is an error that makes sense in context...ie- rmdir should never complain that it can't delete a directory because it "is a directory". That doesn't make sense. And as further evidence, the man page for rmdir(2) does not list EISDIR as a possible error result. So...the problem remains. Anybody have any idea why mysql would fail to drop databases, reporting that rmdir failed with errno 21 (EISDIR in my case) when it _does_ have permission to delete the directory. As I mentioned before, the table files _are_ deleted, but it fails to delete the (now empty) directory. Thanks, Geoff - 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
Can't drop tables
I just installed mysql 3.23 and then ran a script to create a database I had previously been using in an older version. I have a couple questions. First, how can I make a drop database statement not fail if the database doesn't exist. A book I was using seemed to say: drop database dbname if exists; Would work, but I get an error with this. What is the correct syntax? Second, on this newest installation, I can't drop a database. I get the error "failed to rmdir ./dbname, errno 21" or something similar. If I su to my mysql user, and cd to the data directory, an rmdir dbname works successfully, so I'm not sure why this is failing. The actual data files in the database are all gone after the drop statement. Only the empty directory sticks around. Any help would be appreciated. Thanks, Geoff - 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
Mac OS X
Has anybody successfully built 3.23 on Mac OS X Public Beta? Does anybody know if the version it supposedly builds under is a later beta? I still can't seem to get it to work properly. Thanks, Geoff - 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: Large text searches
on 2/1/01 1:36 PM, Gonzalo Aguilar at [EMAIL PROTECTED] wrote: Making search engines is not a trivial thing, but this may be an aproach... I appreciate all the replies. In my original post I also asked about the FULL TEXT index type. If I may ask again, has anyone had any experience with this? Can you comment on it's stability (it's listed as "beta" on the site). In general, how long is something in that state? I don't suppose this particular site will go live for another couple months (it's running fine in filemaker now). Do the FULL TEXT searches perform well? Are the results good? In order to get it working, I'd have to build 3.23 and I had trouble with that when I first tried, but I'm sure I could get it working with a little effort... Thanks, Geoff - 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: Large text searches
on 2/1/01 4:05 PM, Sander Pilon at [EMAIL PROTECTED] wrote: on 2/1/01 1:36 PM, Gonzalo Aguilar at [EMAIL PROTECTED] wrote: Making search engines is not a trivial thing, but this may be an aproach... I appreciate all the replies. In my original post I also asked about the FULL TEXT index type. If I may ask again, has anyone had any experience with this? Can you comment on it's stability (it's listed as "beta" on the site). In general, how long is something in that state? I don't suppose this particular site will go live for another couple months (it's running fine in filemaker now). Do the FULL TEXT searches perform well? Are the results good? In order to get it working, I'd have to build 3.23 and I had trouble with that when I first tried, but I'm sure I could get it working with a little effort... First of all - realize that fulltext search, as implemented in mysql, is WEIRD. If you intend to use it as searchengine and expect it to behave like modern searchengines do (google, altavista) then think again, it does not. (You want boolean search for that, or perhaps ranked boolean search, but not something like mysql has now. - 'coming soon' to a mysql version near you.) I have evaluated it (see my post, use archives) for searchengine use and found that it didn't suit my needs. (since it will have a very weird order of sorting, and by that I mean that a document with two of the words in the query can rank below (!) one with just one of the querywords - depending on what mysql thinks is the information-value of the word.) As for stability - It has had its share of bugs, but I think most are fixed. FULLTEXT indexes are about 95% of the size of the table, and they can perform quite well or very slow, depending on what you do. I did notice in the explanation of the FULLTEXT indexing/searching scheme that the weight of a word decreased as it became more common in the search pool, which might be problematic for me. These are resumes of "tech industry" people and often a client will search for "HTML" or "C++". Some things like that would appear in the text of _many_ resumes. At and rate, it's probably worth playing with. I don't need boolean search capability (the users in this case probably can't spell boolean, let alone understand a boolean search query...) A completely different possibility is to keep the resume and cover letter text in individual files somewhere (perhaps named by candidate id) and use some other tool to index and search them. If I did this, I would still need to coordinate the search with mysql. For instance, they would want to be able to limit the search to a certain state, or a certain date range (date of submission)...has anybody done anything like this? Since these tools rank things by relevance, and limit the quantity of results, I think I would need to limit the search set with a mysql query _before_ doing the full text search... I saw one recommendation to use HTDig, or something like that. I could also use the Apple Information Access Toolkit, as this in on Mac OS X. Any feedback on either of these? Also, I don't consider disk usage an issue at all. As long as performance is good, they can put as much disk space as needed in to it. Thanks, Geoff - 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