Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[MSSQL] APIM 3.1.0 to 4.2.0 migration fails when running the migration tool #2019

Closed
Akila94 opened this issue Jul 21, 2023 · 1 comment
Closed

[MSSQL] APIM 3.1.0 to 4.2.0 migration fails when running the migration tool #2019

Akila94 opened this issue Jul 21, 2023 · 1 comment
Labels
Affected/APIM-3.1.0 Affected/APIM-4.x.x Affected/APIM-4.2.0 Area/Migration Component/APIM-MigrationClient Priority/Highest Severity/Critical Type/Bug

Comments

@Akila94
Copy link
Member

Akila94 commented Jul 21, 2023 •
edited
Loading

Description

When migrating APIM 3.1.0 to 4.2.0, the following error was encountered,

[2023-07-13 15:11:36,002]  INFO - PreDBScriptMigrator WSO2 API-M Migration Task : Executing the PreDBScriptMigrator for AM_DB SQL script at /Users/akilaa/wso2/ob-packs/2023/july/5/mssql-migration/post-migration/wso2am-4.2.0/migration-resources/migration-scripts/pre-migration-scripts/migration-3.2.0_to_4.0.0/
[2023-07-13 15:11:36,002]  INFO - DBUtil WSO2 API-M Migration Task : Executing SQL script at /Users/akilaa/wso2/ob-packs/2023/july/5/mssql-migration/post-migration/wso2am-4.2.0/migration-resources/migration-scripts/pre-migration-scripts/migration-3.2.0_to_4.0.0/
[2023-07-13 15:11:36,048] ERROR - DBUtil WSO2 API-M Migration Task : Error occurred while migrating databases
com.microsoft.sqlserver.jdbc.SQLServerException: Column 'AM_REVISION.REVISION_UUID' is not the same length or scale as referencing column 'AM_API_REVISION_METADATA.REVISION_UUID' in foreign key 'FK__AM_API_RE__REVIS__000AF8CF'. Columns participating in a foreign key relationship must be defined with the same length and scale.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) ~[sqljdbc41.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1522) ~[sqljdbc41.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) ~[sqljdbc41.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) ~[sqljdbc41.jar:?]
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) ~[sqljdbc41.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1717) ~[sqljdbc41.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) ~[sqljdbc41.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) ~[sqljdbc41.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332) ~[sqljdbc41.jar:?]
	at jdk.internal.reflect.GeneratedMethodAccessor174.invoke(Unknown Source) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
	at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
	at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118) ~[jdbc-pool_9.0.56.wso2v1.jar:?]
	at com.sun.proxy.$Proxy53.execute(Unknown Source) ~[?:?]
	at org.wso2.carbon.apimgt.migration.util.DBUtil.runSQLScript(DBUtil.java:66) ~[org.wso2.carbon.apimgt.migrate.client-4.2.0.53.jar:?]
	at org.wso2.carbon.apimgt.migration.migrator.commonMigrators.PreDBScriptMigrator.run(PreDBScriptMigrator.java:52) ~[org.wso2.carbon.apimgt.migrate.client-4.2.0.53.jar:?]
	at org.wso2.carbon.apimgt.migration.migrator.client.V400Migration.migrate(V400Migration.java:54) ~[org.wso2.carbon.apimgt.migrate.client-4.2.0.53.jar:?]
	at org.wso2.carbon.apimgt.migration.APIMMigrationClient.executeMigration(APIMMigrationClient.java:90) ~[org.wso2.carbon.apimgt.migrate.client-4.2.0.53.jar:?]
	at org.wso2.carbon.apimgt.migration.APIMMigrationClient.completedServerStartup(APIMMigrationClient.java:63) ~[org.wso2.carbon.apimgt.migrate.client-4.2.0.53.jar:?]
	at org.wso2.carbon.core.internal.CarbonCoreServiceComponent.notifyAfter(CarbonCoreServiceComponent.java:264) ~[?:?]
	at org.wso2.carbon.core.internal.StartupFinalizerServiceComponent.completeInitialization(StartupFinalizerServiceComponent.java:214) ~[?:?]
	at org.wso2.carbon.core.internal.StartupFinalizerServiceComponent.serviceChanged(StartupFinalizerServiceComponent.java:309) ~[?:?]
	at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:113) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:985) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:234) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:151) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:866) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:804) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:130) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:228) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:525) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:544) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.wso2.carbon.throttling.agent.internal.ThrottlingAgentServiceComponent.registerThrottlingAgent(ThrottlingAgentServiceComponent.java:118) ~[org.wso2.carbon.tenant.throttling.agent_4.9.20.jar:?]
	at org.wso2.carbon.throttling.agent.internal.ThrottlingAgentServiceComponent.activate(ThrottlingAgentServiceComponent.java:96) ~[org.wso2.carbon.tenant.throttling.agent_4.9.20.jar:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
	at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
	at org.eclipse.equinox.internal.ds.model.ServiceComponent.activate(ServiceComponent.java:260) ~[org.eclipse.equinox.ds_1.4.400.v20160226-2036.jar:?]
	at org.eclipse.equinox.internal.ds.model.ServiceComponentProp.activate(ServiceComponentProp.java:146) ~[org.eclipse.equinox.ds_1.4.400.v20160226-2036.jar:?]
	at org.eclipse.equinox.internal.ds.model.ServiceComponentProp.build(ServiceComponentProp.java:345) ~[org.eclipse.equinox.ds_1.4.400.v20160226-2036.jar:?]
	at org.eclipse.equinox.internal.ds.InstanceProcess.buildComponent(InstanceProcess.java:620) ~[org.eclipse.equinox.ds_1.4.400.v20160226-2036.jar:?]
	at org.eclipse.equinox.internal.ds.InstanceProcess.buildComponents(InstanceProcess.java:197) ~[org.eclipse.equinox.ds_1.4.400.v20160226-2036.jar:?]
	at org.eclipse.equinox.internal.ds.Resolver.getEligible(Resolver.java:343) ~[org.eclipse.equinox.ds_1.4.400.v20160226-2036.jar:?]
	at org.eclipse.equinox.internal.ds.SCRManager.serviceChanged(SCRManager.java:222) ~[org.eclipse.equinox.ds_1.4.400.v20160226-2036.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:113) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:985) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:234) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:151) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:866) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:804) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:130) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:228) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:525) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:544) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.wso2.carbon.core.init.CarbonServerManager.initializeCarbon(CarbonServerManager.java:529) ~[org.wso2.carbon.core_4.8.1.jar:?]
	at org.wso2.carbon.core.init.CarbonServerManager.removePendingItem(CarbonServerManager.java:305) ~[org.wso2.carbon.core_4.8.1.jar:?]
	at org.wso2.carbon.core.init.PreAxis2ConfigItemListener.bundleChanged(PreAxis2ConfigItemListener.java:118) ~[org.wso2.carbon.core_4.8.1.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:973) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:234) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:345) ~[org.eclipse.osgi_3.14.0.v20190517-1309.jar:?]

Had to modify the size of the REVISION_UUID column of the table AM_API_REVISION_METADATA to match the size of the same column in the table AM_REVISION in the 3.2.0 to 4.0.0 pre-migration script of the APIM migration tool to overcome this issue.

Need to change from:

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_REVISION_METADATA]') AND TYPE IN (N'U'))

CREATE TABLE AM_API_REVISION_METADATA (
    API_UUID VARCHAR(64),
    REVISION_UUID VARCHAR(64),
    API_TIER VARCHAR(128),
    UNIQUE (API_UUID,REVISION_UUID),
    FOREIGN KEY(REVISION_UUID) REFERENCES AM_REVISION(REVISION_UUID) ON DELETE CASCADE
);

to

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_REVISION_METADATA]') AND TYPE IN (N'U'))

CREATE TABLE AM_API_REVISION_METADATA (
    API_UUID VARCHAR(64),
    REVISION_UUID VARCHAR(255),
    API_TIER VARCHAR(128),
    UNIQUE (API_UUID,REVISION_UUID),
    FOREIGN KEY(REVISION_UUID) REFERENCES AM_REVISION(REVISION_UUID) ON DELETE CASCADE
);

This needs to be fixed in the APIM migration tool.

Steps to Reproduce

Try to migrate APIM from 3.1.0 to 4.2.0

Affected Component

APIM

Version

4.2.0

Environment Details (with versions)

No response

Relevant Log Output

No response

Related Issues

No response

Suggested Labels

Migration, APIM
@Akila94
Copy link
Member Author

Akila94 commented Jul 21, 2023 •
edited
Loading

Closing this issue since this will be moved to an internal ticket https://github.com/wso2-enterprise/wso2-apim-internal/issues/3835.

@Akila94 Akila94 closed this as completed Jul 21, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Affected/APIM-3.1.0 Affected/APIM-4.x.x Affected/APIM-4.2.0 Area/Migration Component/APIM-MigrationClient Priority/Highest Severity/Critical Type/Bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@Akila94