Support

Database migration has failed; Error while attempting to save record

Follow

Problem

When attempting to migrate your SQLite3 database to a Postgres database, you may see the error below on newer versions of Rstudio Connect (Connect versions greater than v1.9.0):

Error Running Command err=The database migration has failed; do not use the target database: while attempting to save record 000/000 in table job_metrics: pq: value "2393485252" is out of range for type integer

The INT data type for postgres databases are set to a fixed value, whereas SQLite INTEGERs magically expand when needed.

 

Resolution

1. Check that the sqlite3 binary is available on your Connect server by running the command below:

which sqlite3

NOTE: You may need to install it if necessary. The command to do so will vary on your Linux distribution:

Ubuntu

sudo apt install sqlite3

 

2. Locate your SQLite database directory used by Connect; this is configured with https://docs.rstudio.com/connect/admin/appendix/configuration/#SQLite.Dir; by default it is {Server.DataDir}/db. The rest of the instructions will refer to this location as {db dir}/; replace this with your actual database directory.

 

3. Shut down Connect by running:

sudo systemctl stop rstudio-connect

https://docs.rstudio.com/connect/admin/server-management/#stopping-starting 

Ensure that no Connect process is running, or you may corrupt your database.

 

4. Back up your database directory by copying it to a separate location.

 

5. Update the job_metrics table to drop big rows by running the command below (replacing {db dir} with your actual database directory):

sqlite3 {db dir}/connect.db "DELETE FROM job_metrics WHERE mem >= 2147483648"

 

Retry the SQLite -> PostgreSQL migration. Note that if a previous migration failed, you will either need to manually drop all the data from the target PostgreSQL database, or use the --drop-all flag with the migrate db command.


This time the migration should succeed:

2021/11/08 20:24:11 Data migration successful.

 

These steps work around a bug in the PostgreSQL schema, which will be fixed in the next release of Connect. The rows that are dropped from the job_metrics table are historical metrics data and will not affect the operation of Connect or any of your published content.

 

Note

Please note that this issue will only appear on certain versions of RStudio Connect. Currently, this includes:

  • 2021.08.0
  • 2021.09.0
  • 2021.10.0

This will be resolved in version 2021.11.0 of RStudio Connect.

 

Support Ticket

If you still have issues after completing the above, you can always lodge a support ticket, where our group of friendly, and incredibly knowledgeable staff can assist with any issues that you may be having. You can submit a ticket here:

https://support.rstudio.com/hc/en-us/requests/new

 

 

Comments