mnagy
April 27, 2020, 3:16pm
1
Hello,
I’ve set up REPLICATION of a few database in my DEV cluster to my DR DEV cluster. When I look at the database status on the DR MemSQL Studio page, I’m seeing ONLINE - IMPACTED
Is this expected behavior from studio?
Within MemSQL it looks healthy:
david
April 28, 2020, 3:13pm
2
Studio is expecting this partition be synchronously replicated. Studio reads this from the following query:
SELECT
DATABASE_NAME AS databaseName,
NUM_PARTITIONS AS numPartitions,
REMOTE_NAME != "" AS drReplica,
IS_SYNC AS syncReplicated
FROM
INFORMATION_SCHEMA.DISTRIBUTED_DATABASES
Because IS_SYNC is true for this partition, Studio expects it to be synchronously replicated.
Studio checks whether a partition is being synchronously replicated or not via this query:
SELECT
HOST AS host,
PORT AS port,
DATABASE_NAME AS databaseName,
ORDINAL AS ordinal,
ROLE AS role,
IS_OFFLINE AS isOffline,
STATE AS syncState
FROM
INFORMATION_SCHEMA.DISTRIBUTED_PARTITIONS
If STATE is not "sync", then Studio will consider this partition be incorrectly replicated (it should be “sync”).
To help us debug this issue, can you send us the output of the 2 queries above?
mnagy
April 28, 2020, 3:32pm
3
David, I’ve attached both for the primary and dr site.
From the Primary Site
> memsql> SELECT
> -> DATABASE_NAME AS databaseName,
> -> NUM_PARTITIONS AS numPartitions,
> -> REMOTE_NAME != "" AS drReplica,
> -> IS_SYNC AS syncReplicated
> -> FROM
> -> INFORMATION_SCHEMA.DISTRIBUTED_DATABASES;
> +--------------+---------------+-----------+----------------+
> | databaseName | numPartitions | drReplica | syncReplicated |
> +--------------+---------------+-----------+----------------+
> | rh_test | 8 | 0 | 1 |
> | adt_store | 8 | 0 | 1 |
> | hl7 | 8 | 0 | 1 |
> | crca_dm | 8 | 0 | 1 |
> +--------------+---------------+-----------+----------------+
> 4 rows in set (0.01 sec)
>
> memsql> SELECT
> -> HOST AS host,
> -> PORT AS port,
> -> DATABASE_NAME AS databaseName,
> -> ORDINAL AS ordinal,
> -> ROLE AS role,
> -> IS_OFFLINE AS isOffline,
> -> STATE AS syncState
> -> FROM
> -> INFORMATION_SCHEMA.DISTRIBUTED_PARTITIONS;
> +----------------------------+------+--------------+---------+--------+-----------+-----------+
> | host | port | databaseName | ordinal | role | isOffline | syncState |
> +----------------------------+------+--------------+---------+--------+-----------+-----------+
> | CH-D-MEM-SQL1 | 3306 | rh_test | 0 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | rh_test | 1 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | rh_test | 2 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | rh_test | 3 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | rh_test | 4 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | rh_test | 5 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | rh_test | 6 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | rh_test | 7 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | rh_test | 0 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | rh_test | 1 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | rh_test | 2 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | rh_test | 3 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | rh_test | 4 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | rh_test | 5 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | rh_test | 6 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | rh_test | 7 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | adt_store | 0 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | adt_store | 1 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | adt_store | 2 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | adt_store | 3 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | adt_store | 4 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | adt_store | 5 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | adt_store | 6 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | adt_store | 7 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | adt_store | 0 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | adt_store | 1 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | adt_store | 2 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | adt_store | 3 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | adt_store | 4 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | adt_store | 5 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | adt_store | 6 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | adt_store | 7 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | hl7 | 0 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | hl7 | 1 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | hl7 | 2 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | hl7 | 3 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | hl7 | 4 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | hl7 | 5 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | hl7 | 6 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | hl7 | 7 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | hl7 | 0 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | hl7 | 1 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | hl7 | 2 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | hl7 | 3 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | hl7 | 4 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | hl7 | 5 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | hl7 | 6 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | hl7 | 7 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | crca_dm | 0 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | crca_dm | 1 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | crca_dm | 2 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | crca_dm | 3 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | crca_dm | 4 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | crca_dm | 5 | Master | 0 | |
> | CH-D-MEM-SQL1 | 3306 | crca_dm | 6 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | crca_dm | 7 | Master | 0 | |
> | CH-D-MEM-SQL2 | 3306 | crca_dm | 0 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | crca_dm | 1 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | crca_dm | 2 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | crca_dm | 3 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | crca_dm | 4 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | crca_dm | 5 | Slave | 0 | sync |
> | CH-D-MEM-SQL2 | 3306 | crca_dm | 6 | Slave | 0 | sync |
> | CH-D-MEM-SQL1 | 3306 | crca_dm | 7 | Slave | 0 | sync |
> +----------------------------+------+--------------+---------+--------+-----------+-----------+
> 64 rows in set (0.00 sec)
>
>
> From the DR Site
>
> memsql> SELECT
> -> DATABASE_NAME AS databaseName,
> -> NUM_PARTITIONS AS numPartitions,
> -> REMOTE_NAME != "" AS drReplica,
> -> IS_SYNC AS syncReplicated
> -> FROM
> -> INFORMATION_SCHEMA.DISTRIBUTED_DATABASES;
> +------------------------------+---------------+-----------+----------------+
> | databaseName | numPartitions | drReplica | syncReplicated |
> +------------------------------+---------------+-----------+----------------+
> | cluster_13878949897310538896 | 0 | 1 | 0 |
> | adt_store | 8 | 1 | 1 |
> | rh_test | 8 | 1 | 1 |
> | hl7 | 8 | 1 | 1 |
> +------------------------------+---------------+-----------+----------------+
> 4 rows in set (0.02 sec)
>
> memsql> SELECT
> -> HOST AS host,
> -> PORT AS port,
> -> DATABASE_NAME AS databaseName,
> -> ORDINAL AS ordinal,
> -> ROLE AS role,
> -> IS_OFFLINE AS isOffline,
> -> STATE AS syncState
> -> FROM
> -> INFORMATION_SCHEMA.DISTRIBUTED_PARTITIONS;
> +----------------------------+------+--------------+---------+--------+-----------+-----------+
> | host | port | databaseName | ordinal | role | isOffline | syncState |
> +----------------------------+------+--------------+---------+--------+-----------+-----------+
> | MI-D-MEM-SQL1 | 3306 | adt_store | 0 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | adt_store | 1 | Master | 0 | |
> | MI-D-MEM-SQL1 | 3306 | adt_store | 2 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | adt_store | 3 | Master | 0 | |
> | MI-D-MEM-SQL1 | 3306 | adt_store | 4 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | adt_store | 5 | Master | 0 | |
> | MI-D-MEM-SQL1 | 3306 | adt_store | 6 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | adt_store | 7 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | adt_store | 0 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | adt_store | 1 | Slave | 0 | async |
> | MI-D-MEM-SQL2 | 3306 | adt_store | 2 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | adt_store | 3 | Slave | 0 | async |
> | MI-D-MEM-SQL2 | 3306 | adt_store | 4 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | adt_store | 5 | Slave | 0 | async |
> | MI-D-MEM-SQL2 | 3306 | adt_store | 6 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | adt_store | 7 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | rh_test | 0 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | rh_test | 1 | Master | 0 | |
> | MI-D-MEM-SQL1 | 3306 | rh_test | 2 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | rh_test | 3 | Master | 0 | |
> | MI-D-MEM-SQL1 | 3306 | rh_test | 4 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | rh_test | 5 | Master | 0 | |
> | MI-D-MEM-SQL1 | 3306 | rh_test | 6 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | rh_test | 7 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | rh_test | 0 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | rh_test | 1 | Slave | 0 | async |
> | MI-D-MEM-SQL2 | 3306 | rh_test | 2 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | rh_test | 3 | Slave | 0 | async |
> | MI-D-MEM-SQL2 | 3306 | rh_test | 4 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | rh_test | 5 | Slave | 0 | async |
> | MI-D-MEM-SQL2 | 3306 | rh_test | 6 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | rh_test | 7 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | hl7 | 0 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | hl7 | 1 | Master | 0 | |
> | MI-D-MEM-SQL1 | 3306 | hl7 | 2 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | hl7 | 3 | Master | 0 | |
> | MI-D-MEM-SQL1 | 3306 | hl7 | 4 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | hl7 | 5 | Master | 0 | |
> | MI-D-MEM-SQL1 | 3306 | hl7 | 6 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | hl7 | 7 | Master | 0 | |
> | MI-D-MEM-SQL2 | 3306 | hl7 | 0 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | hl7 | 1 | Slave | 0 | async |
> | MI-D-MEM-SQL2 | 3306 | hl7 | 2 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | hl7 | 3 | Slave | 0 | async |
> | MI-D-MEM-SQL2 | 3306 | hl7 | 4 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | hl7 | 5 | Slave | 0 | async |
> | MI-D-MEM-SQL2 | 3306 | hl7 | 6 | Slave | 0 | async |
> | MI-D-MEM-SQL1 | 3306 | hl7 | 7 | Slave | 0 | async |
> +----------------------------+------+--------------+---------+--------+-----------+-----------+
> 48 rows in set (0.02 sec)
david
April 29, 2020, 5:42pm
4
Thanks for the query outputs! That led us to the core issue here: Studio has a bug where it thinks DR replicas can be synchronously replicated but they can’t. We’ll fix this, thanks again.
1 Like
We just published a new Studio Version, 1.9.6, which includes a fix for this situation.
https://docs.memsql.com/v7.0/release-notes/memsql-studio-release-notes/#2020-05-06-version-1-9-6
The relevant entry is: “Stops warning users about an incorrect replication type on slave partitions for databases marked as replicas”
Thanks once more for reporting this issue!
