failed to load data from SQL server to MYSQL

[suvbin]

Hi, 

while replicating data from SQLServer to MYSQL, am facing the below issue.

Failure in starting (with TXN consistency...) Failure validating initial LSN 'anywhere existance' mssql_lsn_anywhere_validator(...)Failed in looking at Active TLOG lsn_exists(...)Failed in statement execution for looking at Active TLOG RetCode: SQL_ERROR SqlState: HY000 NativeError: 9003 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The log scan number (60:376:5) passed to log scan in database 'sample1' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. Line: 1 Column: -1 Failed (retcode -1) to execute statement: 'select top 1 [Current LSN] from fn_dblog('0x0000003c:00000178:0005','0x0000003c:00000178:0005')'

[sureshkumar]

Hello Team,

The Error "Failure validating initial LSN, anywhere existance" could be that your LSN is already archived.

Could you please check with your DBA to restore the LSN, if not able to restore, you will have to reload the task

Some of the useful links:

Failure validating initial LSN 'xx:xx:xx' 'anywher... - Qlik Community - 1918783

What to do when we see the error 'Native code #900... - Qlik Community - 1924240

Regards,
Suresh

[suvbin]

This replication am doing in my desktop. As suggested i have reloaded the task. Source and target are in my local.

now its showing new error.

"No FULL database backup found (under the 'FULL' recovery model). To enable all changes to be captured, you must perform a full database backup."

i have changed the recovery model from "simple" to "Full". But still its showing error.

Kindly suggest.

[john_wang]

Hello @suvbin ,

The model change from "simple" to "Full" will not take effect until you do a Full database backup operation, like below:

Hope this helps.

Regards,

John.

[suvbin]

It's already configured to "full" only. Please check the below screenshot.

In the logs i see both errors again.

"No FULL database backup found (under the 'FULL' recovery model). To enable all changes to be captured, you must perform a full database backup."

"Failure in starting (with TXN consistency...) Failure validating initial LSN 'anywhere existance' mssql_lsn_anywhere_validator(...)Failed in looking at Active TLOG lsn_exists(...)Failed in statement execution for looking at Active TLOG RetCode: SQL_ERROR SqlState: HY000 NativeError: 9003 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The log scan number (60:376:5) passed to log scan in database 'sample1' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. Line: 1 Column: -1 Failed (retcode -1) to execute statement: 'select top 1 [Current LSN] from fn_dblog('0x0000003c:00000178:0005','0x0000003c:00000178:0005')'"

Not sure why.  

[john_wang]

Hello @suvbin ,

Yes, I see. However this is a change in SSMS GUI only, it does not take effect in the backend. Please perform a Full Backup (as below sample) then Replicate task will not report above error. It's SQL Server Backup operations.

let me know if works.

Regards,

John.

[suvbin]

Hi John,

This i have done. 

[Bill_Steinagle]

@suvbin 

Hi, as per the latest with the post can you check make sure the Publication is recreated as per the initial post? Also to ensure the health of the backup can you run the below on this sample1 DB as could be an issue with the backup. In the SQL Server environment run the below:

dbcc checkdb

expected results:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'SQLSERVER17'.

Regards,

Bill

[suvbin]

the below displayed when ran the "dbcc checkdb"

DBCC results for 'sample1'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 1453 rows in 17 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 190 rows in 3 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysclones'.
There are 0 rows in 0 pages for object "sys.sysclones".
DBCC results for 'sys.sysallocunits'.
There are 229 rows in 3 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.sysseobjvalues'.
There are 0 rows in 0 pages for object "sys.sysseobjvalues".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysdbfrag'.
There are 0 rows in 0 pages for object "sys.sysdbfrag".
DBCC results for 'sys.sysfgfrag'.
There are 0 rows in 0 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.syspru'.
There are 0 rows in 0 pages for object "sys.syspru".
DBCC results for 'sys.sysbrickfiles'.
There are 0 rows in 0 pages for object "sys.sysbrickfiles".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysdbreg'.
There are 0 rows in 0 pages for object "sys.sysdbreg".
DBCC results for 'sys.sysprivs'.
There are 190 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 2608 rows in 37 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscsrowgroups'.
There are 0 rows in 0 pages for object "sys.syscsrowgroups".
DBCC results for 'sys.sysextsources'.
There are 0 rows in 0 pages for object "sys.sysextsources".
DBCC results for 'sys.sysexttables'.
There are 0 rows in 0 pages for object "sys.sysexttables".
DBCC results for 'sys.sysextfileformats'.
There are 0 rows in 0 pages for object "sys.sysextfileformats".
DBCC results for 'sys.sysmultiobjvalues'.
There are 0 rows in 0 pages for object "sys.sysmultiobjvalues".
DBCC results for 'sys.syscolpars'.
There are 1233 rows in 23 pages for object "sys.syscolpars".
DBCC results for 'sys.sysxlgns'.
There are 0 rows in 0 pages for object "sys.sysxlgns".
DBCC results for 'sys.sysxsrvs'.
There are 0 rows in 0 pages for object "sys.sysxsrvs".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.sysusermsgs'.
There are 0 rows in 0 pages for object "sys.sysusermsgs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysrmtlgns'.
There are 0 rows in 0 pages for object "sys.sysrmtlgns".
DBCC results for 'sys.syslnklgns'.
There are 0 rows in 0 pages for object "sys.syslnklgns".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 279 rows in 5 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 495 rows in 4 pages for object "sys.sysiscols".
DBCC results for 'sys.sysendpts'.
There are 0 rows in 0 pages for object "sys.sysendpts".
DBCC results for 'sys.syswebmethods'.
There are 0 rows in 0 pages for object "sys.syswebmethods".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 322 rows in 25 pages for object "sys.sysobjvalues".
DBCC results for 'sys.syscscolsegments'.
There are 0 rows in 0 pages for object "sys.syscscolsegments".
DBCC results for 'sys.syscsdictionaries'.
There are 0 rows in 0 pages for object "sys.syscsdictionaries".
DBCC results for 'sys.sysclsobjs'.
There are 20 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 192 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 146 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysfoqueues'.
There are 0 rows in 0 pages for object "sys.sysfoqueues".
DBCC results for 'sys.syschildinsts'.
There are 0 rows in 0 pages for object "sys.syschildinsts".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftsemanticsdb'.
There are 0 rows in 0 pages for object "sys.sysftsemanticsdb".
DBCC results for 'sys.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysftproperties'.
There are 0 rows in 0 pages for object "sys.sysftproperties".
DBCC results for 'sys.sysxmitbody'.
There are 0 rows in 0 pages for object "sys.sysxmitbody".
DBCC results for 'sys.sysfos'.
There are 0 rows in 0 pages for object "sys.sysfos".
DBCC results for 'sys.sysqnames'.
There are 98 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 100 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 19 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 6 rows in 1 pages for object "sys.syssoftobjrefs".
DBCC results for 'sys.sqlagent_jobs'.
There are 0 rows in 0 pages for object "sys.sqlagent_jobs".
DBCC results for 'sys.sqlagent_jobsteps'.
There are 0 rows in 0 pages for object "sys.sqlagent_jobsteps".
DBCC results for 'sys.sqlagent_job_history'.
There are 0 rows in 0 pages for object "sys.sqlagent_job_history".
DBCC results for 'sys.sqlagent_jobsteps_logs'.
There are 0 rows in 0 pages for object "sys.sqlagent_jobsteps_logs".
DBCC results for 'sys.plan_persist_query_text'.
There are 0 rows in 0 pages for object "sys.plan_persist_query_text".
DBCC results for 'sys.plan_persist_query'.
There are 0 rows in 0 pages for object "sys.plan_persist_query".
DBCC results for 'sys.plan_persist_plan'.
There are 0 rows in 0 pages for object "sys.plan_persist_plan".
DBCC results for 'sys.plan_persist_runtime_stats'.
There are 0 rows in 0 pages for object "sys.plan_persist_runtime_stats".
DBCC results for 'sys.plan_persist_runtime_stats_interval'.
There are 0 rows in 0 pages for object "sys.plan_persist_runtime_stats_interval".
DBCC results for 'sys.plan_persist_context_settings'.
There are 0 rows in 0 pages for object "sys.plan_persist_context_settings".
DBCC results for 'sys.plan_persist_query_hints'.
There are 0 rows in 0 pages for object "sys.plan_persist_query_hints".
DBCC results for 'sys.plan_persist_query_template_parameterization'.
There are 0 rows in 0 pages for object "sys.plan_persist_query_template_parameterization".
DBCC results for 'sys.plan_persist_wait_stats'.
There are 0 rows in 0 pages for object "sys.plan_persist_wait_stats".
DBCC results for 'sys.persistent_version_store'.
There are 0 rows in 0 pages for object "sys.persistent_version_store".
DBCC results for 'sys.persistent_version_store_long_term'.
There are 0 rows in 0 pages for object "sys.persistent_version_store_long_term".
DBCC results for 'sys.wpr_bucket_table'.
There are 0 rows in 0 pages for object "sys.wpr_bucket_table".
DBCC results for 'attrep_apply_exceptions'.
There are 0 rows in 0 pages for object "attrep_apply_exceptions".
DBCC results for 'sample.emp'.
There are 2 rows in 1 pages for object "sample.emp".
DBCC results for 'sysarticles'.
There are 1 rows in 1 pages for object "sysarticles".
DBCC results for 'sysarticlecolumns'.
There are 2 rows in 1 pages for object "sysarticlecolumns".
DBCC results for 'sysschemaarticles'.
There are 0 rows in 0 pages for object "sysschemaarticles".
DBCC results for 'syspublications'.
There are 1 rows in 1 pages for object "syspublications".
DBCC results for 'syssubscriptions'.
There are 1 rows in 1 pages for object "syssubscriptions".
DBCC results for 'sysarticleupdates'.
There are 0 rows in 0 pages for object "sysarticleupdates".
DBCC results for 'MSpub_identity_range'.
There are 0 rows in 0 pages for object "MSpub_identity_range".
DBCC results for 'systranschemas'.
There are 0 rows in 0 pages for object "systranschemas".
DBCC results for 'MSpeer_lsns'.
There are 0 rows in 0 pages for object "MSpeer_lsns".
DBCC results for 'MSpeer_request'.
There are 0 rows in 0 pages for object "MSpeer_request".
DBCC results for 'MSpeer_response'.
There are 0 rows in 0 pages for object "MSpeer_response".
DBCC results for 'MSpeer_topologyrequest'.
There are 0 rows in 0 pages for object "MSpeer_topologyrequest".
DBCC results for 'MSpeer_topologyresponse'.
There are 0 rows in 0 pages for object "MSpeer_topologyresponse".
DBCC results for 'MSpeer_originatorid_history'.
There are 0 rows in 0 pages for object "MSpeer_originatorid_history".
DBCC results for 'MSpeer_conflictdetectionconfigrequest'.
There are 0 rows in 0 pages for object "MSpeer_conflictdetectionconfigrequest".
DBCC results for 'MSpeer_conflictdetectionconfigresponse'.
There are 0 rows in 0 pages for object "MSpeer_conflictdetectionconfigresponse".
DBCC results for 'sysreplservers'.
There are 1 rows in 1 pages for object "sysreplservers".
DBCC results for 'attrep_truncation_safeguard'.
There are 2 rows in 1 pages for object "attrep_truncation_safeguard".
DBCC results for 'demodb.emp'.
There are 0 rows in 0 pages for object "demodb.emp".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'sys.filetable_updates_2105058535'.
There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'sample1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Completion time: 2023-03-16T04:23:16.9988880-07:00

[suvbin]

still the issue didn't resolved. Any help on this please .