This post is almost like a place where I dump a bunch of stuff that I have used once or twice in previous projects. Some are vary basic, others not so much.
I plan on editing this post whenever I encounter something new and useful that I think would be a good addition.
MySQL server version
Let's start off with a basic one - retreiving the version of the db server that you are connected to. This will probably come in handy when evaluating how queries are written and what features are available to developers when the database itself is not under your control.
SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.28 |
+-----------+
As an added bonus to this one, you could find all variables (the ones prefixed with @@) by running the SHOW VARIABLES command.
SHOW VARIABLES;
+-----------------------------+--------+
| Variable_name | Value |
+-----------------------------+--------+
| activate_all_roles_on_login | OFF |
...
| version | 8.0.28 |
+-----------------------------+--------+
Timezones
Retreiving the database timezone settings. There are a few of them and sometimes there is a little bit of detective work to be done. The example data below is for my local development database, but overall I would not suggest to use anything other than UTC for production databases. For more info check the official docs at https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html.
SHOW VARIABLES LIKE '%time_zone%'; -- Alternative: SELECT @@system_time_zone, @@time_zone;
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| system_time_zone | W. Europe Summer Time |
| time_zone | SYSTEM |
+------------------+-----------------------+
Retrieve the current global and session time zone values.
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+----------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone; |
+--------------------+----------------------+
| SYSTEM | SYSTEM |
+--------------------+----------------------+
Size of stored data
This could be very useful when monitoring the database disc space being used.
SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS "Size (MB)" FROM information_schema.tables GROUP BY table_schema;
+----------+-----------+
| Database | Size (MB) |
+----------+-----------+
| mysql | 2.7 |
| sakila | 6.5 |
...
+----------+-----------+
-- For larger (GigaByte+) databases use:
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema;
Counting rows in large databases
A normal query would be something like "SELECT COUNT(unique_column_in_this_db) FROM table_in_this_db;" and for smaller databases this works just fine. As the amount of stored data increases however, this might cause timeouts. I generally use this if the number of rows in a table is above a couple of millions, but that of course depends on a lot of things. If the main reason to get the number of rows is for monitoring or similar, there are better options.
Each table have a somewhat detailed status saved that is being updated when changes happen. To retrieve this info for a table:
USE sakila; -- I'm just using example db for this
SHOW TABLE STATUS LIKE "actor";
+-------+------+-------------+--------------+-----
| Name | Rows | Data_length | Index_length | ...
+--------------+-------------+--------------+-----
| actor | 200 | 16384 | 16384 | ...
+-------+------+-------------+--------------+-----
Searching for column names
Let's say you end up with a large, confusing database that lacks any sort of standard or shared vision. You might have to spend a lot of time to figure out how the data is linked to other data (because who uses foreign keys right?). At some point you may have to search for columns by name in order to figure out how things are connected between tables:
USE sakila; -- I'm just using example db for this
SELECT table_name, column_name FROM information_schema.columns WHERE column_name LIKE "%actor_id%";
+------------+-------------+
| TABLE_NAME | COLUMN_NAME |
+------------+-------------+
| actor | actor_id |
| film_actor | actor_id |
| actor_info | actor_id |
+------------+-------------+
Check for duplicates
At some point, you probably have to check for duplicate data. One way to do this is by grouping the data, count instances and ignoring all rows that only have 1 instance. Something like this:
USE sakila; -- I'm just using example db for this
SELECT film_id, COUNT(actor_id) FROM film_actor GROUP BY film_id HAVING COUNT(actor_id) > 1;
+---------+-----------------+
| film_id | COUNT(actor_id) |
+---------+-----------------+
| 1 | 10 |
| 2 | 4 |
| 3 | 5 |
...
+---------+-----------------+
In the example above, I'm really just counting the number of actors linked to films so the query has to be altered to suit the current needs. But the example shows how to do the duplicate check if the table was not meant to contain duplicates.
Unblock IP after connection errors
If you encounter the error:
Host '192.168.0.1' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
you probably have to unblock the now blocked IP's. The reason why the IP has been blocked could be a number of different things, but basically MySQL server found that a program running on another IP is suspicious since it has tried and failed to connect many times (default value is 100). When that happens, MySQL will block it and refuse to accept connections from it.
I have had this happen when a BI-software basically spammed the server without checking for a response. Every X minutes the same very heavy query was run even if the previous query has not yet completed. Eventually the server simply said no and blocked that server IP.
SHOW VARIABLES LIKE "max_connect_errors"; -- Check current limit
SHOW VARIABLES LIKE "max_connections"; -- Could be useful when investigating
SELECT * FROM performance_schema.host_cache; -- List currently blocked IP's
FLUSH HOSTS; -- Will clear all IP blocks
Obviously you should not flush hosts until you have figured out what the cause of the block is. If not - it is probably just a matter of time until the block appears again.
Searching for specific text in large databases
The situation that I'm in when this mess of a an SQL query have to be revived; The database is very large, unstructured, impossible to get a grip on and I need to find what table and column a specific line of text is in. I would assume that this is a somewhat specific case, but it did happen to me recently. For small and medium databases, the built in search in MySQL Workbench is great, but for larger databases it might timeout and you might have to search each table individually. Instead of doing that, I constructed the following mess:
-- Select all possible columns in all tables that contains data of text datatypes
SELECT
CONCAT('SELECT ',QUOTE(tb),',',QUOTE(col),',COUNT(1) ExistsHere FROM ',db,'.',tb,' WHERE `',col,'`=''',SearchString,''' UNION ALL ') SearchSQL
FROM
(
SELECT
table_schema db, table_name tb, column_name col FROM information_schema.columns
WHERE
table_schema = 'sakila'
AND
(column_type LIKE 'char(%' OR column_type LIKE 'varchar(%' OR column_type LIKE '%text')
AND NOT
table_name IN ('tbl_name_to_ignore','tbl_name2_to_ignore')
LIMIT
0, 1000
) A, (SELECT 'Mike' AS SearchString) B; -- The actual string to search for
-- The result is a bunch of rows that are select statements of their own:
SELECT 'actor','first_name',COUNT(1) ExistsHere FROM sakila.actor WHERE `first_name`='Mike' UNION ALL
SELECT 'actor','last_name',COUNT(1) ExistsHere FROM sakila.actor WHERE `last_name`='Mike' UNION ALL
SELECT 'customer','first_name',COUNT(1) ExistsHere FROM sakila.customer WHERE `first_name`='Mike' UNION ALL
SELECT 'customer','last_name',COUNT(1) ExistsHere FROM sakila.customer WHERE `last_name`='Mike' UNION ALL
SELECT 'staff','first_name',COUNT(1) ExistsHere FROM sakila.staff WHERE `first_name`='Mike' UNION ALL
SELECT 'staff','last_name',COUNT(1) ExistsHere FROM sakila.staff WHERE `last_name`='Mike' UNION ALL
SELECT 'staff','email',COUNT(1) ExistsHere FROM sakila.staff WHERE `email`='Mike' UNION ALL
SELECT 'staff','username',COUNT(1) ExistsHere FROM sakila.staff WHERE `username`='Mike' UNION ALL
-- Remove the trailing UNION ALL from the result above (last row only) and run the whole thing
+----------+------------+------------+
| | | ExistsHere |
+----------+------------+------------+
| actor | first_name | 0 |
| actor | last_name | 0 |
| customer | first_name | 1 |
| customer | last_name | 0 |
| staff | first_name | 1 |
| staff | last_name | 0 |
| staff | email | 0 |
| staff | username | 1 |
+----------+------------+------------+
Yes, I know that it is not the prettiest, but it works. There are probably many better ways to do this, but the problem this solves is not such a common problem that I feel the need to research and create a fancy version. I will park this one firmly in the "not proud of it, but if it works - it works" garage.
