Skywalking OAP + PostgreSQL DB and ERROR "value too long for type character varying(200)"

[jarofi]

We use Skywalking V9.6.0 with PostgreSQL as backend storage. The log file skywalking-oap-server.log contains a lot of errors "value too long for type character varying(200)":

2023-12-20 14:19:22,120 - org.apache.skywalking.oap.server.storage.plugin.jdbc.common.dao.JDBCBatchDAO - 81 [DataCarrier.H2_ASYNCHRONOUS_BATCH_PERSISTENT.Consumer.2.Thread] ERROR [] - Batch entry 0 INSERT INTO alarm_record_20231220(id,table_name,scope,name,id0,id1,start_time,alarm_message,rule_name,tags_raw_data,time_bucket) VALUES ('alarm_record_20231220141921_endpoint_relation_resp_time_rule_VXNlcg==.0_VXNlcg==_TUFLVXxXQVN8UFJPRA==.1_R0VUOi9hY3Rpb25zL2x1bXAve2ZhTnVtYmVyOltBLVpdezN9LVxkezN9LVtBLVpdezF9LVxkezJ9LVxkK317dW5kZXJMaW5lOl8/fXt2ZXJzaW9uTnVtYmVyOlxkezEsMn18LnswLDB9fS97ZGV0YWlsVHlwZTpmaW5hbmNpbmd9','alarm_record',6,'User in User to GET:/actions/lump/{faNumber:[A-Z]{3}-\d{3}-[A-Z]{1}-\d{2}-\d+}{underLine:_?}{versionNumber:\d{1,2}|.{0,0}}/{detailType:financing} in XXX|WAS|PROD','VXNlcg==.0_VXNlcg==','TUFLVXxXQVN8UFJPRA==.1_R0VUOi9hY3Rpb25zL2x1bXAve2ZhTnVtYmVyOltBLVpdezN9LVxkezN9LVtBLVpdezF9LVxkezJ9LVxkK317dW5kZXJMaW5lOl8/fXt2ZXJzaW9uTnVtYmVyOlxkezEsMn18LnswLDB9fS97ZGV0YWlsVHlwZTpmaW5hbmNpbmd9',1703078361965,'Response time of endpoint relation User in User to GET:/actions/lump/{faNumber:[A-Z]{3}-\d{3}-[A-Z]{1}-\d{2}-\d+}{underLine:_?}{versionNumber:\d{1,2}|.{0,0}}/{detailType:financing} in XXXX|XXX|PROD is more than 1000ms in 2 minutes of last 10 minutes','endpoint_relation_resp_time_rule','W10=',20231220141921) was aborted: ERROR: value too long for type character varying(200)  Call getNextException to see other errors in the batch.
java.sql.BatchUpdateException: Batch entry 0 INSERT INTO alarm_record_20231220(id,table_name,scope,name,id0,id1,start_time,alarm_message,rule_name,tags_raw_data,time_bucket) VALUES ('alarm_record_20231220141921_endpoint_relation_resp_time_rule_VXNlcg==.0_VXNlcg==_TUFLVXxXQVN8UFJPRA==.1_R0VUOi9hY3Rpb25zL2x1bXAve2ZhTnVtYmVyOltBLVpdezN9LVxkezN9LVtBLVpdezF9LVxkezJ9LVxkK317dW5kZXJMaW5lOl8/fXt2ZXJzaW9uTnVtYmVyOlxkezEsMn18LnswLDB9fS97ZGV0YWlsVHlwZTpmaW5hbmNpbmd9','alarm_record',6,'User in User to GET:/actions/lump/{faNumber:[A-Z]{3}-\d{3}-[A-Z]{1}-\d{2}-\d+}{underLine:_?}{versionNumber:\d{1,2}|.{0,0}}/{detailType:financing} in XXXX|XXX|PROD','VXNlcg==.0_VXNlcg==','TUFLVXxXQVN8UFJPRA==.1_R0VUOi9hY3Rpb25zL2x1bXAve2ZhTnVtYmVyOltBLVpdezN9LVxkezN9LVtBLVpdezF9LVxkezJ9LVxkK317dW5kZXJMaW5lOl8/fXt2ZXJzaW9uTnVtYmVyOlxkezEsMn18LnswLDB9fS97ZGV0YWlsVHlwZTpmaW5hbmNpbmd9',1703078361965,'Response time of endpoint relation User in User to GET:/actions/lump/{faNumber:[A-Z]{3}-\d{3}-[A-Z]{1}-\d{2}-\d+}{underLine:_?}{versionNumber:\d{1,2}|.{0,0}}/{detailType:financing} in XXXX|XXX|PROD is more than 1000ms in 2 minutes of last 10 minutes','endpoint_relation_resp_time_rule','W10=',20231220141921) was aborted: ERROR: value too long for type character varying(200)  Call getNextException to see other errors in the batch.
        at org.postgresql.jdbc.BatchResultHandler.handleCompletion(BatchResultHandler.java:186) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:571) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:887) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1663) ~[postgresql-42.4.1.jar:42.4.1]
        at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128) ~[HikariCP-3.1.0.jar:?]
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) ~[HikariCP-3.1.0.jar:?]
        at org.apache.skywalking.oap.server.storage.plugin.jdbc.BatchSQLExecutor.executeBatch(BatchSQLExecutor.java:85) ~[storage-jdbc-hikaricp-plugin-9.6.0.jar:9.6.0]
        at org.apache.skywalking.oap.server.storage.plugin.jdbc.BatchSQLExecutor.invoke(BatchSQLExecutor.java:74) ~[storage-jdbc-hikaricp-plugin-9.6.0.jar:9.6.0]
        at org.apache.skywalking.oap.server.storage.plugin.jdbc.common.dao.JDBCBatchDAO.lambda$flush$1(JDBCBatchDAO.java:78) ~[storage-jdbc-hikaricp-plugin-9.6.0.jar:9.6.0]
        at java.util.HashMap.forEach(HashMap.java:1421) [?:?]
        at org.apache.skywalking.oap.server.storage.plugin.jdbc.common.dao.JDBCBatchDAO.flush(JDBCBatchDAO.java:75) [storage-jdbc-hikaricp-plugin-9.6.0.jar:9.6.0]
        at org.apache.skywalking.oap.server.storage.plugin.jdbc.common.dao.JDBCBatchDAO$H2BatchConsumer.consume(JDBCBatchDAO.java:105) [storage-jdbc-hikaricp-plugin-9.6.0.jar:9.6.0]
        at org.apache.skywalking.oap.server.library.datacarrier.consumer.ConsumerThread.consume(ConsumerThread.java:75) [library-datacarrier-queue-9.6.0.jar:9.6.0]
        at org.apache.skywalking.oap.server.library.datacarrier.consumer.ConsumerThread.run(ConsumerThread.java:53) [library-datacarrier-queue-9.6.0.jar:9.6.0]
Caused by: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(200)
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:560) ~[postgresql-42.4.1.jar:42.4.1]
        ... 13 more

2023-12-20 14:20:39,627 - org.apache.skywalking.oap.server.storage.plugin.jdbc.common.dao.JDBCBatchDAO - 81 [pool-3-thread-2] ERROR [] - Batch entry 31 INSERT INTO endpoint_relation_server_side_20231220(id,table_name,source_endpoint,dest_endpoint,component_id,entity_id,time_bucket) VALUES ('202312201420_VXNlcg==.0-VXNlcg==-TUFLVXxXQVN8UFJPRA==.1-UE9TVDovYWN0aW9ucy9sdW1wL3tmYU51bWJlcjpbQS1aXXszfS1cZHszfS1bQS1aXXsxfS1cZHsyfS1cZCt9e3VuZGVyTGluZTpfP317dmVyc2lvbk51bWJlcjpcZHsxLDJ9fC57MCwwfX0ve2RldGFpbFR5cGU6ZmluYW5jaW5nfS9lZGl0','endpoint_relation_server_side','VXNlcg==.0_VXNlcg==','TUFLVXxXQVN8UFJPRA==.1_UE9TVDovYWN0aW9ucy9sdW1wL3tmYU51bWJlcjpbQS1aXXszfS1cZHszfS1bQS1aXXsxfS1cZHsyfS1cZCt9e3VuZGVyTGluZTpfP317dmVyc2lvbk51bWJlcjpcZHsxLDJ9fC57MCwwfX0ve2RldGFpbFR5cGU6ZmluYW5jaW5nfS9lZGl0',14,'VXNlcg==.0-VXNlcg==-TUFLVXxXQVN8UFJPRA==.1-UE9TVDovYWN0aW9ucy9sdW1wL3tmYU51bWJlcjpbQS1aXXszfS1cZHszfS1bQS1aXXsxfS1cZHsyfS1cZCt9e3VuZGVyTGluZTpfP317dmVyc2lvbk51bWJlcjpcZHsxLDJ9fC57MCwwfX0ve2RldGFpbFR5cGU6ZmluYW5jaW5nfS9lZGl0',202312201420) was aborted: ERROR: value too long for type character varying(200)  Call getNextException to see other errors in the batch.
java.sql.BatchUpdateException: Batch entry 31 INSERT INTO endpoint_relation_server_side_20231220(id,table_name,source_endpoint,dest_endpoint,component_id,entity_id,time_bucket) VALUES ('202312201420_VXNlcg==.0-VXNlcg==-TUFLVXxXQVN8UFJPRA==.1-UE9TVDovYWN0aW9ucy9sdW1wL3tmYU51bWJlcjpbQS1aXXszfS1cZHszfS1bQS1aXXsxfS1cZHsyfS1cZCt9e3VuZGVyTGluZTpfP317dmVyc2lvbk51bWJlcjpcZHsxLDJ9fC57MCwwfX0ve2RldGFpbFR5cGU6ZmluYW5jaW5nfS9lZGl0','endpoint_relation_server_side','VXNlcg==.0_VXNlcg==','TUFLVXxXQVN8UFJPRA==.1_UE9TVDovYWN0aW9ucy9sdW1wL3tmYU51bWJlcjpbQS1aXXszfS1cZHszfS1bQS1aXXsxfS1cZHsyfS1cZCt9e3VuZGVyTGluZTpfP317dmVyc2lvbk51bWJlcjpcZHsxLDJ9fC57MCwwfX0ve2RldGFpbFR5cGU6ZmluYW5jaW5nfS9lZGl0',14,'VXNlcg==.0-VXNlcg==-TUFLVXxXQVN8UFJPRA==.1-UE9TVDovYWN0aW9ucy9sdW1wL3tmYU51bWJlcjpbQS1aXXszfS1cZHszfS1bQS1aXXsxfS1cZHsyfS1cZCt9e3VuZGVyTGluZTpfP317dmVyc2lvbk51bWJlcjpcZHsxLDJ9fC57MCwwfX0ve2RldGFpbFR5cGU6ZmluYW5jaW5nfS9lZGl0',202312201420) was aborted: ERROR: value too long for type character varying(200)  Call getNextException to see other errors in the batch.
        at org.postgresql.jdbc.BatchResultHandler.handleCompletion(BatchResultHandler.java:186) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:571) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:887) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1663) ~[postgresql-42.4.1.jar:42.4.1]
        at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128) ~[HikariCP-3.1.0.jar:?]
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) ~[HikariCP-3.1.0.jar:?]
        at org.apache.skywalking.oap.server.storage.plugin.jdbc.BatchSQLExecutor.executeBatch(BatchSQLExecutor.java:85) ~[storage-jdbc-hikaricp-plugin-9.6.0.jar:9.6.0]
        at org.apache.skywalking.oap.server.storage.plugin.jdbc.BatchSQLExecutor.invoke(BatchSQLExecutor.java:74) ~[storage-jdbc-hikaricp-plugin-9.6.0.jar:9.6.0]
        at org.apache.skywalking.oap.server.storage.plugin.jdbc.common.dao.JDBCBatchDAO.lambda$flush$1(JDBCBatchDAO.java:78) ~[storage-jdbc-hikaricp-plugin-9.6.0.jar:9.6.0]
        at java.util.HashMap.forEach(HashMap.java:1421) ~[?:?]
        at org.apache.skywalking.oap.server.storage.plugin.jdbc.common.dao.JDBCBatchDAO.flush(JDBCBatchDAO.java:75) ~[storage-jdbc-hikaricp-plugin-9.6.0.jar:9.6.0]
        at org.apache.skywalking.oap.server.core.storage.PersistenceTimer.lambda$extractDataAndSave$3(PersistenceTimer.java:135) ~[server-core-9.6.0.jar:9.6.0]
        at java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1804) [?:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
        at java.lang.Thread.run(Thread.java:833) [?:?]
Caused by: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(200)
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.4.1.jar:42.4.1]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:560) ~[postgresql-42.4.1.jar:42.4.1]
        ... 15 more

Is there any way to modify the table templates (i.e. increase the length of the corresponding columns) alarm_record* and endpoint_relation_server_side*? Since new tables are created for each day, it does not make sense to modify the columns of existing tables. Thank you very much in advance for your help.

[wu-sheng]

They are controlled in the source files. AlarmRecord and EndpointRelationServerSideMetrics.

About alarm record, #11404 had changed it, and included in the latest 9.7.0 release.

[jarofi]

But version 9.7 only solves the AlarmRecord problem, not the EndpointRelationServerSideMetrics problem. Or am I wrong?

[wu-sheng]

I don't know that part. If you can see which field you face, we can fix easily.
We don't have your data, so can't tell whether there is an issue.

[jarofi]

The second part of the above log contains information about an error in EndpointRelationServerSideMetrics and the problem is with the source_endpoint and dest_endpoint columns, which are set to 200 and can actually have values over 200 characters (512 characters should be enough).

[wu-sheng]

Would you like to send a pull request to fix this? I think ServiceRelation*Metrics and InstanceRelation*Metrics should be updated accordingly as well.

[wu-sheng]

By default, the length controls of instance and endpoint are

    instanceNameMaxLength: ${SW_INSTANCE_NAME_MAX_LENGTH:70}
    # The max length of service + endpoint names should be less than 240
    endpointNameMaxLength: ${SW_ENDPOINT_NAME_MAX_LENGTH:150}

We don't face that as obeying the default configuration for the demo, personally, I am using OpenSearch for the product.
My recommendation is you could fix them and align the length in all places to avoid this happening again.

[jarofi]

Would you like to send a pull request to fix this? I think ServiceRelation*Metrics and InstanceRelation*Metrics should be updated accordingly as well.

I am just getting started with Skywalking so I would like to leave the decision up to someone experienced.

[wu-sheng]

The main possible issue to set them as 512 is, that the entity ID of the endpoint relation combines the source and dist IDs, so, if the length of both endpoints is near 500, then the ID would be over-length.

[wu-sheng]

It may be better if you could update the default configuration in application.yml to control the length.

    # The max length of service + instance names should be less than 200
    serviceNameMaxLength: ${SW_SERVICE_NAME_MAX_LENGTH:70}
    instanceNameMaxLength: ${SW_INSTANCE_NAME_MAX_LENGTH:70}
    # The max length of service + endpoint names should be less than 240
    endpointNameMaxLength: ${SW_ENDPOINT_NAME_MAX_LENGTH:150}

Meanwhile, I may only feel good to set the max length of those IDs to 250.

[wu-sheng]

I submitted this, #11686. No guarantee this could fix your issue if the length at your side is the edge case.

[jarofi]

Thank you very much for the #11686.

I checked our setting and we use the default settings in application.yml:

    # The max length of service + instance names should be less than 200
    serviceNameMaxLength: ${SW_SERVICE_NAME_MAX_LENGTH:70}
    instanceNameMaxLength: ${SW_INSTANCE_NAME_MAX_LENGTH:70}
    # The max length of service + endpoint names should be less than 240
    endpointNameMaxLength: ${SW_ENDPOINT_NAME_MAX_LENGTH:150}

So I don't know why we're reaching the length limit.

[wu-sheng]

You could take a closer look about that PR, the topo metrics could be over the length limitation.

[jarofi]

It seems that upgrading to version 9.7.0 has not solved the problem:

java.sql.BatchUpdateException: Batch entry 0 INSERT INTO alarm_record_20240115(id,table_name,scope,name,id0,id1,start_time,alarm_message,rule_name,tags_raw_data,time_bucket) VALUES ('alarm_record_20240115163216_endpoint_relation_resp_time_rule_VXNlcg==.0_VXNlcg==_TUFLVXxXQVN8UFJPRA==.1_R0VUOi9hY3Rpb25zL3thY3Rpb25UeXBlOmZsZWV0fHJldGFpbHxsdW1wfS97ZmFOdW1iZXI6W0EtWl17M30tXGR7M30tW0EtWl17MX0tXGR7Mn0tXGQrfXt1bmRlckxpbmU6Xz99e3ZlcnNpb25OdW1iZXI6XGR7MSwyfXwuezAsMH19L3Zpbi9saXN0','alarm_record',6,'User in User to GET:/actions/{actionType:fleet|retail|lump}/{faNumber:[A-Z]{3}-\d{3}-[A-Z]{1}-\d{2}-\d+}{underLine:_?}{versionNumber:\d{1,2}|.{0,0}}/vin/list in XXXX|XXX|XXXX','VXNlcg==.0_VXNlcg==','TUFLVXxXQVN8UFJPRA==.1_R0VUOi9hY3Rpb25zL3thY3Rpb25UeXBlOmZsZWV0fHJldGFpbHxsdW1wfS97ZmFOdW1iZXI6W0EtWl17M30tXGR7M30tW0EtWl17MX0tXGR7Mn0tXGQrfXt1bmRlckxpbmU6Xz99e3ZlcnNpb25OdW1iZXI6XGR7MSwyfXwuezAsMH19L3Zpbi9saXN0',1705332736559,'Response time of endpoint relation User in User to GET:/actions/{actionType:fleet|retail|lump}/{faNumber:[A-Z]{3}-\d{3}-[A-Z]{1}-\d{2}-\d+}{underLine:_?}{versionNumber:\d{1,2}|.{0,0}}/vin/list in XXXX|XXX|XXXX is more than 1000ms in 2 minutes of last 10 minutes','endpoint_relation_resp_time_rule','W10=',20240115163216) was aborted: ERROR: value too long for type character varying(200)  Call getNextException to see other errors in the batch.

java.sql.BatchUpdateException: Batch entry 79 INSERT INTO endpoint_relation_server_side_20240115(id,table_name,source_endpoint,dest_endpoint,component_id,entity_id,time_bucket) VALUES ('202401150738_QlQyfFdBU3xQUk9E.1-L2J0Mi9iYXN5cy9hcGkvdjEvc2tvZGEvY2FyZHM/ZGRwPVg3WCZkZHA9WDFEJmRkcD1YMkImZGRwPVgyRSZkZHA9WDRKJndpdGhJbmFjdGl2ZUNhcmRzPXRydWUmcGFnZVNpemU9OTk5OTk5Jm1vZGVsWWVhcj0yMDI2Jm1vZGVsWWVhcj0yMDI0Jm1vZGVsWWVhcj0y-QlQyfFdBU3xQUk9E.1-R0VUOi9hcGkvdjEve2JyYW5kfS9jYXJkcw==','endpoint_relation_server_side','QlQyfFdBU3xQUk9E.1_L2J0Mi9iYXN5cy9hcGkvdjEvc2tvZGEvY2FyZHM/ZGRwPVg3WCZkZHA9WDFEJmRkcD1YMkImZGRwPVgyRSZkZHA9WDRKJndpdGhJbmFjdGl2ZUNhcmRzPXRydWUmcGFnZVNpemU9OTk5OTk5Jm1vZGVsWWVhcj0yMDI2Jm1vZGVsWWVhcj0yMDI0Jm1vZGVsWWVhcj0y','QlQyfFdBU3xQUk9E.1_R0VUOi9hcGkvdjEve2JyYW5kfS9jYXJkcw==',14,'QlQyfFdBU3xQUk9E.1-L2J0Mi9iYXN5cy9hcGkvdjEvc2tvZGEvY2FyZHM/ZGRwPVg3WCZkZHA9WDFEJmRkcD1YMkImZGRwPVgyRSZkZHA9WDRKJndpdGhJbmFjdGl2ZUNhcmRzPXRydWUmcGFnZVNpemU9OTk5OTk5Jm1vZGVsWWVhcj0yMDI2Jm1vZGVsWWVhcj0yMDI0Jm1vZGVsWWVhcj0y-QlQyfFdBU3xQUk9E.1-R0VUOi9hcGkvdjEve2JyYW5kfS9jYXJkcw==',202401150738) was aborted: ERROR: value too long for type character varying(200)  Call getNextException to see other errors in the batch.