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

[DesmondWOO]

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

[suvbin]

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.

[john_wang]

Hello @suvbin ,

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

Regards,

John.

[DesmondWOO]

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

[suvbin]

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

thanks.

[DesmondWOO]

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

[suvbin]

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.

[suvbin]

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.

[Heinvandenheuvel]

@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.

[suvbin]