Welcome to the WSO2 Identity Server source code! For info on working with the WSO2 Identity Server repository and contributing code, click the link below.
Consider the table in the MySQL 5.7 script (Identity DB script),
CREATE TABLE IF NOT EXISTS IDN_SCIM_GROUP (
ID INTEGER AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
ROLE_NAME VARCHAR(255) NOT NULL,
ATTR_NAME VARCHAR(1024) NOT NULL,
ATTR_VALUE VARCHAR(1024),
PRIMARY KEY (ID)
)ENGINE INNODB;
And index,
CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN_AN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME, ATTR_NAME);
MySQL use factor of 3 to generate utf-8 character set. As an example, if we use the above query to
create index 'IDX_IDN_SCIM_GROUP_TI_RN_AN' it will use (addition of size of TENANT_ID, ROLE_NAME, ATTR_NAME columns) * 3 bytes to store the key.
If this value is greater than the default value 3072 bytes, we can observe below error.
[2018-11-12 14:12:20,427] ERROR {org.wso2.carbon.identity.core.internal.IdentityCoreServiceComponent} - Error occurred while populating identity configuration properties org.wso2.carbon.identity.base.IdentityRuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes at org.wso2.carbon.identity.base.IdentityRuntimeException.error(IdentityRuntimeException.java:71) at org.wso2.carbon.identity.core.persistence.IdentityDBInitializer.executeSQL(IdentityDBInitializer.java:351) at org.wso2.carbon.identity.core.persistence.IdentityDBInitializer.executeSQLScript(IdentityDBInitializer.java:264) at org.wso2.carbon.identity.core.persistence.IdentityDBInitializer.createIdentityDatabase(IdentityDBInitializer.java:141) at org.wso2.carbon.identity.core.persistence.JDBCPersistenceManager.initializeDatabase(JDBCPersistenceManager.java:116) at org.wso2.carbon.identity.core.internal.IdentityCoreServiceComponent.activate(IdentityCoreServiceComponent.java:133) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.eclipse.equinox.internal.ds.model.ServiceComponent.activate(ServiceComponent.java:235) 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:107) at org.eclipse.osgi.framework.internal.core.BundleContextImpl.dispatchEvent(BundleContextImpl.java:861) at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148) at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:819) at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:771) at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:130) at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:214) at org.eclipse.osgi.framework.internal.core.BundleContextImpl.registerService(BundleContextImpl.java:433) at org.eclipse.osgi.framework.internal.core.BundleContextImpl.registerService(BundleContextImpl.java:451) at org.wso2.carbon.core.init.CarbonServerManager.initializeCarbon(CarbonServerManager.java:515) at org.wso2.carbon.core.init.CarbonServerManager.removePendingItem(CarbonServerManager.java:291) at org.wso2.carbon.core.init.PreAxis2ConfigItemListener.bundleChanged(PreAxis2ConfigItemListener.java:118) at org.eclipse.osgi.framework.internal.core.BundleContextImpl.dispatchEvent(BundleContextImpl.java:847) at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:340) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2480) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2438) at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745) at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy19.execute(Unknown Source) at org.wso2.carbon.identity.core.persistence.IdentityDBInitializer.executeSQL(IdentityDBInitializer.java:318) ... 31 more [2018-11-12 14:12:20,529] INFO {org.wso2.carbon.healthcheck.api.core.internal.HealthMonitorServiceComponent} - Carbon health monitoring service is activated..
Solution:
Modify the index to be a subset of index. This ensures that the total length used to store will be less than
3072 bytes.
CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN_AN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME, ATTR_NAME(500));
mefarazath
commented
5 years ago
Consider the table in the MySQL 5.7 script (Identity DB script),
CREATE TABLE IF NOT EXISTS IDN_SCIM_GROUP ( ID INTEGER AUTO_INCREMENT, TENANT_ID INTEGER NOT NULL, ROLE_NAME VARCHAR(255) NOT NULL, ATTR_NAME VARCHAR(1024) NOT NULL, ATTR_VALUE VARCHAR(1024), PRIMARY KEY (ID) )ENGINE INNODB;
And index, CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN_AN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME, ATTR_NAME);
MySQL use factor of 3 to generate utf-8 character set. As an example, if we use the above query to create index 'IDX_IDN_SCIM_GROUP_TI_RN_AN' it will use (addition of size of TENANT_ID, ROLE_NAME, ATTR_NAME columns) * 3 bytes to store the key.
If this value is greater than the default value 3072 bytes, we can observe below error.
[2018-11-12 14:12:20,427] ERROR {org.wso2.carbon.identity.core.internal.IdentityCoreServiceComponent} - Error occurred while populating identity configuration properties org.wso2.carbon.identity.base.IdentityRuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes at org.wso2.carbon.identity.base.IdentityRuntimeException.error(IdentityRuntimeException.java:71) at org.wso2.carbon.identity.core.persistence.IdentityDBInitializer.executeSQL(IdentityDBInitializer.java:351) at org.wso2.carbon.identity.core.persistence.IdentityDBInitializer.executeSQLScript(IdentityDBInitializer.java:264) at org.wso2.carbon.identity.core.persistence.IdentityDBInitializer.createIdentityDatabase(IdentityDBInitializer.java:141) at org.wso2.carbon.identity.core.persistence.JDBCPersistenceManager.initializeDatabase(JDBCPersistenceManager.java:116) at org.wso2.carbon.identity.core.internal.IdentityCoreServiceComponent.activate(IdentityCoreServiceComponent.java:133) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.eclipse.equinox.internal.ds.model.ServiceComponent.activate(ServiceComponent.java:235) 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:107) at org.eclipse.osgi.framework.internal.core.BundleContextImpl.dispatchEvent(BundleContextImpl.java:861) at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148) at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:819) at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:771) at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:130) at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:214) at org.eclipse.osgi.framework.internal.core.BundleContextImpl.registerService(BundleContextImpl.java:433) at org.eclipse.osgi.framework.internal.core.BundleContextImpl.registerService(BundleContextImpl.java:451) at org.wso2.carbon.core.init.CarbonServerManager.initializeCarbon(CarbonServerManager.java:515) at org.wso2.carbon.core.init.CarbonServerManager.removePendingItem(CarbonServerManager.java:291) at org.wso2.carbon.core.init.PreAxis2ConfigItemListener.bundleChanged(PreAxis2ConfigItemListener.java:118) at org.eclipse.osgi.framework.internal.core.BundleContextImpl.dispatchEvent(BundleContextImpl.java:847) at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:340) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2480) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2438) at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745) at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy19.execute(Unknown Source) at org.wso2.carbon.identity.core.persistence.IdentityDBInitializer.executeSQL(IdentityDBInitializer.java:318) ... 31 more [2018-11-12 14:12:20,529] INFO {org.wso2.carbon.healthcheck.api.core.internal.HealthMonitorServiceComponent} - Carbon health monitoring service is activated..Solution:
Modify the index to be a subset of index. This ensures that the total length used to store will be less than 3072 bytes.