MySQL / mariaDB phpmyadmin - multipolygon field export / import error-CodePudding

I exported my database from my server including a multipolygon field from MySQL 5.6.51 with phpmyadmin v4.7.9. The table:

CREATE TABLE `cities` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `code` varchar(4) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `longcode` varchar(5) DEFAULT NULL,
  `level` varchar(32) DEFAULT NULL,
  `border` multipolygon NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The border column contains multipolygons, each city's border in WKT format.

After exporting with phpmyadmin, the values in the border column are x0 (if the original field was empty) or ling binary codes. Setting the export option "Binary columns..." on the phpmyadmin export page changes noting for me.

While importing to localhost (XAMPP with mariaDB 10.4.22, phpmyadmin 5.1.1), after I created the same table, during the import I get the error #1054 - The '0x' column is invalid in field list. (or something like that, but in Hungarian, sorry, if not exactly that...)

How can I import my data?

Thanks.

Update:

(1, '1188', 'Abda', '11882', 'község', 0x),
(2, '1035', 'Abod', '10357', 'község', 0x),
(3, '2787', 'Abony', '27872', 'város', 0x000000000106000000010000000103000000010000003a00000023ac7c85bb153440f6b704e09f9a47409b81806bdd163440ff5481ff529947409f52ca106c193440b3ba302d45974740ca4c0e44bb1534400c4d2377229547409204e10a28123440d7a02fbdfd9347401b8755174b10344057bbdc161b934740bc7e1c284b0e3440e1687d80a4924740523ea99b300e3440eb9161702892474073446392360c3440528259fc01904740630333f0ed0734401eb1cc11e88f4740546c713216063440a9ab96cf4d904740491bfd1eab043440290989b48d9047409d4cdc2a8803344032bd0ee6c0904740117b57f3d201344018acdd1be8904740762b9501b1003440ecd918e0279147401f5db6e743ff3340917af42b42914740c38a09206bfe334094d0a79f81914740b5bbac2e4cfb33400a0d69f9269247405c6622b605f933402a79d047749247400391eae005f6334040af2e5d77924740f790f0bdbff333404eb3e501d1924740b26a6b9fe9f03340a64cc52176934740971fb8ca13f0334082b9cc9f259447400278b06ab5ec3340acf2f395f69547406687f8872def3340e93d3a1a2c97474011644ee152ef33400788388c38974740d6123ccfe9ef3340382163a4cd974740d0a6d9f280f033402d29d258569847407be29414b3f133408c70ee0ad8994740933d9d8603f23340134abac1ab9a4740eeefc74851f23340af7f3221419b47407e16f0d709f23340e39bb7fb669b4740c1ddfe017bf13340d30847dad79b4740045372f332f13340fbbec980fd9b4740066e3887c6f0334024a35e4b239c4740208ebffe35f0334045e4aa68629c47407acac57311f0334080a378f06e9c47406fa0c03bf9ee3340a810d951e69d4740a1061dbe96f1334004a09b470f9f4740a77114c550f33340c7719648589f4740a7d71cd60af5334042ab387aa19f4740bacd65b49ff63340a7ca9cd3d19f4740614557337cf73340e84cda54dd9f4740845db9837df83340b2593f47f59f47407d14bf7339fb3340e53796c162a04740d53439c65ffc3340091a33897aa047403369f8bb1cfd3340baf42f4965a04740d591c8e30e0834400a0d69f9269f4740a0d513a7a4083440030af5f4119f47401bc9d4134c0b344050a15f00869e4740b6775787ed0c344056f4e21f119e474053094fe8f50d34404419051c9d9d47400c99863cdd0e3440f4c9e59a5d9d47404e041665db103440e4d70fb1c19c47400f67d9ee7913344087d62f7d549c474018f1f85168143440d21852ea379c4740e78fc4268d143440b35c363ae79a474023ac7c85bb153440f6b704e09f9a4740),
(4, '0456', 'Ábrahámhegy', '04561', 'község', 0x),
(5, '0442', 'Ács', '04428', 'város', 0x),

CodePudding user response：

Ordinary dump-restore operations don't handle geospatial data correctly. Sigh.

You could try creating a view of your data that exposes the WKT and then dumping that.

CREATE OR REPLACE VIEW cities_geo 
SELECT id, `code`, `name`, `longcode`, `level`,
       ST_AsText(`border`) border
  FROM cities;
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then you should be able to export that view as if it were a table. You may have to work with the exported SQL to make it valid for importing.