是否有可能“赶上"渔船.当我得到外键约束错误时，MySQL中的无效值? [英] Is it possible to "catch" the invalid value in MySQL when I get a foreign key constraint error?
问题描述
我正在将约15万行的CSV文件导入到MySQL(InnoDB)表中.
I'm importing a CSV file of ~150k rows into a MySQL (InnoDB) table.
在导入过程中的某个时刻，由于外键约束错误而导致失败.
At some point during the import, there's a failure because of a foreign key constraint error.
它显示发生故障的列:
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails
(`db`.`table`, CONSTRAINT `fk` FOREIGN KEY (`otherTableId`) REFERENCES `otherTable`
(`otherTableId`))
是否可以捕获"该值是什么?
Is it possible to "catch" what that value is?
我还没有尝试过任何东西，因为我不知道该怎么做. :P
I haven't tried anything yet, because I don't know what to try. :P
啊-我在MySQL文档中找到了以下命令:
Ahh - so I found the following command in the MySQL documentation:
SHOW ENGINE INNODB STATUS\G
我从该命令中找到了以下信息:
I found the following information from that command:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2014-01-22 16:27:26 2afaf98b8940 Transaction:
TRANSACTION 85952, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 944, OS thread handle 0x2afaf98b8940, query id 66223 System lock
{{QUERY}}
Foreign key constraint fails for table {TABLE INFO}:
,
CONSTRAINT `fk` FOREIGN KEY (`Id`) REFERENCES `table` (`id`)
Trying to add in child table, in index `fk` tuple:
DATA TUPLE: 2 fields;
0: len 11; hex 4163636573736f72794964; asc AccessoryId;;
1: len 4; hex 80000003; asc ;;
But in parent table `db`.`table`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 46494c544552; asc FILTER;;
1: len 6; hex 000000007300; asc s ;;
2: len 7; hex fb000001b01476; asc v;;
3: len 1; hex 46; asc F;;
4: len 9; hex 756e646566696e6564; asc undefined;;
我在这里寻找的是:
Trying to add in child table, in index `fk` tuple:
DATA TUPLE: 2 fields;
0: len 11; hex 4163636573736f72794964; asc AccessoryId;;
1: len 4; hex 80000003; asc ;;
我不知道'asc'在'AccessoryId'之前是什么意思，但这使我看到我并没有忽略CSV的第一行.
I don't know what the 'asc' means before 'AccessoryId', but that led me to seeing that I wasn't ignoring the first line of the CSV.
啊-我想了一会儿'asc'代表什么，并且由于它前面是十六进制，所以我认为它的意思是"ASCII"
Ahh - I thought for a minute about what the 'asc' stood for, and since there's hex just before it, I assumed it meant "ASCII"
我将"4163636573736f72794964"转换为ASCII并获得了"AccessoryId"，所以这只是我尝试导入的文本的十六进制值.
I converted the '4163636573736f72794964' into ASCII and got "AccessoryId", so that was just the HEX value of the text that I was trying to import.
所以答案是:
SHOW ENGINE INNODB STATUS\G
并搜索标题为LATEST FOREIGN KEY ERROR
您可以从那里获取信息.如果有人知道更好或自动的方式来检索此信息，我将接受它作为答案.
You can get your info from there. If anyone knows a better or automated way of retrieving this info, I'll accept it as an answer.
推荐答案
不幸的是，没有比从SHOW ENGINE INNODB STATUS中的LATEST FOREIGN KEY ERROR部分获取外键错误的更好的方法了.列信息显示为例如:
Unfortunately there is no better way to get the foreign key errors than from the LATEST FOREIGN KEY ERROR section in SHOW ENGINE INNODB STATUS. The column information is printed as e.g.:
Trying to add in child table, in index `fk` tuple:
DATA TUPLE: 2 fields;
0: len 11; hex 4163636573736f72794964; asc AccessoryId;;
1: len 4; hex 80000003; asc ;;
这将打印索引(fk)中的全部列，并且您猜到格式是:
This is printing the full set of columns from the index (fk), and as you guessed the format is:
<column index>:
len <length of bytes stored>;
hex <hex representation of bytes>;
asc <ascii representation of bytes>;;
更不幸的是，InnoDB对如何存储MySQL的列类型不够了解，无法为您提供合理的打印表示形式，因此某些值有些怪异"，例如80000003是存储为整数3的字节的十六进制表示(InnoDB在内部翻转高位).
Further unfortunately, InnoDB doesn't know enough about how MySQL's column types are stored to give you a reasonable printed representation, so some of the values are a bit "weird" e.g. 80000003 is the hex representation of the bytes stored for the integer 3 (InnoDB internally flips the high bit).
这篇关于是否有可能“赶上"渔船.当我得到外键约束错误时，MySQL中的无效值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
