How to find a database schema mismatch between servers

When VICIdial starts throwing 'Unknown column' errors or replication between database servers breaks down after an upgrade, the most direct way to find the cause is the DB Schema Compare Utility: run it, select the server pair, and the three result sections tell you exactly which table or column is missing and on which side.

Why schema drift happens

VICIdial upgrades run SQL migration scripts that add tables and columns to the database. In a multi-server setup - primary plus a secondary or cold-storage server - those scripts need to run on every server. If a migration script completes on the primary but the secondary was unavailable or the replication thread was paused at the time, the secondary ends up with an older schema. The next time VICIdial tries to write a field that exists on the primary but not on the secondary, you get the 'Unknown column' error.

The same issue shows up after a manual ALTER TABLE run directly on the primary, or after importing a dump that was created at a different version than the target server.

Running the compare

Open the DB Schema Compare Utility from Admin Utilities at the bottom of the Reports page. Select the server you want to check against the primary - typically the secondary database server if you suspect a replication problem, or the cold-storage server after a recent archive migration. Submit the form. Results appear in three sections:

• TABLES LIST shows every table name. A table present on the primary but missing on the secondary is the most severe mismatch - any operation that touches that table on the secondary fails entirely.
• TABLE ROW COUNTS shows the record count for each shared table. A large gap in a table like vicidial_log or vicidial_closer_log means rows that exist on the primary have not yet replicated, or replication stopped at a point in the past.
• TABLE FIELD COUNTS shows the column count per table. A column count that is lower on the secondary than on the primary means at least one ALTER TABLE ADD COLUMN ran on the primary only - this is the direct cause of 'Unknown column' errors.

flowchart TD
  A["Unknown column error or replication break"] --> B["Open DB Schema Compare Utility"]
  B --> C["Select secondary or cold-storage server"]
  C --> D["Check TABLES LIST"]
  D --> E{"Table missing on secondary?"}
  E -->|Yes| F["Run CREATE TABLE on secondary"]
  E -->|No| G["Check TABLE FIELD COUNTS"]
  G --> H{"Column count lower on secondary?"}
  H -->|Yes| I["Run ALTER TABLE ADD COLUMN on secondary"]
  H -->|No| J["Check TABLE ROW COUNTS"]
  J --> K{"Large row gap?"}
  K -->|Yes| L["Diagnose and repair replication thread"]

Acting on the findings

Once the field count section shows a mismatch on a specific table, pull the schema from the primary using SHOW CREATE TABLE and run the missing ALTER TABLE statement directly on the secondary. After applying the fix, run the compare again to confirm the column counts now match. For a missing table, do the same with SHOW CREATE TABLE on the primary and run the CREATE TABLE on the secondary, then let replication fill in the rows.

A large row-count gap is a different category of problem. It means replication is running behind or stopped. Check the replication thread status with SHOW SLAVE STATUS on the secondary. A non-zero Seconds_Behind_Master or an IO or SQL thread that is not running points to the repair you need - fixing the thread lag is outside the compare utility itself, but the utility tells you the gap exists and which tables are affected.

After any fix, run the compare utility a second time before declaring the issue resolved. Schema changes applied under replication can sometimes replay on the secondary automatically if the thread was only temporarily paused - but a manual fix applied directly while replication is active can create a conflict. The second compare run catches either outcome and gives you a clean all-clear.

Schema health affects every feature that touches a Lead, a Campaign, or a Call recording entry. Keeping both servers aligned is part of the wider discipline of monitoring VICIdial server health and capacity. For the server-level performance context that helps you determine whether replication lag is tied to a resource bottleneck, see VICIdial system load explained.

Want a managed VICIdial environment where upgrade scripts are applied consistently and schema state is tracked for you? Spin up a managed VICIdial box on VICIfast - live in under 40 seconds, with a clean, consistent schema from the first boot.