Playground v7.2.0 Cluster Startup: Data Backup and Restore Not Possible Using SQLyog or Navicat Tools

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

Original topic: playground v7.2.0 启动集群,使用SQLyog或者Navicat工具,都不能实现数据的备份与恢复

| username: TiDBer_4ZtoEU7K

【 TiDB Environment 】Development environment (TiDB is running in Ubuntu 22.04.2 LTS x86_64 (Py3.7.8) environment)
【 TiDB Version 】v7.2.0
【 SQLyog Version 】v12.14
【 Reproduction Process 】

  1. First, export the existing data table

  2. The exported SQL file code is as follows:

CREATE DATABASE `my_db_test`;
USE `my_db_test`;

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 /*T![auto_rand_base] AUTO_RANDOM_BASE=30001 */ COMMENT='system table';

insert into `system`(`id`,`name`,`code`,`url`,`wap_url`,`description`,`status`,`err_info`,`create_at`,`update_at`) values 
  1. Run the above code to restore the data, and the following error occurs
Error Code: 8216
Invalid auto random: Explicit insertion on auto_random column is disabled. Try to set @@allow_auto_random_explicit_insert = true.
  1. If you use Navicat to export and then import, the problem mentioned in this post will occur

Can’t commonly used tools perform backup and restore for TiDB?

| username: TiDBer_4ZtoEU7K | Original post link

The issue is that when the id column is set with the AUTO_RANDOM parameter, it cannot recognize externally input id values. Databases like MySQL and MongoDB also have auto-increment parameters, but they don’t encounter this problem. Shouldn’t the official team address this? This issue significantly affects the user experience.

| username: Billmay表妹 | Original post link

You can take a look at this document written by @啦啦啦啦啦:

| username: Billmay表妹 | Original post link

The issue with third-party tools is actually unrelated to TiDB.

You can make suggestions to the tool provider~

It is recommended to use BR for backup and recovery.

| username: caiyfc | Original post link

Just set the global allow_auto_random_explicit_insert = true. TiDB has already provided the parameter, just set it and you’re good to go. What else is there to solve? :rofl:

| username: 啦啦啦啦啦 | Original post link

If the data volume is not particularly large, isn’t it better to use dumpling + lightning for backup and recovery? :kissing_heart:

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

This cannot be fixed.

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

SET GLOBAL allow_auto_random_explicit_insert=TRUE;
That’s it.

| username: TiDBer_4ZtoEU7K | Original post link

Just use your method.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.