When an application runs a query in MySQL, from time to time, it may receive various errors. Some are related to syntax errors in the query text itself, some occur because the statement attempted an illegal operation such as for example writing a duplicate value into a column with unique constraint. But there are a few that are not as easy to figure out, because they have no direct relationship with the actual work being done. One of such error messages reads "Lost connection to MySQL server during query". What does it actually mean? If it appears frequently, how to diagnose what the problem may be?
Read MoreHow important a primary key design can be for MySQL performance? The answer is: Extremely! If tables use InnoDB storage engine, that is.
Read MoreFor as long as it is only about a few of them, it is as simple as looking at the SHOW PROCESSLIST output for thread identifiers to kill. They can be found in the first column called Id. These values can be passed to KILL thread_id command in MySQL. The problems appear with more complex scenarios. What if one needs to terminate all queries running longer than ten seconds? Doing copy&paste; repetitively could take a lot of time with twenty or so candidate threads. This can be done much more efficiently.
Read MoreIn an earlier post titled "How to work with a long process list in MySQL", we showed a neat way to work with the process list by using various shell tools. But some of that can also be done using pure SQL.
Read MoreIf for any reason you need to change the size of InnoDB log files (also known as transaction logs), but not sure how to do it, this post will guide you through the steps.
Read More