Hi, reading https://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes
I understand the problem could perhaps be avoided using charset latin1 for tables, instead of default utf-8. Could you confirm ? Since I cannot find any DB scripts for those tables in concern in analytics worker package, could you please tell us where/how we should change this charset ? Thanks, Bernard Le 20 févr. 2019 à 12:53, Bernard Paris <[email protected]<mailto:[email protected]>> a écrit : Hello, yes we are testing mySQL because too much problems with Postgres (socketTimeOutException…) With mySQL: > engine is innoDB > the charset is utf-8 Here are repeated errors I didn't talk about in my previous mail, I suppose there are in relation with the previous ones. ERROR {org.wso2.siddhi.core.table.Table} - Error on 'EI_Analytics_StatApp'. Dropping event at Table 'MediatorStatAgg_MINUTES' as its still trying to reconnect!, events dropped 'EventChunk{first=StreamEvent{ timestamp=1550663520000, beforeWindowData=null, onAfterWindowData=null, outputData=[1550663520000, 1550663520000, -1234, _resource_mismatch_handler_@5:PropertyMediator:HTTP_SC, PropertyMediator:HTTP_SC, mediator, Horaires-API, -324346780, -1838372858, 1550663521447, 1550663521447, 0, 1, 0, 0, 0], type=CURRENT, next=StreamEvent{ timestamp=1550663520000, beforeWindowData=null, onAfterWindowData=null, outputData=[1550663520000, 1550663520000, -1234, _resource_mismatch_handler_@4:PropertyMediator:NO_ENTITY_BODY, PropertyMediator:NO_ENTITY_BODY, mediator, Horaires-API, -324346780, 668423040, 1550663521447, 1550663521447, 0, 1, 0, 0, 0], type=CURRENT, next=StreamEvent{ timestamp=1550663520000, beforeWindowData=null, onAfterWindowData=null, outputData=[1550663520000, 1550663520000, -1234, HashCodeNullComponent, PropertyMediator:messageType, mediator, Horaires-API, -324346780, -492546373, 1550663521447, 1550663521447, 0, 1, 0, 0, 0], type=CURRENT, next=StreamEvent{ timestamp=1550663520000, beforeWindowData=null, onAfterWindowData=null, outputData=[1550663520000, 1550663520000, -1234, HashCodeNullComponent, SwitchMediator, mediator, Horaires-API, -324346780, ………... Bernard Le 20 févr. 2019 à 08:38, Niveathika Rajendran <[email protected]<mailto:[email protected]>> a écrit : Hi Bernard, RDBMS's have limit on the for the primary key and seems that is exceeding, thus the tables could not be created. From your previous emails, I understand you are trying to test this in MySQL. If so, 1. What is the MySQL database storage engine type? (InnoDB..) 2. What is the character set the DB is configured with? Best Regards, Niveathika Rajendran, Senior Software Engineer. Mobile : +94 077 903 7536 [http://c.content.wso2.com/signatures/wso2-signature-general.png] On Tue, Feb 19, 2019 at 3:01 PM Bernard Paris <[email protected]<mailto:[email protected]>> wrote: Hello, (using wso2 EI-6.4.0) Starting analytics worker log lot of errors about several similar table initialization -errors are repeated every 2 minutes - it talks about a key which is too long. Here is an log example for MediatorStatAgg_DAYS table, same error logs occur for MediatorStatAgg_YEARS MediatorStatAgg_HOURS MediatorStatAgg_MINUTES MediatorStatAgg_SECONDS note: I never had this problem when we tested with postgres dbs, strange... Here are: the only thing I have changed in EI_Analytics_StatApp.siddhi : @store(type = 'rdbms', datasource = 'EI_ANALYTICS', field.length="configData:18000") [18000 instead of default 6000] and add the convert time function at the end of the file : from TablePurgingTriggerStream select convert(time:dateSub(triggered_time, 2, 'week'), 'long') as purgingTime delete ConfigEntryTable on ConfigEntryTable.eventTimestamp < purgingTime; from TablePurgingTriggerStream select convert(time:dateSub(triggered_time, 2, 'week'), 'long') as purgingTime delete ESBEventTable on ESBEventTable.eventTimestamp < purgingTime; ERROR {org.wso2.siddhi.core.table.Table} - Error on 'EI_Analytics_StatApp'. Failed to initialize store for table name 'MediatorStatAgg_DAYS': Unable to initialize table 'MediatorStatAgg_DAYS': Specified key was too long; max key length is 3072 bytes Error while connecting to Table 'MediatorStatAgg_DAYS', will retry in '5 sec'. org.wso2.siddhi.core.exception.ConnectionUnavailableException: Failed to initialize store for table name 'MediatorStatAgg_DAYS': Unable to initialize table 'MediatorStatAgg_DAYS': Specified key was too long; max key length is 3072 bytes at org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.connect(RDBMSEventTable.java:882) at org.wso2.siddhi.core.table.Table.connectWithRetry(Table.java:364) at org.wso2.siddhi.core.SiddhiAppRuntime.startWithoutSources(SiddhiAppRuntime.java:400) at org.wso2.siddhi.core.SiddhiAppRuntime.start(SiddhiAppRuntime.java:375) at org.wso2.carbon.stream.processor.core.internal.StreamProcessorService.deploySiddhiApp(StreamProcessorService.java:224) at org.wso2.carbon.stream.processor.core.internal.StreamProcessorDeployer.deploySiddhiQLFile(StreamProcessorDeployer.java:93) at org.wso2.carbon.stream.processor.core.internal.StreamProcessorDeployer.deploy(StreamProcessorDeployer.java:306) at org.wso2.carbon.deployment.engine.internal.DeploymentEngine.lambda$deployArtifacts$0(DeploymentEngine.java:291) at java.util.ArrayList.forEach(ArrayList.java:1257) at org.wso2.carbon.deployment.engine.internal.DeploymentEngine.deployArtifacts(DeploymentEngine.java:282) at org.wso2.carbon.deployment.engine.internal.RepositoryScanner.sweep(RepositoryScanner.java:112) at org.wso2.carbon.deployment.engine.internal.RepositoryScanner.scan(RepositoryScanner.java:68) at org.wso2.carbon.deployment.engine.internal.DeploymentEngine.start(DeploymentEngine.java:121) at org.wso2.carbon.deployment.engine.internal.DeploymentEngineListenerComponent.onAllRequiredCapabilitiesAvailable(DeploymentEngineListenerComponent.java:216) at org.wso2.carbon.kernel.internal.startupresolver.StartupComponentManager.lambda$notifySatisfiableComponents$7(StartupComponentManager.java:266) at java.util.ArrayList.forEach(ArrayList.java:1257) at org.wso2.carbon.kernel.internal.startupresolver.StartupComponentManager.notifySatisfiableComponents(StartupComponentManager.java:252) at org.wso2.carbon.kernel.internal.startupresolver.StartupOrderResolver$1.run(StartupOrderResolver.java:204) at java.util.TimerThread.mainLoop(Timer.java:555) at java.util.TimerThread.run(Timer.java:505) Caused by: org.wso2.extension.siddhi.store.rdbms.exception.RDBMSTableException: Unable to initialize table 'MediatorStatAgg_DAYS': Specified key was too long; max key length is 3072 bytes at org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.createTable(RDBMSEventTable.java:1125) at org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.connect(RDBMSEventTable.java:874) ... 19 more 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:2484) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197) at com.zaxxer.hikari.proxy.PreparedStatementProxy.execute(PreparedStatementProxy.java:44) at com.zaxxer.hikari.proxy.HikariPreparedStatementProxy.execute(HikariPreparedStatementProxy.java) at org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.executeDDQueries(RDBMSEventTable.java:1159) at org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.createTable(RDBMSEventTable.java:1120) ... 20 more [2019-02-19 10:04:09,207] ERROR {org.wso2.siddhi.core.table.Table} - Error on 'EI_Analytics_StatApp'. Failed to initialize store for table name 'MediatorStatAgg_MONTHS': Unable to initialize table 'MediatorStatAgg_MONTHS': Specified key was too long; max key length is 3072 bytes Error while connecting to Table 'MediatorStatAgg_MONTHS', will retry in '5 sec'. org.wso2.siddhi.core.exception.ConnectionUnavailableException: Failed to initialize store for table name 'MediatorStatAgg_MONTHS': Unable to initialize table 'MediatorStatAgg_MONTHS': Specified key was too long; max key length is 3072 bytes at org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.connect(RDBMSEventTable.java:882) In the DB I can see > describe ESBStatAgg_DAYS; +--------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+--------------+------+-----+---------+-------+ | AGG_TIMESTAMP | bigint(20) | NO | PRI | NULL | | | AGG_EVENT_TIMESTAMP | bigint(20) | NO | PRI | NULL | | | metaTenantId | int(11) | NO | PRI | NULL | | | componentId | varchar(254) | NO | PRI | NULL | | | componentName | varchar(254) | NO | PRI | NULL | | | componentType | varchar(254) | NO | PRI | NULL | | | entryPoint | varchar(254) | NO | PRI | NULL | | | AGG_LAST_EVENT_TIMESTAMP | bigint(20) | YES | | NULL | | | eventTimestamp | bigint(20) | YES | | NULL | | | AGG_SUM_duration | bigint(20) | YES | | NULL | | | AGG_COUNT | bigint(20) | YES | | NULL | | | AGG_MIN_duration | bigint(20) | YES | | NULL | | | AGG_MAX_duration | bigint(20) | YES | | NULL | | | AGG_SUM_faultCount | bigint(20) | YES | | NULL | | +--------------------------+--------------+------+-----+---------+-------+ Thanks, Bernard _______________________________________________ Dev mailing list [email protected]<mailto:[email protected]> http://wso2.org/cgi-bin/mailman/listinfo/dev _______________________________________________ Dev mailing list [email protected]<mailto:[email protected]> http://wso2.org/cgi-bin/mailman/listinfo/dev
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
