Drizzle Team•2y ago

0.27.2 mysql:push

mysql:push in 0.27.2 is trying to alter my primaryKey with no schema changes. it also seems to try and drop a constraint that doesn't exist yet? I think it's something to do with the unique index changes

6 Replies

thdxrOP•2y ago

ALTER TABLE `workspace` DROP PRIMARY KEY
ALTER TABLE `workspace` MODIFY COLUMN `time_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `workspace` MODIFY COLUMN `time_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `workspace` ADD PRIMARY KEY(`id`);

ALTER TABLE `workspace` DROP PRIMARY KEY
ALTER TABLE `workspace` MODIFY COLUMN `time_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `workspace` MODIFY COLUMN `time_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `workspace` ADD PRIMARY KEY(`id`);

hm it might be unrelated to the upgrade not sure what i changed to cause this though and this is the current schema

CREATE TABLE `workspace` (
    `id` char(24) NOT NULL,
    `time_created` timestamp NOT NULL DEFAULT current_timestamp(),
    `time_updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    `time_deleted` timestamp NULL,
    `slug` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE `workspace` (
    `id` char(24) NOT NULL,
    `time_created` timestamp NOT NULL DEFAULT current_timestamp(),
    `time_updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    `time_deleted` timestamp NULL,
    `slug` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

i'm not sure why there's any diff ok yeah there's two layers of bugs so on drizzle-kit 0.19.3 every time i run mysql:push it drops the pk and recreates it (it's perceving some change)

philbookst•2y ago

i got the same problem, did you find a fix for it or do we need to wait for a new version?

Andrii Sherman•2y ago

👀 checking that out @thdxr is it possibly to get ts schema you have? will help me to debug it properly I can compose it from sql you've sent. But want to be precise

philbookst•2y ago

I think you can use any schema with uniqueIndex added Here is my schema for the failing table as reference though:

export const Account = mysqlTable(
    "Account",
    {
        id: varchar("id", { length: 50 }).primaryKey().notNull(),
        tenantId: varchar("tenantId", { length: 100 }).notNull(),
        createdAt: timestamp("createdAt").defaultNow(),
        updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow(),
        role: mysqlEnum("role", ["ADMIN", "USER"]).default("USER").notNull(),
        email: varchar("email", { length: 100 }).notNull(),
        emailVerified: timestamp("emailVerified"),
        passwordHash: varchar("passwordHash", { length: 150 }).notNull(),
        userAuthToken: varchar("userAuthToken", { length: 50 }).notNull(),
        passwordResetToken: varchar("passwordResetToken", { length: 50 }),
        passwordResetExpiry: timestamp("passwordResetExpiry"),
        userId: varchar("userId", { length: 50 }).notNull()
    },
    (table) => {
        return {
            userIdKey: uniqueIndex("Account_userId_key").on(table.userId),
            passwordResetTokenKey: uniqueIndex("Account_passwordResetToken_key").on(
                table.passwordResetToken
            ),
            tenantIduserAuthTokenIdx: uniqueIndex("Account_tenantId_userAuthToken_idx").on(
                table.userAuthToken,
                table.tenantId
            ),
            tenantIdUserEmailIdx: uniqueIndex("Account_tenantId_userEmail_idx").on(
                table.tenantId,
                table.email
            )
        };
    }
);

export const Account = mysqlTable(
    "Account",
    {
        id: varchar("id", { length: 50 }).primaryKey().notNull(),
        tenantId: varchar("tenantId", { length: 100 }).notNull(),
        createdAt: timestamp("createdAt").defaultNow(),
        updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow(),
        role: mysqlEnum("role", ["ADMIN", "USER"]).default("USER").notNull(),
        email: varchar("email", { length: 100 }).notNull(),
        emailVerified: timestamp("emailVerified"),
        passwordHash: varchar("passwordHash", { length: 150 }).notNull(),
        userAuthToken: varchar("userAuthToken", { length: 50 }).notNull(),
        passwordResetToken: varchar("passwordResetToken", { length: 50 }),
        passwordResetExpiry: timestamp("passwordResetExpiry"),
        userId: varchar("userId", { length: 50 }).notNull()
    },
    (table) => {
        return {
            userIdKey: uniqueIndex("Account_userId_key").on(table.userId),
            passwordResetTokenKey: uniqueIndex("Account_passwordResetToken_key").on(
                table.passwordResetToken
            ),
            tenantIduserAuthTokenIdx: uniqueIndex("Account_tenantId_userAuthToken_idx").on(
                table.userAuthToken,
                table.tenantId
            ),
            tenantIdUserEmailIdx: uniqueIndex("Account_tenantId_userEmail_idx").on(
                table.tenantId,
                table.email
            )
        };
    }
);

i've played with adding a dummy constraint ALTER TABLE Account ADD CONSTRAINT Account_userId_key CHECK (LENGTH(id) > 0); which fixed the error for Account_userId_key but then a new error for Account_passwordResetToken_key popped up it deleted the dummy constraint when trying db:push

Andrii Sherman•2y ago

Thanks! I’ll try to fix that asap just reproduced the behaviour Found an issue with uniques, I'll patch update it in 1hr, need to test a bit more @philbookst can you please check this tag? drizzle-kit@mysql-uniques Should fix your issue. If yes - going to release it The problem was with unique constraints. In MySQL, there is no difference between them. Therefore, the logic was changed to treat MySQL unique indexes the same as unique constraints for diffs. The same was done for SQLite, but it was my mistake to overlook it in the MySQL handlers. @thdxr found your issue with primary key as well. Will include it Just updated drizzle-kit@mysql-uniques with primary key fixes. The issue was with incorrect querying of the information schema. Drizzle Kit was examining the column definition and the column_key setting from the information_schema.columns table. However, it turns out that the column_key value can indicate PRIMARY even for columns that are both UNIQUE and NOT_NULL, leading to a logical misinterpretation. I have made changes to address this problem by utilizing the information_schema.table_constraints table, which provides a proper distinction between primary keys and columns with the combination of UNIQUE and NOT_NULL

thdxrOP•2y ago

cool thank you!

Gaming

Programming

0.27.2 mysql:push