Error on migration:run

[dmeinke-etc]

I last ran a migration a week or so ago on production and have ran many in the past.

Now when I do node ace migration:run

I get an error:
[ error ] create table adonis_schema_versions (version int not null) - Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

Obviously dev works fine. This is on production on a Digital Ocean droplet

The database is up and running. I can login the app and do what I need.

Any ideas on this? At least where to start investigating?

[MaximeMRF]

Hello @dmeinke-etc,

Have you check this stackoverflow link ?

I think you have to set the sql variable sql_require_primary_key to "off" or 0.
Or just add a primary key to all your migrations:

Model:

export default class User extends BaseModel {
  @column({ isPrimary: true })
  public id: number;
//...

Migration:

export default class extends BaseSchema {
  protected tableName = "users";

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments("id").primary();
// ...

[dmeinke-etc]

Defiantly all my tables have a primary key. This app has been running for over a year. I added a new feature last week and the migration ran fine. I have a total of 63 records in my adonis_schema. Why would this break now?

Where is the variable sql_require_primary_key

[MaximeMRF]

You can modified it by using the digital ocean api (check the stackoverflow link that i have send in the previous response).
Or also execute a query directly with ssh if you have a ssh access for your db, or execute a query in AdonisJs with the rawQuery.

Like that (I haven't tested this code):

await Database.rawQuery('set sql_require_primary_key = 0')

[soonerdm]

The first solution fixed it. Thanks for the insight.

[MaximeMRF]

@soonerdm Cool ! Can you close the discussion if you have found the solution ?

[soonerdm]

This was the answer
https://stackoverflow.com/questions/62418099/unable-to-create-or-change-a-table-without-a-primary-key-laravel-digitalocean