Author: Vinay

Various Queries For Showing User Permissions To Objects

SELECT
   u.usename,
   s.schemaname,
   has_schema_privilege (u.usename, s.schemaname, 'create') AS user_has_select_permission,
   has_schema_privilege (u.usename, s.schemaname, 'usage') AS user_has_usage_permission
FROM
   pg_user u
   CROSS JOIN
      (SELECT DISTINCT
         schemaname
      FROM
         pg_tables) s
WHERE u.usename = 'tim'
   AND s.schemaname = 'dbo';
SELECT
   u.usename,
   t.schemaname || '.' || t.tablename,
   has_table_privilege (u.usename, t.tablename, 'select') AS user_has_select_permission,
   has_table_privilege (u.usename, t.tablename, 'insert') AS user_has_insert_permission,
   has_table_privilege (u.usename, t.tablename, 'update') AS user_has_update_permission,
   has_table_privilege (u.usename, t.tablename, 'delete') AS user_has_delete_permission,
   has_table_privilege (
      u.usename,
      t.tablename,
      'references'
   ) AS user_has_references_permission
FROM
   pg_user u
   CROSS JOIN pg_tables t
WHERE u.usename = 'tim' --    AND t.tablename = 'myTableName'
    AND t.schemaname = 'dbo';
SELECT
   u.usename,
   t.schemaname || '.' || t.tablename,
   has_table_privilege (u.usename, t.tablename, 'select') AS user_has_select_permission,
   has_table_privilege (u.usename, t.tablename, 'insert') AS user_has_insert_permission,
   has_table_privilege (u.usename, t.tablename, 'update') AS user_has_update_permission,
   has_table_privilege (u.usename, t.tablename, 'delete') AS user_has_delete_permission,
   has_table_privilege (
      u.usename,
      t.tablename,
      'references'
   ) AS user_has_references_permission
FROM
   pg_user u
   CROSS JOIN pg_tables t
WHERE --    u.usename = 'tim'
--    AND t.tablename = 'myTableName'
    user_has_insert_permission = 't'
   AND t.schemaname = 'dbo';

Detecting And Killing Duplicate Queries

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;

Error In Executing Mysqldump For 8.0

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

Mysqldump Syntax

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.

Drop All Foreign Key Constraints For A Schema

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 'schemaName' ;