skip-delay-key-write
Anybody knows...whats the effect if use this option to my.cnf SKIP-DELAY-KEY-WRITE I had a problem when recovering from crash...all my data is incomplete...I wonder that MySQL server had its delay from writing the data from memory to disk...is this correct?...or I got wrong about what I think... R.B.Roa PhilCom Corporation Tel. No. 858- Mobile No. (63) (919-xxx) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 the first letter MATCH in mySQL
I want to select all the records, which have the first letter matched the letter 'A'. $SQL_get_lyrics = SELECT lyric_title FROM lyrics WHERE lyric_title LIKE \'A%\'; Some how this statement given me other record, which has the letter A inside, too like: Know A Word and other other unexpected records Son Nguyen __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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
Re: SELECT the first letter MATCH in mySQL
There is a method using trim() but I think it's better to use RLIKE ^A.* regards, Jan Peuker - Original Message - From: Son Nguyen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 8:42 AM Subject: SELECT the first letter MATCH in mySQL I want to select all the records, which have the first letter matched the letter 'A'. $SQL_get_lyrics = SELECT lyric_title FROM lyrics WHERE lyric_title LIKE \'A%\'; Some how this statement given me other record, which has the letter A inside, too like: Know A Word and other other unexpected records Son Nguyen __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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: SELECT the first letter MATCH in mySQL
This works on MsSQL: SELECT lyric_title FROM lyrics WHERE LEFT(lyric_title,1)='A' I want to select all the records, which have the first letter matched the letter 'A'. $SQL_get_lyrics = SELECT lyric_title FROM lyrics WHERE lyric_title LIKE \'A%\'; Some how this statement given me other record, which has the letter A inside, too like: Know A Word and other other unexpected records Son Nguyen __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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 --- Charles Brisson System Developer Phone: +46-(0)709-61 44 03 Fax: +46-(0)709-63 64 03 www.doberman.se --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: SELECT the first letter MATCH in mySQL
This brings up another question to my mind. What if I only want to show the first 10 records and then the next 10 from that query? How do I do that? -Original Message- From: Charles Brisson [mailto:[EMAIL PROTECTED]] Sent: den 19 april 2002 09:52 To: [EMAIL PROTECTED] Subject: Re: SELECT the first letter MATCH in mySQL Importance: High This works on MsSQL: SELECT lyric_title FROM lyrics WHERE LEFT(lyric_title,1)='A' I want to select all the records, which have the first letter matched the letter 'A'. $SQL_get_lyrics = SELECT lyric_title FROM lyrics WHERE lyric_title LIKE \'A%\'; Some how this statement given me other record, which has the letter A inside, too like: Know A Word and other other unexpected records Son Nguyen __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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 --- Charles Brisson System Developer Phone: +46-(0)709-61 44 03 Fax: +46-(0)709-63 64 03 www.doberman.se --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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[2]: SELECT the first letter MATCH in mySQL
Hello Mikael, Just add LIMIT 10 (which is equivalent to LIMIT 0,10) to show the first 10 records, and LIMIT 10,10 to show 10 records starting from 10. This issue is covered in online docs. Regards, Tom Friday, April 19, 2002, 9:18:53 AM, you wrote: MH This brings up another question to my mind. MH What if I only want to show the first 10 records and then the next 10 MH from that query? How do I do that? MH This works on MsSQL: MH SELECT lyric_title FROM lyrics WHERE LEFT(lyric_title,1)='A' I want to select all the records, which have the first letter matched the letter 'A'. $SQL_get_lyrics = SELECT lyric_title FROM lyrics WHERE lyric_title LIKE \'A%\'; Some how this statement given me other record, which has the letter A inside, too like: Know A Word and other other unexpected records - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 with Order By that don't use my INDEX :(
I'm using mysql 3.23.49 on Linux redhat dedicated server. Here is my query : mysql show index from MyTable; Table: MyTable Non_unique : 0 Key_name: PRIMARY Seq_in_index: 1 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 1 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 2 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 1 Column_name : Field2 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 2 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 3 Column_name : Field4 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL MyTable info : Field0 : int(10) unsigned primary key Field1 : int(10) unsigned Field2 : int(10) unsigned Field3 : int(10) unsigned Field4 : datetime Null: Yes default -00-00 00:00:00 ( but this field don't have null values ) Field5 : varchar(50); mysql explain SELECT * FROM Forums WHERE Field2=5020 AND Field3=0 ORDER BY Field4 DESC LIMIT 0,20; ** table : MyTable type : ref possible_keys: Index1,questions key: questions key_len : 1 ref : const,const rows : 390 Extra : where used; Using filesort 1 row in set (0.00 sec) We can see that index questions which have Fields 2, 3 and 4 isn't use for the order by. Why ? Thanks in advance David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Large Index's Fail
Michael Williams a écrit : Okay, this may well be a LINUX issue I am having, so be kind before you flame. When I try to index a large file, it fails with a write error on the /tmp directory. Now my /tmp directory is on a smaller partition (as sent to me by dell). Can I configure MySQL to use a different area when creating indexes? If not, would I have to configure something in the OS to use a different partition as the temporary storage area. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Hi, You can change mysqld temporary file location with: tmpdir = /path/to/some/directory in /etc/my.cnf configuration file (check MySQL manual for details). Make sure to create this directory and change its owner/permissions so that mysqld can write in it. Hope this helps -- 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: FW: Mysql unresponsive ....
Hi, May be this list was unresponsive because your description of the problem was a bit vague... I haven't experienced this kind of behaviour myself however some more details on your problem may help: - What OS are you using ? - What version of MySQL are you using ? - Was mysql server dead ? (Have you tried mysqladmin with ping, status or processlist options ?) - Have you checked mysql error log ? - When the problem happened, what kind of system activity have you seen ? Have you run top, vmstat (I assume you are on a Unix like OS) ? Hope this helps -- Joseph Bueno NetClub/Trader.com [EMAIL PROTECTED] wrote : Since I didn't get any response (like mysql server) I am sending it again. thanks Vivek -Original Message- From: CHAUDHARY, Vivek Sent: Wednesday, April 17, 2002 3:34 PM To: '[EMAIL PROTECTED]' Subject: Mysql unresponsive Hi all, Last friday we found that mysql was unresponsive (all the SQL activities were on hold) but the daemon was still running. We shutdown the daemon and restarted and everything was OK. My questions: 1) What could cause this behavior ? 2) Is there any way to monitor such a behavior ? any commands, scripts that could test for inactivity etc. Needless to say that I am a newbie on mysql front. Vivek Chaudhary Database Administrator IT STMicroelectronics, Phoenix. Phone:(602)485-2271 Fax: (602)485-6402 Cell:(602)363-1492 Email: [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: Select with Order By that don't use my INDEX :(
David BORDAS a écrit : I'm using mysql 3.23.49 on Linux redhat dedicated server. Here is my query : mysql show index from MyTable; Table: MyTable Non_unique : 0 Key_name: PRIMARY Seq_in_index: 1 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 1 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 2 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 1 Column_name : Field2 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 2 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 3 Column_name : Field4 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL MyTable info : Field0 : int(10) unsigned primary key Field1 : int(10) unsigned Field2 : int(10) unsigned Field3 : int(10) unsigned Field4 : datetime Null: Yes default -00-00 00:00:00 ( but this field don't have null values ) Field5 : varchar(50); mysql explain SELECT * FROM Forums WHERE Field2=5020 AND Field3=0 ORDER BY Field4 DESC LIMIT 0,20; ** table : MyTable type : ref possible_keys: Index1,questions key: questions key_len : 1 ref : const,const rows : 390 Extra : where used; Using filesort 1 row in set (0.00 sec) We can see that index questions which have Fields 2, 3 and 4 isn't use for the order by. Why ? Thanks in advance David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Hi, MySQL 3.23.x doesn't use the index with DESC option of order by. It seems that it is fixed in 4.x but I haven't tested it yet. Hope this helps -- 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Date function
Hello I have the following SQL and I wanna insert the current date in one of the table fields but it's not working. Can u tell me whats the problem? (I'm using mysql and php) Thx a lot $SQL = INSERT INTO orders (Customer_Id, Order_Date). VALUES($CustID,date()); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Date function
I never saw the function date(), to insert current date use: CURRENT_DATE(); regards, Jan - Original Message - From: Alia Mikati [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 11:27 AM Subject: Date function Hello I have the following SQL and I wanna insert the current date in one of the table fields but it's not working. Can u tell me whats the problem? (I'm using mysql and php) Thx a lot $SQL = INSERT INTO orders (Customer_Id, Order_Date). VALUES($CustID,date()); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Select with Order By that don't use my INDEX :(
David, it can't because this field is not the first one in your concat index! Regards, Frank. David BORDAS wrote: I'm using mysql 3.23.49 on Linux redhat dedicated server. Here is my query : mysql show index from MyTable; Table: MyTable Non_unique : 0 Key_name: PRIMARY Seq_in_index: 1 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 1 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 2 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 1 Column_name : Field2 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 2 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 3 Column_name : Field4 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL MyTable info : Field0 : int(10) unsigned primary key Field1 : int(10) unsigned Field2 : int(10) unsigned Field3 : int(10) unsigned Field4 : datetime Null: Yes default -00-00 00:00:00 ( but this field don't have null values ) Field5 : varchar(50); mysql explain SELECT * FROM Forums WHERE Field2=5020 AND Field3=0 ORDER BY Field4 DESC LIMIT 0,20; ** table : MyTable type : ref possible_keys: Index1,questions key: questions key_len : 1 ref : const,const rows : 390 Extra : where used; Using filesort 1 row in set (0.00 sec) We can see that index questions which have Fields 2, 3 and 4 isn't use for the order by. Why ? Thanks in advance David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Dr. Frank Ullrich, Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Date function
This should work : $SQL = INSERT INTO orders (Customer_Id, Order_Date). VALUES($CustID,Now()); Xavier -Message d'origine- De : Alia Mikati [mailto:[EMAIL PROTECTED]] Envoyé : vendredi 19 avril 2002 11:28 À : [EMAIL PROTECTED] Objet : Date function Hello I have the following SQL and I wanna insert the current date in one of the table fields but it's not working. Can u tell me whats the problem? (I'm using mysql and php) Thx a lot $SQL = INSERT INTO orders (Customer_Id, Order_Date). VALUES($CustID,date()); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
How can I change my default character set without ./configure??
I have a table within turkish characters. I know which set I must use: latin5 But I couldn' t set it. Thanks SQL,Sql,sql Edakom Internet Sorumlusu Gokce Akkaya - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Select with Order By that don't use my INDEX :(
- Original Message - From: Joseph Bueno [EMAIL PROTECTED] To: David BORDAS [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 10:34 AM Subject: Re: Select with Order By that don't use my INDEX :( David BORDAS a écrit : I'm using mysql 3.23.49 on Linux redhat dedicated server. Here is my query : mysql show index from MyTable; Table: MyTable Non_unique : 0 Key_name: PRIMARY Seq_in_index: 1 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 1 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 2 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 1 Column_name : Field2 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 2 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 3 Column_name : Field4 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL MyTable info : Field0 : int(10) unsigned primary key Field1 : int(10) unsigned Field2 : int(10) unsigned Field3 : int(10) unsigned Field4 : datetime Null: Yes default -00-00 00:00:00 ( but this field don't have null values ) Field5 : varchar(50); mysql explain SELECT * FROM Forums WHERE Field2=5020 AND Field3=0 ORDER BY Field4 DESC LIMIT 0,20; ** table : MyTable type : ref possible_keys: Index1,questions key: questions key_len : 1 ref : const,const rows : 390 Extra : where used; Using filesort 1 row in set (0.00 sec) We can see that index questions which have Fields 2, 3 and 4 isn't use for the order by. Why ? Thanks in advance David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Hi, MySQL 3.23.x doesn't use the index with DESC option of order by. It seems that it is fixed in 4.x but I haven't tested it yet. Hope this helps -- Joseph Bueno NetClub/Trader.com Arf, i've tried with an ASC query and index is used :( now i must wait a new mysql release :( 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 3.23.49 and HP-UX 10.20 Install
Hi! Michael == Michael Stassen [EMAIL PROTECTED] writes: Michael This has come up before. In fact, I had the exact same problem. You can Michael see the the thread Coredump when running scripts/mysql_install_db at Michael http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:msp:93051 Michael The conclusion is in Problem with setrlimit on HPUX 10.20 at Michael http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:msn:94565 Michael In short, HP-UX 10.20 has a bug in dce/cma_ux.h which breaks setrlimit Michael when largefiles is enabled. If I understood him correctly, Monty (Michael Michael Widenius) acknowledged that his copy of HP-UX 10.20 has this flaw, but he Michael says his copy of the official binary works there, though I honestly cannot Michael see how. cut This problem should have been fixed in 3.23.49 I added in include/global.h the following defines: #if defined(HPUX) defined(_LARGEFILE64_SOURCE) defined(THREAD) /* Fix bug in setrlimit */ #undef setrlimit #define setrlimit cma_setrlimit64 #endif Which should have fixed this problem. Clarence, any change you could compile MySQL with --with-debug and try to find out why it fails for you? Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
avoiding skip locking
I am doing some automated mysql -u user -ppasswd database insertfile.sql in a loop and have some skip locking presumably because there are a number of jobs running to do this (with different files, which however use the same tables). Should I rather do line by line inserts for speed? Thanks, S.Alexiou - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
database restriction
Hi the best thing to do is check out Dev.Shed.com - MySql the addressas follows http://www.devshed.com./Server_Side/MySQL/Administration This tells you basically the tasks of correctly administering the MySql server,including the manipulation of the privilege system. I could waffle on a bit but realistically check this site or the MySql doc's . To be fair DBA stuff isn't that taxing once you've read the relevant doc's so happy administring. Cheers Chuck Amadi Systems Programmer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
hi , i have a little mysql problem . : got 2 servers .. 1 got a dump from server1 via myadmin , but when trying to insert the data into server 2 i got a strange errror and i got disconected from server2 . any ideeas ? -- Your MySQL connection id is 752533 to server version: 3.23.37-log Type 'help' for help. mysql INSERT INTO ref_data VALUES (186, 139, 'Tess of the D\'Urbervilles', 'THOMAS HARDY\r\nTESS OF THE D'URBERVILLES\r', 'tess.DOC', '2002-02-06', 648, 15872, 1, '', 10); ERROR 2005: Unknown MySQL Server Host ''tess.DOC',' (2) mysql -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: SELECT the first letter MATCH in mySQL
At 9:00 +0200 4/19/02, Jan Peuker wrote: There is a method using trim() but I think it's better to use RLIKE ^A.* Regular expression patterns (unlike SQL patterns) don't need to match the entire string, so RLIKE '^A' is sufficient and more efficient because it doesn't spend time trying to match anything other than the first character. As to the original question (below), that query looks like it should work. Perhaps the problem lies elsewhere in the code that executes the query. (It may be the query gets modified somehow. Without seeing the context, it's hard to say.) regards, Jan Peuker - Original Message - From: Son Nguyen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 8:42 AM Subject: SELECT the first letter MATCH in mySQL I want to select all the records, which have the first letter matched the letter 'A'. $SQL_get_lyrics = SELECT lyric_title FROM lyrics WHERE lyric_title LIKE \'A%\'; Some how this statement given me other record, which has the letter A inside, too like: Know A Word and other other unexpected records Son Nguyen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Really big text - where to get?
When you write another MySQL-based search engine, you obviosly test it issuing queries against big amounts of data which contains more or less sane text. Tell me, where (or how) you usually get the text when you need, say, 10GB of it and, I repeat, it should be a REAL non-repeating text, not just would-be words randomly generated with a filler script? 10G maybe a bit high, but when I wanted a few tens of MB to test MySQL FULLTEXT. I downloaded some books from Project Gutenberg and wrote a tiny app to split them into records with one sentence in each record. MySQL proved blindingly fast on the King James Bible (8Mb, 30,000 records). 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
group by help?
Hi I am having some trouble figuring out how mySQL interprets the group by clause in a particular set of data that I have (the ensembl database if anyone is familar with it!) Now, I have some data like this: +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | | 24173 | Q96GS5 | SPTREMBL | +-++--+ and this is produced by the following SQL: select t.gene_id, x.display_id,e.db_name from ensembl_core_test.objectXref as ox, ensembl_core_test.Xref as x, ensembl_core_test.transcript as t, ensembl_core_test.externalDB as e where e.db_name='SPTREMBL' and x.xrefID = ox.xrefID and t.translation_id = ox.ensembl_id and e.externalDBId=x.externalDBId and gene_id =24173; Now, the SQL is not important, what is is that we have two display_ids for one gene_id. Now, if we add a group by gene_id clause into the above SQL, then presumably mySQL must make an arbitrary decision on which display_id to choose. And the odd thing is that in the main ensembl database it chooses one, and in my local copy it chooses the other! So what I want to figure out is how mySQL makes that arbitrary decision - is it based on which it comes across first in memory, which it comes across last, alphabetical order, random choice (though mySQL is always consistent in which it chooses) ... or is there some other way it will make the decision? Furthermore, and more confusingly, mySQL chooses differently if I parameterise the SQL. For example: mysql create table test - select t.gene_id, x.display_id,e.db_name - from ensembl_core_test.objectXref as ox, -ensembl_core_test.Xref as x, -ensembl_core_test.transcript as t, - ensembl_core_test.externalDB as e - where e.db_name='SPTREMBL' and -x.xrefID = ox.xrefID and -t.translation_id = ox.ensembl_id and -e.externalDBId=x.externalDBId and gene_id = 24173 - group by gene_id - order by gene_id; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql select * from test; +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | +-++--+ 1 row in set (0.00 sec) compare this to: mysql create table test - select t.gene_id, x.display_id,e.db_name - from ensembl_core_test.objectXref as ox, -ensembl_core_test.Xref as x, -ensembl_core_test.transcript as t, - ensembl_core_test.externalDB as e - where e.db_name='SPTREMBL' and -x.xrefID = ox.xrefID and -t.translation_id = ox.ensembl_id and -e.externalDBId=x.externalDBId - group by gene_id - order by gene_id; Query OK, 11674 rows affected (6.84 sec) Records: 11674 Duplicates: 0 Warnings: 0 mysql select * from test where gene_id = 24173; +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q96GS5 | SPTREMBL | +-++--+ 1 row in set (0.01 sec) So here we see that mySQL has chosen differently simply because of the presence or absence of the gene_id = 24173 in the create table command I want to try and figure out why mySQL is making these rather inconsistant decisions and see if it's possible to remove this feature Thanks for your time, if there is another mailing list which is more appropriate, please tell me! Thanks Mick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump doesn't write data on subsequent calls (MySql 4.0.1)
On Wed, Apr 10, 2002 at 08:48:01AM -0700, Nick Pasich wrote: [snip] Any subsequent executions do not. This is because of query caching. I can run -- mysql -e reset query cache -- which will enable the next call to mysqldump to write data. I believe that mysqldump should write data whether query caching is enabled/disabled or not-flushed/flushed. I belive this is already fixed in 4.0.2. Others reported the bug, and I recall seeing a patch for it applied to the tree. 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 70 days, processed 1,895,087,815 queries (309/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
Max 127 records
Hey - can anyone help? I have a MySql-db in which I can only have 127 records. Using phpmyadmin to insert record number 128 (autoincrement) gets this message: INSERT INTO `kontakt` (`id`, `navn`, `adresse`, `postnummer`, `by`, `telefon`, `kommentar`) VALUES ('', 'fsd', 'sfdg', 'sg', 'fdsg', 'sfg', 'sg'); MySQL returnerede: Duplicate entry '127' for key 1 No matter what table or desing of table - the result is the same. Using other interfaces than phpmyadmin gives the same result too. Thanks for helping 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
SV: Max 127 records
Fra: Henning Olsen [mailto:[EMAIL PROTECTED]] Sendt: 19. april 2002 14:39 Hey - can anyone help? I have a MySql-db in which I can only have 127 records. Using phpmyadmin to insert record number 128 (autoincrement) gets this message: INSERT INTO `kontakt` (`id`, `navn`, `adresse`, `postnummer`, `by`, `telefon`, `kommentar`) VALUES ('', 'fsd', 'sfdg', 'sg', 'fdsg', 'sfg', 'sg'); MySQL returnerede: Duplicate entry '127' for key 1 This is a classic :-) You've created your primary key (the autoincremented field) as a TINYINT, which only allows values in the range -128 to 127. auto_increment does not support negative numbers, so when you've filled the first 127 records, it cannot add any more values. Alter the field to a INT UNSIGNED, then you're good to go - Carsten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Max 127 records
Hi, On Fri, Apr 19, 2002 at 01:38:49PM +0100, Henning Olsen wrote: Hey - can anyone help? I have a MySql-db in which I can only have 127 records. Using phpmyadmin to insert record number 128 (autoincrement) gets this message: INSERT INTO `kontakt` (`id`, `navn`, `adresse`, `postnummer`, `by`, `telefon`, `kommentar`) VALUES ('', 'fsd', 'sfdg', 'sg', +'fdsg', 'sfg', 'sg'); MySQL returnerede: Duplicate entry '127' for key 1 Your table has an auto_increment column of type 'tiny int'. This allows values between -128 and 127. You should choose a different column type. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Max 127 records
WowI think I can finally help somebody on this list. Ok. I bet that you have your column set up as tinyint. I presume it would be the id column that you have. tinyint is the following: Signed values: -128 to 127 Unsigned values: 0 to 255 I ran into the problem before too. So if you want to go past 127, you either need to set it as an Unsigned value, but that will only let you go to 255, or set it to another int. smallint will go to 65535 (Unsigned), mediumint will go to 16777215 (Unsigned), etc. Happy to finally be able to help. :) Steve Buehler At 07:38 AM 4/19/2002, Henning Olsen wrote: Hey - can anyone help? I have a MySql-db in which I can only have 127 records. Using phpmyadmin to insert record number 128 (autoincrement) gets this message: INSERT INTO `kontakt` (`id`, `navn`, `adresse`, `postnummer`, `by`, `telefon`, `kommentar`) VALUES ('', 'fsd', 'sfdg', 'sg', 'fdsg', 'sfg', 'sg'); MySQL returnerede: Duplicate entry '127' for key 1 No matter what table or desing of table - the result is the same. Using other interfaces than phpmyadmin gives the same result too. Thanks for helping 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
installation prob.
Hi Gurus, I just now reinstalled MySQL Ver 4.0.1 on Linux Mandrake 7.2 on Intel P III m/c using the RPM available at MySQL website. The installation got through w/o any prolem, but it gives Segmentation Fault - core dumped when I run MySQLADMIN. I have followed all the post-installation steps given in the manual. Kindly guide, thanks in adv. -- Paras. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Max 127 records
A closed mouth maintains a happy mind. It could be that you specified signed TINYINT as type for the PRIMARY KEY. Signed TINYINT goes from -127 to 127, hence your problem. HO INSERT INTO `kontakt` (`id`, `navn`, `adresse`, `postnummer`, `by`, HO `telefon`, `kommentar`) VALUES ('', 'fsd', 'sfdg', 'sg', 'fdsg', 'sfg', HO 'sg'); HO MySQL returnerede: HO Duplicate entry '127' for key 1 -- Ciprian There is no such thing as an underestimate of average intelligence. -- Henry Adams - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Max 127 records
* Henning Olsen I have a MySql-db in which I can only have 127 records. Using phpmyadmin to insert record number 128 (autoincrement) gets this message: INSERT INTO `kontakt` (`id`, `navn`, `adresse`, `postnummer`, `by`, `telefon`, `kommentar`) VALUES ('', 'fsd', 'sfdg', 'sg', 'fdsg', 'sfg', 'sg'); MySQL returnerede: Duplicate entry '127' for key 1 This happens when the key is defined as a tinyint, a single byte datatype. Change the column to a smallint (2 bytes), mediumint (3 bytes) or int (4 bytes): ALTER TABLE kontakt MODIFY id INT UNSIGNED NOT NULL; URL: http://www.mysql.com/doc/A/L/ALTER_TABLE.html -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Downgrade
David, Friday, April 19, 2002, 8:17:27 AM, you wrote: DT Are there any steps I need to take to ensure no corruption of the database if DT I downgrade from say DT 3.23.49 DT to DT 3.23.48 You can find a short notes about upgrading/downgrading MySQL in the manual: http://www.mysql.com/doc/U/p/Upgrade.html Before downgrading you can make backup of your databases, look at: http://www.mysql.com/doc/B/a/Backup.html DT Thanks, Dave DT query,sql -- 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
Re: Renaming database
MH, Thursday, April 18, 2002, 11:50:12 PM, you wrote: M Is there a sanctioned way to rename a MySQL 3.23.x database, consisting M of MyISAM tables? 1. Take down the MySQL server. 2. Rename dir, that contains tables(files) of your database. 3. Start the MySQL server. 4. Don't forget to correct permissions. M Thanks, M MH -- 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
Re: Re: TZ setting
Gerald, Thursday, April 18, 2002, 1:52:25 AM, you wrote: GRJ Can the TZ be set in my.cnf (my.ini) when the MySQL server starts up? GRJ I have read the manual on this, and tried various combinations, but haven't GRJ hit the right syntax. No, you can't specify timezone in the my.cnf(my.ini) file ... (you can find some more info about it in the Paul's book, page 419). On Windows you can change timezone setting TZ environmental variable. GRJ Gerald Jensen -- 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
Re: How to upgrade/migrate Mysql Server
Sridhar, Friday, April 19, 2002, 12:54:14 AM, you wrote: SP We are currently using Mysql (3.22.32) on a Solaris platform. We are SP looking to upgrade/migrate this current version to 3.23.49a, for SP utilizing the replication functionality. I would appreciate if anyone SP can clarify the following: SP 1) Any known/unknown issues with 3.23.49a SP 2) What are the steps in migrating to this new version. I say this SP because i come from a Oracle dba world. SP 2) Anything in particular that i need to watch for, while upgrading SP to this new version. Take a look at: http://www.mysql.com/doc/U/p/Upgrade.html http://www.mysql.com/doc/U/p/Upgrading-from-3.22.html You can find some notes about upgrading there... SP Thanks SP Sri -- 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
Re: Grant User Persmissions
Chris, Thursday, April 18, 2002, 8:50:06 PM, you wrote: CS Is there any way to grant a user only SELECT permissions on a set of tables CS in MySQL, but to also allow them to have CREATE, DROP, and INSERT on the CS Database itself?? CS What I want is a database where the users cannot modify the core tables, but CS I want them to be able to CREATE and DROP temporary tables that they have CS created?!? Yes, you can set SELECT privilege on some tables and CREATE, DROP, INSERT privileges on the certain database. Use GRANT statement, look at: http://www.mysql.com/doc/G/R/GRANT.html Note: if you set SELECT privileges on some tables (f.e. table1 and table2), your users don't have SELECT privileges on tables, that will be created by themself ... So, you need to correct privileges on new tables or to set SELECT privileges on the whole database ... CS Any help would be great.. CS Thanks, CS Chris -- 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
Re: Re: Changing international charset for ORDER by sorting
Webmaster33, Thursday, April 18, 2002, 8:01:30 PM, you wrote: W I understand. Bad to hear. :-( W I think there would be important to implement features to be able W to set different character sets for each table, or to change W character sets dynamically without the need to restart MySQL server. W This would be especially important for the ORDER BY string W collations (result ordering, sorting) ... W Where should I suggest this feature? W Is there any place where suggestions can be sent? In 4.1 there will be a full support of charsets on the table and query levels .. Take a look at MySQL TODO: http://www.mysql.com/doc/T/O/TODO_MySQL_4.1.html W Thanks, W Webmaster33 -- 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
Re: How can I change my default character set without ./configure??
Gokce, Friday, April 19, 2002, 12:51:34 PM, you wrote: GA I have a table within turkish characters. GA I know which set I must use: latin5 GA But I couldn' t set it. Run mysqld with --default-character-set=latin5 option or edit my.cnf. Look at: http://www.mysql.com/doc/C/h/Character_sets.html GA Thanks GA Edakom Internet Sorumlusu GA Gokce Akkaya -- 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
Re: Max 127 records
Henning, Friday, April 19, 2002, 3:38:49 PM, you wrote: HO Hey - can anyone help? HO I have a MySql-db in which I can only have 127 records. HO Using phpmyadmin to insert record number 128 (autoincrement) gets this message: HO INSERT INTO `kontakt` (`id`, `navn`, `adresse`, `postnummer`, `by`, `telefon`, `kommentar`) VALUES ('', 'fsd', 'sfdg', 'sg', 'fdsg', 'sfg', 'sg'); HO MySQL returnerede: HO Duplicate entry '127' for key 1 HO No matter what table or desing of table - the result is the same. Using other interfaces than phpmyadmin gives the same result too. What is the type of your 'id' column? Tinyint? If so, you should change column type to increase the range. Look at: http://www.mysql.com/doc/C/o/Column_types.html HO Thanks for helping HO Henning -- 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
Re: group by help?
mysql is acting correctly. GROUP BY is used to consolidate data for SUMming, COUNTing, etc. Your SELECT statement makes not such request. You have simply requested the value of a specific field. Data are stored in mysql databases randomly. Therefore, when you request a field's data, you are getting whatever is first in the list of records matching your WHERE clause. Bottom line: you are NOT using GROUP BY as it is supposed to be used. Your SQL is in error, not mysql. hope this helps... - Original Message - From: Mick Watson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 6:01 AM Subject: group by help? Hi I am having some trouble figuring out how mySQL interprets the group by clause in a particular set of data that I have (the ensembl database if anyone is familar with it!) Now, I have some data like this: +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | | 24173 | Q96GS5 | SPTREMBL | +-++--+ and this is produced by the following SQL: select t.gene_id, x.display_id,e.db_name from ensembl_core_test.objectXref as ox, ensembl_core_test.Xref as x, ensembl_core_test.transcript as t, ensembl_core_test.externalDB as e where e.db_name='SPTREMBL' and x.xrefID = ox.xrefID and t.translation_id = ox.ensembl_id and e.externalDBId=x.externalDBId and gene_id =24173; Now, the SQL is not important, what is is that we have two display_ids for one gene_id. Now, if we add a group by gene_id clause into the above SQL, then presumably mySQL must make an arbitrary decision on which display_id to choose. And the odd thing is that in the main ensembl database it chooses one, and in my local copy it chooses the other! So what I want to figure out is how mySQL makes that arbitrary decision - is it based on which it comes across first in memory, which it comes across last, alphabetical order, random choice (though mySQL is always consistent in which it chooses) ... or is there some other way it will make the decision? Furthermore, and more confusingly, mySQL chooses differently if I parameterise the SQL. For example: mysql create table test - select t.gene_id, x.display_id,e.db_name - from ensembl_core_test.objectXref as ox, -ensembl_core_test.Xref as x, -ensembl_core_test.transcript as t, - ensembl_core_test.externalDB as e - where e.db_name='SPTREMBL' and -x.xrefID = ox.xrefID and -t.translation_id = ox.ensembl_id and -e.externalDBId=x.externalDBId and gene_id = 24173 - group by gene_id - order by gene_id; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql select * from test; +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | +-++--+ 1 row in set (0.00 sec) compare this to: mysql create table test - select t.gene_id, x.display_id,e.db_name - from ensembl_core_test.objectXref as ox, -ensembl_core_test.Xref as x, -ensembl_core_test.transcript as t, - ensembl_core_test.externalDB as e - where e.db_name='SPTREMBL' and -x.xrefID = ox.xrefID and -t.translation_id = ox.ensembl_id and -e.externalDBId=x.externalDBId - group by gene_id - order by gene_id; Query OK, 11674 rows affected (6.84 sec) Records: 11674 Duplicates: 0 Warnings: 0 mysql select * from test where gene_id = 24173; +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q96GS5 | SPTREMBL | +-++--+ 1 row in set (0.01 sec) So here we see that mySQL has chosen differently simply because of the presence or absence of the gene_id = 24173 in the create table command I want to try and figure out why mySQL is making these rather inconsistant decisions and see if it's possible to remove this feature Thanks for your time, if there is another mailing list which is more appropriate, please tell me! Thanks Mick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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)
Re[2]: SELECT the first letter MATCH in mySQL
Hello Paul, But RLIKE won't use indexes (even if they exist), so why RLIKE '^A' is better than LIKE 'A%', if LIKE which starts at the beginning of the string **will** use indexes? Regards, Tom PD Regular expression patterns (unlike SQL patterns) don't need to match the PD entire string, so RLIKE '^A' is sufficient and more efficient because PD it doesn't spend time trying to match anything other than the first PD character. PD As to the original question (below), that query looks like it should PD work. Perhaps the problem lies elsewhere in the code that executes PD the query. (It may be the query gets modified somehow. Without seeing PD the context, it's hard to say.) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Max 127 records
Are you saying that you want a table with a maximum of 127 records ? Or, that due to your TINYINT, you are RESTRICTED to 127 records? If you are restricted to records, change TINYINT to INT. If you want to have ONLY 128 records, change TINYINT to TINYINT UNSIGNED, which will permit records 1 through 255. FYI: show us your table structure next time; please don't force us to guess your structure - Original Message - From: Henning Olsen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 7:38 AM Subject: Max 127 records Hey - can anyone help? I have a MySql-db in which I can only have 127 records. Using phpmyadmin to insert record number 128 (autoincrement) gets this message: INSERT INTO `kontakt` (`id`, `navn`, `adresse`, `postnummer`, `by`, `telefon`, `kommentar`) VALUES ('', 'fsd', 'sfdg', 'sg', 'fdsg', 'sfg', 'sg'); MySQL returnerede: Duplicate entry '127' for key 1 No matter what table or desing of table - the result is the same. Using other interfaces than phpmyadmin gives the same result too. Thanks for helping 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
problem with load data local infile
I am having problems with load data local infile. The mysql host server is running 3.23.23 I was able to successfully use load data local infile before to import data off of a client machine onto the mysql host but now all i get is a can't stat file errcode2 message. I know that the pathname I am using is correct. What would keep the mysql server from being able to see the file? 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
Re: group by help?
Thank you :-) I do realise the SQL is not being used as it should be, but as it is not my database and nor is it my SQL, there's not much I can do to really change either of these I merely have to work with a database that has been produced by a third party group, and I must say at this point that I have nothing but praise for the ensembl group for the difficult job that they have done very well (see http://www.ensembl.org) So, I realise this is not the problem of mySQL, but what I want to try and find out is really the internal workings of mySQL when it performs this operation so that I can understand why my copy of ensembl, which is produced from direct dumps of the main ensembl, behaves differently to the main ensembl. I guess from what you are saying, that the data is stored randomly, that there is very little I can do to actually make my database behave the same as the main database? Could it be affected in any way by operating system and/or file system? Superficially the data is organised in exactly the same way in both databases, but I have no doubt that things like memory locations are completely different, but possibly if I could understand what the variables are that affect this behaviour I could minimise the inconsistancy...? Thanks for your time Mick Richard Emery wrote: mysql is acting correctly. GROUP BY is used to consolidate data for SUMming, COUNTing, etc. Your SELECT statement makes not such request. You have simply requested the value of a specific field. Data are stored in mysql databases randomly. Therefore, when you request a field's data, you are getting whatever is first in the list of records matching your WHERE clause. Bottom line: you are NOT using GROUP BY as it is supposed to be used. Your SQL is in error, not mysql. hope this helps... - Original Message - From: Mick Watson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 6:01 AM Subject: group by help? Hi I am having some trouble figuring out how mySQL interprets the group by clause in a particular set of data that I have (the ensembl database if anyone is familar with it!) Now, I have some data like this: +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | | 24173 | Q96GS5 | SPTREMBL | +-++--+ and this is produced by the following SQL: select t.gene_id, x.display_id,e.db_name from ensembl_core_test.objectXref as ox, ensembl_core_test.Xref as x, ensembl_core_test.transcript as t, ensembl_core_test.externalDB as e where e.db_name='SPTREMBL' and x.xrefID = ox.xrefID and t.translation_id = ox.ensembl_id and e.externalDBId=x.externalDBId and gene_id =24173; Now, the SQL is not important, what is is that we have two display_ids for one gene_id. Now, if we add a group by gene_id clause into the above SQL, then presumably mySQL must make an arbitrary decision on which display_id to choose. And the odd thing is that in the main ensembl database it chooses one, and in my local copy it chooses the other! So what I want to figure out is how mySQL makes that arbitrary decision - is it based on which it comes across first in memory, which it comes across last, alphabetical order, random choice (though mySQL is always consistent in which it chooses) ... or is there some other way it will make the decision? Furthermore, and more confusingly, mySQL chooses differently if I parameterise the SQL. For example: mysql create table test - select t.gene_id, x.display_id,e.db_name - from ensembl_core_test.objectXref as ox, -ensembl_core_test.Xref as x, -ensembl_core_test.transcript as t, - ensembl_core_test.externalDB as e - where e.db_name='SPTREMBL' and -x.xrefID = ox.xrefID and -t.translation_id = ox.ensembl_id and -e.externalDBId=x.externalDBId and gene_id = 24173 - group by gene_id - order by gene_id; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql select * from test; +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | +-++--+ 1 row in set (0.00 sec) compare this to: mysql create table test - select t.gene_id, x.display_id,e.db_name - from ensembl_core_test.objectXref as ox, -ensembl_core_test.Xref as x, -ensembl_core_test.transcript as t, - ensembl_core_test.externalDB as e - where e.db_name='SPTREMBL' and -x.xrefID = ox.xrefID and -
Re: Lock databases
Is it ok for version 3.22.32 ? João Alexander Marques [EMAIL PROTECTED] Uranet Projetos Sistemas Ltda. Tel. : 55 11 3242-1353 - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Kory Wheatley [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, April 18, 2002 5:22 PM Subject: Re: Lock databases On Thu, Apr 18, 2002 at 12:54:47PM -0600, Kory Wheatley wrote: Is there a way to Lock all databases for a couple of minutes and then resume. I don't want to run mysqldump for a backup. I have another utility that I will use to take a snapshot of the databases at that instant and it might take a minute to run so thats way I need them locked.. FLUSH TABLES WITH READ LOCK will do what you want. 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 70 days, processed 1,880,517,572 queries (309/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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
licence
I hope I'm not out of line by asking a licensing question. I am working on a MySQL database for a web-based survey. We will be charging a fee to complete the survey and receive reports. Am I required to purchase a MySQL licence for this or does my hosting company who sold me the use of MySQL cover the licence? 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
MySQL Problem
am having some trouble with MySQL. Seems I cannot add users. Well, I can add them (through phpMyAdmin, but they are still not able to get into the datasbases. I flushed the tables, too. Current users are not affected. This started, rather suddenly, the day after I was able to do this. Anyone have any ideas? Thanks, -Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 SQL ++ Bug and Port
I am working with the MySql++ 1.7.9 on windows98 using borland C++ 6.0. I have found and fixed a memory leak ( applicable on all platforms that need the __USLC__ conditional defined) , and sucessfully ported the system to borlanbd C++ 6 with a very minimum of modifications. (mostly resolving include file differences between compilers). I would be willing to submit the port back to mysql, but i am not sure who to contact about it. The memory leak is in the function string SQLQuery::str(const SQLQueryParms p) const, starting on line 98 of sql_query.cpp (.cc ? I may have changed the extentions.) This is the fixed function: string SQLQuery::str(const SQLQueryParms p) const { SQLQuery *const_this = const_castSQLQuery *(this); if (!parsed.empty()) const_this-proc(const_castSQLQueryParms(p)); *const_this ends; #ifdef __USLC__ strstreambuf *tmpbuf = const_this-rdbuf(); uint length = tmpbuf-pcount() + 1; char *s = new char[length];//isnt this a memory leak? #else uint length = const_this-pcount() + 1; char s[length]; #endif const_this-get(s, length, '\0'); const_this-seekg (0,ios::beg); const_this-seekp (-1,ios::cur); //fixed leak for conditional __USLC__ er 4/3/2002 /* *original code * return string(s); */ /* *new code */ #ifdef __USLC__ string tmp(s); delete s; return tmp; #else return string(s); #endif /* * end mod ***/ } - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: group by help?
As a relational database, it doe not matter the order in which data are stored. Rather, what matters are the methods by which data are extracted via the SELECT statement. In order to derive benefits of GROUP BY, you must use it with SELECTs that use GROUP BY functionality, such as SUM(), COUNT(). If you need to know the internal machinations of mysql, I can only suggest that you study the source code. - Original Message - From: Mick Watson [EMAIL PROTECTED] To: Richard Emery [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 7:34 AM Subject: Re: group by help? Thank you :-) I do realise the SQL is not being used as it should be, but as it is not my database and nor is it my SQL, there's not much I can do to really change either of these I merely have to work with a database that has been produced by a third party group, and I must say at this point that I have nothing but praise for the ensembl group for the difficult job that they have done very well (see http://www.ensembl.org) So, I realise this is not the problem of mySQL, but what I want to try and find out is really the internal workings of mySQL when it performs this operation so that I can understand why my copy of ensembl, which is produced from direct dumps of the main ensembl, behaves differently to the main ensembl. I guess from what you are saying, that the data is stored randomly, that there is very little I can do to actually make my database behave the same as the main database? Could it be affected in any way by operating system and/or file system? Superficially the data is organised in exactly the same way in both databases, but I have no doubt that things like memory locations are completely different, but possibly if I could understand what the variables are that affect this behaviour I could minimise the inconsistancy...? Thanks for your time Mick Richard Emery wrote: mysql is acting correctly. GROUP BY is used to consolidate data for SUMming, COUNTing, etc. Your SELECT statement makes not such request. You have simply requested the value of a specific field. Data are stored in mysql databases randomly. Therefore, when you request a field's data, you are getting whatever is first in the list of records matching your WHERE clause. Bottom line: you are NOT using GROUP BY as it is supposed to be used. Your SQL is in error, not mysql. hope this helps... - Original Message - From: Mick Watson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 6:01 AM Subject: group by help? Hi I am having some trouble figuring out how mySQL interprets the group by clause in a particular set of data that I have (the ensembl database if anyone is familar with it!) Now, I have some data like this: +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | | 24173 | Q96GS5 | SPTREMBL | +-++--+ and this is produced by the following SQL: select t.gene_id, x.display_id,e.db_name from ensembl_core_test.objectXref as ox, ensembl_core_test.Xref as x, ensembl_core_test.transcript as t, ensembl_core_test.externalDB as e where e.db_name='SPTREMBL' and x.xrefID = ox.xrefID and t.translation_id = ox.ensembl_id and e.externalDBId=x.externalDBId and gene_id =24173; Now, the SQL is not important, what is is that we have two display_ids for one gene_id. Now, if we add a group by gene_id clause into the above SQL, then presumably mySQL must make an arbitrary decision on which display_id to choose. And the odd thing is that in the main ensembl database it chooses one, and in my local copy it chooses the other! So what I want to figure out is how mySQL makes that arbitrary decision - is it based on which it comes across first in memory, which it comes across last, alphabetical order, random choice (though mySQL is always consistent in which it chooses) ... or is there some other way it will make the decision? Furthermore, and more confusingly, mySQL chooses differently if I parameterise the SQL. For example: mysql create table test - select t.gene_id, x.display_id,e.db_name - from ensembl_core_test.objectXref as ox, -ensembl_core_test.Xref as x, -ensembl_core_test.transcript as t, - ensembl_core_test.externalDB as e - where e.db_name='SPTREMBL' and -x.xrefID = ox.xrefID and -t.translation_id = ox.ensembl_id and -e.externalDBId=x.externalDBId and gene_id = 24173 - group by gene_id - order by gene_id; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql select * from test; +-++--+ | gene_id |
Re: group by help?
It's ok, I do actually fully understand relational database theory and what the group by statement is used for, but like I say, I personally have no control over the database or the SQL :-) I was hoping to get hold of someone who maybe knows the source code quite well already and could help me out before I take the drastic step of trawling through the source code myself. It does strike me that, on the rare occasions when a truly arbitrary decision must be made, when there is a choice but it really doesn't matter which is chosen, there is no method in relational databases to make this choice in a consistent manner. A tough task probably, and obviously group by is not the perfect answer, but you can see why the developer made that choice as it does make mySQL make an arbitrary decision... just not a consistent one :-) Thanks Mick Richard Emery wrote: As a relational database, it doe not matter the order in which data are stored. Rather, what matters are the methods by which data are extracted via the SELECT statement. In order to derive benefits of GROUP BY, you must use it with SELECTs that use GROUP BY functionality, such as SUM(), COUNT(). If you need to know the internal machinations of mysql, I can only suggest that you study the source code. - Original Message - From: Mick Watson [EMAIL PROTECTED] To: Richard Emery [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 7:34 AM Subject: Re: group by help? Thank you :-) I do realise the SQL is not being used as it should be, but as it is not my database and nor is it my SQL, there's not much I can do to really change either of these I merely have to work with a database that has been produced by a third party group, and I must say at this point that I have nothing but praise for the ensembl group for the difficult job that they have done very well (see http://www.ensembl.org) So, I realise this is not the problem of mySQL, but what I want to try and find out is really the internal workings of mySQL when it performs this operation so that I can understand why my copy of ensembl, which is produced from direct dumps of the main ensembl, behaves differently to the main ensembl. I guess from what you are saying, that the data is stored randomly, that there is very little I can do to actually make my database behave the same as the main database? Could it be affected in any way by operating system and/or file system? Superficially the data is organised in exactly the same way in both databases, but I have no doubt that things like memory locations are completely different, but possibly if I could understand what the variables are that affect this behaviour I could minimise the inconsistancy...? Thanks for your time Mick Richard Emery wrote: mysql is acting correctly. GROUP BY is used to consolidate data for SUMming, COUNTing, etc. Your SELECT statement makes not such request. You have simply requested the value of a specific field. Data are stored in mysql databases randomly. Therefore, when you request a field's data, you are getting whatever is first in the list of records matching your WHERE clause. Bottom line: you are NOT using GROUP BY as it is supposed to be used. Your SQL is in error, not mysql. hope this helps... - Original Message - From: Mick Watson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 6:01 AM Subject: group by help? Hi I am having some trouble figuring out how mySQL interprets the group by clause in a particular set of data that I have (the ensembl database if anyone is familar with it!) Now, I have some data like this: +-++--+ | gene_id | display_id | db_name | +-++--+ | 24173 | Q9H701 | SPTREMBL | | 24173 | Q96GS5 | SPTREMBL | +-++--+ and this is produced by the following SQL: select t.gene_id, x.display_id,e.db_name from ensembl_core_test.objectXref as ox, ensembl_core_test.Xref as x, ensembl_core_test.transcript as t, ensembl_core_test.externalDB as e where e.db_name='SPTREMBL' and x.xrefID = ox.xrefID and t.translation_id = ox.ensembl_id and e.externalDBId=x.externalDBId and gene_id =24173; Now, the SQL is not important, what is is that we have two display_ids for one gene_id. Now, if we add a group by gene_id clause into the above SQL, then presumably mySQL must make an arbitrary decision on which display_id to choose. And the odd thing is that in the main ensembl database it chooses one, and in my local copy it chooses the other! So what I want to figure out is how mySQL makes that arbitrary decision - is it based on which it comes across first in memory, which it comes across last, alphabetical
Re: SELECT from blob column
Not to beat an apparently dead horse... My problem was that interactively I could not get the following statement to execute (on Win NT, myISAM table, mySQL 3.23.49-nt): insert mytable values (1, 2 ,3, LOAD_FILE('c:\myfile.ext')); However, I found this would work: insert mytable (col1, col2, col3) values (1,2,3); update mytable set col4 = LOAD_FILE('c:\myfile.ext')) where col1 = 1; Is this unusual? Is there something I am missing here? -Grady - Original Message - From: João Paulo Vasconcellos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, April 18, 2002 5:29 PM Subject: Re: SELECT from blob column In Thursday 18 April 2002 17:56, Grady Drago wrote: Well, I found that I can use LOAD_FILE in INSERT statement, and I found that my text file should be finished with a newline char :( . Well... never mind. Would you give an example please? If you do : $ cat test.file Some text. ^D $ And then use LOAD_FILE(), the data doesn't show up. But if you do: $ cat test.file Some text. ^D $ Then the text shows up. Better saying, if you create a file without a trailing newline, the text does not show up, but if you put a trailing newline, it appears. sql -- João Paulo Vasconcellos Gerente de Tecnologia - NetCard Tel. 21 3852-9008 Ramal 31 [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
FW: BSD: Random unknown database error
Update: After finding a reference to problems with BSDI and the realpath() system call in the mailing list archives, I tried rebuilding MySQL with the HAVE_BROKEN_REALPATH flag defined. That seemed to clear up the problem, at least with the testing that I performed. YMMV. DSC _ Dan S. Camper Borrowed Time, Inc. Software Thaumaturge http://www.bti.net -- Forwarded Message From: Dan S. Camper [EMAIL PROTECTED] Date: Thu, 18 Apr 2002 12:41:31 -0500 To: MySQL [EMAIL PROTECTED] Subject: BSD: Random unknown database error All: Recently I've run into a problem where I intermittently receive an Unknown database error while attempting to communicate with a local MySQL server. I read through the mail list archives and discovered that others reported similar problems under BSD, but I didn't see any concrete solutions. I may not be able to provide a solution, but I believe I can pinpoint the cause. Let me apologize now for the length of this message. I'm trying to explain enough so that someone else, more knowledgable about MySQL's internals, can either use it to solve the problem or refute my guess. First, my environment: MacOS X (10.1.4), MySQL 3.23.49, running on a G3 PowerBook (Firewire), latest dev tools from Apple. The MySQL server is running on that system as well as my own C++ code. Before seeing the problem within MySQL I found a problem with my own multithreaded code. Each thread is responsible for loading a file in one directory and then manipulating a couple of other files in a different directory, all referenced relatively to the application's directory. When the load got high -- there were many concurrent threads -- I would intermittently receive an error from the OS telling me that one of the files I was trying to open could not be found. Given that the file in question was one that was supposed to always be present, I grew suspicious. After a bit of debugging, imagine my surprise when I discovered that the full pathname the OS was trying to use was wrong. Example Source file:foo/text.txt Dest file: data/index.txt App directory: /Users/lordgrey/Projects/myproject/ When the problem occurred while opening the dest file, referenced relatively, the OS was actually trying to open: /Users/lordgrey/Projects/myproject/foo/data/index.txt instead of /Users/lordgrey/Projects/myproject/data/index.txt Once I nailed down a variable with the current working directory at application launch time and used that to fully qualify my datafiles, all of my problems disappeared. As further testing, I started cranking up the number of threads. Now, in addition to dinking around with files, each thread has its own MySQL connection. Increasing the number of threads started causing the unknown database error to appear, pretty much in direct proportion to the number threads. The other problem reports in the archive noted that the unknown database problem seemed to occur when the system's load was high. That would agree with what I found. Anyway, it occurs to me that MySQL is failing to find a database -- which is a directory -- for the same reason that my code couldn't find its own data files. Namely: MySQL is using a relative path, BSD is mucking around with the concept of current working directory incorrectly and it's colliding with other concurrent threads also trying to use relative paths. I haven't picked the MySQL source code apart to see if this is really the case or not, but it seems reasonable. If it's true then the actual problem lies in BSD/MacOS/etc. but it can be worked around in the MySQL server. Cheers, DSC -- End of Forwarded Message - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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[2]: SELECT the first letter MATCH in mySQL
At 15:29 +0200 4/19/02, Toomas Vendelin wrote: Hello Paul, But RLIKE won't use indexes (even if they exist), so why RLIKE '^A' is better than LIKE 'A%', if LIKE which starts at the beginning of the string **will** use indexes? I was comparing RLIKE '^A.*' to RLIKE '^A', not RLIKE to LIKE. Regards, Tom PD Regular expression patterns (unlike SQL patterns) don't need to match the PD entire string, so RLIKE '^A' is sufficient and more efficient because PD it doesn't spend time trying to match anything other than the first PD character. PD As to the original question (below), that query looks like it should PD work. Perhaps the problem lies elsewhere in the code that executes PD the query. (It may be the query gets modified somehow. Without seeing PD the context, it's hard to say.) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: licence
Klym writes: I hope I'm not out of line by asking a licensing question. I am working on a MySQL database for a web-based survey. We will be charging a fee to complete the survey and receive reports. Am I required to purchase a MySQL licence for this or does my hosting company who sold me the use of MySQL cover the licence? thanks Hi! This is a question for [EMAIL PROTECTED] -- 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
Re: My SQL ++ Bug and Port
Thank you for your report. Yes, I know about this leak in Windows version. It is due to it's stack limitations. Current developrment branch has this leask fixed already ... -- 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
Re: licence
Let me know about the answer to this one. [EMAIL PROTECTED] --- Life must be lived as play. - Plato (427 - 347 BC) - Original Message - From: Sinisa Milivojevic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 19, 2002 2:55 PM Subject: Re: licence Klym writes: I hope I'm not out of line by asking a licensing question. I am working on a MySQL database for a web-based survey. We will be charging a fee to complete the survey and receive reports. Am I required to purchase a MySQL licence for this or does my hosting company who sold me the use of MySQL cover the licence? thanks Hi! This is a question for [EMAIL PROTECTED] -- 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
Birthday select
Hi everyone. I have a sql database of users with their birth date (Y/M/D). Can anyone please tell me how I can check whose birthday each day is, and pull them from the database? Thanks, Soheil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to build mysql 3.23.49a on HP-UX 11.11
Has anyone built mysql 3.23.49a on HP-UX 11.11 using GCC 3.0.x? If so, what were your CC, CXX, CFLAGS, CXXFLAGS and ./configure set up? Thanks, 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
Re: licence
Neil Highley writes: Let me know about the answer to this one. [EMAIL PROTECTED] --- Life must be lived as play. - Plato (427 - 347 BC) You will be duly informed ... -- 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
Re: Birthday select
Hi everyone. I have a sql database of users with their birth date (Y/M/D). Can anyone please tell me how I can check whose birthday each day is, and pull them from the database? WHERE MONTH(birth) = MONTH(CURDATE()) AND DAYOFMONTH(birth) = DAYOFMONTH(CURDATE()) I'm assuming you have birth date stored in a single field birth. Thanks, Soheil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Birthday select
SELECT ... FROm table WHERE datecolumn=NOW(); Gurhan -Original Message- From: Soheil Shaghaghi [mailto:[EMAIL PROTECTED]] Sent: Friday, April 19, 2002 11:09 AM To: [EMAIL PROTECTED] Subject: Birthday select Hi everyone. I have a sql database of users with their birth date (Y/M/D). Can anyone please tell me how I can check whose birthday each day is, and pull them from the database? Thanks, Soheil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: licence
Shaun, I am not sure about everyone else on the list, but as far as I am concerned, since you are purchasing a service from your provider, all licencing should be dealt with by them. I would be awefully suspicious of any survice provider that wouldn't already have this information. Regards, Shaun - Original Message - From: Sinisa Milivojevic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 11:13 AM Subject: Re: licence Neil Highley writes: Let me know about the answer to this one. [EMAIL PROTECTED] --- Life must be lived as play. - Plato (427 - 347 BC) You will be duly informed ... -- 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: installation prob.
Paras, Friday, April 19, 2002, 2:55:22 PM, you wrote: PM I just now reinstalled MySQL Ver 4.0.1 on Linux Mandrake 7.2 on Intel P III PM m/c using the RPM available at MySQL website. The installation got through PM w/o any prolem, but it gives Segmentation Fault - core dumped when I run PM MySQLADMIN. I have followed all the post-installation steps given in the PM manual. Please, do the following: strace mysqladmin yourparameters and send the output to me. PM Kindly guide, thanks in adv. PM -- Paras. -- 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
Re: problem
At 1:10 PM +0300 4/19/02, cristian ditoiu wrote: hi , i have a little mysql problem . : got 2 servers .. 1 got a dump from server1 via myadmin , but when trying to insert the data into server 2 i got a strange errror and i got disconected from server2 . any ideeas ? -- Your MySQL connection id is 752533 to server version: 3.23.37-log Type 'help' for help. mysql INSERT INTO ref_data VALUES (186, 139, 'Tess of the D\'Urbervilles', 'THOMAS HARDY\r\nTESS OF THE D'URBERVILLES\r', 'tess.DOC', '2002-02-06', ...^ There's an unescaped ' there; if you created this dump from phpmydmin, perhaps there's a quote handling bug somewhere... That's a strange error, though, since AFAIK there's no place to put a hostname in an INSERT. -steve 648, 15872, 1, '', 10); ERROR 2005: Unknown MySQL Server Host ''tess.DOC',' (2) mysql -- -- ++ | Steve Edberg [EMAIL PROTECTED] | | University of California, Davis (530)754-9127 | | Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ | ++ | If only life would imitate toys. | | - Ted Raimi, March 2002 | | - http://www.whoosh.org/issue67/friends67a.html#raimi | ++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump doesn't write data on subsequent calls (MySql 4.0.1)
On Fri, Apr 19, 2002 at 04:12:35AM -0700, Jeremy Zawodny wrote: On Wed, Apr 10, 2002 at 08:48:01AM -0700, Nick Pasich wrote: [snip] Any subsequent executions do not. This is because of query caching. I can run -- mysql -e reset query cache -- which will enable the next call to mysqldump to write data. I believe that mysqldump should write data whether query caching is enabled/disabled or not-flushed/flushed. I belive this is already fixed in 4.0.2. Others reported the bug, and I recall seeing a patch for it applied to the tree. Jeremy -- Jeremy, Thanks for your reply. I have also sent in a patch to Egor. ---( Nick )--- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Optimizing the table
Hi all, A few days ego, I asked the group about the meaning of the Overhead in sql. Some people were nice enough to answer me, and told me I needed to optimize the table. Here is what phpMyAdmin reports on the table: Space usage : Type Usage Data 1,602 KB Index 3,729 KB Overhead 24,718 Bytes Effective 5,307 KB Total 5,331 KB Now, when I try to optimize the table, OPTIMIZE TABLE USERS; I get this error: error: The handler for the table doesn't support check/repair Does anyone know what the problem is here? Thanks again. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Birthday select
Ignore my previous post.. SELECT ... FROM table WHERE MONTH(datecolumm)=MONTH(NOW()) AND DAYOFMONTH(datecolumn)=DAYOFMONTH(NOW()); Lessons learned in the last few days = Never post messages right before or on the project implementation day. Thanks Keith for pointing that out... Gurhan -Original Message- From: Keith C. Ivey [mailto:[EMAIL PROTECTED]] Sent: Friday, April 19, 2002 11:47 AM To: [EMAIL PROTECTED] Subject: RE: Birthday select On 19 Apr 2002, at 11:21, Gurhan Ozen wrote: SELECT ... FROm table WHERE datecolumn=NOW(); Do you have a lot of newborn users at your place? The original poster might also be interested in a query like SELECT RIGHT(birthdate, 5), last_name, first_name FROM users ORDER BY RIGHT(birthdate, 5), last_name, first_name assuming birthdate is a DATE column. That should give a list of birthdays for all users. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.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
join?
My question is this; I have a database that looks like this. galleries gallery_id gallery_name 1 3d 2 fractals 3 abstract 4 cats 5 cars Images image_id gallery_id image_name etc 1 4 cat1 2 4 cat2 3 2 sky 4 2 blue 5 5 car1 I am trying to run a SQL query that will return an array of rows from the images table. But I want the gallery_id number to be replaced with the actual gallery name from the galleries table. Mike Fifield Charles Schwab Co, Inc. WARNING: All e-mail sent to or from this address will be received by the Charles Schwab corporate e-mail system and is subject to archival and review by someone other than the recipient. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Optimizing the table
At 9:35 -0700 4/19/02, Soheil Shaghaghi wrote: Hi all, A few days ego, I asked the group about the meaning of the Overhead in sql. Some people were nice enough to answer me, and told me I needed to optimize the table. Here is what phpMyAdmin reports on the table: Space usage : Type Usage Data 1,602 KB Index 3,729 KB Overhead 24,718 Bytes Effective 5,307 KB Total 5,331 KB Now, when I try to optimize the table, OPTIMIZE TABLE USERS; I get this error: error: The handler for the table doesn't support check/repair OPTIMIZE TABLE works only for MyISAM tables. Is your table a different type? Does anyone know what the problem is here? Thanks again. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 questions on OS X.
Why is this producing a syntax error at the %? This is supposed to create a global user, yes? GRANT ALL ON *.* TO myuser@% IDENTIFIED BY mypassword; Is there a quick command to show all GRANTS? or Users? -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 build mysql 3.23.49a on HP-UX 11.11
Hi Rick, I've posted a similar question about it, trying to compile MySQLMAX on an HPUX 11 box. Nobody answered !! Browsing the web for related sites and information I've found that the only way to do that is using the aCC compiler, which is sold separatelly from HP at a cost of more than $1500. Best reagrds, Ing. Gustavo A. Edelstein Tech. Mgr. Equiplus Argentina S.A. __ Visit us On Line at www.equiplus.com Email addresses: Operations: [EMAIL PROTECTED] Consulting: [EMAIL PROTECTED] Technical: [EMAIL PROTECTED] Web related: [EMAIL PROTECTED] General: [EMAIL PROTECTED] - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 12:10 PM Subject: How to build mysql 3.23.49a on HP-UX 11.11 Has anyone built mysql 3.23.49a on HP-UX 11.11 using GCC 3.0.x? If so, what were your CC, CXX, CFLAGS, CXXFLAGS and ./configure set up? Thanks, 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
Selecting Information Just Inserted
Maybe someone can help me here.. I'm inserting data with an AUTO_INCREMENT column and immediately after I need to use that new number for a corresponding record in another table. How am I able to extract that new number quickly? I could run a SELECT query after the INSERT using the MAX command, but if at a busy time another record is entered while the first record was being entered, it's possibly I may get the wrong number. If there a way to use the INSERT command and extract the new AUTO_INCREMENT all in one command? Any help is appreciated. Andrew - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: GRANT questions on OS X.
At 12:59 PM -0500 4/19/02, Paul DuBois wrote: Why is this producing a syntax error at the %? This is supposed to create a global user, yes? GRANT ALL ON *.* TO myuser@% IDENTIFIED BY mypassword; myuser@% Andrew Hazen emailed me to use single quotes and it worked. So does mysql care if it is single or double quotes? -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: table-building advice requested
Hi, I am following up on a thread I started yesterday about building my tables. (I have included the original email at the end of this one for reference.) I got some good feedback about it, and so have decided to change my entire battle plan, and was hoping to get some advice about it. The scenario is this: a table exists called properfiles, and its columns keep track of various attributes of files. Each record is a file, of course. These files have been properly entered into the database using a PHP application I am writing, and thumbnails have been uploaded to the filesystem for each of these records. A user can use the application to select any of these files to be printed, adding the files to a shopping-cart-like object. But in addition to printing these already-existing files, the user needs to be able to define their own files to be printed -- these don't already exist in the database. However, I would like to keep track of them, since they are being printed. So here's the dilemma -- I've got some proper files that have been entered properly into the database, and have a great deal of information stored about them, and I have some user-entered files that have been entered only for the sake of being printed. The attributes of a user-entered file are different from the attributes of a proper file. Should I have one big table named files which keeps track of both kinds of files? This seems dodgy to me -- that would mean that some columns would be for proper files (meaning that a user-entered file record would have a null value for that column) and some columns would be for user-entered files (so proper files would have a null value for that column). Is this an acceptable database design? Originally I was thinking of storing each in its own table -- properfiles and userfiles, and having a prints table act as a foreign key, storing one-to-many relationships between properfiles and userfiles (for each print there could be any combination of properfiles and userfiles). But this would require having some way of tracking whether the file_id matches a proper file_id or a user file_id. It doesn't seem very smart to have a setup like this. So I think I should go with the big files table and just have some columns that apply to some kinds of files and not to others. Is this done? I'm just hesitant to have a lot of null values in my table. What do you think? Thanks for reading! Thanks even more if you can help me. Erik On Thursday, April 18, 2002, at 03:52 PM, Erik Price wrote: Hello, I was hoping to solicit some advice on the structure of the database I am building. Here is a simplified version of my scenario: The application that I am designing, in PHP4.1.2 with MySQL (3.23.46 on RedHat 7.2), has a feature which allows it to keep track of graphics files (JPGs, GIFs, PSDs, AIs, QPTs, etc), which are stored on CDs in a CD cabinet. I have already designed a table to store information about files, where each row is an individual file and each column is an attribute of that file. mysql describe files; +-+---+--+-+-+ | Field | Type | Null | Key | Default | +-+---+--+-+-+ | file_id | int(10) unsigned | | PRI | NULL| | file_name | varchar(64) | | | | | filetype_id | smallint(5) unsigned | YES | | NULL| | stor_id | mediumint(8) unsigned | YES | | NULL| | file_size | float(4,2)| | | 0.00| | width_in| float(4,2)| | | 0.00| | height_in | float(4,2)| | | 0.00| | file_res| smallint(5) unsigned | YES | | NULL| | cre_date| date | YES | | NULL| | insert_date | datetime | YES | | NULL| | inserter_id | smallint(5) unsigned | YES | | NULL| +-+---+--+-+-+ (there are other tables that relate to this one, but they are not relevant to my problem) Using my application via a web browser, users can select existing file records which need to be printed, and store them as objects into an array (actually a session variable array), similar to a shopping cart. Later, they will use another section of the application to check out, meaning each object in the array will become a row in another table (printedfiles), representing files that have been selected to be printed. (What happens with this data is irrelevant to my problem) The problem comes into play here: in addition to files they have selected from the database (let's use the alias files since they are all rows from the files table), users may also select files which do not exist in the database yet -- let's use the alias user files. I do not wish to store user files
query optimization
trying to make a query faster. the list helped out a lot last time, so i figured i would bring back and get more assistance. any thoughts? katen EXPLAIN SYNTAX: +-+---+-+---+-+---+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+-+---+-+---+--+---+ | PROXY | const | PROXY_U,PROXY_I | PROXY_U | 2 | const |1 | | | FILTERS | const | FILTERS_U | FILTERS_U | 20 | const |1 | | +-+---+-+---+-+---+--+---+ QUERY: select PROXY.TARGET_1_AUTH, PROXY.SHARED_SECRET, PROXY.AUTH_PORT, PROXY.ACCT_PORT, PROXY.RETRIES, PROXY.TIMEOUT, PROXY.USEOLDASCENDPASSWORDS, PROXY.SERVERHASBROKENPORTNUMBERS, PROXY.SERVERHASBROKENADDRESSES, PROXY.IGNOREREPLYSIGNATURE, FILTERS.ALLOW, FILTERS.ADD, PROXY.IP_POOL, PROXY.FAILUREPOLICY from PROXY, FILTERS where PROXY.DNIS=substring('9162331155',7,4) and PROXY.TARGET_1_AUTH is not null and FILTERS.FILTER=PROXY.FILTER and PROXY.REALM='' and PROXY.STATUS=1; CREATE TABLE SYNTAX: CREATE TABLE `PROXY` ( `ID` smallint(6) NOT NULL auto_increment, `DNIS` smallint(5) unsigned NOT NULL default '0', `STATUS` tinyint(4) unsigned NOT NULL default '0', `REALM` char(40) default NULL, `IP_POOL` char(20) NOT NULL default '', `FILTER` char(20) NOT NULL default '', `TEST_USER` char(30) default NULL, `TEST_PASS` char(30) default NULL, `TARGET_1_AUTH` char(25) NOT NULL default '', `TARGET_1_ACCT` char(25) default NULL, `TARGET_2_AUTH` char(25) default NULL, `TARGET_2_ACCT` char(25) default NULL, `TARGET_3_AUTH` char(25) default NULL, `TARGET_3_ACCT` char(25) default NULL, `TARGET_4_AUTH` char(25) default NULL, `TARGET_4_ACCT` char(25) default NULL, `AUTH_PORT` smallint(6) default NULL, `ACCT_PORT` smallint(6) default NULL, `SHARED_SECRET` char(25) NOT NULL default '', `RETRIES` tinyint(4) unsigned NOT NULL default '3', `TIMEOUT` tinyint(4) unsigned NOT NULL default '20', `USEOLDASCENDPASSWORDS` int(11) default NULL, `SERVERHASBROKENPORTNUMBERS` int(11) default NULL, `SERVERHASBROKENADDRESSES` int(11) default NULL, `IGNOREREPLYSIGNATURE` int(11) default NULL, `REPLYHOOK` char(255) default NULL, `FAILUREPOLICY` tinyint(4) default '0', PRIMARY KEY (`ID`), UNIQUE KEY `PROXY_U` (`DNIS`), KEY `PROXY_I` (`REALM`,`TARGET_1_AUTH`,`STATUS`,`FILTER`) ) CREATE TABLE `FILTERS` ( `ID` tinyint(4) NOT NULL auto_increment, `FILTER` varchar(20) NOT NULL default '', `ALLOW` text, `ADD` text, PRIMARY KEY (`ID`), UNIQUE KEY `FILTERS_U` (`FILTER`) ) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: FW: Mysql unresponsive ....
Hi Joseph, The situation is like this. The daemon was running but any command / requests were hanging for example any mysqladmin command would sit there forever, any mysql command would behave the same way. OS - HP-UX 10.20 Mysql version - 3.23.33 There were lot of activities at the time server hung-up. But even after users got out it was in the hung state. Nothing in the error log files to give any clues. thanks Vivek -Original Message- From: joseph.bueno [mailto:[EMAIL PROTECTED]] Sent: Friday, April 19, 2002 1:18 AM To: CHAUDHARY, Vivek Cc: mysql Subject: Re: FW: Mysql unresponsive Hi, May be this list was unresponsive because your description of the problem was a bit vague... I haven't experienced this kind of behaviour myself however some more details on your problem may help: - What OS are you using ? - What version of MySQL are you using ? - Was mysql server dead ? (Have you tried mysqladmin with ping, status or processlist options ?) - Have you checked mysql error log ? - When the problem happened, what kind of system activity have you seen ? Have you run top, vmstat (I assume you are on a Unix like OS) ? Hope this helps -- Joseph Bueno NetClub/Trader.com [EMAIL PROTECTED] wrote : Since I didn't get any response (like mysql server) I am sending it again. thanks Vivek -Original Message- From: CHAUDHARY, Vivek Sent: Wednesday, April 17, 2002 3:34 PM To: '[EMAIL PROTECTED]' Subject: Mysql unresponsive Hi all, Last friday we found that mysql was unresponsive (all the SQL activities were on hold) but the daemon was still running. We shutdown the daemon and restarted and everything was OK. My questions: 1) What could cause this behavior ? 2) Is there any way to monitor such a behavior ? any commands, scripts that could test for inactivity etc. Needless to say that I am a newbie on mysql front. Vivek Chaudhary Database Administrator IT STMicroelectronics, Phoenix. Phone:(602)485-2271 Fax: (602)485-6402 Cell:(602)363-1492 Email: [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: GRANT questions on OS X.
Alex, Enclose the user string in quotes. This will allow you to use the global wildcard option. Example: GRANT ALL ON *.* TO myuser@% IDENTIFIED BY mypassword; For those not using Mac OS X, the default shell is tcsh. I am not sure if this quirk is a function of string handling in tcsh or what, but this is the workaround I am using. Happy granting, -- Barry C. Hawkins Systems Consultant All Things Computed [EMAIL PROTECTED] On Fri, 19 Apr 2002 13:45:56 -0400 Alex Pilson [EMAIL PROTECTED] wrote: Why is this producing a syntax error at the %? This is supposed to create a global user, yes? GRANT ALL ON *.* TO myuser@% IDENTIFIED BY mypassword; Is there a quick command to show all GRANTS? or Users? -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Selecting Information Just Inserted
Hi, Andrew, To get the auto_increment value, simply execute select last_insert_id() after insert statement Andrew Kuebler wrote: Maybe someone can help me here.. I'm inserting data with an AUTO_INCREMENT column and immediately after I need to use that new number for a corresponding record in another table. How am I able to extract that new number quickly? I could run a SELECT query after the INSERT using the MAX command, but if at a busy time another record is entered while the first record was being entered, it's possibly I may get the wrong number. If there a way to use the INSERT command and extract the new AUTO_INCREMENT all in one command? Any help is appreciated. Andrew - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Selecting Information Just Inserted
On Friday, April 19, 2002, at 02:10 PM, Andrew Kuebler wrote: I'm inserting data with an AUTO_INCREMENT column and immediately after I need to use that new number for a corresponding record in another table. How am I able to extract that new number quickly? I could run a SELECT query after the INSERT using the MAX command, but if at a busy time another record is entered while the first record was being entered, it's possibly I may get the wrong number. If there a way to use the INSERT command and extract the new AUTO_INCREMENT all in one command? LAST_INSERT_ID() returns the last auto-incremented number for your database connection. Yes, this means that even if you execute this and someone else has auto-incremented the table in the meantime, you will still have your auto-incremented number (not the new, higher one). That is why this function is better than doing SELECT MAX() on that column. Erik Erik Price Web Developer Temp Media Lab, H.H. Brown [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: Mysql Installation problem with 3.23.49 Linux Source release on RH7.3 Beta 1.
I am trying to install mysql 3.23.49 source release on RH7.3 Beta 1 workstation... Encountered the following gcc C++ compiler executable creation problem when ./configure checks for C++ compiler it complains : checking for gcc... gcc checking whether the C compiler (gcc ) works... yes checking whether the C compiler (gcc ) is a cross-compiler... no checking whether we are using GNU C... yes checking whether gcc accepts -g... yes checking for c++... no checking for g++... no checking for gcc... gcc checking whether the C++ compiler (gcc ) works... no configure: error: installation or configuration problem: C++ compiler = =3D cannot create executables. The version of gcc I am using is 2.96 2731... any hints? Why it's not recognized as a C++ compiler? $gcc -v Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.2 2.96-109) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Installation problem with 3.23.49 Linux Source release on RH7.3 Beta 1.
Do you have g++ installed? It doesn't look like you do. On Friday 19 April 2002 1:45 pm, Ricky Sun wrote: I am trying to install mysql 3.23.49 source release on RH7.3 Beta 1 workstation... Encountered the following gcc C++ compiler executable creation problem when ./configure checks for C++ compiler it complains : checking for gcc... gcc checking whether the C compiler (gcc ) works... yes checking whether the C compiler (gcc ) is a cross-compiler... no checking whether we are using GNU C... yes checking whether gcc accepts -g... yes checking for c++... no checking for g++... no checking for gcc... gcc checking whether the C++ compiler (gcc ) works... no configure: error: installation or configuration problem: C++ compiler = =3D cannot create executables. The version of gcc I am using is 2.96 2731... any hints? Why it's not recognized as a C++ compiler? $gcc -v Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.2 2.96-109) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: problem with load data local infile (repeat)
I never saw this message make this list so I'm reposting it. - Original Message - From: Darren Vollmer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 7:33 AM Subject: problem with load data local infile I am having problems with load data local infile. The mysql host server is running 3.23.23 I was able to successfully use load data local infile before to import data off of a client machine onto the mysql host but now all i get is a can't stat file errcode2 message. I know that the pathname I am using is correct. What would keep the mysql server from being able to see the file? 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
Re: GRANT questions on OS X.
Why is this producing a syntax error at the %? This is supposed to create a global user, yes? GRANT ALL ON *.* TO myuser@% IDENTIFIED BY mypassword; myuser@% Andrew Hazen emailed me to use single quotes and it worked. So does mysql care if it is single or double quotes? No. -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: GRANT questions on OS X.
At 14:58 -0400 4/19/02, Barry C. Hawkins wrote: Alex, Enclose the user string in quotes. This will allow you to use the global wildcard option. Example: GRANT ALL ON *.* TO myuser@% IDENTIFIED BY mypassword; You should quote the user name and host name parts separately. myuser@% For those not using Mac OS X, the default shell is tcsh. I am not sure if this quirk is a function of string handling in tcsh or what, but this is the workaround I am using. The shell doesn't have anything to do with how MySQL interprets the GRANT statement. ?? Happy granting, -- Barry C. Hawkins Systems Consultant All Things Computed [EMAIL PROTECTED] On Fri, 19 Apr 2002 13:45:56 -0400 Alex Pilson [EMAIL PROTECTED] wrote: Why is this producing a syntax error at the %? This is supposed to create a global user, yes? GRANT ALL ON *.* TO myuser@% IDENTIFIED BY mypassword; Is there a quick command to show all GRANTS? or Users? -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Selecting Information Just Inserted
At 14:10 -0400 4/19/02, Andrew Kuebler wrote: Maybe someone can help me here.. I'm inserting data with an AUTO_INCREMENT column and immediately after I need to use that new number for a corresponding record in another table. How am I able to extract that new number quickly? I could run a SELECT query after the INSERT using the MAX command, but if at a busy time another record is entered while the first record was being entered, it's possibly I may get the wrong number. If there a way to use the INSERT command and extract the new AUTO_INCREMENT all in one command? Any help is appreciated. Andrew INSERT INTO tbl2 (id, ...) VALUES(LAST_INSERT_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
MySQL and Perl for the Web now available in Dutch
I'm pleased to announce that MySQL and Perl for the Web (New Riders, by me) is now available in a Dutch translation (MySQL en Perl voor het web). Not that I can read a word of it ... :-) Information is available on the translations page at the book's Web site: http://www.kitebird.com/mysql-perl/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Seeking Freelancer
Greets Folks, I need help with some too-involved-for-me CGI stuff, and I'd like to work with a freelance programmer/consultant. The consulting will be for a mature themed site, and I am running on a limited budget, so open minds only please. The project is for a paid membership site using Perl/MySQL. The site will also run a number of other CGI programs, namely eCards, bulletin boards, auction and image galleries. Some of the work I can handle myself, but other times I may need direct assistance. Thanks, Will _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Specifying Error Log
I think it would be a good idea to be able to specify the error log on the command line when starting mysqld. Right now mysql users can only specify the following logs on the command line: log (access/activity log) log bin log bin index log update log isam log slow-queries innodb_log_* I would love to be able to specify the error log on the command line. log error ( by default-only it is $MYSQL_ROOT/var/`hostname`.err ) mysqld --log-error=/usr/mysql/logs/error.log Maybe some people don't like that it is flagged as an error log. I really don't care what it is referred to as long as I can actually specify the name and location of the log. Does anyone else have an opinion on this? -RG - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how do i applying an equation to every row of a result set?
I am trying to normalize a data set based on the minimum values of certain columns. I figured out that I can get the minimum value using a query like SELECT least(min(colOne),min(colTwo)) FROM myTable Is there a way I can do the normalization in a single query? Ideally, something like... SELECT colOne-least(min(colOne),min(colTwo)) FROM myTable I realize that that does not work and maybe I need some kind of join. Is it possible to apply that subtraction to every row of the result set when it is returned? (instead of normalizing after I retrieve the result) thx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Large text amounts into a mysql table?
Hi! I admit I´m a newbie but I´m really trying to get a grip on this... I have suceeded in creating an administration solution for websites and now one problem remains: When the values for a new website is submitted into a mysql table, it doesn´t work if the amount of text in the values are too large. I have set the actual cell to mediumtext, so that shouldn´t be the problem. I have tried to find the answer to my question in different forums, but the only result I have got is that it may have to do with the max-key limit? I mean, in the address field in my web browser all the text is visible when submitted successfully, is there a limit on how long this link can be? Anyway, how can I submit large text amounts into a mysql table? A lot of thanks in advance, Jonas Andersson _ 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
Re: Syntax error with merge tables
On Sat, 20 Apr 2002 [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 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: I always get a syntax error when using INSERT_METHOD in creating merge tables. If I leave off the INSERT_METHOD part it works fine. I have tried this on many versions in the past and now on 3.23.49a. I am running on Mandrake Linux 8.1 but have seen this same error on other linux distros. It is my understanding from reading the manual that the following should work but it doesn't. CREATE TABLE mytable0 ( aINTEGER NOT NULL PRIMARY KEY, bCHAR(18) NOT NULL ); CREATE TABLE mytable1 ( aINTEGER NOT NULL PRIMARY KEY, bCHAR(18) NOT NULL ); CREATE TABLE mytable2 ( aINTEGER NOT NULL PRIMARY KEY, bCHAR(18) NOT NULL ); CREATE TABLE mytable ( aINTEGER NOT NULL PRIMARY KEY, bCHAR(18) NOT NULL ) TYPE=MERGE UNION=(mytable0, mytable1, mytable2) INSERT_METHOD=LAST; Since the .MRG files are plain text could someone send me what the INSERT_METHOD part is supposed to look like so I can do it manually if needed. Thanks. Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to use TWZ1 jdbc driver
I'm trying to use the twz1 jdbc driver with dbvis. I keep getting a java.lang.VerifyError error. Has anyone been able to get dbvis to work with mysql via twz1 driver? thanks in advance jennifer moter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Intermittent 1049 error
Hello, I'm running a relatively busy MySQL server on Mac OS X Server. MySQL runs on a dedicated dual G4 500, with 1.5 GB of RAM and an 18GB 15k rpm Ultra160 Cheetah drive. No other services besides MySQL run on this machine. Roughly 3-5 times per day, my MySQL server suddenly forgets most or all of its databases, and begins claiming that they don't exist with an error 1049. I've searched the mailing list (found 3 messages with 1049 in the archives, none of which described this problem), the online docs (error 1049 isn't even mentioned), and Google (which found 150+ pages who are apparently having this same problem, but found no answers) ... I can't believe I'm the only one having this problem, but since there's no mention of it anywhere that I can find, I am beginning to wonder. Can anyone shed some light on this issue, and hopefully, offer a remedy? Thanks in advance. -Clay ( Further info -- the server hosts only about 15-20 databases, the total size of which is less than 1.5 GB. Well under 2MM records total. mysqladmin status as of this writing: Uptime: 205602 Threads: 145 Questions: 4293056 Slow queries: 0 Opens: 207 Flush tables: 1 Open tables: 202 Queries per second avg: 20.880 ) ___ Clay Loveless Webmaster, Crawlspace http://www.crawlspace.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: problem
* Steve Edberg At 1:10 PM +0300 4/19/02, cristian ditoiu wrote: mysql INSERT INTO ref_data VALUES (186, 139, 'Tess of the D\'Urbervilles', 'THOMAS HARDY\r\nTESS OF THE D'URBERVILLES\r', 'tess.DOC', '2002-02-06', ...^ There's an unescaped ' there; if you created this dump from phpmydmin, perhaps there's a quote handling bug somewhere... That's a strange error, though, since AFAIK there's no place to put a hostname in an INSERT. -steve 648, 15872, 1, '', 10); ERROR 2005: Unknown MySQL Server Host ''tess.DOC',' (2) mysql -- The \r is probably interpreted as a connect command: mysql \? MySQL commands: Note that all text commands must be first on line and end with ';' help(\h)Display this help. ? (\?)Synonym for `help'. clear (\c)Clear command. connect (\r)Reconnect to the server. Optional arguments are db and host. -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Syntax error with merge tables
Hi. AFAIK, insert for MERGE tables was introduced in version 4.0.0. Let's see... yep, http://www.mysql.com/doc/N/e/News-4.0.0.html says so. Regards, Benjamin. PS: The syntax you used should be fine with a version supporting it On Fri, Apr 19, 2002 at 04:00:02PM -0700, [EMAIL PROTECTED] wrote: [...] I always get a syntax error when using INSERT_METHOD in creating merge tables. If I leave off the INSERT_METHOD part it works fine. I have tried this on many versions in the past and now on 3.23.49a. I am [...] -- [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: Why using filesort here?
Hi. On Fri, Apr 12, 2002 at 02:49:03PM +0200, [EMAIL PROTECTED] wrote: [...] mysql explain select * from news where category = 'x' order by category DESC, provider_date DESC; +---+--+++-+---+--++ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+--+++-+---+--++ | news | ref | category_provider_date | category_provider_date | 50 | const | 1 | where used; Using filesort | +---+--+++-+---+--++ 1 row in set (0.00 sec) Why is filesort used here? According to the MySQL doc (5.2.7, example 5) this should work using the index without any additional sorting. MySQL-3.23.49-max-log running on FreeBSD 4.4-RELEASE I assume you are referring to the online manual? It's documenting the most recent version. According to the change history (Appendix D), this optimization was introduced in version 4.0.2. Bye, Benjamin. -- [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
Demande de confirmation d'inscription à mysql-france
Bonjour, Nous avons reçu votre demande d'inscription au groupe mysql-france sur Yahoo! Groupes, le nouveau service de communautés de Yahoo!. Pour vous inscrire, vous devez confirmer votre demande en répondant à ce message. Si vous n'avez pas demandé ou ne souhaitez pas vous inscrire au groupe mysql-france, veuillez ignorer ce message. Cordialement, L'équipe support Yahoo! Groupes L'utilisation du service Yahoo! Groupes est soumise à l'acceptation des Conditions d'utilisation et de la Charte sur la vie privée, disponibles respectivement sur http://fr.docs.yahoo.com/info/utos.html et http://fr.docs.yahoo.com/info/privacy.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Bienvenue ¦à mysql-france
Bonjour, Bienvenue sur la liste de diffusion Mysql-france. Prenez 2 minutes pour lire ce message. Pour vous desinscrire, allez sur le site ONElist, www.onelist.com, et selectionnez les liens My ONElist situé à gauche. Vous pouvez aussi demander à recevoir une fois par semaine l'ensemble des messages postés dans la liste ou, au contraire, recevoir les messages postés au fur et à mesure. En esperant que vous trouverez ce que vous cherchez... Je vous conseille vivement de vous inscrire, si ce n'est déjà fait, dans la liste php3-france ainsi que dans la liste apache-france. Franck tabary L'utilisation du service Yahoo! Groupes est soumise ¦à l'acceptation des Conditions d'utilisation et de la Charte sur la vie priv ¦ée, disponibles respectivement sur http://fr.docs.yahoo.com/info/utos.html et http://fr.docs.yahoo.com/info/privacy.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php