nexus-public: Cannot upload after OrientDB to H2 migration: Unique index or primary key violation
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?
About this issue
- Original URL
- State: open
- Created 2 years ago
- Reactions: 49
- Comments: 88
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.
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
For those using docker compose, create the above script and add a
entrypoint:pointing to it. For the fix script, useNEXUS_BIN=/opt/sonatype/nexus/AndNEXUS_DATAwould default to/nexus-data/, although I think that could be changed depending on your setup.@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?
Hi all, We’ve released 3.70.2 today with the fix. Please use the 3.70.2 db-migrator instead. Our sincere apologies for any inconvenience this has caused.
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?
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;
Hi @gracecllee , this is a nice message, but you are very unclear in your communications about the state of this issue:
The point release you are preparing, is it for the migration tool or a new Nexus version?
Kind regards, Vincent
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=truenexus.h2.httpListenerPort=1234restart the nexus3
enter the url in browser “http://XXXXX:1234”
Notice: the jdbc url is : jdbc:h2:file:nexus leave the username & password empty
Hello,
Facing the same issue here. We would really appreciate a new version for the database migrator that handles these auto-increment cases.
We run Nexus on Docker and faced the same issue (version 3.78.2). The script posted above fixed our issue but we followed a slightly different process (we made a backup, modified the backup file, and restored it).
Admin - Backup H2 Databaseto generate a fresh and unlockednexus.mv.db- it generates a zip file, so it needs to be unzipped).curl -s -L -O https://repo1.maven.org/maven2/com/h2database/h2/2.3.232/h2-2.3.232.jarjava -cp h2*.jar org.h2.tools.Shell -url jdbc:h2:/backups/nexus. Were able to run queries likeSHOW TABLES;./backups/nexus.mv.dband the command below was run in the path where the fileh2-2.3.232.jarwas):/nexus-data, exactly to the pathnexus-data/db/nexus.mv.db.@cthiemann save my day.
Step by step for docker compose based installation:
docker compose stopdocker-compose.yml:/var/lib/docker/volumes/nexus3_nexus-data/_datachown 200 migrator_script.shchmod +x migrator_script.shmigrator_repair.sh:docker compose up -ddocker compose exec nexus3 /bin/bashcd /nexus-ddata./migrator_repair.shCTRL+Ddocker compose stopdocker-compose.yml- removecommand:docker compose up -dexactly my point. 3.70.1 -> 3.71.0 I would argue this can be interpreted as minor. but nonetheless, a disaster.
We also have this prob after migrated OrientDB to H2 with nexus 3.71 This script also solve our issue, great help, thanks!
Thanks.
If you are using nexus in container, you can run this script as entrypoint and then just remove after its done.
Please consider to put a warning into your official documentation about this, so others won’t fall into the same pit.
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
bring up continer
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
Add executable permissions to the script, stop the docker container, then start it up with entrypoint as a once off.
Once the script has worked its magic then start the container using the original entry point for normal operation.
thanks again to everyone. It certainly took us much longer than it should have done to figure this out.
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
that was it. I had that extra ‘nexus’ on the NEXUS_DATA variable. Thanks - you ROCK!
I would like that too, but guess that’s where the paid plan with enterprise support comes into play.
Just to add feedback : we tried migrating from OrientDB to H2 this morning, we are encountering the exact same problem. Currently trying to see if we can fix it with intel provided here, but rollback is on the table too.
Worked like a charme. Thx.
@AllRWeak If running docker/podman there is no need to install java. Just use the normal docker image of nexus and perform the migration within the image in a shell. This way there is no need to install java and you’ll also not run into permission issues for the files created… You just need to replace the command being run in
docker runby adding/bin/bashand use-itso that you’ll get a console.so more or less like this (simplified from what i do…)
This might be helpful, at least that’s how I did it on a Docker running on an Ubuntu host:
Prerequisites
Depending on your environment you might need to adjust the paths.
cd /srv/nexus/data/backup sudo wget https://download.sonatype.com/nexus/nxrm3-migrator/nexus-db-migrator-3.70.2-01.jar sudo apt install openjdk-11-jdk-headless -y sudo java -Xmx16G -Xms16G -XX:+UseG1GC -XX:MaxDirectMemorySize=28672M -jar nexus-db-migrator-*.jar --migration_type=h2 sudo docker stop nexus sudo mv nexus.mv.db …/db/ sudo chown 200:200 …/db/nexus.mv.db sudo echo “nexus.datastore.enabled=true” >> …/etc/nexus.properties sudo apt remove openjdk-11-jdk-headless -y sudo docker start nexus sudo docker logs -f nexus
Hi,
Is there some place one can find a solid documentation about how to properly upgrade a OSS 3.70.1-02 Docker-based Nexus setup which was using OrientDB towards 3.71 with H2 migration?
So far, this github issue is the only place where parts of scattered information have to be gathered, which does not reflect the usual high quality of the Sonatype documentation otherwise.
is there any official fix for anyone who’s already migrated from OrientDB -> H2 (ie already upgraded to 3.71.x many weeks ago). Or do we have to roll back to an earlier backup version and run the migration again? I hope not, this seems pretty poor given the 3.71.x upgrade was originally published 3+ weeks ago?
perhaps I’m oversimplifying this, but for people in my situation - upgraded to 3.71.x and have broken indexes - could you not create a scheduled maintenance task to correct the indexes in a re-release of 3.71.x or the next release of 3.72.x ?
FYI - I struggled to implement the scripted change suggested by other community members on my Docker instance for Nexus3 3.71.x (I’ve zero experience of OrientDB / H2).
If anyone can offer some advice (for someone who’s already migrated to 3.71.x and has primary key violations) I’d very much appreciate it, like “These are the [officially supported?] steps you need to take to get back to working version of nexus3”.
This really should’ve been released as Nexus 4 or something. This is a major and not painless transition.
an upgrade with database conversion is no minor upgrade.
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.
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
A repair task would also be helpful. Is there anything like that already?
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.