In this post, we will learn about the tools available to investigate issues with InnoDB locks preventing queries to run. The two commands that can be used in all versions of MySQL to investigate locks preventing InnoDB queries to proceed are SHOW [FULL] PROCESSLIST and SHOW ENGINE INNODB STATUS. Additionally in MySQL 5.1 using InnoDB […]
mysql
Troubleshooting MySQL Query hung – “Waiting for Table Flush”
Question: The Queries are hung and Process list shows: “Waiting for table flush”. How to troubleshoot the issue? There are two main causes of this problem. The most obvious and easiest to detect is a pending FLUSH TABLES WITH READ LOCK. It is obvious because the pending FLUSH can be seen in the process list. […]
MySQL: How To Find Queries Taking Longer Than N Seconds
Question: How to find queries taking more than a given amount of time to complete? The simplest way to find long-running queries is to look at the process list. There are various options for how to do this depending on your version and whether you have the Sys Schema installed. The examples in this post […]
MySQL Backup stuck at “Starting to lock all the tables”
The Problem When starting a backup, the message “Starting to lock all the tables” appears and then backup stalls, it will not continue even after many hours. MySQL Enterprise Backup connection on the server can be observed with SHOW PROCESSLIST to be in the following state: State: Waiting to get readlock Info: FLUSH TABLES WITH […]
How to Change Default Character Sets in MySQL using ALTER TABLE Statement
To put it simply, there are two ways you can alter the table to use a new character set. 1. ALTER TABLE tablename DEFAULT CHARACTER SET utf8; This will alter the table to use the new character set as the default, but as a safety mechanism, it will only change the table definition for the […]
Which Character Set Should Be Used To Store Emojis in MySQL Database
As some emojis are using unicode code points beyond what can be represented with a three byte utf8 encoding, it is recommended to use utf8mb4 for columns that are used to store emojis. Alternatives are ucs16, ucs16le, and ucs32. For example: mysql> CREATE TABLE emoji (id int unsigned NOT NULL PRIMARY KEY, val varchar(12) NOT […]
How to Change the Default Character Set and Collation for a Database in MySQL
You can specify the character set and collation to use by default for tables in a database. Checking the current character set and collation To determine the default character set and collation for a database, enter this SQL statement: mysql> SHOW CREATE DATABASE database1; *************************** 1. row *************************** Database: database1 Create Database: CREATE DATABASE `database1` […]
How to Rollback RPM Upgrade of the MySQL Server
Question: How can I rollback an RPM upgrade of the MySQL Server that I don’t want to keep? There may be a number of reasons why you need to rollback an upgrade of the server, but the last thing you want is to perform the upgrade, have it fail, and then can’t get back to […]
How to List Users and Privileges in MySQL
The information about the users is stored in the user system table in the mysql database. A user consists of two parts: the user name and the host the user connected from, so to get the list of users, it is necessary to include both the user and host columns from the user table: mysql> […]
How to gather information on the MySQL 8 Roles and Privileges assigned to a user without using SHOW GRANTS
List the active and inactive roles available on the system Active Role: a roll that has at least 1 user account assigned to the role: mysql> SELECT DISTINCT u.User ‘Role Name’, if(e.from_user is NULL,0, 1) Active FROM mysql.user u LEFT JOIN role_edges e ON e.from_user=u.user WHERE u.account_locked=’Y’ AND u.password_expired=’Y’ AND u.authentication_string=”; +———–+——–+ | Role Name […]