The Automatic Schema Synchronization mechanism, detailed in a two part series: part 1 and part 2, automatically detects and synchronizes mismatches in metadata between NDB Dictionary and MySQL Server’s Data Dictionary. Information about the mechanism is exposed through different MySQL Server status variables. These variables deal with the count of metadata objects in different states of detection and synchronization. Users had to delve into the error log for granular details about the objects. This has been improved in NDB Cluster 8.0.21 by the introduction of two Performance Schema tables: ndb_sync_pending_objects and ndb_sync_excluded_objects. These tables are present only if support for the NDB Storage Engine has been enabled for the MySQL Server.
The ndb_sync_pending_objects table displays information about NDB metadata objects that have been detected as mismatches by the Metadata Change Monitor component. The contents of the table represent the metadata objects awaiting synchronization by the NDB Event Handling component. The row corresponding to the object is removed when the Event Handler picks the object up for synchronization.
Should the synchronization of the object fail due to a temporary error, the object remains eligible for detection by the Metadata Change Monitor in its subsequent runs. The mismatch will then be detected again, as can be seen by monitoring the ndb_sync_pending_objects table, and its synchronization will be retried. If the synchronization of the object fails with a permanent error, the object will be excluded from mismatch detection by the Metadata Change Monitor. Information about these excluded objects can be found in the ndb_sync_excluded_objects table.
Using The New Performance Schema Tables
To demonstrate how the two new tables can be used to track the progress of automatic schema synchronization, we consider a scenario involving the native backup and restore of metadata objects in the NDB Dictionary.
Create NDB Metadata
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> CREATE SCHEMA db1; Query OK, 1 row affected (0.18 sec) mysql> USE db1; Database changed mysql> CREATE TABLE t1 ( -> a INT, -> b INT, -> PRIMARY KEY(a,b) -> ) ENGINE NDB; Query OK, 0 rows affected (0.34 sec) mysql> CREATE TABLE t2 ( -> a INT PRIMARY KEY, -> b VARCHAR(255) -> ) ENGINE NDB; Query OK, 0 rows affected (0.25 sec) |
Create A Native NDB Cluster Backup
A backup of NDB metadata and data is created from the ndb_mgm management client.
|
1
2
3
4
5
6
7
|
ndb_mgm> START BACKUP Waiting for completed, this may take several minutes Node 2: Backup 1 started from node 50 Node 2: Backup 1 started from node 50 completed StartGCP: 747 StopGCP: 750 #Records: 2061 #LogRecords: 0 Data: 52364 bytes Log: 0 bytes |
Drop Metadata
|
1
2
|
mysql> DROP SCHEMA db1; Query OK, 2 rows affected (0.39 sec) |
Restore The NDB Cluster Backup
The backup taken earlier is restored using the ndb_restore utility. For the sake of demonstration, the synchronization of tables t1 and t2 should fail in order for their information to be displayed in the ndb_sync_excluded_objects table. This is achieved by using the disable-indexes command-line option which disables the restoration of indexes from the backup. The anomaly in table definitions is detected by the NDB Event Handler and the tables aren’t synchronized successfully.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
~/cluster_install/8.0$ ./bin/ndb_restore --nodeid=2 --backupid=1 --restore-meta --disable-indexes --backup-path=/home/arnab/cluster_install/8.0/datadir/ndb_data/node2/BACKUP/BACKUP-1 Nodeid = 2 Backup Id = 1 backup path = /home/arnab/cluster_install/8.0/datadir/ndb_data/node2/BACKUP/BACKUP-1 2020-07-13 14:44:10 [restore_metadata] Read meta data file header Opening file '/home/arnab/cluster_install/8.0/datadir/ndb_data/node2/BACKUP/BACKUP-1/BACKUP-1.2.ctl' File size 29488 bytes Backup version in files: mysql-8.0.21 ndb-8.0.21 ndb version: mysql-8.0.21 ndb-8.0.21 2020-07-13 14:44:10 [restore_metadata] Load content Stop GCP of Backup: 750 Start GCP of Backup: 747 2020-07-13 14:44:10 [restore_metadata] Get number of Tables 2020-07-13 14:44:10 [restore_metadata] Validate Footer Connected to ndb!! 2020-07-13 14:44:11 [restore_metadata] Restore objects (tablespaces, ..) 2020-07-13 14:44:11 [restore_metadata] Restoring tables Successfully restored table `db1/def/t1` Successfully restored table `db1/def/t2` 2020-07-13 14:44:11 [restore_metadata] Save foreign key info 2020-07-13 14:44:11 [restore_data] Start restoring table data ~/cluster_install/8.0$ |
Querying The Performance Schema Tables
Schema db1 and tables t1 and t2 are synchronized to the MySQL Server by using the ndb_metadata_sync system variable. The variable is automatically set to OFF once all mismatched objects have been synchronized.
|
1
2
3
4
5
6
7
8
9
10
|
mysql> SET GLOBAL ndb_metadata_sync = ON; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'ndb_metadata_sync'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | ndb_metadata_sync | OFF | +-------------------+-------+ 1 row in set (0.02 sec) |
Querying the ndb_sync_pending_objects table at this point in time will result in an empty set returned since all objects have been synchronized.
|
1
2
|
mysql> SELECT * FROM performance_schema.ndb_sync_pending_objects; Empty set (0.00 sec) |
The mechanism is designed such that detection and synchronization is done by different components as explained earlier. This leads to mismatched objects being synchronized fairly quickly depending on a number of factors. As a result, rows in the ndb_sync_pending_objects table may be short-lived. The below example information has been captured by enabling a special debug flag that delays the synchronization of the detected objects.
|
1
2
3
4
5
6
7
8
9
|
mysql> SELECT * FROM performance_schema.ndb_sync_pending_objects; +-------------+------+--------+ | SCHEMA_NAME | NAME | TYPE | +-------------+------+--------+ | db1 | NULL | SCHEMA | | db1 | t2 | TABLE | | db1 | t1 | TABLE | +-------------+------+--------+ 3 rows in set (0.01 sec) |
The SCHEMA_NAME column represents the schema in which the object resides (NULL for tablespaces and logfile groups), the NAME column represents the name of the object (NULL for schemas), and the TYPE column denotes the type of the object which is one of LOGFILE GROUP, TABLESPACE, SCHEMA, or TABLE.
The next step is to check if the metadata objects have been synchronized successfully.
|
1
2
3
4
5
6
7
8
9
10
|
mysql> SHOW SCHEMAS LIKE 'db1'; +----------------+ | Database (db1) | +----------------+ | db1 | +----------------+ 1 row in set (0.01 sec) mysql> SHOW TABLES IN db1; Empty set (0.01 sec) |
The above tells us that schema db1 has been successfully synchronized but tables t1 and t2 are still missing from the MySQL Server’s perspective. The ndb_sync_excluded_objects table is our next stop.
|
1
2
3
4
5
6
7
8
|
mysql> SELECT * FROM performance_schema.ndb_sync_excluded_objects; +-------------+------+-------+------------------------------+ | SCHEMA_NAME | NAME | TYPE | REASON | +-------------+------+-------+------------------------------+ | db1 | t2 | TABLE | Mismatch in indexes detected | | db1 | t1 | TABLE | Mismatch in indexes detected | +-------------+------+-------+------------------------------+ 2 rows in set (0.01 sec) |
The first three columns are equivalent to their namesakes in the ndb_sync_pending_objects table. The additional REASON column sheds light on why the synchronization of the table was unsuccessful.
Another change in this area in NDB 8.0.21 is that the status variable Ndb_metadata_blacklist_size is now deprecated and will be replaced in a subsequent release. In any case, it is recommended that the ndb_sync_excluded_objects table is used in place of the status variable.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SHOW STATUS LIKE 'Ndb_metadata_blacklist_size'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | Ndb_metadata_blacklist_size | 2 | +-----------------------------+-------+ 1 row in set (0.02 sec) mysql> SELECT COUNT(*) FROM performance_schema.ndb_sync_excluded_objects; +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec) |
Deprecation (and eventual replacement) of the above status variable is a part of our broader effort to remove terms with negative origins.