RE: Order by on Alphnumeric
select * from table where strcol REGEXP ^[[:digit:]]+$ order by strcol Regards Dan -Original Message- From: Prospect'In [mailto:[EMAIL PROTECTED]] Sent: Thursday, 28 February 2002 11:31 a.m. To: [EMAIL PROTECTED] Subject: Order by on Alphnumeric sql,query Good Day, I have a varchar field which contains alphanumeric data. I want to be able to order this field by only the numeric values in the field. help!! Rick - 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: ignore words in full text indexes
David,
I think the nominal minimum word length is 4 so 'fiat' will not be indexed.
This can be modified however.
regards,
Dan
-Original Message-
From: David yahoo [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 28 February 2002 12:28 p.m.
To: [EMAIL PROTECTED]
Subject: ignore words in full text indexes
Hi all,
I m using mysql 4.01 alpha
I read in the doc :
MySQL uses a very simple parser to split text into words. A
``word'' is any
sequence of letters, numbers, `'', and `_'. Any ``word'' that is
present in
the stopword list or just too short (3 characters or less) is ignored.
My queries let me devine that =4 words are ignored.
How to know more.
SELECT * FROM T_Stories WHERE match against ('+fiat -bagnole' IN
BOOLEAN MODE)
doesnt give any row
but when changing fiat to fiato it gives me row ?
I regenerate the index beetween queries - nothing ?
Is there any easy way planed fro changing stopword and words min length in
the future ?
By an easy way I didint want to recompile a mysql server only
changing some
files or syetem database,
like the mysql database is ?
Thanks.
Regards.
_ Do You
Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can somebody help me with round (columna,columnb) ?
Perhaps this is something like what your looking for? select substring(round(number,digits) ,1,instr(round(number,digits),'.')+digits) as roundednumber,digits,number from testme Regards, Dan -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 27 February 2002 6:32 a.m. To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Can somebody help me with round (columna,columnb) ? Hi Richard, i want to round() the value of a column, but the number of digits is depending on the value of another column. i found out, that this doesn't work: create table testme - (number double (5,4),digits tinyint(3)); Query OK, 0 rows affected (0.08 sec) mysql explain testme; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | number | double(6,4) | YES | | NULL| | | digits | tinyint(3) | YES | | NULL| | ++-+--+-+-+---+ 2 rows in set (0.08 sec) mysql insert into testme values (100.4235,3),(85.4,1); Query OK, 2 rows affected (0.08 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select * from testme; +--++ | number | digits | +--++ | 100.4235 | 3 | | 85.4000 | 1 | +--++ 2 rows in set (0.08 sec) mysql select round(number,digits) from testme; +--+ | round(number,digits) | +--+ | 100.4240 | | 85.4000 | +--+ 2 rows in set (0.08 sec) is there any way i could do this with *one* query? the only solution i see is to read the digits-column with a script and then create a new query, but thats not possible in my case... It does the same for me (that's not much help, but it's obviously not 'you'!) I first went looking to see if there is a MySQL parameter which says how many decimal digits will be displayed, by default - but failed to find it and may be confusing myself with another product. Certainly nothing in my.cnf. I then wondered if the reason the result is expressed this way is because of the schema which defines: number double (5,4) Sure enough, changing number to double (5,3) and then re-running your tests, makes it change. Again no help to you. I notice that all of the examples in the manual for round(), truncate(), and format() render the D argument as a constant. So I'm wondering if the digits is being ignored? Sorry not to have more to offer. Is there a wiser mind that can shed some light? =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: SET field=field+1 no longer works?
Have privileges changed? Dan -Original Message- From: Tyler Longren [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 27 February 2002 11:55 a.m. To: DL Neil Cc: MySQL List Subject: Re: SET field=field+1 no longer works? Well, here's the query that PHP is generating: UPDATE users SET board_posts=board_posts+1 WHERE username='tyler' AND password='myfakepassword' If I copy and paste that exactly into the mysql client, it's executed correctly. If I use phpMyAdmin to execute it, it IS NOT executed correctly (same as in my PHP code). I really don't think this is a problem with my coding since it worked with previous versions of mysql. Could PHP just be screwing up while sending the query to MySQL? Also, if I use MySQL Front (www.mysqlfront.de), the query doesn't get executed properly. It only works correctly when issuing the query from the mysql command line client. :) Tyler - Original Message - From: DL Neil [EMAIL PROTECTED] To: Tyler Longren [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 26, 2002 8:52 AM Subject: Re: SET field=field+1 no longer works? Hi Tyler, [back on-list so that others can offer their wisdom!] OK, so it's not a problem with the MySQL client, then it's likely the PHP. Most likely that the username and password data values are strings and need to be properly contained with single- or double-quotation marks. If you need further assistance, first try some debug ECHOs on the three fields used in the query, and ECHO the query itself immediately prior to the call to MySQL. (is the last how you posted (copy-pasted) the query into the MySQL client, or did you type it into MySQL by hand?) If lights still don't go off, please post the PHP code snippet. Regards, =dn I tried it in PHP first, and it doesn't work in that. But, when I use the mysql client, it works as expected. Any ideas? Hello Tyler, Did someone pick up this question - haven't spotted a response on the list? I haven't spotted any such mis-behavior under either Win2000 or WinNT. Are you entering the query at the command line or into some tool? Have you tried another client? If it is still unresolved, send me (NOT the whole list) the actual query, and a short table with sample data, and I'll try it on my two Win boxes here. Regards, =dn I'm running MySQL on a Windows 2000 box. I was running 3.23.47 until 3.23.49 was released. After upgrading to 3.23.49, queries like this don't work: UPDATE test_table SET board_posts=board_posts+1 WHERE username='blah' AND password='blah'; Normally, that would increment the value in board_posts by 1, this no longer happens. Is there a different way I should do this now? Thanks, Tyler - 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 - 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: Combining two similar queries?
You could create a table that is the combination of manufacturer and products table, with identical column and key information as the custom table -- call this say manprod table, then create a merge table from the manprod and custom table, and run one select query against the newly created merge table: http://www.mysql.com/doc/M/E/MERGE.html Regards, Dan -Original Message- From: Nathan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 27 February 2002 12:33 p.m. To: MySQL Subject: Combining two similar queries? Good afternoon, list! I am attempting to figure out how to get the following two queries smashed into one. Both of these work very well on their own. I am not sure if this is possible, but it seems like it should be :-) =-=-=-=-= QUERY #1 =-=-=-=-= SELECT mt.manufacturer, pt.model, rt.qty, rt.item_order, rt.description, rt.status, rt.version, if(rt.status = 'del', 0, (rt.qty * rt.this_sell)) AS sell_ttl FROM room_table rt, products pt, manu mt WHERE pt.product_id = rt.product_id AND pt.manu_id = mt.manu_id AND rt.room_id = 2 AND rt.print = 'Yes' AND rt.type = 'product' ORDER BY rt.item_order, mt.manufacturer, pt.model; =-=-=-=-= QUERY #2 =-=-=-=-= SELECT ct.manufacturer, ct.model, rt.qty, rt.item_order, rt.description, rt.status, rt.version, if(rt.status = 'del', 0, (rt.qty * rt.this_sell)) AS sell_ttl FROM room_table rt, custom ct WHERE ct.product_id = rt.product_id AND rt.room_id = 2 AND rt.print = 'Yes' AND rt.type = 'custom' ORDER BY rt.item_order, ct.manufacturer, ct.model; Both return the same number of columns with the same information type in each column, but I can't figure out how to get both integrated into one query. I have been staring at the manual long enough to make me think either this isn't possible (unlikely) or that I'm just trying to over-complicate the hell out of it (much more likely) and I'm missing some basic concept. I have tried a few things and it's beyond me at the moment... any insights would be greatly appreciated!!! Further info is definitely available if needed. Thanks! # Nathan - 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: MySQL php - assigning date variables
SQL statement are: What is proper way to define a variable to include all dates newer than 1995-01-01? $query = ??? select * from table where date '1995-01-01' What is proper way to define a variable to include all dates older than 1995-01-01? $query = ??? select * from table where date '1995-01-01' What is proper way to define a variable to include all dates between 1995-01-01 and 1998-12-31? $query = ??? select * from table where date '1998-12-31' and date '1995-01-01' Regards, Dan -Original Message- From: Craig Westerman [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 26 February 2002 12:18 p.m. To: MySQL List Subject: MySQL php - assigning date variables $query = mysql_query(SELECT * FROM table WHERE date LIKE '%. $query .%'); // returns all items in database $query = 2001-01-01 mysql_query(SELECT * FROM table WHERE date LIKE '%. $query .%'); // returns all rows that have 2001-01-01 as the date What is proper way to define a variable to include all dates newer than 1995-01-01? $query = ??? What is proper way to define a variable to include all dates older than 1995-01-01? $query = ??? What is proper way to define a variable to include all dates between 1995-01-01 and 1998-12-31? $query = ??? Everything I tried gives me an error. This has to be simple, but I must be overlooking something. Where would I find the answer? Thanks Craig [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Beginner needs help
Change it to INSERT into book( isbn, title, authlname, authfname, publisher, pubdate, dewey, lcnum ) values ( '1-56592-434-7', 'MySQL mSQL', 'Yarger', 'Randy Jay', O'Reilly, null, null, null ), ( '0-312-25313-3', 'Coup de Grace', 'Borthwick', 'J S', St Martin's Minotaur, null, '813.54-dc21', 'PS3552.O756 C68 2000' ); i.e. drop 'values' for the second row Regards Dan -Original Message- From: Bob Rea [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 26 February 2002 12:34 p.m. To: [EMAIL PROTECTED] Subject: Beginner needs help I'm getting a syntax error when I try to insert more two sets of values into a table. I have looked at the manual on the insert statement, and don't see what is wrong. Can someone tell me. Here's the info. mysql describe book; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | booknum | int(20) | | PRI | NULL| auto_increment | | isbn | varchar(14) | YES | | NULL| | | title | varchar(50) | YES | | NULL| | | authlname | varchar(20) | YES | | NULL| | | authfname | varchar(20) | YES | | NULL| | | publisher | varchar(20) | YES | | NULL| | | pubdate | date| YES | | NULL| | | dewey | varchar(20) | YES | | NULL| | | lcnum | varchar(20) | YES | | NULL| | | checkin | date| YES | | NULL| | | checkout | date| YES | | NULL| | | duedate | date| YES | | NULL| | +---+-+--+-+-++ 12 rows in set (0.00 sec) Here's the attempt: mysql INSERT into book( - isbn, - title, - authlname, - authfname, - publisher, - pubdate, - dewey, - lcnum - ) - values ( - '1-56592-434-7', - 'MySQL mSQL', - 'Yarger', - 'Randy Jay', - O'Reilly, - null, - null, - null - ), - values( - '0-312-25313-3', - 'Coup de Grace', - 'Borthwick', - 'J S', - St Martin's Minotaur, - null, - '813.54-dc21', - 'PS3552.O756 C68 2000' - ); ERROR 1064: You have an error in your SQL syntax near 'values( '0-312-25313-3', 'Coup de Grace', 'Borthwick', 'J S', St Martin's Minot' at line 21 TIA -- Bob Rea ** On the side of the box, under 'System Requirements', it said 'Requires Windows 95 or better'. So I installed Linux. ** [EMAIL PROTECTED] http://home.earthlink.net/~sfpetard/ - 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: any way to do this with SQL ???
what about RENAME current_table temp_table CREATE TABLE current_table (identical create_definition to current_table except adding the datetime column type) select * from temp_table checking here DROP TABLE temp_table Regards, Dan -Original Message- From: Laszlo G. Szijarto [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 26 February 2002 4:14 p.m. To: [EMAIL PROTECTED] Subject: any way to do this with SQL ??? Thank you in advance for your help. I have a table which has a timestamp column. Now, I want to add a column of type datetime and transfer the original timestamp (date or original insertion) into the new datetime column (so as to preserve this information for all posterity in case an new update to a row should alter the timestamp column). I had not intended to do any updates on this table (I know, my bad ! , for lack of planning). But now I see a need to do so. Can I do this using sql ? Thank you very much in advance, Laszlo - 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: help with big table search
Jamie, I think your approach of a cross-reference table is a good start. This is similar to creating a stemming index. Perhaps you might like to look into, for example, some perl Modules for stemming (like Linga::Stem) to further reduce your data space. Perhaps since there may be a large resultset from the first select, and then feeding this information back to the server, and the second search also returning a large resultset, perhaps you'd be better off trying to obtain the results in one select statement --- what about something like: select eventlog.* from eventlog left join crossref on (eventlog.id=crossref.id and word = 'HELLO') ORDER BY eventlog.id DESC LIMIT 20; ? Regards, Dan -Original Message- From: Jaime Teng [mailto:[EMAIL PROTECTED]] Sent: Monday, 25 February 2002 2:34 p.m. To: [EMAIL PROTECTED] Subject: help with big table search Hi, I have a table and currently has about 1.6 million entries. It is a table of events with date/time and description of the event. mysql describe eventlog; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | timestamp | int(10) unsigned | | MUL | 0 || | description | char(100)| | | || +-+--+--+-+-++ My task is to search this table for any particular word that may occur anywhere in the description field: ie. SELECT * FROM eventlog WHERE description LIKE '%pattern%' ORDER BY id DESC LIMIT 20; Considering that I am using LIKE instead of = as a search option, making description into an index would not do any good. This search works well ONLY if the items to search are relatively near the top of the table *AND* most importantly, there are at least '20' matching items available on the table. *IF* the table contains only 19 matches or less, then the SELECT will search through the whole table and I may have to wait some 2~3 minutes to get the result. Then I started using cross-reference table. mysql describe crossref; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | word | char(15) | | MUL | | | | id| int(10) unsigned | | MUL | 0 | | +---+--+--+-+-+---+ word is an independent index, id is an independent index For every entry into the eventlog table, I broken down each word from description and inserted them into the crossref table. This way, whenever I want to find the word HELLO, all i need to do is: SELECT id FROM crossref WHERE word = 'HELLO' ORDER BY id DESC limit 20; and then use the results into another search: SELECT * from eventlog where id in (previous result); However still, for whatever reason, this search isnt working well. Though the search time is better than before, it still takes about 30~60 seconds for an answer. (sometimes fairly fast 5 seconds). SOMETIMES, searching through this crossref were actually slower. I'd like to know how you people come up with a very good table and search. Eventlog is currently 200MB in size. Jaime - 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: Can I do it with single query in mysql?
If you only interested in getting the overall sum from the child tables you could try the following: 1) Change your child create statements to: CREATE TABLE child1 ( auto_nr int(11) unsigned zerofill NOT NULL, link int(11) unsigned , value int(11), PRIMARY KEY auto_nr(auto_nr), KEY link1_key(link) ) CREATE TABLE child2 ( auto_nr int(11) unsigned zerofill NOT NULL, link int(11) unsigned , value int(11), PRIMARY KEY auto_nr(auto_nr), KEY link2_key(link) ) 2) Then create a merge table like: CREATE TABLE childMerge ( auto_nr int(11) unsigned zerofill NOT NULL, link int(11) unsigned , value int(11), KEY auto_nr(auto_nr), KEY link_key(link) ) TYPE=MERGE UNION=(child1,child2); 3) The the one statement then becomes: Select master.link, max(dat) as m_dat, sum(childMerge.value) from master left join childMerge using (link) group by master.link KEY is a synonym for INDEX Regards Dan -Original Message- From: Alvis [mailto:[EMAIL PROTECTED]] Sent: Friday, 22 February 2002 2:44 p.m. To: [EMAIL PROTECTED] Subject: Can I do it with single query in mysql? Hello all, Suppose I have 3 tables, 1. master (auto_nr, link, dat) CREATE TABLE master ( auto_nr int(11) unsigned zerofill NOT NULL, link int(11) unsigned zerofill NOT NULL, dat DATE NOT NULL, PRIMARY KEY auto_nr(auto_nr), KEY link_key(link) ) 2. child1 (auto_nr, link1, value1) CREATE TABLE child1 ( auto_nr int(11) unsigned zerofill NOT NULL, link1 int(11) unsigned , value1 int(11), PRIMARY KEY auto_nr(auto_nr), KEY link1_key(link1) ) 3. child2 (auto_nr, link2, value2) CREATE TABLE child2 ( auto_nr int(11) unsigned zerofill NOT NULL, link2 int(11) unsigned , value2 int(11), PRIMARY KEY auto_nr(auto_nr), KEY link2_key(link2) ) At the moment I use temporary tables following way: CREATE TEMPORARY table temp1 Select link, max(dat) as m_dat, sum(value1) as val1 from master left join child1 on master.link=child1.link1 group by link CREATE TEMPORARY table temp2 Select link, sum(value2) as val2 from master left join child2 on master.link=child2.link2 group by link //OK. Actually I use 2 steps to build one temporary table: a. Full CREATE TEMPORARY TABLE statement using KEY (or maybe better INDEX???) if needed. b. INSERT INTO to populate created table. FINALLY: Select m_dat, temp1.link, val1, val2 from temp1, temp2 where temp1.link=temp2.link Can I do it with one select statement in mysql? Sorry to say, I have to create up to 12 different temporary tables to get result sets for everyday use. As you may guess execution of bunch of queries takes time (up to 1 min) and may be considered as rather messy. Ive come to conclusion that SQL optimization with MySql (using temporary tables) is time consuming. IMHO I need feature called CREATE VIEW , but perhaps I have to learn some of features of standard SQL (i.e. progressive extensive use of different JOIN types;-) so, really good SQL books regarding this subject; your recommendations. My database is not large (50 tables, max 25 columns per table, currently overall amount ~50Mb plus about 0.2-1Mb each day). I try to stick with SQL92 and keep my client code independent from particular SQL server implementation; maybe someday I have to shift to other back-end, so I want to make migration to different back-end as easy as possible. Speed penalty is inescapable. My system configuration is Compaq PROLIANT ML370, 128Mb RAM, 933 Mhz PIII, RedHat Linux 7.1. Its dual processor system, so some hardware upgrade is quite possible. I have small number of clients (1-20). Any suggestions for optimal MySql server configuration? Any help will be appreciated. p.s. Whats the difference between KEY and INDEX in CREATE TABLE syntax (any +/- effect on JOIN)? Regards, Alvis __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL query
I presuming you are expecting more results from the first select statement, hence the reason and indicating this in the second, should then your first statement be: select edate from traffic where year(edate)='2001' group by edate; ? Regards, Dan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, 22 February 2002 2:30 p.m. To: [EMAIL PROTECTED] Subject: SQL query Everyone, Have u encountered this kind of query? mysql select edate from traffic where year(edate)='2001' group by month(edate); ++ | edate | ++ | 2001-04-29 | | 2001-05-01 | | 2001-06-01 | | 2001-07-01 | | 2001-08-01 | | 2001-09-01 | | 2001-10-30 | | 2001-11-01 | | 2001-12-01 | ++ 9 rows in set (10 min 53.83 sec) mysql select count(*) from traffic; +--+ | count(*) | +--+ | 2645563 | +--+ 1 row in set (0.00 sec) mysql is there a problem with my select statement?... R.B.Roa Traffic Management Engineer PhilCom Corporation Tel.No. (088) 858-1028 Mobile No. (0919) 30856267 - 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: SQL query
I can't see any way of making this statement faster, however: One method to increase speed, especially since the amount of data is large is to create seperate tables into your lowest common denominator, here year (e.g. traffic_2000,traffic_2001 etc). Then create a merge table with the same name as your current one i.e. 'traffic', such that you don't break any current functionality in your application, then depending on your year value, to increase speed, consult the specific year table e.g. traffic_2001. re: http://www.mysql.com/doc/M/E/MERGE.html Regards, Daniel -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, 22 February 2002 4:20 p.m. To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: SQL query My concern here is the query speed Mysql query on the statement result so slow...is there a way to improve it? R.B.Roa Traffic Management Engineer PhilCom Corporation Tel.No. (088) 858-1028 Mobile No. (0919) 30856267 -Original Message- From: Daniel Rosher [SMTP:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 11:15 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject:RE: SQL query I presuming you are expecting more results from the first select statement, hence the reason and indicating this in the second, should then your first statement be: select edate from traffic where year(edate)='2001' group by edate; ? Regards, Dan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, 22 February 2002 2:30 p.m. To: [EMAIL PROTECTED] Subject: SQL query Everyone, Have u encountered this kind of query? mysql select edate from traffic where year(edate)='2001' group by month(edate); ++ | edate | ++ | 2001-04-29 | | 2001-05-01 | | 2001-06-01 | | 2001-07-01 | | 2001-08-01 | | 2001-09-01 | | 2001-10-30 | | 2001-11-01 | | 2001-12-01 | ++ 9 rows in set (10 min 53.83 sec) mysql select count(*) from traffic; +--+ | count(*) | +--+ | 2645563 | +--+ 1 row in set (0.00 sec) mysql is there a problem with my select statement?... R.B.Roa Traffic Management Engineer PhilCom Corporation Tel.No. (088) 858-1028 Mobile No. (0919) 30856267 - 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: insert select in ONE statement?
use LAST_INSERT_ID() to get the last inserted id http://www.mysql.com/doc/G/e/Getting_unique_ID.html and 'The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). ' Regards, Dan -Original Message- From: Lee P Reilly [mailto:[EMAIL PROTECTED]] Sent: Thursday, 21 February 2002 8:50 a.m. To: MySQL Subject: insert select in ONE statement? Hi, I wonder if anyone can offer me some advice with this one: I have a table called 'iq_data' holding just a primary key and a field called 'iq_data'. +++--+-+-++ | Field | Type | Null | Key | Default | Extra | +++--+-+-++ | iqid | int(11)| | PRI | NULL| auto_increment | | iqdata | mediumtext | | | NULL|| +++--+-+-++ 'iq_data' contains the contents of a plain text file in the following format: .010 .1083649E+03 .2186916E+02 .0013470 .1993729E+03 .2738670E+02 == + approx. 100-1000 more lines. After I insert data into the table, I need to get the iqid that was generated. One solution is to do something like: SELECT iqid from iq_data where iqdata=the contents of the file; // ^ this will give me the correct answer, but it may be a // little inefficient as there may be thousands of records, and the // search string is very large or SELECT iqid from iq_id order by iqid (and get the last iqid generated) // ^ querying like this immediately after inserting data to the table, // but assumes that another record has not yet been added A problem will arise if say, data is inserted by user 1, and then by user 2, and then the iqid for user1 is requested, but the iqid for user2 will be returned. So... Is there any way I can e.g. insert the data into the table and return the value of the PK that was generated in one statement? If not, of the 2 solutions above what is more efficient? Is there are more elegant solution? Thank you very much for your time; hope someone can help ;-) - Best regards, Lee Reilly /My SQL 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 - 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 HELP
You can also try: Select SUBSTRING( MAX( CONCAT(LPAD(calltime,6,'0'),finishcode) ), 7) AS finishcode, 0+LEFT( MAX( CONCAT(LPAD(calltime,6,'0'),finishcode )), 6) AS calltime From callhistory As H,calllist As L where H.rowid = L.rowid group by H.rowid This is from: http://www.mysql.com/doc/e/x/example-Maximum-column-group-row.html Although it's regarded as 'inefficient', but you can do it in one statement. Regards, Dan -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Thursday, 21 February 2002 11:18 a.m. To: David McInnis; 'Shade, Richard'; [EMAIL PROTECTED] Subject: Re: MYSQL HELP David, How would you use a temporary table? Is that the most efficient way? Use SELECT INTO temp_tbl to replace the sub-select clause. Rewrite the existing SELECT into a join with the temp_tbl. Is there another way? =dn Richard, Does anyone know what is wrong with this syntax... Select H.FinishCode, L.RowID From CallHistory As H, BD2.CallList As L Where (H.CallTime in(Select MAX(CallTime) From CallHistory Where H.RowID = L.RowID) AND H.RowID = L.RowID); Sub-selects not (yet) permitted by MySQL (RTFM: 1.7.4.1 Sub-SELECTs) Looks like a job for two SELECTs and a temporary table... 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: [PHP] MySQL question...not sure if this is the correct forum to ask.
What about REPLACE?
http://www.mysql.com/doc/R/E/REPLACE.html
'REPLACE works exactly like INSERT, except that if an old record in the
table has the same value as a new record on a unique index, the old record
is deleted before the new record is inserted'
Regards,
Dan
-Original Message-
From: Rick Emery [mailto:[EMAIL PROTECTED]]
Sent: Friday, 15 February 2002 11:10 a.m.
To: [EMAIL PROTECTED]
Cc: '[EMAIL PROTECTED]'
Subject: FW: [PHP] MySQL question...not sure if this is the correct
forum to ask.
-Original Message-
From: Peter Ruan [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 14, 2002 4:04 PM
To: [EMAIL PROTECTED]
Subject: [PHP] MySQL question...not sure if this is the correct forum to
ask.
Hi,
Can the UPDATE statement have conditional check embedded in it? I
have a page that displays a record (in a FORM format) that the user can
change the information on each column. I want to check each column and
see which has been changed and update the table for entries that were
changed only.
for each column data {
if column is changed
then update;
else
do nothing;
}
Maybe I am making this too complicated than it needs and just go ahead
and update all of the columns regardless with the new values, regardless
they are actually different or not.
Thanks in advance,
-Peter
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.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: how do you increment a field on the fly?
David, try 1) create temporary table x (a INT PRIMARY KEY AUTO_INCREMENT) [select_statement] where [select_statement] is some legal select statement, presumebly select g.Description,sum(i.Retail_Value) from Groups g, Item i where i.Group_ID =g.Group_ID and i.Group_ID 0 group by i.Group_ID order by i.Category_ID; then 2) select * from x When you close the connection the trmporary table x will be removed. The same temporary table name can be used for multiple connections. Otherwise get the resultset into your application, and then increment a counter in a for/while loop etc Regards, Dan -Original Message- From: David S. Jackson [mailto:[EMAIL PROTECTED]] Sent: Friday, 15 February 2002 12:08 p.m. To: [EMAIL PROTECTED] Subject: Re: how do you increment a field on the fly? How do you add a column that increments on the fly those fields you've selected to print in a mysql query? Example: select count(g.Group_ID) as Number, g.Description, sum(i.Retail_Value) from Groups g, Item i where i.Group_ID = g.Group_ID and i.Group_ID 0 group by i.Group_ID order by i.Category_ID; My intention was to have the Number field simple be a number that increments by one for each line that prints out. But, of course, the documentation says that's not what the count function is for. How can I add a simple little old line counter? -- David S. Jackson[EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= I put instant coffee in a microwave and almost went back in time. -- Steven Wright -- David S. Jackson[EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= I'm not afraid of death -- I just don't want to be there when it happens. -- Woody Allen - 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: data type bigint(20)
http://www.mysql.com/doc/N/u/Numeric_types.html 20 is the display width: 'for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 4' Regards Dan -Original Message- From: John D. Kirkpatrick [mailto:[EMAIL PROTECTED]] Sent: Friday, 15 February 2002 12:27 p.m. To: [EMAIL PROTECTED] Subject: Re: data type bigint(20) MySQL Gurus, I'm trying to figure out what the number after the type means for numbers. I noticed in someone's code that for the ID field they used bigint(20). bigint I thought was fixed at 8 bytes. Does this override the size? 20 bytes??? 20 bits? Thanks John - 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: 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: 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: 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: How to connect in non-interactive mode
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 - 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
You can do the following to remove duplicates: - create table foo as select distinct cols from table_name_containing_duplicates; - drop table_name_containing_duplicates; - alter table foo rename to table_name_containing_duplicates; Done. Now, make a primay key for the new table!! -- this will avoid duplicates. Regards, Dan -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Monday, 11 February 2002 8:30 a.m. To: Rich; MySql Subject: Re: Duplicate Records 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
RE: Error 13
From the Manual (21.11 Problems with File Permissions) By default MySQL will create database and RAID directories with permission type 0700. You can modify this behavior by setting the UMASK_DIR variable. If you set this, new directories are created with the combined UMASK and UMASK_DIR. For example, if you want to give group access to all new directories, you can do: shell UMASK_DIR=504 # = 770 in octal shell export UMASK_DIR shell /path/to/safe_mysqld In MySQL Version 3.23.25 and above, MySQL assumes that the value for UMASK and UMASK_DIR is in octal if it starts with a zero. So either the dir has to be owned by the same user which runs mysqld or change the current directory permissions and the UMASK_DIR to prevent further issues. Regards, Dan -Original Message- From: Quentin Bennett [mailto:[EMAIL PROTECTED]] Sent: Thursday, 10 January 2002 8:27 a.m. To: 'P.Agenbag'; mysql Subject: RE: Error 13 Hi Error 13 is from the OS - nothing to do with locks or whatever. However, when you add a new field (or change the table structure in (nearly) any way), mysqld will create a temporary table in the database directory - are you sure that the permissions on the directory are correct? Remember, it is the user running mysqld that needs the permissions, not the user running the command. HTH Quentin -Original Message- From: P.Agenbag [mailto:[EMAIL PROTECTED]] Sent: Thursday, 10 January 2002 6:14 a.m. To: mysql Subject: Error 13 Hi I have a table that I want to add a new field to while running on the server, however, when I try to add a new field, it comes up with the error13 message, saying it doesn't have permission, yet the files and folder are all chmod 777. Can it be due to the fact that there is a permanent connection to this table from another server and that mysql is preventing any major changes? Should I just try to make a copy of the table and make the changes to it and then overwrite the old one? Is there a cleaner way of managing your tables and to make changes without having to resort to this manual way? Thanks - 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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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
