Re: how to split database and index
On Thu, 10 May 2001, roger westin wrote: We have a database of size 10GB and its index is also almost of the same size. Now we want this database in MYSQL. Please let me know how we can slpit it across 3-4 hard disks.We are using Linux 6.2 Hi there, My self I have a machine runnig RH Linux 6.0 with a 70Gb database. And it works just fine. What you need to do is to, recompile tha source using --with-raid or download the -max version of mysql Because what you should do is to use the raid option in create table. It will then create several subdirectorys in wich it puts the datafiles. (read more in the manual about this feature (on CREATE TABLE)). And then just mount diffrent HD/partitions to the diffrent subdirectoris (works fine for me) The problem you will have is the indexfile Because as far as I know no solution has been made to split the index file into smaler files. The ext2 has a max filesize about 2Gb so you will have a problem. So for your sake I hope this is not in one table. Otherwise, my recomendation is to try to split the table into several smaller tables and thus the indexfiles as well. - For me that was not possible, so I had to drop all indexed columns (exept my uniqe numric identifyer) in the main table, and create a kind of quick serach system (187 tables) of the rest of the data. (so insted of having 1 tabled of 70Gb and 1 index on 50Gb, I have 188 tables total 250Gb and 188 indexes on about 90Gb), but I have some desent speed in on everything. - Which is better? Creating a MERGE table or using the RAID options on create? TIA, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 TOP?
On Fri, 11 May 2001, Anthony Colli wrote: Hi all- I am really new to the list and mysql. Here is my Question. I am used to writing SELECT TOP [num] FROM [table] I have searched the documentation online and found no mention of how to do this. But I did find info on LIMIT that limits the number of records returned. Is this how mysql limits rows returned? I know it sounds like an easy question but I am curious. Thanks -Anthony yes. t. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
OLAP
Is anyone using mysql for OLAP? Any particular tools to check? thanks in advance, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: newbie needs help setting paswords
On Sat, 5 May 2001, Daniel Goldin (E-mail) wrote:
I'bve been following SAM'S TEACH YOURSELF MYSQL and hit a glitch. Whenn I do
what I'm told to do create passwords here's what happens
prompt insert into user (host, user, password) values('localhost', 'myname'
password('testpass');
Try closing another parentheses right before the ;
regards,
thalis
I get the following error:
Syntax error near unexoected token '(h'
Daniel Goldin [EMAIL PROTECTED]
Creative Director 323.225.1926
BlueLamp Productions
www.blue-lamp.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: auto_increment
Check out the myisamchk manual page and the --set-auto-increment[=value] flag. regards, thalis On Fri, 4 May 2001, Stefan Wehowsky wrote: Let´s say I got a column id that is of type tinyint and has the extra auto_increment. Let´s further say that I have 50 entries in that column. Now if I delete e.g. entry No. 30 and right after that add another entry without naming an id (for ist auto_increment) MySQL gives it the id 51 AND NOT 30 which leads to more and more gaps between the id´s. Is there anything i can do about that ? Best regards, Stefan Wehowsky - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: 'Show' output
On your mysql monitor do: mysql tee filename.out This will redirect everhting to filename.out besides ptinting it to the screen. Alternatively from you shell: $ echo show status;|mysql -u thalis -p lala filename.out regards, thalis On Fri, 4 May 2001, Tim wrote: Is there any way to redirect the output of a 'Show' command to a text file?? I know you can do this with a SELECT sql statement... -TIM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Aggregate string values
On Fri, 4 May 2001, Joel Desamero wrote: Hello, I was wondering if there is a way to aggregate string values when grouping together results. I guess what I'm looking for is the string version of the SUM() function. Is there such a thing in MySQL? Thanks. No. You could write your UDF and take it public as well :o) cheers, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question
Hello all, mysql uses only one index for the entire execution of a query? i.e. if it uses one index to do the row retrieval because it matches the where criteria but then has to sort these rows on something else that would benefit from index, would mysql use this index? tia, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Your mysql database (a system db with the access privileges) has a table called host which has amongst other files, one called host.frm It seems that mysqld doesn't have permission to read this file. Check that the mysqld owner and this file's owner are the same and change accordingly. regards, thalis p.s. in general do a: $ perror error_code to see what the error you are getting means. Eg your error gave: Error code 13: Permission denied On Tue, 1 May 2001, aditya shanker wrote: hi i am attaching a log file which was generated by the server in starting up mysql could any please tell me what exactlly the problen is log file = 010427 18:25:29 mysqld started 010427 18:25:29 /usr/local/mysql-3.23.36-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010427 18:25:29 mysqld ended 010427 18:26:05 mysqld started 010427 18:26:05 /usr/local/mysql-3.23.36-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010427 18:26:05 mysqld ended 010427 18:34:12 mysqld started /usr/local/mysql/bin/mysqld: ready for connections 010427 20:23:30 Aborted connection 10 to db: 'unconnected' user: 'root' host: `localhost' (Got an error reading communication packets) 010427 20:27:15 Aborted connection 17 to db: 'unconnected' user: 'root' host: `localhost' (Got an error writing communication packets) 010427 20:27:15 /usr/local/mysql/bin/mysqld: Normal shutdown 010427 20:27:15 /usr/local/mysql/bin/mysqld: Shutdown Complete 010427 20:27:15 mysqld ended 010430 12:10:00 mysqld started 010430 12:10:01 /usr/local/mysql-3.23.36-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010430 12:10:01 mysqld ended 010430 15:45:30 mysqld started 010430 15:45:30 /usr/local/mysql-3.23.36-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010430 15:45:30 mysqld ended 010430 15:46:20 mysqld started 010430 15:46:20 /usr/local/mysql-3.23.36-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010430 15:46:20 mysqld ended 010501 10:29:42 mysqld started 010501 10:29:42 /usr/local/mysql-3.23.36-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010501 10:29:42 mysqld ended 010501 10:30:34 mysqld started 010501 10:30:34 /usr/local/mysql-3.23.36-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010501 10:30:34 mysqld ended 010501 10:41:05 mysqld started 010501 10:41:05 /usr/local/mysql-3.23.36-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010501 10:41:05 mysqld ended 010501 11:06:18 mysqld started 010501 11:06:18 /usr/local/mysql-3.23.36-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010501 11:06:18 mysqld ended 010501 11:42:37 mysqld started 010501 11:42:37 /usr/local/mysql-3.23.36-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010501 11:42:37 mysqld ended 010501 11:52:29 mysqld started 010501 11:52:29 /usr/local/mysql-3.23.36-pc-linux-gnu-i686/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010501 11:52:29 mysqld ended 010501 12:37:43 mysqld started Fatal error: Can't change to run as user 'ser=mysql' ; Please check that the user exists! 010501 12:37:43 Aborting 010501 12:37:43 mysqld ended 010501 12:47:05 mysqld started /usr/local/mysql/bin/mysqld: ready for connections - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ORDER BY DESC optimization
On Mon, 30 Apr 2001, ryc wrote: I have a fairly large table (greater than 4mil rows) that I would to preform a query like: SELECT col1,col2 FROM table WHERE a=1, c=2 ORDER BY b DESC LIMIT 50; I have an index on the table INDEX1( a,b,c ); When running the query as is, it takes around 4seconds. If I omit the DESC part the query runs in a fraction of a second. I would like the query to run faster when I use DESC. I looked at myisamchk -R to sort by the 'b' index but I want to be sure it will speed up my query since it may take a while to sort all 4million rows. To improve things I'd suggest you drop the (a,b,c) index and create two new ones: one on (a,c) and another on (b) and then do the myisamchk -R on the second index You might also consider the --sort-records=# option regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ORDER BY DESC optimization
On Tue, 1 May 2001, Thalis A. Kalfigopoulos wrote: On Mon, 30 Apr 2001, ryc wrote: I have a fairly large table (greater than 4mil rows) that I would to preform a query like: SELECT col1,col2 FROM table WHERE a=1, c=2 ORDER BY b DESC LIMIT 50; I have an index on the table INDEX1( a,b,c ); When running the query as is, it takes around 4seconds. If I omit the DESC part the query runs in a fraction of a second. I would like the query to run faster when I use DESC. I looked at myisamchk -R to sort by the 'b' index but I want to be sure it will speed up my query since it may take a while to sort all 4million rows. To improve things I'd suggest you drop the (a,b,c) index and create two new ones: one on (a,c) and another on (b) and then do the myisamchk -R on the second index You might also consider the --sort-records=# option Sorry --sort-records is -R. I meant --sort-index. regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Newbie group/count query question
On Tue, 1 May 2001, Graham Nichols wrote: I have a table which contains a date column and an order_number column. I need to formulate a query syntax to return the total number of orders for each day in a given month (if any). Can someone help me with the syntax please as I've been stumbling around with it all day without success. Many thanks, Graham select data_column,count(*) as number_of_orders from my_table where MONTH(date_column)=# group by TO_DAYS(date_column); and you replace the # with the month number you are looking for regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ORDER BY DESC optimization
On Tue, 1 May 2001, ryc wrote: I have a fairly large table (greater than 4mil rows) that I would to preform a query like: SELECT col1,col2 FROM table WHERE a=1, c=2 ORDER BY b DESC LIMIT 50; I have an index on the table INDEX1( a,b,c ); When running the query as is, it takes around 4seconds. If I omit the DESC part the query runs in a fraction of a second. I would like the query to run faster when I use DESC. I looked at myisamchk -R to sort by the 'b' index but I want to be sure it will speed up my query since it may take a while to sort all 4million rows. To improve things I'd suggest you drop the (a,b,c) index and create two new ones: one on (a,c) and another on (b) and then do the myisamchk -R on the second index You might also consider the --sort-records=# option The reason I decided to use (a,b,c) as the index is because I read in the How mysql uses indexes (http://www.mysql.com/doc/M/y/MySQL_indexes.html) that with the index (a,b,c) if you have a where clause where a=constant and have order by b (the key_part2) it will use the index. This is correct, but that way the 'where a=1 AND c=3' clause would use the index only for the a=1 condition and would do an exhaustive search to find which of those rows also have c=3. This is why I suggested the (a,c) index as a better alternative for your where clause. The reason why I suggested an index on (b) by itself is for the soring of the results. BUT I don't know whether mysql will utilize this index to sort the temporary results or whether only one index can be used from the beginning till the end of a query. Anyone a bit more experienced willing to lend a hand here? If the second index is not used for the sorting, then since both your 'where' conditions are using constants (a=1 and c=3), the optimal index for you is on (a,c,b). cheers again, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: index troubles
Hello, The index you have created is just fine and should be utilized as long as you perform a query that needs to use an index. Your query: select a,b from t; doesn't have any conditions imposed upon the table's rows. It just asks for all the them. Simple file scan. regards, thalis On Mon, 30 Apr 2001, Wix,Christian XCW wrote: Hi! I have some troubles with my index. I want to be able to use an index (test=(mintid, name)). I will use the index when I write: select mintid, name from loeb; I have created an index but it doesn't seem to work. Why? // Chris - Copenhagen mysql show index from loeb; +---++--+--+-+---+-- ---+--+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +---++--+--+-+---+-- ---+--+ | loeb | 0 | PRIMARY |1 | id | A | 90 | NULL | | loeb | 1 | rekord |1 | mintid | A | NULL | NULL | | loeb | 1 | test |1 | mintid | A | NULL | NULL | | loeb | 1 | test |2 | name| A | NULL | NULL | +---++--+--+-+---+-- ---+--+ 4 rows in set (0.00 sec) mysql show columns from loeb; +--+-+--+-+--++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+--++ | name | varchar(20) | | | || | distance | double(3,1) | | | 0.0 || | dato | date| YES | | NULL || | id | int(11) | | PRI | 0| auto_increment | | tottid | time| | | 00:00:00 || | mintid | time| | MUL | 00:00:00 || | art | varchar(10) | YES | | NULL || +--+-+--+-+--++ 7 rows in set (0.00 sec) mysql explain select mintid, name from loeb; +---+--+---+--+-+--+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+---+ | loeb | ALL | NULL | NULL |NULL | NULL | 90 | | +---+--+---+--+-+--+--+---+ 1 row in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: SV: index troubles
On Mon, 30 Apr 2001, Wix,Christian XCW wrote: But why doesn't this work then: select name, tottid, distance, min(mintid) from loeb group by distance; (tottid seems to be random) mysql explain select name, tottid, distance, min(mintid) from loeb group by distance; +---+--+---+--+-+--+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+---+ | loeb | ALL | NULL | NULL |NULL | NULL | 90 | | +---+--+---+--+-+--+--+---+ 1 row in set (0.00 sec) Because your index is on (mintid,name) and you are grouping by distance. The group by will force the table to be sorted (i think) and from each group you then select the min(mintid),etc. Having an index on things that are in your select clause is of no help. I bet (not much, but still I'd bet something) that if you added a where clause involving (mintid) you'd see you index utilised. If you want to help this particular query, add an index on (distance) regards, thalis :w // Chris -Oprindelig meddelelse- Fra:Thalis A. Kalfigopoulos [SMTP:[EMAIL PROTECTED]] Sendt: 30. april 2001 15:42 Til:Wix,Christian XCW Cc: '[EMAIL PROTECTED]' Emne: Re: index troubles Hello, The index you have created is just fine and should be utilized as long as you perform a query that needs to use an index. Your query: select a,b from t; doesn't have any conditions imposed upon the table's rows. It just asks for all the them. Simple file scan. regards, thalis On Mon, 30 Apr 2001, Wix,Christian XCW wrote: Hi! I have some troubles with my index. I want to be able to use an index (test=(mintid, name)). I will use the index when I write: select mintid, name from loeb; I have created an index but it doesn't seem to work. Why? // Chris - Copenhagen mysql show index from loeb; +---++--+--+-+---+ -- ---+--+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +---++--+--+-+---+ -- ---+--+ | loeb | 0 | PRIMARY |1 | id | A | 90 | NULL | | loeb | 1 | rekord |1 | mintid | A | NULL | NULL | | loeb | 1 | test |1 | mintid | A | NULL | NULL | | loeb | 1 | test |2 | name| A | NULL | NULL | +---++--+--+-+---+ -- ---+--+ 4 rows in set (0.00 sec) mysql show columns from loeb; +--+-+--+-+--++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+--++ | name | varchar(20) | | | || | distance | double(3,1) | | | 0.0 || | dato | date| YES | | NULL || | id | int(11) | | PRI | 0| auto_increment | | tottid | time| | | 00:00:00 || | mintid | time| | MUL | 00:00:00 || | art | varchar(10) | YES | | NULL || +--+-+--+-+--++ 7 rows in set (0.00 sec) mysql explain select mintid, name from loeb; +---+--+---+--+-+--+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+---+ | loeb | ALL | NULL | NULL |NULL | NULL | 90 | | +---+--+---+--+-+--+--+---+ 1 row in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
USE INDEX()/IGNORE INDEX() not getting through
I'm having the following problem with the IGNORE INDEX()/USE INDEX() directives on a mysql 3.23.32 mysql explain SELECT age_0,reliable,COUNT(*) FROM age,reliable WHERE age.id=rel iable.id GROUP BY age_0,reliable; +--++---++-++-+- -+ | table| type | possible_keys | key| key_len | ref| rows| Extr a| +--++---++-++-+- -+ | age | index | dd_idx,id | dd_idx | 4 | NULL | 100 | Usin g index; Using temporary | | reliable | eq_ref | dd_idx,id | id | 3 | age.id | 1 | | +--++---++-++-+- -+ Normally my query uses index(dd_idx) on table age_0 and index(id) on table reliable. Now I want to force the use of index(id) on table age_0 as well: mysql explain SELECT age_0,reliable,COUNT(*) FROM age USE INDEX(id),reliable WH ERE age.id=reliable.id GROUP BY age_0,reliable; +--++---++-++-+- -+ | table| type | possible_keys | key| key_len | ref| rows| Extr a| +--++---++-++-+- -+ | age | index | dd_idx,id | dd_idx | 4 | NULL | 100 | Usin g index; Using temporary | | reliable | eq_ref | dd_idx,id | id | 3 | age.id | 1 | | +--++---++-++-+- -+ Why does it insist on using index dd_idx for table age_0? I even tried telling to explicitly ignore this index: mysql explain SELECT age_0,reliable,COUNT(*) FROM age IGNORE INDEX(dd_idx),reli able WHERE age.id=reliable.id GROUP BY age_0,reliable; +--++---++-++-+- -+ | table| type | possible_keys | key| key_len | ref| rows| Extr a| +--++---++-++-+- -+ | age | index | dd_idx,id | dd_idx | 4 | NULL | 100 | Usin g index; Using temporary | | reliable | eq_ref | dd_idx,id | id | 3 | age.id | 1 | | +--++---++-++-+- -+ It still uses index(dd_idx) for table age_0 :-( Any ideas? regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Random string for each row in a table
Not understanding excactly why you'd need a random string, I'd suggest doing a simple password() call on each row's recipient ALTER TABLE ADD COLUMN rand_string char(16) NOT NULL; UPDATE my_table SET rand_string=password(email); This gives you a 16 char long (I think) random string. Sending this rand_string to the coresponding email, is beyond SQL. Export in a file the email and corresponding rand_string and from there beat the file to death with bash or perl or whatever scripting lang uage you want. regards, thalis On Mon, 23 Apr 2001, Prasad Mhatre wrote: Dear All, How do I create random string for each row in a mysql table? and mail the same each recepient in the respective row. Thanks Love and regards Prasad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: commit/rollback?
You can't with MyISAM and executing from shell prompt. Either alter the table type to BDB or Innobase If you stick with MyISAM you can use a higher level language (perl,php) to check the return value of each insert and handle the flow accordingly. If the 2nd insert fails, you'll have to complement the first insert with a delete. regards, thalis On Thu, 12 Apr 2001, Jochen Mielke wrote: Hello, Let's say I have a file which contains the following lines: insert into table_1 values (1,2,3); insert into table_2 values (4,5,6); It's called from the command line with mysql -u root -ppassword db_name file; I would like to undo the first operation on table_1 in case the second query (on table_2) returns an error. I believe this could be done by using commit/rollback if I used BDB tables. But I'm using MyISAM. Any hints how could I make this? Thanks, Jochen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Q U E R Y
On Tue, 10 Apr 2001, Peter Pentchev wrote: First, get a book on SQL. Read about joins in SELECT statements. Then, if you still need a quick answer, try the following: INSERT INTO table_c(name, score) SELECT name, a.score - b.score FROM table_a a LEFT JOIN table_b b ON b.name = a.name; Hope that helps. I think this will give him problems with the values of table B that don't have a match on table A, cause the expression number-NULL evaluates to NULL; Maybe it would be better to: CREATE TABLE C SELECT A.name,IFNULL(A.score-B.score,A.score) FROM A LEFT JOIN B ON A.name=B.name; cheers, thalis G'luck, Peter -- No language can express every thought unambiguously, least of all this one. On Tue, Apr 10, 2001 at 03:57:54PM +0545, Deependra B. Tandukar wrote: Greetings! I am using MySQL in RedHat 6.2 with PHP 4.0. I have a question on MySQL. Suppose I have two tables A and B in my database: Table A Name Score a45 b20 c75 d55 Table B NameScore a20 b9 c25 Now I need to subtract values of table B from table A and result should be like: Table C NameScore a25 b11 c50 d55 How can I do this? Looking forward to hearing from you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
3 Qs
Hello all, Does anyone know: 1. what the disadvantages/advantages are in building a static binary (EXCEPT for the extra space) 2. read somewhere that turning of the swap could improve performance. Anyone can testify for or against that? 3. is there a potential impact in performance when running under Linux 2.0.36. (compiled from source Mysql version 3.23.32 with gcc version 2.95.2) thanks in advance, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: show databases forbidden or limited to the owner database
I sent that reply about this 1 WEEK Back. Look up safe_show_databases and skip_show_databases in http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html regards, thalis On Mon, 9 Apr 2001, Fabien Salvi wrote: Hello, is it possible to prevent users from seing other databases than the one there are using ? I thought it was possible using priveleges and "Select_priv", but It doesn't work... I am using 3.23.33 Thanks in advance for your help... - Fabien SALVI Centre de Ressources Informatiques Archamps, France -- http://www.cri74.org PingOO GNU/linux distribution : http://www.pingoo.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Security
I haven't read Mysql's authorization mechanism from Paul's book, but from the Oreilly book and it was pretty comprehensive and intuitive. I suggest you borrow that book from your local library or buy it. To try to write here about how it works would be a waste of time for both of us. It is rather long and I'm not exactly proud of my explanatory abilities. It is a pretty elaborate and interesting mechanism though. Before you pull the trigger, I also suggest you remember to always do a 'mysqladmin reload' after every change you make to the mysql database, because otherwise you won't be able to see the changes you make. This has caused me lots of pain in the beginning. regards, thalis On Mon, 9 Apr 2001, Ashley M. Kirchner wrote: Okay, I'm about to rip my hair out trying to figure this out, and I thought before I start looking for a gun, maybe I should ask.. I need someone to explain the 'mysql' database to me. I've tried reading about it, tried different settings, but I'm lost. So far I've just been adding users and db's to the 'db' and 'user' tables, but something tells me that's not all there is to it. What are the other tables for? And how's about adding a user that can only access (and change) their DB (assigned by me), and/or adding a (different) user that can create their own DB(s), yet not muck with anything else on the entire (mysql) system (and screw up other users). I'm willing to entertain even more literature if that's easiest to point me to, but like I said, I've gone through the online docs, I've checked other resources online, and I'm still lost. AMK4 -- W | | I haven't lost my mind; it's backed up on tape somewhere. | ~ Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 SysAdmin / Websmith . 800.441.3873 x130 Photo Craft Laboratories, Inc. .eFax 248.671.0909 http://www.pcraft.com . 3550 Arapahoe Ave #6 .. . . . . Boulder, CO 80303, USA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: BUG: Cannot DELETE all records with NULL entries in UNIQUE KEYfields
On 9 Apr 2001 [EMAIL PROTECTED] wrote: Description: Attempting to delete all records in a table containing NULL values in a UNIQUE KEY field does not work as expected. Only a single record is deleted, presumably because the server thinks that the table will only have one record with a NULL value in it (as it is in a UNIQUE field). How-To-Repeat: Run the following SQL commands: --- BEGIN SQL TEST STATEMENTS -- USE test; # # Create a table with a unique key in addition to a primary key # DROP TABLE IF EXISTS table_with_key; CREATE TABLE table_with_key ( id int(10) unsigned NOT NULL auto_increment, uniq_id int(10) unsigned default NULL, PRIMARY KEY (id), UNIQUE KEY idx1 (uniq_id) ) TYPE=MyISAM; # # Create a table with only a primary key # DROP TABLE IF EXISTS table_without_key; CREATE TABLE table_without_key ( id int(10) unsigned NOT NULL auto_increment, uniq_id int(10) unsigned default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; # # Insert test data into table with unique key # INSERT INTO table_with_key VALUES (1,NULL); INSERT INTO table_with_key VALUES (2,NULL); INSERT INTO table_with_key VALUES (3,1); INSERT INTO table_with_key VALUES (4,2); INSERT INTO table_with_key VALUES (5,NULL); INSERT INTO table_with_key VALUES (6,NULL); INSERT INTO table_with_key VALUES (7,3); INSERT INTO table_with_key VALUES (8,4); INSERT INTO table_with_key VALUES (9,NULL); INSERT INTO table_with_key VALUES (10,NULL); # # Insert identical data into table without unique key # INSERT INTO table_without_key VALUES (1,NULL); INSERT INTO table_without_key VALUES (2,NULL); INSERT INTO table_without_key VALUES (3,1); INSERT INTO table_without_key VALUES (4,2); INSERT INTO table_without_key VALUES (5,NULL); INSERT INTO table_without_key VALUES (6,NULL); INSERT INTO table_without_key VALUES (7,3); INSERT INTO table_without_key VALUES (8,4); INSERT INTO table_without_key VALUES (9,NULL); INSERT INTO table_without_key VALUES (10,NULL); # # Delete all records from each table where the uniq_id field is null # DELETE FROM table_with_keyWHERE uniq_id IS NULL; DELETE FROM table_without_key WHERE uniq_id IS NULL; # # Select what is left -- notice the difference # SELECT * FROM table_with_keyORDER BY uniq_id, id; SELECT * FROM table_without_key ORDER BY uniq_id, id; --- END SQL TEST STATEMENTS The output for the last four statements looks like the following: --- BEGIN SQL TEST OUTPUT -- mysql DELETE FROM table_with_keyWHERE uniq_id IS NULL; Query OK, 1 row affected (0.00 sec) mysql DELETE FROM table_without_key WHERE uniq_id IS NULL; Query OK, 6 rows affected (0.00 sec) mysql SELECT * FROM table_with_keyORDER BY uniq_id, id; ++-+ | id | uniq_id | ++-+ | 2 |NULL | | 5 |NULL | | 6 |NULL | | 9 |NULL | | 10 |NULL | | 3 | 1 | | 4 | 2 | | 7 | 3 | | 8 | 4 | ++-+ 9 rows in set (0.00 sec) mysql SELECT * FROM table_without_key ORDER BY uniq_id, id; ++-+ | id | uniq_id | ++-+ | 3 | 1 | | 4 | 2 | | 7 | 3 | | 8 | 4 | ++-+ 4 rows in set (0.00 sec) --- END SQL TEST OUTPUT This is perfectly normal from Mysql's part. What you call a uniq_id in the table where you don't declare it as a unique key is merely another int field and a table scan will be performed to actually find ALL the occurences of the value you are deleting. On the other hand in the case where you actually declare the uniq_id to be unique by building a UNIQUE index on it, Mysql will of course use the asserted uniqueness and stop processing as soon as the first occurence of the value you want to delete is found. I.e. it assumes that as a unique field there will not be another occurence of this value. Of course on the other hand it allows you to insert multiple cases of NULL even though it is a unique field, because NULL is just SO DAMN NULL that it is NOT EVEN EQUAL TO ITSELF :-) So when you have a NULL in a unique field and add another NULL Mysql will not complain about it cause Null!=Null whereas it would complain if you had inserted a '1' and tried to insert another '1'. Clear as mud? regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: create table foo select * from bar does not copy keys??
I don't know if the manual says anything about this but if you check Paul's book page 150 (paul now we are referencing your book down to page level) it says: "creating a table by selecting data into it does not automatically copy any indexes from the original table" regards, thalis On Sun, 8 Apr 2001, Marc Swanson wrote: I'm not sure if this is a bug or not, but I just realized today that some of my create table queries (on mysql 3.23.32 ) were not behaving as I would expect.. Example: ** mysql create table foo (id int not null primary key, field1 varchar(55), unique data_index (id,field1)); Query OK, 0 rows affected (0.04 sec) mysql show columns from foo; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | id | int(11) | | PRI | 0 | | | field1 | varchar(55) | YES | | NULL| | ++-+--+-+-+---+ 2 rows in set (0.02 sec) mysql create table bar select * from foo; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql show columns from bar; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | id | int(11) | | | 0 | | | field1 | char(55) | YES | | NULL| | ++--+--+-+-+---+ 2 rows in set (0.00 sec) *** As you can see the primary key is not a part of table bar. Nor is the unique index as shown with mysqldump *** root@raid:/home/mswanson mysqldump -d test bar # MySQL dump 8.12 # # Host: localhostDatabase: test # # Server version3.23.32-log # # Table structure for table 'bar' # CREATE TABLE bar ( id int(11) NOT NULL default '0', field1 char(55) default NULL ) TYPE=MyISAM; ** The manual doesn't say anything about this. Is this a bug or just something I missed in the manual? Thanks -Marc- #/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/# #Marc Swanson | # #MSwanson Consulting | \|||/ # # | /o o\ # #Phone: (603)868-1721|-oooOooo-# #Fax:(603)868-1730| Solutions in: # #Mobile: (603)512-1267| 'PHP'Perl # #[EMAIL PROTECTED] | 'SQL'C++ # # | 'HTML 'Sh/Csh# #http://www.mswanson.com | 'Javascript# #/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/# - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Working with FIND_IN_SET
On Sun, 8 Apr 2001, John Hart wrote: I am working on a rather large database project, in which I am making a text column that contains different data, seperated by commas. What I need to do is run a query where I will return only the datasets that contain any of sets that partially, or fully match any data in the set... For example, if the dataset contains: dark, black, small I want to be able to return this data if the query contains ran contains any of the following matches: dark black small sma bla ...etc... I do not, however, want a full LIKE search with wildcards, because I do not want to return the set if they were to search on 'all' (which would match smALL). Is there an easy way to do this, or am I forced into matching entire words in a set? I appreciate any help anyone could offer me... Thank you... John If I got you correctly, you might consider RLIKE where you define the mathing string as a regular expressions. So in you case the regexp would be something like: RLIKE ".*, YOUR_QUERY_STRING_HERE.*, .*" regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: replace only certain words in a text column
On Sun, 8 Apr 2001, Stephen U. Lee wrote:
i want to replace all occurences of www to http://www in all columns of a
table.
the column contains blocks of text.
is there any way to do this directly in mySQL?
Check the manual for the infinite String manipulation functions.
If all your records start with a 'www' then the easy way out is:
UPDATE table SET column_name=CONCAT("http://",column_name);
If not then, again the easy way out is:
UPDATE table SET column_name=CONCAT("http://",column_name) WHERE column_name LIKE
'www%';
regards,
thalis
thankx. =)
---
Stephen U. Lee [EMAIL PROTECTED]
(632) 714-9924 (0917)9068115
===
f o r t h m e d i a
http://www.forthmedia.net [EMAIL PROTECTED]
---
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Hierarchical lists
On Sun, 8 Apr 2001, Realbad Atgames wrote:
--- [EMAIL PROTECTED] wrote:
Hello, I'm new to MySQL and have ran into a
problem in
designing my database. I have a table of items, A,
and
each item should have any number of other records
from
another table, B, associated with it. I also need
to
have any number of records from table C associated
with table B. They need to be associated in such a
way
that I can quickly find out what is associated
with a
record in table A, as well as what is associated
with
a record in table B. In other words, how do I
store an
array of pointers? What's the best way to do this?
I
couldn't find anything relevant in the docs.
I'm not sure that I've understood correctly, but
perhaps :
create table A (
aid int unsigned not null auto_increment,
value char(1000),
key idxaid (aid)
);
create table B (
bid int unsigned not null auto_increment,
aid int unsigned not null default -1,
value char(1000),
key idxbid (bid),
key idxaid (aid)
);
create table C (
cid int unsigned not null auto_increment,
bid int unsigned not null default -1,
value char(1000),
key idxcid (cid),
key idxbid (bid)
);
That way, reading data from Table B tells you about
the parent,
likewise for C.
Thanks for you reply..but I want to be able to find
the list of the children, not parents. For example:
In table A: foo quux (separate from B's quux)
/ \ /
In table B: baz quux bar
/|\ \
In table C: w x y z
Each table's rows (except for table C) should be
allowed to have any number of children, and I should
be able to get a list of all the children of a parent.
In C, I would do it like this:
typedef struct _A
{
int value;
B* children[100];
} A;
typedef struct _B
{
int value;
C* children[100];
}
typedef struct _C
{
int value;
}
How would I do this in MySQL?
The question is not a Mysql issue but rather simple sense and logic.
Make your tables as:
A: aname char()
aid int auto_increment
B: bname char()
bid int auto_increment
aid int which you'll set depending on which record of A this specific record
of B is a child of
C: cname char()
bid int which you'll set depending on which record of B this specific record
of C is a child of
To find out all the B_children of record 'A123' from table A you'd do:
select bname from A,B where A.aid=B.aid AND A.aname="A123";
Same for finding the C_children of a record from table B.
Hell! you can even get the grandchildren in table C that belong to a record 'A123'
from table A:
select cname from A,B,C where A.aid=B.aid AND B.bid=C.bid AND A.aname="A123";
regards,
thalis
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate of Everything in Table
Yes, but in his case there is a full duplicate of every row for every column, so just specifying one of the columns will do the job. regards, thalis On Fri, 6 Apr 2001, Gerald Clark wrote: You need to specify those columns that need to form a unique combination, "Thalis A. Kalfigopoulos" wrote: Actually you don't have to specify all columns in the UNIQUE index. Just one of them, and the entire row will be dropped for all redundant copies. So just do: ALTER IGNORE TABLE lala ADD UNIQUE (PageID); I didn't remember the IGNORE thing. Nice idea... regards, thalis On Fri, 6 Apr 2001, Gerald Clark wrote: Are you checking the manual and following along, or just plugging in my hints? You have to specify a length for the text fields. Your total key length must be less than 256 if I remember correctly. Choose values that are long enough to be unique. EX: ALTER IGNORE TABLE Pages ADD UNIQUE ( PageID, ArticleID, PageTitle(25), Text(100)) Vigile wrote: That gave me: SQL-query: ALTER IGNORE TABLE Pages ADD UNIQUE ( PageID, ArticleID, PageTitle, Text ) MySQL said: BLOB column 'PageTitle' used in key specification without a key length I guess because PageTitle and Text are [text] fields I can't do that. Ryan Shrout Production Manager Athlonmb.com http://www.athlonmb.com [EMAIL PROTECTED] - Original Message - From: "Gerald Clark" [EMAIL PROTECTED] To: "Vigile" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 06, 2001 9:54 AM Subject: Re: Duplicate of Everything in Table Sorry: ALTER IGNORE TABLE Pages ADD UNIQUE ( PageID, ArticleID, PageTitle, Text ) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: REPOST: SELECT syntax
On Wed, 4 Apr 2001, Ashley M. Kirchner wrote: Db - table1, table2 and table3 SELECT table1.task, table2.comment, table2.remarks, table3.history FROM table1 LEFT JOIN table2 ON table1.id=table2.todoid LEFT JOIN table3 ON table1.id=table3.todoid WHERE table1.id=10; Results (roughly) in: +--++--+-+ | task | comment |remarks | history | +--++--+-+ | task_1 | cmt_1| rmk_1| | | task_1 | cmt_2| | | | task_1 | cmt_3| |hst_1| | task_1 | cmt_4| rmk_2| | | task_1 | cmt_5| |hst_2| | task_1 | cmt_6| | | +--++--+-+ However, what I'd really like to have is the following: +--++--+-+ | task | comment |remarks | history | +--++--+-+ | task_1 | 6 | 2 | 2 | +--++--+-+ How do I change the select clause to have COUNT() in it instead? AMK4 You get your COUNT() by adding the corresponding GROUP BY clause. I assume something like the following should work: SELECT table1.task,COUNT(table2.comment),COUNT(table2.remarks),COUNT(table3.history) FROM table1 LEFT JOIN table2 ON table1.id=table2.todoid LEFT JOIN table3 ON table1.id=table3.todoid WHERE table1.id=10 GROUP BY table1.task; hope it works, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 well does MySQL handle Web Publishing?
On Thu, 5 Apr 2001, Pete Koning wrote: Greetings! Quick background: i have extensive RDBMS experience (sybase, progress) but have been "out of the loop" in this field for a few years working as a sys-admin for a linux/unix-based ISP in Canada. We want to host a web-based resource center powered on the backend by an RDBMS. Documents to be displayed on this web site may take many forms: adobe, text and word processing documents, digitized images, hyperlinks, video and audio clips, etc. Text-based documents must be indexed and searchable by keywords entered by users. I would be interested in your experiences with MySQL in this respect. Specifically I'm trying to determine the following: 1. would text (word, excell, adobe, etc) be stored directly in the database? If not, how would they referenced? You can have them in the DB as BLOBs, or you can have them as files on the fs, and have the pathname in the DB 2. what about other document types (mgp, jpg, streaming-video, audio). binary files, are binary files. I'd go for the fs solution for all binary data. Makes them accessible by other applications if they are on the fs. 3. can text-based documents be indexed directly for quick searches? If not, do i need to manually index them via descriptor fields of some sort? Lookup the FULLTEXT at: http://www.mysql.com/doc/M/y/MySQL_full-text_search.html 4. what 'connective' software could be used to create web-based forms that can both query the database to retrieve documents? I don't get exactly what 'connective software' means, but you can create your CGI sitting behind your forms with just about anything you like. Take you pick: Perl, PHP, C, C++, Python are ones I'm aware of. 5. what would be the best software to use to create "input" forms to allow documents to be added/updated/deleted? Again I'm not clear. See above. 6. Most importantly, how well does MySQL handle this kind of application, as opposed to proprietary DBs like Sybase, Oracle and Progress? It is a worthy competitor :-) Make what you want of it. regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: REPOST: SELECT syntax
On Thu, 5 Apr 2001, Ashley M. Kirchner wrote: "Thalis A. Kalfigopoulos" wrote: +--++--+-+ | task | comment |remarks | history | +--++--+-+ | task_1 | cmt_1| rmk_1| | | task_1 | cmt_2| | | | task_1 | cmt_3| |hst_1| | task_1 | cmt_4| rmk_2| | | task_1 | cmt_5| |hst_2| | task_1 | cmt_6| | | +--++--+-+ SELECT table1.task,COUNT(table2.comment),COUNT(table2.remarks),COUNT(table3.history) FROM table1 LEFT JOIN table2 ON table1.id=table2.todoid LEFT JOIN table3 ON table1.id=table3.todoid WHERE table1.id=10 GROUP BY table1.task; Based on the above data, and the above select, I get the following: +--+-+-+---+ | task | comment | remarks | history | +--+-+-+---+ | task_1 | 6 | 6 | 2| +--+-+-+---+ Obviously there are not 6 remarks, but only 2. So why is it counting 6? Is it because it's in the same table as the comments, and there are 6 comments? Does this mean MySQL counts an empty cell as having data in it? If I replicated the case correctly, I can say that it works fine for me :-/ What you mean by 'empty cell'? COUNT() doesn't include NULL values, but if the empty cells are actually empty strings '', then they'll be counted. Check you don't have empty strings as empty remarks, but that you have NULLs. Even with empty strings as remarks though you can bypass them with an extra WHERE condition :-) regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: SCRIPTS IN DIFFERENTS DATABASES
On Thu, 5 Apr 2001, Santiago LLobet wrote: Hi,... We've got a MySQL server running with a lot of different databases that have the same table-structure. Does anybody knows how to make an SCRIPT that performs an administrative task (like ALTER TABLE 'table_name') in all the databases? Do I have to use a script like this ? USE database1; ALTER TABLE users . ; USE database2; ALTER TABLE users..; USE database3; . . . (Actually the USE command does not work) Thanks a lot to all the people in the list :-) I don't see why you'd have an administrative script to perform ALTER commands. Not my idea of a batch job. Anyway, why don't you just give th DB name on the ALTER line: ALTER TABLE dbname.tablename... You write the script and execute it as: mysql -u root -p lala.sql regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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] cant start mysqld process
On Thu, 5 Apr 2001, Gerald Clark wrote: Another thought. These are isam tables, not myisam tables. Unless the two machines are the same hardware type and operating system, you can't just copy the files over. Use msqldump. Actually I was under the impression that the way the files were stored, they were archtecture independent and you can infact copy them from anywhere to just about anywhere :-/ (correct if wrong). So h/w architecture and OS should not matter (do they?) .ISM is for ISAM index files, which is the old format. The MyISAM files which is the new format, have a .MYI ending. host.ISM is the ISAM version of the index file of the 'host' table of the 'mysql' database, which is used for user authorization (too many 'of's in a sentence 8-). Actually it should be looking for host.MYI What version was the last one and what is the new you moved to? regards, thalis "Kevin J. MacDonald" wrote: In my mysql/data/mysq directory all the permissions are set to 660. Any ideas on what host.ISM file that it is looking for. I never seen it before and when I check the old server I can't find it. thanks kevin -- From: "Brian Warn" [EMAIL PROTECTED] To: "Kevin J. MacDonald" [EMAIL PROTECTED] Subject: Re: [mysql] cant start mysqld process Date: Thu, Apr 5, 2001, 12:53 PM Kevin, Make sure that the permissions on your mysql database are at least 660. I had the same problem. After fixing, the problems went away. - Original Message - From: "Kevin J. MacDonald" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, April 05, 2001 8:43 AM Subject: [mysql] cant start mysqld process Hello, I just finished moving my database to another machine. It was running fine and after I rebooted I now get an error when I try to run safe_mysqld . When I look in mysql/data/comproom.err file, I get a message about '... cant find file: './mysql/host.ISM' (errno: 13) thanks for any help kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: turning logging on/off while server is running
On Thu, 5 Apr 2001, Roel Vanhout wrote: Hi all, Is there a way to turn the query logging on and off while the database server is running? Right now I have a script that stops and restarts the database with loggin on or off depending on the parameters, but this is not so great; I was wondering if there is another way. Also, is there a way to query the server if logging is on or off? I'd like to write a nice gnome applet to start/stop logging but I'm not sure how to get this value. You can get whether the server is loging or not through $ mysqladmin -p variables and check the value of, you guessed it, the entry 'log' I don't know if you can make the server turn logging on/off while it is running. But you can swithc logging on/off for a particular session giving: SET SQL_LOG_OFF=1 (the client must have the Process privilege) regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Foreign Keys
On Thu, 5 Apr 2001, Dennis Gearon wrote: Are people using Mysql using foreign keys and how are they using them? It seems that the way to use them is with the scripting language used to access the database. Sure we are...the manual way i.e. one insert/delete at a time for each foreign key :-) Read http://www.mysql.com/doc/B/r/Broken_Foreign_KEY.html to get an idea why MySQL doesn't like foreign keys that much. It could have them, but it won't. At least it supports the syntax so that you don't get errors when migrating from Oracle -) regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: change in user info requires restart
On Thu, 5 Apr 2001, Kris Gonzalez wrote: when adding a new user or changing the authentication information for existing users, the changes often do not take effect unless i take down and restart 'mysqld'. example: changing root's password and exiting the mysql client, i cannot log back in to the mysql client using the new password...i must use the old password until i restart the database daemon. You have to reload the grant tables (the 'mysql' database) mysqladmin reload -u DBA_user -p Give your password when prompted and the new changes will take place. regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Previleges Issue
On Fri, 6 Apr 2001, K. C. Huang wrote: The question is regarding the previleges config on a virtual server enviroment. I managed to set the previleges for the user so that he can not access databases other than his own, but one problem still bothers me. The user is able to list (show databases) all the databases on the server even though he can not read any table inside. Can anyone show me how to hide all the other database names from virtualhost user? Read http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html and check out parameter safe_show_databases regards, thalis Thanks in advance Terry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate of Everything in Table
On Fri, 6 Apr 2001, Vigile wrote: I have a table, that DOES NOT have a primary key, but is setup like this: Field Type Attributes Null Default Extra Action PageID int(11)No 0Change Drop Primary Index Unique ArticleID int(11)No 0Change Drop Primary Index Unique PageTitle textNo Change Drop Primary Index Unique Text textNo Change Drop Primary Index Unique However, for some reason (I think I know why, but its not important) I have two of every entry. Such as this: 169words words 269words words more 369words words most 169words words 269words words more 369words words most So, when I am calling info from this database, I get doubles of each result. How can I delete just one of each entry, so I am left with just: 169words words 269words words more 369words words most I can't do the delete that I was thinking about: Delete FROM Pages Where Pageid=1 and articleid=69 because that would delete BOTH of them. Doh! Any help? How about: delete from tablename where id=1 limit 1; But you'd have to do it for id=1,2,3... Not so good. regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: random select
On Tue, 3 Apr 2001, Marcos wrote: hi, i would like to show 5 random field from a total of 100. id is my primary key id name -- abmarcos akjohn how can i show 5 of them randomly? thanks in advance, marcos When you say "5 random fields" I assume you mean "5 random rows". select * from table order by rand() limit 5; Read the SELECT from the manual (http://www.mysql.com/doc/S/E/SELECT.html) regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: time and date
Check the manual for the TIMESTAMP datatype. Will hold the last update time per record abd therefore the insert time that you want. regards, thalis -- No excellent soul is exempt from a mixture of madness. -- Aristotle On Tue, 2 Apr 2002, chris wrote: Is there a way to store the time and date that a record was added within MySQL? I want to show the time and date on some of my records and can't seem to figure out an easy way to do it. Thanks! -Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Creating database
On Fri, 30 Mar 2001, Herman Pool wrote: Hi out there, I'm new with MySQL. I have installed Linux 6.0 and MySQL 3.23.33 This happens when I want to create a database: [mysql@nestorix mysql]$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 49 to server version: 3.23.33 Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql create database java_db; ERROR 1044: Access denied for user: '@localhost' to database 'java_db' mysql \q Bye [mysql@nestorix mysql]$ Why? What did I do wrong? Any ideas? You don't have the privileges to perform a CREATE command. This is part of the MySQL authorization mechanism. From the error message I see that you are connected as user 'nobody', who most probably doesn't have such a privilege. Try connecting as the DB admin (if you have that authority). If not do a: select user(); to see the username you are logged in under and then: select * from mysql.user; and check that the Create_priv column is set to 'Y' for this user. If this is 'N' you'll have to check the mysql.db table and maybe even the mysql.host table. To understand what's going on start reading at: http://www.mysql.com/doc/P/r/Privileges.html regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: multiple instances and initial password
On Fri, 30 Mar 2001, Kristopher Briscoe wrote: Two things that I am sure will be answered very quickly. 1) I have a development box that I have successfully installed and configured 3 seperate instances. Each instances has its own port number. When playing around with mysqladmin I know I can specify the port number as an option, but what I cannot figure out is how to change the password. What is the default password that is used whenever the tables are created for root? the empty string '' 2) second what is the syntax for changing that blasted password. since root initially doesn't have a passwd, you set it with: mysqladmin password 'THE+NEW+PASSWORD' But changing it for a user that already has one is: mysqladmin -p password 'THE_NEW_PASSWORD' and this time you'll be prompted for the old passwd for the change to take place. Thanks for the patience, Kris (aka: unixboy Kris_aka:_unixboy, I think you should do the unix approach: read the manual. regards, thalis -- No excellent soul is exempt from a mixture of madness. -- Aristotle - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Match Um up
On Thu, 29 Mar 2001 [EMAIL PROTECTED] wrote: Hi, I have two database`s with one table in each. (MySQL) Is there a way that I can select data from one table and then match it with the data in another and discard the results so all I am left with is the original data from the first table which never matched up. Database1 EmailAddress RecID - Email Database2 Members lots of stuff blah blah Email So I would match the Email from Database1 Table EmailAddress to those in Database2 Members. Take out the matches and be left with a list of people who started signing up but never completed it. Any thoughts? Ade select db1.table1.email from db1.table1 left join db2.table2 on db1.table1.email=db2.table2.email where db2.table2.email is null; regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
safe_mysqld options
Hello, A question about the -O option you can pass on command line to safe_mysqld. If for example I give -O record_buffer=64M does that mean that I'll get: exactly 64M, at the most 64M or at least 64M? Also I have a batchfile with about 150 queries (all plain selects) which I feed into mysql. Are they all going to be executed by one thread sequentially or split amongst many threads? thanks in advance, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Counting distinct rows
SELECT COUNT(DISTINCT ID) FROM my_table; Note: NULL values will be omitted from the count. regards, thalis p.s. would be a good idea to read an SQL tutorial before your next post On Thu, 29 Mar 2001 [EMAIL PROTECTED] wrote: Hi, I am trying to count all the distinct id numbers in a column but I just can't figure it out... What would be the appropriate select statement for this, 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: LOAD_FILE and replication
On Mon, 26 Mar 2001, Maros Klempa wrote:
Hi,
I have two servers with MySQL 3.23.24-beta. One as master and one as slave.
Replication between this two servers work fine. Now I want store pictures
to table. On master server I use command
INSERT INTO PICTURES (ID, PICT) VALUES (1, LOAD_FILE( '/images/image.jpg')).
It work o.k. MySQL save file image.jpg to table PICTURES. But when I look to table
PICTURES on slave server in field ID is value 1 but field PICT is empty. Why?
Work function LOAD_FILE with replication correctly?
I'll just guess here: at the slave side, the command is ran excactly the same way.
This means that the slave will try to do a LOAD_FILE('/imag...') looking for the image
at its local fs. Of course will fail because the image doesn't reside on its
filesystem.
I think something similar was mentioned some weeks ago with regard to a LOAD DATA
INFILE... at the master side, in which case again the input_file didn't resize on the
slave filesystem and so the call failed. Correct me if i'm wrong.
regards,
thalis
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: alter table error
On Mon, 26 Mar 2001, Daniel Adams wrote: Well, I wanted to limit the size of the index so it wouldn't take up a lot of space. The message table is about 500mb right now and is going to get a lot bigger. Well not limiting the size of the index get rid of the error? Actually you should be able to do that, and it is quite advisable. I assume/guess that the error code returned with your error message is actually '126'. Looking it up with perror, I get: 126 = Index file is crashed / Wrong file format If this is correct, it would be nice to ran a myisamchk to check on your indeces and then try again. regards, thalis Original Message On 3/26/01, 11:35:19 AM, "LIBASOV IOANNIS" [EMAIL PROTECTED] wrote regarding Re: alter table error: It's better like this: alter table message add index subject2 (subject); - Original Message - From: "Daniel Adams" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 26, 2001 7:19 PM Subject: alter table error Can anyone tell me what this means and how to fix it? I ran the below query to simple add another index and this is what i got. Thank you. mysql alter table message add index subject2 (subject(10)); ERROR 1034: 126 for record at pos 176850808 - Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
i think i screwed up
Hello, I did something dumb: ran myisamchk while mysqld was up and running and, being on a linux system, I run the deamon with --skip-locking. This is what I guess made one of my tables appear to be empty. According to the manual if you run myisamchk while someone else is doing an update on the table, you corrupt it. But there are two things that made me think twice: 1) I afterwards shutdown the server and re-ran myisamchk with --information --check (and later with --extend-check) and it says that the table has 32 records (which are excactly what it's supposed to have) and that everything is just fine 2) the table that lost its records, isn't one of the tables that get updated at all :-( It is totally static. So even if running myisamchk with mysqld running is a bad thing to do, still I wouldn't expect THIS table to get corrupted. 3)I had a backup (bit of wisdom in midst of the stupidity) and I recovered the data. I notices that the data file .MYD was half the size that in my backup, and the index MYI and .frm was just fine (which explains why myisamchk thought everything was just fine) Any ideas how I could have brought back the 32 recs that myisamchk says are there, but I cannot see? Is there some utility to check the .MYD file's integrity? Also in the manual it says that --skip-locking is used due to locking deficiencies of the OS locking of some linux systems. Can someone define which these linux systems are? I am running on 2.2.13 (slackware7), is it safe to remove the --skip-locking? regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
i think i screwed up (cont'd)
Also with regard to the lost 32 recs, the error log mentioned: 010323 5:41:50 read_const: Got error 127 when reading table ./eval/reviewer Where perror gives: 127 = Record-file is crashed And Mysql version: 3.23.30-gamma-log regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 script that uses mysql.
On Fri, 23 Mar 2001, Johan Vikerskog (ECS) wrote:
I am not sure if i should post it here but please do help my if you can.
The script is as follows:
html
body
?php
mysql_connect (localhost, username, password);
mysql_select_db (dbname);
if ($first_name == "")
{$first_name = '%';}
if ($email_player == "")
{$email_player = '%';}
$result = mysql_query ("SELECT * FROM tablename
WHERE first_name LIKE '$first_name%'
AND email_player LIKE '$email_player%'
");
if ($row = mysql_fetch_array($result)) {
do {
print $row["first_name"];
print (" ");
print $row["email_player"];
print ("p");
} while($row = mysql_fetch_array($result));
} else {print "Sorry, no records were found!";}
?
/body
/html
I basically have two questions.
If i want to make a "mailto" function to "print $row["email_player"];", how do i do
that?
Got to http://www.php.net and on the top search entrybox, give the word: mail
You'll get the mail() function manual which is pretty descriptive.
And question #2.
Exactly what do the $row do?
Logically: $row holds a row from your result set. So you have to call
mysql_fetch_array() multiple times to iterrate through every row of the result set,
represented here by the $result handler that mysql_query() returned.
Physically: $row holds an associative array whose keys are the labels of the columns
of your result set.
regards,
thalis
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: _
On Fri, 23 Mar 2001, Markus Siagian wrote: Hi, I'm still a newbie in mysql and also sql statements. I was wondering if some one can help me with this problem. I'm trying to select 5 of the highest values from a table. But i can't seem to find any help from the manual and also some other books. In the manual, there is only a function to find the highest(max) value. Do i need to make a new function?? Lookup ORDER BY and LIMIT clauses in the manual. It would be: select * from my_table order by a_column limit 0,5; regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL, PHP, Apache: /usr/libexec/ld.so: httpd: libmysqlclient.so.6.0:No such file or directory
Since you updated both mysql and php you presumably should recompile apache as well. A quickdirty solution though could be to just create a link: libmysqlclient.so.6.0-libmysqlclient.so.10.0 and see what happens from there :-) regards, thalis On Fri, 23 Mar 2001, Colonel Panic wrote: Hi I've installed MySQL (mysql-3.23.33-pth) and PHP (php4-4.0.3pl1-mysql) from OpenBSD ports... ... but this has broken apache. Restarts/configtests now trigger:- /usr/libexec/ld.so: httpd: libmysqlclient.so.6.0: No such file or directory I've added shlib_dirs="/usr/local/lib/mysql" to rc.conf and the file is world readable and executable (although oened by root). ...but the problem persists. In /usr/local/lib/mysql/ libmysqlclient.so.6.0 is actually version so.10 ... is this the cause of the problem? all any help and guidance would be appreciated. thanks cp -- [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: create table error
On Fri, 23 Mar 2001, Daniel Adams wrote: Can someone please tell me what the problem is below. All I am trying to do is pipe a backup created with mysqldump into mysql and it gives me this error: ERROR 1071 at line 1291: Specified key was too long. Max key length is 500 It is excactly what it says. Mysql cannot create keys whith length greater than 500 bytes. In your case name+description=255+255=510 bytes. Try changing the last line to: KEY name (name(250),description(250)) It'll probably work, but still it'd be better if you truncated the 2 fields even more. For example it is highly unlikely you'll need more that the first 15 characters to discriminate between 2 names. regards, thalis Here is the query where the error is: CREATE TABLE list ( list_id mediumint(8) unsigned NOT NULL auto_increment, name varchar(255) DEFAULT '' NOT NULL, description varchar(255), email varchar(100) DEFAULT '' NOT NULL, message_count mediumint(8) unsigned DEFAULT '0' NOT NULL, category_id mediumint(8) unsigned DEFAULT '0' NOT NULL, type tinyint(3) unsigned DEFAULT '0' NOT NULL, day_count smallint(5) unsigned DEFAULT '0' NOT NULL, num_days mediumint(8) unsigned DEFAULT '0' NOT NULL, day_ave smallint(5) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (list_id), KEY type (type), KEY category_id (category_id), KEY name (name,description) ) Thanks you in advance. - Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL question
I don't know how you define 'minimal' but approximately 100+ emails per day from the user list and a response time from 5 minutes to 24 hours should be adequate for the average "small-time real estate company" as you call it. Coming to "What will you guys answer and not answer", I guess there is no pattern. Whatever one fancys. Then again, since some people should be making smthing out of it, I suggest you go for the $200 package, even if you do get your answers through this list :-) regards, thalis On Fri, 23 Mar 2001, Erik Slazyk wrote: Hello, I am developing a web database for a new small-time real estate company, and I have been reading into using MySQL. I have read that there is minimal email support for MySQL problems, unless one pays for one of your 5 support packages. What I would like to know is, if they decide to use MySQL, how much support is "minimal" support? What will you guys answer and not answer if they do not pay for the support? (I have recommended to my clients to go with the $200 a year package, but this was one of their questions before making a decision.) Thank you for your time! Erik Slazyk _ Get your FREE download of MSN Explorer at http://explorer.msn.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: FW: potential vulnerability of mysqld running with root privileges
I think that Benjamin was trying to make a point here regarding an easily reproducible scenario (I don't care if you wanna call it a "security flaw" or a "flying pig") under some conditions which are not that hard to come upon in the real world. The problem that really comes to mind is that some people think mysql is the next big thing after instant coffee. Most people, probably me included, made the mysql choice without much thought or background search. The infering mechanism usually is 'it is popular'='it must be good'. So even more people join in, so it gets more popular...ad infinitum. I only realized how many things were missing after I started using it, and I'm not willing to give it up because I don't really have any real-world/high-volume/critical application needs and there is still stuff to learn working with it. As long as I can play around and it doesn't crash every other day, I'm happy. regards, thalis On Wed, 21 Mar 2001, Sinisa Milivojevic wrote: Benjamin Pflugmann writes: Hi. cut Of course, that why I was explicitly talking about the fact, that the user needs CREATE privileges (FILE privileges are not needed, If I am not mistaken). First of all, it is easy to reproduce a test case. Second, that FILE privilege I was citing is there because of SELECT .. INTO OUTFILE ... I thought that you would understand that. Regarding shadow file, I can crack it in 15 minutes, if I had the interest, but I have no such interests. And I did it only on my own computer once 4 years ago. A CGI script that could be talked to executing ln -s That is a bit far fetched. Any scenario that involves shell access (or funny CGI scripts) or similar, can not be considered as MySQL security flaw. Regards, Sinisa - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 wishlist
Dear Santa, here is my wishlist: I wish that the mysql utility had support for the shell's history functions. I catch mysql so many time doing something like !show regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Tables
On Sat, 17 Mar 2001, Denis Mettler wrote: Hi there, Another problem. i have the following tables: table 1: id, primary key, auto increment name varchar(50) city varchar(50) phone varchar(25) table 2: categorie_id, primary key, auto_increment kategorie varchar(50) in table one i have the contacts and in table two the categories (Business, privat...) but how can i link these tables for a query. i ask because i know that mysql doesn't support foreign keys. thanks in advance denis Thinking of your ER diagram will help: your 1st table originates from entity 'contact' and your 2nd table from entity 'category'. In between you have a relationship linking them. So the relationship's table is what you need and it consists of (id,category_id). regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: question about NOT IN
On Fri, 16 Mar 2001, Jason Landry wrote: Subqueries like that won't work until version 4.0. for now it's rather ugly and inefficient, but one way you could do it would be like this: select table1.*, ifnull(table2.column2,-1) as marked from table1 left join table2 on table1.column1=table2.column2 having marked=-1 First when I read this I thought that you probably wanted 'where marked=-1' rather than 'having marked=-1' but yo are right ? I tried it with 'where marked=-1' and I got an error that it didn't know which one column 'marked' is. Strange... Anyway, some questions/comments: I tried doing a bulk load (load data infile...) of 1M rows in a single table with 100 columns. It took ~13mins. Then I tried doing an insert...select from a source table to my destination table, and it was over in ~1min. I though things would have been the other way around (seems everything I 'think of' today, us wrong :-) The other thing is that if a user has a password with spaces in the end of it, mysql trims of the trailing spaces before validating. So if my passwd is abcd and I give abdc[space], I still get granted access. I didn't like that :-( Would rather like to have trailing spaces. regards, thalis I've done something similar before, and it worked ok. Of course having subqueries will be nice. - Original Message - From: "Carl Karsten" [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, March 16, 2001 12:13 AM Subject: Re: question about NOT IN select * from table1 where column1 not in (select column2 from table2) - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 15, 2001 7:13 PM Subject: question about NOT IN Please I would want how I can get the list of records from a table1 that do not appears on a table2, supossing a column e.g code is used as connector or joiner present at both tables; Thanks Ernesto - Este mensaje fue enviado a través de Qnet http://www.qnet.com.pe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: summarizing rows. an sql question
What's wrong with SUM? select sum(count) from table; Does it get any simpler than that? regards, thalis On Wed, 14 Mar 2001, Maxim Maletsky wrote: Hello everyone, I have a quick question: the following table keeps a track of logged in users. CREATE TABLE auth_lost_in_space ( sid varchar(32) NOT NULL, count smallint(5) unsigned zerofill DEFAULT '1' NOT NULL, date datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (sid) ); Count is the amount of time the sid logged in. What I need is to have a SELECT statement which selects every row and combines the values of count together: count 1 count 5 count 1 count 3 count 1 and with one select I want to get 11 (or 00011 which is the summary of count in all rows) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: UNIQUE multiple-column index
On Fri, 2 Mar 2001, Nick Kostirya wrote: Hi, All! Please, give me an opinion about multiple-column index. May I create the UNIQUE multiple-column index, which the index of each column is not UNIQUE in? If yes than how do it? Best. Nick. When you create a multi-column unique index, it means that the combination of the participating columns must be "unique" and not that each of the participating columns must be "unique" by itself. How you do it?...the normal way: mysql create unique my_index on my_table (col1,col2..); and you may or may not have indeces (unique or not) on cols 1,2... The above is fully equivalent (as of 3.22) to: mysql alter table my_table add unique my_index (col1,col2...); But be careful because even though Mysql will complain if you try to insert a tuple whose value for the unique index attributes (col1,2...) is the same as an already existing tuple, this doesn't hold if any of the index attributes is NULL. To cut a long story short (as I understand it): NULL!=NULL regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Locked Out
On Fri, 2 Mar 2001, Gerald L. Clark wrote: The manual tells you how to reset a forgotten password. It also tells you not to mess with these tables, but to use. GRANT and REVOKE. We all have to explore everything, dont't we? Worst case, kill mysqld and reinstall privilege tables with mysql_install_db --user=mysql Or alternatively kill mysqld and restart it with option --skip-grant-tables. Read the manual about it (section 4.16.4 Command-line Options) regards, thalis Asaf Maruf Ali wrote: I have been using MySQL for sometime now. However, i had been experimenting with MySQL priviliges and have locked myself out. I cannot access the database to perform any function. I cannot issue any command like mysqladmin ping or mysqlshow. Somehow the command mysqlaccess doesnt execute either. The message i get trying to access mysql is: Access denied to user root@localhost using (password=NO) If i try to connect to the DB using mysql -p and give any password the message is: Access denied to user root@localhost using (password=YES) Unfortunately, i didnt note down the changes i made to the host, DB and user tables. Any help would be really appreciated. Thanks Asaf Maruf - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Problen in mysql 'read only'
Did you change the permissions of the files after starting the sever, or before? Did you read the GRANT section of the manual. Does your user have the UPDATE privilege? Raman Aggarwal wrote: Dear Sir I am a regular user of MySql. I will be obliged if you help me. 1. The path of the data directory is /var/lib/mysql 2. The database name is 'directory' 3. The permissions of directory are drwxrwxrwx2 root root 4096 Mar 1 16:24 directory 4. All the files in the directory are having the permissions -rw-rw-rw-2 root root Now when I want to insert the data error message appears that the table is read only. error 1036. I tried all combination of permissions but no result. Mysql version is 3.22.25 Raman IMHO I don't think Mysql should be restarted after changing the permissions of the files at the OS level. Does MySQL cache the permissions too[?] Anyway, perror gave me: $ perror 1036 Error code 1036: Unknown error 1036 Are you sure you are giving the error code correct here? regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: PHP and MySQL
Hi, I've installed MYSQL on Red hat. I'm trying to run the command mysqlaccess It asks for the Password for MySQL superuser root: Can anyone tell me the password for this? Thanks Sandeep mysqlaccess is a perl script to check the access priviliges of a user:db:host scenario. What you need is to run safe_mysql to get the daemon running and then mysql (which will not ask for a pass the 1st time) and set a password for root by yourself. best of luck, thalis p.s. alternatively to the above: read the first ~500 lines from the mysql manual - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 problem
On Fri, 2 Mar 2001, Richard Vibert wrote: Hi, I having trouble working out how to get a result set similar to the following where I select from a table with Date Sales column. My specific question is can I have a column that accumulates values, if so could I have some guidance on how to express this in a select statement please. +--+---+---+ | Month| Sales | Cum Sales | +--+---+---+ | Jan | 1000 | 1000 | | Feb | 1500 | 2500 | | Mar | 1200 | 3700 | | April| 1400 | 5100 | +--+---+---+ Many thanks in advance. Richard I assume the query should be like: select MONTHNAME(date_col) as Month,count(sales_amount) as Sales,sum(sales_amount) as Cum_sales from lala_table group by MONTHNAME(date_col); regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 problem
Now that I notice more closely the numbers, my answer was obviously wrong with regard to the 3rd column :o) Very interesting question...but I doubt there is a SQL way to do that. Looking fwd to what the rest will sugest. cheers, thalis On Fri, 2 Mar 2001, Richard Vibert wrote: Hi, At 01:52 pm 2/03/2001, Thalis A. Kalfigopoulos wrote: On Fri, 2 Mar 2001, Richard Vibert wrote: Hi, I having trouble working out how to get a result set similar to the following where I select from a table with Date Sales column. My specific question is can I have a column that accumulates values, if so could I have some guidance on how to express this in a select statement please. +--+---+---+ | Month| Sales | Cum Sales | +--+---+---+ | Jan | 1000 | 1000 | | Feb | 1500 | 2500 | | Mar | 1200 | 3700 | | April| 1400 | 5100 | +--+---+---+ Many thanks in advance. Richard I assume the query should be like: select MONTHNAME(date_col) as Month,count(sales_amount) as Sales,sum(sales_amount) as Cum_sales from lala_table group by MONTHNAME(date_col); regards, thalis Thanks for your reply. This is not quite what I'm after. Count(sales) gives me the number of sales transactions. The first two cols I write as monthname(date_col) as Month, sum(sales) as Sales It's the next column that's got me. That needs to be a "Running total" if you like. Richard === Richard Vibert [EMAIL PROTECTED] Tatura Mitre10 === - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 problem
On Fri, 2 Mar 2001, Nathan Clemons wrote:
Can't you do something with SUM() to get the results, possibly in
coordination with GROUP BY?
--Nathan
Not to my knowledge/imagination. What are you going to group by? You want and
incremental grouping or better you want a dynamic calculation on a very specific
subset of the rows (0-current_row) while current_row inrcrements through the result.
This cannot be done in a query and probably not in SQL at all.
I can only imagine this as a loop in a higher level language (take your pick:
perl/php/C). I would start thinking of creating a temporary table with
Id,Month,Sum(sales) and from there getting the runnning sales (the Id would range from
1-12).
dummy_code follows:
for(curr_count=1;curr_count=12;curr_count++){
pose_query("select curr_count,sum(sales_per_month) from temp_table where
id=curr_count");
}
Maybe I'm too tired to see straight and the answer is looking at me straight in the
face but that was my $0.02 anyhow.
regards,
thalis
On 2001.03.01 23:49:28 -0500 Thalis A. Kalfigopoulos wrote:
Now that I notice more closely the numbers, my answer was obviously wrong
with regard to the 3rd column :o)
Very interesting question...but I doubt there is a SQL way to do that.
Looking fwd to what the rest will sugest.
cheers,
thalis
On Fri, 2 Mar 2001, Richard Vibert wrote:
Hi,
At 01:52 pm 2/03/2001, Thalis A. Kalfigopoulos wrote:
On Fri, 2 Mar 2001, Richard Vibert wrote:
Hi,
I having trouble working out how to get a result set similar to the
following where I select from a table with Date Sales column.
My specific question is can I have a column that accumulates
values, if so
could I have some guidance on how to express this in a select
statement
please.
+--+---+---+
| Month| Sales | Cum Sales |
+--+---+---+
| Jan | 1000 | 1000 |
| Feb | 1500 | 2500 |
| Mar | 1200 | 3700 |
| April| 1400 | 5100 |
+--+---+---+
Many thanks in advance.
Richard
I assume the query should be like:
select MONTHNAME(date_col) as Month,count(sales_amount) as
Sales,sum(sales_amount) as Cum_sales from lala_table group by
MONTHNAME(date_col);
regards,
thalis
Thanks for your reply.
This is not quite what I'm after. Count(sales) gives me the number of
sales
transactions.
The first two cols I write as monthname(date_col) as Month, sum(sales)
as Sales
It's the next column that's got me. That needs to be a "Running total"
if
you like.
Richard
===
Richard Vibert
[EMAIL PROTECTED]
Tatura Mitre10
===
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Nathan Clemons [EMAIL PROTECTED] 978-635-5300 ext 123
Linux Systems Administrator IRC: etrnl ICQ: 2810688 AIM: StormeRidr
O | S | D | N,50 Nagog Park,Acton,MA01720
http://www.osdn.com/ Open Source Development Network
Nextel: 978-423-0165 [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: Stumped
This should be very simple, but for some reason, my query does not return the desired results I have the following two tables: CREATE TABLE Rank ( Rank CHAR(40) NOT NULL PRIMARY KEY, ReportsTo CHAR(40) NULL ); CREATE TABLE ROSTER ( EntryNumber INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, IDNumber INTEGER NOT NULL, LastName CHAR(30) NOT NULL, FirstName CHAR(30) NOT NULL, DOB Date NULL, DateJoined Date NULL, Rank CHAR(40), NULL CrewName CHAR(30), NULL HomePhone CHAR(20) NULL, WorkPhone CHAR(20) NULL, CellPhone CHAR(20) NULL, PagerNumber CHAR(20) NULL, EMail CHAR(50) NULL, ); I want to select members that are not at the root level for rank (ReportsTo field is not blank), but are also not assigned to a crew (CrewName is blank) And the query SELECT A.*, B.ReportsTo FROM ROSTER A, RANK B WHERE A.Rank = B.Rank AND ReportsTo '' AND CrewName = '' does not return the names of members that I know are not assigned to a crew. What have I done wrong? One would think that this is very straight forward. An educated guess: QUOTE 20.17 Problems with NULL Values The concept of the NULL value is a common source of confusion for newcomers to SQL, who often think that NULL is the same thing as an empty string ''. This is not the case! /QUOTE So it should work as: select a.*,b.reportsto from roster a, rank b where a.rank=b.rank and reportsto IS NOT NULL, and crewname IS NULL; regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Hopefully Easy SQL Join Question
On Thu, 22 Feb 2001, Oson, Chris M. wrote: Good Day All I have two tables... Table 1 --- MSRSectionID ContractID Heading 1 1 107 Technical Progress: 7107 Technical Problems: 13 107 Administrative Actions Pending: 19 107 Problems/Issues: 25 107 Completed Travel: 31 107 Expected Travel: 37 107 Plans For Next Reporting Period: Table 2 --- MSRSectionID Description 1Updates may be made here. Now what I want to do is grab all the Headings from the first table regardless of the number of entries in the second table. So the output of my desired query should look... Technical Progress: -- Updates may be made here. Technical Problems: Administrative Actions Pending: Problems/Issues: Completed Travel: Expected Travel: Plans For Next Reporting Period: The output for the description doesn't need to be indented. I was just trying to explain as well as I can what I'm trying to get. I tried using a outer joins on both tables, but I'm not getting what I want. What you want is a left join (it is outer indeed as you guessed) select if(t2.name is null,t1.name,concat(t1.name,t2.name)) from table1 as t1 left join table2 as t2 on t1.sectionID=t2.sectionID; regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: special characters in MySQL query
If you are using PHP there is the htmlspecialchars() function that will do just that for you. cheers, thalis - Original Message - From: "Robert Heron" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 17, 2001 6:35 PM Subject: special characters in MySQL query I have written a program which reads data from WWW forms and passes it to MySQL queries. All works fine except for the cases when the form is filled with characters like '%', '"', ''', etc. Then, these characters are treated like special, formatting characters in SQL query, which, of course demage the SQL query. I know that this problem can be solved by adding '\' char before each such special character, but users typing texts in the form by WWW will not know that. Is there any simple way in MySQL to solve this problem? Robert - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: special characters messing me up
On Tue, 13 Feb 2001, Peter Skipworth wrote: Or a handy one-liner... perl -pi -e "s/[\012\015]//" *.sql Or alternatively edit the file with vi and do: :s/ // This inteprets to the following keystrokes: ':' colon 's' s for substitute ' ' Ctrl-v-m to create the annoying ^M as one character '//'two slashes regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: special characters messing me up
Or a handy one-liner... perl -pi -e "s/[\012\015]//" *.sql Or alternatively edit the file with vi and do: :s/// This inteprets to the following keystrokes: ':' colon 's' s for substitute ''Ctrl-v-m to create the annoying ^M as one character '//' two slashes Xmmm, seems the listd didn't like my '^M' :-( My mistake. REPEAT: In vi you type: :s/^M// This inteprets to the following keystrokes: ':' colon 's' s for substitute '^M'Ctrl-v-m to create the annoying ^M as one character (the CR) '//'two slashes regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SET question
This is a reply to the SET column UPDATE question (I deleted to e-mail, oups!).
The question was that you have a column of type SET and value ("a,b,c") and you want
to make this ("a,b,c,d")
In the __MySQL MANUAL__ it says that the SET type is actually stored numerically and
in particular "with the low-order bit of the stored value corresponding to the first
set member" and a couple of lines further down "If a number is stored into a SET
column, the bits that are set in the binary representation of the number determine the
set members in the column value"
i.e. if your set has values "a,b,c,d" then:
a = 0001
b = 0010
c = 0100
d = 1000
So if you do an update of the form:
UPDATE table SET set_var=3 WHERE my_cond;
it would actually make set_var=("a,b") for the tuple that my_cond holds since the
ten_Base 3 is the binary 11.
So in your case that you want to add "d" to a tuple that is "a,b,c" you do
UPDATE table SET set_val=15 WHERE my_cond;
since 15 is in binary (the leftmost '1' is the flag that tells mysql to add the
"d" in your set)
does it make any sense?
regards,
thalis
---+
You're definitely on their list.
The correct question to ask is what list it is.
---+
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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 tpc-w
Has anyone tried running the TPC-W benchmarks on MySQL? I'm not talking about making the results official (I assume too much $$$) but just to implement them and see how it goes. cheers, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: ISAM and MYISAM
I'm new too, but a good starting point would be: http://www.mysql.com/doc/T/a/Table_types.html regards, thalis ---+ You're definitely on their list. The correct question to ask is what list it is. ---+ On Sat, 10 Feb 2001, Teddy A Jasin wrote: Hi, I've read some people talking about ISAM and MYISAM table. I dont understand what are those tables and can anyone explain to me and the difference of each table. and also which one has better performance? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Exporting data
Hello, if you want to have a file with just the bare data contents of the table, you simply do: mysql SELECT * FROM table_name INTO OUTFILE '/tmp/lala.txt'; otherwise if you want your data represented in SQL, so that you can reconstruct the table by importing at a later time (a backup form): $ mysqldump database_name table_name and since this gets printed to stdout, you should probably redirect to a file like: $ mysqldump database_name table_name table_name.sql.bak regards, thalis On Sat, 10 Feb 2001, Mike Yuen wrote: How do I dump all the contents of a table called "clients" into a .txt file (or anyother kind of file for that matter). Thanks for your help. 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
