Kill Sleeping MySQL Processes using Quick PHP Script

Our server was getting swamped with too many sleeping connections causing "too many connections" error. During production hours, manually killing them isn't an option, there are just too many and MySQL nor MariaDB doesn't have a native tool for it. I needed it fast, so I wrote a quick PHP code which can then be run every minute as a CRON job.

Here's the code:

<?php
mysql_connect('yourhost', 'username', 'password');
$res = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($res)) {
  $pid=$row["Id"];
  if ($row['Command']=='Sleep') {
      if ($row["Time"] > 3 ) { //any sleeping process more than 3 secs
         $sql="KILL $pid";
         echo "\n$sql"; //added for log file
         mysql_query($sql);
      }
  }
}

You can then save this to a file mysqlkillsleeping.php, and add it as a CRON job running every minute

vim /etc/crontab

*/1 * * * * root php /path/of/mysqlkillsleeping.php 2>&1 >> /var/log/mysqlsleep.log

MySQL Error on UPDATE - Integrity constraint violation: 1062 Duplicate entry

I was doing a MySQL UPDATE operation and obviously it shouldn't create new records. So why would it throw an duplicate entry exception? It can make you scratching your head for a bit of time if you're not aware.

The problem is actually quite simple, you are updating multiple records, having the PRIMARY KEY or UNIQUE KEY included in the input data and the resulting UPDATE operation matches more than one row. Hence one or more rows having a different PRIMARY KEY or UNIQUE KEY is being updated with the input value which already exists in another row and the attempt to update the other rows throws a duplicate error.

Fix this by simply using the UNIQUE KEY or PRIMARY KEY in the WHERE condition.

MySQL - ERROR 1045 (28000): Access denied for user

When you get a weird permissions denied error while connecting to MySQL considering that,

1. You have properly granted privileges to the user.
2. That the user logs-in from a host that's permitted by the the database server.
3. That you have entered the correct username + password combination.
4. That you are accessing a database that the user have been granted permission

Then, you are most likely a victim of a setup that has an anonymous user. How to verify and fix.

Comparing Data Between Master and Slave on Mysql

In a replicated environment it is quite common that during the initial stages of setup, frequent replication breaks happen. The break happens because the Slave resulted differently from what was observed in the Master. This usually happens with statement-based replication which is due to many reasons like duplicate keys, partial success of a bulk update that was not encapsulated in a transactional clause, deadlocks and many more.

Comparing and Synchronizing of MySQL Databases from Master to Slave in a Replicated Environment Simplified

Introduction

Legacy systems that were built in a standalone infrastructure mostly suffer architecture design loose-ends in foreseeing effects on a distributed and replicated environment regardless of what technology is used. Systems using the community edition of MySQL as database are good examples of having to encounter mind-boggling challenges in moving to distributed.
In this particular topic, I will be discussing MySQL replication challenges and efficient ways in making sure that a Master (Publisher) is always synchronized with its Slaves (Subscribers) satisfying most of the following,

Retrieving Data Hierarchies on a SQL Table: Adjacency List Model

Adjacency List model is more popularly identified by an obvious parent-to-child relationship in the table schema itself. This model represents the hierarchy tree in the form of a list.

Using the following table, we will discuss how to manipulate data hierarchies using this model.