Error: relation ...... already exists

Questions and Answers Database-SQL
1

I’m using the digital ocean installation, which I found that it uses postgresql.
Everything went fine. I started building the schema using strapi UI, then when strapi server restarts to save the schema at one point it just takes too long to save. I refresh the page and then I get 502 error (which means the server completely isnt running )

I then try to ssh to the server, and use

‘npm start’

inside the
‘/srv/strapi/strapi-development’
directory

And when the server tries to start, it gives me this error:

Error: relation post_category already exists

I also tried running the server using
‘pm2’
… and it didn’t work.

2

It’s most likely related to:

While running as the service user, can you give the entire error from ~/.pm2/logs/*.log (just the error).
Also can you describe what you were changing, we will probably need to manually adjust the database.

2 Likes
3

I remember I deleted a collection, then I created a new one with the same name. If Strapi (as I read in the link that you provided) does not delete tables in the db (as I understand 1 table = 1 collection), then that might be the cause of the problem.

Also I used complex names when I was building the relations, which I don’t know how much it might be related to causing an error.

Either way, I tried logging directly into the postgresql database to delete the duplicate relations and this is what I noticed:

  1. I couldn’t see any duplicates in the database, at least for the entry that the server was claiming their was a duplicate for.
  2. I had a very hard time (4 hours minimum wasted) with trying to delete the relations from the db. As few seconds after I delete them they get automatically created, then I tried deleting their sequences first and same trouble, and I ended up messing with the whole tables and db.

As my installation was still new (only few hours invested in creating the schema through Strapi’s UI) I decided to reinstall it and start over again.

Things are working fine of course with a fresh installation but I’m afraid a similar error can happen again when I am days or months down the road with Strapi.

But, knowing that Strapi doesn’t delete the table from the db after a collection is deleted will make me never re-use the same collection name twice with Strapi.

Either way I felt that whenever the server was trying to start it was insisting on trying to write the same relations to the db. I was hoping for a way /utility to delete the cache of the server, or a utility to run health checks and fixing for the db. I felt helpless without these.

For the log file, the one from pm2 is different from the one I get when I run from ‘npm start’, and it shows only this:

Error: Cannot find module ‘/srv/strapi/strapi-development’
at Function.Module._resolveFilename (internal/modules/cjs/loader.js:965:15)
at Function.Module._load (internal/modules/cjs/loader.js:841:27)
at Object. (/usr/local/share/.config/yarn/global/node_modules/pm2/lib/ProcessContainerFork.js:32:23)
at Module._compile (internal/modules/cjs/loader.js:1137:30)
at Object.Module._extensions…js (internal/modules/cjs/loader.js:1157:10)
at Module.load (internal/modules/cjs/loader.js:985:32)
at Function.Module._load (internal/modules/cjs/loader.js:878:14)
at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:71:12)
at internal/main/run_main_module.js:17:47 {
code: ‘MODULE_NOT_FOUND’,
requireStack: []
}

4

Yeah we are aware of the massive pain point on this, especially since these one click options are geared for more non-technical users.

I’ve escalated this internally.

2 Likes
5

I’m very new to Strapi (version) 3.5.3 and MySQL 5.7.31.

Deleting a content type doesn’t delete the table/fields from the db. I tried using strict mode (with reference to issue no.1114, but to no avail. The MySQL db still contains the content type table and fields.
database.js:

module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'mysql',
        host: env('DATABASE_HOST', '127.0.0.1'),
        port: env.int('DATABASE_PORT', 3306),
        database: env('DATABASE_NAME', 'beta'),
        username: env('DATABASE_USERNAME', 'root'),
        password: env('DATABASE_PASSWORD', ''),
        ssl: env.bool('DATABASE_SSL', false),
        collation: 'latin1_swedish_ci'
      },
      options: {
        strict: 'true',
        collation: 'latin1_swedish_ci'
      }
    },
  },
});

Has there been any changes since this workaround? I am pretty new to strapi and I am kinda stuck. :slight_smile: Cheers!

6