export mysql POINT coordinates as hex

[CharlieEtienne]

More details here : https://stackoverflow.com/questions/51530268/export-import-issue-with-mysql-point-coordinates/51900521

Steps to reproduce

1. In my project (Laravel + Google Maps JavaScript API), I create (on local environment) a Place with these coordinates : 48°41'10.8"N 6°10'13.4"E, stored in database as POINT(48.6863316 6.1703782)
2. Then, I go in phpMyAdmin in export tab and make a simple sql export (with default options)
3. Then I log in phpMyAdmin on the prod server, go in import tab and import my sql file (also with default options)
4. When I look in the new db (on the prod server), the value is stored as POINT(-0.000000000029046067630853117 -3.583174595546599e227) : invalid coordinates.

Expected behaviour

coordinates should be exported as HEX
Here is an export file from HeidiSQL:

-- --------------------------------------------------------
-- Hôte :                        localhost
-- Version du serveur:           5.7.19 - MySQL Community Server (GPL)
-- SE du serveur:                Win64
-- HeidiSQL Version:             9.4.0.5125
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Export de la structure de la table xxx. lieux
DROP TABLE IF EXISTS `lieux`;
CREATE TABLE IF NOT EXISTS `lieux` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `coords` point DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Export de données de la table xxx.lieux : ~3 rows (environ)
DELETE FROM `lieux`;
/*!40000 ALTER TABLE `lieux` DISABLE KEYS */;
INSERT INTO `lieux` (`id`, `coords`) VALUES
	(1, _binary 0x000000000101000000A0AE4912DF6D4840BE440603195B0240),
	(2, _binary 0x0000000001010000000E1BC0B6D95748402ECE739F77AE1840),
	(3, _binary 0x000000000101000000346A1904B1A545401FA56D5743751540);
/*!40000 ALTER TABLE `lieux` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Actual behaviour

coordinates aren't exported as HEX
Here is the export file from phpMyAdmin:

-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1:3306
-- Généré le :  jeu. 26 juil. 2018 à 03:46
-- Version du serveur :  5.7.19
-- Version de PHP :  7.1.9

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- --------------------------------------------------------

--
-- Structure de la table `lieux`
--

DROP TABLE IF EXISTS `lieux`;
CREATE TABLE IF NOT EXISTS `lieux` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `coords` point DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Déchargement des données de la table `lieux`
--

INSERT INTO `lieux` (`id`, `coords`) VALUES
(1, '\0\0\0\0\0\0\0��I�mH@�D[@'),
(2, '\0\0\0\0\0\0\0���WH@.�s�w�@'),
(3, '\0\0\0\0\0\0\04j��E@�mWCu@');

I tried all export options, and nothing changes.

Server configuration

Operating system:
Tested on local env (windows, wamp, apache) and in prod env (linux, apache)

Database:
Source database: Local environment MySql 5.7.19
Table: "lieux" | InnoDB | utf8_unicode_ci
Column Name: coords
Column Type: point

Target database: Production environment MySql 5.6.39
Table: "lieux" | InnoDB | utf8_unicode_ci
Column Name: coords
Column Type: point

PHP version:
Tested on 7.0.30 and 7.1.9

phpMyAdmin version:
Tested on 4.7.4 and 4.7.3

[williamdes]

Related to: #6350

[CharlieEtienne]

Do you plan to investigate more ? It seems it could be fixed with --hex-blob on mysql dump...

[williamdes]

Affects also .ods and .odt exports

[williamdes]

@CharlieEtienne This is now fixed by 7f454ac and will part of the next version of phpMyAdmin (4.8.6)

[williamdes]

INSERT INTO `lieux` (`id`, `coords`) VALUES
(1, 0x000000000101000000a0ae4912df6d4840be440603195b0240),
(2, 0x0000000001010000000e1bc0b6d95748402ece739f77ae1840),
(3, 0x000000000101000000346a1904b1a545401fa56d5743751540);

[github-actions]

This issue has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.