This requires that metadata lock instrumentation be enabled. UPDATE performance_schema.setup_instruments SET ENABLED = ‘YES’ WHERE NAME = ‘wait/lock/metadata/sql/mdl’; Once that’s in place, you can do this: SELECT ml.object_schema, ml.object_name, p.id, p.user, p.state, ml.lock_type, ml.lock_duration, ml.lock_status, p.time, LEFT(p.info, 100) FROM performance_schema.metadata_locks ml JOIN performance_schema.threads ..
Category : MySQL snippets
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] … FLUSH PRIVILEGES; Why the syntax is slightly different from the GRANT command is ..
In attempting to use the mysqldump utility provided with MySQL 8.x against a 5.7 server, you may receive the following error message: mysqldump: Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ‘$.”number-of-buckets-specified”‘) This is due to the INFORMATION_SCHEMA in 8.0 having more columns to support several new features in 8.x. This error can be silenced by adding the ..
Since this is an area not everyone has to deal with, I thought a quick one-liner was in order. MySQL can have one of three different binary log formats, which are used to facilitate replication. MySQL masters have binary logging enabled and the binary logs are streamed to all the replicas, where the logs are ..
Problem statement: A rogue application process is sending multiple copies of exactly the same query to the database so you end up with a ton of them DBA charge: (a) write a query that checks for these, and (b) for my own purposes, a quick way to kill the duplicate offenders. — output just the ..
Edit to spec as necessary: DELIMITER $$ CREATE PROCEDURE trimTable() BEGIN DECLARE ROWS INT; DECLARE rows_deleted INT; SET ROWS = 1; SET rows_deleted = 10000; WHILE ROWS > 0 DO DELETE FROM db.tabA WHERE predicate_col < CURDATE() - INTERVAL 90 DAY ORDER BY `id` LIMIT 10000; SET ROWS = ROW_COUNT(); SET rows_deleted = rows_deleted + ..
— SQL snippet to calculate table fragmentation. SELECT table_schema, TABLE_NAME, ROUND(DATA_LENGTH / 1024 / 1024) AS data_length, ROUND(INDEX_LENGTH / 1024 / 1024) AS index_length, ROUND(DATA_FREE / 1024 / 1024) AS data_free, CONCAT( ROUND( ( data_free / (index_length + data_length) ) * 100 ), ‘%’ ) AS frag_ratio FROM information_schema.tables WHERE DATA_FREE > 0 AND TABLE_SCHEMA ..
Unfortunately, MySQL does not support the INTERSECT operator. However, you can simulate the INTERSECT operator. Let’s create some sample data for the demonstration. The following statements create tables t1 and t2, and then insert data into both tables. 1 2 3 4 5 6 7 8 9 CREATE TABLE t1 ( id INT PRIMARY KEY ); ..
By now, we’re all aware that a utf8 charset and collation is the “right” thing to do, with most folks opting for utf8_general_ci or utf8_unicode_ci. However, lots of installations still default to some form of latin1, which is unfortunate. There are multiple ways to do this, all which functionally do the same thing. Be aware ..
Suppose I try to create a table with a primary key that’s varchar(500), and MySQL complains the key length is longer than the maximum of 1000 bytes. 500 is less than 1000. What’s happening? Plus, a tasty (yet apparently harmless) bug in MySQL. Here’s a statement that will fail on most servers: CREATE TABLE test(c ..
To rename a schema (emulating the now-dropped RENAME DATABASE functionality): SELECT concat(‘RENAME TABLE oldSchema.’,table_name, ‘ TO newSchema.’,table_name, ‘;’) FROM information_schema.TABLES WHERE table_schema=’oldSchema’; Execute the resultant sql, then drop the now-empty old sche..
MySQL is SO irritating in its timestamp limitations, in that you can only have one default timestamp column. (Update 6/12/2013: in version 5.6.x, this restriction will be lifted, so I hear) However, you *can* trick MySQL like this. You can use only one of the definitions in one table. Create both timestamp columns like so: ..
Various ways to get sizing, counts, etc. — Total rows and size for an entire server, excluding system schemata, ordered by size DESC SELECT table_schema,table_name, CONCAT(ROUND(SUM(table_rows) / 1000000,2),’M’) rows, CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), ‘G’) DATA, CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),’G’) idx, CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * ..
SELECT create_time FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ‘your_schema’ AND table_name = ‘your_table’ Unfortunately, reports the last ALTER for Inno..
It sucks, but sometimes ya gotta. You can’t DROP multiple tables, a parent table or a child table until you disable foreign key checks four your current database. Or for that matter, TRUNC the table, either. To disable/enable foreign key checks, use the following syntax. (Don’t ever disable @@GLOBAL, unless you’re really irritated and have ..
mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior: * –routines – FALSE by default * –triggers – TRUE by default This means that if you want to include in an existing backup script also the triggers and stored procedures you only ..
Assumes there is a text file, “instances.txt” that has the IP or symbolic name of the server where the instances are Just put the query in quotes at the end of the script (such as pasting the below into runonall.sh) params=$@ for i in `cat /home/rbyrd2/bin/instances.txt` ; do echo “$i: ” ; mysql -h $i ..
Various crap. SELECT d.id, d.name FROM ireit_analysis.v_ireit_house d, ireit_reference.dictionary_newage r WHERE d.name REGEXP r.word AND ( r.word LIKE ‘%finance%’ || r.definition LIKE ‘%finance%’ || r.word LIKE ‘%bank%’ || r.definition LIKE ‘%bank%’ || r.word LIKE ‘%money%’ || r.definition LIKE ‘%money%’); SELECT d.id, d.name FROM ireit_analysis.v_ireit_house d WHERE d.name STRCMP(SELECT r.word FROM ireit_reference.dictionary_newage r WHERE r.word LIKE ..
Useful when migrating. Point at source DB instance, retrieve grants using this script, pipe through the Add a semicolon in PERL script, and voila, recreate grants on destination instance. #!/bin/bash ( mysql –batch –skip-column-names -e “SELECT user, host FROM user” mysql ) | while read user host do echo “# $user @ $host” mysql –batch ..
DELETE t2.* FROM the_table t1 INNER JOIN the_table t2 ON t2.non_unique_column = t1.non_unique_column AND t2.id &..
CONVERT ALL THE THINGS. Well, almost all the things. #!/bin/bash for database in aaaaa bbbbb ccccc ddddd eeee do thesetables=`mysql -N -B -e “SELECT CONCAT(TABLE_SCHEMA,’.’,TABLE_NAME) \ FROM information_schema.TABLES where TABLE_SCHEMA = \”$database\”” \ AND TABLE_SCHEMA NOT IN (‘mysql’,’information_schema’,’performance_schema’)` alltables=`echo $alltables $thesetables` # change the schema itself mysql -e “ALTER DATABASE $database CHARACTER SET utf8 COLLATE ..
SELECT d.id, d.name, CONCAT_WS(‘ -> ‘,c5.name,c4.name,c3.name,c2.name,c1.name) as cat_string FROM domain d LEFT JOIN category c1 ON c1.id = d.category_id LEFT JOIN category c2 ON c1.parent_id = c2.id LEFT JOIN category c3 ON c2.parent_id = c3.id LEFT JOIN category c4 ON c3.parent_id = c4.id LEFT JOIN category c5 ON c4.parent_i..
If ever there was a TL;DR, this is it. However, it is important to understand the “whys” in my world, so here goes. A full discussion of character sets and encoding is beyond the scope of this document. (If you want more background, I recommend checking out the wikipedia article for a good place to ..
I hates the FKs, my precious. SELECT CONCAT( ‘ALTER TABLE ‘, table_schema, ‘.’, table_name, ‘ DROP FOREIGN KEY ‘, constraint_name, ‘;’ ) FROM information_schema.table_constraints WHERE constraint_type = ‘FOREIGN KEY’ AND table_schema LIKE ‘sch..