Duplicate Myisam Table: Spatial Index Lost

[Meinolf]

Hello,

when duplicating MYISAM tables with the function “Duplicat table structure/data” a SPATIAL INDEX on a field of the type geometrie is copied as a normal INDEX of BTREE type, that results in a severe, but sporadically occuring error: Selects with geometry function like MBRIntersects often give no result, depending on the values of the coordinates, especially on the Right-of-comma position values. So this missbehaviour of the new table is not obvious and the concluding errors only occur sometimes.

I am using SQLYog Ultimate 9.02

This is clearly an error of SQLYog, because the executed “create table” statement is wrong:

/*[11:58:33][ 93 ms]*/ CREATE TABLE `ECK`.`table_ok_copy` ( PRIMARY KEY(`gid`),KEY `geometrie`( `geometrie` (32)))ENGINE=MYISAM COLLATE = latin1_swedish_ci COMMENT = '' SELECT `gid`, `geometrie`, `objektid`, `zeichenvorschrift`, `geometrietyp` FROM `ECK`.`table_ok` WHERE 1 = 0;

The original table looks like this:

…..

TABLE Non_unique Key_name Seq_in_index Column_name COLLATION Cardinality Sub_part Packed NULL Index_type COMMENT

---

---

---

---

---

---

---

---

---

---

---

---

table_ok 0 PRIMARY 1 gid A 0 (NULL) (NULL) BTREE

table_ok 1 geometrie 1 geometrie A (NULL) 32 (NULL) SPATIAL

/*DDL Information*/

---

CREATE TABLE `table_ok` (

`gid` INT(11) NOT NULL DEFAULT '0',

`geometrie` GEOMETRY NOT NULL DEFAULT '',

`objektid` VARCHAR(15) DEFAULT NULL,

`zeichenvorschrift` VARCHAR(255) NOT NULL DEFAULT '0',

`geometrietyp` INT(2) NOT NULL DEFAULT '1',

PRIMARY KEY (`gid`),

SPATIAL KEY `geometrie` (`geometrie`)

) ENGINE=MYISAM DEFAULT CHARSET=latin1

Although the in SQLYog shown Create table construct of the table is ok, the executed Create table is wrong.

When Copying the table into another database its indices are created correct.

Please correct this annoying error, it's even an old error.

[peterlaursen]

We do not claim to support spatial datatypesl. But any copy of an existing table should work. And it should be possible to work with such tables as long as you don't manipulate those such data from SQLyog GUI.

But you are right that 'duplicate table' has some issues. You can see in HISTORY tab what we do. We execute

SHOW FULL FIELDS FROM …

SHOW TABLE STATUS FROM `test` …

SHOW KEYS FROM …

(Note there is no SHOW CREATE TABLE. We generate the schema for the new table dynamically).

This fails in some contexts. This is one (it seems). Other such contexts are special/third-party storage engines that have some special constructs or do not support the constructs of 'standard' MySQL storage engines (Maria/Aria engine is an example as it has an optional 'transactional YES|NO' parameter that no other engines have).

We have in our issue tracker already:http://code.google.com/p/sqlyog/issues/detail?id=1550

I think 'duplicate table' in its current form is not very well fit for today's storage engines. We should probably simply copy from SHOW CREATE TABLE (there is a small problem with Forign Keys however).

But we will also check if this particular issue can be resolved more easily and if either solution can be pushed into next release cycles (9.1 or 9.2)

[Meinolf]

'peterlaursen' wrote:

But we will also check if this particular issue can be resolved more easily and if either solution can be pushed into next release cycles (9.1 or 9.2)

Hello,

this issue is still unsolved in version 9.3!

original index:

…

SPATIAL KEY `geometrie` (`geometrie`)

…

History of duplicate table operation:

/*[16:09:36][7 ms]*/ SHOW FULL FIELDS FROM `se`.`bplan_alle_copy`;

/*[16:09:36][1 ms]*/ SHOW TABLE STATUS FROM `se` LIKE 'bplan_alle_copy';

/*[16:09:43][1 ms]*/ SHOW KEYS FROM `se`.`bplan_alle_copy`;

/*[16:09:43][7 ms]*/ CREATE TABLE `se`.`bplan_alle_copy_copy` ( PRIMARY KEY(`file`),KEY `overlay`( `overlay` ), KEY `origfile`( `origfile` ), KEY `plantyp`( `plantyp` ), KEY `geometrie`( `geometrie` (32)))ENGINE=MYISAM COLLATE = latin1_swedish_ci COMMENT = '' SELECT `file`, `geometrie`, `overlay`, `imagetype`, `subsample`, `sub_minscale`, `sub_maxscale`, `resample`, `res_minscale`, `res_maxscale`, `origfile`, `plantyp` FROM `se`.`bplan_alle_copy` WHERE 1 = 0;

When will it be solved?

M. Asshoff

[peterlaursen]

Thank you for the reminder. You also opened a ticket where I linked to http://code.google.com/p/sqlyog/issues/detail?id=1550.

I can only promise that we will discuss this week.

[Author]

Posts