An exception occurred while migrating data

[VisionsOfIdiot]

Describe the bug When I migrated the sqlite file data to mysql8, I encountered an error that resulted in only a portion of the data being migrated, but the error could not be traced from the error log Expected behaviour What you expected.

Actual result

System Information 2024-04-29 14:46:28 INFO Transferring table device 100%|████████████████████████████████████████████████████████████████████████████████████████████████████| 6/6 [00:00<00:00, 129.17it/s] Traceback (most recent call last): File "/usr/local/python3/bin/sqlite3mysql", line 8, in sys.exit(cli()) File "/usr/local/python3/lib/python3.9/site-packages/click/core.py", line 1157, in call return self.main(*args, **kwargs) File "/usr/local/python3/lib/python3.9/site-packages/click/core.py", line 1078, in main rv = self.invoke(ctx) File "/usr/local/python3/lib/python3.9/site-packages/click/core.py", line 1434, in invoke return ctx.invoke(self.callback, **ctx.params) File "/usr/local/python3/lib/python3.9/site-packages/click/core.py", line 783, in invoke return __callback(*args, **kwargs) File "/usr/local/python3/lib/python3.9/site-packages/sqlite3_to_mysql/cli.py", line 165, in cli SQLite3toMySQL( File "/usr/local/python3/lib/python3.9/site-packages/sqlite3_to_mysql/transporter.py", line 741, in transfer self._add_indices(table["name"]) File "/usr/local/python3/lib/python3.9/site-packages/sqlite3_to_mysql/transporter.py", line 421, in _add_indices if index["origin"] == "pk": KeyError: 'origin'

$ sqlite3mysql -f /text2sql/dataset/CSpider/database/device/device.sqlite -h XXXX -u XXX --mysql-password XXX -d cspider_device -l /script/test.log  --debug

This command is only available on v1.3.6 and greater. Otherwise, please provide some basic information about your system (Python version, operating system, etc.).

Additional context Add any other context about the problem here.

In case of errors please run the same command with --debug. This option is only available on v1.4.12 or greater.

[techouse]

Looks like the key name origin is problematic. Check if it's a reserved keyword https://dev.mysql.com/doc/refman/8.0/en/keywords.html

[VisionsOfIdiot]

I didn't use any keywords，here is my table structure

[techouse]

Hmm, looks like an index issue or the lack of the index's "origin" key.

Can you quickly run these SQLite commands

PRAGMA index_list("stock");
PRAGMA index_list("device");
PRAGMA index_list("shop");

and provide the output?

I would greatly appreciate it if you were to provide me with the DDL and the exact steps to replicate the error?

[VisionsOfIdiot]

I need to convert my sqlite file to a MySQL SQL file，so I converted it through the command line, and then an exception occurred Here's the command:

sqlite3mysql \
    -f /text2sql/dataset/CSpider/database/device/device.sqlite \
    -h XXXX \
    -u XXX \
    --mysql-password XXX \
    -d cspider_device \
    -l /script/test.log \
    --debug

Here's the DDL:

CREATE TABLE "device" (
  "Device_ID" int,
  "Device" text,
  "Carrier" text,
  "Package_Version" text,
  "Applications" text,
  "Software_Platform" text,
  PRIMARY KEY ("Device_ID")
);

CREATE TABLE "shop" (
  "Shop_ID" int,
  "Shop_Name" text,
  "Location" text,
  "Open_Date" text,
  "Open_Year" int,
  PRIMARY KEY ("Shop_ID")
);

CREATE TABLE "stock" (
  "Shop_ID" int,
  "Device_ID" int,
  "Quantity" int,
  PRIMARY KEY ("Shop_ID","Device_ID"),
  FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
  FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
);

[techouse]

I can't replicate your error. The conversion works just fine on my end resulting in this MySQL db

-- MySQL dump 10.13  Distrib 8.3.0, for macos14.2 (arm64)
--
-- Host: localhost    Database: device
-- ------------------------------------------------------
-- Server version	8.3.0

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

--
-- Current Database: `device`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `device` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `device`;

--
-- Table structure for table `device`
--

DROP TABLE IF EXISTS `device`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `device` (
  `Device_ID` int NOT NULL AUTO_INCREMENT,
  `Device` text COLLATE utf8mb4_general_ci,
  `Carrier` text COLLATE utf8mb4_general_ci,
  `Package_Version` text COLLATE utf8mb4_general_ci,
  `Applications` text COLLATE utf8mb4_general_ci,
  `Software_Platform` text COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`Device_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `device`
--

LOCK TABLES `device` WRITE;
/*!40000 ALTER TABLE `device` DISABLE KEYS */;
/*!40000 ALTER TABLE `device` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `shop`
--

DROP TABLE IF EXISTS `shop`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `shop` (
  `Shop_ID` int NOT NULL AUTO_INCREMENT,
  `Shop_Name` text COLLATE utf8mb4_general_ci,
  `Location` text COLLATE utf8mb4_general_ci,
  `Open_Date` text COLLATE utf8mb4_general_ci,
  `Open_Year` int DEFAULT NULL,
  PRIMARY KEY (`Shop_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `shop`
--

LOCK TABLES `shop` WRITE;
/*!40000 ALTER TABLE `shop` DISABLE KEYS */;
/*!40000 ALTER TABLE `shop` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `stock`
--

DROP TABLE IF EXISTS `stock`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stock` (
  `Shop_ID` int NOT NULL,
  `Device_ID` int NOT NULL,
  `Quantity` int DEFAULT NULL,
  PRIMARY KEY (`Shop_ID`,`Device_ID`),
  KEY `stock_FK_0_0` (`Device_ID`),
  CONSTRAINT `stock_FK_0_0` FOREIGN KEY (`Device_ID`) REFERENCES `device` (`Device_ID`),
  CONSTRAINT `stock_FK_1_0` FOREIGN KEY (`Shop_ID`) REFERENCES `shop` (`Shop_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `stock`
--

LOCK TABLES `stock` WRITE;
/*!40000 ALTER TABLE `stock` DISABLE KEYS */;
/*!40000 ALTER TABLE `stock` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!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-05-11 10:35:35

This is my setup

software	version
sqlite3-to-mysql	2.1.9
Operating System	Darwin 23.4.0
Python	CPython 3.12.0
MySQL	mysql Ver 8.3.0 for macos14.2 on arm64 (Homebrew)
SQLite	3.43.2
click	8.1.7
mysql-connector-python	8.3.0
pytimeparse2	1.7.1
simplejson	3.19.2
tabulate	0.9.0
tqdm	4.66.2