Issue while running FullMigrationCronJob

[premkumar316]

Following exception occurs when the FullMigrationCronJob is run:

ERROR [fullMigrationJob::de.hybris.platform.servicelayer.internal.jalo.ServicelayerJob] [FullMigrationJob] {migrationID=b437d65e-7443-4ee6-9712-99928342d08d} Exception caught: message= Failed to execute SQL script statement #1 of class path resource [sql/transformationFunctions/mssql-general.sql]: CREATE OR ALTER FUNCTION mask_str (@originialvalue nvarchar(255), @maskedvalue nvarchar(255)) RETURNS nvarchar(255) AS BEGIN IF @originialvalue IS NULL RETURN NULL RETURN @maskedvalue END; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Failed to update database "database_name" because the database is read-only.

The database in question is the source DB here. Why would it try to update the source DB?

[lnowakowski]

Internal tables required to track migration/export progress needs to be created in DB. In export mode it creates tables by default in source DB (Azure SQL). However firstly check you configuration as it looks like you're trying to use DB with name database_name

@mateuszporzucek Is it currently possible to redefine (via configuration) in which database internal tables will be stored (source or target)?

[premkumar316]

@lnowakowski Thanks a lot for getting back. Just wanted to provide more context. The name "database_name" is a placeholder. Its my source DB.

This is my prod source DB config:

migration.ds.source.db.url = jdbc:sqlserver://server:port;database=database_name;loginTimeout=4;ApplicationIntent=ReadOnly;

As you can see the source DB is in read-only mode, in this case will the operations in sql/transformationFunctions/mssql-general.sql still succeed?

[mateuszporzucek]

The process will create those metatables in source instead, why ApplicationIntent=ReadOnly; param?

[premkumar316]

@mateuszporzucek In the documentation: https://github.com/SAP/sap-commerce-db-sync/blob/main/docs/user/USER-GUIDE-DATA-REPLICATION.md its specified to give ApplicationIntent=ReadOnly for the property migration.ds.source.db.url . I just followed this.

[premkumar316]

@mateuszporzucek Can you please review my previous comment?

[premkumar316]

@mateuszporzucek @lnowakowski Can you please have a look into my previous comment regarding the param ApplicationIntent=ReadOnly

[lnowakowski]

It might be just an inconsistency in documentation, from time where metadata tables where created always in target DB, hence it make sense to have source (for data export) in read-only mode. However it was discovered that in most cases meta tables should be stored in Azure SQL DB to improve performance, but documentation was not aligned. Additionally we have a ticket in backlog, with feature allowing to specify where meta tables should be persisted (source or target), after this you can again use read-only setup as per documentation, if you prefer.