problem with long query!
We have the following problem: on the server (quite fast) we have the Mysqld-nt daemon running ; and the data resides in it. When a user access the server with a very long query and another try to do the same , but with a very simple query ; the last one has to wait a very long time because of the first query takes almost all the resources (100% CPU time when it should not !) ...when normally it would take 2 or 3 seconds... Is there a way ( some variables to set , some specific commands,OS configuration or whatever!) to make the server stop for a while the thread concerned with the long query , execute the simple query , and return back to its job?...in short: how could we optimize and better balance the server load ? We tried several solution , like changing variables like key_buffer_size , join_buffer_size etc. Change some TEXT fields in CHAR fields but it does not seem to work . You know where the problem is ?...if in a big office one launch a long query ( in our case it could happen) the other clients are almost stuck! TIA C. P.S. We use a Visual Basic-like programming language to build our application , and we access the data via MyODBC 2.5 and ADO. Normally the LAN we are dealing with are of peer-to-peer type. __ Tiscali ADSL. Scopri la fantastica promozione di Natale: tutto Gratis fino al 9 gennaio! Abbonati ora: prima ti abboni, più risparmi! http://point.tiscali.it/adsl/index.shtml - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sequence of SQL actions gives wrong query result for 10 seconds!
All, I've been having problems with a pretty simple series of SQL statements run against some InnoDB tables returning the wrong results - temporarily! Basically, I have one middle table that maps a role to an operation (many-to-many), and I have separate role and operation tables that contain respective descriptions for the codes. If I remove all the rows for a particular role id from that middle table and run a SELECT looking for that role and joining to both other tables to get relevant descriptions, I get erroneous rows back! (The number of rows seems to differ depending upon whether my isolation-level is SERIALIZABLE or READ_COMMITTED.) These erroneous rows continue to be returned until about 10 seconds after I've committed the transaction, when I receive the correct Empty set response. I've managed to come up with a fully repeatable test that I'll include below. (I'm not sufficiently familiar with MySQL that I know how to use the test script or whatever it is, and I don't think it came with my Windows' binary distribution anyhow. Hopefully I'm providing enough info to make it easy anyway...) I'm running mysqlmax 3.23.52 on Win2k. I understand there's a .53 in existence but I checked the change-logs and they didn't mention anything about a problem like this having been fixed. The script follows. After that, there's the mysqladmin variables output and the (not-very-pretty) results I get when running the test - though it should be dead easy for anyone to replicate using the script (as long as it's not a problem with my setup). BEWARE - the script drops / creates tables. Note also that I'm running in ANSI compatible mode. (Oh - one last thing: I may be off track, but I seem to recall that not having primary keys on the tables (when I was trying to produce my test-script) affected the results.) Any info appreciated! Thanks, Mike. THE SCRIPT STARTS HERE. # SET UP THE STARTING POSITION. drop table application_role; drop table application_role_operation; drop table application_operation; CREATE TABLE application_role (aro_role_id integer not null, aro_role_desc varchar(30) not null, last_updated_by integer null, last_updated datetime null, constraint aro_pk primary key (aro_role_id)) type=InnoDB; CREATE TABLE application_operation (aop_operation_code varchar(30) not null, aop_operation_desc varchar(30) not null, last_updated_by integer null, last_updated datetime null, constraint aop_pk primary key (aop_operation_code)) type=InnoDB; CREATE TABLE application_role_operation (aro_role_id integer not null, aop_operation_code varchar(30) not null, last_updated_by integer null, last_updated datetime null, constraint arn_pk primary key (aro_role_id, aop_operation_code)) type=InnoDB; begin; insert into application_role(aro_role_id, aro_role_desc) values (1000,'test 1'); insert into application_role(aro_role_id, aro_role_desc) values (1001,'test 2'); insert into application_operation(aop_operation_code, aop_operation_desc) values ('OP1','Operation 1'); insert into application_operation(aop_operation_code, aop_operation_desc) values ('OP2','Operation 2'); insert into application_role_operation(aro_role_id, aop_operation_code) values (1000,'OP1'); insert into application_role_operation(aro_role_id, aop_operation_code) values (1001,'OP1'); insert into application_role_operation(aro_role_id, aop_operation_code) values (1000,'OP2'); commit; # END OF SETUP. Below are the statements that cause the problem. begin; DELETE FROM application_role_operation WHERE aro_role_id=1000 AND aop_operation_code='OP1'; DELETE FROM application_role_operation WHERE aro_role_id=1000 AND aop_operation_code='OP2'; SELECT * FROM application_role_operation WHERE aro_role_id=1000; #This is the problem query. It still brings back a row, even though we've shown above that there #are not rows in application_role_operation that have an aro_role_id of 1000. SELECT * FROM application_role_operation a, application_role b, application_operation c WHERE a.aro_role_id = b.aro_role_id AND a.aop_operation_code = c.aop_operation_code AND a.aro_role_id = 1000 AND b.aro_role_id = 1000; commit; #The problem persists past a commit, but only for about 10 seconds or so begin; SELECT * FROM application_role_operation a, application_role b, application_operation c WHERE a.aro_role_id = b.aro_role_id AND a.aop_operation_code = c.aop_operation_code AND a.aro_role_id = 1000 AND b.aro_role_id = 1000; THE SCRIPT ENDS HERE SETUP INFORMATION.. mysqld-max-nt Ver 3.23.52-max-nt for NT on i32 (Windows 2000 Professional 5.00.2195 Service Pack 2 on a Compaq Evo D510SFF 1.8GHz P4, 1GB Ram.) [I think I've also run the non-NT version of the executable against the same datafiles.] OUTPUT OF mysqladmin variables: +-+-
Index of packed table not used
Hi! We are using mysql, version 4.0.4 beta on windows 2000 Server. I packed a table with 100 million rows, using myisampack. Now queries don't use the index of this table. It's not possible for me, to work with table scan. So, what can i do? Kai - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Decrypt MYSQL Password
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Ben, From the MySQL man online @ http://www.mysql.com/doc/en/Miscellaneous_functions.html#IDX1330 : PASSWORD() encryption is non-reversible. To create a forgot your password page, the best method is to generate a net password and send that new password. To make it easier to your users to remember their passwords, the best way is to set that password as a temporary one, that only allows them to set the original password. (maybe using a diferent field, so they can keep the original password if someone else goes to your site and request the password, just because he was bored. MPNeves On Wednesday 13 November 2002 07:27 am, Ben C. wrote: I am using the MySQL password() function for the my passwords on the user names. How do I decrypt the password in PHP to send it in an e-mail. I am making a forgot your password page and want to have the user enter their e-mail and have the password sent to them. Please help! - -- Merlin, the Mage themage.camelot.co.pt -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE90hleKOMrqDFTeBARAoc7AKDozVXEVP8Tfv8Qjx9DE4ChPF8zsACaAh/C efo5W0z4+dO3UEvTnDz9qJk= =i4Oj -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql,Explore abused lolitas 4 free !
Hello dear Mysql http://gobrutal.com dedicated to the beauty of Young Girls. Undoubtedly, this site is the best of its kind. We bring you only tasteful high quality fotos videos of young girls. Every frame honors purity and innocence of youth involved in sexual acts... http://gobrutal.com Young girls fucking with friends and relatives! We collect only high quality fotos videos. Most of the movies are from its original source and are of its original quality. You will never find blackwhite or low resolution images on our site. All fotos are sorted into thumbnail galleries. Needless to say that there are no banners or any sort of advertisements in the members area. Just thousands and thousands mpegs!!! Go Here for free preview http://gobrutal.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
AW: Decrypt MYSQL Password
Hi Ben, As far as I know the algorithm used for the decrypting is a one-way hash function, thus there is no way of decrypting it. You may consider to reset the password and mail the user the new one instead of mailing the old password. The second solution is to store the password a second time as clear-text at the user's property. Regards, Wolf -Originalnachricht- Von: Ben C. An: [EMAIL PROTECTED] Gesendet: 13.11.2002 08:27 Betreff: Decrypt MYSQL Password I am using the MySQL password() function for the my passwords on the user names. How do I decrypt the password in PHP to send it in an e-mail. I am making a forgot your password page and want to have the user enter their e-mail and have the password sent to them. Please help! -- The content of this email message and any attachments are confidential and may be legally privileged, intended solely for the addressee. If you are not the intended recipient, be advised that any use, dissemination, distribution, or copying of this e-mail is strictly prohibited. If you receive this message in error, please notify the sender immediately by reply email and destroy the message and its attachments. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Help! problem using MySQL C API
Hi, I'm having trouble writing dial-up user billing software in C using MySQL C API. Program suppose to send query to mysql server and retrieve records and process those. It sends query for each class_id for every day and get records from ACCOUNTING table. While retrieving records it adds user in linked list if user doesn't exist. There will be total not more than 1000 users per one day with duration, number of session, time_stamp etc. After adding user to linked list it calculates the charges for duration that he connected to Internet. In order to calculate charge it sends various queries to various tables to get data. Program is working fine for one day. But when I try to calculate from beginning of the month, after 2 days program crashes saying: Out of memory (Needed 8164 bytes) select charge,from_min,to_min,class_id,id from customers.intervals where class_id='19' order by id query failed! when sending trying to send query to get data for calculation. I think there are some problem with memory management, but when I just run program without calculating charges it works just fine. Can somebody recommend me better way of handling in this situation? Is this good choice to use linked list in this case? Is there any other good resource for writing mysql client in C on the web? Please let me know if somebody knows and solved this problem before. Please give me some recommendation in this regard. I'm using FreeBSD 4.6-STABLE, with 512 MB RAM and 50GB HDD with 6GB of free space. MySQL version is :3.23.38 thanks in advance, Ganbold Below is my code snippets: - typedef struct user_b{ int contract_id; int class_id; char *username; int numSession; long time_stamp; long duration; double pre_balance; double due_balance; double total; double tax; double payment; int discount; int is_payed; /* 1 for true, 0 for false */ int location_id; struct user_b *next; } USER; Main code USER *customer_list; customer_list = NULL; for(i=day;i0;i--){ deleteTableEntry(fp,i,billing_test); if(customer_list!=NULL){ customer_list = destroyUserList(customer_list); /* destroy user list */ } customer_list = prepareUser(fp,customer_list,i,daytime,nighttime); writeBillingTable(fp,customer_list); writePaymentTable(fp,customer_list); } if(customer_list!=NULL){ customer_list = destroyUserList(customer_list); /* destroy user list */ } Functions /*-*/ USER *installUser(FILE *logfile, USER *pslist,USER **curr, char *userid, int class_id, int contract_id, long duration, long time_stamp, int day) /* Parameter: USER **curr - pointer to pointer to current user */ { USER *p; USER *prev; /* pointer to previous element in list */ double amount; if((p = searchUser(pslist,prev,userid))==NULL){ /* if user is not in list */ /* allocate memory */ p = (USER *) malloc(sizeof(USER)); /* if unsuccessfully allocated */ if(p ==NULL || (p-username = my_alloc(userid)) ==NULL){ fprintf(stderr,Out of memory while allocating!\n); exit(4); } p-class_id = class_id; p-contract_id = contract_id; p-duration = duration; p-time_stamp = time_stamp; p-numSession = 1; /* add user to our linked list in appropriate place */ if(prev == NULL){ /* in the beginning or to null list */ p-next = pslist; pslist = p; }else{/* in the middle or at end */ p-next = prev-next; prev-next = p; } }else{ p-duration += duration; p-numSession++; } /* calculate charges */ amount = applyIntervals(logfile, day, class_id, time_stamp, duration,p-duration,p-total); amount = applyTimeFrame(logfile, day, class_id, time_stamp, duration,p-duration,p-total); amount = applyClassInfo(logfile, day, class_id, time_stamp, duration,p-duration,p-total); /* current is now points to existing user or to new user */ *curr = p; /* return pointer to our list */ return pslist; } /* end installUser */ /*-*/ USER *prepareUser(FILE *logfile, USER *customer_list, int day, char *day_time, char *night_time) { MYSQL_RES *res_set; MYSQL_ROW row; char query[BUF]; USER *curr; /* pointer to current elem */ char *morning; char *midnight; int session,contract_id,class_id; long time_stamp;
newbie stuff: good mysql books?
Greets Folks, I have been wanting to get into data driven sites for the longest time, and I can do some immediate level work in them using perl, but my understanding is still not comprehensive enough for whatever it is I want to do (subconsciously). I sense there is some need to pursue database design better, and an overall book on system's design might be good too. I've read MySQL and Perl for the Web by Paul, and Programming with CGI.pm, and both helped a lot. But there is some sort of a want to work specifically with the database more, especially design for largescale systems development, and I am just wondering what books might be good for this purpose? PLMK, Thanks, Will _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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 Rows in two tables
If you know the primary keys of the two tables, it may be done as follows: select count(distinct t1.pk1) + count(distinct t2.pk2) from t1,t2; where - pk1 is the primary key column name of table t1 and pk2 is the primary key column name of table t2. SB - Original Message - From: Rick Baranowski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 13, 2002 8:39 AM Subject: Re: Count Rows in two tables Please forgive me I am new to mySQL, could you give me an example of how I would do this? The two tables are users and staff Thanks Rick - Original Message - From: Murad Nayal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 12, 2002 6:58 PM Subject: Re: Count Rows in two tables Alan McDonald wrote: You can't count the join? Alan if you count the (unqualified) join you'll end up with the product of the two table counts. Murad -Original Message- From: Rick Baranowski [mailto:rickb;baranconsulting.com] Sent: Wednesday, 13 November 2002 12:10 To: [EMAIL PROTECTED] Subject: Count Rows in two tables Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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! problem using MySQL C API
Try using mysql_free_result each time after fetching the rows. SB - Original Message - From: Ganbold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 13, 2002 4:27 PM Subject: Help! problem using MySQL C API Hi, I'm having trouble writing dial-up user billing software in C using MySQL C API. Program suppose to send query to mysql server and retrieve records and process those. It sends query for each class_id for every day and get records from ACCOUNTING table. While retrieving records it adds user in linked list if user doesn't exist. There will be total not more than 1000 users per one day with duration, number of session, time_stamp etc. After adding user to linked list it calculates the charges for duration that he connected to Internet. In order to calculate charge it sends various queries to various tables to get data. Program is working fine for one day. But when I try to calculate from beginning of the month, after 2 days program crashes saying: Out of memory (Needed 8164 bytes) select charge,from_min,to_min,class_id,id from customers.intervals where class_id='19' order by id query failed! when sending trying to send query to get data for calculation. I think there are some problem with memory management, but when I just run program without calculating charges it works just fine. Can somebody recommend me better way of handling in this situation? Is this good choice to use linked list in this case? Is there any other good resource for writing mysql client in C on the web? Please let me know if somebody knows and solved this problem before. Please give me some recommendation in this regard. I'm using FreeBSD 4.6-STABLE, with 512 MB RAM and 50GB HDD with 6GB of free space. MySQL version is :3.23.38 thanks in advance, Ganbold - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Bug: Dumps with InnoDB and Constrains
Hello! mysql Ver 11.18 Distrib 3.23.53, for portbld-freebsd4.7 (i386) I have a problem with dumping data into databases. I dump the database with mysqldump into a SQL-File. So, i delete the database. Now i want to redump the SQL-File into database. SQL tells me an error. The Problem is MySQL doesn't sort table by Constrains. In the Test-Case you see what MySQL Dumps, but the problem is that 'a_table' has references to 'b_table' and 'b_table' doesn't exist on an initial dump from file into database. Test-Case: # # Tabellenstruktur für Tabelle `a_table` # DROP TABLE IF EXISTS `a_table`; CREATE TABLE `a_table` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `b_table_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `b_table_id` (`b_table_id`), FOREIGN KEY (`b_table_id`) REFERENCES `b_table` (`id`) ON DELETE CASCADE ) TYPE=InnoDB; # # Tabellenstruktur für Tabelle `b_table` # DROP TABLE IF EXISTS `b_table`; CREATE TABLE `b_table` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', PRIMARY KEY (`id`) ) TYPE=InnoDB; -- Mit freundlichen Grüßen Enrico Stahn --- Enrico Stahn Kaiserin-Augusta-Allee 10-11, 10553 Berlin, Germany todo GmbHTel: +49 30 726 192 0 Fax: +49 30 726 192 192 [EMAIL PROTECTED], http://www.todo.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqld cpu load
When MYSQL is being heavily used on a windows cpu, In Task Manager does the memory cpu load increase show the use for mysqld or another process like Explorer? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: limited queries to one returned row
* raj However, I wish to use this as a security tool. I.e. one in which I (as the mysql admin) can do a grant like: all queries on a specific table from user apache must only return 1 record This would be like forcing a limit of 1 (using the technique below). I don't think GRANT can be used this way, but take a look at SQL_SELECT_LIMIT: URL: http://www.mysql.com/doc/en/SET_OPTION.html ...and also the --safe-updates parameter of the mysql client: URL: http://www.mysql.com/doc/en/mysql.html -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sorting Records based on field
Having just walked into a job a few months back, one of the tasks I've been asked to accomplish is to counting records inside a DB based on a field. The database in questions holds realtor listings. There are fields for Company Name and Agent Name on each listing. What I need to do is sort records based on the Company Name and the itemize the number of ads per Agent and show that in a report. The Agents may change from time to time so keeping a table for a Company with only the agent names may not be a solution. Is there a way to pull these records in this fashion in MySQL? Like I said all the information is stored in one table with fields for Company name, Agent name, price, city, county etc.. Example: Realty Company 1 -- Agent 1 10 ads Agent 2 8 ads Agent 3 20 ads Realty Company 2 -- Agent 1 15 ads Agent 2 3 ads and so on Thanks, Ed Curtis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Windows cnf file location
On Tue, 12 Nov 2002, John Chang wrote: Is there a good or bad reason to create the config file in the MySQL folder? IMHO, usually no. But if you have several differently configured server instances, it might make things more logical. On production servers which logs do you turn on? Error log (which is automatically on) and I also use slow query logging. For replication you need binary logging. Query logging is hardly needed on production machines (it slows things down, use it on your development box). Typically what things do you set on in the config file on a production server? http://www.mysql.com/doc/en/Server_parameters.html might help you. Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Moving a MySQL database
I need to move a whole bunch of web sites from one server to another. This means moving cold fusion, MySQL, qmail, etc.. fun stuff. What's the best way to move *ALL* of the MySQL databases on my system? Do I basically just shut down the database, tar up the /var/lib/mysql directory and move it to the new server, or is it more complicated than that? The version of mysql will be exactly the same. I'm moving the sites to a temporary server so I can rebuild my production server with RAID, more reliable filesystems, etc. I'm running MySQL 3.23.41 from a binary RPM distribution on RedHat 7.2 Thanks. P.S. - is there a mailing list archive? I didn't see a refernece to one on the MySQL web site. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySql++ memory leak
Description: I use a little subset of mysql++ capabilities - Connection - Query ( only Query::store ) - Result Memory leak I discovered is located somewhere in Result::operator= and is available only after update; Proof of concept code is supplied. It leaks 1 Mb of memory in 10 seconds executing about 12000 updates I use Intel celeron 333, 2.4.18 linux kernel on late slackware distibution. MySQL++ 1.7.9 built from source, no __USLC__ conditional defined How-To-Repeat: #include iostream #include mysql++ using namespace std; #define DBNAME test #define DBHOST localhost #define DBUSER root #define DBPW const string dbname(DBNAME); const string dbhost(DBHOST); const string dbuser(DBUSER); const string dbpw(DBPW); #define dlog cerr Connection* con=0; Result res; //Result *res=0; void sql(const char* q) { //dlog [ DB ] Query sent: q endl; try { Query query = con-query(); // Use of dynamically allocated and manually cleaned Result // will eliminate memory leak // if(res) delete res; // res=new Result(); // *res=query.store(q); // MEMORY LEAK res=query.store(q); } catch(BadQuery e) { dlog [ DB ] MySQLLow -- BadQuery: e.error endl; throw; } catch(exception e) { dlog [ DB ] MySQLLow -- exception: e.what() endl; throw; } catch(...) { dlog [ DB ] MySQLLow -- exception: ? endl; throw; } } void connect(void) { dlog [ DB ] Connecting to database: dbname endl; try { con=new Connection(use_exceptions); con-connect(,dbhost.c_str(),dbuser.c_str(),dbpw.c_str()); try { con-select_db(dbname); } catch (BadQuery er) { con-create_db(dbname.c_str()); con-select_db(dbname.c_str()); } } catch(BadQuery e) { dlog MySQLLow -- BadQuery: e.error endl; throw; } catch(exception e) { dlog MySQLLow -- exception: e.what() endl; throw; } } int main(void) { connect(); int i=0; while(true) { sql(SELECT * FROM a); // comment out this line to eliminate memory leak sql(UPDATE a SET i=i+1); dlog '.'; } } Fix: Workaround: Recreating Result object each query elminates this problem. But we still hope on MySQL++ development team, aint we =? Submitter-Id: submitter ID Originator: Organization: MySQL support: none Synopsis: mysql++ memory leak Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.39 (Source distribution) Server: /usr/bin/mysqladmin Ver 8.21 Distrib 3.23.39, for slackware-linux-gnu on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.39-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysql/mysql.sock Uptime: 2 days 21 hours 27 min 45 sec Threads: 11 Questions: 2375042 Slow queries: 2 Opens: 195656 Flush tables: 1 Open tables: 4 Queries per second avg: 9.498 Environment: System: Linux kerzum 2.4.18 #4 SMP Sun Oct 13 19:50:52 MSD 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Oct 13 20:28 /lib/libc.so.6 - libc-2.2.3.so -rwxr-xr-x1 root root 4783716 May 26 2001 /lib/libc-2.2.3.so -rw-r--r--1 root root 24721042 May 26 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 May 26 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/usr --with-mysqld-user=mysql --with-unix-socket-path=/var/run/mysql/mysql.sock --localstatedir=/var/lib/mysql --with-pthread --enable-thread-safe-client --enable-assembler --with-raid --with-libwrap --without-bench i386-slackware-linux - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL++ Memory leak
Background: MySQL++ ( libsqlplus ) is a pretty nice official C++ styled mysql client library wrapper of libmysqlclient. It offers STL-style C++ interface and is designed with an effort of generic programming style. It should be a really excellent choise if only it had not a MEMORY LEAK problem. http://www.mysql.com/downloads/api-mysql++.html Investigation: As this library is commonly used to develop a CGI C++ application problem is not so crytical. CGI process lives just a few seconds and then dies, all memory it have been using is deallocated by the system. I've fortunely found this bug developing apache mod_rewrite program map application that should stay alive all the apache lifetime. It cannot be restarted without httpd restart, and if it craches it crashes apache too =( I use a little subset of mysql++ capabilities - Connection - Query ( only Query::store ) - Result Memory leak I discovered is located somewhere in Result::operator= and is available only after update; Proof of concept code is supplied. It leaks 1 Mb of memory in 10 seconds executing about 12000 updates I use Intel celeron 333, 2.4.18 linux kernel on late slackware distibution. MySQL++ 1.7.9 built from source, no __USLC__ conditional defined Workaround: Recreating Result object each query elminates this problem. But we still hope on MySQL++ development team, aint we =? Links: Note that I have been using just a little subset of MySQL++, further memory leaks may follow. mailing.database.myodbc and mailing.database.mysql group has several repots about -- have a look at them ( Google-groups search result on 'mysql++ memory leak' ) -- http://groups.google.com.ru/groups?q=mysql%2B%2B+memory+leakhl=rulr=ie=UTF-8inlang=ruselm=a9p6vn%242oci%241%40FreeBSD.csie.NCTU.edu.twrnum=3 http://groups.google.com.ru/groups?q=mysql%2B%2B+memory+leakhl=rulr=ie=UTF-8inlang=ruselm=2ed0f107.0203200439.2b4ef27a%40posting.google.comrnum=2 // Proof of concept --- #include iostream #include mysql++ using namespace std; #define DBNAME test #define DBHOST localhost #define DBUSER root #define DBPW const string dbname(DBNAME); const string dbhost(DBHOST); const string dbuser(DBUSER); const string dbpw(DBPW); #define dlog cerr Connection* con=0; Result res; //Result *res=0; void sql(const char* q) { //dlog [ DB ] Query sent: q endl; try { Query query = con-query(); // Use of dynamically allocated and manually cleaned Result // will eliminate memory leak // if(res) delete res; // res=new Result(); // *res=query.store(q); // This is another workaround: // res.purge() // I soppose this is not called or called incorrectly in ResUse::operator= // MEMORY LEAK res=query.store(q); } catch(BadQuery e) { dlog [ DB ] MySQLLow -- BadQuery: e.error endl; throw; } catch(exception e) { dlog [ DB ] MySQLLow -- exception: e.what() endl; throw; } catch(...) { dlog [ DB ] MySQLLow -- exception: ? endl; throw; } } void connect(void) { dlog [ DB ] Connecting to database: dbname endl; try { con=new Connection(use_exceptions); con-connect(,dbhost.c_str(),dbuser.c_str(),dbpw.c_str()); try { con-select_db(dbname); } catch (BadQuery er) { con-create_db(dbname.c_str()); con-select_db(dbname.c_str()); } } catch(BadQuery e) { dlog MySQLLow -- BadQuery: e.error endl; throw; } catch(exception e) { dlog MySQLLow -- exception: e.what() endl; throw; } } int main(void) { connect(); int i=0; while(true) { sql(SELECT * FROM a); // comment out this line to eliminate memory leak sql(UPDATE a SET i=i+1); dlog '.'; } } // Table creation command: // CREATE TABLE a ( i integer); -- Sincerely yours Peter Kerzum - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Sorting Records based on field
* [EMAIL PROTECTED] Having just walked into a job a few months back, one of the tasks I've been asked to accomplish is to counting records inside a DB based on a field. The database in questions holds realtor listings. There are fields for Company Name and Agent Name on each listing. What I need to do is sort records based on the Company Name and the itemize the number of ads per Agent and show that in a report. The Agents may change from time to time so keeping a table for a Company with only the agent names may not be a solution. Is there a way to pull these records in this fashion in MySQL? Like I said all the information is stored in one table with fields for Company name, Agent name, price, city, county etc.. Example: Realty Company 1 -- Agent 1 10 ads Agent 2 8 ads Agent 3 20 ads Realty Company 2 -- Agent 1 15 ads Agent 2 3 ads This looks like two different results... a SQL query returns _one_ resultset. Try this: SELECT CompanyName,AgentName,count(*) as ads FROM table GROUP BY CompanyName,AgentName ORDER BY CompanyName,AgentName; URL: http://www.mysql.com/doc/en/SELECT.html The output could be something like this: ComanyNameAgentName ads - Realty Company 1 Agent 1 10 Realty Company 1 Agent 2 8 Realty Company 1 Agent 3 20 Realty Company 2 Agent 1 15 Realty Company 2 Agent 2 3 If you really want separate result tables for each company, you would probably need to use some programming language. -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
winmysqladmin.exe does not run on Windows NT
I recently installed mysql 3.23.53 on Windows NT. I'm no longer able to run 'winmysqladmin.exe'. The following message is displayed: winmysqladmin.exe - Entry Point Not Found The procedure entry point CreateToolhelp32Snapshot could not be located in the dynamic link library KERNEL32.DLL. Best regards, --- Lode Couplez mailto:Lode.Couplez;telindus.be Technical coordinator Phone: (+32) 16-38.24.52 Fax: (+32) 16-38.25.45 --- For more information about our products and services, please visit out website http://www.telindus.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
MySQL Process ID Unix PID
Hi, Is there a way to compare between a mysql process ID showed in top or ps with a process ID from mysqladmin processlist ? I'm asking this because we operate a mysql machine used for webhosting and it has many users and many databases. If users do crazy stuff that result in high usage of resources, we need to determine who is the user, which database is it accessing and finally what script causes the problem. Any ideas or utils/scripts for digging this info apreciated. Tnx. Justin Grindea System Administrator SweetHome.co.il - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Complicated query. query help
I've been trying to work out a complicated query to select and sum multiple columns in one select using a condition and multi column group by What I expected to get was ONE row with the columns summed . this query returns 5 rows SELECT sum(`AllTheWeb`),sum(`AltaVista`),sum(`AOL`),sum(`Ask`),sum(`Ask Jeeves`),sum(`Cest trouve`),sum(`DirectHit`),sum(`DMOZ`),sum(`Dogpile`),sum(`Euroseek`),sum(`Excite`),sum(`Fireball`),sum(`FrancitT`),sum(`Go2Net`),sum(`Google`),sum(`Hotbot`),sum(`Kanoodle`),sum(`LBB`),sum(`Libertysurf`),sum(`Lokace`),sum(`Lycos`),sum(`Mamma`),sum(`MegaSpider`),sum(`MetaCrawler`),sum(`MetaGer`),sum(`MSN`),sum(`NBCI`),sum(`Netscape`),sum(`Nomade`),sum(`NorthernLight`),sum(`Overture`),sum(`Spray`),sum(`Terra`),sum(`Vindex.nl`),sum(`Voila`),sum(`Web.de`),sum(`WebCrawler`),sum(`WebSearch`),sum(`Yahoo`) FROM engine where sitenum = 2365602 GROUP BY `AllTheWeb`,`AltaVista`,`AOL`,`Ask`,`Ask Jeeves`,`Cest trouve`,`DirectHit`,`DMOZ`,`Dogpile`,`Euroseek`,`Excite`,`Fireball`,`FrancitT`,`Go2Net`,`Google`,`Hotbot`,`Kanoodle`,`LBB`,`Libertysurf`,`Lokace`,`Lycos`,`Mamma`,`MegaSpider`,`MetaCrawler`,`MetaGer`,`MSN`,`NBCI`,`Netscape`,`Nomade`,`NorthernLight`,`Overture`,`Spray`,`Terra`,`Vindex.nl`,`Voila`,`Web.de`,`WebCrawler`,`WebSearch`,`Yahoo` the simplest query select * from engine where sitenum = 2365602 returns 15 rows Did I find a bug (either on the query side) on the error checking side, where by it's allowing a disallow able query or is that query just SOO badly formatted that it'll never work but is allowable? I really need some help :) to execute THAT query where by getting one row with those columns summed Thanks Rick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Count Rows in two tables
Thank you for the response. It looks like that worked! I am going to try it on the webpage now. I will let everybody know if this works. Thanks Rick PS. to everyone else I want to say thanks. I have run the ones that you have sent but always came up with an error. If you want me to send you the error let me know and I will send it to you. Thanks again - Original Message - From: Shyamal Banerjee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 13, 2002 4:29 AM Subject: Re: Count Rows in two tables If you know the primary keys of the two tables, it may be done as follows: select count(distinct t1.pk1) + count(distinct t2.pk2) from t1,t2; where - pk1 is the primary key column name of table t1 and pk2 is the primary key column name of table t2. SB - Original Message - From: Rick Baranowski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 13, 2002 8:39 AM Subject: Re: Count Rows in two tables Please forgive me I am new to mySQL, could you give me an example of how I would do this? The two tables are users and staff Thanks Rick - Original Message - From: Murad Nayal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 12, 2002 6:58 PM Subject: Re: Count Rows in two tables Alan McDonald wrote: You can't count the join? Alan if you count the (unqualified) join you'll end up with the product of the two table counts. Murad -Original Message- From: Rick Baranowski [mailto:rickb;baranconsulting.com] Sent: Wednesday, 13 November 2002 12:10 To: [EMAIL PROTECTED] Subject: Count Rows in two tables Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Sequence of SQL actions gives wrong query result for 10 seconds!
Mike, thank you very much for a repeatable bug report! This revealed an embarrasing bug in InnoDB SELECTs: if you selected with the full value of a primary key, and the row which matched to it was delete-marked, then InnoDB could in some cases return the NEXT row. Since purge cleans off delete-marked records in the background, normally after a few seconds you would again get the correct result. I pushed the fix to the 4.0 tree, but am not sure if it makes it to 4.0.5. The fix will also be in 3.23.54. Thank you, Heikki Innobase Oy sql query - Original Message - From: Mike W-M [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, November 13, 2002 11:07 AM Subject: Sequence of SQL actions gives wrong query result for 10 seconds! All, I've been having problems with a pretty simple series of SQL statements run against some InnoDB tables returning the wrong results - temporarily! Basically, I have one middle table that maps a role to an operation (many-to-many), and I have separate role and operation tables that contain respective descriptions for the codes. If I remove all the rows for a particular role id from that middle table and run a SELECT looking for that role and joining to both other tables to get relevant descriptions, I get erroneous rows back! (The number of rows seems to differ depending upon whether my isolation-level is SERIALIZABLE or READ_COMMITTED.) These erroneous rows continue to be returned until about 10 seconds after I've committed the transaction, when I receive the correct Empty set response. I've managed to come up with a fully repeatable test that I'll include below. (I'm not sufficiently familiar with MySQL that I know how to use the test script or whatever it is, and I don't think it came with my Windows' binary distribution anyhow. Hopefully I'm providing enough info to make it easy anyway...) I'm running mysqlmax 3.23.52 on Win2k. I understand there's a .53 in existence but I checked the change-logs and they didn't mention anything about a problem like this having been fixed. The script follows. After that, there's the mysqladmin variables output and the (not-very-pretty) results I get when running the test - though it should be dead easy for anyone to replicate using the script (as long as it's not a problem with my setup). BEWARE - the script drops / creates tables. Note also that I'm running in ANSI compatible mode. (Oh - one last thing: I may be off track, but I seem to recall that not having primary keys on the tables (when I was trying to produce my test-script) affected the results.) Any info appreciated! Thanks, Mike. THE SCRIPT STARTS HERE. # SET UP THE STARTING POSITION. drop table application_role; drop table application_role_operation; drop table application_operation; CREATE TABLE application_role (aro_role_id integer not null, aro_role_desc varchar(30) not null, last_updated_by integer null, last_updated datetime null, constraint aro_pk primary key (aro_role_id)) type=InnoDB; CREATE TABLE application_operation (aop_operation_code varchar(30) not null, aop_operation_desc varchar(30) not null, last_updated_by integer null, last_updated datetime null, constraint aop_pk primary key (aop_operation_code)) type=InnoDB; CREATE TABLE application_role_operation (aro_role_id integer not null, aop_operation_code varchar(30) not null, last_updated_by integer null, last_updated datetime null, constraint arn_pk primary key (aro_role_id, aop_operation_code)) type=InnoDB; begin; insert into application_role(aro_role_id, aro_role_desc) values (1000,'test 1'); insert into application_role(aro_role_id, aro_role_desc) values (1001,'test 2'); insert into application_operation(aop_operation_code, aop_operation_desc) values ('OP1','Operation 1'); insert into application_operation(aop_operation_code, aop_operation_desc) values ('OP2','Operation 2'); insert into application_role_operation(aro_role_id, aop_operation_code) values (1000,'OP1'); insert into application_role_operation(aro_role_id, aop_operation_code) values (1001,'OP1'); insert into application_role_operation(aro_role_id, aop_operation_code) values (1000,'OP2'); commit; # END OF SETUP. Below are the statements that cause the problem. begin; DELETE FROM application_role_operation WHERE aro_role_id=1000 AND aop_operation_code='OP1'; DELETE FROM application_role_operation WHERE aro_role_id=1000 AND aop_operation_code='OP2'; SELECT * FROM application_role_operation WHERE aro_role_id=1000; #This is the problem query. It still brings back a row, even though we've shown above that there #are not rows in application_role_operation that have an aro_role_id of 1000. SELECT * FROM application_role_operation a, application_role b, application_operation c WHERE a.aro_role_id = b.aro_role_id AND a.aop_operation_code = c.aop_operation_code AND a.aro_role_id = 1000 AND b.aro_role_id =
Fw: SCHEMA, ARCHITECTURE ???
- Original Message - From: William Martell [EMAIL PROTECTED] To: MySQL Main List [EMAIL PROTECTED] Sent: Monday, November 11, 2002 2:02 PM Subject: SCHEMA, ARCHITECTURE ??? Hello All, How do I determine the best architecture for my MySQL database? Do I create multiple databases or do I create only one database with multiple tables? (Any general rules of thumb?) How do I know if I need to use Keys, Indexes or other features in order to optimize the database? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Complicated query. query help
* TimeWalker I've been trying to work out a complicated query to select and sum multiple columns in one select using a condition and multi column group by What I expected to get was ONE row with the columns summed . this query returns 5 rows SELECT sum(`AllTheWeb`),sum(`AltaVista`),sum(`AOL`),sum(`Ask`),sum(`Ask Jeeves`),sum(`Cest trouve`),sum(`DirectHit`),sum(`DMOZ`),sum(`Dogpile`),sum(`Euroseek `),sum(`Excite`),sum(`Fireball`),sum(`FrancitT`),sum(`Go2Net`),sum (`Google`),sum(`Hotbot`),sum(`Kanoodle`),sum(`LBB`),sum(`Libertysu rf`),sum(`Lokace`),sum(`Lycos`),sum(`Mamma`),sum(`MegaSpider`),sum (`MetaCrawler`),sum(`MetaGer`),sum(`MSN`),sum(`NBCI`),sum(`Netscap e`),sum(`Nomade`),sum(`NorthernLight`),sum(`Overture`),sum(`Spray` ),sum(`Terra`),sum(`Vindex.nl`),sum(`Voila`),sum(`Web.de`),sum(`We bCrawler`),sum(`WebSearch`),sum(`Yahoo`) FROM engine where sitenum = 2365602 GROUP BY `AllTheWeb`,`AltaVista`,`AOL`,`Ask`,`Ask Jeeves`,`Cest trouve`,`DirectHit`,`DMOZ`,`Dogpile`,`Euroseek`,`Excite`,`Fireball `,`FrancitT`,`Go2Net`,`Google`,`Hotbot`,`Kanoodle`,`LBB`,`Libertys urf`,`Lokace`,`Lycos`,`Mamma`,`MegaSpider`,`MetaCrawler`,`MetaGer` ,`MSN`,`NBCI`,`Netscape`,`Nomade`,`NorthernLight`,`Overture`,`Spra y`,`Terra`,`Vindex.nl`,`Voila`,`Web.de`,`WebCrawler`,`WebSearch`,`Yahoo` Have you tried this: SELECT sum(... WHERE sitenum = 2365602 GROUP BY sitenum ...should return one row... -- Roger sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SCHEMA, ARCHITECTURE ???
For speed multiple or single database makes no difference. For SQL I would stick with one if all the data is related. Index any thing you are going to select on. So if you have a user database you would index name and password not there age and job title. Simon - Original Message - From: William Martell [EMAIL PROTECTED] To: MySQL Main List [EMAIL PROTECTED] Sent: Monday, November 11, 2002 2:02 PM Subject: SCHEMA, ARCHITECTURE ??? Hello All, How do I determine the best architecture for my MySQL database? Do I create multiple databases or do I create only one database with multiple tables? (Any general rules of thumb?) How do I know if I need to use Keys, Indexes or other features in order to optimize the database? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: support table synonyms?
Search for aliases in manual.html. PB - - Original Message - From: Phillip Rhodes [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 12, 2002 8:59 PM Subject: support table synonyms? I can not find anywhere that says that mysql supports table name synonyms. For example, I have two databases (test1 and test2) in the same mysql instance. In test1 schema, there is a table called table1 I would like to create a synonym whereby I could refer to test1.table1 as test2.table1 where test2.table1 does not exist. I could do this in oracle as .. create public synonym table1 as test1.table1; 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
version ?
what mysql version is recommended for running w/apache 1.3.26 php 4.2.2? i'm having trouble getting mysql config'd w/these wonder if i should avoid/lean to any particular versions? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: version ?
3.23.55 Production 4.2.2 is ALPHA 4.0.4 is BETA So if you are runing a production server go for 3.* If you can risk it go for 4.0.4 (should be ok) and if you want to have some fun 4.2.2 Simon -Original Message- From: P. Fawcett [mailto:fawcett;bard.edu] Sent: 13 November 2002 16:05 To: [EMAIL PROTECTED] Subject: version ? what mysql version is recommended for running w/apache 1.3.26 php 4.2.2? i'm having trouble getting mysql config'd w/these wonder if i should avoid/lean to any particular versions? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
setting passwd?
after install binary mysql 3.23.49: must be something obvious but i'm missing it - how come i can't set root user passwd? simple syntax error? [root@a bin]# ./mysqladmin -u root -p password pp Enter password: i enter p again ./mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' # - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
min() in where clause
I want to code the following in one statement: select min(field1) from table1; $res=result of the query select * from table1 where field1=$res; does anyone know? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: setting passwd?
pp p Look like you need a 'p' :-) Simon -Original Message- From: P. Fawcett [mailto:fawcett;bard.edu] Sent: 13 November 2002 16:28 To: MySQL Mailinglist Subject: setting passwd? after install binary mysql 3.23.49: must be something obvious but i'm missing it - how come i can't set root user passwd? simple syntax error? [root@a bin]# ./mysqladmin -u root -p password pp Enter password: i enter p again ./mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' # - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
mysqlhotcopy ignores --host or -h option
Description: mysqlhotcopy ignores --host -h option How-To-Repeat: call mysqlhotcopy with a host differs from localhost Fix: Add host|h=s to GetOptions Submitter-Id: submitter ID Originator: Organization: organization of PR author (multiple lines) MySQL support: none Synopsis: synopsis of the problem (one line) Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.53 (Source distribution) Environment: machine, os, target, libraries (multiple lines) System: Linux debmu401 2.4.19 #1 SMP Tue Sep 10 09:00:50 CEST 2002 i686 unknown Architecture: i686 Some paths: /DBA/public/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.2 2.96-108.7.2) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Sep 2 15:53 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 5735106 Aug 7 13:03 /lib/libc-2.2.4.so -rw-r--r--1 root root 27336078 Aug 7 12:56 /usr/lib/libc.a -rw-r--r--1 root root 178 Aug 7 12:57 /usr/lib/libc.so Configure command: ./configure --prefix=/DBA/mysql/SQL/3.23.53 --enable-thread-safe-client - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ver...
tx - i knew the diff between prod/ 'having fun' will stick w/production. just wasn't sure if there were verion conflicts w/the php apache, or it was just user (ie. me) error in config's per below response - that would explain why mysql gets installed 1st. i've heard/read? to stick w/all source, or rpm, or binaries. but does it matter if the php apache are source then the mysql is binary? It is the other way around. You configure PHP and apache to use mysql. Chect the installation documents for PHP and apache for instructions on how to use MySQL. P. Fawcett wrote: what mysql version is recommended for running w/apache 1.3.26 php 4.2.2? i'm having trouble getting mysql config'd w/these wonder if i should avoid/lean to any particular versions? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Decrypt MYSQL Password
Ben C. wrote: I am using the MySQL password() function for the my passwords on the user names. How do I decrypt the password in PHP to send it in an e-mail. I am Short answer: You don't. Long answer: search the archives for this mailing list; it was asked about a week ago and I gave a longer much more accurate answer then. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Order issue with MySQL
Hello All, Using MySQL for sql...webpage forms: I am trying to order the following: 1-03-0A0019-03-0D004; this works fine using simple order by asc. when I added 11-02-0B001, etc. it came after 1-03- 0D004, then continued to rest of order. how do I fix this so that 11-xx-xxx comes after 9-xx- xxx? The first digit is numbered as odd. thanks, Lamar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: setting passwd?
The -p means you are going to enter the CURRENT password. Since you don't have one yet, either leave off the -p, or hit the enter key at the passowrd prompt. P. Fawcett wrote: after install binary mysql 3.23.49: must be something obvious but i'm missing it - how come i can't set root user passwd? simple syntax error? [root@a bin]# ./mysqladmin -u root -p password pp Enter password: i enter p again ./mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' # - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
SPOOL
The MySQL have SPOOL where can I stores query results in a file? How can I do this? How can I show my Indexes, Primary keys and Foreign Keys? best regards, Sil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: min() in where clause
Erwin Ulreich wrote: I want to code the following in one statement: select min(field1) from table1; $res=result of the query select * from table1 where field1=$res; does anyone know? select * from table1 order by field1 desc limit 1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Order issue with MySQL
-- From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] Hello All, Using MySQL for sql...webpage forms: I am trying to order the following: 1-03-0A0019-03-0D004; ... when I added 11-02-0B001, etc. it came after 1-03- 0D004, then continued to rest of order. how do I fix this you could use 01-03-0A00109-03-0D004 Francis Cooke ** Please visit the official Granada website at www.granadamedia.com for the latest company news. This email and any files transmitted are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Thank you. ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication error?
Hi, I currently have my replication setup with 3 machines as such: A B C Machine A just died, so B is now my master. However, I want to re-adjust my setup so it looks like this: B A C So what I did was stop C and copy everything over to A. At this point, either A or C should be able to act as a slave reading from B. However, whenever I start A it starts to skip large chunks of the update log. C doesn't seem to have this problem. Does anyone know what is making this happen? Using: 3.23.49a-Max kernel: 2.4.9-34enterprise Thanks, Adrian mysql query __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order issue with MySQL
Hi, perhaps try : ORDER BY SUBSTRING_INDEX(your_field, '-', 1) asc,SUBSTRING_INDEX(your_field, '-', 2) asc,SUBSTRING_INDEX(your_field, '-', 3) asc; Regards, Jocelyn - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 13, 2002 4:38 PM Subject: Order issue with MySQL Hello All, Using MySQL for sql...webpage forms: I am trying to order the following: 1-03-0A0019-03-0D004; this works fine using simple order by asc. when I added 11-02-0B001, etc. it came after 1-03- 0D004, then continued to rest of order. how do I fix this so that 11-xx-xxx comes after 9-xx- xxx? The first digit is numbered as odd. thanks, Lamar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
create table w/ data/index directory
Hi, I am using Mysql version: 3.23.52 on Red Hat Linux. I tried to create a table as below and got error. Could some one point me what I did wrong? Or the DATA DIRECTORY defination does not really exist? Thanks, Yuyi Guo mysql create table testtable2 (col1 varchar(10) NOT NULL, col2 varchar(10), primary key (col1), index idx1 (col2)) DATA_DIRECTORY=/tmp; ERROR 1064: You have an error in your SQL syntax near 'DATA_DIRECTORY=/tmp' at line 1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: min() in where clause
That's right, I should have checked before clicking on Send ;) gerald_clark wrote: That should not be DESC. That will give the max value. Joseph Bueno wrote: Erwin Ulreich wrote: I want to code the following in one statement: select min(field1) from table1; $res=result of the query select * from table1 where field1=$res; does anyone know? select * from table1 order by field1 desc limit 1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Decrypt MYSQL Password
I am a little new to MySQL and PHP. How do I make the password a temporary password. So when they login the first time they need to reset the password? Thanks for you help in advance. Ben From: Merlin, The Mage [EMAIL PROTECTED] Date: 2002/11/13 Wed AM 04:20:20 EST To: Ben C. [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Decrypt MYSQL Password -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Ben, From the MySQL man online @ http://www.mysql.com/doc/en/Miscellaneous_functions.html#IDX1330 : PASSWORD() encryption is non-reversible. To create a forgot your password page, the best method is to generate a net password and send that new password. To make it easier to your users to remember their passwords, the best way is to set that password as a temporary one, that only allows them to set the original password. (maybe using a diferent field, so they can keep the original password if someone else goes to your site and request the password, just because he was bored. MPNeves On Wednesday 13 November 2002 07:27 am, Ben C. wrote: I am using the MySQL password() function for the my passwords on the user names. How do I decrypt the password in PHP to send it in an e-mail. I am making a forgot your password page and want to have the user enter their e-mail and have the password sent to them. Please help! - -- Merlin, the Mage themage.camelot.co.pt -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE90hleKOMrqDFTeBARAoc7AKDozVXEVP8Tfv8Qjx9DE4ChPF8zsACaAh/C efo5W0z4+dO3UEvTnDz9qJk= =i4Oj -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: create table w/ data/index directory
At 11:21 -0600 11/13/02, Yuyi Guo wrote: Hi, I am using Mysql version: 3.23.52 on Red Hat Linux. The option is DATA DIRECTORY, not DATA_DIRECTORY, but in any case it was introduced in 4.0.0. Your version doesn't support it. I tried to create a table as below and got error. Could some one point me what I did wrong? Or the DATA DIRECTORY defination does not really exist? Thanks, Yuyi Guo mysql create table testtable2 (col1 varchar(10) NOT NULL, col2 varchar(10), primary key (col1), index idx1 (col2)) DATA_DIRECTORY=/tmp; ERROR 1064: You have an error in your SQL syntax near 'DATA_DIRECTORY=/tmp' at line 1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Decrypt MYSQL Password
At 23:27 -0800 11/12/02, Ben C. wrote: I am using the MySQL password() function for the my passwords on the user names. How do I decrypt the password in PHP to send it in an e-mail. I am making a forgot your password page and want to have the user enter their e-mail and have the password sent to them. Please help! It's always a good idea to read the manual before assuming that a function behaves in a certain way. In this case, what you ask is impossible. The manual makes it clear that PASSWORD() encryption is non-reversable: http://www.mysql.com/doc/en/Miscellaneous_functions.html Alternatives: ENCODE()/DECODE() AES_ENCRYPT()/AES_DECRYPT() DES_ENCRYPT()/DES_DECRYPT() - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Decrypt MYSQL Password
Ben, create a column in the user table, that holds a flag called mustChangePassword (or whatever you want to name it). Then write your PHP code that way, that a login of a user who must change his password will be redirected to the change password page and store the new password and reset the flag to false. Regards Hans -Ursprüngliche Nachricht- Von: Ben C. [mailto:benc;cox.net] Gesendet: Mittwoch, 13. November 2002 18:27 An: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Betreff: Re: Decrypt MYSQL Password I am a little new to MySQL and PHP. How do I make the password a temporary password. So when they login the first time they need to reset the password? Thanks for you help in advance. Ben From: Merlin, The Mage [EMAIL PROTECTED] Date: 2002/11/13 Wed AM 04:20:20 EST To: Ben C. [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Decrypt MYSQL Password -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Ben, From the MySQL man online @ http://www.mysql.com/doc/en/Miscellaneous_functions.html#IDX1330 : PASSWORD() encryption is non-reversible. To create a forgot your password page, the best method is to generate a net password and send that new password. To make it easier to your users to remember their passwords, the best way is to set that password as a temporary one, that only allows them to set the original password. (maybe using a diferent field, so they can keep the original password if someone else goes to your site and request the password, just because he was bored. MPNeves On Wednesday 13 November 2002 07:27 am, Ben C. wrote: I am using the MySQL password() function for the my passwords on the user names. How do I decrypt the password in PHP to send it in an e-mail. I am making a forgot your password page and want to have the user enter their e-mail and have the password sent to them. Please help! - -- Merlin, the Mage themage.camelot.co.pt -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE90hleKOMrqDFTeBARAoc7AKDozVXEVP8Tfv8Qjx9DE4ChPF8zsACaAh/C efo5W0z4+dO3UEvTnDz9qJk= =i4Oj -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Order issue with MySQL
Thanks for the reply. I'm not clear on the solution? --- Original Message --- From: Cooke, Francis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: Order issue with MySQL -- From:[EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Hello All, Using MySQL for sql...webpage forms: I am trying to order the following: 1-03-0A0019-03-0D004; ... when I added 11-02-0B001, etc. it came after 1-03- 0D004, then continued to rest of order. how do I fix this you could use 01-03-0A00109-03-0D004 Francis Cooke ** Please visit the official Granada website at www.granadamedia.com for the latest company news. This email and any files transmitted are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Thank you. ** -- --- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail mysql- [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [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: special character
Daya, Wednesday, November 13, 2002, 7:51:53 AM, you wrote: DKD Can naybody tell me how do i ignore special character while insering in DKD mysql database. For example while i m inserting string 'dk\bd\r' it takes \b DKD and \r as diffenrent character, although i can ignore it with one more DKD escape character like this 'dk\\bd\\r'. Field type is Text. Is there any DKD other way to do this. Special characters must be escaped, you can do it with functions in programming languages like addslashes() in PHP; since 4.0.3 you can use QUOTE() function in MySQL: http://www.mysql.com/doc/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
libc read hang?
Hello. I have a problem with mysqld 3.23.49 . My program consists of a father process and n children. The children do a UPDATE at a certain time and the father might do a SELECT at the 'same time'. I suspect strongly that this is the problem. Any ways the father process hangs at random situations that involve the children doing UPDATEs and the father SELECT. I am not sure of this. What could be the problem? /Mattias Program received signal SIGINT, Interrupt. 0x40137544 in __libc_read () from /lib/i686/libc.so.6 (gdb) bt #0 0x40137544 in __libc_read () from /lib/i686/libc.so.6 #1 0x0806fcd0 in ?? () #2 0x0805c46e in my_net_read () #3 0x0805893c in net_safe_read () #4 0x0805a662 in mysql_read_query_result () #5 0x0805b76f in mysql_query () #6 0x0804d678 in dataQuery (mysql=0x806e600, query=0xb680 SELECT ID FROM AccessServer WHERE UPPER('192.168.0.123') LIKE UPPER(AddressPattern), num_rows=0xb6e8, store=1) at datamanager.c:71 #7 0x0804da30 in dataAccessServerHandle (address=0x80772d8 192.168.0.123) at datamanager.c:326 #8 0x0804d5ec in adminClientType (ip_address=0x80772d8 192.168.0.123, dispatcher_handle=0x8077034) at administrator.c:53 #9 0x08049f62 in initClientInfo (client=0x8077298) at controller.c:253 #10 0x0805593f in newClient (socket_fd=13, socket_name=0xb7b0) at client.c:356 #11 0x08055dca in communicateClients (server=0xb910, client_list_top=0x0, timeout=0xb908) at client.c:682 #12 0x0804a142 in startServer () at controller.c:415 #13 0x0804a2d6 in main (argc=4, argv=0xba14) at controller.c:650 #14 0x40073507 in __libc_start_main (main=0x804a25c main, argc=4, ubp_av=0xba14, init=0x804958c _init, fini=0x8062ff0 _fini, rtld_fini=0x4000dc14 _dl_fini, stack_end=0xba0c) at ../sysdeps/generic/libc-start.c:129 -- Mattias Barthel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using MySQL with Perl 5.8?
Hi all, I was able to install the module DBD::mysql for Perl 5.6.1 using ppm (under Windows 2000) but after installing Perl 5.8, ppm can't find this module anymore. I could find only the module DBD::mysqlPP for pure Perl. I don't understand what pure perl means but I've seen that this module accepts less functions than DBD::mysql. Do you know if DBD::mysql works with Perl 5.8 and how can I install it? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Installation Help
I wasn't sure if I was supposed to use 'root' or 'root@localhost' so I did it for both. I'm still getting this: [root@web01 phil]# /usr/local/mysql/bin/mysqladmin version /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' Ugh. :) -Original Message- From: Paul DuBois [mailto:paul;snake.net] Sent: Tuesday, November 12, 2002 7:21 PM To: Black, Kelly W [PCS]; 'Phil Iovino'; 'Scott Pippin' Cc: [EMAIL PROTECTED] Subject: RE: Installation Help At 11:45 -0600 11/12/02, Black, Kelly W [PCS] wrote: Make sure you issued the correct GRANT statements at the sql, query. mysqluse mysql; Database Changed mysql GRANT * ON *.* TO '[EMAIL PROTECTED]' IDENTIFIED BY mysql 'somepassword'; That's not quite right. - GRANT * is not legal, I suspect you mean GRANT ALL. - '[EMAIL PROTECTED]' is quoted incorrectly. That is equivalent to '[EMAIL PROTECTED]'@'%'. I suspect you mean 'userid'@'hostname.com'. (user and host parts should be quoted separately.) #note that will give FULL access...see the docs to restrict this. mysql FLUSH PRIVILEGES; 0 Rows Affected. ~Kelly W. Black - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Installation Help
Right. My mistake. I think you get the general idea. ~K Black -Original Message- From: Paul DuBois [mailto:paul;snake.net] Sent: Tuesday, November 12, 2002 4:21 PM To: Black, Kelly W [PCS]; 'Phil Iovino'; 'Scott Pippin' Cc: [EMAIL PROTECTED] Subject: RE: Installation Help At 11:45 -0600 11/12/02, Black, Kelly W [PCS] wrote: Make sure you issued the correct GRANT statements at the sql, query. mysqluse mysql; Database Changed mysql GRANT * ON *.* TO '[EMAIL PROTECTED]' IDENTIFIED BY 'somepassword'; That's not quite right. - GRANT * is not legal, I suspect you mean GRANT ALL. - '[EMAIL PROTECTED]' is quoted incorrectly. That is equivalent to '[EMAIL PROTECTED]'@'%'. I suspect you mean 'userid'@'hostname.com'. (user and host parts should be quoted separately.) #note that will give FULL access...see the docs to restrict this. mysql FLUSH PRIVILEGES; 0 Rows Affected. ~Kelly W. Black - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrading MySQL from 3.23.41 to 3.23.53a
I would like to upgrade MySQL to the latest production release (3.23.53a) from my current 3.23.41 I have the following packages installed already: mysql-devel-3.23.41-1 mysqlclient9-3.23.22-6 mysql-3.23.41-1 mysql-server-3.23.41-1 What do I need to do in order to upgrade to the newest version? I tried using the RPM facility to install and upgrade the new packages I just downloaded but I get conflicts... ie... [root@frodo MySQL]# rpm --install MySQL-3.23.53a-1.i386.rpm error: failed dependencies: MySQL conflicts with mysql-3.23.41-1 MySQL-server conflicts with mysql-server-3.23.41-1 [root@frodo MySQL]# rpm -U MySQL-3.23.53a-1.i386.rpm error: failed dependencies: MySQL-server conflicts with mysql-server-3.23.41-1 libmysqlclient.so.10 is needed by mysql-server-3.23.41-1 libmysqlclient.so.10 is needed by perl-DBD-MySQL-1.2216-4 [root@frodo MySQL]# So what should I do? Do I need to actually remove the old MySQL first and then install the new one? - Rick Root - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
max-3.23.53a crash?
Hi, Today I had a crash on my production server running Max-3.23.53a It has the following specifications MySQL-Max3.23.53a RedHat 7.3 Kernel 2.4.18-SMP Glibc-2.2.5-42 2GB of memory When I resolv my stack_dump I get the following. Can anyone make something useful out of this? I was not running with any other logging, I've turned it on now if this is going to be happening a lot (remembering the glibc problems...) == resolve_stack_dump -s /usr/lib/mysql/mysqld.sym -n mysqld.stack 0x806eeb4 init_signals__Fv + 16 0x82d9b38 _end + 421148 0x80ada70 test_quick_select__10SQL_SELECTUlUlUlb + 828 0x80af33c key_or__FP7SEL_ARGT0 + 316 0x80aef61 key_and__FP7SEL_ARGT0Ui + 573 0x80ae1fd get_mm_tree__FP13st_qsel_paramP4Item + 1121 0x80adead get_mm_tree__FP13st_qsel_paramP4Item + 273 0x8090ebf make_join_readinfo__FP4JOINUi + 735 0x808ce3a mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP13select_result + 3258 0x8075d90 mysql_execute_command__Fv + 1628 0x8079ecc reload_acl_and_cache__FP3THDUiP13st_table_list + 280 0x8074f34 do_command__FP3THD + 2276 0x80742e7 handle_bootstrap__FPv + 303 The complete log looks like this. = /usr/sbin/mysqld-max: ready for connections mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=402649088 record_buffer=131072 sort_buffer=2097144 max_used_connections=60 max_connections=400 threads_connected=3 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1263608 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: Stack range sanity check OK, backtrace follows: 0x806eeb4 0x82d9b38 0x80ada70 0x80af33c 0x80aef61 0x80ae1fd 0x80adead 0x8090ebf 0x808ce3a 0x8075d90 0x8079ecc 0x8074f34 0x80742e7 Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8c509020 is invalid pointer thd-thread_id=3282450 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 3282450 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash Number of processes running now: 0 021113 18:28:15 mysqld restarted 021113 18:28:15 Can't start server: Bind on TCP/IP port: Address already in use 021113 18:28:15 Do you already have another mysqld server running on port: 3306 ? 021113 18:28:15 Aborting /Lars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Decrypt MYSQL Password
Hi, You can make your own authentification system.A table which can have a binary char/varchar field for encrypted password(effect similar like password from mysql.user) and another char for ASCII password. If you not use grant statement for adding new user, you can use the MySQL authentification system and you can make relation between this table and mysql.user using encrypted password like foreign key. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Merlin, The Mage [EMAIL PROTECTED] To: Ben C. [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, November 13, 2002 7:53 PM Subject: Re: Decrypt MYSQL Password Well, my ideia was to have two diferent fields, the first one to the real password, and the second to the tmp passwd. When the user logs normally, the tmppasswd is set to null, and when the user request a new passwd, this field is set to the temporary password. This will sent an email to the user with a link that allow the user to type the temporary passwd and if correct, set the real one. mpneves On Wednesday 13 November 2002 05:26 pm, Ben C. wrote: I am a little new to MySQL and PHP. How do I make the password a temporary password. So when they login the first time they need to reset the password? Thanks for you help in advance. Ben From: Merlin, The Mage [EMAIL PROTECTED] Date: 2002/11/13 Wed AM 04:20:20 EST To: Ben C. [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Decrypt MYSQL Password -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Ben, From the MySQL man online @ http://www.mysql.com/doc/en/Miscellaneous_functions.html#IDX1330 : PASSWORD() encryption is non-reversible. To create a forgot your password page, the best method is to generate a net password and send that new password. To make it easier to your users to remember their passwords, the best way is to set that password as a temporary one, that only allows them to set the original password. (maybe using a diferent field, so they can keep the original password if someone else goes to your site and request the password, just because he was bored. MPNeves On Wednesday 13 November 2002 07:27 am, Ben C. wrote: I am using the MySQL password() function for the my passwords on the user names. How do I decrypt the password in PHP to send it in an e-mail. I am making a forgot your password page and want to have the user enter their e-mail and have the password sent to them. Please help! - -- Merlin, the Mage themage.camelot.co.pt -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE90hleKOMrqDFTeBARAoc7AKDozVXEVP8Tfv8Qjx9DE4ChPF8zsACaAh/C efo5W0z4+dO3UEvTnDz9qJk= =i4Oj -END PGP SIGNATURE- -- Merlin, the Mage themage.camelot.co.pt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: confirm unsubscribe from mysql@lists.mysql.com
On 13 Nov 2002 18:31:15 - [EMAIL PROTECTED] wrote: Hi! This is the ezmlm program. I'm managing the [EMAIL PROTECTED] mailing list. To confirm that you would like [EMAIL PROTECTED] removed from the mysql mailing list, please send an empty reply to this address: [EMAIL PROTECTED] Usually, this happens when you just hit the reply button. If this does not work, simply copy the address and paste it into the To: field of a new message. I haven't checked whether your address is currently on the mailing list. To see what address you used to subscribe, look at the messages you are receiving from the mailing list. Each message has your address hidden inside its return path; for example, [EMAIL PROTECTED] receives messages with return path: mysql-return-number[EMAIL PROTECTED] Some mail programs are broken and cannot handle long addresses. If you cannot reply to this request, instead send a message to [EMAIL PROTECTED] and put the entire address listed above into the Subject: line. --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, send a message to the address in the List-Unsubscribe header of messages from the list. If you don't know how to display headers and haven't changed E-mail addresses since subscribing, you'll be successful with an e-mail to: [EMAIL PROTECTED] Send mail to the following for info and FAQ for this list: [EMAIL PROTECTED] [EMAIL PROTECTED] Similar addresses exist for the digest list: [EMAIL PROTECTED] [EMAIL PROTECTED] To get messages 123 through 145 (a maximum of 100 per request), mail: [EMAIL PROTECTED] To get an index with subject and author for messages 123-456 , mail: [EMAIL PROTECTED] They are always returned as sets of 100, max 2000 per request, so you'll actually get 100-499. To receive all messages with the same subject as message 12345, send an empty message to: [EMAIL PROTECTED] The messages do not really need to be empty, but I will ignore their content. Only the ADDRESS you send to is important. You can start a subscription for an alternate address, for example [EMAIL PROTECTED], just add a hyphen and your address (with '=' instead of '@') after the command word: [EMAIL PROTECTED] To stop subscription for this address, mail: [EMAIL PROTECTED] In both cases, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete your subscription. If despite following these instructions, you do not get the desired results, please contact my owner at [EMAIL PROTECTED] Please be patient, my owner is a lot slower than I am ;-) --- Enclosed is a copy of the request I received. Return-Path: [EMAIL PROTECTED] Received: (qmail 20638 invoked from network); 13 Nov 2002 18:31:14 - Received: from pc-62-30-156-15-hw.blueyonder.co.uk (HELO mailsys.recruit2recruit.net) (62.30.156.15) by www.mysql.com with SMTP; 13 Nov 2002 18:31:14 - content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Subject: X-MimeOLE: Produced By Microsoft Exchange V6.0.5762.3 Date: Wed, 13 Nov 2002 18:31:13 - Message-ID: [EMAIL PROTECTED] X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Index: AcKLQtgeXVIMlZsBTyaw9YDI5+GXTw== From: Kerin Millar [EMAIL PROTECTED] To: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: create table w/ data/index directory
It doesn't appear this part is valid sql, query DATA_DIRECTORY=/tmp; ~K Black -Original Message- From: Yuyi Guo [mailto:yuyi;fnal.gov] Sent: Wednesday, November 13, 2002 9:21 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: create table w/ data/index directory Hi, I am using Mysql version: 3.23.52 on Red Hat Linux. I tried to create a table as below and got error. Could some one point me what I did wrong? Or the DATA DIRECTORY defination does not really exist? Thanks, Yuyi Guo mysql create table testtable2 (col1 varchar(10) NOT NULL, col2 varchar(10), primary key (col1), index idx1 (col2)) DATA_DIRECTORY=/tmp; ERROR 1064: You have an error in your SQL syntax near 'DATA_DIRECTORY=/tmp' at line 1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
remove primary keys
I'm working with MySQL-Innodb and I have one table with primary key composed by the 5 first fields, and I want to change this removing the primary keys attributes and add one new field in the first position as primary key and auto_increment. How can I do this? best regards, Sil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Installation Help
Sorry I made a mistake as Paul pointed out. I meant GRANT ALL ON *.* TO '[EMAIL PROTECTED]' IDENTIFIED BY 'somepassword'; If you use the localhost substitution for the fully qualified domain name then be sure your /etc/hosts file has a line at the top like 127.0.0.1 localdomain.localhost localhost ~Kelly W. Black -Original Message- From: Phil Iovino [mailto:phil;nxtek.net] Sent: Wednesday, November 13, 2002 10:36 AM To: 'Paul DuBois'; Black, Kelly W [PCS]; 'Scott Pippin' Cc: [EMAIL PROTECTED] Subject: RE: Installation Help I wasn't sure if I was supposed to use 'root' or 'root@localhost' so I did it for both. I'm still getting this: [root@web01 phil]# /usr/local/mysql/bin/mysqladmin version /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' Ugh. :) -Original Message- From: Paul DuBois [mailto:paul;snake.net] Sent: Tuesday, November 12, 2002 7:21 PM To: Black, Kelly W [PCS]; 'Phil Iovino'; 'Scott Pippin' Cc: [EMAIL PROTECTED] Subject: RE: Installation Help At 11:45 -0600 11/12/02, Black, Kelly W [PCS] wrote: Make sure you issued the correct GRANT statements at the sql, query. mysqluse mysql; Database Changed mysql GRANT * ON *.* TO '[EMAIL PROTECTED]' IDENTIFIED BY mysql 'somepassword'; That's not quite right. - GRANT * is not legal, I suspect you mean GRANT ALL. - '[EMAIL PROTECTED]' is quoted incorrectly. That is equivalent to '[EMAIL PROTECTED]'@'%'. I suspect you mean 'userid'@'hostname.com'. (user and host parts should be quoted separately.) #note that will give FULL access...see the docs to restrict this. mysql FLUSH PRIVILEGES; 0 Rows Affected. ~Kelly W. Black - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
InnoDB: Error: tablespace size stored in header
Hi, we're using Innodb here and I just upped the number of datafiles that innodb was using. When I did a show table status I noticed that I didn't get all the space that I had added. When I looked at the error log, I got recieved the following error. InnoDB: Error: tablespace size stored in header is 96 pages, but InnoDB: the sum of data file sizes is 1152000 pages Can anyone help me out here? BTW, we recompiled innodb to use 64kb pages rather then the default of 16kb. Here is the my.cnf we are using... Thanks! --shak innodb_data_file_path = ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:4000M;ibdata5:4000M;ibdata6:4000M;ibdata7:4000M;ibdata8:4000M;ibdata9:4000M;ibdata10:4000M;ibdata11:4000M;ibdata12:4000M;ibdata13:4000M;ibdata14:4000M;ibdata15:4000M;ibdata16:4000M;ibdata17:4000M;ibdata18:4000M innodb_flush_log_at_trx_commit=0 set-variable = innodb_buffer_pool_size=2048M innodb_data_home_dir = /opt/mysql/data/ innodb_log_group_home_dir = /opt/mysql/data/ innodb_log_arch_dir = /opt/mysql/data/ set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=128M set-variable = innodb_log_buffer_size=192M innodb_log_archive=0 innodb_fast_shutdown=1 innodb_flush_method=nosync set-variable = innodb_additional_mem_pool_size=128M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 set-variable = innodb_thread_concurrency=12 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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 server hardware
On Tue, Nov 12, 2002 at 11:22:11PM -0800, Devore, Jacob wrote: Can any of you point me to documentation or have a quick summary of how beefy to make a mysql server. Basically we probably won't have more than 50 connections at one time and we will be pushing packets of 2Mb at times. I'm curious if I should go with tons of ram, faster cpu's, a gig nic, or is it even worth it to upgrade our server. I believe right now it's on a dual 400 p2 with 512Mb of ram and 100Mb connection. Do some benchmarking. I suspect you'll find that your current hardware is adaquate, assuming that MySQL and your operating system are well tuned. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 99 days, processed 2,102,130,626 queries (244/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Installation Help
It does except my hosts file has localhost.localdomain switched around from what you specified. Mine is: 127.0.0.1 web01.nxtek.net web01 localhost.localdomain localhost Any other ideas anyone? I wish the rpm did all this crap. heh -Original Message- From: Black, Kelly W [PCS] [mailto:kblack05;sprintspectrum.com] Sent: Wednesday, November 13, 2002 1:39 PM To: 'Phil Iovino'; 'Paul DuBois'; 'Scott Pippin' Cc: [EMAIL PROTECTED] Subject: RE: Installation Help Sorry I made a mistake as Paul pointed out. I meant GRANT ALL ON *.* TO '[EMAIL PROTECTED]' IDENTIFIED BY 'somepassword'; If you use the localhost substitution for the fully qualified domain name then be sure your /etc/hosts file has a line at the top like 127.0.0.1 localdomain.localhost localhost ~Kelly W. Black -Original Message- From: Phil Iovino [mailto:phil;nxtek.net] Sent: Wednesday, November 13, 2002 10:36 AM To: 'Paul DuBois'; Black, Kelly W [PCS]; 'Scott Pippin' Cc: [EMAIL PROTECTED] Subject: RE: Installation Help I wasn't sure if I was supposed to use 'root' or 'root@localhost' so I did it for both. I'm still getting this: [root@web01 phil]# /usr/local/mysql/bin/mysqladmin version /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' Ugh. :) -Original Message- From: Paul DuBois [mailto:paul;snake.net] Sent: Tuesday, November 12, 2002 7:21 PM To: Black, Kelly W [PCS]; 'Phil Iovino'; 'Scott Pippin' Cc: [EMAIL PROTECTED] Subject: RE: Installation Help At 11:45 -0600 11/12/02, Black, Kelly W [PCS] wrote: Make sure you issued the correct GRANT statements at the sql, query. mysqluse mysql; Database Changed mysql GRANT * ON *.* TO '[EMAIL PROTECTED]' IDENTIFIED BY mysql 'somepassword'; That's not quite right. - GRANT * is not legal, I suspect you mean GRANT ALL. - '[EMAIL PROTECTED]' is quoted incorrectly. That is equivalent to '[EMAIL PROTECTED]'@'%'. I suspect you mean 'userid'@'hostname.com'. (user and host parts should be quoted separately.) #note that will give FULL access...see the docs to restrict this. mysql FLUSH PRIVILEGES; 0 Rows Affected. ~Kelly W. Black - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 the mysql JDBC driver thread safe?
[EMAIL PROTECTED] wrote: Hi all, i'm planning on using servlets for an application I am building What I need to know is if there are any thread safe JDBC drivers? MySQL Connector/J is thread-safe, but even though the JDBC spec states that the driver itself must be thread-safe, most of the API can not be used in a threaded manner. ie, can I open just one connection to the database for a multithreaded servlet? Will the mysql JDBC driver serialize access or allow concurrent statements? Or will I have to worry about synchronization? The driver will serialize access, but you most likely will not want to do that. You will get lower through-put than with multiple connections, and since transactions are mapped to connections, if you ever want to use transactions, you will need to devote one connection per client. You can do this efficiently by using a connection pool, which should be provided by your servlet container. The JDBC API was not really designed to be used in a multi-threaded fashion, so I would avoid it if you can. -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
libc hang
Hello. I have a problem with mysqld 3.23.49 . My program consists of a father process and n children. The children do a UPDATE at a certain time and the father might do a SELECT at the 'same time'. I suspect strongly that this is the problem. Any ways the father process hangs at random situations that involve the children doing UPDATEs and the father SELECT. I am not sure of this. What could be the problem? /Mattias Program received signal SIGINT, Interrupt. 0x40137544 in __libc_read () from /lib/i686/libc.so.6 (gdb) bt #0 0x40137544 in __libc_read () from /lib/i686/libc.so.6 #1 0x0806fcd0 in ?? () #2 0x0805c46e in my_net_read () #3 0x0805893c in net_safe_read () #4 0x0805a662 in mysql_read_query_result () #5 0x0805b76f in mysql_query () #6 0x0804d678 in dataQuery (mysql=0x806e600, query=0xb680 SELECT ID FROM AccessServer WHERE UPPER('192.168.0.123') LIKE UPPER(AddressPattern), num_rows=0xb6e8, store=1) at datamanager.c:71 #7 0x0804da30 in dataAccessServerHandle (address=0x80772d8 192.168.0.123) at datamanager.c:326 #8 0x0804d5ec in adminClientType (ip_address=0x80772d8 192.168.0.123, dispatcher_handle=0x8077034) at administrator.c:53 #9 0x08049f62 in initClientInfo (client=0x8077298) at controller.c:253 #10 0x0805593f in newClient (socket_fd=13, socket_name=0xb7b0) at client.c:356 #11 0x08055dca in communicateClients (server=0xb910, client_list_top=0x0, timeout=0xb908) at client.c:682 #12 0x0804a142 in startServer () at controller.c:415 #13 0x0804a2d6 in main (argc=4, argv=0xba14) at controller.c:650 #14 0x40073507 in __libc_start_main (main=0x804a25c main, argc=4, ubp_av=0xba14, init=0x804958c _init, fini=0x8062ff0 _fini, rtld_fini=0x4000dc14 _dl_fini, stack_end=0xba0c) at ../sysdeps/generic/libc-start.c:129 -- Mattias Barthel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using MySQL with Perl 5.8?
Did you remember to allow backwards compatibility in @INC for perl 5.6.1? ~K Black -Original Message- From: Octavian Rasnita [mailto:orasnita;home.ro] Sent: Wednesday, November 13, 2002 2:58 AM To: MySQL List Subject: Using MySQL with Perl 5.8? Hi all, I was able to install the module DBD::mysql for Perl 5.6.1 using ppm (under Windows 2000) but after installing Perl 5.8, ppm can't find this module anymore. I could find only the module DBD::mysqlPP for pure Perl. I don't understand what pure perl means but I've seen that this module accepts less functions than DBD::mysql. Do you know if DBD::mysql works with Perl 5.8 and how can I install it? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Upgrading MySQL from 3.23.41 to 3.23.53a
You might need to use rpm -e to uninstall previous version first. Check the documentation. ~Kelly W. Black -Original Message- From: Rick Root [mailto:rroot;wakeinternet.com] Sent: Wednesday, November 13, 2002 10:47 AM To: mysql Subject: Upgrading MySQL from 3.23.41 to 3.23.53a I would like to upgrade MySQL to the latest production release (3.23.53a) from my current 3.23.41 I have the following packages installed already: mysql-devel-3.23.41-1 mysqlclient9-3.23.22-6 mysql-3.23.41-1 mysql-server-3.23.41-1 What do I need to do in order to upgrade to the newest version? I tried using the RPM facility to install and upgrade the new packages I just downloaded but I get conflicts... ie... [root@frodo MySQL]# rpm --install MySQL-3.23.53a-1.i386.rpm error: failed dependencies: MySQL conflicts with mysql-3.23.41-1 MySQL-server conflicts with mysql-server-3.23.41-1 [root@frodo MySQL]# rpm -U MySQL-3.23.53a-1.i386.rpm error: failed dependencies: MySQL-server conflicts with mysql-server-3.23.41-1 libmysqlclient.so.10 is needed by mysql-server-3.23.41-1 libmysqlclient.so.10 is needed by perl-DBD-MySQL-1.2216-4 [root@frodo MySQL]# So what should I do? Do I need to actually remove the old MySQL first and then install the new one? - Rick Root - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL load problems.... almost forgot
Almost forgot to say, I'm running mysql with the following command: ./safe_mysql --user=mysql --skip-networking --skip-locking --log-slow-queries - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL load problems.
Hello all, I've been having some problems with MySQL crashing. I use a mixture of MyISAM and InnoDB tables, but most of the data is under InnoDB tables. My web applications all do relatively complex join queries. Almost all fields in the WHERE clauses of queries are indexed. I'm running RedHat 7.2, and MySQL version is 3.23.53a The server is: PIII-866 Mhz, 512 MB RAM 18 GIG SCSI drive (no RAID). The server acts as an Apache server AND as a MySQL server The server seems to work fine and has +- an uptime load of 1 - 1.5 when MySQL is running arround 20 queries/sec When it reaches about 30-40 queries/sec, the machine goes to 3-5 load in uptime, often registers slow queries, and eventually dies without any error messages (except that the socket file doesn't exist - i.e. crash). How should I set my memory usage in my.cnf I have a hard time understanding how MySQL works with memory, how it manages it, etc. What should I consider when setting my memory settings??? I have included a paste of my.cnf # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=128M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= net_buffer_length=16K set-variable= myisam_sort_buffer_size=96M set-variable= thread_cache=8 set-variable= max_connections=500 log-bin server-id = 1 # Uncomment the following if you are using Innobase tables innodb_data_file_path = innodb/ibdata1:1000M:autoextend innodb_data_home_dir = /usr/local/mysql/data/ innodb_log_group_home_dir = /usr/local/mysql/data/innodb innodb_log_arch_dir = /usr/local/mysql/data/innodb set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=32M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=160M set-variable = innodb_additional_mem_pool_size=96M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable= max_allowed_packet=128M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: remove primary keys
On Wed, Nov 13, 2002 at 05:03:38PM -0200, Silmara wrote: I'm working with MySQL-Innodb and I have one table with primary key composed by the 5 first fields, and I want to change this removing the primary keys attributes and add one new field in the first position as primary key and auto_increment. How can I do this? I'd suggest using the ALTER TABLE command as described in the manual. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 99 days, processed 2,102,337,423 queries (244/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqlhotcopy point-in-time recovery
On Wed, Nov 13, 2002 at 01:18:18PM -0500, Michael T. Babcock wrote: Jeremy Zawodny wrote: Well, it handles ISAM, MyISAM, and BDB (last I checked). So what should it be called? mysql_isam_myisam_bdb_hotcopy? Heheh, point taken. It seems simply unfortunate that the program in question doesn't work with exactly one table type (Gemini as well perhaps?). A notice on the help screen (at the command-line man page) stating table type compatibility would be most helpful though, since it simply says db_name, not db_name must be MyISAM, ISAM or BDB. Thoughts? Yeah, the docs should be made more clear. A patch is in order. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 99 days, processed 2,101,971,725 queries (244/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql, ODBC and PHP
I have a windows 2000 adv server that is running mysql and php in other to interact with couple of other Microsoft Access database. However, I have a challenge with the function odbc_fetch_array( ) which is returning a endefined function error when used with the ODBC connection. I have used mysql_fetch_array( ) with Mysql connection and it works fine. I have checked the php site for documentation on this function but there is none. Can anybody help. Thanx Toks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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 server hardware
If you ran into any problems it would probably be RAM. RAM is cheap so if you have trouble...just pop a couple of 512MB chips in it. ~KB -Original Message- From: Jeremy Zawodny [mailto:Jeremy;Zawodny.com] Sent: Wednesday, November 13, 2002 11:12 AM To: Devore, Jacob Cc: '[EMAIL PROTECTED]' Subject: Re: mysql server hardware On Tue, Nov 12, 2002 at 11:22:11PM -0800, Devore, Jacob wrote: Can any of you point me to documentation or have a quick summary of how beefy to make a mysql server. Basically we probably won't have more than 50 connections at one time and we will be pushing packets of 2Mb at times. I'm curious if I should go with tons of ram, faster cpu's, a gig nic, or is it even worth it to upgrade our server. I believe right now it's on a dual 400 p2 with 512Mb of ram and 100Mb connection. Do some benchmarking. I suspect you'll find that your current hardware is adaquate, assuming that MySQL and your operating system are well tuned. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 99 days, processed 2,102,130,626 queries (244/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Upgrading MySQL from 3.23.41 to 3.23.53a
At 13:47 2002-11-13 -0500, Rick Root wrote: [root@frodo MySQL]# rpm --install MySQL-3.23.53a-1.i386.rpm error: failed dependencies: MySQL conflicts with mysql-3.23.41-1 MySQL-server conflicts with mysql-server-3.23.41-1 [root@frodo MySQL]# rpm -U MySQL-3.23.53a-1.i386.rpm error: failed dependencies: MySQL-server conflicts with mysql-server-3.23.41-1 libmysqlclient.so.10 is needed by mysql-server-3.23.41-1 So what should I do? Do I need to actually remove the old MySQL first and then install the new one? You are trying to replace the redhat installed mysql with MySQL:s rpms. Do things in this order and it should work (I don't promise it will work =) 1. do a full mysqldump of all your databases. 2. shutdown and remove mysql with rpm -e your-old-package 3. Install the new mysql, start it and check if your databases are intact. Else restore from dumps. regards /Lars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL load problems.... almost forgot
Almost forgot to say, I'm running mysql with the following command: ./safe_mysql --user=mysql --skip-networking --skip-locking --log-slow-queries - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL load problems.
Hello all, I've been having some problems with MySQL crashing. I use a mixture of MyISAM and InnoDB tables, but most of the data is under InnoDB tables. My web applications all do relatively complex join queries. Almost all fields in the WHERE clauses of queries are indexed. I'm running RedHat 7.2, and MySQL version is 3.23.53a The server is: PIII-866 Mhz, 512 MB RAM 18 GIG SCSI drive (no RAID). The server acts as an Apache server AND as a MySQL server The server seems to work fine and has +- an uptime load of 1 - 1.5 when MySQL is running arround 20 queries/sec When it reaches about 30-40 queries/sec, the machine goes to 3-5 load in uptime, often registers slow queries, and eventually dies without any error messages (except that the socket file doesn't exist - i.e. crash). How should I set my memory usage in my.cnf I have a hard time understanding how MySQL works with memory, how it manages it, etc. What should I consider when setting my memory settings??? I have included a paste of my.cnf # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=128M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= net_buffer_length=16K set-variable= myisam_sort_buffer_size=96M set-variable= thread_cache=8 set-variable= max_connections=500 log-bin server-id = 1 # Uncomment the following if you are using Innobase tables innodb_data_file_path = innodb/ibdata1:1000M:autoextend innodb_data_home_dir = /usr/local/mysql/data/ innodb_log_group_home_dir = /usr/local/mysql/data/innodb innodb_log_arch_dir = /usr/local/mysql/data/innodb set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=32M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=160M set-variable = innodb_additional_mem_pool_size=96M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable= max_allowed_packet=128M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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 server hardware
Where can I get a tuning guide for MySQL (MyISAM and InnoDB)... memory-wise and son on? At 11:11 13/11/02 -0800, you wrote: On Tue, Nov 12, 2002 at 11:22:11PM -0800, Devore, Jacob wrote: Can any of you point me to documentation or have a quick summary of how beefy to make a mysql server. Basically we probably won't have more than 50 connections at one time and we will be pushing packets of 2Mb at times. I'm curious if I should go with tons of ram, faster cpu's, a gig nic, or is it even worth it to upgrade our server. I believe right now it's on a dual 400 p2 with 512Mb of ram and 100Mb connection. Do some benchmarking. I suspect you'll find that your current hardware is adaquate, assuming that MySQL and your operating system are well tuned. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 99 days, processed 2,102,130,626 queries (244/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB: Error: tablespace size stored in header
Shakeel, - Original Message - From: Shakeel Sorathia [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, November 13, 2002 9:39 PM Subject: InnoDB: Error: tablespace size stored in header Hi, we're using Innodb here and I just upped the number of datafiles that innodb was using. When I did a show table status I noticed that I didn't get all the space that I had added. When I looked at the error log, I got recieved the following error. InnoDB: Error: tablespace size stored in header is 96 pages, but InnoDB: the sum of data file sizes is 1152000 pages Can anyone help me out here? BTW, we recompiled innodb to use 64kb pages rather then the default of 16kb. Here is the my.cnf we are using... the support for 16 kB pages is not tested. Big pages were used in 2001 to let InnoDB store BLOBs of size up to 30 kB. But when big BLOBs up to 4 GB were implemented, that was no longer needed. I recommend keeping the page size at the default 16 kB. The error message above can occur if mysqld crashes or fails when you add a new data file. Then it may be that the new data file already got created, but the tablespace size in the tablespace header in the first data file was not yet updated. In that case you normally can remove the extra data file and create a new. But in your case this might also be some bug which surfaces because of the 64 kB page size. 96 pages = 60 000 MB 1152000 pages = 72 000 MB Did you add the last 3 data files manually, and did not let InnoDB to create them and write them full? If yes, that explains why the tablespace size in the tablespace header was not updated. Thanks! --shak Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query innodb_data_file_path = ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:4000M;ibdata5:4000M;ibdata 6:4000M;ibdata7:4000M;ibdata8:4000M;ibdata9:4000M;ibdata10:4000M;ibdata11:40 00M;ibdata12:4000M;ibdata13:4000M;ibdata14:4000M;ibdata15:4000M;ibdata16:400 0M;ibdata17:4000M;ibdata18:4000M innodb_flush_log_at_trx_commit=0 set-variable = innodb_buffer_pool_size=2048M innodb_data_home_dir = /opt/mysql/data/ innodb_log_group_home_dir = /opt/mysql/data/ innodb_log_arch_dir = /opt/mysql/data/ set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=128M set-variable = innodb_log_buffer_size=192M innodb_log_archive=0 innodb_fast_shutdown=1 innodb_flush_method=nosync set-variable = innodb_additional_mem_pool_size=128M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 set-variable = innodb_thread_concurrency=12 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
a question about mysql++
Hi, I am trying to comile a c++ program but I got an error message as below: sqlplus.hh: No such file or directory I have mysql server and client 3.23... on rh 7.2 and I have installed binary RPM for SuSE 8.* built by gcc 3.2 ../Downloads/mysql++/mysql++-1.7.9gcc3.2-2.i386.rpm by: rpm -ivh mysql++-1.7.8gcc3.2-2.i386.rpm I checked sqlplus.hh file and it is under /usr/include/sqlplus directory. Any help would be appreciated, Jing [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: Changing character sets and 'myisamchk -dvv'
At 23:23 -0500 11/12/02, [EMAIL PROTECTED] wrote: The MySQL documentation (4.6.1) says: You can change the character set with the --default-character-set option when you start the server. The character sets available depend on the --with-charset=charset and --with-extra-charsets= list-of-charset | complex | all options to configure, and the character set configuration files listed in `SHAREDIR/charsets/Index'. See section 2.3.3 Typical configure Options. If you change the character set when running MySQL (which may also change the sort order), you must run myisamchk -r -q on all tables. Otherwise, your indexes may not be ordered correctly. I have a MySQL server with databases that need to begin supporting japanese characters. Per the advice cited above, I've shut it down, restarted it with '--with charset=sjis', and run 'myisamchk -r -q' on all tables. All seems well enough. But when I run 'myisamchk -dvv tablename', the 'Character Set' line in the output indicates that the character set for tables created before I added '--with charset=sjis' to the safe_mysqld startup script is still 'latin1', whereas for newly created tables, myisamchk reports the character set is 'sjis'. The fact that the output is different for tables created before and after the change was made suggests to me that I'm over- looking something. When you run myisamchk -r -q, you should also add the --set-character-set=sjis option so that myisamchk knows what character set to use. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To 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, ODBC and PHP
Hi It's not a MySQL issue, it's a web server configuration problem. Check you php.ini file and unquote in the section: ;Windows Extensions the line accordingly with the DB connexion you're using. MySQL is set by default. Also try the function odbc_fetch_into() -Mensaje original- De: Tokunboh Oladejo [mailto:tokunboh.oladejo;homeownersdream.com] Enviado el: miércoles, 13 de noviembre de 2002 14:11 Para: '[EMAIL PROTECTED]' Asunto: Mysql, ODBC and PHP I have a windows 2000 adv server that is running mysql and php in other to interact with couple of other Microsoft Access database. However, I have a challenge with the function odbc_fetch_array( ) which is returning a endefined function error when used with the ODBC connection. I have used mysql_fetch_array( ) with Mysql connection and it works fine. I have checked the php site for documentation on this function but there is none. Can anybody help. Thanx Toks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
turn off auto commit in MYSQL
Hi, I am newbie to MYSQL and ODBC I have to update more than 2 tables simultanously currently my application uses ADOBC connection object. How can turn off the autocommit option and set manaul commit. what places I need to change inorder to work manual commit Thanks in Advance for your time Devi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
compiling problem with mysql++
Hi Guys, I am trying to comile a c++ program with mysql++ but I got an error message: In file included from /usr/include/sqlplus/coldata1.hh:10, from /usr/include/sqlplus/sqlplus.hh:12, from createtable.cc:3, /usr/include/sqlplus/null1.hh:4:19: ostream: No such file or directory I have mysql server and client 3.23... on rh 7.2 and I have installed binary RPM for SuSE 8.* built by gcc 3.2 ../Downloads/mysql++/mysql++-1.7.9gcc3.2-2.i386.rpm by: rpm -ivh mysql++-1.7.8gcc3.2-2.i386.rpm I checked sqlplus.hh file and it is under /usr/include/sqlplus directory. My gcc version is 2.96 Any help would be appreciated, Jing [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
create database not replicated
MySQL folks, I am having a problem with replication. When I create a new database on the master, the CREATE DATABASE command is not shipped to the slave. Subsequent table creation and table updates on the new database on the master are replicated on the slave, but since the new database was not created on the slave, the slave thread hangs. In this example the new database is called zzz. This is what I see in the binary log (via mysqlbinlog): ... # at 18236 #021108 14:06:48 server id 5 Query thread_id=72exec_time=0 error_code=0 use zzz; SET TIMESTAMP=1036782408; create table abc (id int); # at 18289 #021108 14:08:21 server id 5 Query thread_id=72exec_time=0 error_code=0 SET TIMESTAMP=1036782501; insert into abc values (100); # at 18345 #021108 14:11:52 server id 5 Intvar SET INSERT_ID = 4933; # at 18367 #021108 14:11:52 server id 5 Query thread_id=238 exec_time=0 error_code=0 ... Note that there is a use zzz, but no create database zzz. The error from show slave status on the slave is error 'Can't create/write to file './zzz/abc.frm' (Errcode: 2)' on query 'create table abc (id int)' Is this a problem? Feature? The master is running 3.23.53a-Max-log. The slave 3.23.49-log. -- Mark -- Mark M. Ito, Thomas Jefferson National Accelerator Facility 12000 Jefferson Ave., Mail Stop 12H, Newport News, VA 23606 Email: [EMAIL PROTECTED], Phone: (757)269-5295 WWW: http://claspc2.jlab.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fw: SCHEMA, ARCHITECTURE ???
if you can affect closure without artificial constructs, then it probably belongs in a single database. if closure can't be affected in the logical design, then you probably need multiple databases. if you don't understand the concept of a relational database closure, refer to http://www.coremodel.com mysql - Original Message - From: William Martell [EMAIL PROTECTED] To: MySQL Main List [EMAIL PROTECTED] Sent: Monday, November 11, 2002 2:02 PM Subject: SCHEMA, ARCHITECTURE ??? Hello All, How do I determine the best architecture for my MySQL database? Do I create multiple databases or do I create only one database with multiple tables? (Any general rules of thumb?) How do I know if I need to use Keys, Indexes or other features in order to optimize the database? -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.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
trouble with LOAD command
How do I LOAD a text file into a table that has a Primary Key defined? I have the following table defined: mysql describe phoneList; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(11) | | PRI | NULL| auto_increment | | First_Name | varchar(20) | YES | | NULL|| | Last_Name| varchar(20) | YES | | NULL|| | Phone_Number | varchar(20) | YES | | NULL|| +--+-+--+-+-++ I'm attempting to load a comma delimited list using load. The text file looks like this: Abe,Lincoln,8347 Herb,Albert,9387 George,Washington,9283 ... When I do this: mysql load data local infile /home/cwalcott/PhoneList_b.txt into table phoneList - fields terminated by ',' (First_Name, Last_Name, Phone_Number); I get this on select * ++++--+ | ID | First_Name | Last_Name | Phone_Number | ++++--+ |e| Lincoln| 8347 |rb | Albert | 9387 |orge | Washington | 9283 ++++--+ if I do this: load data local infile /home/cwalcott/PhoneList_b.txt into table phoneList; I get: +++---+--+ | ID | First_Name | Last_Name | Phone_Number | +++---+--+ | 1 | NULL | NULL | NULL | | 2 | NULL | NULL | NULL | | 3 | NULL | NULL | NULL | +++---+--+ I've also tried using a text file with the first column set to index numbers but the results are very similar. If I do this: mysql load data local infile /home/cwalcott/PhoneList_small.txt into table phoneList - fields terminated by ',' (First_Name, Last_Name, Phone_Number); I get: +++---+--+ | ID | First_Name | Last_Name | Phone_Number | +++---+--+ | 1 | 1 | Abe | Lincoln | | 2 | 2 | Herb | Albert | | 3 | 3 | George| Washington | +++---+--+ If I do this: mysql load data local infile /home/cwalcott/PhoneList_small.txt into table phoneList - fields terminated by ',' (ID, First_Name, Last_Name, Phone_Number); I get this: ++++--+ | ID | First_Name | Last_Name | Phone_Number | ++++--+ |e| Lincoln| 8347 |rb | Albert | 9387 |orge | Washington | 9283 +-++---+--+ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: trouble with LOAD command
I'd pick up the text file and search/replace all end of line character(s) with a end of line00 comma combination so it reads 00,Abe,Lincoln,8347 on each line and try your first method. If that fails, I would drop the ID field off the table, then do the import with original file, then atlter the table again to add the ID field Alan -Original Message- From: Chris Walcott [mailto:cwalcott;macromedia.com] Sent: Thursday, 14 November 2002 9:42 To: Mysql-L (E-mail) Subject: trouble with LOAD command How do I LOAD a text file into a table that has a Primary Key defined? I have the following table defined: mysql describe phoneList; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(11) | | PRI | NULL| auto_increment | | First_Name | varchar(20) | YES | | NULL|| | Last_Name| varchar(20) | YES | | NULL|| | Phone_Number | varchar(20) | YES | | NULL|| +--+-+--+-+-++ I'm attempting to load a comma delimited list using load. The text file looks like this: Abe,Lincoln,8347 Herb,Albert,9387 George,Washington,9283 ... When I do this: mysql load data local infile /home/cwalcott/PhoneList_b.txt into table phoneList - fields terminated by ',' (First_Name, Last_Name, Phone_Number); I get this on select * ++++--+ | ID | First_Name | Last_Name | Phone_Number | ++++--+ |e| Lincoln| 8347 |rb | Albert | 9387 |orge | Washington | 9283 ++++--+ if I do this: load data local infile /home/cwalcott/PhoneList_b.txt into table phoneList; I get: +++---+--+ | ID | First_Name | Last_Name | Phone_Number | +++---+--+ | 1 | NULL | NULL | NULL | | 2 | NULL | NULL | NULL | | 3 | NULL | NULL | NULL | +++---+--+ I've also tried using a text file with the first column set to index numbers but the results are very similar. If I do this: mysql load data local infile /home/cwalcott/PhoneList_small.txt into table phoneList - fields terminated by ',' (First_Name, Last_Name, Phone_Number); I get: +++---+--+ | ID | First_Name | Last_Name | Phone_Number | +++---+--+ | 1 | 1 | Abe | Lincoln | | 2 | 2 | Herb | Albert | | 3 | 3 | George| Washington | +++---+--+ If I do this: mysql load data local infile /home/cwalcott/PhoneList_small.txt into table phoneList - fields terminated by ',' (ID, First_Name, Last_Name, Phone_Number); I get this: ++++--+ | ID | First_Name | Last_Name | Phone_Number | ++++--+ |e| Lincoln| 8347 |rb | Albert | 9387 |orge | Washington | 9283 +-++---+--+ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: trouble with LOAD command
Chris Walcott wrote: How do I LOAD a text file into a table that has a Primary Key defined? Most stuff snipped. Sorry :) I drop the Primary Key (and Timestamp) when I use 'load data infile'. Below is my VB code which I use in Access to import stuff into MySQL. Maybe someone will find it useful. The point is that you have to drop the PK before the import, and add it back later. If you can't do this, because you already have data in the table and don't want to mess up the PK, then import the data into a tmp table and then do a 'insert into' command to append it where you want it. Function MySQLImport(source As String, Destination As String, _ FieldTerminator As String, Optional OptionalWrapper As String, _ Optional IgnoreLines As Integer, Optional LineTerminator As String, _ Optional NoTruncate As Boolean, Optional KeepPrimaryKey As Boolean, _ Optional KeepMyStamp As Boolean) Dim myconn As ADODB.connection, mycommand As ADODB.command, sqlstr As String sqlstr = DRIVER={MySQL ODBC 3.51 Driver}; _ SERVER=vortex; _ DATABASE=mydatabase; _ UID=me; _ PASSWORD=mypassword Set myconn = New ADODB.connection With myconn .ConnectionString = sqlstr .Open End With Set mycommand = New ADODB.command mycommand.ActiveConnection = myconn If NoTruncate = False Then sqlstr = truncate table Destination mycommand.CommandText = sqlstr mycommand.Execute End If ' Drop primary key timestamp for import... If KeepPrimaryKey And KeepMyStamp Then ' Nothing... Else sqlstr = alter table Destination If Not KeepPrimaryKey Then sqlstr = sqlstr drop primary key, _ drop DanPK, End If If Not KeepMyStamp Then sqlstr = sqlstr drop MyStamp End If mycommand.CommandText = sqlstr mycommand.Execute End If ' Import... sqlstr = load data infile _ '/usr/local/bills_on_disk/ElectronicBills2MySQL/ source ' _ into table Destination _ fields terminated by ' FieldTerminator ' _ IIf(Len(OptionalWrapper) = 0, , optionally enclosed by ' Chr(34) ') _ IIf(Len(LineTerminator) = 0, , lines terminated by ' LineTerminator ') _ IIf(Len(IgnoreLines) = 0, , ignore IgnoreLines lines) mycommand.CommandText = sqlstr mycommand.Execute ' Add Primary Key Timestamp so Access can get hold of it... If KeepPrimaryKey And KeepMyStamp Then ' Nothing Else sqlstr = alter table Destination If Not KeepPrimaryKey Then sqlstr = sqlstr add DanPK mediumint(8) unsigned NOT NULL auto_increment first, _ add primary key (DanPK), End If If Not KeepMyStamp Then sqlstr = sqlstr add MyStamp timestamp(14) not null after DanPK End If mycommand.CommandText = sqlstr mycommand.Execute End If myconn.Close Set myconn = Nothing Set mycommand = Nothing End Function -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.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
mysql4 starts then stops
Hi, If this is a FAQ, I haven't been able to find it. I am a newbie to MySQL, but I've followed the directions carefully and I get this whenever I start mysqld: # bin/safe_mysqld --user=mysql [1] 24487 zaphod:/usr/local/mysql[15:02]# Starting mysqld daemon with databases from /var/lib/mysql 021113 15:02:18 mysqld ended [1] + 24487 done bin/safe_mysqld --user=mysql There is nothing in the log that suggest why the daemon ends immediately. Any further attempts to connect to mysql result in errors: # mysql/bin/mysqld version Fatal error: Please read Security section of the manual to find out how to run mysqld as root! 021113 15:07:13 Aborting 021113 15:07:13 mysql/bin/mysqld: Shutdown Complete There doesn't appear to be any reason for this listed in the Security section of the manual. # ./bin/mysqladmin -u root -h zaphod.randori.com password 'new-password' ./bin/mysqladmin: connect to server at 'zaphod.randori.com' failed error: 'Can't connect to MySQL server on 'zaphod.randori.com' (111)' Check that mysqld is running on zaphod.randori.com and that the port is 3306. You can check this by doing 'telnet zaphod.randori.com 3306' # telnet zaphod.randori.com 3306 Trying 209.155.82.57... telnet: connect to address 209.155.82.57: Connection refused Also, why is this file lost? It appears that you *have* to be in /usr/local (or whereever your root is) to run mysqld. # bin/!! bin/mysqld version 021113 15:04:41 Can't find messagefile 'src/mysql-max-4.0.3-beta-pc-linux-gnu-i686/share/mysql/english/errmsg.sys' 021113 15:04:41 Aborting Any help would be greatly appreciated. -Kevin L. Gross Randori News Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Changing character sets and 'myisamchk -dvv'
At 23:23 -0500 11/12/02, [EMAIL PROTECTED] wrote: The MySQL documentation (4.6.1) says: You can change the character set with the --default-character-set option when you start the server. The character sets available depend on the --with-charset=charset and --with-extra-charsets= list-of-charset | complex | all options to configure, and the character set configuration files listed in `SHAREDIR/charsets/Index'. See section 2.3.3 Typical configure Options. If you change the character set when running MySQL (which may also change the sort order), you must run myisamchk -r -q on all tables. Otherwise, your indexes may not be ordered correctly. I have a MySQL server with databases that need to begin supporting japanese characters. Per the advice cited above, I've shut it down, restartedit with '--with charset=sjis', and run 'myisamchk -r -q' on all tables. All seems well enough. But when I run 'myisamchk -dvv tablename', the 'Character Set' line in the output indicates that the character set for tablescreated before I added '--with charset=sjis' to the safe_mysqld startup scriptis still 'latin1', whereas for newly created tables, myisamchk reports the character set is 'sjis'. The fact that the output is different for tables created before and after the change was made suggests to me that I'm over- looking something. When you run myisamchk -r -q, you should also add the --set-character-set=sjis option so that myisamchk knows what character set to use. Okay, but after 'myisamchk -r -q --set-character-set=sjis table' is run, 'myisamchk -dvv table' still reports the character set of the table as being the old 'latin1', not 'sjis', so I'm still thinking I may need to drop and recreate all my tables. Thanks for your reply. -Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqlhotcopy point-in-time recovery
Michael, Jeremy, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, November 13, 2002 9:21 PM Subject: Re: mysqlhotcopy point-in-time recovery On Wed, Nov 13, 2002 at 01:18:18PM -0500, Michael T. Babcock wrote: Jeremy Zawodny wrote: Well, it handles ISAM, MyISAM, and BDB (last I checked). So what should it be called? mysql_isam_myisam_bdb_hotcopy? Heheh, point taken. It seems simply unfortunate that the program in question doesn't work with exactly one table type (Gemini as well perhaps?). A notice on the help screen (at the command-line man page) stating table type compatibility would be most helpful though, since it simply says db_name, not db_name must be MyISAM, ISAM or BDB. Thoughts? I do not know if for BDB tables the copy is transaction-consistent. I mean, can there be uncommitted transactions in the image which the mysqlhotcopy program makes of the database? Then also the BDB log files should be copied, but what happens if there is activity in other BDB databases at the same time? Yeah, the docs should be made more clear. A patch is in order. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 99 days, processed 2,101,971,725 queries (244/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
(Fwd) Mail Delivery Failure.
--- Forwarded message follows --- From: Mail Delivery System To: Self Subject:Mail Delivery Failure. Date sent: Thu, 14 Nov 2002 06:50:33 Delivery has failed on the enclosed message for the following reasons reported either by the mail delivery system on the mail relay host or by the local TCP/IP transport module: 550 5.7.1 [EMAIL PROTECTED]... we do not relay [EMAIL PROTECTED] Your original mail message follows: X-PM-Identity: Default From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Thu, 14 Nov 2002 06:49:06 +0530 MIME-Version: 1.0 Subject: Thank yous for MYSQL Message-ID: 3DD34762.14287.3C2936@localhost Priority: normal X-mailer: Pegasus Mail for Windows (v4.01) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body Dear friends at MYSQL, I wish to thank you very much for making MYSQL such a well made, effective, top quality database management program and most especially for making it available free and legal for all to use. This is such a very different and uplifting energy and spirit than the crushing, controlling and secretive and hording energy of so many only commercially interested consciousnesses epitomised by Microsoft. That you are still able to make a true succesful business from a GNu liscenced software is wonderful. WE here at The Sri Aurobindo Ashram's computer dept. are much appreciative of all your efforts and skills and sharing attitudes. We are trying to learn how to use MYSQL and are enjoying all the hard work and dedication you have put into it. We are a small group and with little training and experience but if we can be of help in contributing to the growth and perfection of the MYSQL program in some way we would be happy. Even if we could just proof read the english manual or help files. It is just that we wish to congratulate you on your aspiration and perserverence, your dedication and commitment, your self giving and leadership. Thank you again for bringing something good, helpful, bright, and uplifting to the world. Markandeya Aurokruti Computer Dept. Sri Aurobindo Ashram, Pondicherry,South India 605002 --- End of forwarded message --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
character set conversion and LOAD DATA INFILE
I reconfigured and recompiled MySQL 3.23.53 that is supports now conversion between Windows-1250 and ISO-8859-2 charsets. If I do 'set character set cp1250_latin2' in a session, then everything goes all right BUT only in insert/update/select statements. If I do the same set and use LOAD DATA INFILE file then conversion is omitted. Why ? How can I convert charset in file while importing to MySQL db ? filter:sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
why it run so slowly
Dear Sir: i am a user of mysql gui,version mysqlgui-win32-static-1.7.5-2. My pc is Petium 4 1.4G, on which most program run very fast. but when i use mysql gui, i find it run very slowly although i can get the result. I wonder how can i make it run faster. thank you for your helping. My OS: WIN98 SE Jack __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.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
access db port to mysql
Hello everyone I was wondering if there are any tools that would allow me to port a access database to mysql? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: access db port to mysql
When you say port are you asking to export the access DB to mysql? If so, Intelligent Converters makes one and you can also do it with EMS MySQL manager. If you wanting to view the mysql data in access; you can do this using myodbc and open it using external data using the ODBC source. -Original Message- From: Antoine [mailto:asolomon15;nyc.rr.com] Sent: Wednesday, November 13, 2002 10:16 PM To: [EMAIL PROTECTED] Subject: access db port to mysql Hello everyone I was wondering if there are any tools that would allow me to port a access database to mysql? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INDEX directory option with no effect
Hi! We are using mysql, version 4.0.4 beta on windows 2000 Server. I tried to put data und index on different drives, usind the DATA and INDEX directory option of the create table statement. But both was created in the default data directory. Is there anybody, who already used it successfully? Bye, Kai - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Lost connection...
Hi I'm using RedHat 8.0 and MySQL 3.23.52. I've also downloaded the MySQL Control Center (latest). On the Linux mashine everything works just fine, but when I try to connect to MySQLserver from Win2k using alpha version of MySQL Control Center I get ERROR 2013: Lost connection to MySQL server during query. This is very odd. I know I have all the settings correct and I've tried different users and pw. I'm using default port (3306) and checked that Linux computer really listens on that port. I've also installed the MySQL O DBC driver. But the exact same error-message accours when I try to connect via the ODBC-driver in ASP from the Win2k computer. I REALLY would appriciate help regarding this matter. Thanks in advance Staffan, Sweden - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php