ERROR 1364 (HY000): Field 'xxx' doesn't have a default value

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: ERROR 1364 (HY000): Field ‘xxx’ doesn’t have a default value

| username: netcaster1

【TiDB Environment】Dev
【TiDB Version】v6.1.0
【Problem Encountered】
Due to the inability to use the Create Table select command, I used show create table to get the table definition, then manually created the table. When inserting data, I encountered ERROR 1364 (HY000): Field ‘xxx’ doesn’t have a default value.
【Reproduction Path】Can be reproduced at any time
【Problem Phenomenon and Impact】

  1. First, create the original table
    create table testrb(id bigint(20) auto_random primary key, name varchar(2) default null);

  2. Insert record
    MySQL [raydb]> insert into testrb(name) values(‘tr’);
    Query OK, 1 row affected (0.031 sec)

MySQL [raydb]> select * from testrb;
±--------------------±-----+
| id | name |
±--------------------±-----+
| 2017612633061982209 | tr |
±--------------------±-----+
1 row in set (0.003 sec)

  1. Use show create table to get the definition
    MySQL [raydb]> show create table testrb;
    ±-------±------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------+
    | Table | Create Table |
    ±-------±------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------+
    | testrb | CREATE TABLE testrb (
    id bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
    name varchar(2) DEFAULT NULL,
    PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
    ±-------±------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------+
    1 row in set (0.005 sec)

  2. Then only change the table name, copy the definition, and manually execute
    MySQL [raydb]> CREATE TABLE testrb2 (
    → id bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
    → name varchar(2) DEFAULT NULL,
    → PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */
    → ) ;
    Query OK, 0 rows affected (0.122 sec)

  3. Execute the same insert with only the table name different
    MySQL [raydb]> insert into testrb2(name) values(‘tr’);
    ERROR 1364 (HY000): Field ‘id’ doesn’t have a default value

  4. Of course, specifying the ID value is fine, but it loses the meaning of auto_random
    MySQL [raydb]> insert into testrb2(id, name) values(‘1221321’, ‘tr’);
    Query OK, 1 row affected (0.011 sec)

MySQL [raydb]> select * from testrb2;
±--------±-----+
| id | name |
±--------±-----+
| 1221321 | tr |
±--------±-----+
1 row in set (0.004 sec)

If the show create table output is not equivalent to manual creation, then it loses its meaning. Is there any issue with the AUTO_RANDOM situation?

【Attachments】

  1. TiUP Cluster Display Information
  2. TiUP Cluster Edit Config Information
  3. TiDB- Overview Monitoring
2
| username: 我是咖啡哥 | Original post link

Did you forget to add --comments when connecting, causing /*T![auto_rand] AUTO_RANDOM(5) */ to not take effect? Check the table structure with show create table.

3
| username: netcaster1 | Original post link

Yes, because auto_random is in the comments. But doesn’t this make show create table not equivalent to the actual table? If we use mysqldump to export the definition, there will be problems.

mysqldump -h 192.168.0.xxx -P 4000 -u root -pxxx -B raydb -d > test.log

DROP TABLE IF EXISTS `testrb`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `testrb` (
  `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  `name` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=30001 */;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `testrb2`
--

DROP TABLE IF EXISTS `testrb2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `testrb2` (
  `id` bigint(20) NOT NULL,
  `name` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
4
| username: netcaster1 | Original post link

I tried using dumpling and got the same result.
tiup dumpling -B raydb -uroot --filetype sql -o /tmp/test.log -L dump.log -pxxx -d
[tidb@jeeppoc ~]$ cat /tmp/test.log/raydb.testrb-schema.sql
/!40101 SET NAMES binary/;
CREATE TABLE testrb (
id bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
name varchar(2) DEFAULT NULL,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=30001 */;

5
| username: tidb狂热爱好者 | Original post link

The comment didn’t take effect. Use the -c command when connecting to MySQL.

6
| username: 我是咖啡哥 | Original post link

Just remember to add -c when connecting.

7
| username: netcaster1 | Original post link

Thank you, the problem is solved.

8
| username: system | Original post link

This topic was automatically closed 1 minute after the last reply. No new replies are allowed.