Search code examples
mysqlgoogle-mapsphpmyadmincoordinatespoint

export/import issue with mysql POINT coordinates

I've got an issue when I export then import a MySql database from local to prod:

(I export and import via phpMyAdmin in "simple mode" with default options.)

Source database : Local environment MySql 5.7.19 (working)

Table : lieux | InnoDB | utf8_unicode_ci

Column :

  • Name => coords
  • Type => point
  • Value => 'POINT(48.6863316 6.1703782)',0 (the good one)

Target database : Production environment MySql 5.6.?

Table : lieux | InnoDB | utf8_unicode_ci

Column :

  • Name => coords
  • Type => point
  • Value => 'POINT(-0.000000000029046067630853117 -3.583174595546599e227)',0 (the bad one)

After export/import, the point value changes and all coordinates are false.

I also have a weird display when I open .sql export in VSCode editor :

The point value looks like that : capture (the file is in UTF-8 format).

Do you know if utf-8 is problematic with coordinates points in MySql, or maybe if the older MySql version on prod server could cause this ? Should I use utf8_mb4 charset ?

EDIT : Adding more details

  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) and located here :

original location

  1. Then, I go in phpMyAdmin in export tab and make a simple sql export (with default options).

  2. Then I log in phpMyAdmin on the prod server, go in import tab and import my sql file (also with default options).

  3. When I look in the new db (on the prod server), the value is stored as POINT(-0.000000000029046067630853117 -3.583174595546599e227), and is located here (after google maps corrected it by removing the excess characters) :

bad coordinates

I went around this issue by using Heidi SQL instead of phpMyAdmin, which is storing the values differently:

  • In phpMyAdmin export .sql file : capture
  • In Heidi SQL export .sql file : binary thing

EDIT : Export file

-- 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,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  [...],
  `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`, `created_at`, `updated_at`, [...], `coords`, [...]) VALUES
(1, '2018-03-23 09:13:45', '2018-04-19 19:47:22', [...], '\0\0\0\0\0\0\0��I�mH@�D[@', [...]),
(2, '2018-03-23 18:11:59', '2018-07-12 16:15:06', [...], '\0\0\0\0\0\0\0���WH@.�s�w�@', [...]),
(3, '2018-04-02 14:00:29', '2018-04-19 19:47:32', [...], '\0\0\0\0\0\0\04j��E@�mWCu@', [...]);

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

I replaced superfluous data with [...]

EDIT : Trying phpMyAdmin options and command line mysqldump

I tried all relevant options in PHPMyAdmin, and nothing changed. I also tried to export the database from the remote environment which has a different version of PHPMyAdmin => still not working.

I'm trying to export from local in command line, with this instruction:

C:\laragon\bin\mysql\mysql-5.7.19-winx64\bin
λ mysqldump.exe --host=localhost --user=root mydatabase > mydatabase.sql

But it still gives me a bad encoding :

-- MySQL dump 10.13  Distrib 5.7.19, for Win64 (x86_64)
--
-- Host: localhost    Database: db
-- ------------------------------------------------------
-- Server version   5.7.19

/*!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 utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `lieux`
--

DROP TABLE IF EXISTS `lieux`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lieux` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  [...],
  `coords` point DEFAULT NULL,
  [...],
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lieux`
--

LOCK TABLES `lieux` WRITE;
/*!40000 ALTER TABLE `lieux` DISABLE KEYS */;
INSERT INTO `lieux` VALUES (1,'2018-03-23 09:13:45','2018-04-19 19:47:22',[...],'\0\0\0\0\0\0\0��I\�mH@�D[@',[...]),(2,'2018-03-23 18:11:59','2018-07-12 16:15:06',[...],'\0\0\0\0\0\0\0��\�WH@.\�s�w�@',[...]),(3,'2018-04-02 14:00:29','2018-04-19 19:47:32',[...],'\0\0\0\0\0\0\04j��E@�mWCu@',[...]);
/*!40000 ALTER TABLE `lieux` ENABLE KEYS */;
UNLOCK TABLES;

part of mysqldump command line export file

  • How can I get more info about export process ?

  • Do you think it could be caused by a bad value storage on row creation (I mean when the user create the location item)?

  • Maybe it's linked with this other issue (also mine, and only partially resolved)...

Solution

  • Finally, it is fixed in phpMyAdmin 4.8.6

    As you can see here : https://github.com/phpmyadmin/phpmyadmin/issues/14588#event-2323932464

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