topic Re: failed to load data from SQL server to MYSQL in Qlik Replicate

failed to load data from SQL server to MYSQL

suvbin — Wed, 15 Mar 2023 08:55:53 GMT

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')'

Re: failed to load data from SQL server to MYSQL

sureshkumar — Wed, 15 Mar 2023 10:07:06 GMT

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

Re: failed to load data from SQL server to MYSQL

suvbin — Wed, 15 Mar 2023 11:42:41 GMT

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.

Re: failed to load data from SQL server to MYSQL

john_wang — Wed, 15 Mar 2023 11:50:56 GMT

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.

Re: failed to load data from SQL server to MYSQL

suvbin — Wed, 15 Mar 2023 12:17:39 GMT

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.

Re: failed to load data from SQL server to MYSQL

john_wang — Wed, 15 Mar 2023 12:25:17 GMT

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.

Re: failed to load data from SQL server to MYSQL

suvbin — Thu, 16 Mar 2023 08:06:14 GMT

Hi John,

This i have done.

Re: failed to load data from SQL server to MYSQL

Bill_Steinagle — Thu, 16 Mar 2023 11:01:01 GMT

@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

Re: failed to load data from SQL server to MYSQL

suvbin — Thu, 16 Mar 2023 11:55:46 GMT

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

Re: failed to load data from SQL server to MYSQL

suvbin — Tue, 21 Mar 2023 08:16:02 GMT

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

Re: failed to load data from SQL server to MYSQL

DesmondWOO — Tue, 21 Mar 2023 09:25:04 GMT

Hi @suvbin ,

1. After performing the Full Backup, what error message are you currently experiencing?

2. Are you using "sa" account?

3. Have you enabled replication on your SQL Server? Details can be found in the user guide .

Regards,
Desmond

Re: failed to load data from SQL server to MYSQL

suvbin — Wed, 22 Mar 2023 10:54:34 GMT

hi DesmondWoo,

1. After performing the Full Backup, what error message are you currently experiencing?

Replication is happening but getting below error :

i have set everything to full backup. but still its showing error. Request help here

2. Are you using "sa" account? --> yes

3. Have you enabled replication on your SQL Server? Details can be found in the user guide . -- Followed these steps. Replication is happening but getting error as mentioned above.

Thank you.

Re: failed to load data from SQL server to MYSQL

john_wang — Wed, 22 Mar 2023 12:34:32 GMT

Hello @suvbin ,

please make sure you did the full backup successfully. Looks like the backup was not done yet.

Regards,

John.

Re: failed to load data from SQL server to MYSQL

DesmondWOO — Thu, 23 Mar 2023 02:58:27 GMT

Hi @suvbin ,

Please run following statement to check if full backup is done.

SELECT count(*)
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
s.database_name = <your database name>
and s.[type]='D'
and s.recovery_model in ('FULL','BULK-LOGGED'

If full backup is done, returned value is > 0.

Regards,
Desmond

Re: failed to load data from SQL server to MYSQL

suvbin — Thu, 23 Mar 2023 03:12:12 GMT

The return value is '0' . Am not sure, where i have done mistake. Kindly assist.

thanks.

Re: failed to load data from SQL server to MYSQL

DesmondWOO — Thu, 23 Mar 2023 03:49:32 GMT

Hi @suvbin ,

In the Microsoft SQL Server Management Studio,

Right click your database "sample1" --> Tasks --> Backup --> click OK.

If the backup is successful, you will see a message stating "The backup of database 'sample1' completed successfully".

If the query still returns "0", please run following statement to check the content.

SELECT s.database_name, s.[type], s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'Sample1'

Regards,
Desmond

Re: failed to load data from SQL server to MYSQL

suvbin — Thu, 23 Mar 2023 04:15:31 GMT

Its showing "FULL".

The below are the changes that i made to make the database (sample1) as "FULL" .. Please let me know if any where else am i missing.

Re: failed to load data from SQL server to MYSQL

suvbin — Fri, 24 Mar 2023 02:03:47 GMT

Hi @DesmondWOO

Am able to perform a full load + cdc also. Even the cdc is performing , the error still shows up periodically.

Appreciate your help here.

Thanks.

Re: failed to load data from SQL server to MYSQL

Heinvandenheuvel — Sun, 26 Mar 2023 22:08:19 GMT

@suvbin - I'm not convinced you 've actually made a backup. Yes you have shown the backup model is set correctly, and you have shown the SMSS section to create a backup, but was it done?

Note, this is 'just a flag' to tell SQLserver internally to be careful with the Tlog. The full backup is never visited and you may actually proceed to make that backup to the NUL device - the bit-bucket, to make Replicate happy.

At any rate, you best bet after this error when convinced that a backup was indeed made is to run the query that Replicate runs to make the decision. You can find this in the detailed log, or in the SQL history on the server.

It looks like (possibly version dependent): "SELECT count(*) FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() and s.[type]='D' and s.recovery_model in ('FULL','BULK-LOGGED')

Just execute that one the server using the DB and authentications which Replicate is told to use.

Good luck

Hein.

Re: failed to load data from SQL server to MYSQL

suvbin — Tue, 28 Mar 2023 05:41:13 GMT