☰

设置从 mssql 到 mysql 的复制时出错

tag-icon • tag-icon mysql sql-server mysql-replication database-replication
设置从 mssql 到 mysql 的复制时出错

我正在尝试设置从 SQL Server 2005 到 MySql 5.1.53 的复制。我可以毫无问题地添加发布。但是，当我添加订阅时，我收到此错误：

OLE DB 或 ODBC 订阅服务器无法订阅发布“mySqlReplicationPub”中的文章“table_1”，因为该文章具有时间戳列并且发布是“allow_queued_tran”（允许排队更新订阅）。

但是，我在 table_1 中没有时间戳列（见下文）并且我已将 allow_qued_tran 设置为 false。

这是 table_1 的 ddl

CREATE TABLE [dbo].[table_1](
    [objectId] [int] NOT NULL,
    [name] [varchar](50) NOT NULL,
    [aBit] [bit] NOT NULL,
    [nVar] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [objectId] ASC
)

这是我创建出版物和订阅者的代码

-- Enable Replication
exec sp_replicationdboption 
    @dbname = N'mySqlReplication',
    @optname = N'publish', 
    @value = N'true'
    GO

-- Adding the transactional publication
exec sp_addpublication 
    @publication = N'mySqlReplicationPub', 
    @description = N'Transactional publication of database ''mySqlReplication'' from Publisher ''hostname''.', 
    @sync_method = N'concurrent_c', 
    @retention = 0, 
    @allow_push = N'true', 
    @allow_pull = N'false', 
    @allow_anonymous = N'true', 
    @enabled_for_internet = N'false', 
    @snapshot_in_defaultfolder = N'true', 
    @compress_snapshot = N'false', 
    @ftp_port = 21, 
    --    @ftp_login = N'anonymous', 
    @allow_subscription_copy = N'false', 
    @add_to_active_directory = N'false', 
    @repl_freq = N'continuous', 
    @status = N'active', 
    @independent_agent = N'true', 
    @immediate_sync = N'true', 
    @allow_sync_tran = N'false', 
    @autogen_sync_procs = N'false', 
    @allow_queued_tran = N'false', 
    @allow_dts = N'false', 
    @replicate_ddl = 0,
    @allow_initialize_from_backup = N'false', 
    @enabled_for_p2p = N'false', 
    @enabled_for_het_sub = N'true'
GO


exec sp_addpublication_snapshot 
    @publication = N'mySqlReplicationPub', 
    @frequency_type = 1, 
    @frequency_interval = 0, 
    @frequency_relative_interval = 0, 
    @frequency_recurrence_factor = 0, 
    @frequency_subday = 0, 
    @frequency_subday_interval = 0, 
    @active_start_time_of_day = 0, 
    @active_end_time_of_day = 235959, 
    @active_start_date = 0, 
    @active_end_date = 0, 
    @job_login = null, 
    @job_password = null, 
    @publisher_security_mode = 1

--Add the table to be replicated
exec sp_addarticle 
    @publication = N'mySqlReplicationPub', 
    @article = N'table_1', 
    @source_owner = N'dbo', 
    @source_object = N'table_1', 
    @type = N'logbased', 
    @description = null, 
    @creation_script = null, 
    @pre_creation_cmd = N'drop', 
    @schema_option = 0x000000000803509F, 
    --    @identityrangemanagementoption = N'manual', 
    @destination_table = N'table_1', 
    @destination_owner = N'dbo', 
    @vertical_partition = N'false', 
    @ins_cmd = N'CALL sp_MSins_dbotable_1', 
    @del_cmd = N'CALL sp_MSdel_dbotable_1', 
    @upd_cmd = N'SCALL sp_MSupd_dbotable_1'
GO


--add all of the columns to the article
exec sp_articlecolumn
    @publication = N'mySqlReplicationPub'
    ,@article = N'table_1'
    ,@refresh_synctran_procs = 1
GO

--Add the subscriber, the error is coming when running sp_addsubscription
exec sp_addsubscription 
    @publication = N'mySqlReplicationPub', 
    @subscriber = N'mysql-pc', --change to subscriber hostname
    @destination_db = N'mysqlDb', --change to subscriber db name
    @subscription_type = N'Push',
    @sync_type = N'automatic', 
    @article = N'all', 
    @update_mode = N'read only', 
    @subscriber_type = 1

exec sp_addpushsubscription_agent 
    @publication = N'mySqlReplicationPub', 
    @subscriber = N'mysql-pc', --change to subscriber hostname
    @subscriber_db = N'mysqlDb', --change to subscriber db name
    @job_login = null, 
    @job_password = null, 
    @subscriber_security_mode = 0,
    @subscriber_login = N'mssql', --change
    @subscriber_password = '', --change
    @subscriber_provider = N'MSDASQL',
    @subscriber_datasrc = N'mySQLdsn', --change
    @frequency_type = 64, 
    @frequency_interval = 0, 
    @frequency_relative_interval = 0,
    @frequency_recurrence_factor = 0, 
    @frequency_subday = 0, 
    @frequency_subday_interval = 0, 
    @active_start_time_of_day = 0, 
    @active_end_time_of_day = 235959, 
    @active_start_date = 20110922, 
    @active_end_date = 99991231, 
    @enabled_for_syncmgr = N'False', 
    @dts_package_location = N'Distributor'
GO

任何帮助将不胜感激。

答案1

我相信这与 sp_addarticle 的 schema_option 有关。如果您查看它的可能值，0x08 处理时间戳。

以下是我现在正在使用的内容（感谢http://ratecontrol.blogspot.com/2010/12/one-way-transactional-replication-from.html）

--step 1
-- Adding the transactional publication
exec sp_replicationdboption 
    @dbname = N'mySqlReplication', 
    @optname = N'publish', 
    @value = N'true'
GO

exec sp_addpublication @publication = N'mySqlReplication'
    , @description = N'Transactional publication of database'
    , @sync_method = N'concurrent_c'
    , @retention = 0
    , @allow_push = N'true'
    , @allow_pull = N'false'
    , @allow_anonymous = N'true'
    , @enabled_for_internet = N'false'
    , @snapshot_in_defaultfolder = N'true'
    , @compress_snapshot = N'false'
    , @ftp_port = 21
    , @allow_subscription_copy = N'false'
    , @add_to_active_directory = N'false'
    , @repl_freq = N'continuous'
    , @status = N'active'
    , @independent_agent = N'true'
    , @immediate_sync = N'true'
    , @allow_sync_tran = N'false'
    , @allow_queued_tran = N'false'
    , @allow_dts = N'false'
    , @replicate_ddl = 0
    , @allow_initialize_from_backup = N'false'
    , @enabled_for_p2p = N'false'
    , @enabled_for_het_sub = N'true'
    , @autogen_sync_procs = 'false'
GO
--add the article to the publication
exec sp_addarticle @publication = N'mySqlReplication'
    , @article = N'table_1'
    , @source_owner = N'dbo'
    , @source_object = N'table_1'
    , @type = N'logbased'
    , @pre_creation_cmd = N'none'
    , @ins_cmd = N'SQL'
    , @del_cmd = N'SQL'
    , @upd_cmd = N'SQL'
    , @schema_option = 0x20025081
    , @status = 24
GO

--add all of the columns to the article
exec sp_articlecolumn @publication = N'mySqlReplication'
    , @article = N'table_1'
    , @refresh_synctran_procs = 1
GO

--end step1

--step2
--add the publication snaphot
exec sp_addpublication_snapshot 
    @publication = N'mySqlReplication',
    @frequency_type = 1, 
    @frequency_interval = 0, 
    @frequency_relative_interval = 0, 
    @frequency_recurrence_factor = 0, 
    @frequency_subday = 0, 
    @frequency_subday_interval = 0, 
    @active_start_time_of_day = 0, 
    @active_end_time_of_day = 235959, 
    @active_start_date = 0, 
    @active_end_date = 0, 
    @job_login = null, 
    @job_password = null, 
    @publisher_security_mode = 1
GO
--end step2

--step3
--add the subscriber(s)
use [bjMySqlReplication]
exec sp_addsubscription @publication = N'mySqlReplication'
    , @subscriber = N'mySQLdsn'  --system DSN
    , @destination_db = N'mySQLdb'
    , @subscription_type = N'Push'
    , @sync_type = N'automatic'
    , @article = N'all'
    , @update_mode = N'read only'
    , @subscriber_type = 3
GO

--add the pushing subscription agent
exec sp_addpushsubscription_agent @publication = N'mySqlReplication'
    , @subscriber = N'mySQLdsn'  --system DSN
    , @subscriber_db = N'mySQLdb'
    , @job_login = null
    , @job_password = null
    , @subscriber_security_mode = 0
    , @subscriber_login = N'mssql'
    , @subscriber_password = ''
    , @subscriber_provider = N'MSDASQL'
    , @subscriber_datasrc = N'mySQLdsn'  --system DSN
    , @frequency_type = 64
    , @frequency_interval = 1
    , @frequency_relative_interval = 0
    , @frequency_recurrence_factor = 0
    , @frequency_subday = 0
    , @frequency_subday_interval = 0
    , @active_start_time_of_day = 0
    , @active_end_time_of_day = 235959
    , @active_start_date = 20101202
    , @active_end_date = 99991231
    , @enabled_for_syncmgr = N'False'
    , @dts_package_location = N'Distributor'
GO
--end step3

相关内容