Specifying an AUTO_INCREMENT primary key results in spurious duplicate primary key given error

[timsehn]

SQL:

DROP DATABASE IF EXISTS `magento_integration_tests`;

CREATE DATABASE IF NOT EXISTS magento_integration_tests;

USE magento_integration_tests;

DROP TABLE IF EXISTS customer_group;

CREATE TABLE `customer_group` (

`customer_group_id` int unsigned NOT NULL AUTO_INCREMENT,

`customer_group_code` varchar(32) NOT NULL COMMENT 'Customer Group Code',

`tax_class_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'Tax Class ID',

PRIMARY KEY (`customer_group_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;

INSERT INTO `customer_group` VALUES

(0,'NOT LOGGED IN',3),

(1,'General',3),

(2,'Wholesale',3),

(3,'Retailer',3),

(4,'Advisor',3);

Repro:

PS C:\Users\timse\dolthub\dolt> cd .\test_ai_key_specified\
PS C:\Users\timse\dolthub\dolt\test_ai_key_specified> notepad test.sql
PS C:\Users\timse\dolthub\dolt\test_ai_key_specified> dolt init
Successfully initialized dolt data repository.
PS C:\Users\timse\dolthub\dolt\test_ai_key_specified> Get-Content .\test.sql | dolt sql
Database changed
error on line 21 for query

INSERT INTO `customer_group` VALUES

(0,'NOT LOGGED IN',3),

(1,'General',3),

(2,'Wholesale',3),

(3,'Retailer',3),

(4,'Advisor',3): duplicate primary key given: [1]

[zachmu]

MySQL has the same behavior.

This happens because a 0 value for an AUTO_INCREMENT column gets interpreted the same as null, so it uses the next auto increment value (1). The next row being inserted also specifies 1 for that column, which leads to the duplicate key error.

For inserting into auto_increment columns, either use NULL to get a value assigned by the database, don't specify the column in the INSERT statement at all (preferred solution), or make every value inserted 1 or greater.

[palamar]

@zachmu
I can provide example of the real word MySql dump that was created via mysqldump and can be imported back into MySql 8.0.27.
Most probably that it's related to the MySql extension of the SQL /*!40000 ALTER TABLE customer_group DISABLE KEYS */; I think it changes default MySql behavior.

-- MySQL dump 10.13  Distrib 8.2.0, for macos14.0 (arm64)
--
-- Host: 127.0.0.1    Database: magento
-- ------------------------------------------------------
-- Server version	8.0.27

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


--
-- Table structure for table `customer_group`
--

DROP TABLE IF EXISTS `customer_group`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `customer_group` (
  `customer_group_id` int unsigned NOT NULL AUTO_INCREMENT,
  `customer_group_code` varchar(32) NOT NULL COMMENT 'Customer Group Code',
  `tax_class_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'Tax Class ID',
  PRIMARY KEY (`customer_group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb3 COMMENT='Customer Group';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `customer_group`
--

LOCK TABLES `customer_group` WRITE;
/*!40000 ALTER TABLE `customer_group` DISABLE KEYS */;
INSERT INTO `customer_group` VALUES (0,'NOT LOGGED IN',3),(1,'General',3),(2,'Wholesale',3),(3,'Retailer',3),(4,'Advisor',3);
/*!40000 ALTER TABLE `customer_group` ENABLE KEYS */;
UNLOCK TABLES;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-03-12 12:19:02

[jycor]

Upon further investigation, it turns out we need to support SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
from /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

We'll get started on this right away

[jycor]

Hey @palamar, we've added support for SQL_MODE 'NO_AUTO_VALUE_ON_ZERO'.
The fix has been bumped to dolt main.

We'll cut a release with the fix today or tomorrow.