Problems with artifacts to big for internal hsql database?
Hello!
We have starteds to get these kind of errors:
[2024-10-11 13:58:23,387] DEBUG - mpl.indexer.BuildMetadataIndex - Failed to update nuget provider metadata for build <removed> / Build 853 on Agent 1. Started Tue Jan 30 16:21:56 CET 2024, finished Tue Jan 30 16:41:07 CET 2024. Status NORMAL 'Success'. Error: Unexpected exception: SQL error when doing 'DML query' while performing SQL query: SQL DML: insert into entry (provider_id, build_id, entry_key, data_values) values (?, ?, ?, ?) | PARAMETERS: 3, 853, "<removed>", "[Ljava.lang.String;@421935e5": java.sql.SQLDataException: data exception: array data, right truncation. Caused by: org.hsqldb.HsqlException: data exception: array data, right truncation
jetbrains.buildServer.serverSide.db.UnexpectedDBException: Unexpected exception: SQL error when doing 'DML query' while performing SQL query: SQL DML: insert into entry (provider_id, build_id, entry_key, data_values) values (?, ?, ?, ?) | PARAMETERS: 3, 853, "<removed>", "[Ljava.lang.String;@421935e5": java.sql.SQLDataException: data exception: array data, right truncation. Caused by: org.hsqldb.HsqlException: data exception: array data, right truncation
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
at jetbrains.buildServer.serverSide.db.DBFunctions$14.run(DBFunctions.java:1226)
at jetbrains.buildServer.serverSide.db.DBFunctions$14.run(DBFunctions.java:1223)
at jetbrains.buildServer.serverSide.db.DBInternalUtils.rawOperation(DBInternalUtils.java:66)
at jetbrains.buildServer.serverSide.db.DBInternalUtils.rawOperation(DBInternalUtils.java:51)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeDmlInternal(DBFunctions.java:1223)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeDml(DBFunctions.java:1180)
at jetbrains.buildServer.serverSide.metadata.impl.metadata.HSQLMetadataStorage.doAddBuildEntry(HSQLMetadataStorage.java:375)
at jetbrains.buildServer.serverSide.metadata.impl.metadata.HSQLMetadataStorage.access$300(HSQLMetadataStorage.java:66)
at jetbrains.buildServer.serverSide.metadata.impl.metadata.HSQLMetadataStorage$8.run(HSQLMetadataStorage.java:4)
at jetbrains.buildServer.serverSide.metadata.impl.metadata.HSQLMetadataStorage$8.run(HSQLMetadataStorage.java:1)
at jetbrains.buildServer.serverSide.metadata.impl.metadata.HSQLMetadataStorage$7.run(HSQLMetadataStorage.java:2)
at jetbrains.buildServer.serverSide.db.InternalDBUtil.runAndRetry(InternalDBUtil.java:28)
at jetbrains.buildServer.serverSide.metadata.impl.metadata.HSQLMetadataStorage.runQueryAndRetryOnFailure(HSQLMetadataStorage.java:136)
at jetbrains.buildServer.serverSide.metadata.impl.metadata.HSQLMetadataStorage.addBuildEntry(HSQLMetadataStorage.java:47)
at jetbrains.buildServer.serverSide.metadata.impl.metadata.MetadataStorageBean.addBuildEntry(MetadataStorageBean.java:2)
at jetbrains.buildServer.nuget.feed.server.index.impl.NuGetArtifactsMetadataProvider.generateMedatadata(NuGetArtifactsMetadataProvider.kt:83)
at jetbrains.buildServer.serverSide.metadata.impl.indexer.BuildMetadataIndex$2.apply(BuildMetadataIndex.java:4)
at jetbrains.buildServer.serverSide.metadata.impl.indexer.BuildMetadataIndex$2.apply(BuildMetadataIndex.java:1)
at jetbrains.buildServer.serverSide.metadata.impl.metadata.HSQLMetadataStorage.updateCache(HSQLMetadataStorage.java:169)
at jetbrains.buildServer.serverSide.metadata.impl.metadata.MetadataStorageBean.updateCache(MetadataStorageBean.java:12)
at jetbrains.buildServer.serverSide.metadata.impl.indexer.BuildMetadataIndex.updateMetadata(BuildMetadataIndex.java:36)
at jetbrains.buildServer.serverSide.metadata.impl.indexer.BuildMetadataIndex.updateMetadataForAllProviders(BuildMetadataIndex.java:28)
at jetbrains.buildServer.serverSide.metadata.impl.indexer.BuildMetadataIndex.addBuild(BuildMetadataIndex.java:52)
at jetbrains.buildServer.serverSide.build.index.BuildPTRIndexer.lambda$addBuild$6(BuildPTRIndexer.java:144)
at jetbrains.buildServer.util.NamedThreadFactory.executeWithNewThreadName(NamedThreadFactory.java:76)
at jetbrains.buildServer.serverSide.build.index.BuildPTRIndexer.lambda$addBuild$7(BuildPTRIndexer.java:195)
at jetbrains.buildServer.serverSide.build.index.BuildPTRIndexer.lambda$obtainIndex$16(BuildPTRIndexer.java:19)
at jetbrains.buildServer.serverSide.build.index.BuildPTRIndexer.obtainIndex(BuildPTRIndexer.java:147)
at jetbrains.buildServer.serverSide.build.index.BuildPTRIndexer.obtainIndex(BuildPTRIndexer.java:134)
at jetbrains.buildServer.serverSide.build.index.BuildPTRIndexer.lambda$addBuild$8(BuildPTRIndexer.java:248)
at jetbrains.buildServer.serverSide.build.index.BuildTask.run(BuildTask.java:32)
at jetbrains.buildServer.serverSide.build.index.BuildTask.call(BuildTask.java:5)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at jetbrains.buildServer.util.PositionNamedDaemonThreadFactory.lambda$newThread$0(PositionNamedDaemonThreadFactory.java:40)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.sql.SQLDataException: data exception: array data, right truncation
... 43 more
Caused by: org.hsqldb.HsqlException: data exception: array data, right truncation
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.types.ArrayType.convertToType(Unknown Source)
at org.hsqldb.ExpressionOp.getValue(Unknown Source)
at org.hsqldb.StatementDML.getInsertData(Unknown Source)
at org.hsqldb.StatementInsert.getResult(Unknown Source)
at org.hsqldb.StatementDMQL.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 41 more
What we have done to mitigiate this is to set the parameter: teamcity.hsqlMetadataStorage.maxEntryKeys to a bigger number since what we have gathered from the source code is the parameter controlling the field that is getting truncated. Now we are getting other errors that we thing are related to us increasing the field size of column DATA_VALUES.
Does anyone know what kind of data is stored in that field (DATA_VALUES)? Why we are getting the error in the first place? Our biggest artifacts files are about 300 Mb.
Is there a limitation to name length of artifacts or something?
Please sign in to leave a comment.
Please consider the following points:
1. We strongly recommend against using internal HSQLDB in scenarios other than demos/POC/evaluation. Please see https://www.jetbrains.com/help/teamcity/set-up-external-database.html#Default+Internal+Database.
2. We advise against applying/changing internal properties when not instructed to do so by TeamCity support or the documentation. Some of them, while applicable in some scenarios, may cause unexpected behavior in others. And others exist only for debug purposes and should never be changed from default values in user environments.
Regarding the initial error message, what version of TeamCity you're using?
Best regards,
Anton
Thanks for replying!
We are using MSSQL as database, the HSQL we found by reading the stacktrace and reading code when we got the error.
Yes you are correct. We were having issues with the nuget feed getting stuck when indexing new packages and since that is central to our development workflow we tried to fix it and we tracked that error to this HSQL problem but yes I think we broke something else instead when we changed the parameter.
We are running version 2024.07.3.
Thank you for the details!
I've confirmed some points with the developers, and it seems that you were on the right track with that internal property.
It looks like the nuget package that causes this error has more attributes than we can store by default (1024) in a build metadata database (which uses HSQLDB).
The suggestion is:
1. Change the internal property `teamcity.hsqlMetadataStorage.maxEntryKeys=2048` (default value is 1024).
2. Stop TeamCity.
3. Backup metadata files (`<TeamCity Data Directory>/system/caches/buildsMetadata`).
4. Delete the metadata cache files
5. Start TeamCity
Metadata tables will be recreated with new limits and indexes will be rebuilt. But re-indexing can take several hours or even days, depending on the number of indexed builds, so please proceed with it carefully.
Best regards,
Anton
Hi!
We have tried this but this we still get problems with packages not being indexed.
What attributes are stored in that field? Is there something we can do with our artifacts to mitigate this issue? Or do you have any other suggestions?
Will it be possible for you to send us packages on which indexing doesn't work? We could then reproduce this case locally and investigate it.
You can upload them using https://uploads.jetbrains.com/ and share the upload ID.
Best regards,
Anton
Hi!
This is the upload id for one of the packages: 2024_11_05_omcqFGrLGepwL4T3SBzHJQ
Hope you can reproduce
Regards
Sorry for the wait.
We tried to reproduce the issue with your package, but if works as expected: freshly created TeamCity with NuGet Feed was able to index the package. It means that some other package is blocking the indexing process when trying to store long array data.
Here is an example of what we store:
```
ID: <id>
PROVIDER_ID: <provider_id>
BUILD_ID: <build_id>
ENTRY_KEY: <package.name>.<version>
DATA_VALUES: ARRAY['Package.Name','<version>','<version>','<Publisher>','<License>','<Description>','false','https://<publisher>/','false',...,'<SHA-hash>']
```
From the logs we see the following:
```
Error: Unexpected exception: SQL error when doing 'DML query' while performing SQL query: SQL DML: insert into entry (provider_id, build_id, entry_key, data_values) values (?, ?, ?, ?) | PARAMETERS: 3, 853, "<removed>", "[Ljava.lang.String;@421935e5": java.sql.SQLDataException: data exception: array data, right truncation. Caused by: org.hsqldb.HsqlException: data exception: array data, right truncation
```
It means that the broken artifact, which would help to continue the investigation, was produced in the build with ID 853.
Could you check the artifacts produced by the build with ID 853? Please check the Artifacts tab for this build, find all *.nupkg files there and check their metadata. The easiest way to check metadata is to unpack the *.nupkg file like it's a zip archive and open the *.nuspec file. The DATA_VALUES array for some packages from this build is probably way too long. Please share any suspected packages for us to take a look at them, probably we may need to introduce some changes on how we write it to the metadata DB.
Best regards,
Anton