Re: bad too many connections error (os x)
On Sep 2, 2004, at 6:04 AM, Egor Egorov wrote: Michael Winston [EMAIL PROTECTED] wrote: Okay, so the first thing to try is obviously enlarge the max_connections. Have you tried this? Yes. It's set to 400 (a number we will never reach unless there's some sort of logjam). max_connect_errors is set to 200. Is it a webserver backend database? Yes. PHP-generated pages. Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bad too many connections error (os x)
Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
On Sep 1, 2004, at 9:10 AM, V. M. Brasseur wrote: Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Hmmm. All of our connections are coming from php-generated web pages. PHP automatically closes the connection at the end of the script. Unless I completely misunderstand how this stuff works. Plus, this problem only happens once every few weeks. If some of our queries are causing this, I would expect the problem to occur more often. I'll look into this, though. Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
This would make sense since they all the connections are coming from the same website. But if this is true, then why can't we connect using 'mysqladmin -uroot'? Shouldn't that work from any host? Thanks, Michael On Sep 1, 2004, at 9:08 AM, Victor Pendleton wrote: Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
natural sorting
Is there any way to do natural sorting in MySQL? That is: Chapter 1 Chapter 2 Chapter 10 instead of Chapter 1 Chapter 10 Chapter 2 ? If not, are there any plans for this feature in future releases? Would be pretty helpful... Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: natural sorting
At 2:06 PM -0500 10/16/2003, Dan Nelson wrote: In the last episode (Oct 16), Michael Winston said: Is there any way to do natural sorting in MySQL? That is: Chapter 1 Chapter 2 Chapter 10 instead of Chapter 1 Chapter 10 Chapter 2 If not, are there any plans for this feature in future releases? Would be pretty helpful... You can easily enough write a UDF that zero-pads all numbers in a string to N digits, which would let you do SELECT * FROM mytable ORDER BY dwim_sort(textfield,5) Easily enough? ha ha. I've looked at the example UDF and realized that I am in over my head on that one. Still, I wasn't even aware of UDFs, so thanks. Anyone done this already or know of a good repository of UDFs? Google didn't provide anything useful... Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deleting semi-duplicate records
Let's say I have the following table (t1): ++--++ | id | c1 | c2 | ++--++ | 1 | NULL | 1 | | 2 | a| 1 | | 3 | NULL | 2 | | 4 | b| 2 | | 5 | NULL | 3 | | 6 | c| 3 | | 7 | NULL | 4 | ++--++ I would like to delete all rows that c1 = NULL *and* have a duplicate c2. So, in this case I want to eliminate rows 1,3, and 5. So my question is two-fold: 1) What's a good command way to do this? 2) I tried an UPDATE/DELETE combo: UPDATE t1 LEFT JOIN t1 as t2 ON (t1.c2 = t2.c2 AND t2.c1 IS NOT NULL) SET t1.c2 = NULL; DELETE FROM t1 WHERE c1=NULL and c2 =NULL; This would only replace and delete one record at a time (so I would need to run it 3 times). Why is it only updating one row? Why not all matches or no matches? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie needs help: can I order by before grouping?
I'm having trouble ordering my results before grouping them. I don't want to go into too much detail, so my simplified query without any grouping or ordering results in: ++---++ | jobnum | milestone | shipdate | ++---++ | 96-655 | 4 || | 96-655 | 2 || | 96-655 | 0 | 2003-12-04 | | 98-308 | 0 | 1973-05-11 | | 98-308 | 4 || | 98-316 | 0 | 1973-06-11 | | 98-316 | 4 || | 98-500 | 2 || | 98-327 | 4 || | 98-327 | 0 | 1973-08-11 | ++---++ when I add group by jobnum order by milestone I get: ++---++ | jobnum | milestone | shipdate | ++---++ | 98-308 | 0 | 1973-05-11 | | 98-316 | 0 | 1973-06-11 | | 96-655 | 4 || | 98-327 | 4 || ++---++ but what I really want is this: ++---++ | jobnum | milestone | shipdate | ++---++ | 98-308 | 0 | 1973-05-11 | | 98-316 | 0 | 1973-06-11 | | 96-655 | 0 | 2003-12-04 | | 98-327 | 0 | 1973-08-11 | | 98-500 | 2 || ++---++ Clearly, mysql is grouping first and then ordering. I suppose that's reasonable, but I really want to order the results internally first and then group so that the displayed row from each group is the smallest value (and then maybe do some more sorting on shipdate, but that's another issue). Is there a way to do this? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
