REPLACE function

The replace function is another one of my often used data analysis MySQL functions. However, it can create some ugly queries when it is nested many times. Here is an example I how I used it remove some text from server name. I have a client that creates replicas of their servers and names them <servername>_0, _1, _2. etc. I wrote a script to collect database sizes, table sizes, row counts and then each day run some reports on the changes. I only collect the data from the replicas and not the primary server but wanted to remove the replica names and only refer to the "cluster" name.

MySQL REPLACE() replaces all the occurrences of a substring within a string.
https://www.w3resource.com/mysql/string-functions/mysql-replace-function.php

Here is my example query that will give me the last 30 days of data:

SELECT
ss.server_name as server_name,
REPLACE(REPLACE(REPLACE(REPLACE(ss.server_name, '_2', ''),'_1',''),'_0',''),'_3','') as server_name,
ss.total_size_mb as total_size_mb,
ss.date_created,
ss.date as date
FROM growth_stats_lmp.schema_stats ss
WHERE ss.minutes_since_last_timestamp IS NOT NULL -- Removes the entries from a first run of growth_stats collection
AND ss.date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
GROUP BY server_name,schema_name,date;

The replace function will remove the _2, _1, _0, _3 out of the servername column (if it finds those values) and display only the remaining text that hasn't been "replaced out".

Setting up sys schema for Aurora

Here is a good article on how to setup the sys schema for AWS Aurora:

https://www.datadoghq.com/blog/how-to-collect-aurora-metrics/

These are the steps I followed for my Aurora instances:

git clone https://github.com/mysql/mysql-sys.git

cd mysql-sys

./generate_sql_file.sh -v 56 -b -u root

gsed -i '10486d' gen/sys_1.5.1_56_inline.sql

mysql -u root -p -h <Aurora Server> -P 3306  < gen/sys_1.5.1_56_inline.sql

<enter password at prompt>

After setting up sys schema, you will need a user that can view the reports.

If you plan to use MySQL workbench to view the reports, some won't be available unless the user has EXECUTE on the SYS schema and PROCESS globally in addition to SELECT access to databases.  

GRANT SELECT, SHOW VIEW, EXECUTE ON sys.*  TO 'UserName'@'%';

GRANT SELECT ON peformance_schema.*  TO 'UserName'@'%';

GRANT SELECT, SHOW DATABASES, PROCESS ON *.*  TO 'UserName'@'%';

If you don't give the user SELECT access to *.* because you want to limit permissions to not include the mysql schema then you would need to GRANT SELECT to the non system databases. Also make sure the user has a password hash statement after running the GRANT statements so the user doesn't have an empty password. 

Barracuda format causes Aurora read only instances to restart randomly

As of writing this, have found a problem with using Barracuda table format in Aurora.

Per the MySQL manual, to use Barracuda, you can set ROW_FORMAT=COMPRESSED or ROW_FORMAT=DYNAMIC.

From: https://dev.mysql.com/doc/refman/5.6/en/innodb-compression-usage.html

Aurora doesn’t actually support compressed tables and will automatically change the format if you try to use ROW_FORMAT=COMPRESSED. 

Per their documentation:

Amazon Aurora doesn't support compressed tables (that is, tables created with ROW_FORMAT=COMPRESSED).

Copied from: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Migrating.RDSMySQL.Import.html

So, if you want to use Barracuda, you are left with ROW_FORMAT=DYNAMIC. I have a client who wanted to use Barracuda and was using it on a physical machine at their data center. After doing the migration to Aurora, everything automatically changed to Antelope because the table create statements were using ROW_FORMAT=COMPRESSED. We went through and changed everything to ROW_FORMAT=DYNAMIC in order to force the tables to use Barracuda. This client has a sharded application spread out over about 30 instances. Each cluster has a writer end point instance and a replica for read only traffic. After we converted the tables to  Barracuda, the read only replicas would randomly restart. The error on the application side was "org.mariadb.jdbc.internal.util.dao.QueryException: unexpected end of stream". Then they would get a number of errors related to the instance not being available. Then it would work fine for a while. 

It was hard to figure it out but eventually we traced it back to the read only replicas using Barracuda. For a while we transferred all the read only traffic to the writer end points and all the problems stopped. 

If I log onto the writer end point, you can see that I've set the innodb_file_format  and innodb_file_format_max to Barracuda (same settings for read only replica also). 

mysql> show global variables like 'innodb_file_%';

+--------------------------+-----------+

| Variable_name            | Value     |

+--------------------------+-----------+

| innodb_file_format       | Barracuda |

| innodb_file_format_check | ON        |

| innodb_file_format_max   | Barracuda |

| innodb_file_per_table    | ON        |

+--------------------------+-----------+

4 rows in set (0.09 sec)

This query shows I'm on the writer end point:

mysql> show global variables like 'innodb_read_only';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| innodb_read_only | OFF   |

+------------------+-------+

1 row in set (0.12 sec)

mysql> show global variables like 'aurora_version';

+----------------+--------+

| Variable_name  | Value  |

+----------------+--------+

| aurora_version | 1.15.1 |

+----------------+--------+

1 row in set (0.12 sec)

mysql>

mysql> use tmp;

Database changed

mysql>

mysql> DROP TABLE IF EXISTS test_table;

Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE `test_table` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `message` varchar(255) NOT NULL,

    ->   `created_at` datetime NOT NULL,

    ->   PRIMARY KEY (`id`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED;

Query OK, 0 rows affected, 2 warnings (0.13 sec)

mysql> SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'tmp/test_table';

+----------+----------------+------+--------+-------+-------------+------------+---------------+

| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

|      196 | tmp/test_table |    1 |      6 |   138 | Antelope    | Compact    |             0 |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

1 row in set (0.09 sec)

Table became Antelope (Aurora silently changes the format for you).

mysql> DROP TABLE IF EXISTS test_table;

Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE `test_table` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `message` varchar(255) NOT NULL,

    ->   `created_at` datetime NOT NULL,

    ->   PRIMARY KEY (`id`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC ;

Query OK, 0 rows affected (0.11 sec)

mysql> SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'tmp/test_table';

+----------+----------------+------+--------+-------+-------------+------------+---------------+

| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

|      197 | tmp/test_table |   33 |      6 |   139 | Barracuda   | Dynamic    |             0 |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

1 row in set (0.10 sec)

Now the table is Barracuda. 

The issue we seem to have is something to with Aurora starting up with the setting in the parameter group for innodb_file_format_max as Barracuda. The system tables are using Antelope but when it reads a table using Barracuda, it tries to set the innodb_file_format_max to Barracuda but since it is read only it crashes. 

After the read only replica crashes, it seems to fix itself for a while. And then eventually crashes again. I'm not sure how it gets back into a bad state which allows it to crash again. Reverting all the tables back to Antelope solved the issue. 

Slave skip counter terminates the entire transaction

If you have been an admin for MySQL with replication, at some point you are going to need to skip statements on a replica that passed on the master but are failing on the slave. There are too many reason this can happen and depending on the situation it might be safe to skip or might lead to data inconsistencies on the replica.

When you skip the error, it skips the entire transaction. Even if the first part of the transaction would have been successful because there are multiple statements in the transaction and one of them fails, then all of them are skipped on the slave.

This has been accurately described by Jervin Real in this blog post:

https://www.percona.com/blog/2013/07/23/another-reason-why-sql_slave_skip_counter-is-bad-in-mysql/

Something to note is that skipping errors on Aurora is different than on normal MySQL server:

On a normal MySQL slave you would run this;

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

START SLAVE;

show slave status;

If you have thousands of errors and want to skip them all then you can put a large number in the SQL_SLAVE_SKIP_COUNTER. If you have that many error then you probably have larger issues and might want to consider rebuilding the slave to prevent data inconsistency problems.

However on Aurora you can only skip one error at a time:

CALL mysql.rds_skip_repl_error;

show slave status;

 

You don't have to stop and start replication but do need to use the stored procedure provided by the Aurora folks.

I have a client that uses Aurora as  a disaster recovery "site". The client has thousands of databases that are being replicated from their local data center into Aurora. If their local data center were to go down, they have all their data in Aurora. However, periodically some statement breaks replication and I have to skip replication if it is safe to do so.

One of the semi frustrating things I have to deal with is the widespread use of the BLACKHOLE storage engine in Aurora by my client. When replicating from the local data center to Aurora, the client does not always want to replicate every single database into Aurora. There might be hundreds of databases on a single server and the client only want to replicate one of them into Aurora. What I do is export and import all the tables for all the database into Aurora. Then I change all the tables for the databases I don't want to replicate to BLACKHOLE. However, eventually people start creating new tables, modifying existing tables on the master server which is located in the local data center and those DDL changes are replicated to the database for which I have set all the tables to BLACKHOLE. Eventually this causes replication to break. Because I don't care about the data in the BLACKHOLE tables I can skip all the errors but sometimes I have to skip hundreds of errors.

How to break replication to Aurora instance slaves

I have a client which is migrating database to the AWS cloud. We are setting up Aurora slaves from the physical data to replicate all data. Today I was updating my permissions for my own user and ran a query like this on all the physical machines:

GRANT ALL PRIVILEGES ON *.* TO 'me'@'%' IDENTIFIED BY PASSWORD '*PASSWORDHASH' WITH GRANT OPTION;

That query ran fine on all the physical machine in the data center but it broke replication on every single Aurora instance that was replicating. This is because there are many permissions which are not allowed in Aurora because only the "SUPER" user can have them and that user is controlled by Amazon. When granting permissions or changing password hash, we have to be careful to only run grant statements which will not break replication on Aurora.

Here is an interesting list of other things you can learn from doing migrations to amazon rds:

https://www.percona.com/blog/2014/07/28/what-i-learned-while-migrating-a-customer-mysql-installation-to-amazon-rds/

if else / case statements in MySQL queries

Being able to change what is displayed in the query or to display the results of a different column based on criteria in a WHERE clause is super helpful. Because this is so useful and something I use so frequently I writing a blog post on it.

Here is am example query I wrote. I want to display the Aurora "EndPoint" alias when I provide an IP Address. I've created two tables, one which has RDS/Aurora Cluster details, and one with RDS/Aurora Instance details. I've pulled this information from the AWS API and stored it locally to easily query it in a relational database. If the IP Address turns out to be the writer end point then I want the c.EndPoint column value to be displayed. If the IP Address is for reader end point then I want the c.ReaderEndpoint to be displayed. If there is only one instance in the cluster then this query will always return c.EndPoint (writer end point).


    SELECT
    IF(i.IsClusterWriter = 1,  c.EndPoint,  c.ReaderEndpoint ) AS alias
    FROM RDSCluster c
    INNER JOIN RDSInstance i ON (i.DBClusterIdentifier = c.DBClusterIdentifier)
    WHERE i.IPAddress = 'xx.xx.xxx.xx';

With a case statement, I could also write it like this:

    SELECT
    (case when (i.IsClusterWriter = 1)
 THEN
      c.EndPoint
 ELSE
      c.ReaderEndpoint
 END)
 as alias
     FROM RDSCluster c
    INNER JOIN RDSInstance i ON (i.DBClusterIdentifier = c.DBClusterIdentifier)
    WHERE i.IPAddress = 'xx.xx.xxx.xx'

Other examples:

https://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query

Clearing the buffer cache before starting MySQL - Flush out the file system cache

Here was as good question I found on stackexchange:

How do you empty the buffers and cache on a Linux system?

http://unix.stackexchange.com/questions/87908/how-do-you-empty-the-buffers-and-cache-on-a-linux-system

If I don't flush out the file system cache when restarting MySQL on prod severs, I frequently get timeouts when restarting. The servers I typically work with have about 250GB of RAM, 40 CPU. Something like this will happen where I have to kill the start command or it just doesn't start.

# service mysql status
MySQL (Percona Server) running (182654)                    [  OK  ]
# service mysql restart
Shutting down MySQL (Percona Server).......................[  OK  ]......................................
Starting MySQL (Percona Server).......................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................^C

However, if I run this:

sync && echo 3 > /proc/sys/vm/drop_caches

MySQL starts up quick.

Starting MySQL (Percona Server)............................[  OK  ]....................