after upgrade to 4.1 - error in multi delete
Hi there, I have recently upgraded from mysql 4.0 to 4.1.x Now I am facing an error while issuing this delete command: # delete report images in db $stmt= DELETE FROM $DB.$T21 USING $DB.$T21 AS rp, $DB.$T13 AS r WHERE r.ID = rp.recommendations_id AND r.user_recommending = '$user_id' ; Error: 1109 Unknown table 'the_table_name' in MULTI DELETE Has there something changed in the syntax? Thank you for any help, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql takes server down
Hi everybody, I am running a LAMP installation with mysql 3.x installed. Since a few hours the szstem load goes up till the server crashes. I have to reboot it, and then 60 minutes later it is gone again! Server is running now for over 2 years. I do have the impession that mysql is the reason for it. Since I have disabled the webapp, but as soon as I start mysql the system starts going down. has anybody an idea what is going wrong? Thank you for any help, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Enabling loging on mysql 3.x
Hi there, I am trying to enable two things: 1. Slow query log 2. Error log Despite the fact that I have enabled slow query log in my.cnf and the -var command says it is enabled the file is not existing. So I was searching for the error log, but there is none? :-( Can somebody please help me with some hints how to enable both of the log files? thank you for any help, merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default root password not blank?
Hi there, I just compiled mysql4.1.22 on a suse 9.3 machine. All went fine, but when I try to change the password I get prompted for one. So I hit return as it is supposed to be blank, right? But it seems to be not: /usr/local/mysql/bin # ./mysqladmin -u root -p password testpw Enter password: ./mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' if I do the command without -p I get: ./mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Is there a special command for blank? As I noted using password NO for hitting return. Thank you for any hint, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to slow after inserting another table
Well this is exactly the problem. The OR statement. Do you think there is another way around the tmp table. I did not make the best experiances with tmp tables. regards, merlin On Sat, 27 May 2006 00:26:09 -0500, mos [EMAIL PROTECTED] said: Merlin, Lose the OR part of the Where clause and it should speed up. If so, that's what you have to work on. You could execute 2 queries, where each one writes the results to a temporary memory table and display that table instead. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Merlin [EMAIL PROTECTED] -- http://www.fastmail.fm - Same, same, but different -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to slow after inserting another table
Hi, thank you for this hint. That worked excellent! Now the overall query time is about 0.1 s on average. Best regards, Merlin On Sat, 27 May 2006 02:14:34 -0700, Merlin [EMAIL PROTECTED] said: Well this is exactly the problem. The OR statement. Do you think there is another way around the tmp table. I did not make the best experiances with tmp tables. regards, merlin On Sat, 27 May 2006 00:26:09 -0500, mos [EMAIL PROTECTED] said: Merlin, Lose the OR part of the Where clause and it should speed up. If so, that's what you have to work on. You could execute 2 queries, where each one writes the results to a temporary memory table and display that table instead. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Merlin [EMAIL PROTECTED] -- http://www.fastmail.fm - Same, same, but different -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Merlin [EMAIL PROTECTED] -- http://www.fastmail.fm - Choose from over 50 domains or use your own -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query to slow after inserting another table
Hi there, I am using mysql 4.0.18 and do want to do the following: There is one table with classifieds that contains the title and the text. Another one contains tags associated with the classifieds. Each classified row can have multiple tags inside the tag table. The tag table contains the ad_id which is the ID of the classified table. Without querying the tag table the query takes just 0.000x seconds, but with the tag table it takes over 0.3. Indexes are set properly, as far as I can see. This is my query (involves some other tables as well, but they the performance problem lies with the tags table. SELECT cp . * , UNIX_TIMESTAMP( cl.timestamp ) AS date, cl . * , MATCH ( cl.text, cl.title ) AGAINST ( 'anzug' IN boolean MODE ) AS relevance, ar.area_name, ar.area_name_secure, cs.name_safe AS sub_ns FROM classifieds cl, de_cat_sub cs LEFT JOIN classifieds_pix AS cp ON cl.ID = cp.classified_id LEFT JOIN area_names AS ar ON cl.area_id = ar.ID LEFT JOIN tags AS ta ON ta.ad_id = cl.ID WHERE cl.confirmed =1 AND cl.country = 'de' AND ( ( MATCH ( cl.text, cl.title ) AGAINST ( 'anzug' IN BOOLEAN MODE ) ) OR ta.tag = 'anzug' ) AND ( 1 ) AND cl.cat_id = cs.ID GROUP BY cl.ID ORDER BY relevance DESC , cl.timestamp DESC LIMIT 0 , 20 Doing an explain brings up: table type possible_keys key key_len ref rows Extra cs ALL PRIMARY NULL NULL NULL 53 Using temporary; Using filesort cl ref cat_id,country cat_id 2 cs.ID 85 Using where cp ref album_id album_id 3 cl.ID 1 ar eq_ref PRIMARY PRIMARY 2 cl.area_id 1 ta ref ad_id ad_id 3 cl.ID 2 Using where I am completly lost here. I assume that there is a much better way to include the tags table into the search. Any ideas? Thank you for any help on that. Merlin -- Merlin [EMAIL PROTECTED] -- http://www.fastmail.fm - Faster than the air-speed velocity of an unladen european swallow -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: updates during database dump
Hi,
I think there is another usable method to backup databases in MySQL.
And that is using a slave server, (that can be running in the same
server),
and when backuping up the databases you do (in the slave):
STOP SLAVE; -- This will stop the replication
-- Now you backup the databases (using mysqldump or anything you
prefer) from
the slave server
START SLAVE; -- This will restart the replication
This way you have a consistent snapshot of your database, without stop
the
main database, that is being used to whatever aplication you need.
Hope this help,
themage
On Monday 08 May 2006 00:07, Logan, David (SST - Adelaide) wrote:
Hi Luke,
Like most backup systems, there is no window unless you are locking the
table/database. If the record has been read and passed out to mysqldump,
any further updates will not be included until the next backup.
The only way to ensure you get complete referential integrity is to stop
any applications and lock all users out of the database.
This is highly impractical in many cases so a hot backup is the only
solution. A common solution is to have the database on a mirrored
volume, drop the apps, break the mirror, restart the apps and backup
from the unused half of the mirror. Rejoin the mirror at the end of the
backup.
If you are using InnoDB, Innobase have a hot backup product which (I
believe) works well. Other than that, I don't know if there is any
product currently for the MyISAM engine but I believe there are noises
afoot from MySQL in this regard.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Luke Vanderfluit [mailto:[EMAIL PROTECTED]
Sent: Monday, 8 May 2006 8:32 AM
To: MySQL List
Subject: updates during database dump
Hi.
When mysql is doing a dump, do the updates that happen during the dump
get included in the dump.
I have a dump that starts at 11pm and goes for 2 hours. If someone
updates data at say 11:45pm, does that update get included in the dump?
When does the window, on what gets included in a dump, close?
Thanks.
Kind regards.
--
Luke Vanderfluit.
Analyst/Programmer.
Internode Systems Pty. Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Merlin, the Mage
http://www.sites-favoritos.com/user/merlin
http://www.cultodavida.com
http://www.code.online.pt
Carpe Vitam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding stop words table in german language
Hi there, as mysql docs describe, there is a stop words table by default: http://dev.mysql.com/doc/refman/5.0/en/fulltext-stopwords.html Does anybody know how to add a german table ( I guess there is an equivalent to the engl. one)? Does this also work with MySQL 4.0.18? Thank you for any help, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBDesigner4 under Linux
Hi, and you have a libmysqlclient.so ? sometimes the lib is installed only with a libmysqlclient.so.xxx, and the packages don't install the libmysqlclient.so file, so all you have to do is ln -s the lib to libmysqlclient.so Verify this, mpneves On Saturday 15 October 2005 16:05, Bruno Cochofel wrote: I am able to run DBDesigner4 under Windows but now good under Linux. Try .rpm, .tar.gz, home install, all users install, still the same error: Unable to load libmysqlclient.so Put this in ld.so.conf, run ldconfig, nothing... Changed startdbd script to force dirs path and nothing. Need help on this? Or perhaps if someone point me out in the direction off ODBC connect to MySQL? I've never did this so don't know where to start... Thanks, Bruno -- Merlin, the Mage http://www.sites-favoritos.com/user/merlin http://www.cultodavida.online.pt http://www.code.online.pt Carpe Vitam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deleting rows in 2 tables
Hi there, I am wondering if there is a possiblity to delete rows in more than one table with one query. At the moment I am doing this: I do have at the moment 3 querys!: # get table1_id SELECT table1_id from ... WHERE ID = ... # delete dependent entry DELETE FROM $DB.$T5 WHERE ID = '$data[table1_id]' LIMIT 1 # delete main entry DELETE FROM $DB.$T4 WHERE ID = '$data[id]' LIMIT 1 Is there a better solution as this? Thanx for any hint, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext search on words inside words
Hi there, I am wondering if it is possible to find words inside words with the help of fulltext search. For example: Search for: Antenne Schould also find Stabantenne Is this possible? Google does that, so somehow there should be a way. Another thing is, how do I exclude popular words like and for and similar from the search? Is there a MySQL setting for this. Like words_to_exclude = Thanx, Merlin Oh.. I am running MySQL 4.0.14 on Suse Linux 9. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting unwanted rows
[EMAIL PROTECTED] wrote: Merlin [EMAIL PROTECTED] wrote on 10/04/2005 10:58:21 AM: [EMAIL PROTECTED] wrote: Merlin [EMAIL PROTECTED] wrote on 10/04/2005 10:21:00 AM: Hi there, I just discovered, that I do have some old rows I do not need anymore. A result from forgeting to empty the table before starting to go into production :-) However, I do try to find the rows which are not asociated with another table in order to delete them. I have 2 tables: table1 and table2 table1 has the key: ID table2 has the subkey table1_id Now I would like to delete all rows in table1 which are not listed in table2 with an id. I tried: SELECT t1.* FROM table1 as t1, table2 as t2 WHERE t1.ID != t2.table1_id But this returns hundreds of thousends of results. I also tryed to group by t1.ID, but it did not help Does anybody have a good idea how to get rid of those rows? Thanx, Merlin PS: Thanx for the answer for the question with full text search! That worked excellent! Use a LEFT JOIN not an INNER JOIN! SELECT t1.* FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.table1_id WHERE t2.table1_id is NULL; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hi, this workes excellent, but I tried to replace the select * from with delete from, but this did not work. Do I have to use a differnt syntax for deleting in this case? Thanx, Merlin Yes, and it which form you can use depends on your server's version. More details here: http://dev.mysql.com/doc/mysql/en/delete.html DELETE table1 FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.table1_id WHERE t2.table1_id is NULL; Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS - always CC: the list on all responses Thanx that worked excellent! Now I know why this has happened. I have forgot to delete entries in table1 which refer to table 2. Is there a way to delete them with one statement, or do I have to make a select to get the table1_id first and then do 2 delets? I do have at the moment 3 querys!: # get table1_id SELECT table1_id from ... WHERE ID = ... # delete dependent entry DELETE FROM $DB.$T5 WHERE ID = '$data[table1_id]' LIMIT 1 # delete main entry DELETE FROM $DB.$T4 WHERE ID = '$data[id]' LIMIT 1 Is there a better solution as this? Thanx, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full text search
Hi there, I am facing problems with fulltext search on MySQL 4.0.18 Problem is, that words which are not seperated by space are not found. Example: A search for dsl will not find DSL-Modem I looked it up on mysql.com help, but despite the fact that this is not seperated by space and only 3 letter, I could not find a solution. Can anybody point me to the right way? Thank you for any help, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deleting unwanted rows
Hi there, I just discovered, that I do have some old rows I do not need anymore. A result from forgeting to empty the table before starting to go into production :-) However, I do try to find the rows which are not asociated with another table in order to delete them. I have 2 tables: table1 and table2 table1 has the key: ID table2 has the subkey table1_id Now I would like to delete all rows in table1 which are not listed in table2 with an id. I tried: SELECT t1.* FROM table1 as t1, table2 as t2 WHERE t1.ID != t2.table1_id But this returns hundreds of thousends of results. I also tryed to group by t1.ID, but it did not help Does anybody have a good idea how to get rid of those rows? Thanx, Merlin PS: Thanx for the answer for the question with full text search! That worked excellent! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ROW_SIZE or something alike
oix ppl, thanks a lot for the answers, but if it was this simple I wouldn't ask... Yes, I am Core Certificate, and yes I did read the show-table-status page. BUT, that don't satisfy my needs. The sugestion to use data_length divided by the number of rows is good if all rows have a fixed length, that don't happen if there is one or more text/blob field in the table, in which case the row size is diferent depending in that field(s) content. Any other ideia? Thanks, mpeves On Thursday 17 March 2005 21:37, Keith Ivey wrote: Marco Neves wrote: I'm looking for some way to know the size each row of a table uses in my database (phisical - real disk space allocated or logical - datasize ignoring compression and any control data, don't mind, anything is better than nothing). You might try using the Data_length and Index_length values from SHOW TABLE STATUS, divided by the number of rows. http://dev.mysql.com/doc/mysql/en/show-table-status.html -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- Merlin, the Mage www.code.online.pt www.cultodavida.online.pt Carpe Vitam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT Problem
Hi, Tihs looks to me as too few information. What is in your tables (the information)? What does the query return (a empty set)? Maybe 'cause in your where clause where you have Project_ID 2 you should have Project_ID=2? Or you have several projects with ID2 and you want tasks for all of them? Or the query don't even run as Project_ID exists in both table and you should specify a table alias (T.Project_ID or P.Project_ID)? themage On Sunday 17 October 2004 20:36, shaun thornburgh wrote: Hi, I have the following two tables in my database: mysql DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Client_ID| int(11) | YES | | NULL|| | Project_Name | varchar(100) | YES | | NULL|| +--+--+--+-+-++ 3 rows in set (0.00 sec) mysql DESCRIBE Tasks; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | Task_ID| int(11) | | PRI | NULL| auto_increment | | Task_Name | varchar(40) | | | || | Project_ID | int(11) | | | 0 || ++-+--+-+-++ 3 rows in set (0.00 sec) mysql A Project will have 1 or more tasks assigned to it. Using the following query, how can I modify it so that I can find out the largest number of tasks assigned to a group of projects. SELECT P.*, T.* FROM Projects P, Tasks T WHERE P.Project_ID = T.Project_ID AND Project_ID 2; So if Project A has 3 tasks and Project B has 4 tasks I need the query to return 4. Hope this makes sense! Thanks for your help -- Merlin, the Mage www.code.online.pt www.cultodavida.online.pt Que o(s) vosso(s) Deus(es) vos abençoe(m) E a vida vos ame e proteja -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql eats all the CPU power
Hi there, I am having serious problems with a mysql 4.0.18 production server. Mysql takes away sometimes almost 100% of the cpu. The system works fine for days, but then suddenly load average climbes up and up and up till about 17. This is wat top says: top - 12:06:47 up 9:50, 2 users, load average: 16.47, 8.13, 3.38 Tasks: 123 total, 26 running, 97 sleeping, 0 stopped, 0 zombie Cpu(s): 46.3% user, 53.7% system, 0.0% nice, 0.0% idle Mem: 1025160k total, 999616k used,25544k free,14736k buffers Swap: 2048276k total, 5440k used, 2042836k free, 679028k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 9157 mysqladm 20 0 55204 53m 2400 R 5.1 5.4 0:01.34 mysqld 9155 mysqladm 19 0 55204 53m 2400 R 4.8 5.4 0:01.35 mysqld 9184 mysqladm 20 0 55204 53m 2400 R 4.8 5.4 0:00.18 mysqld 9171 mysqladm 13 0 55204 53m 2400 R 4.3 5.4 0:00.78 mysqld 9178 mysqladm 12 0 55204 53m 2400 R 4.0 5.4 0:00.57 mysqld 9179 mysqladm 12 0 55204 53m 2400 R 4.0 5.4 0:00.59 mysqld 9177 mysqladm 12 0 55204 53m 2400 R 3.8 5.4 0:00.62 mysqld 9154 mysqladm 14 0 55204 53m 2400 R 3.5 5.4 0:01.11 mysqld 9182 mysqladm 12 0 55204 53m 2400 R 3.2 5.4 0:00.15 mysqld 9172 mysqladm 11 0 55204 53m 2400 R 3.0 5.4 0:00.82 mysqld 9176 mysqladm 15 0 55204 53m 2400 R 3.0 5.4 0:00.63 mysqld 9180 mysqladm 14 0 55204 53m 2400 R 3.0 5.4 0:00.43 mysqld 9151 mysqladm 10 0 55204 53m 2400 S 2.7 5.4 0:01.22 mysqld 9153 mysqladm 13 0 55204 53m 2400 R 2.7 5.4 0:01.17 mysqld 9173 mysqladm 12 0 55204 53m 2400 R 2.7 5.4 0:00.80 mysqld 9174 mysqladm 11 0 55204 53m 2400 R 2.7 5.4 0:00.76 mysqld 9181 mysqladm 14 0 55204 53m 2400 R 2.7 5.4 0:00.35 mysqld 9065 root 13 0 1112 1112 852 R 2.4 0.1 0:03.48 top 9156 mysqladm 11 0 55204 53m 2400 R 2.4 5.4 0:01.27 mysqld 9162 mysqladm 12 0 55204 53m 2400 R 2.4 5.4 0:00.87 mysqld 9165 mysqladm 10 0 55204 53m 2400 S 2.4 5.4 0:00.87 mysqld 9152 mysqladm 12 0 55204 53m 2400 R 1.9 5.4 0:01.20 mysqld 9160 mysqladm 12 0 55204 53m 2400 R 1.9 5.4 0:01.14 mysqld 2682 www 10 0 16184 15m 12m S 1.6 1.6 0:59.01 httpd 9149 mysqladm 12 0 55204 53m 2400 R 1.1 5.4 0:01.56 mysqld 9166 mysqladm 12 0 55204 53m 2400 R 1.1 5.4 0:01.05 mysqld 9167 www9 0 7564 7560 4524 S 1.1 0.7 0:00.12 httpd 2664 www9 0 16080 15m 12m S 0.8 1.6 1:06.70 httpd 2673 www9 0 15632 15m 11m S 0.8 1.5 1:03.46 httpd 8766 www9 0 8452 8448 5044 S 0.8 0.8 0:05.55 httpd 9121 www9 0 7952 7952 4536 S 0.8 0.8 0:00.38 httpd 9183 www9 0 7224 7224 4360 S 0.8 0.7 0:00.04 httpd 2663 www9 0 15784 15m 12m S 0.5 1.5 1:07.80 httpd 2669 www9 0 19228 18m 12m S 0.5 1.9 1:10.13 httpd 2670 www9 0 15508 15m 12m S 0.5 1.5 1:05.34 httpd 2677 www9 0 15596 15m 12m S 0.5 1.5 1:06.77 httpd 2678 www 10 0 15464 15m 12m S 0.5 1.5 1:06.44 httpd 2679 www9 0 15500 15m 12m S 0.5 1.5 1:07.30 httpd 7094 www9 0 8692 8688 5136 S 0.5 0.8 0:25.88 httpd 8768 www9 0 8588 8584 5072 S 0.5 0.8 0:07.55 httpd 8988 www9 0 7780 7776 4732 S 0.5 0.8 0:01.89 httpd 8989 www9 0 8388 8384 4940 S 0.5 0.8 0:01.80 httpd 9025 www9 0 8164 8160 4680 S 0.5 0.8 0:01.03 httpd 9028 www9 0 7636 7632 4636 S 0.5 0.7 0:00.92 httpd 9030 www 10 0 7540 7536 4640 S 0.5 0.7 0:01.12 httpd Funny enough the logfile of apache shows that there have been not a lot requests made. After a few minutes everything was back to normal. Once every few months the system starts to swap and troutles down, so I have reboot and then again everything is fine for weeks. Does anybody have an idea how to fix that, I am completly lost here :-( Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql eats all the cpu power
Hi there, I am having serious problems with a mysql 4.0.18 production server. Mysql takes away sometimes almost 100% of the cpu. The system works fine for days, but then suddenly load average climbes up and up and up till about 17. This is wat top says: top - 12:06:47 up 9:50, 2 users, load average: 16.47, 8.13, 3.38 Tasks: 123 total, 26 running, 97 sleeping, 0 stopped, 0 zombie Cpu(s): 46.3% user, 53.7% system, 0.0% nice, 0.0% idle Mem: 1025160k total, 999616k used,25544k free,14736k buffers Swap: 2048276k total, 5440k used, 2042836k free, 679028k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 9157 mysqladm 20 0 55204 53m 2400 R 5.1 5.4 0:01.34 mysqld 9155 mysqladm 19 0 55204 53m 2400 R 4.8 5.4 0:01.35 mysqld 9184 mysqladm 20 0 55204 53m 2400 R 4.8 5.4 0:00.18 mysqld 9171 mysqladm 13 0 55204 53m 2400 R 4.3 5.4 0:00.78 mysqld 9178 mysqladm 12 0 55204 53m 2400 R 4.0 5.4 0:00.57 mysqld 9179 mysqladm 12 0 55204 53m 2400 R 4.0 5.4 0:00.59 mysqld 9177 mysqladm 12 0 55204 53m 2400 R 3.8 5.4 0:00.62 mysqld 9154 mysqladm 14 0 55204 53m 2400 R 3.5 5.4 0:01.11 mysqld 9182 mysqladm 12 0 55204 53m 2400 R 3.2 5.4 0:00.15 mysqld 9172 mysqladm 11 0 55204 53m 2400 R 3.0 5.4 0:00.82 mysqld 9176 mysqladm 15 0 55204 53m 2400 R 3.0 5.4 0:00.63 mysqld 9180 mysqladm 14 0 55204 53m 2400 R 3.0 5.4 0:00.43 mysqld 9151 mysqladm 10 0 55204 53m 2400 S 2.7 5.4 0:01.22 mysqld 9153 mysqladm 13 0 55204 53m 2400 R 2.7 5.4 0:01.17 mysqld 9173 mysqladm 12 0 55204 53m 2400 R 2.7 5.4 0:00.80 mysqld 9174 mysqladm 11 0 55204 53m 2400 R 2.7 5.4 0:00.76 mysqld 9181 mysqladm 14 0 55204 53m 2400 R 2.7 5.4 0:00.35 mysqld 9065 root 13 0 1112 1112 852 R 2.4 0.1 0:03.48 top 9156 mysqladm 11 0 55204 53m 2400 R 2.4 5.4 0:01.27 mysqld 9162 mysqladm 12 0 55204 53m 2400 R 2.4 5.4 0:00.87 mysqld 9165 mysqladm 10 0 55204 53m 2400 S 2.4 5.4 0:00.87 mysqld 9152 mysqladm 12 0 55204 53m 2400 R 1.9 5.4 0:01.20 mysqld 9160 mysqladm 12 0 55204 53m 2400 R 1.9 5.4 0:01.14 mysqld 2682 www 10 0 16184 15m 12m S 1.6 1.6 0:59.01 httpd 9149 mysqladm 12 0 55204 53m 2400 R 1.1 5.4 0:01.56 mysqld 9166 mysqladm 12 0 55204 53m 2400 R 1.1 5.4 0:01.05 mysqld 9167 www9 0 7564 7560 4524 S 1.1 0.7 0:00.12 httpd 2664 www9 0 16080 15m 12m S 0.8 1.6 1:06.70 httpd 2673 www9 0 15632 15m 11m S 0.8 1.5 1:03.46 httpd 8766 www9 0 8452 8448 5044 S 0.8 0.8 0:05.55 httpd 9121 www9 0 7952 7952 4536 S 0.8 0.8 0:00.38 httpd 9183 www9 0 7224 7224 4360 S 0.8 0.7 0:00.04 httpd 2663 www9 0 15784 15m 12m S 0.5 1.5 1:07.80 httpd 2669 www9 0 19228 18m 12m S 0.5 1.9 1:10.13 httpd 2670 www9 0 15508 15m 12m S 0.5 1.5 1:05.34 httpd 2677 www9 0 15596 15m 12m S 0.5 1.5 1:06.77 httpd 2678 www 10 0 15464 15m 12m S 0.5 1.5 1:06.44 httpd 2679 www9 0 15500 15m 12m S 0.5 1.5 1:07.30 httpd 7094 www9 0 8692 8688 5136 S 0.5 0.8 0:25.88 httpd 8768 www9 0 8588 8584 5072 S 0.5 0.8 0:07.55 httpd 8988 www9 0 7780 7776 4732 S 0.5 0.8 0:01.89 httpd 8989 www9 0 8388 8384 4940 S 0.5 0.8 0:01.80 httpd 9025 www9 0 8164 8160 4680 S 0.5 0.8 0:01.03 httpd 9028 www9 0 7636 7632 4636 S 0.5 0.7 0:00.92 httpd 9030 www 10 0 7540 7536 4640 S 0.5 0.7 0:01.12 httpd Funny enough the logfile of apache shows that there have been not a lot requests made. After a few minutes everything was back to normal. Once every few months the system starts to swap and troutles down, so I have reboot and then again everything is fine for weeks. Does anybody have an idea how to fix that, I am completly lost here :-( Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stressing mysql - configuration advice
Hi there, I just installed the newest mysql 4.x server on a suse9.0 system with apache 1.x and php4.x The system is a p4 2.4G with 1GB of RAM. It serves one LAMP application. Now I am trying to configure the server for huge load. To stress test it, I installed the Microsoft stress test tool which simulates 300 threads in this case. I configureed the server with following settings: key_buffer = 348M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 Problem is: The server can't satisfy each query request once more than 200 threads are issued. Result is that the application does not respond anymore. I have a php script which redirects users if the db does not respond to a query. This is the only site they get with the current settings. Is it possible to increase the number of users? I think this might be the main problem in my case. Any advice is apreciated. I am just starting to understand how to configure mysql server. Thank you in advance, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stressing mysql - configuration advice
Hi there, I just installed the newest mysql 4.x server on a suse9.0 system with apache 1.x and php4.x The system is a p4 2.4G with 1GB of RAM. It serves one LAMP application. Now I am trying to configure the server for huge load. To stress test it, I installed the Microsoft stress test tool which simulates 300 threads in this case. I configureed the server with following settings: key_buffer = 348M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 Problem is: The server can't satisfy each query request once more than 200 threads are issued. Result is that the application does not respond anymore. I have a php script which redirects users if the db does not respond to a query. This is the only site they get with the current settings. Is it possible to increase the number of users? I think this might be the main problem in my case. Any advice is apreciated. I am just starting to understand how to configure mysql server. Thank you in advance, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connect Error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Jonh, You're probably forget the -p param for mysqladmin ask you your root password. merlin Escreveu John Wilkerson em Thursday 02 January 2003 04:11 am: Hi, I'm a newbie with MySQL and get the following error: bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' I get this whenever I attempt to connect to MySQL. I am running Slackware 8.1, MySQL Max 3.23.54b. I have set the MySQL root password. What am I missing? TIA, John - -- Merlin, the Mage Que os vosso(s) Deus(es) vos abençoe(m) E a vida vos ame e proteja. -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE+FIzgKOMrqDFTeBARApO8AKCszGgvPbe6PoeU1oXtCfRGSm+BcgCggPBw ZpvYwUf3XcsWJgPRflwoG5M= =klHF -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: 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: cant store checkbox vale ..... ?????
On Tuesday 30 July 2002 07:13 am, toby - wrote:
(...)
input type=checkbox name=news_letter_subs sign me up fo weekly news
letter and updates/input
(...)
the processing for this is:
$isql = insert into usr_inf (email, firstName, lastName, news_letter_subs)
.
values (' . $email . ', ' . $firstName . ', ' . $lastName . ',' .
$news_letter_subs . ') ;
(...)
Hi Toby,
Two points:
First, I presume you're quotting ($dbh-quote(...)) your data between
the
cgi retrieve and that your insert. if you aren't, you should.
Second... well it depends on the browser you're using on your testes.
Some
browser with check boxes like yours (with no value) send only the field with
no value (an http parameter like this ...news_letter_subs=...) and other
send an ON value on checked checkbox fields with no value (an http parameter
like this ...news_letter_subs=ON...) - In this last ones your wuery should
work at all if as i presume your field is numeric.
I presume that your field is a numeric, so mysql uses the '' value as a 0.
What you must do is one of two things. Depending on the way you parse/the
module you use to parse your query string, you must check if your parameter
news_letter_subs is defined. And if it is defined your checkbox was checked,
since unchecked checkbox fields are not sent.
Or, the simplest way, just change your HTML to this
input type=checkbox name=news_letter_subs VALUE=1sign me up fo weekly news
letter and updates/input
And this way, the value you get if the field is checked is allways 1.
MPNeves
--
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
Re: Crash importing old data to new server
Hi, Why don't you just dump the databases, tar.gzip them, and transfer that file? It's smaller then the .frm+ .myd, and you can rebuild everything without probs... MPNeves On Tuesday 30 July 2002 07:29 am, [EMAIL PROTECTED] wrote: MySql crashes: On an idle server, executeing truncate table lists with the given data files (only an MYD and frm, no MYI). I'm trying to save bandwidth transfering data from an old mysql install (mysql Ver 11.15 Distrib 3.23.38, for pc-linux-gnu (i686)) to this one. I wanted to transfer the data and frm but not the index, then rebuild on the new host. -- 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
