OpsFire: Not All Databases

by Quintessence

Over the weekend we consolidated some of our databases onto a single RDS instance, as a dual effort to reduce costs as well as to allow for cross database joins.

Our primary RDS instance has a read only replica, so imagine my surprise when the next morning I see this:

primadmin@mysql_prod > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| prod_db_1          |
| prod_db_2          |
| innodb             |
| mysql              |
| performance_schema |
| sys                |
| tmp                |
+--------------------+
8 rows in set (0.01 sec)

---

readadmin@mysql_prod-ro ]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| prod_db_1          |
+--------------------+
2 rows in set (0.00 sec)

Looks like something, somewhere is missing from how we added prod_db_2 to mysql_prod last night. I took a look at the read only replica properties to verify its parameter group and noticed this:

Hrm 🤔

Since it's the middle of the day, I needed to schedule a reboot rather than just rebooting the instance. So we're going to cycle back around to this later.

While I await my reboot window, I decide to rule out two other possibilities and see if there are any "suprises" for how AWS implements MySQL v5.7.19. Specifically I want to know if the read only replica will pick up a second database in both of the following scenarios, as opposed to only grabbing the primary database:

  1. Create the read only replica before creating a second database
  2. Create the read only replica after creating a second database

As a quick note about the instance creation process itself: I noticed I couldn't create a new instance with the same version of MySQL that our production instance is running:


Note the lack of instance types available.


And here are all the instance types.

So to even start my test, I needed to create an instance with 5.7.16, then upgrade to 5.7.19. Lovely.

Skipping past that part of today's mini-🔥, after spinning up the test instance I went through the above test cases. For brevity, the steps for test case #2 are shown below:

primary mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodb             |
| mysql              |
| performance_schema |
| sys                |
| testingonetwothree |
+--------------------+
6 rows in set (0.00 sec)

primary mysql> create database iambluedabudee;
Query OK, 1 row affected (0.01 sec)

primary mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| iambluedabudee     |
| innodb             |
| mysql              |
| performance_schema |
| sys                |
| testingonetwothree |
+--------------------+
7 rows in set (0.00 sec)

Moment of truth: did the read only replica pick up the second database?

replica mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| iambluedabudee     |
| innodb             |
| mysql              |
| performance_schema |
| sys                |
| testingonetwothree |
+--------------------+
7 rows in set (0.00 sec)

Brilliant 😄 It turns out that in both cases (#1 not being documented above) that the replica picked up the second database.

Upon the arrival of the reboot window I was able to reboot the production replica without an issue and found that both databases were now in the replica:

readadmin@mysql_prod-ro ]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| prod_db_1          |
| prod_db_2          |
+--------------------+
3 rows in set (0.00 sec)

Something else that occurred to me to check, that I neglected to run before the reboot, is checking the version in console like so:

readadmin@mysql_prod-ro ]> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.19                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.19-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)

It's entirely probable, nay likely, that the version being reported in the AWS console (below) was incorrect:


Pinocchio versioning.

The actual version was probably a prior minor release, the upgrade being applied only on reboot.

Documented on my frequently used assets page.

Source for header: Cloud database image from Iconfinder, firey background from burnt embers created by Shutterstock user Bernatskaya Oxana.