Strange problem in replication to MySQL
-
Dear all,
I am trying to set up replication to MySQL. This took me overcoming a few problems, but finally it seemed to wrok. I set up a linked server to MySQL through MyODBC 3.51, enabled the linked server as an OLEDB-subscriber and created the subscription. However, after the initial snapshot has been delivered to MySQL, the number of records at the destination is a factor less than on the source. For example, one table has generated a snapshot of 116 rows, while only 20 exist in MySQL. This comes to down to every fifth record being replicated, plus the first record. Other table show other factors, the larger the table, the larger the factor.
It becomes even stranger when I try to replicate a single table with a single column, just for testing. Only one record is being replicated!!
Does anybody have any clue what the **** is going on?Any help would be greatly appreciated, since I have been trying all possible options in the ODBC-link, the linked server and the distribution agent.
Thanx,
Bart Blesgraaf
-
I had a similar situation with another ODBC driver (to AS/400), but this may also apply.
Your connection links are using several libraries and translating and transmitting basic SQL commands (it all comes down to doing SELECTs INSERTs and DELETEs). Some of the links have buffer size and cache size restrictions. Somewhere along the line, you have a large query string (10k characters, for example) being truncated by small buffer or cache size (2k for example). You will likely need to modify the connection settings, either on the linked server ODBC parameters, or the MySQL server parameters to make sure the querys are not being truncated.
In my case, the AS400 DB2 drivers (older software version that could not be updated for other unrelated reasons) would not accept queries greater than 2k, while the Windows ODBC driver had a cache size of 8K. Changing the linked server ODBC connection string resolved the problem, but it took a lot of tracing at the driver level to discover this scenario.
Hope this helps.
Mark -
Hi Mark,
Many thanks for your reply. Can you tell me how to set the buffer and cache size for this link? I cannot find it in the BOL or on the internet.
Cheers,
Bart
-
It depends on the ODBC driver. If you right-click on the linked server, you can set the ODBC settings there, but the exact syntax will depend on the driver.
In addition, the ODBC driver will use a network library (not sure what MySQL will use) that has its own settings. Also, the "listener" on the MySQL server will have its own net-lib and driver settings.
In my case, the problem was the network connection on the remote server (AS400) was too small, but I could not make any changes on that machine. I had to make the ODBC driver cache size smaller in the connection string on the linked server.
Hope this helps
Mark -
Dear Mark,
Just to let you know, I succeeded in setting up replication towards MySQL on our test server. I created an ODBC DSN, set it up as an ODBC-subscriber and when I got an 'Invalid cursor state', I dropped the msrepl7 table in MySQL. After this the replication ran fine. I don't know what this table is used for, but it seems that it doesn't serve its purpose well
However, I still haven't been able to setup replication towards our client, so the struggle continues
Best regards,
Bart
-
Hello
i am dealing with exactly the same problem now, initial snapshots are replicated to mysql partially - did you find any concrete solution to get this work?
thanks,
susan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply