代码之家 › 专栏 › 技术社区 › snowindy
MySQL-10.2中的非内容外键

innodb mariadb foreign-keys mysql
snowindy · 技术社区 · 7 年前
看起来我正面临外键约束错误。很奇怪,从没见过这样的事。
STUDIO_STAGE_ITEM_PARSE_TASKS ,它必须存在于 STUDIO_STAGE_ITEMS 但事实并非如此。
MariaDB怎么能这样?
我们在awsrds中使用MariaDB 10.2。
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------------------------------------+
| Variable_name           | Value                                                      |
+-------------------------+------------------------------------------------------------+
| innodb_version          | 5.7.20                                                     |
| protocol_version        | 10                                                         |
| slave_type_conversions  |                                                            |
| version                 | 10.2.12-MariaDB-log                                        |
| version_comment         | MariaDB Server                                             |
| version_compile_machine | x86_64                                                     |
| version_compile_os      | Linux                                                      |
| version_malloc_library  | jemalloc 3.3.1-0-g9ef9d9e8c271cdf14f664b871a8f98c827714784 |
| version_ssl_library     | OpenSSL 1.0.1k-fips 8 Jan 2015                             |
| wsrep_patch_version     | wsrep_25.21                                                |
+-------------------------+------------------------------------------------------------+
10 rows in set (0.00 sec)


mysql> show create table STUDIO_STAGE_ITEM_PARSE_TASKS;

CREATE TABLE `STUDIO_STAGE_ITEM_PARSE_TASKS` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `stage_Item_uuid` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `database_uuid` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `status_uuid` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `active` bit(1) NOT NULL,
  `DATE_CREATED` timestamp NULL DEFAULT NULL,
  `DATE_UPDATED` timestamp NULL DEFAULT NULL,
  `metadata` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE_STAGE_ITEM_PARSE_TASKS_ITEM` (`stage_Item_uuid`),
  UNIQUE KEY `UNIQUE_STAGE_ITEM_PARSE_TASK_UUID` (`uuid`),
  KEY `FK_STAGE_ITEM_PARSE_TASK_ATASK` (`status_uuid`),
  KEY `FK_STAGE_ITEM_PARSE_TASK_db` (`database_uuid`),
  CONSTRAINT `FK_STAGE_ITEM_PARSE_TASK_ATASK` FOREIGN KEY (`status_uuid`) REFERENCES `STUDIO_ASYNC_TASKS` (`uuid`),
  CONSTRAINT `FK_STAGE_ITEM_PARSE_TASK_ST_ITEM` FOREIGN KEY (`stage_Item_uuid`) REFERENCES `STUDIO_STAGE_ITEMS` (`uuid`),
  CONSTRAINT `FK_STAGE_ITEM_PARSE_TASK_db` FOREIGN KEY (`database_uuid`) REFERENCES `partner` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=3569 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 


mysql> show create table STUDIO_STAGE_ITEMS;
CREATE TABLE `STUDIO_STAGE_ITEMS` (
  `uuid` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `database_uuid` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `status_uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `file_Size` bigint(20) NOT NULL,
  `file_Name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `raw_Items_Count` bigint(20) NOT NULL,
  `parsed_Items_Count` bigint(20) NOT NULL,
  `fileId` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `active` bit(1) NOT NULL,
  `matched_Items_Count` bigint(20) NOT NULL DEFAULT 0,
  `newly_Added_Items_Count` bigint(20) NOT NULL DEFAULT 0,
  `DATE_CREATED` timestamp NULL DEFAULT NULL,
  `DATE_UPDATED` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  KEY `fk_015_db` (`database_uuid`),
  KEY `fk_017_status` (`status_uuid`),
  CONSTRAINT `fk_015_db` FOREIGN KEY (`database_uuid`) REFERENCES `partner` (`uuid`),
  CONSTRAINT `fk_017_status` FOREIGN KEY (`status_uuid`) REFERENCES `STUDIO_ASYNC_TASKS` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |


mysql> select * from STUDIO_STAGE_ITEM_PARSE_TASKS where uuid = '532cc618-5cab-4eae-a934-d3744644dc1e';
+------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------+---------------------+--------------+----------+
| id   | uuid                                 | stage_Item_uuid                      | database_uuid                        | status_uuid                          | active | DATE_CREATED        | DATE_UPDATED | metadata |
+------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------+---------------------+--------------+----------+
| 3560 | 532cc618-5cab-4eae-a934-d3744644dc1e | 63ffcb60-07c6-4e5a-aedc-89904f13c708 | 63086f0e-a0ea-45a2-ad1c-6096edc1b5a1 | 4cdad25b-2fd1-11e9-aa5f-02427e6aefff |       | 2019-02-13 20:52:39 | NULL         | {}       |
+------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------+---------------------+--------------+----------+
1 row in set (0.00 sec)

mysql> select * from STUDIO_STAGE_ITEMS where uuid = '63ffcb60-07c6-4e5a-aedc-89904f13c708';
Empty set (0.00 sec)
0 回复 | 直到 7 年前