Author: techworkshop

Automatically Kill Slow MySQL Queries After N Seconds

Learn how to detect and automatically terminate slow MySQL queries after a set duration. Improve performance and avoid database slowdowns.

Maintaining a performant MySQL-backed application requires smart tuning. Without it, connections may pile up, queries stall, and user experience suffers dramatically, especially due to slow queries.

If you’re using persistent connections, idle or long-running queries can accumulate in SLEEP mode for a log time. One quick solution – especially on MySQL ≥ 5.1 – is to periodically scan the process list and terminate any query that’s been running longer than your acceptable threshold and currently in SLEEP mode.

In this post, we will learn some techniques to identify these slow queries and kill them to improve the performance of the application.

Step 1: Generate KILL QUERY Statements

This SQL query will produce the necessary KILL QUERY commands for any non-system query exceeding your time limit (e.g., 1200 seconds = 20 minutes):

SELECT
  GROUP_CONCAT(
    CONCAT('KILL QUERY ', id, ';')
    SEPARATOR ' '
  ) AS kill_cmds
FROM information_schema.processlist
WHERE user <> 'system user'
  AND time >= 1200;

You can customize the filter via the info column – for instance, if you want queries from specific database (db) or certain query patterns, you can apply those filters to info column and update the above query accordingly.

This query gives you the complete list of slow queries as per your filters. But, you need to run it everytime, whenever you want this data.

So, to automate this process, add this query to cronjob via a shell script.

Step 2: Automate with a Shell Script

Wrap this logic in a shell script and schedule it with cron to run at regular intervals. Here’s a refined example:

SECONDS_TOO_LONG=1200

QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"`

if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" | mysql -uroot -ppassword
fi

This ends all slow MySQL queries running over 20 minutes and can be scheduled every few minutes in crontab.

Alternative: Log and Kill via a Temporary SQL File

Alternatively, if you want to log all the slow MySQL queries before killing them, here’s a safe method:

SECONDS_TOO_LONG=1200

QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"`
if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT CONCAT('KILL QUERY ',id,';') KillQuery FROM information_schema.processlist WHERE user <> 'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" > /tmp/kill_log_queries.sql
    mysql -uroot -ppassword < /tmp/kill_log_queries.sql
fi

This approach gives you a chance to log or inspect the kill commands before they run.

Want to learn more about tuning MySQL performance? Get High Performance MySQL book from https://amzn.to/4dVaueF

Built-In Per-Session Execution Timeout (MySQL 5.7+)

If you’re using MySQL 5.7.4+, a cleaner solution exists:

SET GLOBAL max_execution_time = 5000; -- Timeout in milliseconds

This applies a soft execution limit to all SELECT queries (read-only) at runtime. Once exceeded, MySQL aborts the query with an error but keeps the session alive.

You can also set per-session limits:

SET SESSION max_execution_time = 2000;

Or use the hint syntax on specific queries:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM your_table;

Conclusion

Choose the approach that fits your environment:

  • Older MySQL (< 5.7): Use a cron-driven shell script targeting INFORMATION_SCHEMA.PROCESSLIST.
  • MySQL 5.7.4 and above: Prefer max_execution_time or per-query hints for cleaner, built-in enforcement.

They all help safeguard against runaway queries that could otherwise lock up your server.

Disclaimer: This post contains affiliate links. If you use these links to buy something, I may earn a commission at no extra cost to you.

Alternative of PHP Excel for Excel Reading and Writing

Explore modern, efficient PHP libraries to read and write Excel files as alternatives to the deprecated PHP_Excel. Supports .xls, .xlsx, CSV, and more.

Working with Excel files is a common requirement in many PHP applications, especially for tasks like data import/export, reporting, and analysis. In the past, the go-to library for handling Excel files was PHPExcel, but it has since been deprecated and replaced by more efficient and actively maintained alternatives.

Whether you’re looking to read data from .xls or .xlsx files, write spreadsheet data, or handle both tasks seamlessly, this guide explores a wide range of libraries that offer reliable and modern solutions for Excel file manipulation.

Writing Excel using PHP

Reading Excel

Reading and Writing Excel

  • Ilia Alshanetsky’s Excel extension now on github (xls and xlsx, and requires business libXL segment)
  • spout OfficeOpenXML (xlsx) and CSV
  • PHP’s COM extension (requires a COM empowered spreadsheet program, for example, MS Excel or OpenOffice Calc running on the server)
  • SimpleExcel Claims to read and compose MS Excel XML/CSV/TSV/HTML/JSON/and so forth arranges

Another C++ Excel expansion for PHP, however you’ll have to manufacture it yourself, and the docs are really meager with regards to attempting to discover what usefulness (I can’t discover from the site what groups it bolsters, or whether it peruses or composes or both…. I’m speculating both) it offers is phpexcellib from SIMITGROUP.

All case to be quicker than PHPExcel from codeplex or from github, however (except for COM, PUNO Ilia’s wrapper around libXl and spout) they don’t offer both perusing and composing, or both xls and xlsx; might never again be upheld; and (while I haven’t tried Ilia’s expansion) just COM and PUNO offers the same level of control over the made exercise manual.

Source: http://stackoverflow.com/questions/3930975/alternative-for-php-excel

How to Execute a JAR File from the Terminal on Ubuntu?

Learn how to run JAR files directly from the terminal on ubuntu. Includes syntax examples, troubleshooting tips, and Java runtime requirements.

To execute .jar file, java command should be used as below:

java -jar {path_of_the_file}/{file_name}.jar

And to execute above command, Java package must be installed on Ubuntu PC. To check if java package is already installed, execute below command in a terminal:

java -version

It should display current version of Java package installed.

If it displays “The program java can be found in the following packages”, It means Java hasn’t been installed yet. Execute below command in a terminal to install java package,

sudo apt-get install default-jre

This will install the Java Runtime Environment (JRE) only not Java Development Kit (JDK). If Java Development Kit (JDK) is needed, which is usually needed to compile Java applications, execute the following command in terminal:

sudo apt-get install default-jdk

That is everything to install Java. Now run first command to execute .jar file.

SMTP Errors and Reply Codes Explained (Complete Guide)

Understand SMTP errors and reply codes like 421, 450, 550, and more. This guide explains what each code means and how to troubleshoot email delivery issues.

After execution of every request, SMTP server may return a particular message with some code. The problem is that it will generally be very cryptic, like “550 Requested action not taken: mailbox unavailable” or “421 Try again later”.

First of all: not every code is an error. Sometimes it’s just a response containing a detail about the SMTP server or an answer to a command.

Secondly: every code consist of three digits, and each conveys a particular information.

  • The first one defines whether the server has accepted the command, fulfilled an action, run into a temporary issue, encountered an error etc;
  • The second and the third one refine the description further, stating if there’s been a syntactic problem, or a connection trouble etc.

Unfortunately, different servers sometimes use these codes in a different way, making the whole thing even more complicated… Anyhow, the most critical series of error messages is the 5xx one, and especially the ones from 550 to 559. In particular, you will probably get a lot of 550 SMTP error codes – that is, a problem that concerns the recipient’s email address.

Finally, remember that it’s much easier to deal with these error codes if you choose to rely on a professional SMTP server that will help you solve any issue.

And here’s a list of the main SMTP response codes, with description and an action/guideline to solve it.

CODE

DESCRIPTION

ACTIONS

101

The server is unable to connect.

Try to change the server’s name (maybe it was spelt incorrectly) or the connection port.

111

Connection refused or inability to open an SMTP stream.

This error normally refers to a connection issue with the remote SMTP server, depending on firewalls or misspelled domains. Double-check all the configurations and in case ask your provider.

211

System status message or help reply.

It comes with more information about the server.

214

A response to the HELP command.

It contains information about your particular server, normally pointing to a FAQ page.

220

The server is ready.

It’s just a welcome message. Just read it and be happy that everything is working (so far)!

221

The server is closing its transmission channel. It can come with side messages like “Goodbye” or “Closing connection”.

The mailing session is going to end, which simply means that all messages have been processed.

250

Its typical side message is “Requested mail action okay completed”: meaning that the server has transmitted a message.

The oppsite of an error: everything has worked and your email has been delivered.

251

“User not local will forward”: the recipient’s account is not on the present server, so it will be relayed to another.

It’s a normal transfer action.

252

The server cannot verify the user, but it will try to deliver the message anyway.

The recipient’s email account is valid, but not verifiable. Normally the server relays the message to another one that will be able to check it.

354

The side message can be very cryptic (“Start mail input end <CRLF>.<CRLF>”). It’s the typical response to the DATA command.

The server has received the “From” and “To” details of the email, and is ready to get the body message.

420

“Timeout connection problem”: there have been issues during the message transfer.

This error message is produced only by GroupWise servers. Either your email has been blocked by the recipient’s firewall, or there’s a hardware problem. Check with your provider.

421

The service is unavailable due to a connection problem: it may refer to an exceeded limit of simultaneous connections, or a more general temporary problem.

The server (yours or the recipient’s) is not available at the moment, so the dispatch will be tried again later.

422

The recipient’s mailbox has exceeded its storage limit.

Best is to contact contact the user via another channel to alert him and ask to create some free room in his mailbox.

431

Not enough space on the disk, or an “out of memory” condition due to a file overload.

This error may depend on too many messages sent to a particular domain. You should try again sending smaller sets of emails instead of one big mail-out.

432

Typical side-message: “The recipient’s Exchange Server incoming mail queue has been stopped”.

It’s a Microsoft Exchange Server’s SMTP error code. You should contact it to get more information: generally it’s due to a connection problem.

441

The recipient’s server is not responding.

There’s an issue with the user’s incoming server: yours will try again to contact it.

442

The connection was dropped during the transmission.

A typical network connection problem, probably due to your router: check it immediately.

446

The maximum hop count was exceeded for the message: an internal loop has occurred.

Ask your SMTP provider to verify what has happened.

447

Your outgoing message timed out because of issues concerning the incoming server.

This happens generally when you exceeded your server’s limit of number of recipients for a message. Try to send it again segmenting the list in different parts.

449

A routing error.

Like error 432, it’s related only to Microsoft Exchange. Use other routing method.

450

“Requested action not taken – The user’s mailbox is unavailable”. The mailbox has been corrupted or placed on an offline server, or your email hasn’t been accepted for IP problems or blacklisting.

The server will retry to mail the message again, after some time. Anyway, verify that is working on a reliable IP address.

451

“Requested action aborted – Local error in processing”. Your ISP’s server or the server that got a first relay from yours has encountered a connection problem.

It’s normally a transient error due to a message overload, but it can refer also to a rejection due to a remote antispam filter. If it keeps repeating, ask your SMTP provider to check the situation. (If you’re sending a large bulk email with a free one that can be a common issue).

452

Too many emails sent or too many recipients: more in general, a server storage limit exceeded.

Again, the typical cause is a message overload. Usually the next try will succeed: in case of problems on your server it will come with a side-message like “Out of memory”.

471

An error of your mail server, often due to an issue of the local anti-spam filter.

Contact your SMTP service provider to fix the situation.

500

A syntax error: the server couldn’t recognize the command.

It may be caused by a bad interaction of the server with your firewall or antivirus. Read carefully their instructions to solve it.

501

Another syntax error, not in the command but in its parameters or arguments.

In the majority of the times it’s due to an invalid email address, but it can also be associated with connection problems (and again, an issue concerning your antivirus settings).

502

The command is not implemented.

The command has not been activated yet on your own server. Contact your provider to know more about it.

503

The server has encountered a bad sequence of commands, or it requires an authentication.

In case of “bad sequence”, the server has pulled off its commands in a wrong order, usually because of a broken connection. If an authentication is needed, you should enter your username and password.

504

A command parameter is not implemented.

Like error 501, is a syntax problem; you should ask your provider.

510/511

Bad email address.

One of the addresses in your TO, CC or BBC line doesn’t exist. Check again your recipients’ accounts and correct any possible misspelling.

512

A DNS error: the host server for the recipient’s domain name cannot be found.

Check again all your recipients’ addresses: there will likely be an error in a domain name (like mail@domain.coom instead of mail@domain.com).

513

“Address type is incorrect”: another problem concerning address misspelling. In few cases, however, it’s related to an authentication issue.

Doublecheck your recipients’ addresses and correct any mistake. If everything’s ok and the error persists, then it’s caused by a configuration issue (simply, the server needs an authentication).

523

The total size of your mailing exceeds the recipient server’s limits.

Re-send your message splitting the list in smaller subsets.

530

Normally, an authentication problem. But sometimes it’s about the recipient’s server blacklisting yours, or an invalid email address.

Configure your settings providing a username+password authentication. If the error persists, check all your recipients’ addresses and if you’ve been blacklisted.

541

The recipient address rejected your message: normally, it’s an error caused by an anti-spam filter.

Your message has been detected and labeled as spam. You must ask the recipient to whitelist you.

550

It usually defines a non-existent email address on the remote side.

Though it can be returned also by the recipient’s firewall (or when the incoming server is down), the great majority of errors 550 simply tell that the recipient email address doesn’t exist. You should contact the recipient otherwise and get the right address.

551

“User not local or invalid address – Relay denied”. Meaning, if both your address and the recipient’s are not locally hosted by the server, a relay can be interrupted.

It’s a (not very clever) strategy to prevent spamming. You should contact your ISP and ask them to allow you as a certified sender. Of course, with a professional SMTP provider you won’t ever deal with this issue.

552

“Requested mail actions aborted – Exceeded storage allocation”: simply put, the recipient’s mailbox has exceeded its limits.

Try to send a lighter message: that usually happens when you dispatch emails with big attachments, so check them first.

553

“Requested action not taken – Mailbox name invalid”. That is, there’s an incorrect email address into the recipients line.

Check all the addresses in the TO, CC and BCC field. There should be an error or a misspelling somewhere.

554

This means that the transaction has failed. It’s a permanent error and the server will not try to send the message again.

The incoming server thinks that your email is spam, or your IP has been blacklisted. Check carefully if you ended up in some spam lists, or rely on a professional SMTP service that will nullify this problem.

Fix “Can’t Upgrade Due to Low Disk Space on /boot” in Linux

Learn how to resolve the ‘low disk space on /boot’ error during Linux upgrades. Follow step-by-step solutions to free up /boot and complete your system updates.

Your /boot partition is filled with old kernels. It does that sometimes, not sure why it is never fixed. You can easily remove the old kernels if you know which packages they came in.

First check uname -a to check your current version.

Then run the following command:

dpkg -l 'linux-*' | sed '/^ii/!d;/'"$(uname -r | sed "s/\(.*\)-\([^0-9]\+\)/\1/")"'/d;s/^[^ ]* [^ ]* \([^ ]*\).*/\1/;/[0-9]/!d'

This command will list all packages that you no longer need. I don’t like removing them automatically, I like to be in control when it comes to removing kernels. So for every package listed do the following:

sudo apt-get -y purge some-kernel-package

Intermezzo

This intermezzo describes in more detail how the commands work and tries to fix an issue with linux-libc-dev:amd64. Most users can skip this paragraph.

  • dpkg -l 'linux-*' list all packages that have a name starting with ‘linux-‘
  • sed '/^ii/!d; remove all lines that do *not* start withii`
  • uname -r | sed "s/\(.*\)-\([^0-9]\+\)/\1/" find the current running kernel version
  • /'"$(uname -r | sed "s/\(.*\)-\([^0-9]\+\)/\1/")"'/d Remove all lines, except the ones containing the current running kernel version number
  • s/^[^ ]* [^ ]* \([^ ]*\).*/\1/ For each line list only the package name
  • /[0-9]/!d Remove lines that do not contain numbers.

To fix Frederick Nord’s issue I think the command can be amended as follows:

dpkg -l 'linux-*' | sed '/^ii/!d;/'"$(uname -r | sed "s/\(.*\)-\([^0-9]\+\)/\1/")"'/d;s/^[^ ]* [^ ]* \([^ ]*\).*/\1/;/[0-9]/!d;/^linux-\(headers\|image\)/!d'

It basically adds an extra filter:

  • /^linux-(headers\|image)/!d Delete all lines that do not start with linux-headers or linux-image

/Intermezzo

Where some-kernel-package can be replaced with one of the packages listed. Just beware that you don’t remove the kernel packages that are in current use (as listed by the uname -a) eg. sudo apt-get purge -y linux-headers-3.0.0-12 etc.

It can be automated further using the xargs command, but I don’t like that. It is a personal thing. However, here’s the command to do so:

dpkg -l 'linux-*' | sed '/^ii/!d;/'"$(uname -r | sed "s/\(.*\)-\([^0-9]\+\)/\1/")"'/d;s/^[^ ]* [^ ]* \([^ ]*\).*/\1/;/[0-9]/!d' | xargs sudo apt-get -y purge

This is what my /boot looks like, one spare kernel (2.6.38-11) just in case and 3.2.0-24 being current:

$ ls -l /boot
total 59388
-rw-r--r-- 1 root root   730545 Sep 13  2011 abi-2.6.38-11-generic
-rw-r--r-- 1 root root   791023 Apr 25 13:51 abi-3.2.0-24-generic
-rw-r--r-- 1 root root   130326 Sep 13  2011 config-2.6.38-11-generic
-rw-r--r-- 1 root root   140341 Apr 25 13:51 config-3.2.0-24-generic
drwxr-xr-x 3 root root     5120 May 27 17:46 grub
-rw-r--r-- 1 root root 20883146 Oct  1  2011 initrd.img-2.6.38-11-generic
-rw-r--r-- 1 root root 22474219 May  5 09:04 initrd.img-3.2.0-24-generic
drwxr-xr-x 2 root root    12288 Apr 16  2009 lost+found
-rw-r--r-- 1 root root   176764 Nov 27 11:00 memtest86+.bin
-rw-r--r-- 1 root root   178944 Nov 27 11:00 memtest86+_multiboot.bin
-rw------- 1 root root  2656297 Sep 13  2011 System.map-2.6.38-11-generic
-rw------- 1 root root  2884358 Apr 25 13:51 System.map-3.2.0-24-generic
-rw------- 1 root root     1369 Sep 13  2011 vmcoreinfo-2.6.38-11-generic
-rw------- 1 root root  4526784 Sep 13  2011 vmlinuz-2.6.38-11-generic
-rw------- 1 root root  4965776 Apr 25 13:51 vmlinuz-3.2.0-24-generic

And file system usage:

$ df -h /boot
Filesystem Size Used Avail Use% Mounted
/dev/sda5  228M  63M  154M  29% /boot