Can't add columns to a table with an AUTO_INCREMENT column that isn't primary key

[serhiicherepanov]

The following schema works fine in the mysql, but not in the dolt db.

CREATE TABLE `about` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'draft',
  `date_created` timestamp NULL DEFAULT NULL,
  `date_updated` timestamp NULL DEFAULT NULL,
  `url_key` varchar(255) NOT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `about_url_key_unique` (`url_key`),
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I need the numeric id field in the table to support legacy code, but the primary key which we use is uuid.

[zachmu]

I believe dolt currently requires that the auto increment column is a primary key column, but it shouldn't be that tricky to fix.

Is this blocking you getting dolt into production?

[serhiicherepanov]

@zachmu no, I am not blocked

I just interested to use Dolt for a local development and generate the diff file to apply by migrations tool on production servers that runs mariadb/mysql.

[fulghum]

Closing this one as a duplicate of: https://github.com/dolthub/dolt/issues/2981

@JCOR11599 just started looking at #2981 today and will have updates on that issue as we make progress.

[serhiicherepanov]

Hi @fulghum, @zachmu, Unfortunately the issue still exists, but appear only in case when I try to alter table

To reproduce it just run following query:

CREATE TABLE `about` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'draft',
  `date_created` timestamp NULL DEFAULT NULL,
  `date_updated` timestamp NULL DEFAULT NULL,
  `url_key` varchar(255) NOT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `about_url_key_unique` (`url_key`),
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `about` ADD `background_color` varchar(255) DEFAULT NULL;

[fulghum]

Thanks for reopening @sergeycherepanov. I was able to repro the error – it looks like there's a bug in the validation logic that won't let us add more columns to a table with non-pk auto_increment column.

@JCOR11599 – Could you please take a look at this and see what's going on, since you recently looked at the fix for expanding usage of auto_increment columns?

[jycor]

Hey @sergeycherepanov, the fix for this should be in our latest release