MHSanaei/3x-ui · critical

drop legacy foreign key: %w

Error message

drop legacy foreign key: %w

What it means

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.

Source

Thrown at internal/database/migrate_data.go:115

	}
	defer dstSQL.Close()
	dstSQL.SetConnMaxLifetime(time.Hour)

	log.Println("Creating destination schema...")
	for _, m := range migrationModels() {
		if err := dst.AutoMigrate(m); err != nil {
			return fmt.Errorf("AutoMigrate %T: %w", m, err)
		}
	}

	totalRows := 0
	txErr := dst.Transaction(func(tx *gorm.DB) error {
		// AutoMigrate re-creates the legacy client_traffics -> inbounds foreign key,
		// but the running panel drops it (see dropLegacyForeignKeys) and tolerates
		// client_traffics rows whose inbound was deleted. Drop it here too so copying
		// such orphaned rows can't fail with an fk_inbounds_client_stats violation.
		if err := tx.Exec("ALTER TABLE client_traffics DROP CONSTRAINT IF EXISTS fk_inbounds_client_stats").Error; err != nil {
			return fmt.Errorf("drop legacy foreign key: %w", err)
		}

		// Empty the destination tables before copying: a fresh PostgreSQL DB
		// already holds an auto-seeded admin (id=1) from any prior panel start,
		// so a plain INSERT with explicit ids would collide on users_pkey. Only
		// the panel's own tables are cleared, and a failure anywhere in this
		// transaction rolls the clear back with everything else.
		if err := truncatePostgresTables(tx, migrationModels()); err != nil {
			return fmt.Errorf("clear destination tables: %w", err)
		}

		for _, m := range migrationModels() {
			n, err := copyTable(src, tx, m)
			if err != nil {
				return fmt.Errorf("copy %T: %w", m, err)
			}
			totalRows += n
			log.Printf("  %-32s %d rows", reflect.TypeOf(m).Elem().Name(), n)

View on GitHub (pinned to ad32144c42)

Solutions

1. Read the wrapped %w cause — it names the exact Postgres error (permission, syntax, connection).
2. 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).
3. Verify the statement manually: psql "$XUI_DB_DSN" -c 'ALTER TABLE client_traffics DROP CONSTRAINT IF EXISTS fk_inbounds_client_stats';
4. Migrate into a fresh empty database owned by the DSN user instead of a pre-provisioned shared one.

// before migrating, confirm the DSN role owns client_traffics
var owner string
row := pgDB.Raw("SELECT tableowner FROM pg_tables WHERE tablename = 'client_traffics'").Row()
if err := row.Scan(&owner); err == nil {
    var user string
    pgDB.Raw("SELECT current_user").Row().Scan(&user)
    if owner != user {
        return fmt.Errorf("client_traffics owned by %s, DSN user is %s", owner, user)
    }
}

err := RunSQLiteToPostgres(...)
if err != nil && strings.Contains(err.Error(), "drop legacy foreign key") {
    // surface the wrapped pq error; fix ownership/privileges, then re-run (tx rolled back, safe to retry)
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• clear destination tables: %w
• copy %T: %w
• destination DSN is required
• source sqlite not found at %s: %w
• open postgres destination: %w

AI-assisted analysis of MHSanaei/3x-ui@ad32144c42 (2026-08-15). Data as JSON: /api/errors/be9ca62175d23ed4. Report an issue: GitHub.