Is there any configuration setting using which long running query can be killed automatically in mysql?
53220
I would like to set a maximum execution time for sql queries like set_time_limit() in php. How can I do ?
61136
What's the best way to configure a MySQL database that is only read-only, and will never have any modifications made to it?  1) I have increased my innodb_buffer_pool_size to 70% of available RAM  2) Increase query cache size to 40mb  Running against a load generator, after some time the database starts bottlenecking to only 1000 statements per second.  The configuration is 2-cores, 8GB ram 50GB SSD. The database contains a single InnoDB 13 GB table with the same exact query running on two indexed columns.  We are using a vertx server to hit the database. The symptom we see is that the web service performance degrades after 15-20 minutes of heavy load.  Where can the bottleneck be? What parameters should we optimize?
26688
I need to delete some rows from one large table. The rows to delete shouldn't be in another table, example:  DELETE FROM LargeTable WHERE id IS NOT IN (SELECT DISTINCT foreign_id from EvenLargerTable)  but my server can't handle such blunt query, because there are almost a million records in the LargeTable and few million records in the EvenLargerTable  How can I solve it?
21521
I have a case where a query I'm running is taking a long time.  When I checked sys.dm_exec_requests the blocking_session_id is a negative value, -2 to be specific.  The wait is a LCK_M_X and the blocked command is a DELETE statement.  To the best of my knowledge nothing else is touching that table at the moment.  In fact the only active requests are the blocked connection and the query checking sys.dm_exec_requests.  When I look at sys.dm_tran_session_transactions the blocked session is the only one showing up.
9267
I am aware of the die() command in PHP which exits a script early.  How can I do this in Python?
81999
What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row.
124164
For example, I have a table for user registration:  CREATE TABLE IF NOT EXISTS test_user (      user_id int(10) unsigned NOT NULL AUTO_INCREMENT,      user_name varchar(50) NOT NULL,      PRIMARY KEY (user_id),   ) ENGINE=InnoDB DEFAULT CHARSET=utf8   I want the user_id field ID numbers to be continuous, without gaps.    Failed transactions will occupy some ID numbers, is there any way around this?
123207
Can anyone help me to find a below given details for long running query. ProcessID, process name, database, host ,user ,process login time, query start time and query duration.   I am looking for a query or an SP which gives me this data.
35185
So, I've looked at history and at Ctrl+R, but they are not what I thought I knew. Is there a way that I can type in the beginning of a command, and cycle through the matches in my history with some bash shortcut?  # mysq(some shortcut key)   Gives me:  # mysqldump  --add-drop-table -e -q -n -C -u  (some shortcut key) #  mysql -u ben.dauphinee -p
61606
What is the equivalent of SQLServer function  in mySQL?
123269
Inspired by a StackOverflow question ().  Is there a DBMS that actually supports the = NULL syntax?
123106
I want to know how to efficiently store query-affecting settings in a large analytical database (MS SQL Server). By efficiency I mean no hardcode (1) and fast/optimal query processing (2).  My example  I've got a massive table of sales orders. Let's simplify it to two columns:  SalesOrder  OrderID      int      Primary Key  OrderDate    date   I need to retrieve the list of orders for the last X days and this X should be configurable. That's why I introduce a table of settings where I define it:  Admin_Configuration  SettingName   (PK)|      SettingValue  ------------------|------------------  DaysBack          |          30      It allows me not to hardcode this "30" in query definitions. Then I would run queries like:  SELECT OrderId   FROM SalesOrder as S   JOIN Admin_Configuration as A     ON A.SettingName = 'DaysBack'   WHERE [OrderDate] &gt; DATEADD(DAY, -A.SettingValue, GETDATE());   Performance  It avoids hardcoding, but leads to a performance problem. This query runs much slower than it would've, had I simply hard-coded 30 in the WHERE clause.  My understanding is that optimizer doesn't know how large my SettingValue is and assumes the worst - that all rows would be retrieved from the SalesOrders table, thus leading to a suboptimal query plan.  Trying a scalar function yielded even worse results. I'm desperate enough to have a separate view which stores these hardcoded settings but I've been refraining from it with hopes for a better solution.  Here is the original query plan (with a join): 4 seconds elapsed   Here is the plan with subquery, as :  SELECT orderid FROM [SalesOrder] as s WHERE s.OrderDate &gt; dateadd(DAY,    (SELECT -NumValue    FROM Admin_Configuration where SettingName = 'DaysBack'   )     , getdate())   7 seconds elapsed, slightly slower   As for the ideal performance: I just hardcoded 20, the query took 2 seconds. It needs mentioning that the Admin_Configuration table contains only 18 rows, hence it shouldn't affect performance.  SELECT OrderId   FROM SalesOrder as S   JOIN Admin_Configuration as A     ON A.SettingName = 'DaysBack'   WHERE [OrderDate] &gt; DATEADD(DAY, -20, GETDATE());   Using parameters would mean that I'd have to replace views (that I use now) with stored procedures/table-valued functions, which is unacceptable in my case
83653
Question: What is the best way to recover mysql (or worst case: migrate away) from a "chmod -R 777 /" with databases intact?  System: Ubuntu 12.04 LTS MySQL 5.5.24 64 bit Amazon EC2 cloud server.  Background: Attempting to recover (or at least recover data from) a system which had this done to it:        chmod -R 777 /   No point in worrying about the why.  It was a manager with too much access and too little experience who likes to swim in deep waters.  It was a pure accident on his part, not actually meaning to hit enter when he did.  I have recovered much of the system, but am really hung up on getting mysql working again.  I have already worked through such pages as:         Have already done this:        sudo chmod 644 my.cnf     chown mysql:mysql my.cnf   At which point attempting to start mysql:        sudo service mysql start   Produces this output in syslog:      Apr 12 20:51:42 ip-10-10-25-143 kernel: [18632541.774742] type=1400 audit(1365799902.306:41): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=18210 comm="apparmor_parser"     Apr 12 20:51:42 ip-10-10-25-143 kernel: [18632541.964496] init: mysql main process (18214) terminated with status 1     Apr 12 20:51:42 ip-10-10-25-143 kernel: [18632541.964542] init: mysql main process ended, respawning     Apr 12 20:51:43 ip-10-10-25-143 kernel: [18632542.959796] init: mysql post-start process (18215) terminated with status 1     Apr 12 20:51:43 ip-10-10-25-143 kernel: [18632543.002041] type=1400 audit(1365799903.534:42): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=18238 comm="apparmor_parser"     Apr 12 20:51:43 ip-10-10-25-143 kernel: [18632543.098490] init: mysql main process (18242) terminated with status 1     Apr 12 20:51:43 ip-10-10-25-143 kernel: [18632543.098536] init: mysql main process ended, respawning     Apr 12 20:51:43 ip-10-10-25-143 kernel: [18632543.140706] init: mysql post-start process (18244) terminated with status 1     Apr 12 20:51:43 ip-10-10-25-143 kernel: [18632543.158681] type=1400 audit(1365799903.690:43): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=18258 comm="apparmor_parser"     Apr 12 20:51:43 ip-10-10-25-143 kernel: [18632543.285087] init: mysql main process (18262) terminated with status 1     Apr 12 20:51:43 ip-10-10-25-143 kernel: [18632543.285133] init: mysql respawning too fast, stopped   What I read from that is mysql is terminating with a status 1, and it loops a few times trying to start up and gets stopped from doing that after too many attempts.  I've looked into status 1 but haven't found solutions that seem applicable.
24825
I have inherited a client site that has an extremely large database for no reason. There is a moderate amount of content and very little enabled modules. However, the database is too large for moving around easily and I want to clean it out.  I have cleared out the standard cache tables, syslog and accesslog.  Are there any other tables I can safely truncate in a standard Drupal site?
31406
I am planning to replace repeatedly executed Statement objects with PreparedStatement objects to improve performance. I am using arguments like the MySQL function now(), and string variables.   Most of the PreparedStatement queries I have seen contained constant values (like 10, and strings like "New York") as arguments used for the ? in the queries. How would I go about using functions like now(), and variables as arguments? Is it necessary to use the ?s in the queries instead of actual values? I am quite confounded.
70699
I have this logrotate config and I am running on Ubuntu 10.04.  /var/log/mysql/mysql-slow.log {     daily     rotate 3     compress     notifempty     missingok     create 660 mysql adm     postrotate      if test -x /usr/bin/mysqladmin &amp;&amp; \        /usr/bin/mysqladmin  ping &amp;&gt;/dev/null     then        /usr/bin/mysqladmin  flush-logs     fi endscript   }  I put this in /etc/logrotate.d yesterday and today the log was not rotated.  Below are the things that I have done:   I verified that the log is indeed in /var/log/mysql/mysql-slow.log  mysqladmin lines work fine when run as root mysql is able to write to the mysql-slow.log   When I did this:  $ logrotate -d -f mysql-slow  reading config file mysql-slow reading config info for /var/log/mysql/mysql-slow.log   Handling 1 logs  rotating pattern: /var/log/mysql/mysql-slow.log  forced from command line (3 rotations) empty log files are not rotated, old logs are removed considering log /var/log/mysql/mysql-slow.log  log needs rotating rotating log /var/log/mysql/mysql-slow.log, log-&gt;rotateCount is 3 dateext suffix '-20120329' glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' renaming /var/log/mysql/mysql-slow.log.3.gz to /var/log/mysql/mysql-slow.log.4.gz     (rotatecount 3, logstart 1, i 3),  renaming /var/log/mysql/mysql-slow.log.2.gz to /var/log/mysql/mysql-slow.log.3.gz (rotatecount 3, logstart 1, i 2),  renaming /var/log/mysql/mysql-slow.log.1.gz to /var/log/mysql/mysql-slow.log.2.gz (rotatecount 3, logstart 1, i 1),  renaming /var/log/mysql/mysql-slow.log.0.gz to /var/log/mysql/mysql-slow.log.1.gz (rotatecount 3, logstart 1, i 0),  renaming /var/log/mysql/mysql-slow.log to /var/log/mysql/mysql-slow.log.1 creating new /var/log/mysql/mysql-slow.log mode = 0660 uid = 20004 gid = 4 running postrotate script running script (multiple) with arg /var/log/mysql/mysql-slow.log : "      if test -x /usr/bin/mysqladmin &amp;&amp; \        /usr/bin/mysqladmin &amp;&gt;/dev/null     then        /usr/bin/mysqladmin flush-logs     fi " compressing log with: /bin/gzip removing old log /var/log/mysql/mysql-slow.log.4.gz    Where is the log that shows that logrotate was successful? I want to see if there is anything that would say that there was a problem. Any ideas on why the logrotate is not working?
1116
What's the best way to delete all rows from a table in sql but to keep n number of rows on the top?