RE: PHP does not see Table updates
Show us your code. What are your table structures like? -Original Message- From: AOK Lansing [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 12:37 PM To: [EMAIL PROTECTED] Subject: PHP does not see Table updates I am using PHP to access a MySQL DB. If I stop MySql and then restart it and then open my webpage the table values are correct. If I make a change to the table, close my browser and restart it, the table values are the old values. If I stop MySQL after changes and then restart it, all works well. What am I doing wrong? Thanks, Bob Hiller - 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 - 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
RES: Backup Database MySQL to a Tape Backup
Ok. My hardware hangup and i installing a new server. Howto i restore from tape to a new instalation. André Sartori [EMAIL PROTECTED] -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Enviada em: quinta-feira, 7 de fevereiro de 2002 09:56 Para: Andre Sartori Assunto: Re: Backup Database MySQL to a Tape Backup Quer ter seu próprio endereço na Internet? Garanta já o seu e ainda ganhe cinco e-mails personalizados. DomíniosBOL - http://dominios.bol.com.br mysqldump [options] | tar cvf /dev/nrst0 - Andre Sartori wrote: Howto backup databases MySQL direct to a Tape Backup. André Sartori [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication question to MySQL list
Replication with temporary table issue: HostA: MySQL 3.23.42-log HostB: MySQL 3.23.42 Replication: HostA --to-- HostB Issue: After followed the MySQL Manual recommendation to: SET SQL_LOG_BIN = 0; to create, insert and drop a temporary table, example: SET SQL_LOG_BIN = 0; CREATE TEMPORARY TABLE tempTableA ... INSERT INTO tempTableA ... SET SQL_LOG_BIN = 1; INSERT INTO nomalTableB SELECT * FROM tempTableA --- it does not work because tempTableA table has not been replicated. Question: Is there a work around for this problem, other than make tempTableA a normalTableA to get a good replication of it ? Thank you in advance. - 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: design help
Hi dn, thanks for replying. I suspect you're right about my 'problem' - I'm probably misunderstanding some simple SQL construct :-) Let's say I have the following tables: parts (partid int, partname varchar(255)) cats(catid int, catname varchar(255)) parts_cats (id int, partid int, catid int) To track which categories are assigned to which parts. I can use the following query to return parts and associated categories: SELECT partid,catid,partname,catname FROM parts_cats LEFT JOIN parts ON parts_cats.partid=parts.partid LEFT JOIN cats ON parts_cats.catid=cats.catid However, as I won't know how many categories are assigned to a given part, how can I: a) Form a query that only returns a list of parts that match a specifc set of categories (i.e. parts_cats has records where catid=1, 3 and 7 for any given partid) b) Know how many unique records I'm dealing with. Using the SQL above, if a part has, say, 3 categories then 3 rows would be returned. 4 categories and 4 rows are returned, etc. - how can I tell how big my result set is so that I can page through it using LIMIT? Hope I've explained myself clearly! - thanks in advance, james ps Completely OT question - how do you find your HomeChoice service? Been thinking about getting it for a while. j. At 09:39 10/02/2002 +, you wrote: The separate table idea is standard relational theory. The tbl schema might involve (1) a unique Id column (with AUTO_INCREMENT), (2) a 'copy' of whatever data from the existing tbl(s) to sufficiently identify the particular response/select box, and (3) one of the select box return values - there would then be as many rows as there were responses in the select box (n=3 in this example). The second of these must be sufficient information to uniquely identify which table entry in the original table relates to the particular select-response - these are called Foreign Keys. To retrieve all of the select-responses, you would indeed issue a SELECT...JOIN and thus be able to reassemble the HTML select command, for example. Yes you are correct (in your example) such a SELECT will produce a resultset of 3 rows. This will allow you to search on a single, or multiple (concurrent) select-responses. As mentioned, this is SOP. You say you want multiple responses but not multiple entries, but not why. Perhaps most of the 'problem' lies hidden there? =dn - 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 James Carrier Bullet Online :: Aim Higher [http://www.bulletonline.com] 41b Beavor Lane, London W6 9BL Tel +44 (0) 20 8834 3442 Fax +44 (0) 20 8741 2790 - 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
Windows Version of MySQL licensing question?
I read in Paul's book that to use MySQL on a Windows platform you need to pay for it. I have downloaded a version of the server and client software from the official MySQL.com site and installed it on XP without incident. Paul also writes that the version for windows will be shareware only. This seems not to be the case I have had my server 3.23.43-nt up and running now for some months. I only use the server for my own uni work but I am curious as to what is the real deal. Anyone? Thanks lots David Ayliffe ICQ# 125646758 Query table database - 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
MySQL Replication question
Replication with temporary tables: HostA: MySQL 3.23.42-log HostB: MySQL 3.23.42 Replication: HostA --to-- HostB Issue: After followed the MySQL Manual recommendation to: SET SQL_LOG_BIN = 0; to create, insert and drop a temporary table, example: SET SQL_LOG_BIN = 0; CREATE TEMPORARY TABLE tempTableA ... INSERT INTO tempTableA ... SET SQL_LOG_BIN = 1; INSERT INTO nomalTableB SELECT * FROM tempTableA --- it does not work because tempTableA table has not been replicated. Question: Is there a work around for this problem, other than make tempTableA a normalTableA to get a good replication of it ? Thank you in advance. - 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 this be done?
What do your tables look like? Structure? What are you trying to do? What does your data look like? -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 10, 2002 8:12 PM To: [EMAIL PROTECTED] Subject: Can this be done? I'm trying to update a database. Here's the query that's not working. Is there another way of doing it or is this impossible with MySQL? Thanks, -Ed update SiteIndex set SiteIndex.Visitors = Pages.Visitors where SiteIndex.Title = Documents.Title AND Pages.DocID = Documents.ID - 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 - 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
Need help optimizing a query
I need to optimize the following query: SELECT user, sum(in), sum(out) FROM stats WHERE from='20020205' AND to='20020206' GROUP BY user; In,out are bigint, user is varcher(20) and from,to are timestamp And I want to speed up this type of query, but I have not been able to to get it to use an index, can anyone please tell me how to optimize this. - David Darville - 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: triming white space from a TEXT column
I am trying to trim both leading and trailing whitespace from a TEXT column in my SELECT query. I found the TRIM() command in the manual, however, I can't get it to act upon a column name instead of an actual string. Can anyone help? If you just say TRIM(col_name) it will return the trimmed column value, but it won't change the column itself. Try updating the column to its trimmed value: UPDATE tbl_name SET col_name = TRIM(col_name); I tried both ideas and neither one removed the white spaces. arrr! :-( Thanks, Richard - 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: ORDER BY an ABSolute value
Thanks for your quick response Dave, Have tried this, but no bannana... I get: You have an error in your SQL syntax near 'abs(id)' at line 1 Seems a not-valid thing to do these days... Is there a work around? Dave from Oz select id from table_name order by abs(id); Dave Dundee! On Tue, Feb 12, 2002 at 09:46:25AM +1000, David Mackay wrote: G'Day folks, New to PHP/MySQL. Want to order the results of a SELECT by their 'absolute' value, not their sign. So regardless of whether it's +37 or -37, they are both 37 and thus both come between 38 36, regardless of whether they're + or -. eg. 100 -99 -92 91 72 -38 37 -37 etc... I have the order DESC but it puts 100 first, and -100 last, (that's what you'd usually want, but I need the extremes grouped at one end, down to the middle point zero..). In my fantasy world I could do a ORDER BY ABS(number) DESC If there was any other way to achieve the same result that would be just dandy... I bet this is obvious. Thanks for your time, David Mackay - 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 - 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
Problem with MySQL version 4.0.1
Hi, more and more frequently we are seeing this error: Lost connection to MySQL server during query during queries. I have seen it reported previously on the list, but i haven't been able to find a remedy. Does anyone have a solution to this? Any ideas are appreciated. Thanks, james -- James Cox :: [EMAIL PROTECTED] :: Landonize It! http://landonize.it/ Was I helpful? http://www.amazon.co.uk/exec/obidos/wishlist/23IVGHQ61RJGO/ - 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
Please Help: 2 (Easy?) Timezone (TZ) Questions
Hi folks - I am still having 2 related problems with the timezone setting for mysqld was hoping to get some help. Basically my squestions are as follows: a) the documentation says to set timezone by simply using --timezone=# when starting mysqld - but WHAT are the valid number values which time zones do the map to? b) I have tried to set the timezone using safe_mysqld --timezone=XX where I have tried various values for XX such as o1, 1, 13, 05,08 BUT near as I can tell ANY value I put in gives me GMT/UTC time (I think that's what it is) rather than giving me time offset by the given amount i entered. This is not what the documentation seems to suggest will happen - or am I being obtuse? Any help or suggestions would be appreciated. 'deep -- Amandeep Jawa Worker Bee Software -- deep AT worker DASH bee DOT com 225A Dolores St. San Francisco, CA 94103-2202 Home: 415 255 6257 (ALL MALP) professional: http://www.worker-bee.com personal: http://www.deeptrouble.com political: http://www.sflcv.org - 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: long query status info
I can't read your structures or query from here. [EMAIL PROTECTED] wrote: hi, i'm running a big query across a table containing more than 137 million rows comparing them to another one with about 4000 rows and i'm wondering how long it will still take... is there a way to see how many operations have been made and how many are left? as you can see it already took about 107h: # mysqladmin processlist ++--+---+-+-++--+- | Id | User | Host | db | Command | Time | State| Info ++--+---+-+-++--+ | 7 | root | localhost | einzugsbereiche | Query | 387723 | Copying to tmp table | SELECT - 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 - 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: LIKE work around??
...uhm duh. My apologies. I had the LIKE switched around when I tried it and after reading through the manual and not finding anything about LIKE (it isn't mentioned as a parameter in the SELECT section) I assumed it wasn't supported. Thanks for your comments. Aaron =20 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 1:12 PM To: Rutledge, Aaron Cc: Mysql List (E-mail) Subject: Re: LIKE work around?? Work-around? LIKE should work just fine... mysql select * from fruits where fruit_name like '%banana%'; On Thu, 7 Feb 2002, Rutledge, Aaron wrote: I am trying to query the database for records that have the same sequence of starting characters--such as banana1 and banana2 and have a result set returned that contains everything that starts with banana. Any way to do this? I think I would use LIKE in other types of databases, but I was wondering if there was a work around for MySQL. Thanks, Aaron - 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: LIKE work around??
Paul DuBois writes: At 12:50 -0800 2/7/02, Cindy wrote: Rutledge, Aaron writes: MySQL doesn't support LIKE does it? I got an error when I tried. I use it all the time. Maybe post the error message you got. RLIKE on the other hand...sigh. sigh, what? RLIKE works. It's the same as REGEXP. I posted about it previously, but got no response: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:92816 --Cindy - 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: long query on php
Here is how I do it: $query = some query ; $query.=some more of the same query ; $query.=still more query ; $query.=and so on... ; $rst = mysql_query($query); NOTE: you need to add a space at the end of each line (after the last word and before the closing quotations) if you plan on continuing the query on the next line, or else your SQL will be invalid. Good luck, Brett Original Message Follows From: savaidis [EMAIL PROTECTED] To: MySQL list \(E-mail\) [EMAIL PROTECTED] Subject: long query on php Date: Wed, 6 Feb 2002 21:13:44 +0200 How is possible to pass a long query to MySQL server with php? I mean i.e a create table statement with more than 400 chars. Have I to use shorter create and then alter? Thanks Makis - 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 _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.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: long query on php
Hi. I prefer doing like this $query = CREATE TABLE query ; $query .= tabledef for col a,; $query .= tabledef for col b,; $query .= lots more table defs,; $query .= welcome to line 400; You get the point i guess you can do same things many different ways in php:) /PM\ Alex Aulbach wrote: Yesterday, from Nathan: Since PHP ignores whitespace, I think this is also acceptable: PHP doesn't ignore whitespace in a quote. But your code is of course correct. $query = CREATE TABLE query tabledef for col a, tabledef for col b, lots more table defs, . welcome to line 400; Another way: (without test) $bladefault=24; $query = eoq; CREATE TABLE query ( idchar(2) default 24 # doublequotes work hugo char(2) default '24' # singlequotes work either bla char(2) default '$bladefault' # inline vars work ... ) eoq -- How is possible to pass a long query to MySQL server with php? I mean i.e a create table statement with more than 400 chars. Have I to use shorter create and then alter? Strings in PHP can be really, really long and are binary safe. Perhaps the problem is the editor? -- SSilk - Alexander Aulbach - Herbipolis/Frankonia Minoris - 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 - 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: Tweaking MySQL on a RaQ (or any server) Help please.
Hi James, Many of the questions on this list are answered by people like myself who are users of MySQL. We are busy trying to run our own servers and put food on the table. That being said, you are wondering why your question did not get an answer, it may be the length of the message and the not-very-specific question. My guess is this one will get more answers as you asked 'where can I find the resources to help myself'. I would recommend starting with a good sysadmin book (there are many) such as: Unix System Administration Handbook, Prentice Hall This will help you manage system resources. Futhurmore on MySQL, the manual is an excellent source of information. Have you read all the information about tunable parameters in the manual? There is lots of it. Then when you have a specific question post it here. Addntl books on MySQL: MySQL by Paul Dubois from New Riders is a standard Core MySQL by Leon Atkinson Prentice Hall (Just out this year) and excellent resource and goes beyond Paul's book but and it is newer (despite the numerous spelling errors in the examples and the text). Also there are web resources: http://www.mysql.com/portal/books http://www.faqts.com/knowledge_base/index.phtml/fid/52/ Articles on MySQL in the portal section of the web site: http://www.mysql.com/portal/development/html/development-61-1.html Also one last point, time is money. If you are using MySQL in a commercial venture purchasing a support contract would be wise. They will help (at anything but the most basic level) to tune your system and the higher levels they will do it for you! The MySQL support contract is by far the best money I have ever spent on software! Best of luck and I hope this helped, Ken - Original Message - From: James Riordon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 9:56 PM Subject: Re: Tweaking MySQL on a RaQ (or any server) Help please. At 20:54 -0500 02/04/2002, I whispered: Hi all, A couple of weeks back you may remember that I was asking about setting up a new client on our server that ran around 35GB in bandwidth per month mostly from MySQL queries. Well, we have put them on our server and have good and bad news. The good news is that they are now pulling around 45GB in bandwidth per month. Overall the performance is acceptable and the machine is happy. - 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: PostgreSQL
Sinisa Milivojevic wrote: Shankar Unni writes: And one big reason not: no native Windows port. Or Mac port (though that has probably changed with OS/X - anyone working on that?). When you are saying that there is no native Windows or OS X port, what were you referring to ?? Oh, PostgreSQL, of course. I've been using the MySQL Windows port for a year now.. And Alok Dhir jested: But you do trust a database running on Windows? [;-)] As much as I trust a database running on any native OS (Solaris, Linux, Windows..). It's the cross-OS emulation that makes me nervous.. Cygwin is a technological marvel, but have you seen all the things it has to do? It's a mini-OS in itself, with a lot less testing than Linux or Windows.. -- Shankar. - 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
my.cnf
Hello everyone, There used to be a section in mysql documentation, which explained about all the entries in my.ini file. Do you know what happened to it? There is a part about my.cnf but that's not what I want. There was more. - 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: PHP does not see Table updates
Problem Solved! Thanks - Original Message - From: AOK Lansing [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 07, 2002 1:37 PM Subject: PHP does not see Table updates I am using PHP to access a MySQL DB. If I stop MySql and then restart it and then open my webpage the table values are correct. If I make a change to the table, close my browser and restart it, the table values are the old values. If I stop MySQL after changes and then restart it, all works well. What am I doing wrong? Thanks, Bob Hiller - 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 - 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: Problems with select
I have a (probably dumb) question. My table temperatura has two fields described bellow. snip When I try to select rows with floats, the result is an empty set. This is not a MySQL problem, nor a database one, but a general computer science one. It is almost never save to compare two floating point numbers for equality. The decimal representation you see in ASCII is not an exact representation but an approximation. Because of the way computers store floating point numbers in bunry and humans read them in decimal, the decimal value is alway rounded and there are invisible bits which can be different in apparently identical values. For floating point comparisons you should *always* use unequalities e.g. WHERE temp BETWEEN 15.75 AND 15.85. Alec Cawley - 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
FW: Escape a blob field.....
Hi There! I am using mysql 3.23.41, for redhat-linux-gnu (i386). My problem is this. In java, I am trying to extract a blob field from a mysql table - I am using Byte[] somebytes = RS.getBytes(FPPICTURE); String somestring = new String(somebytes); This gives me a string of the blob data. I then need to create a new SQL insert statement that will insert the blob data stored within somestring into a table.. String sqlCommand = INSERT INTO SOMETABLE (FPPICTURE) VALUES ('+somestring+');; However, when I attempt to execute the above query, I have a problem because the contents of somestring contains some characters that are illegal within a SQL statement such as a ' . I have used PHPMyAdmin, and exported a table which has blob fields within it. The result is a SQL string with the contents of the blob field being escaped within the SQL query. My question is, how can I get a blob field from a mysql table that had already escaped any illegal charachters, or how can I encode the blob field myself so that the mysql statement I create is valid ??? Thanks in advance, Ian Packard - 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
PHP does not see Table updates
I am using PHP to access a MySQL DB. If I stop MySql and then restart it and then open my webpage the table values are correct. If I make a change to the table, close my browser and restart it, the table values are the old values. If I stop MySQL after changes and then restart it, all works well. What am I doing wrong? Thanks, Bob Hiller - 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
Reset auto_increment
Hi, As I am new to MySQL, perhaps this is a common question you have been asked. I have a field Id which using auto_increment. Now I want to reset the value of auto_increment, that is the Id starts form 1 and does not skip the number. I have spent a few hours to find a way, but all failed. Could you please tell me how to do it? Wei _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.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
SELECT query with TRIM and LIKE
Tshering, Wednesday, February 06, 2002, 7:29:11 AM, you wrote: TN Dear list, TN I have a telephone database which has Name as one of the fields/columns. TN This field contains single name e.g Robert, double names e.g Robert TN Downing and more than double names. I want to make a SELECT query using a TN form whose input name is Name i.e '$Name' against the Name field in the TN database. One can type a part/full of one part of the name, or part/full of TN two parts of a name and goes on likewise for more than double names in the TN input form. TN I believe this SELECT query is accomplished using TRIM and LIKE, something TN like: TN $query = (SELECT * from table_name where Name like '% Trim($Name) %' TN ); TN This $query is not working. TN Could sombody please provide me a query statement that I could use for my TN above requirement. Regular expressions will be useful for you, look at: http://www.mysql.com/doc/S/t/String_comparison_functions.html http://www.mysql.com/doc/R/e/Regexp.html TN Thank you so much. TN NOBBY -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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
GRANT command (ERROR 1047)
Joachim, Thursday, February 07, 2002, 11:18:43 AM, you wrote: JH I am using the following versions of mysql on suse linux 7.2: JH frontend: mysql Ver 11.15 Distrib 3.23.47, for pc-linux-gnu (i686) JH server: version 3.23.47 JH If I try to use the GRANT command I get JH #mysql -u root mysql JH mysql GRANT ALL ON *.* TO root@localhost JH - IDENTIFIED BY 'password' WITH GRANT OPTION; JH ERROR 1047: Unknown command JH Any idea? Thanks! If you started mysqld with --skip-grant-tables, you can do FLUSH PRIVILEGES or run mysqladmin reload. Look at: http://www.mysql.com/doc/R/e/Resetting_permissions.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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: Distributed Fulltext?
I did this at a previous job, and we split the data up more or less this way (we used a pre-existing item number for the split which was essentially random in relation to the text data), with a aggregator that did the query X ways, each to a separate box holding 1/X of the data. The results from each unit were paged and sorted, so all the aggregator did was do a simple merge sort on a page of the set, which was fast. On a 6M record dataset, it produced millisecond-range search results. Not exactly Google-class, but pretty good for 12 Linux boxes, two programmers, and about six weeks of effort. james montebello On Thu, 7 Feb 2002, Brian Bray wrote: It seems to me like the best solution that could be implemented as-is would be to keep a random int column in your table (with a range of say 1-100) and then have fulltext server 1 psudo-replicate records with a the random number in the range of 1-10, server 2 11-20 and server 3 21-30 and so on. Then run your query on all 10 servers and merge the result sets and possibly re-sort them if you use the score column. The problem with splitting the index up by word is that is messes up all your scoring and ranking. For example what if you search using 5 keywords, all starting with letters from different groups? Your going to get pretty bad score for each match, and it could totally break boolean searches. -- Brian Bray Brian DeFeyter wrote: On Thu, 2002-02-07 at 15:40, Tod Harter wrote: [snip] Wouldn't be too tough to write a little query routing system if you are using perl. Use DBD::Proxy on the web server side, and just hack the perl proxy server so it routes the query to several places and returns a single result set. Ordering could be achieved as well. I'm sure there are commercial packages out there as well. I don't see why the individual database servers would need to do anything special. [snip] If I'm understanding you correctly, I think you're refering to routing based on the first character of the word. That would work for cases where the query is searching for a word that begins with a certain character.. however fulltext searches also return results with the term in the middle. ie: a search for 'foo' could return: foo.txt foobar but also could return: thisisfoo that_is_foolish I could be wrong, but it's my understanding that MySQL stores it's fulltext index based on all the 'unique words' found. For such a system as you mentioned above, you'd probably have to create your own fulltext indexing system to determine: a) where to store the data 'segments' and b) how to route queries. It seems like this could probably be done much more efficiently inside of the server. - Brian - 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 - 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: opposite of PASSWORD('mypasswd').
dear friends, in Mysql, under sql, we have the function PASSWORD('mypasswd') where we can encrpt 'mypasswd' into a 16 character scrambled word. which function in sql is used to decrypt the same and get back the original mypasswd. thanking you -- Sumeet Shroff proprietor Prateeksha Printing Services and Web Solutions http://www.prateeksha.com Web development and support firm. - 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: Reset auto_increment
The following deletes all records and re-starts renumbering from 1; DELETE FROM mytable; The following deletes all records and continues counting from where the table last left off: DELETE FROM mytable WHERE 1; If you are trying to restart numbering while not deleting records, forget-about-it. -Original Message- From: Wei Gao [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 2:10 PM To: [EMAIL PROTECTED] Subject: Reset auto_increment Hi, As I am new to MySQL, perhaps this is a common question you have been asked. I have a field Id which using auto_increment. Now I want to reset the value of auto_increment, that is the Id starts form 1 and does not skip the number. I have spent a few hours to find a way, but all failed. Could you please tell me how to do it? Wei _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.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 - 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: Distributed Fulltext?
It seems to me like the best solution that could be implemented as-is would be to keep a random int column in your table (with a range of say 1-100) and then have fulltext server 1 psudo-replicate records with a the random number in the range of 1-10, server 2 11-20 and server 3 21-30 and so on. Then run your query on all 10 servers and merge the result sets and possibly re-sort them if you use the score column. The problem with splitting the index up by word is that is messes up all your scoring and ranking. For example what if you search using 5 keywords, all starting with letters from different groups? Your going to get pretty bad score for each match, and it could totally break boolean searches. -- Brian Bray Brian DeFeyter wrote: On Thu, 2002-02-07 at 15:40, Tod Harter wrote: [snip] Wouldn't be too tough to write a little query routing system if you are using perl. Use DBD::Proxy on the web server side, and just hack the perl proxy server so it routes the query to several places and returns a single result set. Ordering could be achieved as well. I'm sure there are commercial packages out there as well. I don't see why the individual database servers would need to do anything special. [snip] If I'm understanding you correctly, I think you're refering to routing based on the first character of the word. That would work for cases where the query is searching for a word that begins with a certain character.. however fulltext searches also return results with the term in the middle. ie: a search for 'foo' could return: foo.txt foobar but also could return: thisisfoo that_is_foolish I could be wrong, but it's my understanding that MySQL stores it's fulltext index based on all the 'unique words' found. For such a system as you mentioned above, you'd probably have to create your own fulltext indexing system to determine: a) where to store the data 'segments' and b) how to route queries. It seems like this could probably be done much more efficiently inside of the server. - Brian - 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 - 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: Distributed Fulltext?
On Thursday 07 February 2002 14:53, Brian DeFeyter wrote: Has anyone made a suggestion or thought about ways to distribute databases which focus on fulltext indexes? fulltext indexes do a good job of indexing a moderate amount of data, but when you get a lot of data to be indexed, the queries slow down significantly. I have an example table, with about 90 million rows.. and has a fulltext index on a varchar(100) field. A single-word query which would return approx 300k results takes an average of 15 seconds. A query with smaller results (~ 10k) can be as quick as 1 sec.. which I would consider acceptable. Has any thought about splitting the data into distributed files or even machines? ie: something as simple as 'words' starting with 'X' are split into a-h, i-p, q-z... or something more advanced? (maybe mysqld could automatically split results based on (#results per unique 'word' / desired # of 'split files/machines') Would such a system give any advantages to searching speed and concurrenct query scalability? I haven't looked at the fulltext internals.. so I don't know if such query routing could take place or not. If nothing else, does anyone else have experience with a table of this size or even larger? What kind of tuning have you done? Thanks, - Brian Wouldn't be too tough to write a little query routing system if you are using perl. Use DBD::Proxy on the web server side, and just hack the perl proxy server so it routes the query to several places and returns a single result set. Ordering could be achieved as well. I'm sure there are commercial packages out there as well. I don't see why the individual database servers would need to do anything special. - 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 - 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: long query on php
Yesterday, from savaidis: The obious question is: (before I test it) This is concatenation to $query that is a string type, no? Yea. The following works either: mysql_query(create bla. bla. bla bla blabla . bla .bla ); So the limit isn't also set to 255 chars too? There is no such limit in PHP. Or is a trick especially for this case? There is no any bit nothing trick not. Read PHP-Doku about defintions of strings. -- SSilk - Alexander Aulbach - Herbipolis/Frankonia Minoris - 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
Project + MyODBC
Has anyone successfully saved Microsoft Projects within MySQL? When I attempt to save a project I get a MyODBC error on the syntax, on, what I presume is one of their table creation SQL statements. Anyone have any insight? - 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: Clear table contents
try: DELETE FROM relay_ip WHERE UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(rei_ts) 600; -Original Message- From: Odhiambo Washington [mailto:[EMAIL PROTECTED]] Sent: Monday, February 11, 2002 1:22 AM To: [EMAIL PROTECTED] Subject: Clear table contents Hello gurus, Please allow me to request for help with the table below. mysql select * from relay_ip; +-+++-+- + | rei_aid | rei_uname | rei_domain | rei_ip | rei_ts | +-+++-+- + | 1 | wash | alligator.wananchi.com | 62.8.64.4 | 2002-02-08 18:17:36 | | 2 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-08 20:36:51 | | 3 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-08 20:42:27 | | 4 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-09 13:03:27 | | 5 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-09 13:03:28 | | 6 | [EMAIL PROTECTED] | freebsd.co.ke | 62.8.64.108 | 2002-02-11 09:46:43 | | 7 | [EMAIL PROTECTED] | freebsd.co.ke | 62.8.64.108 | 2002-02-11 09:47:27 | +-+++-+- + I am looking for a way to clear each record that is older than 10 minutes by referring to the timestamp. Something that I can run from cron. tia -Wash S y s t e m s A d m i n. -- Odhiambo Washington [EMAIL PROTECTED]The box said 'Requires Wananchi Online Ltd. www.wananchi.com Windows 95, NT, or better,' Tel: 254 2 313985-9 Fax: 254 2 313922 so I installed FreeBSD. GSM: 254 72 743 223 GSM: 254 733 744 121 This sig is McQ! :-) ++ No problem is so formidable that you can't just walk away from it. - 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 - 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
question on mysql benchmarking
Hi, When I run perl run-all-tests --server=mysql --cmp=mysql,pg,solid --user=test --password=test --log in the sql-bench direcotory. I encountered following error messages: Can't locate DBI.pm in @INC (@INC contains: /usr/lib/perl5/5.6.0/ia64-linux /usr/lib/perl5/5.6.0 /usr/lib/perl5/site_perl/5.6.0/ia64-linux /usr/lib/perl5/site_perl/5.6.0 /usr/lib/perl5/site_perl .) at run-all-tests line 36. BEGIN failed--compilation aborted at run-all-tests line 36. I am very new to mysql. I cannot figure out the reason easily. Is there any problem with my perl? Another question is that I am also using another compiler (ORC from intel) to compile mysql. ORC borrows the frontend of gcc. but orcc doesnot accept compiler options such as -fno-implicit-templates -fno-exceptions -fno-rtti. I don't know the meaning of these flags. Can you explain me this? Can I eliminate these options and make mysql still runs well? Thanks. Peng - 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: Clear table contents
something easier: DELETE FROM relay_ip WHERE NOW() - rei_ts 1000; -Original Message- From: Rick Emery Sent: Tuesday, February 12, 2002 3:03 PM To: 'Odhiambo Washington'; [EMAIL PROTECTED] Subject: RE: Clear table contents try: DELETE FROM relay_ip WHERE UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(rei_ts) 600; -Original Message- From: Odhiambo Washington [mailto:[EMAIL PROTECTED]] Sent: Monday, February 11, 2002 1:22 AM To: [EMAIL PROTECTED] Subject: Clear table contents Hello gurus, Please allow me to request for help with the table below. mysql select * from relay_ip; +-+++-+- + | rei_aid | rei_uname | rei_domain | rei_ip | rei_ts | +-+++-+- + | 1 | wash | alligator.wananchi.com | 62.8.64.4 | 2002-02-08 18:17:36 | | 2 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-08 20:36:51 | | 3 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-08 20:42:27 | | 4 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-09 13:03:27 | | 5 | wash | alligator.wananchi.com | 62.8.64.108 | 2002-02-09 13:03:28 | | 6 | [EMAIL PROTECTED] | freebsd.co.ke | 62.8.64.108 | 2002-02-11 09:46:43 | | 7 | [EMAIL PROTECTED] | freebsd.co.ke | 62.8.64.108 | 2002-02-11 09:47:27 | +-+++-+- + I am looking for a way to clear each record that is older than 10 minutes by referring to the timestamp. Something that I can run from cron. tia -Wash S y s t e m s A d m i n. -- Odhiambo Washington [EMAIL PROTECTED]The box said 'Requires Wananchi Online Ltd. www.wananchi.com Windows 95, NT, or better,' Tel: 254 2 313985-9 Fax: 254 2 313922 so I installed FreeBSD. GSM: 254 72 743 223 GSM: 254 733 744 121 This sig is McQ! :-) ++ No problem is so formidable that you can't just walk away from it. - 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 - 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: Distributed Fulltext?
Steve Rapaport wrote: On Friday 08 February 2002 06:14 pm, James Montebello wrote: Distribution is how Google gets its speed. You say clustering won't solve the problem, but distributing the indicies across many processors *is* going to gain you a huge speed increase through sheer parallelism. True, but not enough. The BEST parallelism can do in a compute-bound application is divide the time by the number of processors. That's assuming a PERFECT routing system. (Correct me if I'm wrong here) There are actually some exceptions. Specifically if you have a very large problem set and parallelism allows you to move the problem set into a faster storage medium, you can sometimes see greater performance increases. Lets say you are doing a full text search. And you have a 20 GB full text index. It may not be feasible to build a machine with 20GB of RAM for storing the index in RAM. But it might be more feasible to store 1/20th of the index in each of 20 1GB machines. And with RAM being 1000 times as fast as hard disk. You could get a huge win. So to make the routing system + parallelism add up to a MILLION times better performance, you would need at least a MILLION processors. I doubt that even Google is doing that. Google uses thousands of processors to handle its index, and any given search is going to be spread over 100s of processors. Right, so we can expect Google to do, say, 10,000 times (10^4) better than Mysql at a Fulltext search. But in fact we're seeing a million, 10^6, being generous. It's that extra factor of a hundred (more likely a thousand, my estimates were very generous) that I'm getting all fussy about. Asking a general purpose RDBMS to be really good a 10 different things is asking a bit much. Fulltext searches are well down the list. Here we get out of the realm of hard numbers and into opinions. But here's mine: If mysql bothers to support fulltext searches, it's presumably because there's some demand for them in some circumstances. The level of scaling and optimization that could reasonably be expected: What it takes to make the feature useful (i.e. perform similarly) in similar cases to other Mysql features. With a regular index, I can do a hash lookup on 23 million records subsecond. With a fulltext index (on a small field, only 40 characters) my time slips to 3 to 180 seconds. That extra little factor of 100 is my problem. Distributing over 4 processors wouldn't really help much. And because people don't always type a company name the exact same way, FULLTEXT really is the best way to do this. So Monty et al, my request is this: please put on the wish list, enhancement of FULLTEXT search to approximately match the performance of an indexed search on 25 million records, on the same hardware and with other things held equal. Steve Rapaport - 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 - 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: Duplicate Records
A quick method is to create a new table: CREATE TABLE newtable SELECT DISTINCT * FROM oldtable; note; manually verify newtable has your data. Then proceed: DROP TABLE oldtable; CREATE TABLE oldtable SELECT * FROM newtable; DROP TABLE newtable; -Original Message- From: Rich [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 10, 2002 10:31 AM To: MySql Subject: Duplicate Records How does one go about removing one of two identical records in a MySQL database? My mistake in an earlier database was not applying a unique number to each record (1, 2, 3, ---). I know it's possible to use the DISTINCT operator to show only one of the identical records in a result, but that does not remove one of them from the database. One thought that I had would be to add a unique number to each record, and that could probably be done manually, one record at a time, but is there a way to automate the process so that unique numbers could be assigned with one command? TIA Rich -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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
mysql list test - ignore
I apologize for using bandwidth...it appears my posts are no longer going to the [EMAIL PROTECTED] mailing list. I'm just verifying before rteporting it, if necessary magic words: sql,database,query - 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
Embedded Quotes
The mySQL manual says that embedded double quotes within a single quoted expression (as in 'OHara') need no special treatment. However, when I try to update a data base field using such an expression, as in: UPDATE table set col1='OHara' the result I get is that everything to the right of the double quote is deleted. In other words, the result of the update is just the letter O in this example. I get the same result if I use the escape character as follows: UPDATE table set col1='O\Hara' Is this behavior expected?? [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Quick SQL design help
Hello MySQL users I was wondering if one of you could advise me on the best solution to a problem I'm having - I'm sure this has been done before but haven't found anything in the archives. Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). Any ideas? Please help! Cheers, james - 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: What License do I need
Jochen Kaechelin wrote: What licence do I need when a want to install a MySQL-Server in a intranet on a Linux-Box where 4 Win-Clients use it by running a PHP Groupware Application? I'd say you can just go and use it without a fee AFAIK, but for a judicial correct and valid answer I wouldn't ask the list as we can tell you anything about that - it won't help you when the mysql people don't say it themselves and sue you for a license violation. Reading the documentation parts about the license should do it as well. henning - 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
AW: GRANT command (ERROR 1047)
Thanks, but it was just a typo. And I tried it again, but with the same = result. It's really a strange installation. mysql GRANT ALL PRIVILEGES ON *.* TO root@localhost - IDENTIFIED BY 'salach01' WITH GRANT OPTION; ERROR 1047: Unknown command JoacHim -Urspr=FCngliche Nachricht- Von: Diego, Emil [SMTP:[EMAIL PROTECTED]] Gesendet am: Donnerstag, 7. Februar 2002 22:00 An: '[EMAIL PROTECTED]' Betreff: RE: GRANT command (ERROR 1047) =20 Try this =20 GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'password'=20 WITH GRANT OPTION =20 Emil =20 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 4:19 AM To: [EMAIL PROTECTED] Subject: GRANT command (ERROR 1047) =20 =20 I am using the following versions of mysql on suse linux 7.2: frontend: mysql Ver 11.15 Distrib 3.23.47, for pc-linux-gnu (i686) =20 server: version 3.23.47=20 =20 If I try to use the GRANT command I get=20 #mysql -u root mysql mysql GRANT ALL ON *.* TO root@localhost - IDENTIFIED BY 'password' WITH GRANT OPTION; ERROR 1047: Unknown command =20 Any idea? Thanks! =20 =20 =20 =20 =20 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) =20 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 - 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: I must ask.
On Fri, Feb 08, 2002 at 06:10:34PM -, DL Neil wrote: There are some RDBMS-es that implement SQL to the point of apparently setting up a construct that links tables - foreign key based data integrity being the first example that springs to (my) mind. However relational logic suggests that the way to set up a linkage between tables is to show this in dynamic logic - your SQL statement, eg SELECT * FROM tbl1, tbl2 WHERE tbl1.PrimaryKey = tbl2.ForeignKey; will produce a result-row for every intersection 'match' between the two tables. One of the MAJOR advances of relational databases over their predecessors was this move 'away' from structural 'connections'. However these have been maintained as 'features' by some, usually to promote speed/efficiency. Speed/efficiency is a minor issue. Integrity constraints are used to prevent people from writing queries that violate relational integrity rules; e.g. create orphan records. And integrity constraints are unrelated to joins, except in the most incidental way. It is possible to use them to create joins, but that's not why they're built into databases. Bob Hall -- Database - 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: ORDER BY an ABSolute value
Did you try SQRT(id)? Should rank things the way you want if it's legal (*_*) -Original Message- From: David Mackay [mailto:[EMAIL PROTECTED]] Sent: Monday, February 11, 2002 7:08 PM To: 'David Turner'; David Mackay Cc: '[EMAIL PROTECTED]' Subject: RE: ORDER BY an ABSolute value Thanks for your quick response Dave, Have tried this, but no bannana... I get: You have an error in your SQL syntax near 'abs(id)' at line 1 Seems a not-valid thing to do these days... Is there a work around? Dave from Oz select id from table_name order by abs(id); Dave Dundee! On Tue, Feb 12, 2002 at 09:46:25AM +1000, David Mackay wrote: G'Day folks, New to PHP/MySQL. Want to order the results of a SELECT by their 'absolute' value, not their sign. So regardless of whether it's +37 or -37, they are both 37 and thus both come between 38 36, regardless of whether they're + or -. eg. 100 -99 -92 91 72 -38 37 -37 etc... I have the order DESC but it puts 100 first, and -100 last, (that's what you'd usually want, but I need the extremes grouped at one end, down to the middle point zero..). In my fantasy world I could do a ORDER BY ABS(number) DESC If there was any other way to achieve the same result that would be just dandy... I bet this is obvious. Thanks for your time, David Mackay - 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 - 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
MySQL - user variables
Perhaps I should have split this up into two mails, but I saw them related in the difference of databases On mysql Ver 11.12 Distrib 3.23.32, for pc-linux-gnu (i686) this works SELECT SQL_BUFFER_RESULT DATE_FORMAT(ORDERHEADER.AvailableAt,'%y/%m/%d') AS 'Date', (@tl:=SUM(IF(DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step = 1,1,0))) AS 'Total', (@wb:=SUM(IF(DISPATCHLOG.Details LIKE 'Web O/E%' AND DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step = 1,1,0))) AS 'Web', (@sys:=SUM(IF(DISPATCHLOG.Details = 'O/E by System' AND DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step = 1,1,0))) AS 'System', (@can:=(SUM(IF(DISPATCHLOG.ID_STATUS = 43 AND ORDERLINE.Step = 1,1,0))-SUM(IF(DISPATCHLOG.ID_STATUS = 73 AND ORDERLINE.Step = 1,1,0 AS 'Cancelled', (@tl-@wb-@sys-@can) AS 'Balance' FROM ORDERHEADER LEFT JOIN ORDERLINE ON ORDERHEADER.ID = ORDERLINE.ID_ORDERHEADER LEFT JOIN DISPATCHLOG ON ORDERLINE.ID = DISPATCHLOG.ID_ORDERLINE WHERE (ORDERHEADER.AvailableAt BETWEEN '[param:Start Date]' AND '[param:End Date]') GROUP BY 'Date' ORDER BY 'Date' DESC but on mysql Ver 11.13 Distrib 3.23.36, for pc-linux-gnu (i686) it doesnt calculate the balance column -- SELECT @com:=SUM(PAYROLL.Amount) AS 'Commissions', '0.00' AS 'Other', @com+'' AS 'Total', DATE_FORMAT('[param:StartDate]', '%y/%m/%d') AS 'StartDate', DATE_FORMAT('[param:EndDate]', '%y/%m/%d') AS 'EndDate' FROM CONTRACTOR LEFT JOIN PAYROLL ON CONTRACTOR.ID = PAYROLL.ID_CONTRACTOR WHERE Aka = '[param:Aka]' AND (PAYROLL.CreateStamp BETWEEN '[param:StartDate]' AND DATE_FORMAT('[param:EndDate]', '%Y%m%d235959')) This works fine on the older version(3.23.32), but on the newer(3.23.36), I needed a GROUP BY Aka SQLException: General error: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause == SQLState: S1000 VendorError: 1140 -- I do not thing the data is relative to this question. Note: about the use of [param:Variable], these select statements are stored in the database, they are put through a custom parser that prompts for these variables when the statement is used to generate forms 1. How could I get the first statement return a value for 'Balance' in the newer version? 2. What is that SQL Exception telling me? 3. And out of curiosity, why is there now a requirement for the GROUP BY when there wasnt before? Luc Foisy - 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: PostgreSQL
Shankar Unni writes: And one big reason not: no native Windows port. Or Mac port (though that has probably changed with OS/X - anyone working on that?). Here is an unofficial install package of the new PostgreSQL 7.2: http://www.entropy.ch/software/macosx/postgresql/ You can get an unofficial install package of MySQL 3.23.47 and 4.0.0 from the same place: http://www.entropy.ch/software/macosx/mysql/ Downloads of the official MySQL for Mac OS X can be found at the MySQL downloads page. There is also a deb package of MySQL that can be found here: http://homepage.mac.com/macgurutemple/mysql_on_macosx.html -- David Steinbrunner mailto:[EMAIL PROTECTED] MFM Communication Software, Inc. http://www.mfm.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: upgrading mysql-3.23.48 and rpm package.
hi, on redhat 6.2, you have a package manager (rpm) version which cannot interpret rpm's created with package manager version 4. afaik, rpm 3.0.6 is able to read both formats, so you should upgrade your package manager software to version 3.0.6 to be able to install the new mysql package. don't know if there any libc issues, though. bye michael Ireneusz Piasecki wrote: Hi all. I would upgrade my mysql serever from RPM: rpm -i MySQL-3.23.48-1.i386.rpm only packages with major numbers = 3 are supported by this version of RPM error: MySQL-3.23.48-1.i386.rpm cannot be installed What can i solve this problem. I use Red Hat 6.2 Where can i find the RPM engine to install mysql 3.23.48 ? With mysql 3.23.47 was no problem Pleas help. Regards I. Piasecki -- Okresl Swoje potrzeby - my znajdziemy oferte za Ciebie! [ http://oferty.onet.pl ] - 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: Rebooting MySql
Hi Is there any reason to reboot MySql periodically? Short answer: No. My server has been up for just under 6 months without a reboot, That is not unusual. and some of the queries are noticeably slower. This may well be because the data has grown, but I wondered if there is any memory leakage at all. If you have deleted from many tables, OPTIMIZE will be your friend. You might also want to go back to your queries, do an EXPLAIN and see if you need to redefine indexes, etc. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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: Problems with select
Hi. You forgot around temp example: mysqlselect datetime,temperatura from temperatura where datetime=2002-02-08 09:30:00 and temperatura=15.8; Regards /PM\ Miguel Figueiredo wrote: Hello all, I have a (probably dumb) question. My table temperatura has two fields described bellow. mysql describe temperatura; +-+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | datetime | datetime | YES | | NULL| | | temperatura | float| YES | | NULL| | +-+--+--+-+-+---+ 2 rows in set (0.00 sec) When I try to select rows with floats, the result is an empty set. For example: mysql select datetime,temperatura from temperatura where datetime=2002-02-08 09:30:00; +-+-+ | datetime| temperatura | +-+-+ | 2002-02-08 09:30:00 |15.8 | +-+-+ 1 row in set (0.00 sec) got one row :) mysqlselect datetime,temperatura from temperatura where datetime=2002-02-08 09:30:00 and temperatura=15.8; Empty set (0.00 sec) -- same thing but with the temperatura value, gives me an empy set :( When I try to select another row but the temperatura field has an integer this is what happens: mysql select datetime,temperatura from temperatura where datetime=2002-01-25 09:40:00; +-+-+ | datetime| temperatura | +-+-+ | 2002-01-25 09:40:00 | 16 | +-+-+ 1 row in set (0.00 sec) --- got one row :) mysql select datetime,temperatura from temperatura where datetime=2002-01-25 09:40:00 and temperatura=16; +-+-+ | datetime| temperatura | +-+-+ | 2002-01-25 09:40:00 | 16 | +-+-+ 1 row in set (0.00 sec) -- It works now :/ Does anyone have an idea why this happens? How can I select float numbers? Best wishes, Miguel - 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 - 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: Distributed Fulltext?
Why is it that Altavista can index terabytes overnight and return a fulltext boolean for the WHOLE WEB within a second, and Mysql takes so long? I don't know about Altavista, but if you read up on Google, they do indeed do some sort of spreading of keywords across multiple machines - last I saw they had over 10,000 machines. But you have to ask the question as to whether this would be an appropriate development for MySQL. This would seeem to me to be a very complex development of interest to a very small subset of MySQL customers. MySQL, it seems to me, specialises in doing the mainstream database manipulations used by many people simply, quicly, and (my most important criterion) reliably. Such a development would, IMO, be away from the whole trend of MySQL, and shouldn't happen before triggers, stored procedures and the like - if ever. A multe-machine distributed database is a wholly different animal - IMO again. If I was going there I wouldn't start from here - it would need a clean brain look at the whole problem. If you really need such searches, Google survives by licencint that technology - speak to them. Alec Cawley - 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
I must ask.
Hello ppl. I am playing hard with mysql, cause my ultimate objective is to make a big databank for a game. I notice that i cant find a way to make relations beteen tables. Must i do it also with PHP ? Thanck for the tips. And if i am rigth, my dont gays put so relation ? - 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: Distributed Fulltext?
Ooops, factual error: If, say, Google, can search 2 trillion web pages, averaging say 70k bytes each, in 1 second, and Mysql can search 22 million records, with an index on 40 bytes each, in 3 seconds (my experience) on a good day, what's the order of magnitude difference? Roughly 10^9. Google has only 2 Billion with a B web pages. So the order of magnitude difference is only 10^6. But that still leaves a mysql Fulltext search a MILLION times slower than Google. Someone correctly pointed out today that it's not Mysql's job to be Google, and I agree. But it seems to me that it would be fair for mysql to be able to handle searches in under 1 second for databases 1 millionth the size of Google. All I want here is a decent flexible phone book lookup, not a search engine. -steve - 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: I must ask.
Hello André I am playing hard with mysql, cause my ultimate objective is to make a big databank for a game. I notice that i cant find a way to make relations beteen tables. Must i do it also with PHP ? Just to confuse you, the mathematical jargon for table is relation... May I suggest that you search the web for some tutorials on the use of SQL in general/MySQL in particular - it will help your playing, and ultimately your game. There are some RDBMS-es that implement SQL to the point of apparently setting up a construct that links tables - foreign key based data integrity being the first example that springs to (my) mind. However relational logic suggests that the way to set up a linkage between tables is to show this in dynamic logic - your SQL statement, eg SELECT * FROM tbl1, tbl2 WHERE tbl1.PrimaryKey = tbl2.ForeignKey; will produce a result-row for every intersection 'match' between the two tables. One of the MAJOR advances of relational databases over their predecessors was this move 'away' from structural 'connections'. However these have been maintained as 'features' by some, usually to promote speed/efficiency. Perhaps this is a major interest for you/gaming - but in which case, you will find that indexed-file systems will likely be a better/faster/smaller option. (?) An original objective, and one well worth bearing in mind today, is that relational technology allows one to establish a 'relationship' between items of data today, that was not anticipated/designed into the system when it was first built n-years ago. Flexibility! Regards, =dn - 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: Retrieving a numeric series from a SELECT
Hi, Fellows! Sorry for my poor english. Thats why I can explain myself properly. :- ) What I really want is a calculate column with consecutive numbers from n to m, where n can be any number, and m depending on the result of the WHERE clause. I need to print pay orders for students, this pay orders use a consecutive control number, the criteria to select students use to be variable. Example: SELECT colnum, id, name FROM students WHERE status='OK' (4 rows selected) or SELECT colnum, id, name FROM students WHERE grade=1 (230 rows selected) colnum must be the same number that is printed in the pay order. The next pay order on the printer is 15000, then colnum of the first row of the SELECT must be 15100, the next row 15001, and so on. you get it? I remember that the sample i saw in this SQL list only used a SELECT statement without a table, or whitout a memory variable. it was only a SELECT something, and it returns a consecutive serie from 1 to 100. Thanks again. __ José Angel Blanco Linares Depto Servicios de Apoyo Académico Centro de Enseñanza Técnica Industrial 3641-3250, ext: 266 - 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: Distributed Fulltext?
I said: Why is it that Altavista can index terabytes overnight and return a fulltext boolean for the WHOLE WEB within a second, and Mysql takes so long? On Friday 08 February 2002 08:56, Vincent Stoessel wrote: Apples and oranges. Yeah, I know. But let's see if we can make some distinctions. If, say, Google, can search 2 trillion web pages, averaging say 70k bytes each, in 1 second, and Mysql can search 22 million records, with an index on 40 bytes each, in 3 seconds (my experience) on a good day, what's the order of magnitude difference? Roughly 10^9. Have you seen the /hardware/ run that enterprise with? Irrelevant, you're unlikely to get 9 orders of magnitude difference with faster hardware or even with clustering. Also, their software is optimized for full text searches and that is /all/ they do. Mysql is an SQL database and is optimized as such. Absolutely granted. You are completely right. And I don't expect the data format to change. BUT: thought experiment: When Mysql decides to generate a FULLTEXT index, it is using an index file, the .MYI file, which can have ANY FORMAT it wants to. If the .MYI format is poorly optimized for fulltext searches, they can improve the format. They can even introduce a new index file type .MYF solely for optimizing fulltext searches. None of this need have any impact on the data file format or the SQL search optimizations, and yet it could still improve the search speed for fulltext. It might not help as much for the slow indexing, but it could certainly improve the performance of the search. Thinking out loud... Steve - 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: ORDER BY an ABSolute value
This would fail dramatically. The squareroot of a negative number is not a real number. Matthew Walker Ecommerce Project Manager Mountain Top Herbs -Original Message- From: Keith A. Calaman [mailto:[EMAIL PROTECTED]]=20 Sent: Monday, February 11, 2002 5:24 PM To: David Mackay; 'David Turner' Cc: [EMAIL PROTECTED] Subject: RE: ORDER BY an ABSolute value Did you try SQRT(id)? Should rank things the way you want if it's legal (*_*) -Original Message- From: David Mackay [mailto:[EMAIL PROTECTED]] Sent: Monday, February 11, 2002 7:08 PM To: 'David Turner'; David Mackay Cc: '[EMAIL PROTECTED]' Subject: RE: ORDER BY an ABSolute value Thanks for your quick response Dave, Have tried this, but no bannana... I get: You have an error in your SQL syntax near 'abs(id)' at line 1 Seems a not-valid thing to do these days... Is there a work around? Dave from Oz select id from table_name order by abs(id); Dave Dundee! On Tue, Feb 12, 2002 at 09:46:25AM +1000, David Mackay wrote: G'Day folks, New to PHP/MySQL. Want to order the results of a SELECT by their 'absolute' value, not their sign. So regardless of whether it's +37 or -37, they are both 37 and thus both come between 38 36, regardless of whether they're + or -. eg. 100 -99 -92 91 72 -38 37 -37 etc... I have the order DESC but it puts 100 first, and -100 last, (that's what you'd usually want, but I need the extremes grouped at one end, down to the middle point zero..). In my fantasy world I could do a ORDER BY ABS(number) DESC If there was any other way to achieve the same result that would be just dandy... I bet this is obvious. Thanks for your time, David Mackay - 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 - 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 --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.323 / Virus Database: 180 - Release Date: 2/8/2002 =20 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.323 / Virus Database: 180 - Release Date: 2/8/2002 =20 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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
export file
i need help!! i an doing my assignment using mysql to establish a database!! i already create the table and insert the data, but i don't know how to create a export file, anyone can help me!! _ Ãâ·ÑÏÂÔØ MSN Explorer£ºhttp://explorer.msn.com/lccn/intl.asp¡£ - 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
Rebooting MySql
Hi Is there any reason to reboot MySql periodically? My server has been up for just under 6 months without a reboot, and some of the queries are noticeably slower. This may well be because the data has grown, but I wondered if there is any memory leakage at all. Anybody with more experience got any thoughts? Thanx Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- - 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
mysql command dumps core.
Hello, I am relatively new to Mysql. I am have a strange problem that is preventing me from setting up my database to test out mysql as a backend to an IDS system. When I start the mysql command I get the following output. srsids01# ./mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.47-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. Segmentation fault (core dumped) srsids01# I am running mysql 3.23.47 on a Solaris 8 server. Please help. Thanks! vjl -- V.Jay LaRosa EMC Corporation Systems Administrator 171 South Street (508)435-1000 ext 14957Hopkinton, MA 01748 (508)497-8082 fax www.emc.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: Set up help needed
If you want to get the most out of MySQL, then you really need to learn to use the command line tools. MySQL has the easiest to use tools and language of any database I've used. SQL allows you to do really powerful things easily. Download the Windows version and install it. You can use the mysql client to connect to the remote server from your Windows box. Alternatively, and probably even easier, download an SSH client for Windows (do a Google search for putty) and SSH in to your Linux box. Then use the mysql client locally there. For long SQL queries like creating tables, I find it useful to type them up in Notepad and then paste them into the client. If you made a mistake, fix it in Notepad and paste it again. mysqldump is also a very handy tool for viewing a table's schema. What is the best or easiest client to set up on my NT4 workstation so I can connect to the MySql server and create tables, etc via scripts? I tried downloading one or two from the MySql site but none of them seem to have any instructions as to how to set them up or get the connection going. - 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: Duplicate Records
any reason not to select distinct into a tmp table? On Sun, 10 Feb 2002, DL Neil wrote: Rich, How does one go about removing one of two identical records in a MySQL database? My mistake in an earlier database was not applying a unique number to each record (1, 2, 3, ---). I know it's possible to use the DISTINCT operator to show only one of the identical records in a result, but that does not remove one of them from the database. =Ouch! =Do you have a particular criteria to delete one or other of any duplicate records, or are they absolutely identical (and therefore it doesn't matter which stays/goes)? =You cannot really risk automated deletion in either case! - if the records are slightly different, criteria must be established to determine which is right/should be kept; - if they are absolutely identical, how will you identify in a WHERE clause that only one record of that criteria is to be removed of two identical rows? =To identify the duplicate rows try:- SELECT column(s), COUNT(*) as duplicates FROM tbl GROUP BY key HAVING duplicates 1 =Depending upon the number of rows returned, you could then feed those keys into a series of DELETE ... LIMIT 1 commands - assuming duplicates are strictly 'two of'; otherwise it might be easier to take the listing and do it by hand (using a mgmt package). One thought that I had would be to add a unique number to each record, and that could probably be done manually, one record at a time, but is there a way to automate the process so that unique numbers could be assigned with one command? =ALTER TABLE allows the addition of a new column. The only question is whether to do it before (if it might help the editing job) or after, weeding out the duplicates (to get a more continuous AUTO_INCREMENT sequence, if it's at all of interest). =Regards, =dn - 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 - 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
MYSQL Batch Mode Question
All, Say I have a batch file I am attempting to run like this: shellmysql -h host_name -u username -p batch_file Further, say this batch_file has multiple WHERE lines as follows: use dB1 SELECT x,y,z,zXXName, zYYName, zZZName FROM tblXXX WHERE zXXName = run0 AND zYYName = farther0 AND zZZName = morefarther0 {?} zXXName = run1 AND zYYName = farther1 AND zZZName = morefarther1 {?} zXXName = run2 AND zYYName = farther2 AND zZZName = morefarther2 {?} zXXName = run3 AND zYYName = farther3 AND zZZName = morefarther3 {?} zXXName = run4 AND zYYName = farther4 AND zZZName = morefarther4; QUESTION: What do I use for {?} so that the additional lines are interpreted? Thanks alot for the insight. Tom - 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: SQL-Problem with three tables and joins
G'day all, For some reason I am receiving messages from the MySQL list (only) which I have already received - some more the a week ago. Is anyone else getting repeat posts? kim - 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: How to connect in non-interactive mode
Dan, Thanks for your response. I see what you're saying. However, in our case, the only 'clients' connecting to the MySQL server are software modules, no humans. Well actually, humans too, but only through our software modules. So no clients getting disconnected until the server is restarted is what we want. Some of them, in this case our system logger, may have long periods of inactivity (ie, overnight, more than 8 hours, no logs to store in the database, so no activity on that client connection). So... if there is a way to set the interactive_timeout to infinite, that would be most handy. Regards, Becky Daniel Rosher wrote: If you set the interactive_timeout to infinite then no clients will get disconnected until the server is restarted. I know the perl DBI allows for a client to implement ping ($dbh-ping) ... this will reset the timeout for that particular client, allow the client to check for connectivity, without having to make ever client connect forever!!. I'm sure the JDBC driver will allow for this. Regards, Dan -Original Message- From: beckymcelroy [mailto:beckymcelroy]On Behalf Of Becky McElroy Sent: Tuesday, 12 February 2002 9:31 a.m. To: [EMAIL PROTECTED] Subject: How to connect in non-interactive mode Hello- How does one run a client in non-interactive mode? Or, is there a way to set interactive_timeout at the MySQL server to infinite? We have a software component connecting to MySQL (using Mark Matthews JDBC driver) and there may be a long time ( 8 hours) of inactivity. Any suggestions much appreciated - Becky McElroy - 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 -- Becky McElroy Scientific Software Engineering, Inc. 1004 Copeland Oak Drive Morrisville, NC. 27560 Phone: 919-462-0303 ext.24 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: domain name bug
the GRANT command errors out when the domain name has a '-' dash in it. mysql grant Select, Insert, Update, Delete on X.* to [EMAIL PROTECTED] identified by 'X'; ERROR 1064: You have an error in your SQL syntax near '-tech.net identified by 'X'' at line 1 Try grant Select, Insert, Update, Delete on X.* to 'X'@'X.f-tech.net' identified by 'X'; Sincerely, Craig Vincent - 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: Re: Access denied to apache@localhost
On Wed, Feb 13, 2002 at 12:04:22AM +0100, [EMAIL PROTECTED] wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query Should I just put these words into my signature, just so I can contribute to the mailing list? If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: On Sun, Feb 10, 2002 at 11:58:12AM +0100, Nick Wilson wrote: Hi all I've just set up and the latest problem I have getting started is that 'Access denied for user: apache@localhost' msg. How can I put that right? Either a) grant appropriate priveleges to the user in question, or b) tell the invoking code to use a user ID with appropriate priveleges. It obviously depends on what problem you're atcually trying to solve... -- Nick Wilson Tel:+45 3325 0688 Fax:+45 3325 0677 Web:www.explodingnet.com -- Brian 'you Bastard' Reichert [EMAIL PROTECTED] 37 Crystal Ave. #303 Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - 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
Slow Database
Hello, I'm writing a application to connect to a mySQL database over a modem link. Since the link is very slow, I need to limit the number of records returned when the database and tables are opened. I connect to the database using a ODBC driver. Are there any settings I can set to limit the number of records returned ? Thanks for any help. Neil - 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
Embedded Quotes
Please ignore earlier message -- mySQL stores embedded quotes just fine. Wyckham Seelig - 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 command dumps core.
On Tuesday 12 February 2002 03:22 pm, Vjay LaRosa wrote: srsids01# ./mysql Welcome to the MySQL monitor. ?Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.47-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. Segmentation fault (core dumped) That looks like some library incompatibility. Are you using our binary, or have you built your own? In any case, can you post the output of mysqlbug? -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - 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
Delete oldest data
I'm using MySQL-Max-3.23 and I need to keep a table from exceding a certain size. I plan on running a cron job that checks the size of the table and if its close to the max, executes a delete statement. The problem I have is that 3.23 doesn't support delete from .. order by .. -- this was added in MySQL 4.x .. Ideally I'd use DELETE FROM table ORDER BY datestamp ASC LIMIT 1, but of course this doesn't work. How are people doing this in 3.23.xx? Thanks, Eric Mayers - 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
binary installation questions
OK, here's the setup: Using the binary package in mysql-3.23.47-sun-solaris2.8-sparc.tar.gz all unpacked, untarred (with gtar, yes), etc. I follow all the instructions in the INSTALL-BINARY file, with this one change: I intend for the user to be mysql_st, not mysql. But according to note #4 in the INSTALL-BINARY file this should be okay, although it does not say whether this necessitates any changes elsewhere in the installation. Oh, and also the data directory in /usr/local/mysql is softlinked to another drive with much more space. Permissions on that directory are set to the mysql_at user with the rest set to root, as described in the INSTALL-BINARY file. And yes, I ran the scripts/mysql_install_db script. OK, so here is what I have now: I can connect to mysql as mysql -u root and no password is needed. So according to page 414 of the MySQL book I need to run mysqladmin -u root password to fix this. BUT!! Everytime I try to do this, whether as myself, as the mysql_st user, or even as root, I get this: [4:17pm] cindy@steph /usr/local/mysql/bin/mysqladmin -u root password kool /usr/local/mysql/bin/mysqladmin: unable to change password; error: 'You must have privileges to update tables in the mysql database to be able to change passwords for others' At the same time: [4:17pm] cindy@steph mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 to server version: 3.23.44 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql works fine (again, no matter who I am) Eh? Now, what I'm guessing is that I should be using GRANTS to set this up, as outlined in the online manual in section 4.3.6 (Setting up Passwords). But before I muck around much more, I'd like some assurance that I'm on the right track, or whether I've flubbed something else that should be taken care of instead. Particularly because according to this section, doing the insert into the user table should be exactly equivalent to the mysqladmin command that fails above. I also wonder if maybe the INSTALL-BINARY file should include a brief note on setting up a root password, even if just a pointer or whatever. Thanks, --Cindy -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Old Messages
Yep I have been getting lots of old messages as well. Sinisa Milivojevic [EMAIL PROTECTED] 02/12 3:43 PM Van writes: Greetings: Anyone else getting lists messages from several days, or longer ago? Phrrrp!: sql database table mysql Van -- = Linux rocks!!! http://www.dedserius.com/ = Me too !! We shall investigate this matter. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.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: Better solution?
Hi all, could someone help me? I'm looking for one quick answer. I've code like this $result = mysql_query(SELECT FROM ...); $rows = mysql_fetch_array($result); Problem is, when the result have no rows. I receive an error message from MySQL on the second line, which is OK. My question is: Is there a better solution than put a line like If (mysql_num_rows($result)0) ... Could I solve it differently? Thanks Karel $query=(select distinct(publisher) from books order by publisher); $result=mysql_query($query); if (!$result) die (Query 164 failed.); # you can of course do whatever you like here for ($i=0; $i = $PubCount; $i++) { while ($records=mysql_fetch_array($result)) { print option $records[$i]; } } -- Amer Neely, Softouch Information Services W: www.softouch.on.ca E: [EMAIL PROTECTED] V: 519.438.5887 Perl | PHP | MySQL | CGI programming for shopping carts, data entry forms. We make web sites work! - 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
Is The MySQL Mailing List On Crack?
I'm getting duplicate messages separated by several hours. Am I alone in this, or are others seeing it too? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 5 days, processed 189,890,555 queries (402/sec. avg) - 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: How to connect in non-interactive mode
Becky, You would have to change NET_WAIT_TIMEOUT in mysql_com.h in the source from 8*60*60 to n*60*60 where n is in hours, then recompile. Regards, Dan -Original Message- From: beckymcelroy [mailto:beckymcelroy]On Behalf Of Becky McElroy Sent: Wednesday, 13 February 2002 8:08 a.m. To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: How to connect in non-interactive mode Dan, Thanks for your response. I see what you're saying. However, in our case, the only 'clients' connecting to the MySQL server are software modules, no humans. Well actually, humans too, but only through our software modules. So no clients getting disconnected until the server is restarted is what we want. Some of them, in this case our system logger, may have long periods of inactivity (ie, overnight, more than 8 hours, no logs to store in the database, so no activity on that client connection). So... if there is a way to set the interactive_timeout to infinite, that would be most handy. Regards, Becky Daniel Rosher wrote: If you set the interactive_timeout to infinite then no clients will get disconnected until the server is restarted. I know the perl DBI allows for a client to implement ping ($dbh-ping) ... this will reset the timeout for that particular client, allow the client to check for connectivity, without having to make ever client connect forever!!. I'm sure the JDBC driver will allow for this. Regards, Dan -Original Message- From: beckymcelroy [mailto:beckymcelroy]On Behalf Of Becky McElroy Sent: Tuesday, 12 February 2002 9:31 a.m. To: [EMAIL PROTECTED] Subject: How to connect in non-interactive mode Hello- How does one run a client in non-interactive mode? Or, is there a way to set interactive_timeout at the MySQL server to infinite? We have a software component connecting to MySQL (using Mark Matthews JDBC driver) and there may be a long time ( 8 hours) of inactivity. Any suggestions much appreciated - Becky McElroy - 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 -- Becky McElroy Scientific Software Engineering, Inc. 1004 Copeland Oak Drive Morrisville, NC. 27560 Phone: 919-462-0303 ext.24 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: long query on php
This may be due to 'max_allowed_packet' size ... have a look at http://www.mysql.com/doc/P/a/Packet_too_large.html This is configurable Regards, Dan. -Original Message- From: Alain Fontaine - Consultant and developer [mailto:[EMAIL PROTECTED]] Sent: Friday, 8 February 2002 1:30 a.m. To: 'MySQL list (E-mail)' Subject: RE: long query on php Have you tried just putting the querys into a variable and pass the var along to mysql_query() ? Did it not work? --- Alain Fontaine Consultant Developer VAlain S.A. Tel: +32-4-2522950 --- -Original Message- From: savaidis [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 8:14 PM To: MySQL list (E-mail) Subject: long query on php How is possible to pass a long query to MySQL server with php? I mean i.e a create table statement with more than 400 chars. Have I to use shorter create and then alter? Thanks Makis - 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 - 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: Distributed Fulltext?
On Tue, 2002-02-12 at 15:38, Steve Rapaport wrote: David Axmark writes: So the standard answer with Apples and Oranges certainly apply here! More like Äpplen och Apelsiner, that is, different but similar. You Swedish guys should know. Thanks for answering, David, I appreciate the attention from a founder. I also appreciate your point that Google is updating continuously and therefore not always caught up to the current state of the web. But isn't that a problem with indexing speed, not with search speed? Their search speed is still amazing, as is Altavista, and most of the other engines. Your other point about exact vs. approximate answers is unclear, I expect that Google's answers are exact for their currently available indexes at any given time. But even if they are approximate, I'd be happy with that too. The scoring on a FULLTEXT search in Mysql is exact but based on a formula that is approximate anyway. No, MySQL returns all data according to a search. Web engines return what they they find on one search machine. So you can get different results with Google every time you hit refresh if you are routed to different machines. This had happened to me when I was looking for the number of matches and not the result itself. So we should try to make fulltext searches with a limit between 10 and 100 be fast to be closer to google. I have also head about some other things web search engines do since I know some people at FAST but I have forgot that already. I'll summarize this thread best I can. From the math I used, we started with my estimate of 10^9, which was mistaken. The real figure was 10^6, that is, Google searches fulltext about a million times faster than Mysql. Then we used Google's 1 machines +DRAM indexing to reduce the gap to 10^2, or 100 times faster. I would say we should reduce it even further but that could be discussed. It turns out that 100 times is about the factor that is causing my application problems. If it just ran 100 times faster it would be about as fast as a regular indexed search, and I'd be happy. A few people suggested that Mysql shouldn't try to be faster, I (and some high-support customers like Mike Wexler) disagreed. And Alex Aulbach, bless him, actually did his homework and showed that things could be much improved with smart index techniques like inverted files. We will try to make every feature as good as possible. But we do have limited resources. Then Sergei Golubchik wrote back to say he had taken some of the good ideas and inserted them into the TODO list, although he had higher priorities at the time. And I was satisfied for now, although my application still isn't working satisfactorily due to a really slow and CPU-hungry FULLTEXT search. Well there is always the option of sponsoring further fulltext development. We have a guy who has been working on the GNU fulltext engines who is interesting in working with MySQL fulltext. But for the moment we can not afford it. So if some of you are interested in sponsoring this (or know about others who might be) write to [EMAIL PROTECTED] I think that's our story so far. Steve Rapaport Director, Technical Services A-Tono - 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: error messages
http://www.mysql.com/doc/p/e/perror.html use perror to get more information: Error code 2: No such file or directory Regards, Dan -Original Message- From: user lacko [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 12 February 2002 4:15 a.m. To: MYSQL Subject: error messages Hi! Where can I find the MySQL error messages ? I have error message can't find file host.MYD errno: 2 Is it permission problems? Lacko - 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 - 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: forms?
I personally use PHP which it would use your familiar web browser. You can also use PERL, and I believe ASP as well. PHP is easy for a front end and doesn't take much to develop! There is also a web based GUI for mySQL called phpmyadmin which is awesome if you know nothing about administering mySQL. You can pratically do anything from it! Good luck! -Original Message- From: Jim Hatridge [mailto:[EMAIL PROTECTED]] Sent: Monday, February 11, 2002 8:21 AM To: [EMAIL PROTECTED] Subject: forms? Hi all... First of all, I think that MySQL is really great. The only thing I need now is a way to do forms. What I'm looking for is something that will allow me to do data input, update, output either at the command line or by browser, but only if I can use lynx as the browser. I wrote a database system for my stamp export company back around 1990 in Dbase III+. That system is getting very old now. DBase III + has scripts and forms. Something like this is what I'm looking for. Thanks JIM -- Jim Hatridge Linux User #88484 -- BayerWulf Linux System # 129656 The Recycled Beowulf Project Looking for throw-away or obsolete computers and parts to recycle into a Linux super computer - 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 - 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
Using password: _No_
Hello, Can anyone help? When trying to run mysql I get an ERROR 1045: Access denied for user: 'root@localhost' (Using password: No). In the documentation it mentions 'Using password: YES'. Have tried starting mysql with --skip-grant-tables and then mysqladmin flush-privileges which doesn't seem to do anything. What does the Password No mean? How do I correct it short of reinstallation? Well, I don't know maybe I need to start over yet again. Thanks very much for any help, Cliff - 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: Columns
Try 1) create table c as select a,b as b1,b as b2 ... from a 2) drop a; 3) alter table c rename to a; Instead of 2) you can rename the table and drop later, or tar-up the .MYI,.MYD and .frm files for the table before doing the above. Regards, Dan -Original Message- From: Keith A. Calaman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 13 February 2002 5:08 a.m. To: Max Mouse; [EMAIL PROTECTED] Subject: RE: Columns Sounds like an UPDATE: http://www.mysql.com/doc/U/P/UPDATE.html UPDATE TABLE SET columnname1 = columnname2 where KEY = KEY Something like that probably. If it was me I would copy the whole table so I had a backup...UPDATES and DELETES can be destructive if writting improperly (*_*) -Original Message- From: Max Mouse [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 4:11 PM To: [EMAIL PROTECTED] Subject: Columns Hey all, Is it possible to copy the contents of one column to another column using mySQL? I just changed my table structure by adding a few more columns and I need to be able to move the data from the original column to 4 new columns and then drop the orginial. I know that the proper query for sql is that I added with ALTER and remove with DROP. But I can't find anything that would allow me to move the data from one column to another. Anything I can do? Max - 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 - 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: outer join + count() + group by
Hi, Yes I have made some mistakes. There was problem with the outer join. It should have been ad outer joined to review, not the other way. I didn't notice the first tabel person in the query. Try the following with two tables ad and review and later add person table. We don't know the columns of person table to relate with the othere tables. select ad.id,sum(review.id is not null) from ad left join review on ad.id=review.id group by ad.id; OR select ad.id,sum(if (ifnull(review.id,0)=0,0,1)) from ad left join review on ad.id=review.id group by ad.id; the id column can't be null, it's defined as varchar(16) not null. Even though the id column is defined as not null and there is no null values in this field of review table, the value returned by the query for this column can be null for outer joins. In fact we use outer joins to return a row even if there is no rows in the second (right table in the outer join ) with all the columns of this row with null value. The above query (first) behaves like this: It will return one row each for each row in review table which has a corresponding id in ad table. Additionally it will return one row for each row in ad table for which there is no corresponding row in review table but with review column value as null. So for each id in ad for which there is no reviews the review.id will be null and the expression (review.id is not null) will return 0, for which there is review it will return 1. Hence summing this on this expression should give you the right answer. Anvar. - 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: Distributed Fulltext?
[comparisons to Google...] While any speed up with a full table fulltext search would be helpful and useful, there are instances where the search is intersected with another column and the problem of search is therefore more complex but also leads to potential optimizations. In our case we rarely do searches that use just the FTS index. More commonly, we do searches based on other columns and a FTS indexed text column. The WHERE clause restraints on the non-text column usually eliminate 99% of the rows. Fulltext indexes can not be combined with numeric columns in a single composite index, so as far as I can tell, MySQL performs the FTS then filters the results to meet the WHERE conditions. I believe the required amount of the index to be paged into main memory is much larger than might otherwise be needed (since the indexes are larger than available memory, and there a zillion other tables accessed simultaneously, this tends to effect all queries). So besides the obvious desire to lower the size of indexes, it would be beneficial if a combined index could lower the number of matching rows and the amount of the index needed to be in memory. I actually find it odd for people to not be using secondary columns in a fulltext search, other than to do something like Google (which does not lend itself to using SQL in the first place. Of course, I have a myopic view stemming from my own requirements and experience. All that said, I'm glad to have the FTS we have. Thanks Sergei for such a wonderful improvement to LIKE '%word%'! Sincerely, Steven Roussey http://Network54.com/?pp=e PS - is FTS search and update O(n^2)? - 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: simple question on joining tables
Yaniv, Set the field of user_id to INT then make the size 10 or larger, which will produce an ID size of up to 99. Now set it to auto increment then set the user_id field to Primary key, this should solve your problem, if I understand you correctly -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 12, 2002 11:02 AM To: [EMAIL PROTECTED] Subject: simple question on joining tables Hi everyone, I am starting my first mySQL project . I want to create 2 tables Table User : userId , FisrtName, LastNAme, Gender with userId defined as the primary key that auto-increments I want that each time I insert a new row in this table, the field userId is inserted as well in another table named Results Table Results : userId, Category, ResponseText, I read carefully the mySQL tutorial and tried to define userId field when creating Table Resultsas follows : userId smallint not null referenced user . I tried to use then the function last_insert_id to insert new rows in Table Results but it didn't work . I always get 0 in the userId field of TableResult. Thank you very much in advance for your help ! - 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: Old Messages
You know, I am getting rather fed up with all this too. Tried to unsubscribe, but the admin isn't hearing me or something. I sent 17 unsub requests today and I haven't gotten a single confirmation e-mail yet. They will probably come next week and by that time, the request would have expired. E-mail provider won't let me bounce e-mails either. Timothy D Legg [EMAIL PROTECTED] On Tue, 12 Feb 2002, Marjolein Katsma wrote: Yes I'm getting them, with an extra footer, and (sometimes) different encoding. I've just been plowing though this mailbox to delete duplicates... At 06:29 2002-02-12, you wrote: Greetings: Anyone else getting lists messages from several days, or longer ago? Phrrrp!: sql database table mysql Van -- -- Marjolein Katsma HomeSite Help - http://hshelp.com/ - Extensions, Tips and Tools The Bookstore - http://books.hshelp.com/ - Books for webmasters and webrookies - 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
problems with default-character-set=cp1251
Hi. I use mysql 3.26.47, established on Windows 2000 Server Russian. After I set property - default-character-set = cp1251, web-server on a beginning to give out a massage - File ' c:\mysql \\ share\charsets \?.conf ' not found (Errcode: 2) Character set ' .) 14 'is not a compiled character set and is not specified in the' c:\mysql \\ share\charsets\Index ' file If to set default-character-set = latin1, all ok. But I for correct sorting need cp1251. What it is necessary to make? It is my file my.ini from the catalogue winnt # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking character-sets-dir=c:/server/usr/mysql/share/charsets/ default-character-set=cp1251 set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = flush_time=1800 # Uncomment the following rows if you move the MySQL distribution to another # location basedir = c:/server/usr/mysql/ datadir =C:/server/usr/mysql/data # Uncomment the following rows if you are using InnoDB tables # Create the directories ibdata and iblogs before to start the server. # If necessary, modify the size of the files #innodb_data_file_path = ibdata1:200M #innodb_data_home_dir = c:\ibdata #set-variable = innodb_mirrored_log_groups=1 #innodb_log_group_home_dir = c:\iblogs #set-variable = innodb_log_files_in_group=3 #set-variable = innodb_log_file_size=30M #set-variable = innodb_log_buffer_size=8M #innodb_flush_log_at_trx_commit=1 #innodb_log_arch_dir = c:\iblogs #innodb_log_archive=0 #set-variable = innodb_buffer_pool_size=80M #set-variable=lower_case_table_names =0 #set-variable = innodb_additional_mem_pool_size=10M #set-variable = innodb_file_io_threads=4 #set-variable = innodb_lock_wait_timeout=50 # Uncomment the following row if you are using a Max server and you don't want the # InnoDb tables #skip-innodb [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key=16M [client_fltk] help_file= c:\mysql\sql_client\MySQL.help client_file= c:\mysql\MySQL.options history_length=20 database = test queries_root= c:\mysql\queries last_database_file= c:\mysql\lastdb [WinMySQLAdmin] Server=C:/server/usr/mysql/bin/mysqld-max-nt.exe Best regards, Ilya mailto:[EMAIL PROTECTED] http://www.ilysha.i-am.ru - 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: Request for help in testing new replication code in 4.0.2
I have finished a script that takes the bit keeper sources and makes MySQL-4.0.2 RPMS for Redhat 6.x and apparently 7.X as well. I can put these up on our site for download if you want. They might get others trying the code out. I use the spec file included with the source code. I'll put it up if it's something you might find useful. -Original Message- From: Steven Roussey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 12, 2002 12:41 PM To: 'Sasha Pachev' Cc: 'Mysql' Subject: Re: Request for help in testing new replication code in 4.0.2 * If you want to do it this very moment, read the instructions at http://www.mysql.com/doc/I/n/Installing_source_tree.html and install a pre-release 4.0.2 MySQL on your test server. You may also wait until 4.0.2 is released, but in that case, replication in 4.0.2 may have a bug you could have caught on your system and it will not be fixed until 4.0.3. Is that possible to provide a binary snapshot of the pre-release? This can be done with some simple shell scripting, I think. I always found the one I build myself is about 30-50% slower then the official binary. I think that's related to the debian patched glibc-2.95 I am using. Hi, I have to agree with this. I don't want to have to factor in RedHat's 7.2 compiler oddities (which one to use?). Some software projects have a machine to do regular builds on a schedule. This would be ideal. You would find more people testing it. You might just do it in special cases like this one. Testing the new code is relatively easy, and does not require risking the stability of your application by running alpha code. Yes and no. Taking down a server and copying gigs of data can take down a service for a long time (moving gigs of data just is never fast, and the machine I'm going to use has lots of power but no RAID). I don't have time to download and install gcc, bitkeeper, autoconf 2.13, automake 1.4, libtool, m4, and mysql today, but I'll try and get to it this week. Taking down the server will have to wait until maintenance windows over the weekend anyhow. Our test plan for MySQL 4 is as follows: 1. Serialized writes (i.e. replication slave) for two weeks (or about 1 billion insert/update/delete type queries) with no crashes. 2. Everything. We will have the app send duplicates of all queries to the mysql 4 server, but will automatically halt when mysql 4 crashes. We will keep trying this until mysql doesn't crash for a week. 3. Check that the two servers hold the same information and return the same results from queries. With the type and volume of queries that we have, mysql alpha versions either fail within minutes/hours/days or they don't fail at all. We certainly are not the biggest user of mysql, but historically, we have been able to push it hard enough to crash. Not the stable version though. Thankfully, that takes a licking and keeps on ticking. :) Eventually (spring/summer), we will move the main server to v4 and keep the slave around as a live backup. I've been wanting such a slave for a long time. Stopping the slave to make a backup would be great. Not to mention faster fulltext and order by DESC. Sincerely, Steven Roussey http://Network54.com/?pp=e - 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
Regarding Mysql Database file system
I need to create a database in MySQL that I need to bundle with my program for distribution. The user will have its own copy of MySQL running. User using my program will not bother about database issues and the bundled database will provide him, through my application program GUI, to enter/retrieve data. For this I have thought of using the file based database ie some file(s) will be bundled that are MySQL database. The configuration to tell where the MySQL is running that will operate on the file system database bundle will be done before running the program. How to create such database in MySQL. An analogy could be the way MS-ACCESS creates the .mdb file that one can use anywhere configuring the ODBC32 on windows. For creating such database in MYSQL whether i will use INNODB Database or ISAM table. if using INNODB database it is possible then from where i will get mySql that support INNODB(which version of mysql for linux and windows). Regards - 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 I use different charsets with one server?
At 16:56 2002-02-12, Egor Egorov wrote: It's possible and it is covered in the manual as well. Look at: http://www.mysql.com/doc/A/d/Adding_character_set.html That only explains how to install extra character sets - not how to use different character sets with different databases on the same server, which the original question was about. I'd like to know the answer as well! Also: is it possible to install character sets *without* recompiling? What if my hosting provider hasn't installed a character set I need? G: sql database table mysql -- Marjolein Katsma HomeSite Help - http://hshelp.com/ - Extensions, Tips and Tools The Bookstore - http://books.hshelp.com/ - Books for webmasters and webrookies - 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
MySQL and Web Objects5 (OS X)
Does anyone know how to cause WO to recognize and offer MySQL as an adaptor? i.e., how to get WO5 to use MySQL?) Thanks in advance. Ted Rogers FSU MathNet | Apple Systems - 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
request for help with multiple JOINs
Is there anyone who could help me with this simple query. Did i ask anything in a wrong way because I still got no answers to my request? SELECT at.name, av.value, at.unit, at.id, a.product_id FROM attribute_type at LEFT OUTER JOIN attribute a on (at.id = a.type_id) LEFT OUTER JOIN attribute_value av on (av.id = a.value_id) WHERE (a.product_id = 21 OR a.product_id IS Not NULL OR a.value_ID IS Not NULL OR a.type_ID IS Not NULL) and the result: | name | value | unit | id | product_id | | height | 10| cm | 1 | 21 | | width| 20| cm | 2 | 32 | | width| 30| cm | 2 | 40 | | diameter | 222 | cm | 3 | 21 | Number of Results: 4 I only want to have attributes for one product_id (e.g. 21). However, every attributetype available has to be in the result and any corresponding value or NULL. So my preferred result should look like that: | name | value | unit | id | product_id | | height | 10| cm | 1 | 21 | | width| NULL| cm | 2 | NULL | because width is for this product still empty but should be available to fill with a value | diameter | 222 | cm | 3 | 21 | Number of Results: 3 Any chance to achieve this? I have 3 tables to describe attributes for a product database. e.g. attribute_type: ++--+--+ | id | name | unit | ++--+--+ | 1 | height | cm | | 2 | width| cm | | 3 | diameter | cm | ++--+--+ attribute_value: ++--+ | id | value| ++--+ | 1 | 10 | | 2 | 20 | | 3 | 30 | | 4 | 222 | ++--+ attribute: ++--+--++ | id | type_id | value_id | product_id | ++--+--++ | 1 | 1| 1| 21 | | 2 | 2| 2| 32 | | 3 | 2| 3| 40 | | 4 | 3| 4| 21 | ++--+--++ In a web form where I edit the product, there is also an attribute section where I would like to give certain attributes values specific for the product. The problem is when I associate values to attributes in one product, the same attribute in other products is hidden (not in the result of the query). Therefore I would like to list all attribute_types for each product regardless if there are values assigned or not. I used the following query and I think there must be something wrong: SELECT at.name, av.value, at.unit, at.id, a.product_id FROMattribute_type at LEFT OUTER JOIN attribute a on (at.id = a.type_id) LEFT OUTER JOIN attribute_value av on (av.id = a.value_id) WHERE (a.product_id = 21 OR a.product_id IS NULL OR a.value_ID IS NULL OR a.type_ID IS NULL) regards, Andreas - Andreas Habereder private: [EMAIL PROTECTED] mobile: +49 172 838 7771 - - 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: Windows Version of MySQL licensing question?
David Ayliffe a écrit : I read in Paul's book that to use MySQL on a Windows platform you need to pay for it. I have downloaded a version of the server and client software from the official MySQL.com site and installed it on XP without incident. Paul also writes that the version for windows will be shareware only. This seems not to be the case I have had my server 3.23.43-nt up and running now for some months. I only use the server for my own uni work but I am curious as to what is the real deal. Anyone? Thanks lots David Ayliffe ICQ# 125646758 Query table database Hi, MySQL licensing has changed since Paul's book was published. You should check MySQL site for up to date information: http://www.mysql.com/support/arrangements.html Regards -- Joseph Bueno NetClub/Trader.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
Re: Probably OT - SQL join help needed
Hello Robert, Hello experts, I've got a small problem with an sql query here that's got me completely stuck. In my MySQL database I've got two tables here that have identical design, e.g. table 'detail' - columns sales-order, quantity, part-number, price, date-sent and table 'archived' - columns sales-order, quantity, part-number, price, date-sent. Detail is for 'active' orders, and Archived is for fulfilled orders. Now some genius here wants to see all the records that reference a particular part number, irrespective of whether in archived or detail. My current approach is to create a temporary table with all the suitable records from detail, add in any suitable records from archived, and then do a select * query from this temporary table, before dropping it. Now it strikes me that this isn't a very smart way to do this, and it's probably achievable via joins but, try as I might, I can't get the system to do it. Anyone got any bright ideas/suggestions? If the tables are identical then UNION may be what you're looking for: 6.4.1.2 UNION Syntax (MySQL 4.0). For every user genius who forgets to put something in the spec up-front, there's a computer guy who's smarter! Regards, =dn - 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: mysqlhotcopy in 4.0.1
On Mon, 2002-02-11 at 02:04, Jeremy Zawodny wrote: On Mon, Feb 11, 2002 at 03:04:47PM +1300, Ian Collins wrote: In mysqlhotcopy, function copy_files, there is a line, my @non_raid = grep { $_ !~ m:\d\d/: } @$files; (dont you just love perl?). If the database name in question is, for example, testone_750, a perfectly legal database name (??), then no files get copied! and the mysqlhotcopy fails. That's bad. I presume (again ??) that the above line is trying to strip out all ../ matches. Actually it's trying to strip out every database name which contains two digits in a row. That's clearly not ideal. We need something more specific there. [snip] What it should be doing is identifying every file which isn't in a two-digit sub-directory inside a database directory. It should read: my @non_raid = grep { ! m:/\d{2}/[^/]+$: } @$files; I've sent a patch to internals. == Martin - 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
Probably OT - SQL join help needed
Hello experts, I've got a small problem with an sql query here that's got me completely stuck. In my MySQL database I've got two tables here that have identical design, e.g. table 'detail' - columns sales-order, quantity, part-number, price, date-sent and table 'archived' - columns sales-order, quantity, part-number, price, date-sent. Detail is for 'active' orders, and Archived is for fulfilled orders. Now some genius here wants to see all the records that reference a particular part number, irrespective of whether in archived or detail. My current approach is to create a temporary table with all the suitable records from detail, add in any suitable records from archived, and then do a select * query from this temporary table, before dropping it. Worst still, I'm going to have to do this all via PHP :-( Now it strikes me that this isn't a very smart way to do this, and it's probably achievable via joins but, try as I might, I can't get the system to do it. Anyone got any bright ideas/suggestions? Regards Bob Cross. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This message is confidential. It may also be privileged or protected by other legal rules. It does not constitute an offer or acceptance of an offer, nor shall it form any part of a legally binding contract. If you have received this communication in error, please let us know by reply then destroy it. You should not use, print, copy the message or disclose its contents to anyone. E-mail is subject to possible data corruption, is not secure, and its content does not necessarily represent the opinion of this Company. No representation or warranty is made as to the accuracy or completeness of the information and no liability can be accepted for any loss arising from its use. This e-mail and any attachments are not guaranteed to be free from so-called computer viruses and it is recommended that you check for such viruses before down-loading it to your computer equipment. This Company has no control over other websites to which there may be hypertext links and no liability can be accepted in relation to those sites. Scottish Newcastle plc Registered in Scotland, Registered Number 16288 Registered Office: 33, Ellersly Road, Edinburgh, EH12 6HX * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * - 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: ignoring in ORDER
Hi Abdul, Can Mysql ignore alphabet , like 'the' when ORDERING a name. A sample : I have a table (first_name) : - The test. - Falcon - The Armageddon - Battle When ordering it will be : Battle Falcon The Armageddon The test. Can it be like this : The Armageddon Battle Falcon The test. Is that possible? =Anything is possible! =This is a common 'problem' in the library world where in the context of titles 'the', 'a', 'al' are considered 'noise-words'. Some of the 'library' scripts available on the various support/tutorial sites may offer assistance. =Here are three choices: 1 regimentation: insist that all title data entered, is expressed in a sequence-friendly fashion, eg Armageddon, The; 2 smart data: have two title fields in your db. One which is the title straight off the cover. The other which is a 'sequencing field' - sometimes with noise words removed/words re-ordered, sometimes exactly the same. Users should be presented with the former, but the RDBMS is programmed to use/sequence using the latter; 3 smart queries: (this is more of a challenge) look up IF() [6.3 Functions for Use in SELECT and WHERE Clauses] and rewrite the query so that if the title commences with a noise word miss it out of the sort process, eg if the left three characters are the sequence using only the Arrmageddon part. =Regards, =dn - 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: problem with Uploading a CSV file.
Hi Stanley, I am trying to upload a CSV file directly to the database using command strSQL = load data infile ' strFNPath ' into table products where , strFNPath =Path of the file But its not working correctly ,it uploads some data and then seem to stop or enter values in the wrong places. Any help will be appreciated. Well done, you've successfully negotiated half of the 'traps' inherent in using LOAD DATA! I'll take a flying leap into the void and suggest that you are using Windows! Windows terminates lines in text files with CR and LF characters. Unix however uses only LF (and Mac only CR - but that's another story). Check out 6.4.9 LOAD DATA INFILE Syntax and alter the MySQL default terminators/delimiters to suit the source file format. Regards, =dn - 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
errno: 138
Hi I have just moved a copy of a database from and old MySQL server to a new one. But I am geting the folowing error. ---snip--- mysql select * from customers; ERROR 1016: Can't open file: 'customers.ISD'. (errno: 138) ---snip--- Can some one please tell me what this error is, or tell me where I can find a list of error to what the problem is. Thanks Simon Green Internet Systems Engineer -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. - 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