[Migration] Duplicate Column Name 'TOKEN_BINDING_REF' Error While Migrating From IS_KM 5.9.0 - IS 7.1.0

Jun 25, 2025 by ADMIN 104 views

Migration Duplicate Column Name TOKEN_BINDING_REF Error While Migrating from IS KM 5.9.0 to IS 7.1.0

Introduction

This article addresses a common issue encountered during the migration process from WSO2 Identity Server Knowledge Manager (IS_KM) 5.9.0 to WSO2 Identity Server (IS) 7.1.0. Specifically, it focuses on the Duplicate column name 'TOKEN_BINDING_REF' error. This error can halt the migration and prevent the successful upgrade of your identity management system. Understanding the root cause of this issue and the steps to resolve it is crucial for a smooth migration. This guide provides a comprehensive overview of the problem, its causes, and a detailed solution to help you overcome this obstacle. We will delve into the technical aspects of the error, including the relevant SQL scripts and database schema modifications involved in the migration process. By following the steps outlined in this article, you can ensure a successful migration and avoid potential data loss or system instability.

Error Description

When migrating from IS_KM 5.9.0 to IS 7.1.0, the following error might be observed in the logs:

ERROR {org.wso2.carbon.is.migration.service.SchemaMigrator} - Error occurred while executing SQL script for migrating database java.lang.Exception: Error occurred while executing :   ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN ADD COLUMN TOKEN_BINDING_REF VARCHAR(32) DEFAULT 'NONE', CHANGE IDP_ID IDP_ID INTEGER DEFAULT -1 NOT NULL, DROP INDEX CON_APP_KEY, ADD CONSTRAINT CON_APP_KEY UNIQUE (CONSUMER_KEY_ID,AUTHZ_USER,TENANT_ID,USER_DOMAIN,USER_TYPE,TOKEN_SCOPE_HASH,TOKEN_STATE,TOKEN_STATE_ID,IDP_ID,TOKEN_BINDING_REF)
	at org.wso2.carbon.is.migration.service.SchemaMigrator.executeSQL(SchemaMigrator.java:352)
	at org.wso2.carbon.is.migration.service.SchemaMigrator.executeSQLScript(SchemaMigrator.java:268)
	at org.wso2.carbon.is.migration.service.SchemaMigrator.migrateWithDBConnection(SchemaMigrator.java:119)
	at org.wso2.carbon.is.migration.service.SchemaMigrator.migrate(SchemaMigrator.java:84)
	at org.wso2.carbon.is.migration.VersionMigration.executeMigration(VersionMigration.java:98)
	at org.wso2.carbon.is.migration.VersionMigration.migrate(VersionMigration.java:54)
	at org.wso2.carbon.is.migration.MigrationClientImpl.executeMigrationTask(MigrationClientImpl.java:263)
	at org.wso2.carbon.is.migration.MigrationClientImpl.executeMigration(MigrationClientImpl.java:208)
	at org.wso2.carbon.is.migration.MigrationClientImpl.handleMigrationProcess(MigrationClientImpl.java:115)
	at org.wso2.carbon.is.migration.MigrationClientImpl.execute(MigrationClientImpl.java:85)
	at org.wso2.carbon.identity.core.internal.IdentityCoreServiceComponent.activate(IdentityCoreServiceComponent.java:156)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.eclipse.equinox.internal.ds.model.ServiceComponent.activate(ServiceComponent.java:260)
	at org.eclipse.equinox.internal.ds.model.ServiceComponentProp.activate(ServiceComponentProp.java:146)
	at org.eclipse.equinox.internal.ds.model.ServiceComponentProp.build(ServiceComponentProp.java:345)
	at org.eclipse.equinox.internal.ds.InstanceProcess.buildComponent(InstanceProcess.java:620)
	at org.eclipse.equinox.internal.ds.InstanceProcess.buildComponents(InstanceProcess.java:197)
	at org.eclipse.equinox.internal.ds.Resolver.getEligible(Resolver.java:343)
	at org.eclipse.equinox.internal.ds.SCRManager.serviceChanged(SCRManager.java:222)
	at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:113)
	at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:985)
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:234)
	at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:151)
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:866)
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:804)
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:130)
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:228)
	at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:525)
	at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:544)
	at org.wso2.carbon.core.init.CarbonServerManager.initializeCarbon(CarbonServerManager.java:529)
	at org.wso2.carbon.core.init.CarbonServerManager.removePendingItem(CarbonServerManager.java:305)
	at org.wso2.carbon.core.init.PreAxis2ConfigItemListener.bundleChanged(PreAxis2ConfigItemListener.java:118)
	at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:973)
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:234)
	at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:345)
Caused by: java.sql.SQLSyntaxErrorException: Duplicate column name 'TOKEN_BINDING_REF'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:113)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:829)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:681)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
	at com.sun.proxy.$Proxy65.execute(Unknown Source)
	at org.wso2.carbon.is.migration.service.SchemaMigrator.executeSQL(SchemaMigrator.java:307)
	... 37 more

This error indicates that the migration process is attempting to add a column named TOKEN_BINDING_REF to the IDN_OAUTH2_ACCESS_TOKEN table, but a column with that name already exists. This commonly occurs when a previous migration attempt was interrupted or failed, leaving the column in place.

Key Aspects of the Error

Error Message: The core of the error is the Duplicate column name 'TOKEN_BINDING_REF' message, which clearly points to the conflict.
Table Affected: The error specifically mentions the IDN_OAUTH2_ACCESS_TOKEN table, which is crucial for OAuth 2.0 access token management within WSO2 Identity Server.
SQL Script: The error originates from the execution of an ALTER TABLE SQL statement that attempts to add the TOKEN_BINDING_REF column.
Migration Process: This error occurs during the schema migration phase of the upgrade process, where database schema changes are applied to align with the new version.

Understanding these key aspects is crucial for effectively troubleshooting and resolving the issue.

Steps to Reproduce

The error can be reproduced by attempting a migration from IS_KM 5.9.0 to IS 7.1.0. The general steps to trigger this issue are:

Set up a WSO2 IS_KM 5.9.0 instance.
Configure the database for IS_KM 5.9.0.
Attempt to migrate this instance to WSO2 IS 7.1.0 using the migration scripts and tools provided by WSO2.
During the migration process, the error will be thrown when the migration script attempts to add the TOKEN_BINDING_REF column to the IDN_OAUTH2_ACCESS_TOKEN table.

Detailed Reproduction Steps

To reproduce the error more precisely, you can follow these detailed steps:

Install IS_KM 5.9.0: Download and install WSO2 IS_KM 5.9.0 from the WSO2 website or archive.
Set up the Database: Create a database (e.g., MySQL, PostgreSQL) that will be used for IS_KM 5.9.0. Configure the master-datasources.xml file in the <IS_KM_HOME>/repository/conf/datasources/ directory to point to this database.
Populate Data (Optional): If you have an existing IS_KM 5.9.0 instance, you can use its database. If not, you can populate the database with some sample data for testing purposes.
Download IS 7.1.0: Download WSO2 IS 7.1.0 from the WSO2 website.
Prepare for Migration:
- Stop the IS_KM 5.9.0 server.
- Back up the IS_KM 5.9.0 database.
- Copy the migration scripts from the <IS_7.1.0_HOME>/dbscripts/migration/ directory to a safe location.
Run the Migration:
- Configure the migration.toml file in the <IS_7.1.0_HOME>/repository/conf/ directory with the database connection details and other migration settings.
- Execute the migration tool using the command appropriate for your operating system (e.g., ./migrate.sh on Linux).
Observe the Error: During the migration process, the Duplicate column name 'TOKEN_BINDING_REF' error will appear in the logs.

By following these steps, you can consistently reproduce the error and verify the effectiveness of any solutions you implement.

Root Cause Analysis

The primary cause of the Duplicate column name 'TOKEN_BINDING_REF' error is that the column already exists in the IDN_OAUTH2_ACCESS_TOKEN table. This typically happens when a previous migration attempt failed or was interrupted after the column was added, but before the entire migration process completed. As a result, the migration script tries to add the column again during a subsequent migration attempt, leading to the error.

Detailed Explanation

Migration Scripts: WSO2 Identity Server migrations involve running a series of SQL scripts to update the database schema. These scripts add new tables, columns, indexes, and constraints required by the new version.
ALTER TABLE Statement: The specific script that causes the error includes an ALTER TABLE statement to add the TOKEN_BINDING_REF column to the IDN_OAUTH2_ACCESS_TOKEN table.
Interrupted Migration: If the migration process is interrupted (e.g., due to a server crash, network issue, or manual termination) after this column is added but before the script completes, the database schema will be in an inconsistent state.
Subsequent Migration Attempt: When the migration is attempted again, the script will try to add the TOKEN_BINDING_REF column once more, resulting in the Duplicate column name error.

Potential Scenarios

Manual Interruption: The migration process was manually stopped by an administrator.
System Failure: A server crash or power outage occurred during the migration.
Database Issues: Network connectivity issues or database server problems interrupted the migration.
Migration Tool Errors: The migration tool itself encountered an error and stopped prematurely.

Understanding these potential scenarios helps in identifying the specific circumstances under which the error occurred and in preventing it in the future.

Solution

To resolve the Duplicate column name 'TOKEN_BINDING_REF' error, you need to ensure that the migration script does not attempt to add the column if it already exists. The solution involves checking for the existence of the column before attempting to add it. There are two main approaches to address this issue:

Manual Database Modification: This involves manually checking for the existence of the TOKEN_BINDING_REF column in the IDN_OAUTH2_ACCESS_TOKEN table and, if it exists, skipping the addition of the column.
Conditional SQL Script Execution: Modify the migration script to conditionally add the column only if it does not already exist. This approach is more automated and ensures that the migration process is idempotent.

1. Manual Database Modification

This method involves directly interacting with the database to check for and potentially remove the duplicate column.

Connect to the Database: Use a database client (e.g., MySQL Workbench, pgAdmin) to connect to the WSO2 Identity Server database.
Check for the Column: Execute the following SQL query to check if the TOKEN_BINDING_REF column exists in the IDN_OAUTH2_ACCESS_TOKEN table. The specific query will vary depending on your database type (e.g., MySQL, PostgreSQL).
- MySQL:
```
SHOW COLUMNS FROM IDN_OAUTH2_ACCESS_TOKEN LIKE 'TOKEN_BINDING_REF';
```
- PostgreSQL:
```
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'idn_oauth2_access_token' AND column_name = 'token_binding_ref';
```
If the Column Exists: If the query returns a result, the column already exists. In this case, you can proceed with the remaining migration steps, as the column does not need to be added again. You may need to manually execute the remaining SQL statements from the migration script, skipping the ALTER TABLE statement that adds the column.
If the Column Does Not Exist: If the query returns no results, the column does not exist. This is unexpected, and you should investigate further to ensure that the database schema is in the correct state. You can then run the original migration script, which should now execute without errors.

2. Conditional SQL Script Execution

This method involves modifying the migration script to include a conditional check for the existence of the column before attempting to add it.

Locate the Migration Script: Identify the SQL migration script that contains the ALTER TABLE statement to add the TOKEN_BINDING_REF column. This script is typically located in the <IS_7.1.0_HOME>/dbscripts/migration/ directory.

Modify the Script: Add a conditional check to the script to determine if the column already exists before attempting to add it. The specific syntax for the conditional check will depend on your database type.

MySQL Example:

-- Add TOKEN_BINDING_REF column if it doesn't exist
DELIMITER $
DROP PROCEDURE IF EXISTS add_token_binding_ref_column $
CREATE PROCEDURE add_token_binding_ref_column ()
BEGIN
  IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE table_name = 'IDN_OAUTH2_ACCESS_TOKEN'
                  AND column_name = 'TOKEN_BINDING_REF') THEN
    ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN ADD COLUMN TOKEN_BINDING_REF VARCHAR(32) DEFAULT 'NONE';
  END IF;
END $
DELIMITER ;
CALL add_token_binding_ref_column();
DROP PROCEDURE IF EXISTS add_token_binding_ref_column;

PostgreSQL Example:

-- Add TOKEN_BINDING_REF column if it doesn't exist
DO $
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                  WHERE table_name = 'idn_oauth2_access_token'
                  AND column_name = 'token_binding_ref') THEN
    ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN ADD COLUMN TOKEN_BINDING_REF VARCHAR(32) DEFAULT 'NONE';
  END IF;
END $
;

Save the Modified Script: Save the changes to the migration script.
Re-run the Migration: Execute the migration process again. The modified script will now check for the existence of the TOKEN_BINDING_REF column before attempting to add it, preventing the error.

Choosing the Right Approach

Manual Database Modification: This approach is suitable for one-time fixes or when you want to have more control over the migration process. However, it is more manual and error-prone.
Conditional SQL Script Execution: This approach is more automated and ensures that the migration process is idempotent. It is recommended for production environments and scenarios where you need to ensure that the migration can be re-run without errors.

By implementing one of these solutions, you can effectively resolve the Duplicate column name 'TOKEN_BINDING_REF' error and proceed with the migration from IS_KM 5.9.0 to IS 7.1.0.

Verifying the Solution

After applying the solution, it is essential to verify that the issue has been resolved and that the migration process can complete successfully. The verification steps involve checking the database schema and re-running the migration.

Steps to Verify

Check the Database Schema:
- Connect to the WSO2 Identity Server database using a database client.
- Verify that the TOKEN_BINDING_REF column exists in the IDN_OAUTH2_ACCESS_TOKEN table. You can use the following SQL queries:
  - MySQL:
```
SHOW COLUMNS FROM IDN_OAUTH2_ACCESS_TOKEN LIKE 'TOKEN_BINDING_REF';
```
  - PostgreSQL:
```
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'idn_oauth2_access_token' AND column_name = 'token_binding_ref';
```
- If the column exists, it indicates that the solution has been applied correctly.
Re-run the Migration:
- Execute the migration process again using the WSO2 migration tool.
- Monitor the logs for any errors. If the solution has been implemented correctly, the Duplicate column name 'TOKEN_BINDING_REF' error should not appear.
- The migration process should complete successfully, and all migration scripts should be executed without errors.
Functional Testing:
- Start the WSO2 Identity Server 7.1.0 instance.
- Perform functional testing to ensure that the core features of the Identity Server are working as expected.
- Test the OAuth 2.0 flows, as the TOKEN_BINDING_REF column is related to access token management. Verify that access tokens can be issued, refreshed, and revoked correctly.
- Check other functionalities such as user authentication, authorization, and user management to ensure that the migration has not introduced any regressions.

Expected Outcome

The TOKEN_BINDING_REF column should exist in the IDN_OAUTH2_ACCESS_TOKEN table.
The migration process should complete without any errors related to duplicate columns.
WSO2 Identity Server 7.1.0 should start successfully.
All core functionalities of the Identity Server should work as expected.

By performing these verification steps, you can ensure that the solution has been applied correctly and that the migration has been successful.

Preventative Measures

To prevent the Duplicate column name 'TOKEN_BINDING_REF' error and similar issues in future migrations, consider the following measures:

Backup the Database:
- Always create a backup of the database before starting the migration process. This ensures that you can restore the database to its previous state if any issues occur during the migration.
- Regularly back up the database as part of your maintenance routine.
Ensure Stable Environment:
- Ensure that the environment is stable and reliable before starting the migration. This includes checking the network connectivity, database server health, and the availability of system resources.
- Avoid performing migrations during peak hours when the system is under heavy load.
Test Migration in a Staging Environment:
- Before migrating the production environment, perform a test migration in a staging environment that mirrors the production setup. This allows you to identify and resolve any issues before they impact the production system.
- Use a copy of the production database for the staging environment to ensure that the migration process is tested with realistic data.
Monitor the Migration Process:
- Monitor the migration process closely and check the logs for any errors or warnings. This allows you to identify and address issues as they arise.
- Set up alerts to notify you of any critical errors or failures during the migration.
Use Idempotent Migration Scripts:
- Ensure that the migration scripts are idempotent, meaning that they can be executed multiple times without causing errors or inconsistencies. This can be achieved by adding conditional checks to the scripts to verify if a change has already been applied before attempting to apply it again.
- Use database-specific syntax and techniques to create idempotent migration scripts.
Follow WSO2 Migration Guidelines:
- Follow the official WSO2 migration guidelines and best practices. These guidelines provide detailed instructions and recommendations for performing migrations successfully.
- Review the release notes and migration documentation for any specific instructions or known issues related to the migration.
Proper Planning and Communication:
- Plan the migration carefully and communicate the migration schedule and procedures to all stakeholders.
- Ensure that all team members are aware of the migration process and their roles and responsibilities.

By implementing these preventative measures, you can minimize the risk of encountering migration errors and ensure a smooth and successful upgrade process.

Conclusion

The Duplicate column name 'TOKEN_BINDING_REF' error during migration from IS_KM 5.9.0 to IS 7.1.0 is a common issue that can be resolved by ensuring the migration script checks for the existence of the column before attempting to add it. This article has provided a comprehensive guide to understanding the error, its causes, and detailed solutions using manual database modification and conditional SQL script execution. By verifying the solution and implementing preventative measures, you can ensure a smooth and successful migration process.

Key Takeaways

Understanding the Error: The Duplicate column name error occurs when a migration script attempts to add a column that already exists in the database.
Root Cause: The primary cause is often an interrupted or failed migration attempt that leaves the database schema in an inconsistent state.
Solutions: The error can be resolved by manually checking for the column and skipping the addition or by modifying the migration script to include a conditional check.
Verification: It is crucial to verify the solution by checking the database schema and re-running the migration process.
Prevention: Preventative measures such as backing up the database, ensuring a stable environment, and using idempotent migration scripts can minimize the risk of encountering migration errors.

By following the guidelines and solutions provided in this article, you can effectively address the Duplicate column name 'TOKEN_BINDING_REF' error and ensure a successful migration of your WSO2 Identity Server instance. This will help you leverage the new features and improvements in IS 7.1.0 while maintaining the integrity and stability of your identity management system.