How to Stop Runaway Query (automatically)?
Hello Everyone, I am discovering that my MySQL server tends to get locked up by runaway queries, that are continuously sending data. Even after all night long. (The origin of these queries is from report development.) Without having to kill each connection manually on the MySQL server, is there an easier way to automatically stop these runaway queries in the configuration file? I can't find any options for direct timeouts of active connections. Instead, the closest thing that I can find is to limit the packet size. See below for MySQL documentation. However, tt's confusing on what is a single packet. But would this stop runaway queries from running all night long?? http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client. In MySQL 3.23, the largest possible packet is 16MB, due to limits in the client/server protocol. In MySQL 4.0.1 and up, the limit is 1GB. Any help would be greatly appreciated. Thanks! Henry
How to Find the Max/Min from Multiple Columns (in each row)
Hello everyone, I have a table where measurement values are collected in mulitple columns. Table Schema == ID, measurement_01, measurement_02, measurement_03 == 1, 300, 350, 325(max is 350, min is 300) 2, 225, 275, 400(max is 400, min is 225) 3, 100, 500, 300(max is 500, min is 100) == My question is that for each row, what's the sql query that determine the max value and the min value from all the columns?? (I realize that each measurment should be it's own row, but I must use the table as it is.) Any help would be most appreciated. Thanks! Henry
Total Sum of Monthly Counts for Older MySQL 4.0.22 (Please Help)
Hello MySQL users, Currently, I use MySQL 4.0.22 and I can do a straightforward count of employees hired for each month. select employee_hire_date, count(employee_id) from table_employee where employee_hire_date between 2005-01-01 and 2005-4-30 group by month(employee_hire_date) Date | Count | 2005-01-01 | 123 | 2005-02-01 | 50| 2005-03-01 | 76| 2005-04-01 | 89| However, I would like to do a grand total of the counts and the averages that would like the below. Date | Count | Sum | Avg | - 2005-01-01 | 123 | 338 | 84.5 | 2005-02-01 | 50| 338 | 84.5 | 2005-03-01 | 76| 338 | 84.5 | 2005-04-01 | 89| 338 | 84.5 | Since my MySQL version is 4.0.22, I am not able to use subquery and I prefer not to use tmp tables. What would be the right query to solve for the grand total sum and average?? Any help would be greatly appreciated!!! Thanks in Advance. Henry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
eBay: Sold on Grid (database array)
I like to share with you all, an interesting article about eBay's deployment of database arrays. http://www.eweek.com/article2/0,1759,1640234,00.asp Specifically on page 2, Marty Abbott (eBay's VP of Technology) states that eBay has four equal data centers. So in this array scheme, a write in A would be passed to B, C, D. Likewise, a write in D, would be passed to A, B, C. My questions are the following: #1) How was Marty Abbott able to make this database array work?? Is there a specific feature in Oracle database that allows this?? #2) Can eBay's database array be duplicated with MySQL?? As far as I know, MySQL only supports one way Master-Slave replication (which I have already setup). I saw S. Harper's posting in MySQL manual about his master-master replication, but it seems to be on the application-level, and not on the native database-level. #3) If the answer to #2 is NO, then is true replication on the MySQL development plan, and what is the timetable?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Fix Broken Replication
I have MySQL replication setup between Master A and Slave B. Everything was working fine, until one day replication was broken. Status on the slave shows the following error message: Last_error: Error 'Can't drop database X. Database doesn't exist' on query 'DROP DATABASE X. I suspect another admin probably deleted or moved the database from the linux command line. At this point, how can I tell Slave B to jump over this drop command and resume replication at the next command?? Or is re-installing replication the only option on Slave B?? Any help would be greatly appreciated. Master A Slave B MySQL version 4.0.18 Red Hat 9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to Fix Broken Replication
John...the skip-counter is perfect!! My Slave B is now back up and running again. Thanks again for your prompt help!! *** REPLY SEPARATOR *** On 6/14/2004 at 3:54 PM John McCaskey wrote: Give the below a shot: slave stop; set global sql_slave_skip_counter=1; slave start; Good luck. John A. McCaskey -Original Message- From: Henry Chang [mailto:[EMAIL PROTECTED] Sent: Monday, June 14, 2004 3:34 PM To: [EMAIL PROTECTED] Subject: How to Fix Broken Replication I have MySQL replication setup between Master A and Slave B. Everything was working fine, until one day replication was broken. Status on the slave shows the following error message: Last_error: Error 'Can't drop database X. Database doesn't exist' on query 'DROP DATABASE X. I suspect another admin probably deleted or moved the database from the linux command line. At this point, how can I tell Slave B to jump over this drop command and resume replication at the next command?? Or is re-installing replication the only option on Slave B?? Any help would be greatly appreciated. Master A Slave B MySQL version 4.0.18 Red Hat 9 -- 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]
What is Frequency of Master Binlog Dump to Slave
I got MySQL replication working in master-slave configuration. It's really cool, but how often does the master send binlog dump to the slave. The implication is if the master crashes, what would be the potential data gap in the slave?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Confused with Update involving Multiple Tables
I am truly frustrated with what should be a simple update. Any help to enlighten this newbie is appreciated. [TABLE A] tdmr_dmr dmr_customer_code dmr_job_number_code [TABLE B] tjl_job_list jl_customer_code jl_jobnumber In Table A, the dmr_customer_code field is empty. So I need to populate with jl_customer_code data from Table B.into dmr_customer_code in Table Afor matching job numbers. I thought the command would simply be as follows: UPDATE tdmr_dmr, tjl_job_list SET tdmr_dmr.dmr_customer_code = tjl_job_list.jl_customer_code WHERE tdmr_dmr.dmr_job_number_code = tjl_job_list.jl_jobnumber But I keep getting this error from my admin tool, although I can't figure out what is wrong. You have an error in your SQL syntax near ' tjl_job_list SET tdmr_dmr.dmr_customer_code = tjl_job_list.jl_customer_cod' at line 1 Any help is deeply appreciated. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Newbie Confused with Update involving Multiple Tables
Thanks for the reply!! IndeedI am using version 3.23.58. I can stop pounding my head against the wall now. Thanks again!! *** REPLY SEPARATOR *** On 2/19/2004 at 2:10 AM Jeremy March wrote: http://www.mysql.com/doc/en/UPDATE.html I believe multi-table updates weren't supported until MySQL version 4.04. Are you using a version earlier than this? -- 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]
