Announcement

Collapse
No announcement yet.

Synchronization only works correctly in one direction for a single database

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Synchronization only works correctly in one direction for a single database

    Hi,

    I'm often using Navicat for MySQL 12.1.22 Enterprise Edition (64 Bit) to synchronize structure and data between my local development server (XAMPP on a Windows 10 machine) and the staging or production servers.

    Now I'm working with a database hosted by the German web hosting provider ALL-INKL.COM for the first time; the connection is established via VPN instead of an SSH tunnel (see https://all-inkl.com/wichtig/anleitu...nbank_406.html - German only, sorry, but there are several screenshots). The strange thing is: When synchronizing structure or data from ALL-INKL.COM to my local XAMPP, everything works just fine. When I try to do it the other way round in order to deploy my local changes to the server, 16 of the 41 tables on the server are obviously not recognized as the counterparts of the local tables. When I try to synchronize the data, there are 16 empty rows in the list of target tables; when trying to synchronize the structure, these tables are marked for deletion and are also on the list of tables to add to the target server.

    I had a closer look at the databases and found out that the database on the server has a character set of latin1, while my local database uses utf8 (makes no difference when displaying the data since the character set of all tables is utf8). This has happened because at first I just got a dump of the database, only later I could connect to the database via Navicat.

    Also, all tables on the server but one are defined like this:

    Code:
    CREATE TABLE [...] (
      [...]
    ) [...] ROW_FORMAT = Dynamic;
    And on my local machine:

    Code:
    CREATE TABLE [...] (
      [...]
    ) [...] ROW_FORMAT = Compact;

    So I transferred the whole remote database to a new local database with the character set latin1, but to absolutely no avail. The ROW_FORMAT also remained the same.

    Okay, I'm using MariaDB (version 10.1.31) locally, while on the server MySQL (5.7.26) is in use, but that was never a problem, and I'm using Navicat for quite some years now with a whole bunch of databases from different web hosting providers. Has anybody encountered anything similar? When deploying my changes, I can of course check and apply the differences manually when using the synchronization direction that works, but that's a whole bunch of additional work and not what I purchased Navicat for...

    Thank you very much in advance for any hint!

    Cheers,
    Jan
    ALL-INKL.COM - Anleitungen, KAS, Datenbank, MySQL-Datenbank: Verbindung per VPN zur Datenbank

  • #2
    Ouch, I have been completely blind!

    Just now I see that the tables on the server that don't seem to match have uppercase letters in their names. On my Windows system, only lowercase letters are used; it's not possible to use uppercase letters. Obviously the target system determines if the names are compared in a case-sensitive way or not.

    I remembered the MySQL setting lower_case_table_names, but unfortunately this isn't an option here:

    You should not set lower_case_table_names to 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or macOS). It is an unsupported combination that could result in a hang condition when running an INSERT INTO ... SELECT ... FROM tbl_name operation with the wrong tbl_name letter case. With MyISAM, accessing table names using different letter cases could cause index corruption.

    As of MySQL 5.5.46, an error message is printed and the server exits if you attempt to start the server with --lower_case_table_names=0 on a case-insensitive file system.
    (see https://dev.mysql.com/doc/refman/5.5/en ... able_names). Well, I only have to apply a few changes here - the script I'm currently working on is very badly programmed and totally insecure, so I have been hired to create a new one from scratch; I just have to fix some bugs etc. so the employees of my customer can work with the script until the new one is ready. If needed, I'll create a Vagrant VM; using table names with uppercase letters should be no problem then.

    Cheers,
    Jan

    Comment

    Working...
    X