mysql

The preferred solution depends on the version of MySQL. If you are using MySQL 5.7 or later with metadata lock instrumentation enabled in the Performance Schema (available in MySQL 5.7 and later), it is possible to get the information directly from the performance_schema.metadata_locks table. Otherwise, there is no direct way to get the information. To determine whether metadata lock instrumentation is enabled, check the performance_schema.setup_instruments table like: mysql> SELECT * FROM … [Read more...] about MySQL: Identify what user and thread are holding on to a meta data lock that is preventing other queries from running

In this post, we will learn about what options are available if you need to automatically kill long-running queries. MySQL 5.7 and Later For SELECT Statement Starting from MySQL 5.7.4 there is support for automatic timeouts of read-only SELECT statements (the following discussion assumes 5.7.8 or later as the feature had changes to the option/hint name and syntax). There are two ways to set an upper limit of how long a read-only SELECT statement is allowed to execute: the … [Read more...] about MySQL: How to kill a Long Running Query using max_execution_time

This is general guidance for DBAs who are looking to use the MySQL sys schema. Introduction The MySQL sys schema is a collection of views, stored procedures, and stored functions to help MySQL database administrators get insight into what MySQL is doing and to help use the Performance Schema. However while the sys schema primarily use the Performance Schema, it also includes information from the Information Schema. The views and stored programs available depends on the version of the sys … [Read more...] about How to install and configure MySQL sys schema

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 Plugin and in MySQL 5.5 and later, it is possible to use the InnoDB tables in the Information Schema to get details about InnoDB locks. Each of these methods … [Read more...] about How to Troubleshoot InnoDB Lock Issues

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. The less obvious is an internal table flush caused by performing ANALYZE, OPTIMIZE, RENAME, ALTER, REPAIR, or any other command that changes the underlying table's stats or … [Read more...] about Troubleshooting MySQL Query hung – “Waiting for Table Flush”

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 will all look for queries that have taken longer than 10 seconds. Change the duration as required. The Sys Schema session View In MySQL 5.6 or later with the Sys Schema installed, you can … [Read more...] about MySQL: How To Find Queries Taking Longer Than N Seconds

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 READ LOCK The Solution Locking the server is required for the backup to get a consistent snapshot of any non-transactional data such as MyISAM tables, table … [Read more...] about MySQL Backup stuck at “Starting to lock all the tables”

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 default character set. That is, existing character fields will have the old character set per column. For example: mysql> create table mybig5 (id int not null auto_increment primary key, -> … [Read more...] about How to Change Default Character Sets in MySQL using ALTER TABLE Statement

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 NULL) DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.57 sec) -- Insert a value containing the emojis "high voltage sign" and "face screaming in fear": mysql> … [Read more...] about Which Character Set Should Be Used To Store Emojis in MySQL Database

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` /*!40100 DEFAULT CHARACTER SET latin1 */ You can also obtain this information from … [Read more...] about How to Change the Default Character Set and Collation for a Database in MySQL