compare table definitions

[mokraemer]

Just hit the case a computed colum did not match on both hosts. There must have been a sync issue, but it should be monitored as well

[mokraemer]

basic diff for the feature

diff -r 4773fbbe35d0 mysql-replication-check.php
--- a/mysql-replication-check.php   Fri Nov 17 13:30:19 2023 +0100
+++ b/mysql-replication-check.php   Fri Nov 17 14:31:32 2023 +0100
@@ -207,10 +207,11 @@

        if(!QUIET){
            echo str_repeat(' ', $maxTableNameLength + 1);
-           echo 'ML MB MC SW SL MU SC SU', PHP_EOL;
+           echo 'ML MB MC SW SL MU SC SU MT ST', PHP_EOL;
        }

     $check = QUIET ? '' : '.  ';
+       $fail = QUIET ? '' : '!  ';

     $totalErrors = [];

@@ -227,8 +228,12 @@
         echo str_pad($table, $maxTableNameLength + 1, ' ', STR_PAD_RIGHT);
            }

-        $master->query('LOCK TABLES ' . quoteIdentifier($table) . ' READ');
-        echo $check;
+               try{
+                   $master->query('LOCK TABLES ' . quoteIdentifier($table) . ' READ');
+                   echo $check;
+               }catch(PDOException $e){
+                   echo $fail;
+               }

         $masterLockStartTime = microtime(true);

@@ -238,23 +243,36 @@
         $binlogPosition = $status['Position'];
         echo $check;

-        $statement = $master->query('CHECKSUM TABLE ' . quoteIdentifier($table));
-        $checksum = $statement->fetch(PDO::FETCH_ASSOC);
-        $masterChecksum = $checksum['Checksum'];
-        echo $check;
+               try{
+                   $statement = $master->query('CHECKSUM TABLE ' . quoteIdentifier($table));
+                   $checksum = $statement->fetch(PDO::FETCH_ASSOC);
+                   $masterChecksum = $checksum['Checksum'];
+                   echo $check;
+               }catch(PDOException $e){
+                   $masterChecksum = 1;
+                   echo $fail;
+               }

         $statement = $slave->prepare('SELECT MASTER_POS_WAIT(?, ?)');
         $statement->execute([$binlogFile, $binlogPosition]);
         $statement->fetch();
         echo $check;

-        $slave->query('LOCK TABLES ' . quoteIdentifier($table) . ' READ');
-        echo $check;
+               try{
+                   $slave->query('LOCK TABLES ' . quoteIdentifier($table) . ' READ');
+                   echo $check;
+               }catch(PDOException $e){
+                   echo $fail;
+               }

         $slaveLockStartTime = microtime(true);

-        $master->query('UNLOCK TABLES');
-        echo $check;
+               try{
+                   $master->query('UNLOCK TABLES');
+                   echo $check;
+               }catch(PDOException $e){
+                   echo $fail;
+               }

         $masterLockEndTime = microtime(true);
         $masterLockTime = $masterLockEndTime - $masterLockStartTime;
@@ -264,13 +282,23 @@
             $longestMasterLockTime = $masterLockTime;
         }

-        $statement = $slave->query('CHECKSUM TABLE ' . quoteIdentifier($table));
-        $checksum = $statement->fetch(PDO::FETCH_ASSOC);
-        $slaveChecksum = $checksum['Checksum'];
-        echo $check;
+               try{
+                   $statement = $slave->query('CHECKSUM TABLE ' . quoteIdentifier($table));
+                   $checksum = $statement->fetch(PDO::FETCH_ASSOC);
+                   $slaveChecksum = $checksum['Checksum'];
+                   echo $check;
+               }catch(PDOException $e){
+                   $slaveChecksum = 2;
+                   echo $fail;
+               }

-        $slave->query('UNLOCK TABLES');
-        echo $check;
+               try{
+                   $slave->query('UNLOCK TABLES');
+                   echo $check;
+               }catch(PDOException $e){
+                   $slaveChecksum = 2;
+                   echo $fail;
+               }

         $slaveLockEndTime = microtime(true);
         $slaveLockTime = $slaveLockEndTime - $slaveLockStartTime;
@@ -280,14 +308,38 @@
             $longestSlaveLockTime = $slaveLockTime;
         }

-        if ($slaveChecksum === $masterChecksum) {
-            echo QUIET ? '' : 'OK';
-        } else {
-            echo QUIET ? '' : 'ERR';
-            $totalErrors[] = $table.' Master: '.$masterChecksum.' Slave: '.$slaveChecksum;
-        }
+               try{
+                   $statement = $master->query('SHOW CREATE TABLE ' . quoteIdentifier($table));
+                   $tb = $statement->fetch(PDO::FETCH_ASSOC);
+                   $mastertb = $tb['Create Table'];
+                   echo $check;
+               }catch(PDOException $e){
+                   $mastertb='m';
+                   echo $fail;
+               }
+               try{
+                   $statement = $slave->query('SHOW CREATE TABLE ' . quoteIdentifier($table));
+                   $tb = $statement->fetch(PDO::FETCH_ASSOC);
+                   $slavetb = $tb['Create Table'];
+                   echo $check;
+               }catch(PDOException $e){
+                   $slavetb = 's';
+                   echo $fail;
+               }

-        echo QUIET ? '' :PHP_EOL;
+    if($slaveChecksum === $masterChecksum && $mastertb === $slavetb){
+           echo QUIET ? '' : 'OK';
+       } else {
+           echo QUIET ? '' : 'ERR';
+           if($slaveChecksum !== $masterChecksum){
+               $totalErrors[] = $table . ' Checksum Master: ' . $masterChecksum . ' Slave: ' . $slaveChecksum;
+           }
+           if($mastertb !== $slavetb){
+               $totalErrors[] = $table . ' Tabledef differences: ' . levenshtein($mastertb, $slavetb);
+           }
+       }
+
+       echo QUIET ? '' :PHP_EOL;
     }

     $endTime = microtime(true);

[BenMorel]

Hi, are you talking about generated columns? How could tables with a sync failure on generated columns report the same checksum?

[mokraemer]

a virtual (not stored column) can

I was not able to reproduce this issue so far, but it did happen... And this check is simple, so adding it just gives extra safety at no cost. With this check I found I had divergent auto_increment values on some tables too. Don't know how this happend - maybe some admin command...

[BenMorel]

I like the idea of comparing table definitions (which will check the auto_increment as well). But how does this relate to virtual generated columns?

Does that mean that your sync errors came from different definitions of virtual columns on master & slave?

[mokraemer]

Does that mean that your sync errors came from different definitions of virtual columns on master & slave?

exactly. A columns was renamed which is part of the virtual column, Maria updates the definition of the virtual column as well. But somehow that change was not done at the slave - but at the moment I am unable to reproduce this.