Duplicate Myisam Table: Spatial Index Lost

[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