Synchronizing views between MySQL databases

How to compare and synchronize MySQL views? Recently we discovered some tricks in schema synchronization process of MySQL databases. It’s about how MySQL creates views and tables with foreign keys. I’m going to explain them and give workarounds for dbForge Studio for MySQL users.

Let’s take a small database named crm_demo for an example.

CREATE DATABASE crm_demo;
USE crm_demo;

CREATE TABLE productcategories (
  CategoryID INT (11) NOT NULL DEFAULT 0,
  CategoryName VARCHAR (20) DEFAULT NULL,
  ParentCategory INT (11) DEFAULT NULL,
  PRIMARY KEY (CategoryID)
)
ENGINE = INNODB;

CREATE TABLE products (
  ProductID INT (11) NOT NULL DEFAULT 0,
  ProductName VARCHAR (50) NOT NULL,
  CategoryID INT (11) DEFAULT NULL,
  UnitName VARCHAR (20) DEFAULT NULL,
  UnitScale INT (5) DEFAULT NULL,
  InStock INT (11) DEFAULT NULL,
  Price DECIMAL (10, 0) DEFAULT NULL,
  DiscontinuedPrice DECIMAL (10, 0) DEFAULT NULL,
  PRIMARY KEY (ProductID),
  INDEX Product_Category USING BTREE (CategoryID),
  CONSTRAINT Product_Category FOREIGN KEY (CategoryID)
  REFERENCES productcategories (CategoryID)
)
ENGINE = INNODB;

Suppose you deployed this database on two remote MySQL servers under different names: crm_demo and crm_demo_mirror.

First trick: creating view

After some time your decided to expand your crm_demo database by adding a view to it.

You create simple view on your local server by executing following statement:

CREATE VIEW products_view AS SELECT * FROM products;

MySQL expand your query and modifies it’s text to avoid ambiguous references (I guess). So here is the first trick: you need to ensure that crm_demo is the current database when creating view, otherwise MySQL will take current database and convert your view into something you don’t want. For example

CREATE OR REPLACE
ALGORITHM = UNDEFINED
DEFINER = 'root'@'%'
VIEW crm_demo.products_view
AS
/* ALGORITHM=UNDEFINED */ SELECT
  `test`.`products`.`ProductID` AS `ProductID`,
  `test`.`products`.`ProductName` AS `ProductName`,
  `test`.`products`.`CategoryID` AS `CategoryID`,
  `test`.`products`.`UnitName` AS `UnitName`,
  `test`.`products`.`UnitScale` AS `UnitScale`,
  `test`.`products`.`InStock` AS `InStock`,
  `test`.`products`.`Price` AS `Price`,
  `test`.`products`.`DiscontinuedPrice` AS `DiscontinuedPrice`
FROM
  `test`.`products`;

But you don’t want test database you want crm_demo ! To avoid this you could have written view text as following

CREATE VIEW products_view AS SELECT * FROM crm_demo.products;

But if you have created database project in dbForge Studio you don’t need to worry about it because application puts USE statement at the beginning of the deployment script. Thus you can deploy project under crm_demo and crm_demo_mirror database names without having to modify source code.

Important note:
You should consider the same rule about current database when creating table with foreign key.

Second trick: synchronizing two live databases using dbForge Studio for MySQL

Now suppose you need to sync MySQL databases – crm_demo with crm_demo_mirror . The fastest way to do this is to run schema comparison tool and examine database differences visually.

After doing this you see that crm_demo_mirror does not contain products_view. Oh… that’s easy! Wait a minute, here is the second trick: you press synchronize button and get … invalid view in crm_demo_mirror database. That’s because view body contains references to crm_demo database.

Workaround
To synchronize databases correctly you need to do following:

1. Export synchronization script to SQL editor
2. Press Ctrl+H and replace all occurrences of `crm_demo` with `crm_demo_mirror`
3. Execute script against crm_demo_mirror database

Comparison view and synchronizing it are available in dbForge Data Compare for MySQL or dbForge Studio for MySQL.