Error "Field 'id' doesn't have a default value" when inserting data into a table after starting the cluster using playground v7.2.0

translator_bot · June 22, 2024, 3:08am

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

Original topic: 使用 playground v7.2.0 启动集群，往表插入数据报Field ‘id’ doesn’t have a default value错误

| username: TiDBer_4ZtoEU7K

[TiDB Usage Environment] Development environment (TiDB is running in Ubuntu 22.04.2 LTS x86_64 (Py3.7.8) environment)
[TiDB Version] v7.2.0
[Operation]

Below is the operation to export data from version v7.1.0

Export478×502 23.3 KB

The exported file is my_db.sql, as shown below
my_db.sql (2.5 KB)

My SQL statement for creating the table is as follows. Why does the CREATE statement in the exported SQL file look like this according to the above export process?

CREATE TABLE `system` (
  `id` bigint(20) AUTO_RANDOM NOT NULL COMMENT 'id',
  `name` varchar(100) NOT NULL COMMENT 'system name',
  `code`  varchar(50) NOT NULL COMMENT 'system identifier',
  `url`  varchar(240) DEFAULT NULL COMMENT 'pc website',
  `wap_url`  varchar(240) DEFAULT NULL COMMENT 'mobile website',
  `description`  varchar(200) DEFAULT NULL COMMENT 'system description',
  `status` smallint(6) NOT NULL COMMENT 'system status 1 normal, 2 closed',
  `err_info` varchar(200) DEFAULT NULL COMMENT 'status description, explained here when system status is 2',
  `create_at` bigint(20) DEFAULT '0' COMMENT 'creation time',
  `update_at` bigint(20) DEFAULT '0' COMMENT 'update time',
  PRIMARY KEY (`id`) CLUSTERED,
  UNIQUE KEY `uniq_system_name_idx` (`name`),
  UNIQUE KEY `uniq_system_code_idx` (`code`),
  KEY `key_system_create_at_idx` (`create_at`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'system table';

Create a database in v7.2.0

CREATE DATABASE my_text_db

Then import my_db.sql into the newly created v7.2.0 database

Import1
Import2

Then in v7.2.0, directly using the INSERT statement or directly inserting data in Navicat reports the following error

INSERT INTO system (`name`, `code`, `url`, `description`, `status`) VALUE ("aaa", "aaa", "aaa", "aaa", 1)

Error:
Field 'id' doesn't have a default value

translator_bot · June 22, 2024, 3:08am

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

I tried it, no problem.

root@127.0.0.1:4000[(none)]>use test
Database changed
root@127.0.0.1:4000[test]>select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v7.2.0 |
+--------------------+
1 row in set (0.00 sec)

root@127.0.0.1:4000[test]>CREATE TABLE `my_table` (
    ->   `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
    ->   `name` varchar(100) NOT NULL,
    ->   `code` varchar(50) NOT NULL,
    ->   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
    ->   UNIQUE KEY `uniq_system_name_idx` (`name`),
    ->   UNIQUE KEY `uniq_system_code_idx` (`code`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='test';
Query OK, 0 rows affected, 1 warning (0.12 sec)

root@127.0.0.1:4000[test]>show create table my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `uniq_system_name_idx` (`name`),
  UNIQUE KEY `uniq_system_code_idx` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='test'
1 row in set (0.01 sec)

root@127.0.0.1:4000[test]>INSERT INTO my_table( name, code ) value ('test','website');
Query OK, 1 row affected (0.02 sec)

root@127.0.0.1:4000[test]>select * from my_table;
+---------------------+--------+---------+
| id                  | name   | code    |
+---------------------+--------+---------+
| 4611686018427387905 | test   | website |
+---------------------+--------+---------+
1 row in set (0.00 sec)

translator_bot · June 22, 2024, 3:08am

| username: TiDBer_4ZtoEU7K | Original post link

Yes, you are right. My post has been updated, which is why the issue “Field ‘id’ doesn’t have a default value” appeared.

translator_bot · June 22, 2024, 3:08am

| username: xfworld | Original post link

How did you import it? BR or dumping?

translator_bot · June 22, 2024, 3:08am

| username: tidb菜鸟一只 | Original post link

After testing, after importing the old data from TiDB v7.1.0, running the mentioned INSERT will result in this error.
Can you share the table structure of system_test in both 7.1 and 7.2? There is no error when inserting in 7.1? The error only occurs after migrating to 7.2, is that correct?

translator_bot · June 22, 2024, 3:08am

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

I still don’t quite understand your complete operation process. Can you describe the reproduction process in detail?

translator_bot · June 22, 2024, 3:08am

| username: zhanggame1 | Original post link

If the old data was imported, was the table structure imported as well or did you create it using the above statement? If the table structure was imported, use SHOW CREATE TABLE my_table to check the table creation statement and compare to see if there are any differences.

translator_bot · June 22, 2024, 3:08am

| username: TiDBer_4ZtoEU7K | Original post link

The post has been updated. Sorry for the confusion caused by the unclear writing.

translator_bot · June 22, 2024, 3:08am

| username: TiDBer_4ZtoEU7K | Original post link

The post has been updated. I’m sorry that the post was not clear enough and caused inconvenience to everyone.

translator_bot · June 22, 2024, 3:08am

| username: TiDBer_4ZtoEU7K | Original post link

The post has been updated.

translator_bot · June 22, 2024, 3:08am

| username: TiDBer_4ZtoEU7K | Original post link

The post was not clear enough and caused confusion. The post has been updated.

translator_bot · June 22, 2024, 3:08am

| username: TiDB_C罗 | Original post link

The id column of the system table does not have a default.

translator_bot · June 22, 2024, 3:08am

| username: TiDBer_4ZtoEU7K | Original post link

You are right, now there is a new problem. The SQL statement I used to create the table is as follows. Why does the CREATE statement in the exported SQL file look like this when following the export process mentioned above?

CREATE TABLE `system` (
  `id` bigint(20) AUTO_RANDOM NOT NULL COMMENT 'id',
  `name` varchar(100) NOT NULL COMMENT 'system name',
  `code`  varchar(50) NOT NULL COMMENT 'system identifier',
  `url`  varchar(240) DEFAULT NULL COMMENT 'pc website',
  `wap_url`  varchar(240) DEFAULT NULL COMMENT 'mobile website',
  `description`  varchar(200) DEFAULT NULL COMMENT 'system description',
  `status` smallint(6) NOT NULL COMMENT 'system status 1 normal, 2 closed',
  `err_info` varchar(200) DEFAULT NULL COMMENT 'status description, explained here when system status is 2',
  `create_at` bigint(20) DEFAULT '0' COMMENT 'creation time',
  `update_at` bigint(20) DEFAULT '0' COMMENT 'update time',
  PRIMARY KEY (`id`) CLUSTERED,
  UNIQUE KEY `uniq_system_name_idx` (`name`),
  UNIQUE KEY `uniq_system_code_idx` (`code`),
  KEY `key_system_create_at_idx` (`create_at`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'system table';

Is it not possible to back up the database in this way?

translator_bot · June 22, 2024, 3:08am

| username: TiDB_C罗 | Original post link

My understanding: You used Navicat to export and then import, and it couldn’t be inserted properly. It might be an issue with Navicat. You can try using MySQL Workbench instead.

translator_bot · June 22, 2024, 3:08am

| username: tidb菜鸟一只 | Original post link

This is an issue with your Navicat. I tested it with SQLyog and had no problems. You can try using a different tool or a higher version of Navicat.

/*
SQLyog Ultimate v12.3.1 (64 bit)
MySQL - 5.7.25-TiDB-v5.4.3 : Database - pingcap

*/

/*!40101 SET NAMES utf8 */;

/!40101 SET SQL_MODE=‘’/;

/*!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 */;
USE pingcap;

/*Table structure for table system */

DROP TABLE IF EXISTS system;

CREATE TABLE system (
id bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */ COMMENT ‘id’,
name varchar(100) NOT NULL COMMENT ‘system name’,
code varchar(50) NOT NULL COMMENT ‘system identifier’,
url varchar(240) DEFAULT NULL COMMENT ‘pc website’,
wap_url varchar(240) DEFAULT NULL COMMENT ‘mobile website’,
description varchar(200) DEFAULT NULL COMMENT ‘system description’,
status smallint(6) NOT NULL COMMENT ‘system status 1 normal, 2 closed’,
err_info varchar(200) DEFAULT NULL COMMENT ‘status description, explained here when system status is 2’,
create_at bigint(20) DEFAULT ‘0’ COMMENT ‘creation time’,
update_at bigint(20) DEFAULT ‘0’ COMMENT ‘update time’,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY uniq_system_name_idx (name),
UNIQUE KEY uniq_system_code_idx (code),
KEY key_system_create_at_idx (create_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=‘system table’;

/*Data for the table system */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

translator_bot · June 22, 2024, 3:08am

| username: cassblanca | Original post link

It should be a Navicat issue. Currently, TiDB is 100% compatible with the MySQL 5.7 protocol, but other versions may not be 100% compatible.

translator_bot · June 22, 2024, 3:08am

| username: TiDBer_4ZtoEU7K | Original post link

This post was implemented using SQLyog and also encountered issues,
https://asktug.com/t/topic/1010051/1

translator_bot · June 22, 2024, 3:08am

| username: Billmay表妹 | Original post link

Which version of Navicat is it?

translator_bot · June 22, 2024, 3:08am

| username: TiDBer_4ZtoEU7K | Original post link

Navicat v16.0.13

translator_bot · June 22, 2024, 3:08am

| username: caiyfc | Original post link

I didn’t understand your post. Did you mention two issues in total? One is that the exported table structure is different, and the other is that the exported SQL reports an error when executed in version 7.2?
I’m using Navicat 15, and everything is normal. I haven’t encountered any issues.