无法还原几何备份MySQL 5.7错误 [英] Cannot Restore Geometry Backup MySQL 5.7 Error

问题描述

我一直在将网站从Mysql 5.6升级到5.7.从mysqldump还原备份时，该备份在Mysql 5.1-5.6下已经工作了10年(未更改)，但在MySQL 5.7下不再起作用.

I've been upgrading a website from Mysql 5.6 to 5.7. When restoring a backup from mysqldump, which has worked for 10 years (unchanged) under Mysql 5.1-5.6, it no longer works under MySQL 5.7.

具体来说，几何数据的第一行无法还原:

Specifically, the first row of geometry data fails the restore:

ERROR 1416 (22003) at line 1580 Cannot get geometry object from data you send to the GEOMETRY field

因此，此是有效的几何形状，但不再存在.

So, this is valid geometry, but not any more.

尝试的补救措施:

• 将mysqldump切换到--hex-blob
• 在导入之前尝试使用astext( <some geometry> )
• 疲倦的从备份中加载各种几何行，全部失败

• Switch mysqldump to --hex-blob
• Try using astext( <some geometry> ) before importing
• Tired hand loading various geometry rows from the backup, all fail

推荐答案

看起来MySQL 5.7在几何类型上比MySQL 5.6更严格.因此，在5.6中有效的数据现在在5.7中无效.

It looks like MySQL 5.7 is more strict in Geometry types than MySQL 5.6. As such, data that was valid in 5.6 is now invalid in 5.7.

这是对版本 MySQL错误#76337 的修复. a href ="https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html#mysqld-5-7-8-spatial-support" rel ="nofollow noreferrer> MySQL 5.7.8 .

This was the fix to MySQL Bug #76337, in release MySQL 5.7.8.

在这种情况下，LINESTRING被存储在类型为POINT的列中.这工作了将近十年，但仅此而已.将列更改为LINESTRING类型可以解决上述加载错误.

In this case, a LINESTRING was being stored in a column of type POINT. This worked for nearly a decade, but no more. Changing the column to type LINESTRING fixed the above loading error.

-- The Fix - run on MySQL 5.6 database before Upgrade/Export
ALTER TABLE routes MODIFY COLUMN route_path LINESTRING;

其他故障模式

此错误还显示在几何列上，这些列能够保留NULL几何(但不能正式为NULL). MySQL IS NULL会说不为null，但asText( myGeo )在 MySQL 5.7 下返回了NULL.在MySQL 5.6中将它们导出到字符串返回''，空字符串.因此，从5.6输出的''几何是5.7的无效输入.

This bug also manifest on geometric columns where they were able to persist NULL geometries (but not being officially NULL). MySQL IS NULL would say not null, but asText( myGeo ) returned NULL under MySQL 5.7. Exporting these to a string in MySQL 5.6 returned '', empty string. Thus the '' geometry output from 5.6 was and invalid input for 5.7.

解决方法是将这些内容归零.

The fix was to null these out.

-- Convert NULL geometries to actual NULL's
UPDATE myTable SET myGeo = NULL WHERE asText(myGeo) IS NULL;

这篇关于无法还原几何备份MySQL 5.7错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！