Category : MySQL snippets


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 ..

Read more


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 ..

Read more


— 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 ..

Read more


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 ..

Read more


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..

Read more


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: ..

Read more


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 * ..

Read more


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 ..

Read more


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 ..

Read more


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 ..

Read more


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 ..

Read more


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 ..

Read more


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..

Read more