Sync SQL Binary column to MySQL table-CodePudding

I’m attempting to use a piece of software (Layer2 Cloud Connector) to sync a local SQL table (Sage software) to a remote MySQL database where the data is used reports generated via the company's web app. We are doing this with about 12 tables, and have been doing so for almost two years without any issues.

Background:

I’m using a simple piece of software the uses a SELECT statement to sync records from one table to another using ODBC. In this case from SQL (SQLTable) to MySQL (MySQLTable). To do so, the software requires a SELECT statement for each table, a PK field, and, being ODBC-based, a provider. For SQL I'm using the Actian Zen 4.5, and for MySQL I'm using the MySQL ODBC 5.3.

Here is a screenshot of what the setup screen looks like for each of the tables. I have omitted the other column names that I'm syncing to make the SELECT statement more readable. The other columns are primarily varchar or int types.

Problem

For unrelated reasons, we must now sync a new table. Like most of the other tables, it has a primary key column named rGUID of type binary. When initially setting up the other tables, I tried to sync the primary key as a binary type to a MySQL binary column, but it failed when attempting to verify the SELECT statement on the SQLServer side with the error “Cannot remove this column, because it is a part of the constraint Constraint1 on the table SQLTable”.

Example of what I see for the the GUID/rGUID primary key values stored in the SQLTable via Access, or in MySQL after syncing as string:

¡狻➽䪏蚯㰛蓪
Ҝ諺䖷ᦶ肸邅
ब惈蠷䯧몰吲론�
ॺ䀙㚪䄔麽骧⸍薉

To get around this, I use CAST in the SQLTable SELECT statement to CAST the binary value as a string using: CAST(GUID as nchar(8)) as GUID, and then set up the MySQL column as a VARCHAR(32) using utf8_general_ci collation.

This has worked great for every other table since we originally set this up. But this additional table has considerably more records (about 120,000 versus 5,000-10,000), and though I’m able to sync 10,000 – 15,000 successfully, when I try to sync the entire table I get about 10-12 errors such as:

The metabase record 'd36d2dbe-fa89-4712-be4c-6b212367004b' is marked to be added. The table 'SQLTable' does not contain a corresponding row. Changes made to this metabase record will be reset to the initial state.

I don't understand what is causing the above error or how to work past it.

What I’ve tried so far:

I’ve confirmed the SQLTable has no other unique fields that could be used as PK in place of the rGUID column
I’ve tried use different type, length and collation settings on the MySQL table, and have had mixed success, but ultimately still get errors when attempting to sync the entire table.
I’ve also tried tweaking the CAST settings for the SQL SELECT statement, but nchar(8) seems to work best for the other tables
I've tried syncing using HASHBYTES('SHA1', GUID) as GUID and syncing the value of that, but get the below ODBC error

I was thinking perhaps I could convert the SQL GUID to its value, then sync that as a varchar (or a binary), but my attempts at using CONVERT in the SQLTable SELECT statement have failed

Settings I used for all the other tables:
SQL SELECT Statement: SELECT CAST(GUID as nchar(8)) as GUID, OtherColumns FROM SQLTable;
MYSQL SELECT Statement: SELECT GUID, OtherColumns FROM MySQLTable;
Primary Key Field: GUID
Primary Key Field Type: String
MySQL Column Type/Collation: VARCHAR(32), utf8_general_ci

Any help or suggestions at all would be great. I've been troubleshooting this in my spare time for a couple of weeks now, and have no had much success. I'm not particularly familiar with the binary type, and am hoping someone might have an idea on how I might be able to successfully sync this SQL table to MySQL without these errors.

CodePudding user response：

Given the small size of the datasets involved I would select as CHAR(36) from SQL Server and store in a CHAR(36) in MySQL.

If you are able to control the way the data is inserted by Layer2 Cloud Connector then you could set your MySQLTable GUID column as BINARY(16) -

SELECT CAST(GUID AS CHAR(36)) AS GUID, OtherColumns FROM SQLTable;
INSERT INTO MySQLTable (GUID) VALUES (UUID_TO_BIN(GUID)))
SELECT BIN_TO_UUID(GUID) AS GUID, OtherColumns FROM MySQLTable;