Cannot upload after OrientDB to H2 migration: Unique index or primary key violation

sonatype / nexus-public

Sonatype Nexus Repository Open-source codebase mirror

https://www.sonatype.com/products/repository-oss-download

Eclipse Public License 1.0

1.96k stars 577 forks source link

Cannot upload after OrientDB to H2 migration: Unique index or primary key violation #449

Open dioss-Machiel opened 3 months ago

dioss-Machiel commented 3 months ago

We are running "OSS 3.70.1-02", after running the migration script and setting nexus.datastore.enabled=true everything seems to work except uploading new data.

When uploading we see exceptions like this Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.RAW_ASSET_BLOB(ASSET_BLOB_ID) ( /* key:0 */ 36, 'default@e6eab474-ed51-4131-8c45-f9bdae0fd551', CAST(420190 AS BIGINT), 'application/x-bzip2', JSON '{""sha1"":""cd45031441ac944eacebdc8d4b82b20cd9cc8461"",""md5"":""a23646617546bf6ad56f061d8b283c85""}', TIMESTAMP WITH TIME ZONE '2019-04-04 13:41:49.113+00', 'stijnv', '10.152.0.56', TIMESTAMP WITH TIME ZONE '1970-01-01 00:00:00+00')"; SQL statement: INSERT INTO raw_asset_blob (blob_ref, blob_size, content_type, checksums, blob_created, created_by, created_by_ip ) VALUES (?, ?, ?, ?, ?, ?, ? ) [23505-224]

After some further investigation it seems like all "auto increment" columns in the H2 database have been reset to start at "1"

I believe the workaround is to manually fix all the auto increment columns to not start at "1" but at the last value + 1:

ALTER TABLE PUBLIC.DOCKER_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM DOCKER_ASSET_BLOB

However, I would assume the migration script already takes this into account, so I am wondering if something else is going on and other data might be missing in the migration?

Menschomat commented 3 months ago

We are experiencing exact the same. Indexing went through (took quiet a while), but all pushes error out as described above. We rolled back to previous snapshot.

terje2001 commented 3 months ago

We also hit the same problem, we tried the SQL snippet above but found we had to do it for every table, happy to share the sql we generated to do them all if of interest to anyone. It seemingly is working now.

f3l1x commented 3 months ago

Same problem here. Can you @terje2001 share the snippet?

dtronche commented 3 months ago

I have the same error when performing a docker push but the issue also exist on a lot of other repositories: python package repository: Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.PYPI_ASSET_BLOB(ASSET_BLOB_ID) ( / key:0 / 18, 'pip-blob@43059e25-75a8-42ba-8420-742c3c2fb394', CAST(63133 AS BIGINT), 'application/zip', JSON '{""sha1"":""9d05ff0d7df26cc02e181e6fe183450ed9f1ad1e"",""sha256"":""f22fa1e554c9ddfd16e6e41ac79759e17be9e492b3587efa038054674760e72d"",""md5"":""435db0f122c83decd27524f690e89650""}', TIMESTAMP WITH TIME ZONE '2022-01-18 06:21:33.289+00', 'anonymous', '192.168.2.118', TIMESTAMP WITH TIME ZONE '1970-01-01 00:00:00+00')"; SQL statement:

upload on a raw data repository: 2024-08-13 08:36:49,175+0000 ERROR [qtp158623224-724] admin org.sonatype.nexus.coreui.UploadResource - Unable to perform upload to repository raw-directory org.sonatype.nexus.datastore.api.DuplicateKeyException: Duplicate key at org.sonatype.nexus.datastore.mybatis.EntityExecutor.mapException(EntityExecutor.java:255) at org.sonatype.nexus.datastore.mybatis.EntityExecutor.update(EntityExecutor.java:77) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)

Apt package update: 2024-08-13 08:37:09,499+0000 WARN [qtp158623224-743] *UNKNOWN org.sonatype.nexus.repository.httpbridge.internal.ViewServlet - Failure servicing: GET /nexus/repository/apt-security-proxy/dists/bullseye-security/InRelease org.sonatype.nexus.datastore.api.DuplicateKeyException: Duplicate key at org.sonatype.nexus.datastore.mybatis.EntityExecutor.mapException(EntityExecutor.java:255) at org.sonatype.nexus.datastore.mybatis.EntityExecutor.update(EntityExecutor.java:77)

To get back to the orient database, the following commands seems to be sufficient without having to restore a snapshot:

rm $data-dir/db/nexus.mv.db
remove line nexus.datastore.enabled=true in $data-dir/etc/nexus.properties

ShivanethGithub commented 3 months ago

Hello,

Facing the same issue here. We would really appreciate a new version for the database migrator that handles these auto-increment cases.

cthiemann commented 3 months ago

I have used the following script to reset all the auto-increment counters (it alters some 100 columns and takes about a minute). Nexus must not be running when executing the script.

#!/bin/bash

NEXUS_BIN=/opt/nexus/nexus-3.71.0-06
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
h2sql() {
    echo "> $@" >&2
    java -cp "$NEXUS_BIN"/system/com/h2database/h2/*/h2-*.jar org.h2.tools.Shell -url jdbc:h2:"$NEXUS_DATA"/db/nexus -sql "$@"
}

schema=$(h2sql "SCRIPT NODATA")
echo "$schema" | while read line; do
    if [[ $line =~ ^CREATE\ CACHED\ TABLE\ \"PUBLIC\"\.\"([^\"]+)\" ]]; then
        tbl="${BASH_REMATCH[1]}"
    elif [[ $line =~ ^\"([^\"]+)\"\ .*\ GENERATED\ BY\ DEFAULT ]]; then
        col="${BASH_REMATCH[1]}"
        h2sql "ALTER TABLE $tbl ALTER COLUMN $col RESTART WITH SELECT max($col) + 1 FROM $tbl"
    fi
done

K1kc4 commented 3 months ago

I have used the following script to reset all the auto-increment counters (it alters some 100 columns and takes about a minute). Nexus must not be running when executing the script.

#!/bin/bash

NEXUS_BIN=/opt/nexus/nexus-3.71.0-06
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
h2sql() {
    echo "> $@" >&2
    java -cp "$NEXUS_BIN"/system/com/h2database/h2/*/h2-*.jar org.h2.tools.Shell -url jdbc:h2:"$NEXUS_DATA"/db/nexus -sql "$@"
}

schema=$(h2sql "SCRIPT NODATA")
echo "$schema" | while read line; do
    if [[ $line =~ ^CREATE\ CACHED\ TABLE\ \"PUBLIC\"\.\"([^\"]+)\" ]]; then
        tbl="${BASH_REMATCH[1]}"
    elif [[ $line =~ ^\"([^\"]+)\"\ .*\ GENERATED\ BY\ DEFAULT ]]; then
        col="${BASH_REMATCH[1]}"
        h2sql "ALTER TABLE $tbl ALTER COLUMN $col RESTART WITH SELECT max($col) + 1 FROM $tbl"
    fi
done

Thanks.

If you are using nexus in container, you can run this script as entrypoint and then just remove after its done.

gjevardat commented 3 months ago

Same error on my side. Script above fixed the issue, thanks

Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.MAVEN2_COMPONENT(COMPONENT_ID) ( /* key:0 */ 36, 7, 'gaia.cu7', 'VariCharacterisation', 'jar', '7.0', JSON '{""maven2"":{""baseVersion"":""7.0"",""groupId"":""gaia.cu7"",""artifactId"":""VariCharacterisation"",""packaging"":""jar"",""version"":""7.0""}}', TIMESTAMP WITH TIME ZONE '2024-02-09 20:57:40.106+00', TIMESTAMP WITH TIME ZONE '2024-08-13 11:50:33.431142+02', '7.0', '000000007.000000000.000000000.c', NULL)"; SQL statement:
INSERT INTO maven2_component (repository_id, namespace, name, kind, version, normalized_version, attributes

    ) VALUES (?, ?, ?,
    ?, ?, ?, ?

    ) [23505-224]

lalo-mx commented 3 months ago

A repair task would also be helpful. Is there anything like that already?

TheCakeIsNaOH commented 3 months ago

For those using docker compose, create the above script and add a entrypoint: pointing to it. For the fix script, use NEXUS_BIN=/opt/sonatype/nexus/ And NEXUS_DATA would default to /nexus-data/, although I think that could be changed depending on your setup.

Prospica commented 3 months ago

I have used the following script to reset all the auto-increment counters (it alters some 100 columns and takes about a minute). Nexus must not be running when executing the script.
#!/bin/bash

NEXUS_BIN=/opt/nexus/nexus-3.71.0-06
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
h2sql() {
    echo "> $@" >&2
    java -cp "$NEXUS_BIN"/system/com/h2database/h2/*/h2-*.jar org.h2.tools.Shell -url jdbc:h2:"$NEXUS_DATA"/db/nexus -sql "$@"
}

schema=$(h2sql "SCRIPT NODATA")
echo "$schema" | while read line; do
    if [[ $line =~ ^CREATE\ CACHED\ TABLE\ \"PUBLIC\"\.\"([^\"]+)\" ]]; then
        tbl="${BASH_REMATCH[1]}"
    elif [[ $line =~ ^\"([^\"]+)\"\ .*\ GENERATED\ BY\ DEFAULT ]]; then
        col="${BASH_REMATCH[1]}"
        h2sql "ALTER TABLE $tbl ALTER COLUMN $col RESTART WITH SELECT max($col) + 1 FROM $tbl"
    fi
done
Thanks.

If you are using nexus in container, you can run this script as entrypoint and then just remove after its done.

We also have this prob after migrated OrientDB to H2 with nexus 3.71 This script also solve our issue, great help, thanks!

murainwood commented 3 months ago

Pls run the following sql in h2 console (could via web console) to fix the DB issue, regardless of whether you are using Linux, Windows, or macOS.

ALTER TABLE TAG ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM TAG; ALTER TABLE RAW_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM RAW_CONTENT_REPOSITORY; ALTER TABLE RAW_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM RAW_COMPONENT; ALTER TABLE RAW_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM RAW_COMPONENT_TAG; ALTER TABLE RAW_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM RAW_ASSET_BLOB; ALTER TABLE APT_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM APT_BROWSE_NODE; ALTER TABLE MAVEN2_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM MAVEN2_CONTENT_REPOSITORY; ALTER TABLE UPGRADE_TASKS ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM UPGRADE_TASKS; ALTER TABLE SOFT_DELETED_BLOBS ALTER COLUMN RECORD_ID RESTART WITH SELECT max(RECORD_ID) + 1 FROM SOFT_DELETED_BLOBS; ALTER TABLE MAVEN2_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM MAVEN2_COMPONENT_TAG; ALTER TABLE MAVEN2_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM MAVEN2_ASSET_BLOB; ALTER TABLE NUGET_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM NUGET_CONTENT_REPOSITORY; ALTER TABLE NPM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM NPM_BROWSE_NODE; ALTER TABLE NUGET_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM NUGET_ASSET; ALTER TABLE NUGET_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM NUGET_COMPONENT_TAG; ALTER TABLE NUGET_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM NUGET_ASSET_BLOB; ALTER TABLE HELM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM HELM_CONTENT_REPOSITORY; ALTER TABLE HELM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM HELM_COMPONENT; ALTER TABLE HELM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM HELM_COMPONENT_TAG; ALTER TABLE HELM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM HELM_ASSET_BLOB; ALTER TABLE YUM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM YUM_BROWSE_NODE; ALTER TABLE DOCKER_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM DOCKER_CONTENT_REPOSITORY; ALTER TABLE DOCKER_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM DOCKER_COMPONENT; ALTER TABLE DOCKER_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM DOCKER_COMPONENT_TAG; ALTER TABLE DOCKER_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM DOCKER_ASSET_BLOB; ALTER TABLE RUBYGEMS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM RUBYGEMS_BROWSE_NODE; ALTER TABLE PYPI_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM PYPI_CONTENT_REPOSITORY; ALTER TABLE PYPI_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM PYPI_COMPONENT; ALTER TABLE PYPI_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM PYPI_COMPONENT_TAG; ALTER TABLE PYPI_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM PYPI_ASSET_BLOB; ALTER TABLE GITLFS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM GITLFS_BROWSE_NODE; ALTER TABLE YUM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM YUM_CONTENT_REPOSITORY; ALTER TABLE YUM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM YUM_COMPONENT; ALTER TABLE YUM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM YUM_COMPONENT_TAG; ALTER TABLE YUM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM YUM_ASSET_BLOB; ALTER TABLE DOCKER_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM DOCKER_BROWSE_NODE; ALTER TABLE NPM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM NPM_CONTENT_REPOSITORY; ALTER TABLE NPM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM NPM_COMPONENT; ALTER TABLE NPM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM NPM_COMPONENT_TAG; ALTER TABLE NPM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM NPM_ASSET_BLOB; ALTER TABLE RAW_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM RAW_BROWSE_NODE; ALTER TABLE APT_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM APT_CONTENT_REPOSITORY; ALTER TABLE APT_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM APT_COMPONENT; ALTER TABLE APT_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM APT_COMPONENT_TAG; ALTER TABLE APT_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM APT_ASSET_BLOB; ALTER TABLE MAVEN2_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM MAVEN2_BROWSE_NODE; ALTER TABLE CONDA_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM CONDA_CONTENT_REPOSITORY; ALTER TABLE CONDA_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM CONDA_COMPONENT; ALTER TABLE CONDA_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM CONDA_COMPONENT_TAG; ALTER TABLE CONDA_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM CONDA_ASSET_BLOB; ALTER TABLE PYPI_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM PYPI_BROWSE_NODE; ALTER TABLE CONAN_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM CONAN_CONTENT_REPOSITORY; ALTER TABLE CONAN_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM CONAN_COMPONENT; ALTER TABLE CONAN_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM CONAN_COMPONENT_TAG; ALTER TABLE CONAN_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM CONAN_ASSET_BLOB; ALTER TABLE CONDA_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM CONDA_BROWSE_NODE; ALTER TABLE R_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM R_CONTENT_REPOSITORY; ALTER TABLE R_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM R_COMPONENT; ALTER TABLE R_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM R_COMPONENT_TAG; ALTER TABLE R_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM R_ASSET_BLOB; ALTER TABLE CONAN_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM CONAN_BROWSE_NODE; ALTER TABLE GITLFS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM GITLFS_CONTENT_REPOSITORY; ALTER TABLE GITLFS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM GITLFS_COMPONENT; ALTER TABLE GITLFS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM GITLFS_COMPONENT_TAG; ALTER TABLE GITLFS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM GITLFS_ASSET_BLOB; ALTER TABLE HELM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM HELM_BROWSE_NODE; ALTER TABLE COCOAPODS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM COCOAPODS_CONTENT_REPOSITORY; ALTER TABLE COCOAPODS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM COCOAPODS_COMPONENT; ALTER TABLE COCOAPODS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM COCOAPODS_COMPONENT_TAG; ALTER TABLE COCOAPODS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM COCOAPODS_ASSET_BLOB; ALTER TABLE R_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM R_BROWSE_NODE; ALTER TABLE RUBYGEMS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM RUBYGEMS_CONTENT_REPOSITORY; ALTER TABLE RUBYGEMS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM RUBYGEMS_COMPONENT; ALTER TABLE RUBYGEMS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM RUBYGEMS_COMPONENT_TAG; ALTER TABLE RUBYGEMS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM RUBYGEMS_ASSET_BLOB; ALTER TABLE NUGET_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM NUGET_BROWSE_NODE; ALTER TABLE GO_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM GO_CONTENT_REPOSITORY; ALTER TABLE GO_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM GO_COMPONENT; ALTER TABLE GO_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM GO_COMPONENT_TAG; ALTER TABLE GO_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM GO_ASSET_BLOB; ALTER TABLE COCOAPODS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM COCOAPODS_BROWSE_NODE; ALTER TABLE P2_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM P2_CONTENT_REPOSITORY; ALTER TABLE P2_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM P2_COMPONENT; ALTER TABLE P2_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM P2_COMPONENT_TAG; ALTER TABLE P2_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM P2_ASSET_BLOB; ALTER TABLE GO_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM GO_BROWSE_NODE; ALTER TABLE MAVEN2_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM MAVEN2_COMPONENT; ALTER TABLE LOGGING_OVERRIDES ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM LOGGING_OVERRIDES; ALTER TABLE BLOB_STORE_METRICS ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM BLOB_STORE_METRICS; ALTER TABLE MAVEN2_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM MAVEN2_ASSET; ALTER TABLE APT_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM APT_KEY_VALUE; ALTER TABLE APT_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM APT_ASSET; ALTER TABLE RAW_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM RAW_ASSET; ALTER TABLE AGGREGATED_METRICS ALTER COLUMN AGGREGATED_METRIC_ID RESTART WITH SELECT max(AGGREGATED_METRIC_ID) + 1 FROM AGGREGATED_METRICS; ALTER TABLE HISTORICAL_LOGIN_INFO ALTER COLUMN HISTORICAL_LOGIN_INFO_ID RESTART WITH SELECT max(HISTORICAL_LOGIN_INFO_ID) + 1 FROM HISTORICAL_LOGIN_INFO; ALTER TABLE METRICS_LOG ALTER COLUMN METRIC_ID RESTART WITH SELECT max(METRIC_ID) + 1 FROM METRICS_LOG; ALTER TABLE NPM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM NPM_ASSET; ALTER TABLE NUGET_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM NUGET_COMPONENT; ALTER TABLE RUBYGEMS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM RUBYGEMS_ASSET; ALTER TABLE DOWNLOAD_COUNT ALTER COLUMN DOWNLOAD_COUNT_ID RESTART WITH SELECT max(DOWNLOAD_COUNT_ID) + 1 FROM DOWNLOAD_COUNT; ALTER TABLE DOCKER_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM DOCKER_ASSET; ALTER TABLE YUM_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM YUM_KEY_VALUE; ALTER TABLE YUM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM YUM_ASSET; ALTER TABLE COMPONENT_APPLICATION_SCAN_SCHEDULE ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM COMPONENT_APPLICATION_SCAN_SCHEDULE; ALTER TABLE COMPONENT_APPLICATION_SCAN ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM COMPONENT_APPLICATION_SCAN; ALTER TABLE HELM_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM HELM_KEY_VALUE; ALTER TABLE HELM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM HELM_ASSET; ALTER TABLE GITLFS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM GITLFS_ASSET; ALTER TABLE PYPI_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM PYPI_ASSET; ALTER TABLE CONDA_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM CONDA_ASSET; ALTER TABLE CONAN_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM CONAN_ASSET; ALTER TABLE R_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM R_ASSET; ALTER TABLE COCOAPODS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM COCOAPODS_ASSET; ALTER TABLE GO_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM GO_ASSET; ALTER TABLE P2_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM P2_ASSET; ALTER TABLE P2_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM P2_BROWSE_NODE;

goodale commented 3 months ago

I ran the script above from https://github.com/cthiemann. The only output was > SCRIPT NODATAand unfortunately the error is still occurring.

gjevardat commented 3 months ago

I ran the script above from https://github.com/cthiemann. The only output was > SCRIPT NODATAand unfortunately the error is still occurring.

Probably your NEXUS_DATA variable is not pointing to your nexus-data folder ( I had same symptoms when I tried)

goodale commented 3 months ago

I ran the script above from https://github.com/cthiemann. The only output was > SCRIPT NODATAand unfortunately the error is still occurring.

Probably your NEXUS_DATA variable is not pointing to your nexus-data folder ( I had same symptoms when I tried)

my 'nexus.mv.db' file is located in /opt/sonatype-work/nexus3/db I've tried both of the following and get the error either way. Am i pointing to the wrong folder? Thanks!

NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3/db

goodale commented 3 months ago

Pls run the following sql in h2 console (could via web console) to fix the DB issue, regardless of whether you are using Linux, Windows, or macOS.

ALTER TABLE TAG ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM TAG; ALTER TABLE RAW_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM RAW_CONTENT_REPOSITORY; ALTER TABLE RAW_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM RAW_COMPONENT; ALTER TABLE RAW_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM RAW_COMPONENT_TAG; ALTER TABLE RAW_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM RAW_ASSET_BLOB; ALTER TABLE APT_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM APT_BROWSE_NODE; ALTER TABLE MAVEN2_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM MAVEN2_CONTENT_REPOSITORY; ALTER TABLE UPGRADE_TASKS ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM UPGRADE_TASKS; ALTER TABLE SOFT_DELETED_BLOBS ALTER COLUMN RECORD_ID RESTART WITH SELECT max(RECORD_ID) + 1 FROM SOFT_DELETED_BLOBS; ALTER TABLE MAVEN2_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM MAVEN2_COMPONENT_TAG; ALTER TABLE MAVEN2_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM MAVEN2_ASSET_BLOB; ALTER TABLE NUGET_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM NUGET_CONTENT_REPOSITORY; ALTER TABLE NPM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM NPM_BROWSE_NODE; ALTER TABLE NUGET_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM NUGET_ASSET; ALTER TABLE NUGET_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM NUGET_COMPONENT_TAG; ALTER TABLE NUGET_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM NUGET_ASSET_BLOB; ALTER TABLE HELM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM HELM_CONTENT_REPOSITORY; ALTER TABLE HELM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM HELM_COMPONENT; ALTER TABLE HELM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM HELM_COMPONENT_TAG; ALTER TABLE HELM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM HELM_ASSET_BLOB; ALTER TABLE YUM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM YUM_BROWSE_NODE; ALTER TABLE DOCKER_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM DOCKER_CONTENT_REPOSITORY; ALTER TABLE DOCKER_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM DOCKER_COMPONENT; ALTER TABLE DOCKER_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM DOCKER_COMPONENT_TAG; ALTER TABLE DOCKER_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM DOCKER_ASSET_BLOB; ALTER TABLE RUBYGEMS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM RUBYGEMS_BROWSE_NODE; ALTER TABLE PYPI_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM PYPI_CONTENT_REPOSITORY; ALTER TABLE PYPI_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM PYPI_COMPONENT; ALTER TABLE PYPI_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM PYPI_COMPONENT_TAG; ALTER TABLE PYPI_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM PYPI_ASSET_BLOB; ALTER TABLE GITLFS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM GITLFS_BROWSE_NODE; ALTER TABLE YUM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM YUM_CONTENT_REPOSITORY; ALTER TABLE YUM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM YUM_COMPONENT; ALTER TABLE YUM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM YUM_COMPONENT_TAG; ALTER TABLE YUM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM YUM_ASSET_BLOB; ALTER TABLE DOCKER_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM DOCKER_BROWSE_NODE; ALTER TABLE NPM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM NPM_CONTENT_REPOSITORY; ALTER TABLE NPM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM NPM_COMPONENT; ALTER TABLE NPM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM NPM_COMPONENT_TAG; ALTER TABLE NPM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM NPM_ASSET_BLOB; ALTER TABLE RAW_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM RAW_BROWSE_NODE; ALTER TABLE APT_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM APT_CONTENT_REPOSITORY; ALTER TABLE APT_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM APT_COMPONENT; ALTER TABLE APT_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM APT_COMPONENT_TAG; ALTER TABLE APT_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM APT_ASSET_BLOB; ALTER TABLE MAVEN2_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM MAVEN2_BROWSE_NODE; ALTER TABLE CONDA_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM CONDA_CONTENT_REPOSITORY; ALTER TABLE CONDA_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM CONDA_COMPONENT; ALTER TABLE CONDA_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM CONDA_COMPONENT_TAG; ALTER TABLE CONDA_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM CONDA_ASSET_BLOB; ALTER TABLE PYPI_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM PYPI_BROWSE_NODE; ALTER TABLE CONAN_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM CONAN_CONTENT_REPOSITORY; ALTER TABLE CONAN_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM CONAN_COMPONENT; ALTER TABLE CONAN_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM CONAN_COMPONENT_TAG; ALTER TABLE CONAN_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM CONAN_ASSET_BLOB; ALTER TABLE CONDA_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM CONDA_BROWSE_NODE; ALTER TABLE R_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM R_CONTENT_REPOSITORY; ALTER TABLE R_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM R_COMPONENT; ALTER TABLE R_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM R_COMPONENT_TAG; ALTER TABLE R_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM R_ASSET_BLOB; ALTER TABLE CONAN_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM CONAN_BROWSE_NODE; ALTER TABLE GITLFS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM GITLFS_CONTENT_REPOSITORY; ALTER TABLE GITLFS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM GITLFS_COMPONENT; ALTER TABLE GITLFS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM GITLFS_COMPONENT_TAG; ALTER TABLE GITLFS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM GITLFS_ASSET_BLOB; ALTER TABLE HELM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM HELM_BROWSE_NODE; ALTER TABLE COCOAPODS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM COCOAPODS_CONTENT_REPOSITORY; ALTER TABLE COCOAPODS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM COCOAPODS_COMPONENT; ALTER TABLE COCOAPODS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM COCOAPODS_COMPONENT_TAG; ALTER TABLE COCOAPODS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM COCOAPODS_ASSET_BLOB; ALTER TABLE R_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM R_BROWSE_NODE; ALTER TABLE RUBYGEMS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM RUBYGEMS_CONTENT_REPOSITORY; ALTER TABLE RUBYGEMS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM RUBYGEMS_COMPONENT; ALTER TABLE RUBYGEMS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM RUBYGEMS_COMPONENT_TAG; ALTER TABLE RUBYGEMS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM RUBYGEMS_ASSET_BLOB; ALTER TABLE NUGET_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM NUGET_BROWSE_NODE; ALTER TABLE GO_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM GO_CONTENT_REPOSITORY; ALTER TABLE GO_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM GO_COMPONENT; ALTER TABLE GO_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM GO_COMPONENT_TAG; ALTER TABLE GO_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM GO_ASSET_BLOB; ALTER TABLE COCOAPODS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM COCOAPODS_BROWSE_NODE; ALTER TABLE P2_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM P2_CONTENT_REPOSITORY; ALTER TABLE P2_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM P2_COMPONENT; ALTER TABLE P2_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM P2_COMPONENT_TAG; ALTER TABLE P2_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM P2_ASSET_BLOB; ALTER TABLE GO_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM GO_BROWSE_NODE; ALTER TABLE MAVEN2_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM MAVEN2_COMPONENT; ALTER TABLE LOGGING_OVERRIDES ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM LOGGING_OVERRIDES; ALTER TABLE BLOB_STORE_METRICS ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM BLOB_STORE_METRICS; ALTER TABLE MAVEN2_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM MAVEN2_ASSET; ALTER TABLE APT_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM APT_KEY_VALUE; ALTER TABLE APT_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM APT_ASSET; ALTER TABLE RAW_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM RAW_ASSET; ALTER TABLE AGGREGATED_METRICS ALTER COLUMN AGGREGATED_METRIC_ID RESTART WITH SELECT max(AGGREGATED_METRIC_ID) + 1 FROM AGGREGATED_METRICS; ALTER TABLE HISTORICAL_LOGIN_INFO ALTER COLUMN HISTORICAL_LOGIN_INFO_ID RESTART WITH SELECT max(HISTORICAL_LOGIN_INFO_ID) + 1 FROM HISTORICAL_LOGIN_INFO; ALTER TABLE METRICS_LOG ALTER COLUMN METRIC_ID RESTART WITH SELECT max(METRIC_ID) + 1 FROM METRICS_LOG; ALTER TABLE NPM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM NPM_ASSET; ALTER TABLE NUGET_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM NUGET_COMPONENT; ALTER TABLE RUBYGEMS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM RUBYGEMS_ASSET; ALTER TABLE DOWNLOAD_COUNT ALTER COLUMN DOWNLOAD_COUNT_ID RESTART WITH SELECT max(DOWNLOAD_COUNT_ID) + 1 FROM DOWNLOAD_COUNT; ALTER TABLE DOCKER_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM DOCKER_ASSET; ALTER TABLE YUM_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM YUM_KEY_VALUE; ALTER TABLE YUM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM YUM_ASSET; ALTER TABLE COMPONENT_APPLICATION_SCAN_SCHEDULE ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM COMPONENT_APPLICATION_SCAN_SCHEDULE; ALTER TABLE COMPONENT_APPLICATION_SCAN ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM COMPONENT_APPLICATION_SCAN; ALTER TABLE HELM_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM HELM_KEY_VALUE; ALTER TABLE HELM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM HELM_ASSET; ALTER TABLE GITLFS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM GITLFS_ASSET; ALTER TABLE PYPI_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM PYPI_ASSET; ALTER TABLE CONDA_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM CONDA_ASSET; ALTER TABLE CONAN_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM CONAN_ASSET; ALTER TABLE R_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM R_ASSET; ALTER TABLE COCOAPODS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM COCOAPODS_ASSET; ALTER TABLE GO_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM GO_ASSET; ALTER TABLE P2_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM P2_ASSET; ALTER TABLE P2_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM P2_BROWSE_NODE;

how do you access the h2 console? I've searched for nexus h2 console and coming up empty

lalo-mx commented 3 months ago

how do you access the h2 console? I've searched for nexus h2 console and coming up empty

#!/bin/bash

NEXUS_BIN=/opt/nexus/nexus-3.71.0-06
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
java -cp "$NEXUS_BIN"/system/com/h2database/h2/*/h2-*.jar org.h2.tools.Shell -url jdbc:h2:"$NEXUS_DATA"/db/nexus

codingtim commented 3 months ago

I would prefer an actual solution from the maintainers of nexus instead of relying on a custom script. It has been 4 days since the issue was created but it remains silent on even acknowledging the migration tool creates this issue?

cthiemann commented 3 months ago

I ran the script above from https://github.com/cthiemann. The only output was > SCRIPT NODATAand unfortunately the error is still occurring.

Probably your NEXUS_DATA variable is not pointing to your nexus-data folder ( I had same symptoms when I tried)

my 'nexus.mv.db' file is located in /opt/sonatype-work/nexus3/db I've tried both of the following and get the error either way. Am i pointing to the wrong folder? Thanks!
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3/db

You seem to have an extra /nexus/ after /opt. This should work:

NEXUS_DATA=/opt/sonatype-work/nexus3

If you're still having problems, then make sure that this snippet lists some existing jar file (if not, you will get "class not found" errors):

NEXUS_BIN=/opt/nexus/nexus-3.71.0-06
ls -l "$NEXUS_BIN"/system/com/h2database/h2/*/h2-*.jar

If it's still not working, you can replace the whole second part of the script (schema=...) with this line:

h2sql "SCRIPT NODATA"

That should output the whole database schema -- or any errors that might help finding the problem.

moddx commented 3 months ago

I would prefer an actual solution from the maintainers of nexus instead of relying on a custom script. It has been 4 days since the issue was created but it remains silent on even acknowledging the migration tool creates this issue?

I would like that too, but guess that's where the paid plan with enterprise support comes into play.

cthiemann commented 3 months ago

I would prefer an actual solution from the maintainers of nexus instead of relying on a custom script. It has been 4 days since the issue was created but it remains silent on even acknowledging the migration tool creates this issue?

Agreed. We've been using Nexus without issues so far after applying my script (which I was only able to write because @dioss-Machiel very helpfully provided the solution/workaround in the OP -- thanks for that!). However, it would be good to have this workaround "blessed" by someone who knows Nexus/H2 better than me ;-)

goodale commented 3 months ago

I ran the script above from https://github.com/cthiemann. The only output was > SCRIPT NODATAand unfortunately the error is still occurring.

Probably your NEXUS_DATA variable is not pointing to your nexus-data folder ( I had same symptoms when I tried)

my 'nexus.mv.db' file is located in /opt/sonatype-work/nexus3/db I've tried both of the following and get the error either way. Am i pointing to the wrong folder? Thanks!
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3/db
You seem to have an extra /nexus/ after /opt. This should work:
NEXUS_DATA=/opt/sonatype-work/nexus3
If you're still having problems, then make sure that this snippet lists some existing jar file (if not, you will get "class not found" errors):
NEXUS_BIN=/opt/nexus/nexus-3.71.0-06
ls -l "$NEXUS_BIN"/system/com/h2database/h2/*/h2-*.jar
If it's still not working, you can replace the whole second part of the script (schema=...) with this line:
h2sql "SCRIPT NODATA"
That should output the whole database schema -- or any errors that might help finding the problem.

that was it. I had that extra 'nexus' on the NEXUS_DATA variable. Thanks - you ROCK!

ShivanethGithub commented 3 months ago

I would prefer an actual solution from the maintainers of nexus instead of relying on a custom script. It has been 4 days since the issue was created but it remains silent on even acknowledging the migration tool creates this issue?

Agreed. We've been using Nexus without issues so far after applying my script (which I was only able to write because @dioss-Machiel very helpfully provided the solution/workaround in the OP -- thanks for that!). However, it would be good to have this workaround "blessed" by someone who knows Nexus/H2 better than me ;-)

Same here ... been using Nexus for a while, on auto upgrade ... our weekly upgrade failed thanks to the breaking changes (in a minor version .... just saying ...). The script allowed us to upgrade manually to 3.71 but we would appreciate an answer from sonatype

nesc58 commented 3 months ago

For me the script worked. No actual issues but the performance is really bad. I have a group with 7 hosted repos and 1 proxy repository. The performance drops from ~20 seconds to ~50 seconds using 3.71.0 running the migration scripts and the database sequence fix. Does anyone have expierence like that after upgrading and running the scripts?

murainwood commented 3 months ago

Pls run the following sql in h2 console (could via web console) to fix the DB issue, regardless of whether you are using Linux, Windows, or macOS. ALTER TABLE TAG ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM TAG; ALTER TABLE RAW_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM RAW_CONTENT_REPOSITORY; ALTER TABLE RAW_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM RAW_COMPONENT; ALTER TABLE RAW_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM RAW_COMPONENT_TAG; ALTER TABLE RAW_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM RAW_ASSET_BLOB; ALTER TABLE APT_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM APT_BROWSE_NODE; ALTER TABLE MAVEN2_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM MAVEN2_CONTENT_REPOSITORY; ALTER TABLE UPGRADE_TASKS ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM UPGRADE_TASKS; ALTER TABLE SOFT_DELETED_BLOBS ALTER COLUMN RECORD_ID RESTART WITH SELECT max(RECORD_ID) + 1 FROM SOFT_DELETED_BLOBS; ALTER TABLE MAVEN2_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM MAVEN2_COMPONENT_TAG; ALTER TABLE MAVEN2_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM MAVEN2_ASSET_BLOB; ALTER TABLE NUGET_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM NUGET_CONTENT_REPOSITORY; ALTER TABLE NPM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM NPM_BROWSE_NODE; ALTER TABLE NUGET_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM NUGET_ASSET; ALTER TABLE NUGET_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM NUGET_COMPONENT_TAG; ALTER TABLE NUGET_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM NUGET_ASSET_BLOB; ALTER TABLE HELM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM HELM_CONTENT_REPOSITORY; ALTER TABLE HELM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM HELM_COMPONENT; ALTER TABLE HELM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM HELM_COMPONENT_TAG; ALTER TABLE HELM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM HELM_ASSET_BLOB; ALTER TABLE YUM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM YUM_BROWSE_NODE; ALTER TABLE DOCKER_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM DOCKER_CONTENT_REPOSITORY; ALTER TABLE DOCKER_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM DOCKER_COMPONENT; ALTER TABLE DOCKER_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM DOCKER_COMPONENT_TAG; ALTER TABLE DOCKER_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM DOCKER_ASSET_BLOB; ALTER TABLE RUBYGEMS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM RUBYGEMS_BROWSE_NODE; ALTER TABLE PYPI_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM PYPI_CONTENT_REPOSITORY; ALTER TABLE PYPI_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM PYPI_COMPONENT; ALTER TABLE PYPI_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM PYPI_COMPONENT_TAG; ALTER TABLE PYPI_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM PYPI_ASSET_BLOB; ALTER TABLE GITLFS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM GITLFS_BROWSE_NODE; ALTER TABLE YUM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM YUM_CONTENT_REPOSITORY; ALTER TABLE YUM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM YUM_COMPONENT; ALTER TABLE YUM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM YUM_COMPONENT_TAG; ALTER TABLE YUM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM YUM_ASSET_BLOB; ALTER TABLE DOCKER_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM DOCKER_BROWSE_NODE; ALTER TABLE NPM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM NPM_CONTENT_REPOSITORY; ALTER TABLE NPM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM NPM_COMPONENT; ALTER TABLE NPM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM NPM_COMPONENT_TAG; ALTER TABLE NPM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM NPM_ASSET_BLOB; ALTER TABLE RAW_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM RAW_BROWSE_NODE; ALTER TABLE APT_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM APT_CONTENT_REPOSITORY; ALTER TABLE APT_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM APT_COMPONENT; ALTER TABLE APT_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM APT_COMPONENT_TAG; ALTER TABLE APT_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM APT_ASSET_BLOB; ALTER TABLE MAVEN2_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM MAVEN2_BROWSE_NODE; ALTER TABLE CONDA_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM CONDA_CONTENT_REPOSITORY; ALTER TABLE CONDA_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM CONDA_COMPONENT; ALTER TABLE CONDA_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM CONDA_COMPONENT_TAG; ALTER TABLE CONDA_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM CONDA_ASSET_BLOB; ALTER TABLE PYPI_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM PYPI_BROWSE_NODE; ALTER TABLE CONAN_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM CONAN_CONTENT_REPOSITORY; ALTER TABLE CONAN_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM CONAN_COMPONENT; ALTER TABLE CONAN_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM CONAN_COMPONENT_TAG; ALTER TABLE CONAN_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM CONAN_ASSET_BLOB; ALTER TABLE CONDA_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM CONDA_BROWSE_NODE; ALTER TABLE R_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM R_CONTENT_REPOSITORY; ALTER TABLE R_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM R_COMPONENT; ALTER TABLE R_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM R_COMPONENT_TAG; ALTER TABLE R_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM R_ASSET_BLOB; ALTER TABLE CONAN_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM CONAN_BROWSE_NODE; ALTER TABLE GITLFS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM GITLFS_CONTENT_REPOSITORY; ALTER TABLE GITLFS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM GITLFS_COMPONENT; ALTER TABLE GITLFS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM GITLFS_COMPONENT_TAG; ALTER TABLE GITLFS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM GITLFS_ASSET_BLOB; ALTER TABLE HELM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM HELM_BROWSE_NODE; ALTER TABLE COCOAPODS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM COCOAPODS_CONTENT_REPOSITORY; ALTER TABLE COCOAPODS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM COCOAPODS_COMPONENT; ALTER TABLE COCOAPODS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM COCOAPODS_COMPONENT_TAG; ALTER TABLE COCOAPODS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM COCOAPODS_ASSET_BLOB; ALTER TABLE R_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM R_BROWSE_NODE; ALTER TABLE RUBYGEMS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM RUBYGEMS_CONTENT_REPOSITORY; ALTER TABLE RUBYGEMS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM RUBYGEMS_COMPONENT; ALTER TABLE RUBYGEMS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM RUBYGEMS_COMPONENT_TAG; ALTER TABLE RUBYGEMS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM RUBYGEMS_ASSET_BLOB; ALTER TABLE NUGET_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM NUGET_BROWSE_NODE; ALTER TABLE GO_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM GO_CONTENT_REPOSITORY; ALTER TABLE GO_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM GO_COMPONENT; ALTER TABLE GO_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM GO_COMPONENT_TAG; ALTER TABLE GO_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM GO_ASSET_BLOB; ALTER TABLE COCOAPODS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM COCOAPODS_BROWSE_NODE; ALTER TABLE P2_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM P2_CONTENT_REPOSITORY; ALTER TABLE P2_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM P2_COMPONENT; ALTER TABLE P2_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM P2_COMPONENT_TAG; ALTER TABLE P2_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM P2_ASSET_BLOB; ALTER TABLE GO_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM GO_BROWSE_NODE; ALTER TABLE MAVEN2_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM MAVEN2_COMPONENT; ALTER TABLE LOGGING_OVERRIDES ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM LOGGING_OVERRIDES; ALTER TABLE BLOB_STORE_METRICS ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM BLOB_STORE_METRICS; ALTER TABLE MAVEN2_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM MAVEN2_ASSET; ALTER TABLE APT_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM APT_KEY_VALUE; ALTER TABLE APT_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM APT_ASSET; ALTER TABLE RAW_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM RAW_ASSET; ALTER TABLE AGGREGATED_METRICS ALTER COLUMN AGGREGATED_METRIC_ID RESTART WITH SELECT max(AGGREGATED_METRIC_ID) + 1 FROM AGGREGATED_METRICS; ALTER TABLE HISTORICAL_LOGIN_INFO ALTER COLUMN HISTORICAL_LOGIN_INFO_ID RESTART WITH SELECT max(HISTORICAL_LOGIN_INFO_ID) + 1 FROM HISTORICAL_LOGIN_INFO; ALTER TABLE METRICS_LOG ALTER COLUMN METRIC_ID RESTART WITH SELECT max(METRIC_ID) + 1 FROM METRICS_LOG; ALTER TABLE NPM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM NPM_ASSET; ALTER TABLE NUGET_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM NUGET_COMPONENT; ALTER TABLE RUBYGEMS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM RUBYGEMS_ASSET; ALTER TABLE DOWNLOAD_COUNT ALTER COLUMN DOWNLOAD_COUNT_ID RESTART WITH SELECT max(DOWNLOAD_COUNT_ID) + 1 FROM DOWNLOAD_COUNT; ALTER TABLE DOCKER_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM DOCKER_ASSET; ALTER TABLE YUM_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM YUM_KEY_VALUE; ALTER TABLE YUM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM YUM_ASSET; ALTER TABLE COMPONENT_APPLICATION_SCAN_SCHEDULE ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM COMPONENT_APPLICATION_SCAN_SCHEDULE; ALTER TABLE COMPONENT_APPLICATION_SCAN ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM COMPONENT_APPLICATION_SCAN; ALTER TABLE HELM_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM HELM_KEY_VALUE; ALTER TABLE HELM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM HELM_ASSET; ALTER TABLE GITLFS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM GITLFS_ASSET; ALTER TABLE PYPI_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM PYPI_ASSET; ALTER TABLE CONDA_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM CONDA_ASSET; ALTER TABLE CONAN_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM CONAN_ASSET; ALTER TABLE R_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM R_ASSET; ALTER TABLE COCOAPODS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM COCOAPODS_ASSET; ALTER TABLE GO_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM GO_ASSET; ALTER TABLE Pou2_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM P2_ASSET; ALTER TABLE P2_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM P2_BROWSE_NODE;↳

how do you access the h2 console? I've searched for nexus h2 console and coming up empty↳

shutdown nexus3 instance
enter into location "sonatype-work/nexus3/etc", you could get the file "nexus.properties"
edit the file and add tow lines nexus.h2.httpListenerEnabled=true nexus.h2.httpListenerPort=1234
restart the nexus3
enter the url in browser "http://XXXXX:1234"

Notice: the jdbc url is : jdbc:h2:file:nexus leave the username & password empty

enter into console

copy the SQL script and execute

murainwood commented 2 months ago

I would prefer an actual solution from the maintainers of nexus instead of relying on a custom script. It has been 4 days since the issue was created but it remains silent on even acknowledging the migration tool creates this issue?↳

The development team clearly missed the final regression test. The H2 script generated by the migration tool should reset the auto-increment starting value of the primary key for each table （if the table had）, rather than simply starting from the default value of 1

goodale commented 2 months ago

Pls run the following sql in h2 console (could via web console) to fix the DB issue, regardless of whether you are using Linux, Windows, or macOS. ALTER TABLE TAG ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM TAG; ALTER TABLE RAW_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM RAW_CONTENT_REPOSITORY; ALTER TABLE RAW_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM RAW_COMPONENT; ALTER TABLE RAW_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM RAW_COMPONENT_TAG; ALTER TABLE RAW_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM RAW_ASSET_BLOB; ALTER TABLE APT_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM APT_BROWSE_NODE; ALTER TABLE MAVEN2_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM MAVEN2_CONTENT_REPOSITORY; ALTER TABLE UPGRADE_TASKS ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM UPGRADE_TASKS; ALTER TABLE SOFT_DELETED_BLOBS ALTER COLUMN RECORD_ID RESTART WITH SELECT max(RECORD_ID) + 1 FROM SOFT_DELETED_BLOBS; ALTER TABLE MAVEN2_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM MAVEN2_COMPONENT_TAG; ALTER TABLE MAVEN2_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM MAVEN2_ASSET_BLOB; ALTER TABLE NUGET_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM NUGET_CONTENT_REPOSITORY; ALTER TABLE NPM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM NPM_BROWSE_NODE; ALTER TABLE NUGET_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM NUGET_ASSET; ALTER TABLE NUGET_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM NUGET_COMPONENT_TAG; ALTER TABLE NUGET_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM NUGET_ASSET_BLOB; ALTER TABLE HELM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM HELM_CONTENT_REPOSITORY; ALTER TABLE HELM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM HELM_COMPONENT; ALTER TABLE HELM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM HELM_COMPONENT_TAG; ALTER TABLE HELM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM HELM_ASSET_BLOB; ALTER TABLE YUM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM YUM_BROWSE_NODE; ALTER TABLE DOCKER_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM DOCKER_CONTENT_REPOSITORY; ALTER TABLE DOCKER_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM DOCKER_COMPONENT; ALTER TABLE DOCKER_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM DOCKER_COMPONENT_TAG; ALTER TABLE DOCKER_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM DOCKER_ASSET_BLOB; ALTER TABLE RUBYGEMS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM RUBYGEMS_BROWSE_NODE; ALTER TABLE PYPI_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM PYPI_CONTENT_REPOSITORY; ALTER TABLE PYPI_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM PYPI_COMPONENT; ALTER TABLE PYPI_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM PYPI_COMPONENT_TAG; ALTER TABLE PYPI_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM PYPI_ASSET_BLOB; ALTER TABLE GITLFS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM GITLFS_BROWSE_NODE; ALTER TABLE YUM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM YUM_CONTENT_REPOSITORY; ALTER TABLE YUM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM YUM_COMPONENT; ALTER TABLE YUM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM YUM_COMPONENT_TAG; ALTER TABLE YUM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM YUM_ASSET_BLOB; ALTER TABLE DOCKER_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM DOCKER_BROWSE_NODE; ALTER TABLE NPM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM NPM_CONTENT_REPOSITORY; ALTER TABLE NPM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM NPM_COMPONENT; ALTER TABLE NPM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM NPM_COMPONENT_TAG; ALTER TABLE NPM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM NPM_ASSET_BLOB; ALTER TABLE RAW_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM RAW_BROWSE_NODE; ALTER TABLE APT_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM APT_CONTENT_REPOSITORY; ALTER TABLE APT_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM APT_COMPONENT; ALTER TABLE APT_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM APT_COMPONENT_TAG; ALTER TABLE APT_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM APT_ASSET_BLOB; ALTER TABLE MAVEN2_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM MAVEN2_BROWSE_NODE; ALTER TABLE CONDA_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM CONDA_CONTENT_REPOSITORY; ALTER TABLE CONDA_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM CONDA_COMPONENT; ALTER TABLE CONDA_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM CONDA_COMPONENT_TAG; ALTER TABLE CONDA_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM CONDA_ASSET_BLOB; ALTER TABLE PYPI_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM PYPI_BROWSE_NODE; ALTER TABLE CONAN_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM CONAN_CONTENT_REPOSITORY; ALTER TABLE CONAN_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM CONAN_COMPONENT; ALTER TABLE CONAN_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM CONAN_COMPONENT_TAG; ALTER TABLE CONAN_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM CONAN_ASSET_BLOB; ALTER TABLE CONDA_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM CONDA_BROWSE_NODE; ALTER TABLE R_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM R_CONTENT_REPOSITORY; ALTER TABLE R_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM R_COMPONENT; ALTER TABLE R_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM R_COMPONENT_TAG; ALTER TABLE R_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM R_ASSET_BLOB; ALTER TABLE CONAN_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM CONAN_BROWSE_NODE; ALTER TABLE GITLFS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM GITLFS_CONTENT_REPOSITORY; ALTER TABLE GITLFS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM GITLFS_COMPONENT; ALTER TABLE GITLFS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM GITLFS_COMPONENT_TAG; ALTER TABLE GITLFS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM GITLFS_ASSET_BLOB; ALTER TABLE HELM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM HELM_BROWSE_NODE; ALTER TABLE COCOAPODS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM COCOAPODS_CONTENT_REPOSITORY; ALTER TABLE COCOAPODS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM COCOAPODS_COMPONENT; ALTER TABLE COCOAPODS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM COCOAPODS_COMPONENT_TAG; ALTER TABLE COCOAPODS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM COCOAPODS_ASSET_BLOB; ALTER TABLE R_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM R_BROWSE_NODE; ALTER TABLE RUBYGEMS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM RUBYGEMS_CONTENT_REPOSITORY; ALTER TABLE RUBYGEMS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM RUBYGEMS_COMPONENT; ALTER TABLE RUBYGEMS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM RUBYGEMS_COMPONENT_TAG; ALTER TABLE RUBYGEMS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM RUBYGEMS_ASSET_BLOB; ALTER TABLE NUGET_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM NUGET_BROWSE_NODE; ALTER TABLE GO_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM GO_CONTENT_REPOSITORY; ALTER TABLE GO_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM GO_COMPONENT; ALTER TABLE GO_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM GO_COMPONENT_TAG; ALTER TABLE GO_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM GO_ASSET_BLOB; ALTER TABLE COCOAPODS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM COCOAPODS_BROWSE_NODE; ALTER TABLE P2_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM P2_CONTENT_REPOSITORY; ALTER TABLE P2_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM P2_COMPONENT; ALTER TABLE P2_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM P2_COMPONENT_TAG; ALTER TABLE P2_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM P2_ASSET_BLOB; ALTER TABLE GO_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM GO_BROWSE_NODE; ALTER TABLE MAVEN2_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM MAVEN2_COMPONENT; ALTER TABLE LOGGING_OVERRIDES ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM LOGGING_OVERRIDES; ALTER TABLE BLOB_STORE_METRICS ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM BLOB_STORE_METRICS; ALTER TABLE MAVEN2_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM MAVEN2_ASSET; ALTER TABLE APT_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM APT_KEY_VALUE; ALTER TABLE APT_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM APT_ASSET; ALTER TABLE RAW_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM RAW_ASSET; ALTER TABLE AGGREGATED_METRICS ALTER COLUMN AGGREGATED_METRIC_ID RESTART WITH SELECT max(AGGREGATED_METRIC_ID) + 1 FROM AGGREGATED_METRICS; ALTER TABLE HISTORICAL_LOGIN_INFO ALTER COLUMN HISTORICAL_LOGIN_INFO_ID RESTART WITH SELECT max(HISTORICAL_LOGIN_INFO_ID) + 1 FROM HISTORICAL_LOGIN_INFO; ALTER TABLE METRICS_LOG ALTER COLUMN METRIC_ID RESTART WITH SELECT max(METRIC_ID) + 1 FROM METRICS_LOG; ALTER TABLE NPM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM NPM_ASSET; ALTER TABLE NUGET_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM NUGET_COMPONENT; ALTER TABLE RUBYGEMS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM RUBYGEMS_ASSET; ALTER TABLE DOWNLOAD_COUNT ALTER COLUMN DOWNLOAD_COUNT_ID RESTART WITH SELECT max(DOWNLOAD_COUNT_ID) + 1 FROM DOWNLOAD_COUNT; ALTER TABLE DOCKER_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM DOCKER_ASSET; ALTER TABLE YUM_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM YUM_KEY_VALUE; ALTER TABLE YUM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM YUM_ASSET; ALTER TABLE COMPONENT_APPLICATION_SCAN_SCHEDULE ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM COMPONENT_APPLICATION_SCAN_SCHEDULE; ALTER TABLE COMPONENT_APPLICATION_SCAN ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM COMPONENT_APPLICATION_SCAN; ALTER TABLE HELM_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM HELM_KEY_VALUE; ALTER TABLE HELM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM HELM_ASSET; ALTER TABLE GITLFS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM GITLFS_ASSET; ALTER TABLE PYPI_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM PYPI_ASSET; ALTER TABLE CONDA_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM CONDA_ASSET; ALTER TABLE CONAN_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM CONAN_ASSET; ALTER TABLE R_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM R_ASSET; ALTER TABLE COCOAPODS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM COCOAPODS_ASSET; ALTER TABLE GO_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM GO_ASSET; ALTER TABLE Pou2_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM P2_ASSET; ALTER TABLE P2_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM P2_BROWSE_NODE;↳

how do you access the h2 console? I've searched for nexus h2 console and coming up empty↳

shutdown nexus3 instance

enter into location "sonatype-work/nexus3/etc", you could get the file "nexus.properties"

edit the file and add tow lines nexus.h2.httpListenerEnabled=true nexus.h2.httpListenerPort=1234

restart the nexus3

enter the url in browser "http://XXXXX:1234"

Notice: the jdbc url is : jdbc:h2:file:nexus leave the username & password empty

enter into console

copy the SQL script and execute

https://github.com/murainwood thank you!

murainwood commented 2 months ago

Pls run the following sql in h2 console (could via web console) to fix the DB issue, regardless of whether you are using Linux, Windows, or macOS. ALTER TABLE TAG ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM TAG; ALTER TABLE RAW_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM RAW_CONTENT_REPOSITORY; ALTER TABLE RAW_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM RAW_COMPONENT; ALTER TABLE RAW_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM RAW_COMPONENT_TAG; ALTER TABLE RAW_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM RAW_ASSET_BLOB; ALTER TABLE APT_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM APT_BROWSE_NODE; ALTER TABLE MAVEN2_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM MAVEN2_CONTENT_REPOSITORY; ALTER TABLE UPGRADE_TASKS ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM UPGRADE_TASKS; ALTER TABLE SOFT_DELETED_BLOBS ALTER COLUMN RECORD_ID RESTART WITH SELECT max(RECORD_ID) + 1 FROM SOFT_DELETED_BLOBS; ALTER TABLE MAVEN2_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM MAVEN2_COMPONENT_TAG; ALTER TABLE MAVEN2_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM MAVEN2_ASSET_BLOB; ALTER TABLE NUGET_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM NUGET_CONTENT_REPOSITORY; ALTER TABLE NPM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM NPM_BROWSE_NODE; ALTER TABLE NUGET_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM NUGET_ASSET; ALTER TABLE NUGET_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM NUGET_COMPONENT_TAG; ALTER TABLE NUGET_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM NUGET_ASSET_BLOB; ALTER TABLE HELM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM HELM_CONTENT_REPOSITORY; ALTER TABLE HELM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM HELM_COMPONENT; ALTER TABLE HELM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM HELM_COMPONENT_TAG; ALTER TABLE HELM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM HELM_ASSET_BLOB; ALTER TABLE YUM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM YUM_BROWSE_NODE; ALTER TABLE DOCKER_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM DOCKER_CONTENT_REPOSITORY; ALTER TABLE DOCKER_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM DOCKER_COMPONENT; ALTER TABLE DOCKER_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM DOCKER_COMPONENT_TAG; ALTER TABLE DOCKER_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM DOCKER_ASSET_BLOB; ALTER TABLE RUBYGEMS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM RUBYGEMS_BROWSE_NODE; ALTER TABLE PYPI_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM PYPI_CONTENT_REPOSITORY; ALTER TABLE PYPI_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM PYPI_COMPONENT; ALTER TABLE PYPI_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM PYPI_COMPONENT_TAG; ALTER TABLE PYPI_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM PYPI_ASSET_BLOB; ALTER TABLE GITLFS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM GITLFS_BROWSE_NODE; ALTER TABLE YUM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM YUM_CONTENT_REPOSITORY; ALTER TABLE YUM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM YUM_COMPONENT; ALTER TABLE YUM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM YUM_COMPONENT_TAG; ALTER TABLE YUM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM YUM_ASSET_BLOB; ALTER TABLE DOCKER_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM DOCKER_BROWSE_NODE; ALTER TABLE NPM_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM NPM_CONTENT_REPOSITORY; ALTER TABLE NPM_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM NPM_COMPONENT; ALTER TABLE NPM_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM NPM_COMPONENT_TAG; ALTER TABLE NPM_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM NPM_ASSET_BLOB; ALTER TABLE RAW_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM RAW_BROWSE_NODE; ALTER TABLE APT_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM APT_CONTENT_REPOSITORY; ALTER TABLE APT_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM APT_COMPONENT; ALTER TABLE APT_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM APT_COMPONENT_TAG; ALTER TABLE APT_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM APT_ASSET_BLOB; ALTER TABLE MAVEN2_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM MAVEN2_BROWSE_NODE; ALTER TABLE CONDA_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM CONDA_CONTENT_REPOSITORY; ALTER TABLE CONDA_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM CONDA_COMPONENT; ALTER TABLE CONDA_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM CONDA_COMPONENT_TAG; ALTER TABLE CONDA_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM CONDA_ASSET_BLOB; ALTER TABLE PYPI_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM PYPI_BROWSE_NODE; ALTER TABLE CONAN_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM CONAN_CONTENT_REPOSITORY; ALTER TABLE CONAN_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM CONAN_COMPONENT; ALTER TABLE CONAN_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM CONAN_COMPONENT_TAG; ALTER TABLE CONAN_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM CONAN_ASSET_BLOB; ALTER TABLE CONDA_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM CONDA_BROWSE_NODE; ALTER TABLE R_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM R_CONTENT_REPOSITORY; ALTER TABLE R_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM R_COMPONENT; ALTER TABLE R_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM R_COMPONENT_TAG; ALTER TABLE R_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM R_ASSET_BLOB; ALTER TABLE CONAN_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM CONAN_BROWSE_NODE; ALTER TABLE GITLFS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM GITLFS_CONTENT_REPOSITORY; ALTER TABLE GITLFS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM GITLFS_COMPONENT; ALTER TABLE GITLFS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM GITLFS_COMPONENT_TAG; ALTER TABLE GITLFS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM GITLFS_ASSET_BLOB; ALTER TABLE HELM_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM HELM_BROWSE_NODE; ALTER TABLE COCOAPODS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM COCOAPODS_CONTENT_REPOSITORY; ALTER TABLE COCOAPODS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM COCOAPODS_COMPONENT; ALTER TABLE COCOAPODS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM COCOAPODS_COMPONENT_TAG; ALTER TABLE COCOAPODS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM COCOAPODS_ASSET_BLOB; ALTER TABLE R_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM R_BROWSE_NODE; ALTER TABLE RUBYGEMS_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM RUBYGEMS_CONTENT_REPOSITORY; ALTER TABLE RUBYGEMS_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM RUBYGEMS_COMPONENT; ALTER TABLE RUBYGEMS_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM RUBYGEMS_COMPONENT_TAG; ALTER TABLE RUBYGEMS_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM RUBYGEMS_ASSET_BLOB; ALTER TABLE NUGET_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM NUGET_BROWSE_NODE; ALTER TABLE GO_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM GO_CONTENT_REPOSITORY; ALTER TABLE GO_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM GO_COMPONENT; ALTER TABLE GO_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM GO_COMPONENT_TAG; ALTER TABLE GO_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM GO_ASSET_BLOB; ALTER TABLE COCOAPODS_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM COCOAPODS_BROWSE_NODE; ALTER TABLE P2_CONTENT_REPOSITORY ALTER COLUMN REPOSITORY_ID RESTART WITH SELECT max(REPOSITORY_ID) + 1 FROM P2_CONTENT_REPOSITORY; ALTER TABLE P2_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM P2_COMPONENT; ALTER TABLE P2_COMPONENT_TAG ALTER COLUMN COMPONENT_TAG_ID RESTART WITH SELECT max(COMPONENT_TAG_ID) + 1 FROM P2_COMPONENT_TAG; ALTER TABLE P2_ASSET_BLOB ALTER COLUMN ASSET_BLOB_ID RESTART WITH SELECT max(ASSET_BLOB_ID) + 1 FROM P2_ASSET_BLOB; ALTER TABLE GO_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM GO_BROWSE_NODE; ALTER TABLE MAVEN2_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM MAVEN2_COMPONENT; ALTER TABLE LOGGING_OVERRIDES ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM LOGGING_OVERRIDES; ALTER TABLE BLOB_STORE_METRICS ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM BLOB_STORE_METRICS; ALTER TABLE MAVEN2_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM MAVEN2_ASSET; ALTER TABLE APT_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM APT_KEY_VALUE; ALTER TABLE APT_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM APT_ASSET; ALTER TABLE RAW_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM RAW_ASSET; ALTER TABLE AGGREGATED_METRICS ALTER COLUMN AGGREGATED_METRIC_ID RESTART WITH SELECT max(AGGREGATED_METRIC_ID) + 1 FROM AGGREGATED_METRICS; ALTER TABLE HISTORICAL_LOGIN_INFO ALTER COLUMN HISTORICAL_LOGIN_INFO_ID RESTART WITH SELECT max(HISTORICAL_LOGIN_INFO_ID) + 1 FROM HISTORICAL_LOGIN_INFO; ALTER TABLE METRICS_LOG ALTER COLUMN METRIC_ID RESTART WITH SELECT max(METRIC_ID) + 1 FROM METRICS_LOG; ALTER TABLE NPM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM NPM_ASSET; ALTER TABLE NUGET_COMPONENT ALTER COLUMN COMPONENT_ID RESTART WITH SELECT max(COMPONENT_ID) + 1 FROM NUGET_COMPONENT; ALTER TABLE RUBYGEMS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM RUBYGEMS_ASSET; ALTER TABLE DOWNLOAD_COUNT ALTER COLUMN DOWNLOAD_COUNT_ID RESTART WITH SELECT max(DOWNLOAD_COUNT_ID) + 1 FROM DOWNLOAD_COUNT; ALTER TABLE DOCKER_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM DOCKER_ASSET; ALTER TABLE YUM_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM YUM_KEY_VALUE; ALTER TABLE YUM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM YUM_ASSET; ALTER TABLE COMPONENT_APPLICATION_SCAN_SCHEDULE ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM COMPONENT_APPLICATION_SCAN_SCHEDULE; ALTER TABLE COMPONENT_APPLICATION_SCAN ALTER COLUMN ID RESTART WITH SELECT max(ID) + 1 FROM COMPONENT_APPLICATION_SCAN; ALTER TABLE HELM_KEY_VALUE ALTER COLUMN KEY_VALUE_ID RESTART WITH SELECT max(KEY_VALUE_ID) + 1 FROM HELM_KEY_VALUE; ALTER TABLE HELM_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM HELM_ASSET; ALTER TABLE GITLFS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM GITLFS_ASSET; ALTER TABLE PYPI_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM PYPI_ASSET; ALTER TABLE CONDA_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM CONDA_ASSET; ALTER TABLE CONAN_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM CONAN_ASSET; ALTER TABLE R_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM R_ASSET; ALTER TABLE COCOAPODS_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM COCOAPODS_ASSET; ALTER TABLE GO_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM GO_ASSET; ALTER TABLE Pou2_ASSET ALTER COLUMN ASSET_ID RESTART WITH SELECT max(ASSET_ID) + 1 FROM P2_ASSET; ALTER TABLE P2_BROWSE_NODE ALTER COLUMN NODE_ID RESTART WITH SELECT max(NODE_ID) + 1 FROM P2_BROWSE_NODE;↳

how do you access the h2 console? I've searched for nexus h2 console and coming up empty↳

shutdown nexus3 instance

enter into location "sonatype-work/nexus3/etc", you could get the file "nexus.properties"

edit the file and add tow lines nexus.h2.httpListenerEnabled=true nexus.h2.httpListenerPort=1234

restart the nexus3

enter the url in browser "http://XXXXX:1234"

Notice: the jdbc url is : jdbc:h2:file:nexus leave the username & password empty

enter into console

copy the SQL script and execute

https://github.com/murainwood thank you!

That's ok~~ Actually, nexus team forgot to reset the auto-increment starting value of the primary key for related tables, in migration tool logic. Manual execution of SQL statements is applicable to any operating systems (especially for Windows Server)

nicolasprnt commented 2 months ago

I have used the following script to reset all the auto-increment counters (it alters some 100 columns and takes about a minute). Nexus must not be running when executing the script.

#!/bin/bash

NEXUS_BIN=/opt/nexus/nexus-3.71.0-06
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
h2sql() {
    echo "> $@" >&2
    java -cp "$NEXUS_BIN"/system/com/h2database/h2/*/h2-*.jar org.h2.tools.Shell -url jdbc:h2:"$NEXUS_DATA"/db/nexus -sql "$@"
}

schema=$(h2sql "SCRIPT NODATA")
echo "$schema" | while read line; do
    if [[ $line =~ ^CREATE\ CACHED\ TABLE\ \"PUBLIC\"\.\"([^\"]+)\" ]]; then
        tbl="${BASH_REMATCH[1]}"
    elif [[ $line =~ ^\"([^\"]+)\"\ .*\ GENERATED\ BY\ DEFAULT ]]; then
        col="${BASH_REMATCH[1]}"
        h2sql "ALTER TABLE $tbl ALTER COLUMN $col RESTART WITH SELECT max($col) + 1 FROM $tbl"
    fi
done

Thank you so much for your message! We spent two hours troubleshooting this error before finding your script. Our Nexus is now up and running!

Note: We initially used sh instead of bash to launch the script, which caused issues. Hopefully, this helps others avoid the same mistake.

Thanks again!

dmpe commented 2 months ago

3. h2.httpListenerEnabled

Thanks. great idea but unfortunately wont work in Nexus Helm Chart setup, because h2 DB/Nexus app is binding port such as 1234 only to the pod's localhost and not 0.0.0.0. This means that H2 Console cannot be accessed externally, using Nexus pod running inside kubernetes. It return 404 not found. However curl -v http://localhost:1234 works inside the container.

ashleyw-gh commented 2 months ago

I have used the following script to reset all the auto-increment counters (it alters some 100 columns and takes about a minute). Nexus must not be running when executing the script.

#!/bin/bash

NEXUS_BIN=/opt/nexus/nexus-3.71.0-06
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
h2sql() {
    echo "> $@" >&2
    java -cp "$NEXUS_BIN"/system/com/h2database/h2/*/h2-*.jar org.h2.tools.Shell -url jdbc:h2:"$NEXUS_DATA"/db/nexus -sql "$@"
}

schema=$(h2sql "SCRIPT NODATA")
echo "$schema" | while read line; do
    if [[ $line =~ ^CREATE\ CACHED\ TABLE\ \"PUBLIC\"\.\"([^\"]+)\" ]]; then
        tbl="${BASH_REMATCH[1]}"
    elif [[ $line =~ ^\"([^\"]+)\"\ .*\ GENERATED\ BY\ DEFAULT ]]; then
        col="${BASH_REMATCH[1]}"
        h2sql "ALTER TABLE $tbl ALTER COLUMN $col RESTART WITH SELECT max($col) + 1 FROM $tbl"
    fi
done

thanks for this. We are running Nexus OSS in docker container. I'm not familiar with H2 but I got this message when I was trying to debug - any ideas? (we were getting the same 500 errors in the logs when trying to upload after an OrientDB to H2 migration). In the mean time I've reverted back to OrientDB on 3.70.1-02, but am busy setting up a test environment. I agree completely that Sonatype need to fix the upgrade process as many OSS users will be forcibly switching to H2 at the moment.

#  docker exec -it nexus /bin/bash
$ NEXUS_BIN=/opt/sonatype/nexus
$ NEXUS_DATA=/nexus-data
$ /opt/sonatype/nexus/bin/nexus stop
$ h2sql "SCRIPT NODATA"
> SCRIPT NODATA
Exception in thread "main" org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database may be already in use: "/nexus-data/db/nexus.mv.db". Possible solutions: close all other connection(s); use the server mode [90020-224]

K1kc4 commented 2 months ago

I have used the following script to reset all the auto-increment counters (it alters some 100 columns and takes about a minute). Nexus must not be running when executing the script.
#!/bin/bash

NEXUS_BIN=/opt/nexus/nexus-3.71.0-06
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
h2sql() {
    echo "> $@" >&2
    java -cp "$NEXUS_BIN"/system/com/h2database/h2/*/h2-*.jar org.h2.tools.Shell -url jdbc:h2:"$NEXUS_DATA"/db/nexus -sql "$@"
}

schema=$(h2sql "SCRIPT NODATA")
echo "$schema" | while read line; do
    if [[ $line =~ ^CREATE\ CACHED\ TABLE\ \"PUBLIC\"\.\"([^\"]+)\" ]]; then
        tbl="${BASH_REMATCH[1]}"
    elif [[ $line =~ ^\"([^\"]+)\"\ .*\ GENERATED\ BY\ DEFAULT ]]; then
        col="${BASH_REMATCH[1]}"
        h2sql "ALTER TABLE $tbl ALTER COLUMN $col RESTART WITH SELECT max($col) + 1 FROM $tbl"
    fi
done
thanks for this. We are running Nexus OSS in docker container. I'm not familiar with H2 but I got this message when I was trying to debug - any ideas? (we were getting the same 500 errors in the logs when trying to upload after an OrientDB to H2 migration). In the mean time I've reverted back to OrientDB on 3.70.1-02, but am busy setting up a test environment. I agree completely that Sonatype need to fix the upgrade process as many OSS users will be forcibly switching to H2 at the moment.
#  docker exec -it nexus /bin/bash
$ NEXUS_BIN=/opt/sonatype/nexus
$ NEXUS_DATA=/nexus-data
$ /opt/sonatype/nexus/bin/nexus stop
$ h2sql "SCRIPT NODATA"
> SCRIPT NODATA
Exception in thread "main" org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database may be already in use: "/nexus-data/db/nexus.mv.db". Possible solutions: close all other connection(s); use the server mode [90020-224]
/opt/sonatype/nexus/bin/nexus stop is not stopping the instance. You should use script as entrypoint.

murainwood commented 2 months ago

I have used the following script to reset all the auto-increment counters (it alters some 100 columns and takes about a minute). Nexus must not be running when executing the script.↳
#!/bin/bash

NEXUS_BIN=/opt/nexus/nexus-3.71.0-06
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
h2sql() {
    echo "> $@" >&2
    java -cp "$NEXUS_BIN"/system/com/h2database/h2/*/h2-*.jar org.h2.tools.Shell -url jdbc:h2:"$NEXUS_DATA"/db/nexus -sql "$@"
}

schema=$(h2sql "SCRIPT NODATA")
echo "$schema" | while read line; do
    if [[ $line =~ ^CREATE\ CACHED\ TABLE\ \"PUBLIC\"\.\"([^\"]+)\" ]]; then
        tbl="${BASH_REMATCH[1]}"
    elif [[ $line =~ ^\"([^\"]+)\"\ .*\ GENERATED\ BY\ DEFAULT ]]; then
        col="${BASH_REMATCH[1]}"
        h2sql "ALTER TABLE $tbl ALTER COLUMN $col RESTART WITH SELECT max($col) + 1 FROM $tbl"
    fi
done
thanks for this. We are running Nexus OSS in docker container. I'm not familiar with H2 but I got this message when I was trying to debug - any ideas? (we were getting the same 500 errors in the logs when trying to upload after an OrientDB to H2 migration). In the mean time I've reverted back to OrientDB on 3.70.1-02, but am busy setting up a test environment. I agree completely that Sonatype need to fix the upgrade process as many OSS users will be forcibly switching to H2 at the moment.↳
#  docker exec -it nexus /bin/bash
$ NEXUS_BIN=/opt/sonatype/nexus
$ NEXUS_DATA=/nexus-data
$ /opt/sonatype/nexus/bin/nexus stop
$ h2sql "SCRIPT NODATA"
> SCRIPT NODATA
Exception in thread "main" org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database may be already in use: "/nexus-data/db/nexus.mv.db". Possible solutions: close all other connection(s); use the server mode [90020-224]
/opt/sonatype/nexus/bin/nexus stop is not stopping the instance. You should use script as entrypoint.↳

For this shell script, you must shutdown the nexus instance. But if shutdown the nexus, you could not enter into container... Emmm, you have to copy the DB file from container(I think you have data volume) and try to modify it offline.(you could use my pasted SQL statments, use GUI tool, like Datagrip or others) After modified, copy back to container

Pls check H2 operation document:

https://www.h2database.com/html/features.html#embedded_databases

jsaalfeld commented 2 months ago

A repair task would also be helpful. Is there anything like that already?

You can actually just run the task "Repair - Rebuild Maven repository metadata (maven-metadata.xml)" a couple of times. It will try to repair stuff and run in this error so frequently, that at some point the unique id is at the right number.

dioss-Machiel commented 2 months ago

You can actually just run the task "Repair - Rebuild Maven repository metadata (maven-metadata.xml)" a couple of times. It will try to repair stuff and run in this error so frequently, that at some point the unique id is at the right number.

This will only partially fix this issue since it doesn't fix all the other counters

murainwood commented 2 months ago

I have used the following script to reset all the auto-increment counters (it alters some 100 columns and takes about a minute). Nexus must not be running when executing the script.↳
#!/bin/bash

NEXUS_BIN=/opt/nexus/nexus-3.71.0-06
NEXUS_DATA=/opt/nexus/sonatype-work/nexus3
h2sql() {
    echo "> $@" >&2
    java -cp "$NEXUS_BIN"/system/com/h2database/h2/*/h2-*.jar org.h2.tools.Shell -url jdbc:h2:"$NEXUS_DATA"/db/nexus -sql "$@"
}

schema=$(h2sql "SCRIPT NODATA")
echo "$schema" | while read line; do
    if [[ $line =~ ^CREATE\ CACHED\ TABLE\ \"PUBLIC\"\.\"([^\"]+)\" ]]; then
        tbl="${BASH_REMATCH[1]}"
    elif [[ $line =~ ^\"([^\"]+)\"\ .*\ GENERATED\ BY\ DEFAULT ]]; then
        col="${BASH_REMATCH[1]}"
        h2sql "ALTER TABLE $tbl ALTER COLUMN $col RESTART WITH SELECT max($col) + 1 FROM $tbl"
    fi
done
thanks for this. We are running Nexus OSS in docker container. I'm not familiar with H2 but I got this message when I was trying to debug - any ideas? (we were getting the same 500 errors in the logs when trying to upload after an OrientDB to H2 migration). In the mean time I've reverted back to OrientDB on 3.70.1-02, but am busy setting up a test environment. I agree completely that Sonatype need to fix the upgrade process as many OSS users will be forcibly switching to H2 at the moment.↳
#  docker exec -it nexus /bin/bash
$ NEXUS_BIN=/opt/sonatype/nexus
$ NEXUS_DATA=/nexus-data
$ /opt/sonatype/nexus/bin/nexus stop
$ h2sql "SCRIPT NODATA"
> SCRIPT NODATA
Exception in thread "main" org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database may be already in use: "/nexus-data/db/nexus.mv.db". Possible solutions: close all other connection(s); use the server mode [90020-224]

cdb9d605f2ab8304eb147ed9f6b4e1c

shop the nexus3 container
copy the db file nexus.mv.db from data volume to your local desktop
open the db file via Datagrip or any other tools that support H2 embeded mode
execute the SQL statements as I pasted
shutdown the connection and copy back the modified db file to data volume
restart your neuxs container

jsaalfeld commented 2 months ago

You can actually just run the task "Repair - Rebuild Maven repository metadata (maven-metadata.xml)" a couple of times. It will try to repair stuff and run in this error so frequently, that at some point the unique id is at the right number.

This will only partially fix this issue since it doesn't fix all the other counters

You're right. While this worked for the docker based repositories, it didn't for the maven based repositories. The given script worked with the slight adjustment of pinning the directory for the h2 database shell, instead of wildcarding.

Telesphoreo commented 2 months ago

I get this error still after running the SQL commands on the H2 database

2024-08-22 14:48:27,043-0500 INFO  [FelixStartLevel] *SYSTEM org.sonatype.nexus.extender.NexusLifecycleManager - Start STORAGE
2024-08-22 14:48:27,050-0500 INFO  [FelixStartLevel] *SYSTEM org.sonatype.nexus.datastore.DataStoreConfigurationDefaultSource - Loaded 'nexus' data store configuration defaults (Embedded H2)
2024-08-22 14:48:27,147-0500 INFO  [FelixStartLevel] *SYSTEM com.zaxxer.hikari.HikariDataSource - nexus - Starting...
2024-08-22 14:48:28,191-0500 ERROR [FelixStartLevel] *SYSTEM com.zaxxer.hikari.pool.HikariPool - nexus - Exception during pool initialization.
org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.nio.channels.NonWritableChannelException" [50000-224]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:566)
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
        at org.h2.message.DbException.get(DbException.java:212)
        at org.h2.message.DbException.convert(DbException.java:407)
        at org.h2.engine.Database.<init>(Database.java:405)
        at org.h2.engine.Engine.openSession(Engine.java:92)
        at org.h2.engine.Engine.openSession(Engine.java:222)
        at org.h2.engine.Engine.createSession(Engine.java:201)
        at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:343)
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:125)
        at org.h2.Driver.connect(Driver.java:59)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
        at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
        at org.sonatype.nexus.datastore.mybatis.MyBatisDataStore.doStart(MyBatisDataStore.java:224)
        at org.sonatype.nexus.datastore.DataStoreSupport.doStart(DataStoreSupport.java:63)
        at org.sonatype.nexus.common.stateguard.StateGuardLifecycleSupport.start(StateGuardLifecycleSupport.java:69)
        at org.sonatype.nexus.datastore.mybatis.MyBatisDataStore$$EnhancerByGuice$$14bed13b.GUICE$TRAMPOLINE(<generated>)
        at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:74)
        at org.sonatype.nexus.common.stateguard.MethodInvocationAction.run(MethodInvocationAction.java:39)
        at org.sonatype.nexus.common.stateguard.StateGuard$TransitionImpl.run(StateGuard.java:206)
        at org.sonatype.nexus.common.stateguard.TransitionsInterceptor.invoke(TransitionsInterceptor.java:57)
        at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)
        at com.google.inject.internal.InterceptorStackCallback.invoke(InterceptorStackCallback.java:55)
        at org.sonatype.nexus.datastore.mybatis.MyBatisDataStore$$EnhancerByGuice$$14bed13b.start(<generated>)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.doCreate(DataStoreManagerImpl.java:194)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.tryRestore(DataStoreManagerImpl.java:175)
        at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.doStart(DataStoreManagerImpl.java:124)
        at org.sonatype.nexus.common.stateguard.StateGuardLifecycleSupport.start(StateGuardLifecycleSupport.java:69)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl$$EnhancerByGuice$$7150144.GUICE$TRAMPOLINE(<generated>)
        at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:74)
        at org.sonatype.nexus.common.stateguard.MethodInvocationAction.run(MethodInvocationAction.java:39)
        at org.sonatype.nexus.common.stateguard.StateGuard$TransitionImpl.run(StateGuard.java:206)
        at org.sonatype.nexus.common.stateguard.TransitionsInterceptor.invoke(TransitionsInterceptor.java:57)
        at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)
        at com.google.inject.internal.InterceptorStackCallback.invoke(InterceptorStackCallback.java:55)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl$$EnhancerByGuice$$7150144.start(<generated>)
        at org.sonatype.nexus.extender.NexusLifecycleManager.startComponent(NexusLifecycleManager.java:210)
        at org.sonatype.nexus.extender.NexusLifecycleManager.to(NexusLifecycleManager.java:121)
        at org.sonatype.nexus.extender.NexusContextListener.moveToPhase(NexusContextListener.java:334)
        at org.sonatype.nexus.extender.NexusContextListener.frameworkEvent(NexusContextListener.java:231)
        at org.apache.felix.framework.Felix.setActiveStartLevel(Felix.java:1597)
        at org.apache.felix.framework.FrameworkStartLevelImpl.run(FrameworkStartLevelImpl.java:308)
        at java.lang.Thread.run(Thread.java:750)
Caused by: java.nio.channels.NonWritableChannelException: null
        at sun.nio.ch.FileChannelImpl.write(FileChannelImpl.java:751)
        at org.h2.mvstore.DataUtils.writeFully(DataUtils.java:474)
        at org.h2.mvstore.SingleFileStore.writeFully(SingleFileStore.java:67)
        at org.h2.mvstore.SingleFileStore.writeFully(SingleFileStore.java:28)
        at org.h2.mvstore.RandomAccessStore.writeStoreHeader(RandomAccessStore.java:610)
        at org.h2.mvstore.RandomAccessStore.initializeStoreHeader(RandomAccessStore.java:345)
        at org.h2.mvstore.FileStore.start(FileStore.java:912)
        at org.h2.mvstore.MVStore.<init>(MVStore.java:289)
        at org.h2.mvstore.MVStore$Builder.open(MVStore.java:2035)
        at org.h2.mvstore.db.Store.<init>(Store.java:133)
        at org.h2.engine.Database.<init>(Database.java:326)
        ... 44 common frames omitted
2024-08-22 14:48:28,193-0500 ERROR [FelixStartLevel] *SYSTEM org.sonatype.nexus.datastore.mybatis.MyBatisDataStore - Failed transition: NEW -> STARTED
com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: General error: "java.nio.channels.NonWritableChannelException" [50000-224]
        at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:596)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:582)
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
        at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
        at org.sonatype.nexus.datastore.mybatis.MyBatisDataStore.doStart(MyBatisDataStore.java:224)
        at org.sonatype.nexus.datastore.DataStoreSupport.doStart(DataStoreSupport.java:63)
        at org.sonatype.nexus.common.stateguard.StateGuardLifecycleSupport.start(StateGuardLifecycleSupport.java:69)
        at org.sonatype.nexus.common.stateguard.MethodInvocationAction.run(MethodInvocationAction.java:39)
        at org.sonatype.nexus.common.stateguard.StateGuard$TransitionImpl.run(StateGuard.java:206)
        at org.sonatype.nexus.common.stateguard.TransitionsInterceptor.invoke(TransitionsInterceptor.java:57)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.doCreate(DataStoreManagerImpl.java:194)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.tryRestore(DataStoreManagerImpl.java:175)
        at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.doStart(DataStoreManagerImpl.java:124)
        at org.sonatype.nexus.common.stateguard.StateGuardLifecycleSupport.start(StateGuardLifecycleSupport.java:69)
        at org.sonatype.nexus.common.stateguard.MethodInvocationAction.run(MethodInvocationAction.java:39)
        at org.sonatype.nexus.common.stateguard.StateGuard$TransitionImpl.run(StateGuard.java:206)
        at org.sonatype.nexus.common.stateguard.TransitionsInterceptor.invoke(TransitionsInterceptor.java:57)
        at org.sonatype.nexus.extender.NexusLifecycleManager.startComponent(NexusLifecycleManager.java:210)
        at org.sonatype.nexus.extender.NexusLifecycleManager.to(NexusLifecycleManager.java:121)
        at org.sonatype.nexus.extender.NexusContextListener.moveToPhase(NexusContextListener.java:334)
        at org.sonatype.nexus.extender.NexusContextListener.frameworkEvent(NexusContextListener.java:231)
        at org.apache.felix.framework.Felix.setActiveStartLevel(Felix.java:1597)
        at org.apache.felix.framework.FrameworkStartLevelImpl.run(FrameworkStartLevelImpl.java:308)
        at java.lang.Thread.run(Thread.java:750)
Caused by: org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.nio.channels.NonWritableChannelException" [50000-224]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:566)
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
        at org.h2.message.DbException.get(DbException.java:212)
        at org.h2.message.DbException.convert(DbException.java:407)
        at org.h2.engine.Database.<init>(Database.java:405)
        at org.h2.engine.Engine.openSession(Engine.java:92)
        at org.h2.engine.Engine.openSession(Engine.java:222)
        at org.h2.engine.Engine.createSession(Engine.java:201)
        at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:343)
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:125)
        at org.h2.Driver.connect(Driver.java:59)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
        ... 23 common frames omitted
Caused by: java.nio.channels.NonWritableChannelException: null
        at sun.nio.ch.FileChannelImpl.write(FileChannelImpl.java:751)
        at org.h2.mvstore.DataUtils.writeFully(DataUtils.java:474)
        at org.h2.mvstore.SingleFileStore.writeFully(SingleFileStore.java:67)
        at org.h2.mvstore.SingleFileStore.writeFully(SingleFileStore.java:28)
        at org.h2.mvstore.RandomAccessStore.writeStoreHeader(RandomAccessStore.java:610)
        at org.h2.mvstore.RandomAccessStore.initializeStoreHeader(RandomAccessStore.java:345)
        at org.h2.mvstore.FileStore.start(FileStore.java:912)
        at org.h2.mvstore.MVStore.<init>(MVStore.java:289)
        at org.h2.mvstore.MVStore$Builder.open(MVStore.java:2035)
        at org.h2.mvstore.db.Store.<init>(Store.java:133)
        at org.h2.engine.Database.<init>(Database.java:326)
        ... 34 common frames omitted
2024-08-22 14:48:28,198-0500 WARN  [FelixStartLevel] *SYSTEM org.sonatype.nexus.datastore.internal.DataStoreManagerImpl - Problem restoring {name='nexus', type='jdbc', source='local', attributes={jdbcUrl=jdbc:h2:file:${karaf.data}/db/nexus}}
com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: General error: "java.nio.channels.NonWritableChannelException" [50000-224]
        at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:596)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:582)
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
        at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
        at org.sonatype.nexus.datastore.mybatis.MyBatisDataStore.doStart(MyBatisDataStore.java:224)
        at org.sonatype.nexus.datastore.DataStoreSupport.doStart(DataStoreSupport.java:63)
        at org.sonatype.nexus.common.stateguard.StateGuardLifecycleSupport.start(StateGuardLifecycleSupport.java:69)
        at org.sonatype.nexus.common.stateguard.MethodInvocationAction.run(MethodInvocationAction.java:39)
        at org.sonatype.nexus.common.stateguard.StateGuard$TransitionImpl.run(StateGuard.java:206)
        at org.sonatype.nexus.common.stateguard.TransitionsInterceptor.invoke(TransitionsInterceptor.java:57)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.doCreate(DataStoreManagerImpl.java:194)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.tryRestore(DataStoreManagerImpl.java:175)
        at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.doStart(DataStoreManagerImpl.java:124)
        at org.sonatype.nexus.common.stateguard.StateGuardLifecycleSupport.start(StateGuardLifecycleSupport.java:69)
        at org.sonatype.nexus.common.stateguard.MethodInvocationAction.run(MethodInvocationAction.java:39)
        at org.sonatype.nexus.common.stateguard.StateGuard$TransitionImpl.run(StateGuard.java:206)
        at org.sonatype.nexus.common.stateguard.TransitionsInterceptor.invoke(TransitionsInterceptor.java:57)
        at org.sonatype.nexus.extender.NexusLifecycleManager.startComponent(NexusLifecycleManager.java:210)
        at org.sonatype.nexus.extender.NexusLifecycleManager.to(NexusLifecycleManager.java:121)
        at org.sonatype.nexus.extender.NexusContextListener.moveToPhase(NexusContextListener.java:334)
        at org.sonatype.nexus.extender.NexusContextListener.frameworkEvent(NexusContextListener.java:231)
        at org.apache.felix.framework.Felix.setActiveStartLevel(Felix.java:1597)
        at org.apache.felix.framework.FrameworkStartLevelImpl.run(FrameworkStartLevelImpl.java:308)
        at java.lang.Thread.run(Thread.java:750)
Caused by: org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.nio.channels.NonWritableChannelException" [50000-224]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:566)
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
        at org.h2.message.DbException.get(DbException.java:212)
        at org.h2.message.DbException.convert(DbException.java:407)
        at org.h2.engine.Database.<init>(Database.java:405)
        at org.h2.engine.Engine.openSession(Engine.java:92)
        at org.h2.engine.Engine.openSession(Engine.java:222)
        at org.h2.engine.Engine.createSession(Engine.java:201)
        at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:343)
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:125)
        at org.h2.Driver.connect(Driver.java:59)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
        ... 23 common frames omitted
Caused by: java.nio.channels.NonWritableChannelException: null
        at sun.nio.ch.FileChannelImpl.write(FileChannelImpl.java:751)
        at org.h2.mvstore.DataUtils.writeFully(DataUtils.java:474)
        at org.h2.mvstore.SingleFileStore.writeFully(SingleFileStore.java:67)
        at org.h2.mvstore.SingleFileStore.writeFully(SingleFileStore.java:28)
        at org.h2.mvstore.RandomAccessStore.writeStoreHeader(RandomAccessStore.java:610)
        at org.h2.mvstore.RandomAccessStore.initializeStoreHeader(RandomAccessStore.java:345)
        at org.h2.mvstore.FileStore.start(FileStore.java:912)
        at org.h2.mvstore.MVStore.<init>(MVStore.java:289)
        at org.h2.mvstore.MVStore$Builder.open(MVStore.java:2035)
        at org.h2.mvstore.db.Store.<init>(Store.java:133)
        at org.h2.engine.Database.<init>(Database.java:326)
        ... 34 common frames omitted
2024-08-22 14:48:28,200-0500 ERROR [FelixStartLevel] *SYSTEM org.sonatype.nexus.extender.NexusContextListener - Failed to start nexus
org.sonatype.nexus.datastore.api.DataStoreNotFoundException: Data store not found: 'nexus'
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.lambda$1(DataStoreManagerImpl.java:145)
        at java.util.Optional.orElseThrow(Optional.java:290)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.openSession(DataStoreManagerImpl.java:145)
        at org.sonatype.nexus.datastore.TransactionalStoreSupport.openSession(TransactionalStoreSupport.java:48)
        at org.sonatype.nexus.datastore.TransactionalStoreSupport.openSession(TransactionalStoreSupport.java:1)
        at org.sonatype.nexus.transaction.UnitOfWork.doOpenSession(UnitOfWork.java:253)
        at org.sonatype.nexus.transaction.UnitOfWork.openSession(UnitOfWork.java:225)
        at org.sonatype.nexus.transaction.TransactionInterceptor.invoke(TransactionInterceptor.java:54)
        at org.sonatype.nexus.internal.node.datastore.NodeIdInitializerImpl.initialize(NodeIdInitializerImpl.java:57)
        at org.sonatype.nexus.internal.node.NodeAccessBooter.start(NodeAccessBooter.java:51)
        at org.sonatype.nexus.extender.NexusLifecycleManager.startComponent(NexusLifecycleManager.java:210)
        at org.sonatype.nexus.extender.NexusLifecycleManager.to(NexusLifecycleManager.java:121)
        at org.sonatype.nexus.extender.NexusContextListener.moveToPhase(NexusContextListener.java:334)
        at org.sonatype.nexus.extender.NexusContextListener.frameworkEvent(NexusContextListener.java:231)
        at org.apache.felix.framework.Felix.setActiveStartLevel(Felix.java:1597)
        at org.apache.felix.framework.FrameworkStartLevelImpl.run(FrameworkStartLevelImpl.java:308)
        at java.lang.Thread.run(Thread.java:750)
2024-08-22 14:48:28,201-0500 ERROR [FelixStartLevel] *SYSTEM Felix - Framework listener delivery error.
org.sonatype.nexus.datastore.api.DataStoreNotFoundException: Data store not found: 'nexus'
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.lambda$1(DataStoreManagerImpl.java:145)
        at java.util.Optional.orElseThrow(Optional.java:290)
        at org.sonatype.nexus.datastore.internal.DataStoreManagerImpl.openSession(DataStoreManagerImpl.java:145)
        at org.sonatype.nexus.datastore.TransactionalStoreSupport.openSession(TransactionalStoreSupport.java:48)
        at org.sonatype.nexus.datastore.TransactionalStoreSupport.openSession(TransactionalStoreSupport.java:1)
        at org.sonatype.nexus.transaction.UnitOfWork.doOpenSession(UnitOfWork.java:253)
        at org.sonatype.nexus.transaction.UnitOfWork.openSession(UnitOfWork.java:225)
        at org.sonatype.nexus.transaction.TransactionInterceptor.invoke(TransactionInterceptor.java:54)
        at org.sonatype.nexus.internal.node.datastore.NodeIdInitializerImpl.initialize(NodeIdInitializerImpl.java:57)
        at org.sonatype.nexus.internal.node.NodeAccessBooter.start(NodeAccessBooter.java:51)
        at org.sonatype.nexus.extender.NexusLifecycleManager.startComponent(NexusLifecycleManager.java:210)
        at org.sonatype.nexus.extender.NexusLifecycleManager.to(NexusLifecycleManager.java:121)
        at org.sonatype.nexus.extender.NexusContextListener.moveToPhase(NexusContextListener.java:334)
        at org.sonatype.nexus.extender.NexusContextListener.frameworkEvent(NexusContextListener.java:231)
        at org.apache.felix.framework.Felix.setActiveStartLevel(Felix.java:1597)
        at org.apache.felix.framework.FrameworkStartLevelImpl.run(FrameworkStartLevelImpl.java:308)
        at java.lang.Thread.run(Thread.java:750)
2024-08-22 14:48:28,231-0500 INFO  [FelixStartLevel] *SYSTEM org.sonatype.nexus.extender.NexusContextListener - Uptime: 12 seconds and 901 milliseconds (nexus-oss-edition/3.70.1.02)
2024-08-22 14:48:28,231-0500 INFO  [FelixStartLevel] *SYSTEM org.sonatype.nexus.extender.NexusLifecycleManager - Shutting down
2024-08-22 14:48:28,231-0500 INFO  [FelixStartLevel] *SYSTEM org.sonatype.nexus.extender.NexusLifecycleManager - Stop KERNEL

codingtim commented 2 months ago

@Telesphoreo Check whether the user you run nexus under has permission on the h2 database file that the migrator produced. It might be the reason why you get that error.

Telesphoreo commented 2 months ago

@Telesphoreo Check whether the user you run nexus under has permission on the h2 database file that the migrator produced. It might be the reason why you get that error.

I indeed changed the permissions and now I still get spammed with logs in the console that indicate SQL errors

Edit: I changed permissions to 777 as a test and now my nexus installation is wiped. WTF???

It appears that the files still exist, they're just not in Nexus anymore??

I have backups and am fine, but this is a terrible experience.

gracecllee commented 2 months ago

Hi everyone, Our sincere apologies for the issue you've been experiencing. Our team has been actively working on a fix and we will be sending out a point release very soon.

Best regards, Grace

ashleyw-gh commented 2 months ago

thanks @cthiemann, @K1kc4, @murainwood and everyone else that's helped with this. In my case I'm running Nexus through a docker-compose file so to help anyone else in this situation who might not be familiar with all the docker quirks of entry points here is a simple process to run the script docker-compose.yml

services:
  nexus:
    container_name: nexus
    restart: unless-stopped
    image: sonatype/nexus3:3.70.1
    user: nexus
    environment:
      - TZ=Pacific/Auckland
    volumes:
      - /home/ubuntu/nexus-data:/nexus-data
    ports:
      - 8081:8081

bring up continer

# docker compose up -d

Generate a file containing the fix script of @cthiemann and modify script to use NEXUS_HOME environment variable in the container, and generate the fix as a file /home/ubuntu/nexus-data/fixtables

# cat >/home/ubuntu/nexus-data/fixtables << 'EOF'
#!/bin/bash

h2sql() {
    echo "> $@" >&2
    java -cp "$NEXUS_HOME"/system/com/h2database/h2/*/h2-*.jar org.h2.tools.Shell -url jdbc:h2:"$NEXUS_DATA"/db/nexus -sql "$@"
}

schema=$(h2sql "SCRIPT NODATA")
echo "$schema" | while read line; do
    if [[ $line =~ ^CREATE\ CACHED\ TABLE\ \"PUBLIC\"\.\"([^\"]+)\" ]]; then
        tbl="${BASH_REMATCH[1]}"
    elif [[ $line =~ ^\"([^\"]+)\"\ .*\ GENERATED\ BY\ DEFAULT ]]; then
        col="${BASH_REMATCH[1]}"
        h2sql "ALTER TABLE $tbl ALTER COLUMN $col RESTART WITH SELECT max($col) + 1 FROM $tbl"
    fi
done
EOF

Add executable permissions to the script, stop the docker container, then start it up with entrypoint as a once off.

# chmod o+x /home/ubuntu/nexus-data/fixtables
# docker stop nexus
# docker compose run --rm --entrypoint="/bin/sh /nexus-data/fixtables" nexus

Once the script has worked its magic then start the container using the original entry point for normal operation.

# docker start nexus

thanks again to everyone. It certainly took us much longer than it should have done to figure this out.

eitch commented 2 months ago

@ashleyw-gh Thanks a lot four your script! This truly saved me!

GijsCalis commented 2 months ago

Hi everyone, Our sincere apologies for the issue you've been experiencing. Our team has been actively working on a fix and we will be sending out a point release very soon.

Best regards, Grace

@gracecllee and @nblair : To prevent more users from running into this issue, shouldn't a warning be added to at least the release notes that the migration to the H2-database is currently broken?

uohndecadisde commented 2 months ago

Hi everyone, Our sincere apologies for the issue you've been experiencing. Our team has been actively working on a fix and we will be sending out a point release very soon.

Best regards, Grace

Please consider to put a warning into your official documentation about this, so others won't fall into the same pit.

fionnb commented 2 months ago

WARNING: After applying the above fix, uploads are possible again, but all of our apt repositories deliver empty package lists. The packages are still there and can be seen in the web browser but the package-list files retrieved by apt update are empty. Only packages that have been REUPLOADED to nexus AFTER the fix reappear in the debian package files for the repos. At the moment I feel a strong urge to roll back to 3.70 until the mess is sorted.

UPDATE: Problem solved by creating a System->Task of Type "Apt - Rebuild Apt metadata" and applying it to all our repos.

moddx commented 2 months ago

@fionnb Is there a Repair Index task for Debian/apt repos? Same helped me with Helm / Docker repos on one system.

fionnb commented 2 months ago

@fionnb Is there a Repair Index task for Debian/apt repos? Same helped me with Helm / Docker repos on one system.

I think not. I may have to consult the docs on how to make one. Manually clicking "rebuild index" in the apt repo settings page did NOT solve the problem, though.

UPDATE: Problem solved by creating a System->Task of Type "Apt - Rebuild Apt metadata" and applying it to all our repos.

vx-github commented 2 months ago

Hi everyone, Our sincere apologies for the issue you've been experiencing. Our team has been actively working on a fix and we will be sending out a point release very soon.

Best regards, Grace

Hi @gracecllee , this is a nice message, but you are very unclear in your communications about the state of this issue:

This is issue is still labeled pending
On the community forum there is no (update) response from Sonatype (for example on a question like: https://community.sonatype.com/t/update-from-v3-70-1-to-3-71-0/13055/10 )
There still is no warning in the upgrade/migration documentation.

The point release you are preparing, is it for the migration tool or a new Nexus version?

Kind regards, Vincent

devZer0 commented 2 months ago

we have this problem on 2 of our "production" instances and need to rollback now.