{"record":{"id":"be9ca62175d23ed4","repo":"MHSanaei/3x-ui","slug":"drop-legacy-foreign-key-w","errorCode":null,"errorMessage":"drop legacy foreign key: %w","messagePattern":"drop legacy foreign key: %w","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"critical","filePath":"internal/database/migrate_data.go","lineNumber":115,"sourceCode":"\t}\n\tdefer dstSQL.Close()\n\tdstSQL.SetConnMaxLifetime(time.Hour)\n\n\tlog.Println(\"Creating destination schema...\")\n\tfor _, m := range migrationModels() {\n\t\tif err := dst.AutoMigrate(m); err != nil {\n\t\t\treturn fmt.Errorf(\"AutoMigrate %T: %w\", m, err)\n\t\t}\n\t}\n\n\ttotalRows := 0\n\ttxErr := dst.Transaction(func(tx *gorm.DB) error {\n\t\t// AutoMigrate re-creates the legacy client_traffics -> inbounds foreign key,\n\t\t// but the running panel drops it (see dropLegacyForeignKeys) and tolerates\n\t\t// client_traffics rows whose inbound was deleted. Drop it here too so copying\n\t\t// such orphaned rows can't fail with an fk_inbounds_client_stats violation.\n\t\tif err := tx.Exec(\"ALTER TABLE client_traffics DROP CONSTRAINT IF EXISTS fk_inbounds_client_stats\").Error; err != nil {\n\t\t\treturn fmt.Errorf(\"drop legacy foreign key: %w\", err)\n\t\t}\n\n\t\t// Empty the destination tables before copying: a fresh PostgreSQL DB\n\t\t// already holds an auto-seeded admin (id=1) from any prior panel start,\n\t\t// so a plain INSERT with explicit ids would collide on users_pkey. Only\n\t\t// the panel's own tables are cleared, and a failure anywhere in this\n\t\t// transaction rolls the clear back with everything else.\n\t\tif err := truncatePostgresTables(tx, migrationModels()); err != nil {\n\t\t\treturn fmt.Errorf(\"clear destination tables: %w\", err)\n\t\t}\n\n\t\tfor _, m := range migrationModels() {\n\t\t\tn, err := copyTable(src, tx, m)\n\t\t\tif err != nil {\n\t\t\t\treturn fmt.Errorf(\"copy %T: %w\", m, err)\n\t\t\t}\n\t\t\ttotalRows += n\n\t\t\tlog.Printf(\"  %-32s %d rows\", reflect.TypeOf(m).Elem().Name(), n)","sourceCodeStart":97,"sourceCodeEnd":133,"githubUrl":"https://github.com/MHSanaei/3x-ui/blob/ad32144c42455696ea9f14e12168beac3e25f5d2/internal/database/migrate_data.go#L97-L133","documentation":"Thrown inside the SQLite-to-PostgreSQL copy transaction when the raw statement `ALTER TABLE client_traffics DROP CONSTRAINT IF EXISTS fk_inbounds_client_stats` fails. AutoMigrate re-creates this legacy FK, but the running panel drops it so orphaned client_traffics rows (inbound deleted) survive; the migration drops it too or the row copy would abort with fk_inbounds_client_stats violations. The whole copy transaction rolls back, so the destination stays empty rather than half-migrated.","triggerScenarios":"Running the migrate-db path with a destination role that does not own client_traffics (ALTER ... DROP CONSTRAINT requires table ownership), a Postgres server error/parse failure on the DDL, or the transaction's connection already being in an aborted state from an earlier statement.","commonSituations":"Migrating into a Postgres DB provisioned by a cloud control plane where the panel tables are owned by a different role (e.g. postgres) than the DSN user; pointing the destination DSN at a managed Postgres with restricted DDL permissions; a prior statement in the same tx failing and poisoning the connection.","solutions":["Read the wrapped %w cause — it names the exact Postgres error (permission, syntax, connection).","Grant ownership of the panel tables to the DSN role, or run the migration with the role that owns them (ALTER TABLE ... OWNER TO, or re-create the DB owned by the DSN user).","Verify the statement manually: psql \"$XUI_DB_DSN\" -c 'ALTER TABLE client_traffics DROP CONSTRAINT IF EXISTS fk_inbounds_client_stats';","Migrate into a fresh empty database owned by the DSN user instead of a pre-provisioned shared one."],"exampleFix":null,"handlingStrategy":"validation","validationCode":"// before migrating, confirm the DSN role owns client_traffics\nvar owner string\nrow := pgDB.Raw(\"SELECT tableowner FROM pg_tables WHERE tablename = 'client_traffics'\").Row()\nif err := row.Scan(&owner); err == nil {\n    var user string\n    pgDB.Raw(\"SELECT current_user\").Row().Scan(&user)\n    if owner != user {\n        return fmt.Errorf(\"client_traffics owned by %s, DSN user is %s\", owner, user)\n    }\n}","typeGuard":null,"tryCatchPattern":"err := RunSQLiteToPostgres(...)\nif err != nil && strings.Contains(err.Error(), \"drop legacy foreign key\") {\n    // surface the wrapped pq error; fix ownership/privileges, then re-run (tx rolled back, safe to retry)\n}","preventionTips":["Always migrate into a fresh empty database owned by the DSN user.","Never point migrate-db at the database a live panel is using.","Keep the wrapped %w chain intact when logging so the Postgres SQLSTATE stays visible."],"tags":["database","postgres","migration","ddl","gorm"],"backgroundTag":null,"analyzedSha":"ad32144c42455696ea9f14e12168beac3e25f5d2","analyzedAt":"2026-08-15T11:13:23.905Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}