Re: MySQL with perl
I've encountered this several times. When on the web I just use redirect pages. I'll submit the data to a page that puts the information into the database, and then puts a header() call to redirect them to the real finishing page, passing any minor variables I may need. This way also has the unique ability that you can go back and forth in the browser all you want (no back button woes). - Original Message - From: Pete Kuczynski [EMAIL PROTECTED] To: MySQL Email List [EMAIL PROTECTED] Sent: Thursday, June 07, 2001 8:32 AM Subject: Re: MySQL with perl I overcame this by adding to my php script, something that goves the user the results, summerised, of what they added to the database, thus when hitting refresh, it just refreshes the results of what the php script returned, not what they submitted. My php script is below. Pete html body ?php require(fedb.inc); mysql_connect(localhost,$user,$password); @mysql_select_db($db) or die( Unable to select database); /*Insert into database */ mysql_query (INSERT INTO asset (site_id, hostname, device, model, serial, ip, dept, mhz, ram, hd_size, os_ver, status, data_port, pp_port, hub_port, digi_port, csmim_port, tprmim_port, xyplex_port, comments, fe) VALUES ('$site_id', '$hostname', '$device', '$model', '$serial', '$ip', '$dept', '$mhz', '$ram', '$hd_size', '$os_ver', '$status', '$data_port', '$pp_port', '$hub_port', '$digi_port', '$csmim_port', '$tprmim_port', '$xyplex_port', '$comments', '$fe') ); print ($site_id); print ( ); print ($hostname); print ( ); print ($device); print ( ); print ($model); print ( ); print ($serial); print ( ); print ($ip); print ( ); print ($dept); print ( ); print ($mhz); print ( ); print ($ram); print ( ); print ($hd_size); print ( ); print ($os_ver); print ( ); print ($status); print ( ); print ($data_port); print ( ); print ($pp_port); print ( ); print ($hub_port); print ( ); print ($digi_port); print ( ); print ($csmim_port); print ( ); print ($tprmim_port); print ( ); print ($xyplex_port); print ( ); print ($comments); print ( ); print ($fe); print ( ); print (p); print (p); print (Thanks for submitting your device.); ? a href='index.html'bHome/b/a | a href='searchform.html'bSearch Item/b/a | a href='searchform_all.html'bSearch Site/b/a | a href='updateform.html'bUpdate/b/a | a href='deleteform.html'bDelete/b/a /body /html Cindy wrote: Dawn H writes: I've written a couple of scripts and have a thing that I haven't quite figured out. If anyone can shed light on this, I'd appreciate it. When a person submits a form that inserts a record into the database, if they then refresh the page that comes up after the submit, the record will be submitted a second time. I suppose this is due to the fact that the subroutine that is called with the submit contains both the write to database bit and the view bit. So refreshing causes a repeat of both items. How can I do this without that problem? I was finally forced to add a check that there wasn't already an identical record (using a minimum comparison set of fields) before inserting the record. There really isn't any way to prevent dingbats from refreshing, so... I CYA by emailing a copy of the record/element values to myself when I refuse to add it in, but I havne't had any spurious refusals so far. --Cindy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- ___ Pete Kuczynski Principal Field Engineer DHL Airways Inc. Infrastructure Technology Services (773)-462-9758 24/7 Helpdesk 1-800-434-5767 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try:
Re: Select query
When comparing float values, you have to use '' around the value. Your query should look like this: select distinct sequence_id from sequence_protein where mol_wt = '53211.62'; - Original Message - From: Hannes Niedner [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 07, 2001 2:48 PM Subject: Select query What do I do wrong? The query result is not supposed to be an empty set (please cc your response to [EMAIL PROTECTED]) mysql select sequence_id, mol_wt from sequence_protein limit 1; +-+--+ | sequence_id | mol_wt | +-+--+ | 100368 | 53211.62 | +-+--+ 1 row in set (0.02 sec) mysql select distinct sequence_id from sequence_protein - where mol_wt=53211.62; Empty set (0.12 sec) mysql describe sequence_protein; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | sequence_id | int(10) unsigned | | PRI | 0 | | | length | int(10) unsigned | | | 0 | | | mol_wt | float(10,2) | YES | | 0.00| | +-+--+--+-+-+---+ Thank you Hannes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Displaying MySQL database with DATE_FORMAT
I think your doing FAR more work than you need to. I'm also not getting what you mean by it not paying attention to the initial query. I suggest you combine those 3 queries into one, just like this: SELECT somefield1, somefield2, DATE_FORMAT(EventStartDate, '%W %M %D %Y') AS FormattedEventStartDate, TIME_FORMAT(EventStartTime, '%r') AS FormattedEventStartTime FROM comcal2001 WHERE blah - Original Message - From: Tim Thorburn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 01, 2001 12:08 PM Subject: Displaying MySQL database with DATE_FORMAT Hi, I've developed a simple search engine for a site I'm working on. Its purpose is to display community events based on the users input criteria. The problem is, if I use the DATE_FORMAT command to alter the appearance of the events dates and times - then MySQL begins at the top of the list using the exact order of dates and times, rather than listening to the initial query and taking only the relevant dates and times. However, if I leave out the DATE_FORMAT and TIME_FORMAT commands, it works perfectly, just without the nice friendly display. Any thoughts? Here is a sample of my script: if (!$db) { echo Error: Could not connect to database. Please try again later.; exit; } $query = select * from comcal2001 where .$searchtype. like '%.$searchterm.%'; $result = mysql_query($query, $db); $resultdate = mysql_query (SELECT DATE_FORMAT(EventStartDate, '%W %M %D %Y') AS EventStartDate FROM comcal2001); $resulttime = mysql_query (SELECT TIME_FORMAT(EventStartTime, '%r') AS EventStartTime FROM comcal2001); if (!$result) echo Error running query: $querybr.mysql_error($db); else $num_results = mysql_num_rows($result); $num_rows = mysql_numrows($result); echo pNumber of Events found: .$num_rows./p; for ($i=0; $i $num_rows; $i++) { $row = mysql_fetch_array($result); echo pb.($i+1).. Event: ; echo ($row[EventName]); echo /bbrMonth: ; echo ($row[EventMonth]); echo brLocation: ; echo ($row[EventLocation]); echo brCity: ; echo ($row[EventCity]); echo , ; echo ($row[EventState]); echo brTime: ; $rowtime = mysql_fetch_array($resulttime); echo ($row[EventStartTime]); echo brDate: ; $rowdate = mysql_fetch_array($resultdate); echo ($row[EventStartDate]); echo brDescription: ; echo ($row[EventDescription]); echo brCost: ; echo ($row[EventCost]); echo /p; } ? Thanks -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: URGENT !! Hanging query
Please send me the actual physical query that was locking things up. This sounds like a simple join, include table/key structures in your response. More than likely you aren't joining somewhere nicely, and if you don't, you end up with really really large result sets. - Original Message - From: Paul Wilkinson [EMAIL PROTECTED] To: Help MySQL List [EMAIL PROTECTED] Sent: Tuesday, May 29, 2001 1:08 PM Subject: URGENT !! Hanging query PLEASE if anyone can help!! We are past deadline Object: Retrieve rows from table D. GIVEN: 1. MySQL ver 3.23.33 (but must run on 3.22.25!) 2. Table D is connected to tables E,F,K through tables dE,dF,dK. 3. Table D is connected to table L through tables dK then Lk. D - dE - E D - dF - F D - dK - K D - dK - Lk - L 4. All query WHERE clauses use string fields (VAR or VARCHAR) in E, F, K, L 5. Each table E,F,K,L has less than 1000 rows. 6. Each string field is indexed 7. Each joiner field is indexed NEED: 1. WHERE clause which checks exact matches for E,F and L tables and substring match for words in K. 2. Multiple words to limit results to intersection of returns 3. Ordered by number of rows in dE,dF, then dK (both L and K) 4. List only unique rows in D I have tryed several things to create various queries in PHP, some which are as large as 2500 characters! The only thing that I can think of now is Does each AND section of the WHERE clause have to have ALL unique table aliases? (down to the last common one?) Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: What can I do ?
It's all about index's. My current big table is a table which currently has ~500,000 rows. We add around 4000-5000 rows a day. This is simply a table storing session data for visitors. Because of the large amounts of inserts, we keep the index's low, have 1 primary and 2 non unique index's which keeps inserts fast. All the access's of this table are done on the primary key (users session id) keeping the reading fast also. Just be clever with your index's and you should be fine. - Original Message - From: Bruno Faé [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 25, 2001 9:42 AM Subject: What can I do ? Hello, I´m Brazilian and a new subscriber of this list. (sorry my english) I´m working with MySql and with a 100.000 rows table, this table is growing 300 rows in each day. How can I arrange this information to get a good performance of my queries (my SELECT´s) ? We intend to do this: Work with 2 databases, put 10.000 in the one that I have 90% of acces, and put the rest of rows in the orther. Is this a good idea with MySql ? Or there is a better way to do this ? Thank You ! Bruno Fae. _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Compilation problems.
Does your Bug_Report.c have an #include line for the mysql headers? - Original Message - From: Nicu Popovici [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 25, 2001 9:54 AM Subject: Compilation problems. Hello Gurus, I am trying to write a C client for a mysql server and I compile the example with the following line gcc -g Bug_Report.c -o test.o -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient -lnsl -lsocket but I get the following linking errors /home/nicu/QA/Bug_Report.c:7: undefined reference to `mysql_init' /home/nicu/QA/Bug_Report.c:8: undefined reference to `mysql_real_connect' Can anyone point me in the right direction. Thank you, Nicu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: update a column
UPDATE table SET name = REPLACE(name,'blue',''); - Original Message - From: Tiago Mota [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 25, 2001 9:43 AM Subject: update a column Hi, I have a book explanning mysql and the commands, and I was here searching for some kind of a command that do waht I want. But I didn't find anything I have a table named as mytable and two columns (id, name). Like this id name 1 blue dot 2 blue pencil 3 blue pen . And I want to alter all the cell's that have the word blue. I want to remove the word blue from the cells, without edit them 1 by 1. So it will be id name 1 dot 2 pencil 3 pen Thanks, Tiago Mota - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How many records can MySql handle?
Depends on OS, memory, disk space. Theoretically there is no limit. If your using linux, and don't have support for large files built in, then your table size limit is 4GB. Putting a table with 1k rows at a max size of around 4 million records. If you have large file support in, and have the RAM/cpu to support it, you can FAR exceed that. - Original Message - From: Zhu George-CZZ010 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 25, 2001 9:56 AM Subject: How many records can MySql handle? If each record is about 1K byte, how many records can MySql handle? Does anyone have any estimation or hands-on experience? Thank you very much in advance. Regards, George - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 - getting it to start and stay running.
1) Don't use the Redhat RPM's, download the RPM's from mysql.com 2) Check /var/lib/mysql/hostname.err for error reasons 3) Check your config 4) Don't use the Redhat RPM's. - Original Message - From: Tom Walker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 25, 2001 10:37 AM Subject: mysql - getting it to start and stay running. I am trying to start mysqld on a RH7.1 box. The mysql server was installed by the initial RH install routine. I ran the install script to setup the initial databases. I can run either the msql.server or mysqld commands and while the process starts with the /var/lib/mysql database, it just ends. No error, nothing but mysql ended on the screen. I cannot get the process to stick and stay running. Any ideas? Thank You, Tom. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SHOW INDEX FROM table
1) Not sure what's going on there, perhaps run a check on the table. 2) I just recently indexed a column on a table with ~50 rows, on a char(4) field, took roughly 30 seconds. 3) No problems running multiple index's, a tip though, lock the table before you add the index, you can sometimes get a lil more speed that way (though I couldn't do that, was a production table). What kind of hardware you running? - Original Message - From: Paul Wilkinson [EMAIL PROTECTED] To: Help MySQL List [EMAIL PROTECTED] Sent: Thursday, May 24, 2001 1:00 PM Subject: SHOW INDEX FROM table Hi, I have several related database indexing questions: 1)In trying to run SHOW INDEX FROM table my client (MySQL GUI 1.7.4) just hangs, or else this takes many minutes. MySQL 3.23.33 What could be wrong? I tryed an ALTER TABLE index creation on this table a while ago. Could it be tied up with creating an index? 2)How long should it take to index a column (on ~ 800 rows and CHAR(35))? 3) After successfully returning from an ALTER TABLE adding an index on a table (took seconds) the second column ran for ..? (stopped it). Is there a problem running more than 1 index on a table!? Muchas Gracius! Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Results came from
What language are you using to prepare that query? If it's perl, do something like if ($title =~ /Green/) If it's php, do something like if (ereg(Green,$title)) If it's C, cycle the string finding that This is a job for the client truthfully...but if you absolutly MUST do this server side, here is your query: select Title, IF(Title LIKE '%Green','Green','Blue') AS whatmatch - Original Message - From: Carl Schrader [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 21, 2001 10:56 AM Subject: Re: Results came from Is there anyway to determine which part of the an sql query where blahblah produced the result? ie. select Title from inv where Title LIKE '%Green' OR Title Like '%Blue%' I need to distinguish if the Title was like Green or Blue.. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: C API; queries within functions.
Any pointers on where to look? That line says it all really. Your initiallizing connection as a pointer (denoted by *), you have to treat it accordingly when passing it to functions. Your function init would look something like this: MYSQL_RES doquery (MYSQL *connection, char query[100]) { int state; MYSQL_RES *result; state = mysql_query(connection,query); if (state != 0) { printf(mysql_error(connection)); return NULL; } result = mysql_store_result(connection); return result; } then, in your code, you would connect like normal, then call the function like this: MYSQL_RES *result; result = doquery(connection, query); - Original Message - From: Seth Northrop [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 21, 2001 12:25 PM Subject: C API; queries within functions. Hi! This is probably more of a C inadequacy than a MySQL problem. I'm playing around with C with MySQL (I have to date only interfaced with it in PHP) and I was curious if anyone had any example code which illustrates how you would write functions which return mysql data. For example, in PHP I might write a simple function like this: function get_specific_db_column($dbname, $tblname, $pk, $pk_val, $colname) { $sql = SELECT $colname as val FROM $dbname.$tblname WHERE $pk = '$pk_val'; $r = mysql_query($sql); while ($row = mysql_fetch_array($r)) $array[] = $row; return $array; } But, in C I haven't figured out how to do things like navigate passing the MYSQL *mysql init pointer into the function for mysql_query etc. I can however get a program WITHOUT functions (ie, hardcode the query) compiled and working: // Simple test application // for the C Mysql API #include sys/time.h #include stdio.h #include mysql.h #define def_host_name NULL #define def_user_name root #define def_password NULL #define def_db_name reflectivity int main (char **arg) { MYSQL_RES *result; MYSQL_ROW row; MYSQL *connection, mysql; int state; char colsize=10, rowsize=10; // connect to the mysql database on internal mysql_init(mysql); connection = mysql_real_connect(mysql, def_host_name, def_user_name, def_password, def_db_name, 0, /*port defaut*/ NULL, /*socket default*/ 0);/*flag*/ if (connection == NULL) // check for a connection error { // print the error message printf(mysql_error(mysql)); return 1; } state = mysql_query(connection,SELECT * from reflectivity.accounts); if (state != 0) { printf(mysql_error(connection)); return 1; } // you must call mysql_store_result before we can issue anything else result = mysql_store_result(connection); printf(Rows: %d\n, mysql_num_rows(result)); // process each row in the result set while ((row = mysql_fetch_row(result)) != NULL) printf(%s - %s - %s - %s - %s\n,row[0],row[1],row[2],row[3],row[4]); // free some memory mysql_free_result(result); // close the mysql connection mysql_close(connection); printf(Done.\n); } Ny guess is that if I just see a couple of examples which mimic some of the functionality of the above PHP function in C I'll be able to write them without a problem. Any pointers on where to look? Thanks! Seth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: count(*) on different tables
Actually, as far as performance goes, it depends on table types. Most of the MySQL table handlers keep an internal count of record numbers. Thus, a select count(*) from table; is as fast as it gets. - Original Message - From: Siomara Pantarotto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 21, 2001 12:26 PM Subject: Re: count(*) on different tables Well ... I don't know enough about mysql but it must allow you somehow to specify the column by number as in Oracle and other DBs SQL select count(*) from product; COUNT(*) -- 10 SQL select count(1) from product; COUNT(1) -- 10 Once you just want to count the rows the performance of your select will be much better if you specify a column rather than *. Siomara From: Ansgar Becker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: count(*) on different tables Date: Mon, 21 May 2001 20:18:35 +0200 Hi, is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Manual doesn't say anything about that. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count(*) on different tables
Well, let's do a test: mysql CREATE TABLE testcount (product INT NOT NULL AUTO_INCREMENT PRIMARY KEY, description VARCHAR(50)); Query OK, 0 rows affected (0.00 sec) **Ran perl script here to populate the table** mysql SELECT * FROM testcount LIMIT 10; +-+---+ | product | description | +-+---+ | 1 | Decription 1 | | 2 | Decription 2 | | 3 | Decription 3 | | 4 | Decription 4 | | 5 | Decription 5 | | 6 | Decription 6 | | 7 | Decription 7 | | 8 | Decription 8 | | 9 | Decription 9 | | 10 | Decription 10 | +-+---+ 10 rows in set (0.03 sec) mysql SELECT COUNT(*) FROM testcount; +--+ | COUNT(*) | +--+ | 10 | +--+ 1 row in set (0.00 sec) mysql SELECT COUNT(1) FROM testcount; +--+ | COUNT(1) | +--+ | 10 | +--+ 1 row in set (0.00 sec) mysql SELECT COUNT(2) FROM testcount; +--+ | COUNT(2) | +--+ | 10 | +--+ 1 row in set (0.00 sec) mysql SELECT COUNT(*) FROM testcount WHERE product 10; +--+ | COUNT(*) | +--+ |0 | +--+ 1 row in set (0.32 sec) mysql SELECT COUNT(1) FROM testcount WHERE product 10; +--+ | COUNT(1) | +--+ |0 | +--+ 1 row in set (0.32 sec) mysql As you can see, it really makes no difference to MySQL. - Original Message - From: Siomara Pantarotto [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, May 21, 2001 1:48 PM Subject: Re: count(*) on different tables Cool but how about if you put a where clause to your select?? SQL select count(1) from product where productid 3; COUNT(1) -- 7 Would count(*) show the same performance as count(1)??? Siomara From: Eric Fitzgerald [EMAIL PROTECTED] To: Siomara Pantarotto [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: count(*) on different tables Date: Mon, 21 May 2001 13:11:38 -0700 Actually, as far as performance goes, it depends on table types. Most of the MySQL table handlers keep an internal count of record numbers. Thus, a select count(*) from table; is as fast as it gets. - Original Message - From: Siomara Pantarotto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 21, 2001 12:26 PM Subject: Re: count(*) on different tables Well ... I don't know enough about mysql but it must allow you somehow to specify the column by number as in Oracle and other DBs SQL select count(*) from product; COUNT(*) -- 10 SQL select count(1) from product; COUNT(1) -- 10 Once you just want to count the rows the performance of your select will be much better if you specify a column rather than *. Siomara From: Ansgar Becker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: count(*) on different tables Date: Mon, 21 May 2001 20:18:35 +0200 Hi, is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Manual doesn't say anything about that. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http
Re: Require an opinion
MySQL is in fact, as you said, a backend database. Forms, reports, labels, those are all done client side. If you do not want to develop your own client application, I suggest using Mascon (if you have some windows clients) available at: www.scibit.com. I have found it to be a VERY nice client application for windows users. It even has a report designer built in, and I would assume it could do labels just by knowing dimensions :) Search around a bit, there are a ton of MySQL client programmers out there. Just try to find something that does what you want it to do. - Original Message - From: Michael Fakaro [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Michael Fakaro [EMAIL PROTECTED] Sent: Monday, May 14, 2001 9:28 AM Subject: Require an opinion I am new to Linux as well as MySQL. I moved to Linux from OS/2 where I was using R:Base to do my database work. When I inquired about a database for Linux I was overwhelmed with the response MySQL. So I went out and bought a few books and started to study. MySQL is obviously a ready for prime time backend database. (I found out it even runs on OS/2) But!! Is it right for me??, MySQL, database, table, column creation is simple, queries use the same SQL language, I have no problem with these areas. My problems are I need to print reports, forms, labels based on queries from multiple tables and also need to input large amounts of data quickly and manually! R:Base allowed me to create forms for quick data punching and reports for invoicing, packing slips... etc labels. Is this possible with MySQL, if so with what? I'm not looking the details I just need to know before spending weeks studying and working that I have chosen thew right Linux Program. Do I need companion products?? any opinion would be appreciated. Thanks Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Hom many columns is too many?
I would personally suggest they be broken up. I've noticed in the past some speed issues when you have too many columns. - Original Message - From: Richard Reina [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 11, 2001 11:26 AM Subject: Hom many columns is too many? I am designing an in house database app. for sales/contact management. We've identified out about 75 things to know about a customer/contact from there name all the way to where they went to highschool. Should all these attributes be in one table since they all describe the contact or should they be broken up into seperate tables like education, professional backround, business backtound special interests? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 -- tee command ???
It sounds like you might be more interested in the SELECT * INTO OUTFILE syntax...read http://www.mysql.com/doc/S/E/SELECT.html for more information. - Original Message - From: Joseph Chow [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 11, 2001 9:57 AM Subject: MySQL -- tee command ??? Hi, everyone, I have a question about the tee command in MySQL. I know one of its applications is to log everything from the screen to the outfile for debugging purpose. But how do I redirect output ONLY to outfile instead of both standard output (screen) and outfile. Because I donot want to see millions of lines scolled down on the screen when I access to a huge database, but I still want to debug through looking at the content of the db. I wish I make myself clearly. Please reply to me directly, because I am not on the mailing list yet. thanks, Joe Chow - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Update problem, or more likely and problem understanding how to do updates.
You only need to specify SET once. update Pictures set Date_Posted = '2001-05-09', Notes = 'Test' where ID ='1'; - Original Message - From: Barry L. Jeung [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 11, 2001 12:55 PM Subject: Update problem, or more likely and problem understanding how to do updates. Ok, based on the given syntax from the mysql manual.. UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1, [col_name2=expr2, ...] [WHERE where_definition] [ORDER BY ...] [LIMIT #] I've been trying to update some records in a test database I've been playing with. I'm inexperienced with MySQL, so I'm hoping someone out there can enlighten me. When trying to update more than one field at a time, I get the following error: ERROR 1064: You have an error in your SQL syntax near 'set Notes = 'Test' where ID ='1'' at line 1 I was trying to perform the following query to change the data below. mysql select * from Pictures; +++---+--+---+-+ +---++ | ID | Category | Title | Filename | Submitter | Date_Posted | Poster | Notes | Updated| +++---+--+---+-+ +---++ | 1 | Animal Funnies | pic1 | 0001.jpg | Kiwi | 2001-05-08 | WRC| aaa | 0105111248 | | 2 | Animal Funnies | asdfa | 0002.jpg | dsoTm | 2001-05-10 | BLJ| | 0105111036 | | 3 | Animal Funnies | test | 0003.jpg | | 2001-05-10 | BLJ| | 0105102219 | +++---+--+---+-+ +---++ update Pictures set Date_Posted = '2001-05-09', set Notes = 'Test' where ID ='1'; It looks syntatically correct according to the example in the manual, but no matter which combination of fields I try and update, I get that error. Single field updates for all fields works fine. The table is defined as follows: ID = mediumint(8) unsigned NOT NULL auto_increment Category and Poster = enumerated. Title, Filename, Submitter = varchar Date_Posted = date Notes = tinytext Updated = timestamp Please reply directly as I am not on the list. Thanks for your time. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Removing Bad Characters from Database
http://www.mysql.com/doc/S/t/String_functions.html REPLACE(str,from_str,to_str) Returns the string str with all all occurrences of the string from_str replaced by the string to_str: mysql select REPLACE('www.mysql.com', 'w', 'Ww'); - 'WwWwWw.mysql.com' This function is multi-byte safe Your exact form would be something like this (do this in your CODE from now on, this is 2 one time queries to run to fix it historicly) UPDATE table SET field = REPLACE(field,'',''); UPDATE table SET field = REPLACE(field,'',''); - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 10:45 AM Subject: Removing Bad Characters from Database I was wondering if anyone knows of a way i can strip bad characters out of a mysql database field namely the charators. These charactors are causing havok for me when I try and post data to the web. Thanks Alot. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: char - varchar ?
http://www.mysql.com/doc/S/i/Silent_column_changes.html second item, quoted here: If any column in a table has a variable length, the entire row is variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This doesn't affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster. See section 8 MySQL Table Types. - Original Message - From: Alec Solway [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 07, 2001 2:38 PM Subject: char - varchar ? Hi, In various create table statements char columns are converted to varchar.. I was wondering why this was happening. Here's an example: CREATE TABLE realtors(name VARCHAR(255) not null, address VARCHAR(255) not null, city VARCHAR(255) not null, state CHAR(2) not null, country CHAR(2) not null, zip CHAR(5) not null, phone VARCHAR(50) not null, cell VARCHAR(50) not null, fax VARCHAR(50) not null, nvio VARCHAR(25) not null, estlead CHAR(5) not null, servicearea VARCHAR(255) not null); Query OK, 0 rows affected (0.11 sec) Here, zip and estlead have been converted to varchar(5). Thanks. Alec - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Doubt
There are a few options here. First off, when making the query, just put the NOW() statement into the tableit's only a few extra keys to type, and it really isn't that difficult. The second option is using the TIMESTAMP type...take a look, it's probably what your looking for :) - Original Message - From: Sreevatsava Varada [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 02, 2001 8:03 PM Subject: Doubt Hi, I've recently moved from MS-SQL Server 7.0 to mySQL. In MSSQL I used to give the default value 'GetDate()' for a field in the database with the datetime datatype. In mySQL I tried using Now(). But when I insert a new row the datetime field is taking :00:00 00:00:00 value. Please let me know what should I give as the default value for 'record_date' field (of datetime datatype) so that it takes the value returned by Now() function when a new row is inserted using php script. 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: Syntax error
I don't see any ticks surrounding userid...unless of coarse userid is a number?? Also, myResultSet, is that public? If not, are you returning it? (I'm not familiar with Java, so not sure if you need to or not) - Original Message - From: Ryan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 30, 2001 12:17 PM Subject: Syntax error hi everyone in this group, i am getting lately mysql syntax error when i run query from JSP page. here is sample code: public selectMyPass (String userid) { String myQuery = select pass from datainfo where id=; myQuery.concate(myQuery,userid); myResultSet = stmt.executeQuery(myQuery); } when i run that bean from JSP for mysql dataaccess, it gives Syntax error. please help me out. thanks in advance. PS. there not connection problem. i got hooked up with mysql server. i just leave blank in my sample code. - Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
It sounds like you might have some stray ' characters in your text You didn't give much information, but let me see if I can't figure out kinda what your doing... Your using perl, so I assume your going to be using DBI. In DBI you have your statement handles, or do statements. That means your inserts are going to be one of two ways: $dbh-do('query'); or: $sth = $dbh-prepare('query'); $sth-execute(); Whichever way your doing it, use the prepare method, I'll show you why. When you do a prepare, you can put in things called placeholders, so if you needed to insert into table_a 2 values ($header,$body) you would do this: $sth = $dbh-prepare('INSERT INTO table_a VALUES (?,?)'); #Note the ?'s are the placeholders $sth-execute($header,$body); #The arguments passed to execute go into the placeholders What this does is it has DBI automatically escape out special characters, because it knows exactly where they need to go. If neither of these are the case, please give us some more information (the code where your inserting the data etc..) - Original Message - From: Chris Herold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, April 26, 2001 10:26 PM Subject: RE: MySQL question hi-- firstly, I am relatively new to mysql. I am having a problem getting text I enter into a perl script (for reasons of parsing and sending to my mysql database) to consistently get entered into the database. the perl script takes a mail message and makes the body of the message $body. It then pulls the first bit of the body to make the $header... this is then written to the database. All very simple. when i run a simple text body through the program it is picked up, parsed and correctly sent to the mysql database table specified. When I try a number of other texts of same or very similar formats, I found that only a quarter to a half of them get written to the database. this is very confusing because I have it set upin the perl script that all the parsed values ($body and $header) are e-mailed to my address so I can confirm the script worked correctly on the text in question. And in every case it did ... based on the e-mail. So then, why am I getting selective acceptance of data strings into my mysql database? Is this something that others have suffered with that you may be able to help me with? As a note: the mysql I use is run through PhpMyAdmin. thanks, chris herold [EMAIL PROTECTED] 858-534-5875 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Viewing data from the previous row
Actually, there is a way to do this by taking advantage of variables. If you read my previous post on the matter, you can use variables to get a cumulative total. - Original Message - From: Steve Ruby [EMAIL PROTECTED] To: Roger Karnouk [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 27, 2001 12:37 PM Subject: Re: Viewing data from the previous row Roger Karnouk wrote: Is it possible in a Select query to get values from the previously read row in order to do things like cumulative sums or other formulas which require the previous value in order to calculate the current value. ex. day current sales cumu_total - --- 1 120 120 2 60 180 3 125305 4 40 345 I'd like to be able to do this in one select statement is this even possible? Roger Karnouk [EMAIL PROTECTED] You can totalize all numbers but there isn't really any way in sql to do cross-row type functions as you indicated? Why can't you do the running some in whatever program is generating the output? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with Query
Please do a SHOW FIELDS FROM and SHOW KEYS FROM on all tables involved so we can see what's going on here. Also, a warning, if desacription is indexed, by doing LIKE '%GABA%' with wildcard at beginning, it won't use index's. Would also help if you did an EXPLAIN on that query and sent that. - Original Message - From: Bryan Coon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 27, 2001 1:20 PM Subject: Help needed with Query I am working on a mysql database with many large tables (1.5 million rows on some) and came across a beeg problem. I have need for a query like this: mysql select distinct subsnp_pk as sqnmid, c.chrom, chrompos, source as panel, null as first_pcrp, null as second_pcrp, null as prb_seq, null as term, null as validation, null as freq, null as population from locuslink l, subsnp left outer join chromosome_position c on subsnp_pk = c.snp_fk left outer join locus_annotation a on subsnp_pk = a.snp_fk where a.locusid = substring(l.locusid, 4) and description like '%GABA%' order by subsnp_pk; Which seems insane. This 4 table join in another situation would become a 6 table join. As it is, it takes 1min 10.60 seconds for this query to return one result. Clearly I am not a MySQL expert, and even though this query works, there must be a better way to do this. Is it a matter of indexing? A matter of poor schema design? Poor query design? Is it unavoidable? As far as I know, none of the tables are indexed (I did not set up the schema). Please let me know if more info is required to help with this. Thanks, Bryan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with Query UPDATE INFO
Ok, a few things I see...first off, slap some keys into those tables on the join portions. Secondly, upon examining your query a second time, your never joining subsnp and locuslink. I see your trying to use a full join, but you didn't put the criteria for it in the where clause. That would cause some VERY weird join results, and may be your problem. Try adding in the join clause in the where clause. - Original Message - From: Bryan Coon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 27, 2001 2:52 PM Subject: RE: Help needed with Query UPDATE INFO Okay, here is the full monty from the database regarding the query in question, any suggestions on how to improve any of it are greatly appreciated! Also, if any more info is needed, just let me know. Thanks! Bryan QUERY: mysql select distinct subsnp_pk as sqnmid, c.chrom, chrompos, source as panel, null as first_pcrp, null as second_pcrp, null as prb_seq, null as term, null as validation, null as freq, null as population from locuslink l, subsnp left outer join chromosome_position c on subsnp_pk = c.snp_fk left outer join locus_annotation a on subsnp_pk = a.snp_fk where a.locusid = substring(l.locusid, 4) and description like '%GABA%' order by subsnp_pk; EXPLAIN: ++--+---+--+-+--+---+--- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+---+--- --+ | subsnp | ALL | NULL | NULL |NULL | NULL | 1201 | Using temporary; Using filesort | | l | ALL | NULL | NULL |NULL | NULL | 21294 | where used | | c | ALL | NULL | NULL |NULL | NULL | 579 | | | a | ALL | NULL | NULL |NULL | NULL | 704 | where used; Distinct| ++--+---+--+-+--+---+--- --+ 4 rows in set (0.00 sec) DESCRIBE: mysql describe locuslink; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | LOCUSID| varchar(12) | | | | | | SYMBOL | varchar(15) | YES | | NULL| | | INTERIM_SYMBOL | varchar(15) | YES | | NULL| | | MIM_NUMBER | varchar(15) | YES | | NULL| | | CHROM | varchar(6) | YES | | NULL| | | BAND | varchar(20) | YES | | NULL| | | DESCRIPTION| varchar(150) | YES | | NULL| | | SPECIES| varchar(10) | YES | | NULL| | | SOURCESEQ | varchar(15) | YES | | NULL| | ++--+--+-+-+---+ 9 rows in set (0.01 sec) mysql describe subsnp; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | SUBSNP_PK | int(11) | | PRI | 0 | | | SOURCE | varchar(40) | | | | | | SOURCEID | varchar(20) | | | | | | SNPPOSITION| int(11) | | | 0 | | | TOTALSEQLENGTH | int(11) | | | 0 | | | VARIATION | varchar(20) | YES | | NULL| | | NUM_ALLELES| int(11) | YES | | NULL| | ++-+--+-+-+---+ 7 rows in set (0.00 sec) mysql describe chromosome_position; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | SNP_FK | int(11)| | | 0 | | | CHROM| varchar(5) | YES | | NULL| | | CHROMPOS | int(11)| YES | | NULL| | +--++--+-+-+---+ 3 rows in set (0.00 sec) mysql describe locus_annotation; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | SNP_FK | int(11) | | | 0 | | | LOCUS | varchar(20) | YES | | NULL| | | LOCUSID | int(11) | YES | | NULL| | +-+-+--+-+-+---+ 3 rows in set (0.00 sec) mysql KEYS: mysql show keys from locuslink; Empty set (0.00 sec) mysql show keys from subsnp; +++--+--+-+---+- +--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part
Fix for all ODBC users wanting BOOL fields
I sent this to the MyODBC list also, but I know that very few people watch that list. So I'm posting here as well: I have edited the MyODBC source to do a translation of TINYINT fields to BOOL fields. I have tested these thoroughly with Visual Fox Pro, and they work as logical fields, including automatic updates back via views. Because the ZIP of the new MyODBC.DLL is nearly 200k, I am not going to distrubute this as a whole to the list. But I will give anyone that asks for it the zip of the DLL, I will also gladly send them the source with my modifications if they want it. It is critical that you understand that after replacing your MyODBC.DLL with this driver, you will be unable to use TINYINT fields in their native form. All tinyint fields will be converted to BOOL fields when pulled down. Any value 0 in the field will be sent as TRUE, any 0 value will be sent as FALSE. And the translation occurs on the way up, with 1 being TRUE, and 0 being FALSE. Please let me know if you want me to send you this file. Eric Fitzgerald Server Administrator/Programmer/MySQL maintainer/Jack of all trades AMN Television Marketing - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Strange queries
Since MATCH is evaluated AFTER the where, you need to use HAVING SELECT url,newwebsites.description,newwebsites.title,newwebsites.catid,category.ful lname,MATCH newwebsites.description AGAINST ('aliens') as GOO from newwebsites,category LEFT JOIN userrestrictions ON userrestrictions.name REGEXP '[[::]]username|GLOBALAUTHADMIN[[::]]' AND newwebsites.catid=userrestrictions.catid where category.catid=newwebsites.catid AND userrestrictions.catid IS NULL AND category.groupid='1' ORDER BY newwebsites.groupid DESC limit 10 HAVING GOO 0; - Original Message - From: "Brad Barnett" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 09, 2001 1:43 PM Subject: Strange queries SELECT url,newwebsites.description,newwebsites.title,newwebsites.catid,category.ful lname,MATCH newwebsites.description AGAINST ('aliens') as GOO from newwebsites,category LEFT JOIN userrestrictions ON userrestrictions.name REGEXP '[[::]]username|GLOBALAUTHADMIN[[::]]' AND newwebsites.catid=userrestrictions.catid where category.catid=newwebsites.catid AND userrestrictions.catid IS NULL AND category.groupid='1' ORDER BY newwebsites.groupid DESC limit 10; Can anyone tell me why the above query works, and responds with a column labelled "GOO", but if I take the exact same query, and add "AND GOO 0" to it, it complains that there is : ERROR 1054: Unknown column 'GOO' in 'where clause' What gives? If the column GOO exists when MYSQL does an output from that query, why can't I address it in the query? Thanks for any help anyone may have! Quoting Andrew Schmidt [EMAIL PROTECTED]: Have you tried this under a 'stable' version of freebsd? In FreeBSD 4.2 beta, mysql would crash with user locks. Not mysql's fault. now, I understand RC's are generally stable; but I would still make sure that this bug doesn't show up in a stable os. regards, -- Andrew - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 09, 2001 3:34 PM Subject: mysql uses 99% cpu under freebsd 4.3 Description: mysql uses 99% cpu and becomes extremely unresponsive under high load How-To-Repeat: send about 300 simultaneous visitors to www.chicagobusiness.com and tell them to click around. Fix: restart mysql. this usally helps, at least for a few minutes. sometimes it climbs back to 99% though. Submitter-Id: submitter ID Originator: Jon Nathan Organization: Chaffee Interactive MySQL support: extended email support Synopsis: mysql uses 99% cpu and becomes unresponsive Severity: serious Priority: high Category: mysql Class: support Release: mysql-3.23.36 (Source distribution) from freebsd ports Environment: System: FreeBSD d1.crain.com 4.3-RC FreeBSD 4.3-RC #1: Tue Apr 3 16:17:52 GMT 2001 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/CHAFFEE i386 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.2 19991024 (release) Compilation info: CC='cc' CFLAGS='-O2 -pipe -mpentiumpro ' CXX='c++' CXXFLAGS='-O2 -pipe -mpentiumpro -felide-constructors -fno-rtti -fno-except ions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1170734 Apr 3 12:24 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 Apr 3 12:24 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 559764 Apr 3 12:24 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/data/db --without-perl --without-debug --witho ut-readline --without-bench --with-mit-threads=no --with-libwrap --with-low- memory --enable-assembler --with-berkeley-db --with-charset=latin1 --prefix= /usr/local i386--freebsd4.3 Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 --- Dynamic Hosting HTTP://www.L8R.net/ "We Provide Static Hostnames for Dynamic IP's" - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/
Signal to Noise Ratio...
This is an open plea to the MySQL team. The amount of spam and other noise on the list has been growing more and more every day. I would like to make a couple basic plea's. The first, is that you require a poster to the list have an actual account on the list, that should help cut out some of the general spam. Second, perhaps setup some basic moderation, I'll help if need be :) I think the last thing we all need is more spam coming down our pipes, so if anything can be done to help prevent this, it would make everyone's lives easier...except of coarse the low life scum that get's a rise out of farming a list for E-Mails or even sending spam to the list. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Signal to Noise Ratio...
I can understand the concern of longer delays in moderation. My problem with anti spam filters is simply because they are too much like anti-virus filters. TOO many false positives. - Original Message - From: "Steve Werby" [EMAIL PROTECTED] To: "Eric Fitzgerald" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, April 04, 2001 12:12 PM Subject: Re: Signal to Noise Ratio... "Eric Fitzgerald" [EMAIL PROTECTED] wrote: The amount of spam and other noise on the list has been growing more and more every day. Agreed. Same thing's happening with my own email accounts. Second, perhaps setup some basic moderation, I'll help if need be :) I think most people asking questions on the list would rather have some spam slip through than have a thread of a few rapid fire posts take hours in the future because of moderation delays. Maybe implementation of a procmail spam filter like the extensive set of procmail recipes put together by http://www.spambouncer.org/ will cut down on the spam. Just my 2 cents. -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.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: Signal to Noise Ratio...
I actually wouldn't have a problem moderating the false positives and approving them. Coarse, this is all just theoretical untill we get some kind of official word from the MySQL team (You guys listening there?) :) - Original Message - From: "Steve Werby" [EMAIL PROTECTED] To: "Eric Fitzgerald" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, April 04, 2001 1:29 PM Subject: Re: Signal to Noise Ratio... "Eric Fitzgerald" [EMAIL PROTECTED] wrote: I can understand the concern of longer delays in moderation. My problem with anti spam filters is simply because they are too much like anti-virus filters. TOO many false positives. Since you were volunteering...why not volunteer to moderate the false positives and approve them if appropriate? Seriously though, the procmail rules I mentioned have an optional facility that notifies senders of false positives and requires them to re-post the message with a supplied password to bypass the filter the second time. It's something to consider since this is a problem with no perfect solution. -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.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: lots of clients
Depends on your hardware... 500 machines of logging, how many entries per second? I've seen people report their MySQL installs doing hundreds or thousands of queries per second, and not even phasing the machine. - Original Message - From: "Florin Andrei" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 03, 2001 4:35 PM Subject: lots of clients What is the maximum number of connections MySQL can handle? I would like to use MySQL as a logging server, for some 500 machines, but i'm not sure it is able to handle so many simultaneous connections (the clients will do only INSERT or INSERT DELAYED). -- Florin Andrei - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: lots of clients
In that case I refer you to the archives: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:70079:hkagoolipmedibdijphh That message mentions that in his normal operation he is handling 1631 queries per second without even phasing the machine. He also mentions that he got the machine digesting over 6000 inserts per second in a benchmark he ran...this with the perl+DBI scripts running those inserts on the same machine. Now, Wesley has a Quad Xeon 500, 512kB cache with 3GB of memory. All with RAID, and a lot of cache. As I said, the load MySQL can handle is almost 100% dependant on your hardware. I'm sure the MySQL software has some kind of roof somewhere, but where it's at I have no idea. - Original Message - From: "Florin Andrei" [EMAIL PROTECTED] To: "Eric Fitzgerald" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, April 03, 2001 5:04 PM Subject: Re: lots of clients On 03 Apr 2001 16:49:36 -0700, Eric Fitzgerald wrote: Depends on your hardware... 500 machines of logging, how many entries per second? I'm not sure, but i believe most of those machines will not send more than a couple of events per second. Some of them (but not too many) will surely send more. I've seen people report their MySQL installs doing hundreds or thousands of queries per second, and not even phasing the machine. Ok, this sounds encouraging. ;-) -- Florin Andrei - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: A complete newbie here (long post)
Make sure that /tmp is writable by the user running MySQL. You say you are setting MySQL up on your account, do you have access to the root account on this box? If not, are you sure that the box your on doesn't already have MySQL running? - Original Message - From: "Tatti" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, March 31, 2001 7:32 AM Subject: A complete newbie here (long post) I have spent the past 24 hours trying to install and configure MySql on my account at my ISP. Every try failes. I have three different instructions and none works. Installing is no problem. What I can't do is change password to one I want or even run the server without changing the dafault password. I cant even log in with default pwd. Below is a cutpaste of a mailings between me and someone who tried helping me. See if you can figure it out. start MySql: bin/safe_mysqld Gave the following result: bin/mysqld: Fatal error: Can't find messagefile '/usr/local/mysql/share/mysql/english/errmsg.sys bin/mysql Same error message as above Later: bin/mysql And that gave: ERROR 1045: Access denied for user 'stage1@localhost' (Using Password: NO) Yet later: start MySql: bin/safe_mysqld Now I got: Starting mysql daemon with databases from /home/stage1/.www/mysql-x.xx.xxetcetcetc/data rm: cannot unlink '/tmp/mysql.sock':operation not permitted 010331 13:57:50 mysqld ended Full instructions I got from my friend was the following: As root, untar into /usr/local: tar xvfz mysql-xx.xx.xx.tar.gz create a symlink to a mysql directory: ln -s mysql-xx.xx.xx mysql cd to mysql and prep the database: cd mysql scripts/mysql_install_db start MySql: bin/safe_mysqld now connect to it: bin/mysql (no parameters!) And you're there. Well, I followed his instructions and they worked as far as what I pasted above. My plan is to phpnuke the entire site and therefore I need the MySQL. To add to it all I have absolutely no knowledge of either Linux or Unix. But I intend to learn. Thanks in advance for any help I get. Cheers Tatti - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Delete?
Here's how I would do it: $inlist .= sprintf("\'%s\'",$Array['Email']); Then after the loop: $query = "DELETE FROM sometable WHERE email NOT IN (".$inlist.")"; mysql_query($query); - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 29, 2001 5:02 PM Subject: Delete? Hi, I`m just trying to figure out the best method to delete my records and wondered if it was possible to do a DELETE statement to remove any records that don`t match. Here is my code so far $Query="select blah its huge:-)"; $RESULT=mysql_query($Query); $my_rows=mysql_num_rows($RESULT); for ($a=0; $a$my_rows; $a++) { mysql_data_seek($RESULT, $a); $Array = mysql_fetch_array($RESULT); printf("%s", $Array['Email']); } My Query returns Matched Records, what I would like to achieve is deletion of the records that didn`t match, would it be possible to do that under the printf statement. So delete all the records in the table that don`t match up to $Array['Email'] ?? Thanks for any input Ade - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Is it possible to use an optional field?
Your query converted to using left joins: SELECT DISTINCT Questions.QuestionID, Questions.Question, Experts.ExpertFullName, Category.CategoryName, Questions.Shows, Questions.Views FROM Questions LEFT JOIN Experts ON (Questions.ExpertID = Experts.ExpertID) LEFT JOIN Category ON (Questions.CategoryID = Category.CategoryID) WHERE Questions.OnHold = 2 GROUP BY Questions.QuestionID That will grab each record in the Questions table, and return data in Experts and Categories *IF* it exists, otherwise nulls. - Original Message - From: "Roy Nasser" [EMAIL PROTECTED] To: "'nigel wood'" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 28, 2001 3:18 PM Subject: RE: Is it possible to use an "optional field"? I took a look at the manual, but I am a bit unsure of how this would be done in a multi-table wuery, such as the one below... if anyone can help, greatly appreciated! :) SELECT DISTINCT Questions.QuestionID, Questions.Question, Experts.ExpertFullName, Category.CategoryName, Questions.Shows, Questions.Views FROM Questions, Experts, Category WHERE (Questions.ClientID='15') AND (Questions.ExpertID = Experts.ExpertID) AND (Questions.CategoryID = Category.CategoryID) AND Questions.OnHold=2 GROUP BY Questions.QuestionID; Thank you for all help! As usual, please Reply All! :) -Original Message- From: nigel wood [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 28, 2001 8:16 PM To: Roy Nasser Subject: Re: Is it possible to use an "optional field"? On Wed, 28 Mar 2001, you wrote: Hi, I have two joined tables (three actually, but for an example, 2 is OK)... example: Names / Function Then in names I have NameID, Name, and FunctionID, and in Function table, I havce FunctionID, and FunctionName... therefore, I need to find the Function Name of a Person, through their name... - simple engouh until this point... SELECT Names.NameID, Names.Name, Names.FunctionID, Functions.FunctionName WHERE (Functions.FunctionID = Names.FunctionID)... However, in some cases, Names.FunctionID might be empty, or not match anything in Functions table, and in this case I want the Functions.FunctionName to show up as null, or empty... I have been told that in MS Sql, the query would be something like the above, with the where like this: WHERE (Names.FunctionID=*Functions.FunctionID) This did not work in MySQL 3.23.30... Can you help? Thanks! PS: Please reply directly to me... (Reply All)... Thanks! :) Look up LEFT JOIN in the manual get a copy of PaulBois's 'MySQL' book Nigel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: RTFM (was: *sigh* Re: Is it a bug? )
The problem many of us have is not the questions. If I didn't want to answer questions when I have free time, I wouldn't be subscribed to this list. The problem most of us have is with the people who flat out refuse to read the manual or try to find answers on their own. IMHO part of the learning process is learning what sources you can turn to for information. You have to remember, all of our time is worth something, and we are giving to the community to try and support people. Those people that refuse to check the manual are not being respectful of our time. - Original Message - From: "Rick Emery" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 26, 2001 12:20 PM Subject: RE: RTFM (was: *sigh* Re: Is it a bug? ) This list should be open to ALL questions, newbie and non-newbie. If we start filtering, or slamming, then folks will be hesitant to ask questions. We should encourage the learning process, not discourage. And where better to learn than from this list? just my 2 one-hundreths of a dollar -Original Message- From: Oson, Chris M. [mailto:[EMAIL PROTECTED]] Sent: Monday, March 26, 2001 11:53 AM To: Cal Evans; [EMAIL PROTECTED] Subject: RE: RTFM (was: *sigh* Re: Is it a bug? ) Cal, satire 1 I would love to see this list split into 2 lists. MySQL-Newbies and MySQL-non-newbies. Non-newbies would be either an invitation-only list or you have to pass a test to get in. (The test would contain questions that are all in the manual, we don't care if you know, we care if you will bother to look-up!) /satire 1 satire 2 One of the requirements of being on MySQL-non-newbies, other than the RTFM thing, would be that you have to help at least 1 newbie a day. If you go 5 days without getting a gold star from a newbie then you get kicked form the non-newbies club. /satire 2 Ok, so I'm being silly. Oh really? Which part? satire 1 or satire 2? When I was learning ASP, Charles Carroll runs a message list similar to this one but categorized by certain groups. ASPFreeForAll - Post anything here... ASPNotNewbie - People who ask newbie questions on this list get *slammed* as they should. I'd like to see something similar here. Any other thoughts??? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: alter table destroys symbolic links!
I'm gonna take a crack at this. The type keyword in the alter table command is there to convert databases from one type to another. While yes, I do think that the MySQL team could check if the types are the same and not recreate the table if it isn't necessary. I don't consider it a bug that they don't. If you want to prevent this from happening, I suggest you have an entire database on the other partition/drive with the symlink. Then the only way your tables won't exist over on the second drive is if you drop the database. - Original Message - From: "Christian Hammers" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 22, 2001 11:51 AM Subject: BUG: alter table destroys symbolic links! Hello If table xyz is a symbolic link (recommended in the manual for very big files that do not fit on a single partition) then "ALTER TABLE xyz TYPE=ISAM;" ignores this, removes the link and recreate the table on the data directory. I consider it a bug, as it is a) not what the user expects, even repair, optimise and other alter table functions do not work like this b) a user that has no shell account has no other way to alter the table type c) this can be used as a denial of service attack as mysql hangs when getting a "no space left" error message and this is a way to more around the system administrators disk-quota settings! This was tested with the latest MySQL-3.23.35 on linux (ext2 reiserfs, although this shouldn't matter in for the symlink handling) bye, -christian- -- Christian HammersWESTEND GmbH - Aachen und Dueren Tel 0241/701333-0 [EMAIL PROTECTED] Internet Security for ProfessionalsFax 0241/911879 WESTEND ist CISCO Systems Partner - Premium Certified - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Wildcard in Load Data?
I'm not 100% familiar with the load data line, I've never had to do it (I just write import proggies :) I'd suggest a small little script like this (copy and paste this into a text file on the server, give it execute permissions, and run it), please excuse any wrapping: #!/bin/sh for file in *.txt; do if [ -f $file]; then mysql --user=someuser --password=somepassword --execute="LOAD DATA INFILE '$file' INTO TABLE db.table2 FIELDS TERMINATED BY '\t'; fi; done - Original Message - From: "Marcus Ouimet" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 10:42 AM Subject: Wildcard in Load Data? I am trying to load data from several lists. This is what I was trying: LOAD DATA INFILE '*.*' INTO TABLE table2 FIELDS TERMINATED BY '\t'; For a single file I would have used: LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY '\t'; Any ideas? I have several files that all change names so it would be easiest just to have all the files in the dir loaded. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 and XML?
Well list, I think we have a new weekly dead horse to with which to use our sticks on :) To answer your question, no, XML is not being added currently to MySQL. Flat files have some advantages, but have to be implented well to work well, you can't just open the whole thing and scan through it looking for what you need. MySQL is fast, very fast. If your current implentation is having no speed issues, then don't fix what isn't broken. If you are having speed issues, I would suggest you give us some examples of your table layouts so that we can see if maybe you have some innefecient key usage. - Original Message - From: "Bryan Coon" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 2:23 PM Subject: MySQL and XML? Can anyone offer some insight into the following topic? I would like to provide web access to search a large database of information. Currently, I am using MySQL to do this, and it works great. However, this database is almost entirely static, there are virtually no inserts or updates done. For accessing information with this structure, is an RDBM the most efficient way to search through this information? I know Oracle has been modified to use XML files as the database, making queries directly to a flat file (although I have not seen it in action). Does MySQL support anything of this nature, or is anyone working on any module along these lines? What is the advantage for using a flat file? I am fairly new to this kind of thing, I would like to learn more. 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 and XML?
The "dead horse" I'm referring to is XML, it's come up quite a few times :) The major advantage to flat files is that you don't have the MySQL overhead. However, you have to remember a few things. You will have to implement a key structure similiar to MySQL's to get the performance your getting now. This isn't something that's just quick and easy. The need of a key comes in when you have large text files. Opening and scanning through the entire contents of a 5 meg text file can take some time, you need to have a smaller key file that can let you point to a specific offset in the file using a search criteria so that you can open the file to that point. The development time it would take to implement this is rather large. Another key advantage to flat files is the 100% freedom in how you use them. The drawback to this advantage is that nearly anything you want to do with the flat files has to be 100% developed by the developer. And it's no simple task. Hope some of this information can help you out. I'm sure there are other advantages/disadvantages to using flat files that others will be able to point out. Those are just the two biggies that were on the top of my head. - Original Message - From: "Bryan Coon" [EMAIL PROTECTED] To: "'Eric Fitzgerald'" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 4:48 PM Subject: RE: MySQL and XML? What are the advantages of flat files? I am having no speed issues at all... in fact Im quite happy to stay with MySQL. But my boss is loaded with ideas you see, so I have to be able to justify why mysql is better than flat files. Thanks for the response, feel free to kick the dead horse one more time :) Bryan -Original Message- From: Eric Fitzgerald [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 21, 2001 4:43 PM To: Bryan Coon; [EMAIL PROTECTED] Subject: Re: MySQL and XML? Well list, I think we have a new weekly dead horse to with which to use our sticks on :) To answer your question, no, XML is not being added currently to MySQL. Flat files have some advantages, but have to be implented well to work well, you can't just open the whole thing and scan through it looking for what you need. MySQL is fast, very fast. If your current implentation is having no speed issues, then don't fix what isn't broken. If you are having speed issues, I would suggest you give us some examples of your table layouts so that we can see if maybe you have some innefecient key usage. - Original Message - From: "Bryan Coon" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 2:23 PM Subject: MySQL and XML? Can anyone offer some insight into the following topic? I would like to provide web access to search a large database of information. Currently, I am using MySQL to do this, and it works great. However, this database is almost entirely static, there are virtually no inserts or updates done. For accessing information with this structure, is an RDBM the most efficient way to search through this information? I know Oracle has been modified to use XML files as the database, making queries directly to a flat file (although I have not seen it in action). Does MySQL support anything of this nature, or is anyone working on any module along these lines? What is the advantage for using a flat file? I am fairly new to this kind of thing, I would like to learn more. 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 db in separate directory
Databases are essentially just directories. What you do is create a directory in the users home directory. Then, do this: ln -s /path/to/home/dbdir /path/to/mysql/data/dir/somedbname Then grant the user access to that db. Your all set :) - Original Message - From: "Andre Guerrero" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 4:06 PM Subject: create db in separate directory I'm working on a web-hosting site where we'll be giving users a MySQL database. We wanted to store the database in the user's own directory, where we'll be enforcing size quotas. So far though, I haven't been able to find a way to create a database in a directory other than MySQL/data. I've seen a couple of references to symbolic links but am not sure if this is the answer. Can anyone provide me an answer/way to create a database in a different directory? Any examples would be greatly appreciated. So far I'm using a PERL script to create the database - if anyone knows how to incorporate creating this database into the following script that would be very cool. Thanks, Andr use DBI; $database = "AndreTest2"; $host = "dvdba"; $user = "root"; $password = ""; $dbh = DBI-connect("dbi:mysql:dataverse", "root", ""); $rc = $dbh-func('createdb', $database, $host, $user, $password, 'admin'); $dbh-disconnect; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to have a table very very big?
I saw that someone already gave you some suggestions, but the reply seemed to lack a bit of WHY you have that limitation, so I thought I would explain. The 2Gb limitation is not in MySQL. It's in your operating system. If you read some of your OS's docs, you can see how to get around this problem either by installing a filesystem patch or by switching to a different file system. You may not even HAVE this limitation, again the only way to really know is to check with your OS's docs. - Original Message - From: "Mario Latens" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 15, 2001 12:49 AM Subject: How to have a table very very big? I have to store data for Internet desining (10-20 Gb at Day) so I have to use a data base very fast: MySql is fast? I have to put 10-20 Gb of data every day: but I can have a table only of 2Gb (I know that reading manual of Mysql). How to do a table so big? Thank you in advance, Rino. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Little extra info
Just realized, the query I sent you will remove ALL instances of \ inside the field you specify, if that isn't your intent, and you only want to remove \' then do this: UPDATE tablename SET fieldname = REPLACE(fieldname,'\\\'','\''); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: apostrophe ....
Two things here. First off, we had this problem spring up on us, and I think I may know why it's doing it to you. If your using PHP, the more recent version automatically escape out variables sent to the mysql driver. Thus randy's is converted to randy's. HOWEVER, if you still have old code where you ran things through addslashes, it turns out like to look like randy\\\'s. Which ends up being stored as randy\'s. To correct this, first fix your code, then run the following query: UPDATE table SET fieldname = REPLACE(fieldname,'\\',''); - Original Message - From: "Randy Johnson" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 13, 2001 9:54 AM Subject: apostrophe When a text with an apostrophe in it has a \' in it. How can I fix this? example randy's is stored in the DB as randy\'s thanks Randy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: XML
Please drop the stick and back away from the dead horse, it has been beaten enough. :) - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 13, 2001 12:35 PM Subject: XML I've done some rudimentary snooping around on both MySQL's site and on google, trying to find out what status (if there is a status) on MySQL and XML support. If anyone could point me in the right direction, I'd really appreciate it. I'd like to stay away from Oracle if I can manage, but it's XML support is so very tempting. Thanks. Jeff Levy Software Design Meta-Craft Creations - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Scale/load for mysql
Well, really it matters on hardware Some OS's have limits to file sizes, check your OS documentation on that. Other than that, it's all a matter of how much hardware you put behind mysql. - Original Message - From: "Jason Hurst" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 13, 2001 4:15 PM Subject: Scale/load for mysql Ok i know I've seen this somewhere but i can't find the specifications on how much mysql can handle. I'm going to be developing a program for my company which may require a rather large amount of records and i just need to know the maximum capacity of mysql. Thanks for pointing me in the write direction. Sorry if its something easy to find and i just overlooked it somehow. Thanks again for the help. -jason JAPH Webmaster Columbia Ultimate - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: What's the syntax for using ENCODE()?
Your not closing off enough )'s should look like this: insert into payment (payname1,ccaccount) values ('Kinney',encode('','Ilwfci')); Then, for decode, you do it like this: SELECT DECODE(ccaccount,'Ilwfci') AS ccnumber FROM payment WHERE payname = 'Kinney'; - Original Message - From: "Kinney Baughman" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 28, 2001 3:18 PM Subject: What's the syntax for using ENCODE()? Hi, I'm trying to encrypt credit_card numbers in MySQL 3.23.33. I want to decrypt them when I retrieve them. I've tried: mysql insert into payment (payname1,ccaccount) values ("Kinney",encode('','Ilwfci'); ERROR 1064: You have an error in your SQL syntax near '' at line 1 mysql insert into payment (payname1,ccaccount) values ("Kinney",encode(,Ilwfci); ERROR 1064: You have an error in your SQL syntax near 'Ilwfci)' at line 1 mysql insert into payment (payname1,ccaccount) values ("Kinney",ENCODE('',Ilwfci); ERROR 1064: You have an error in your SQL syntax near 'Ilwfci)' at line 1 mysql insert into payment (payname1,ccaccount) values ("Kinney",encode(,'Ilwfci'); ERROR 1064: You have an error in your SQL syntax near '' at line 1 I have "ccaccount" set up as a blob. As you can see, all I get is error messages. What am I doing wrong? After I get them in, my next question is how to get them back out. According to the manual, one should use: DECODE(crypt_str,pass_str) where "crypt_str" should be a string returned from ENCODE(). This seems to be a catch-22, though. Wouldn't I have to know the value of "crypt_str", i.e. the credit_card number in order to get that encrypted string out of ENCODE()? Thanks. -- +---+ | Kinney Baughman Appalachian State University | | Appalachian Regional Development InstituteBoone, NC 28608 | | Email: [EMAIL PROTECTED] Phone: (828) 262-6523 | +---+ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Multiple Column list index
depends fully on how you query the data out. If your going to be using only one field at a time, make seperate indexes. If your going to be ALWAYS using all 3 fields, then I don't see how a single combined index would be a problem. - Original Message - From: "MikemickaloBlezien" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 27, 2001 10:35 AM Subject: Multiple Column list index Hello all, I have a table that stores business locations, which contains 'city','state' and 'zip' and this table is utilize for search queries. Which type of indicing take full advantage of the MySQL optimizer, create a multiple column index (city,state,zip) or separate indices on each column. Thanks, Mickalo Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Why Doesn't This Work???
Please read the documentation on date functions. That looks like one of your problems. Secondly, your not using any '' to encase your strings. - Original Message - From: "Ben Ocean" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 23, 2001 11:12 AM Subject: Why Doesn't This Work??? Hi; I insert the following code in a file: $count = sql_command("select count(id) from calendar"); $count = $count[0] + 1; $mydate = $month.','.$day.','.$year; echo $mydate; echo "br"; echo $time; echo "br"; echo $name; echo "br"; sql_query("insert into calendar (id, date, time, duration, name) values ($count, $mydate, $time, 'one hour', $name)"); echo $mydate; echo "br"; echo $time; echo "br"; echo $name; echo "br"; echo $mydate; echo "br"; echo $time; echo "br"; echo $name; echo "br"; and get the following result: February,26,2001 11:30 Ben_Ocean February,26,2001 11:30 Ben_Ocean So all the variables are passing. Yet the mysql table isn't updated. *However*, if I substitute this line for the above: sql_query("insert into calendar (id, date, time, duration, name) values ($count, $count, $count, 'one hour', $count)"); it enters a new column in the table! Why?! TIA, BenO - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Perl DBI
Connect to the mysql database if you have access. It always exists. - Original Message - From: "John Tsangaris" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 16, 2001 1:58 PM Subject: Perl DBI Is it possible to connect to mysql without connecting directly to a database, check to see if a particular database exists, and if it doesn't exist create it? I have not been able to find a way of connecting to mysql without having a database already (I want perl to be able to make the db.. not have it already made before hand.) tia John - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI
Here is what I would do. There is always a database on every mysql installation called "mysql" This database is where your permissions tables and such are stored. Connect initially to that database. Then, try to create the new database. If you get an error back, prompt the user, rinse and repeat. Your connection string should look something like this: $dbh = DBI-connect('DBI:mysql:mysql;host=somehost','someuser','somepassword'); Of coarse, you could always connect to the mysql database and get a list of databases, and not have to expect an error back from mysql on connection... - Original Message - From: "John Tsangaris" [EMAIL PROTECTED] To: "Eric Fitzgerald" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, February 16, 2001 4:46 PM Subject: RE: Perl DBI Hi, Eric. What I'm trying to do is have my install program check to see if a database of a certain name exists, if it does then the install needs to create another one (using a name given by the user), if not then it needs to create it with the original name. But I cannot figure out how to connect to mysql without already having a database setup. I'll use dbtemp as an example db. I can try to connect to dbtemp and if it successful, I know it exists. So I prompt the user to enter another db name. I can then check if this exists and if it doesn't I can use the dbi to create the new db WHILE I am still connected to dbtemp. If dbtemp does not exist I need to create it. The problem is dbi needs to connect to a database even to create another one. So what/how do I connect to initially to create that first database? I realize, for geeks (myself included), that you could simply create the db manually and then install the program.. but the program is to be usable by the layperson.. which means that there is a chance they don't know anything about mysql other than the guy on tech support said it's installed on the server. How do I connect to mysql without connecting to a database? John -Original Message- From: Eric Fitzgerald [mailto:[EMAIL PROTECTED]] Sent: Friday, February 16, 2001 1:21 PM To: John Tsangaris; [EMAIL PROTECTED] Subject: Re: Perl DBI Connect to the mysql database if you have access. It always exists. - Original Message - From: "John Tsangaris" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 16, 2001 1:58 PM Subject: Perl DBI Is it possible to connect to mysql without connecting directly to a database, check to see if a particular database exists, and if it doesn't exist create it? I have not been able to find a way of connecting to mysql without having a database already (I want perl to be able to make the db.. not have it already made before hand.) tia John - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: error in running mysql - this is after vigorous searching that im sending this email
I can see your command line problem right now... ./mysqladmin is what you need to do, or add the mysql/bin dir to your path - Original Message - From: srihari suthamally To: [EMAIL PROTECTED] Sent: Wednesday, February 14, 2001 4:36 PM Subject: error in running mysql - this is after vigorous searching that im sending this email Hi i have a dell pentium 4 computer i have both windows 2000 and linux-mandrake installed in my computer iits a dimension 8100 system I am trying to run mysql in my linux machine i have mandrake version 7.1 installed for my linux machine for the version and name of linux this is what i got by executing the command [root@localhost mysql]# uname -a Linux localhost.localdomain 2.2.15-4mdk #1 Wed May 10 15:31:30 CEST 2000 i?86 unknown i tried to install mysql-3.23.33-unknown-linux-gnu-alphaev6 version of mysql the installation went on fine but im not able to run mysql or mysqladmin of safe_mysqld my linux box is not connected to the internet this is what i get when running mysqladmin [root@localhost mysql]# cd bin [root@localhost bin]# ls comp_err* mysql_convert_table_format* mysqlbinlog*mysqlimport* isamchk*mysql_find_rows* mysqlbug* mysqlshow* msql2mysql* mysql_fix_privilege_tables* mysqld* mysqltest* my_print_defaults* mysql_setpermission* mysqld.sym.gz* pack_isam* myisamchk* mysql_zap* mysqld_multi* perror* myisampack* mysqlaccess* mysqldump* replace* mysql* mysqlaccess.conf*mysqldumpslow* resolveip* mysql_config* mysqladmin* mysqlhotcopy* safe_mysqld* [root@localhost bin]# mysqladmin bash: mysqladmin: command not found im attaching the error file when i tried to run safe_mysqld i tried to search for the error in all possible ways but im not able to find it out i would greatly appreciate any help with regards srihari - srihari suthamally [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: Load question
Saying your up to 768 courses doesn't give us much information. How many queries/second is the server handling right now? My machine, which is considerably slower than yours handles 10 queries/second without blinking an eye. When we were experiences an extreme slow down before, we found three issues. One was that we didn't have enough RAM (1 Gig of RAM should be fine). The second thing was that our configuration was not optimized for our system in the least (you HAVE tweaked /etc/my.cnf right?). And the last was that we had 1 bad query. To give you a feel for what 1 single badly written query can do to your performance. When we had this problem, we were doing on average 2 queries/second. 1 out of every 200 or so queries would be the one single bad one. But it turned our load average from the 0.00 - 0.06 range to the 20 - 50 range. Just proof that you really need to see if a single query is to blame. Use a util like MyTop to watch the queries as they happen. If you see one appearing more often than others, take a look at that query. There might be a better way to write it (or use keys more effeciently). - Original Message - From: "Jesse Santana" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 09, 2001 9:08 AM Subject: Load question We are currently running Blackboard 5 level 1 (an online course management package) which utilizes MySQL v3.22.32. Our previous semesters saw a course load of approximately 85 courses under which, MySQL performed extremely well. This semester, we have skyrocketed to 768 courses. Now, our server (a Sun Enterprise 450 with 2 400MHz Sparc's, 1 GB of RAM, and 30GB of storage in a RAID 5 configuration) is performing extremely slow. Using top to monitor system performance, we are regularly seeing our load factor rise to double digits and MySQL run away with up to 98% (peak) of the CPU time. Blackboard's response to this is that MySQL does not scale upwards very well and, to fix our problem, we will have to switch to an Oracle database (at an increased price). My question is, is Blackboard pulling our leg or does MySQL truly have a problem scaling upwards? Jesse - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: MyODBC and SQL_BIT type
woops, my fault :) I'll subscribe to the MyODBC list and see what I can find there, thanks :) - Original Message - From: "Sinisa Milivojevic" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 06, 2001 4:03 AM Subject: Re: MyODBC and SQL_BIT type Eric Fitzgerald writes: Let's try this one more time, just in case it got scimmed over :) Hello everyone. I'm hoping the developer of MyODBC hangs out on this list, but here goes. I browsed through the sources a bit, and I noticed several places where you are mapping SQL_BIT type to TinyINT. It appears this is only a one way mapping (software-odbc-mysql) and not the other way. What I would like is 1 of 2 things, either 1) A possible update to MyODBC where an option can be checked (similiar to large results etc..) that allows TinyINT columns to be "converted" to SQL_BIT when sent to the software or 2) Pointers on where in the sources the "translation" is done when coming from mysql. Reason I need these is I'm under the gun to "fully" integrate Visual FoxPro with MySQL (one data source, that whole thing) and the current Visual FoxPro tables use a LOT of bool fields. So if I can do a little translation on the fly, I can fake this, and make everyone happy :) Thanks in advance, Eric Fitzgerald Server Administrator AMN Television Marketing Hi! We have both myodbc and win32 mailing lists which are more appropriate for the above questions. If I knew the answer, I would have told you, but I don't .. ;-( Regards, Sinisa __ _ _ ___ == MySQL AB /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| /*/^^^\*\^^^ /*/ \*\Developers Team - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
MyODBC and SQL_BIT type
Hello everyone. I'm hoping the developer of MyODBC hangs out on this list, but here goes. I browsed through the sources a bit, and I noticed several places where you are mapping SQL_BIT type to TinyINT. It appears this is only a one way mapping (software-odbc-mysql) and not the other way. What I would like is 1 of 2 things, either 1) A possible update to MyODBC where an option can be checked (similiar to large results etc..) that allows TinyINT columns to be "converted" to SQL_BIT when sent to the software or 2) Pointers on where in the sources the "translation" is done when coming from mysql. Reason I need these is I'm under the gun to "fully" integrate Visual FoxPro with MySQL (one data source, that whole thing) and the current Visual FoxPro tables use a LOT of bool fields. So if I can do a little translation on the fly, I can fake this, and make everyone happy :) Thanks in advance, Eric Fitzgerald Server Administrator AMN Television Marketing - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Are all SQL-databases based on the same query-language?
SQL itself is not the way the data is stored. SQL stands for Structured Query Language and is a way of retrieving and modifying/adding records. If you write your SQL carefuly, yes you can use it pretty much "as is" on any SQL system. There are little changes and such however that every SQL server makes to the ASNI SQL standards. Somethings will work on one, but not another. Just be careful when writing your syntax and you should be fine. - Original Message - From: "SED" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 31, 2001 10:26 AM Subject: Are all SQL-databases based on the same query-language? Hi, I have only used MySQL. Today I read that all SQL use the same query-language, so if you program something, e.g. in PHP, you can also use is with other SQL-databases, with perhaps minor adjustments for the database-connections. Is this true? Regards, Sumarlidi Einar Dadason SED - Graphic Design -- Phone: (+354) 4615501 Mobile: (+354) 8960376 Fax: (+354) 4615503 E-mail: [EMAIL PROTECTED] Homepage:www.sed.is - New Homepage! -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Verification
I'm going to have to answer Yes, and No. Yes, they can access it. No, it won't be the best situation. The internet is not very trustworthy, and having DB's seperated over such large distances accessing instantly is probably not the best choice. Not to mention that if you have large record sets, it can really slow down. A better choice IMHO would be to replicate pieces of the DB on the client's machine, and have an automated batch process setup every night or so to update the tables. - Original Message - From: "Carlos Corzo" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 29, 2001 3:06 PM Subject: Verification Hello, I just want to make sure that I can do this but would this scenario work properly. I build an sql database and perl interface at site www.siteX.com. A person on www.siteY.com is going to utilize this tool The perl interface running on www.siteY.com makes a call to the database from www.siteX.com. As long as www.siteY.com is granted access to the database, it should be able to access the database right? Just wanted to check? 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Getting totals from database
http://www.mysql.com/doc/M/a/Mathematical_functions.html Please please please read manual before asking questions. SELECT SUM(minutes) AS ttltime, user FROM table WHERE user = 'someuser' GROUP BY user - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 26, 2001 3:00 PM Subject: Getting totals from database I know this is probably a simple thing, but what I would like to be able to do, is to query a specific account name for minutes each day and then have those minutes ADDED up to show the current total time online. My database has all of the info in the table, all I want to do is be able to query for a specific user and show total time for the month to date. Any help appreciated! Please reply via separate email.. == Phillip Ferraro WorldNet Access, Inc [EMAIL PROTECTED] Onslow County's PREMIER InterNet Service Voice (910) 346-0835 824 Gumbranch Square, Suite Q FAX (910) 455-1933 Jacksonville, Nc 28540-6269 == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: third time asking help concerning charsets
Sometimes when people aren't answering a question, it isn't because they are ignoring you or consider your questions stupid. Sometimes it's because we don't know the answer. Your question has nothing to do with MySQL and everything to do with php. It doesn't like that charset. I have no clue on how to make it like it, I don't know what it wants. Ask a php list. - Original Message - From: "Roman Serbski" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 11, 2001 7:43 PM Subject: third time asking help concerning charsets I'm sorry, this is the third time I ask help from this list. No suggestions :-(, not even one. If it is a dummy question please point me to some url ... whatever. Good day all! Please help me with charset adding. I have some strange trouble. I use FreeBSD 4.2 STABLE and MySQL 3.23.28-gamma (compiled from FreeBSD ports) and PHP 4.04. As I understood Latin1 is a default charset. So, I wanted to add cp1251 or koi8_ru. Ok, I add --default-character-set=cp1251 to my mysql start-up script. The problem is: If I connect from console to mysql and type \s (to get status information) it shows: Language: cp1251 Everything seems OK, but if I try to connect via phpMyAdmin or Horde (webmail implementation) it returns - For phpMyAdmin Warning: MySQL Connection Failed: Can't initialize character set 14 (path: default) in /usr/local/www/htdocs/adm/lib.inc.php on line 255 Error For Horde Warning: Can't initialize character set 14 (path: default) in /usr/local/lib/phplib/db_mysql.inc on line 73 I don't know where is my mistake? I do have sql/share/charsets folder with Index and cp1251.conf. I checked Index - records are OK. Should I add something to /etc/my.cnf file? Looking forward to hear from you. Regards, Roman - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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