Tag: mysql

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 thread IDs of the duplicates, keeping the oldest version intact
SELECT
  DISTINCT(p2.id)
FROM
  information_schema.processlist p1
  INNER JOIN information_schema.processlist p2
    ON p2.info = p1.info
    AND p2.id > p1.id
    AND p1.time > 300
    AND p1.command IS NOT NULL;
-- returns actual executable KILL statements for the duplicates, suitable
-- for a mysql -N -B command line
SELECT DISTINCT
  (CONCAT('KILL ', p2.id, ';')) AS killstatements
FROM
  information_schema.processlist p1
  INNER JOIN information_schema.processlist p2
    ON p2.info = p1.info
    AND p2.id > p1.id
    AND p1.time > 300
    AND p1.command IS NOT NULL;

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 following parameter to the mysqldump command line:

--column-statistics=0

Here is the standard mysqldump syntax I always use.  This dumps routines and triggers, does so in the most expeditious manner possible, and adds DROP xxxxx statements before all CREATE statements, just in case.  It also adds the FORCE parameter in case it runs into issues with views. The “xxxx” at the end can be either one schema or a space-delimited list of schemata.

mysqldump --add-drop-database --add-drop-table --triggers --routines --single-transaction --opt --quick --dump-date --force --databases xxxx > dumpfile.sql

Production dump timing:

[mysqladm@dbvrp83383:/mysbackupdd] time mysqldump --add-drop-database --add-drop-table --triggers --routines --single-transaction --opt --quick --dump-date --force --databases sms > sms.sql         

real    5m47.223s
user    3m23.495s
sys     0m19.139s

In dumping from a 8.x server via a lower version mysqldump, add…

--column-statistics=0

…to avoid those odd stats errors.