Database Import error after exporting

Question

Just like title say. I am using HeidiSQL, and i wan't to duplicate a database so i export the database, create a new empty with name "test". Then i change the name of the database to "test" in the .sql file and try to import the database, then i recieve this error

Error de SQL (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right

syntax to use near '--

-- Host: ' at line 1

I think is something about the first part of the sql file, which is

-- --------------------------------------------------------
-- Host:                         
-- Versión del servidor:         5.5.41 - MySQL Community Server (GPL) by Remi
-- SO del servidor:              Linux
-- HeidiSQL Versión:             9.1.0.4867
-- --------------------------------------------------------

As far as i know its supposed to be only just comments.

Any suggestion?

Thanks in advance and sorry if its an idiot question but don't know how to solve this, i searched without results. Sorry if my english is not perfect too.

UPDATE: first 100 lines of the file

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 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' */;

-- Volcando estructura de base de datos para desarrolloweb
CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;


-- Volcando estructura para tabla desarrolloweb.tqg2h_akeeba_common
CREATE TABLE IF NOT EXISTS `tqg2h_akeeba_common` (
  `key` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Volcando datos para la tabla desarrolloweb.tqg2h_akeeba_common: ~3 rows (aproximadamente)
/*!40000 ALTER TABLE `tqg2h_akeeba_common` DISABLE KEYS */;
INSERT INTO `tqg2h_akeeba_common` (`key`, `value`) VALUES
    ('stats_lastrun', '1419900936'),
    ('stats_siteid', '89afdf9407b43fb898c32abbabbdae6f4d9aefc8'),
    ('stats_siteurl', '94599098f96a3bbdb57077a7334fd064');
/*!40000 ALTER TABLE `tqg2h_akeeba_common` ENABLE KEYS */;


-- Volcando estructura para tabla desarrolloweb.tqg2h_ak_params
CREATE TABLE IF NOT EXISTS `tqg2h_ak_params` (
  `tag` varchar(255) NOT NULL,
  `data` longtext,
  PRIMARY KEY (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Volcando datos para la tabla desarrolloweb.tqg2h_ak_params: ~1 rows (aproximadamente)
/*!40000 ALTER TABLE `tqg2h_ak_params` DISABLE KEYS */;
INSERT INTO `tqg2h_ak_params` (`tag`, `data`) VALUES
    ('update_version', '1.2.0');
/*!40000 ALTER TABLE `tqg2h_ak_params` ENABLE KEYS */;


-- Volcando estructura para tabla desarrolloweb.tqg2h_ak_profiles
CREATE TABLE IF NOT EXISTS `tqg2h_ak_profiles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL,
  `configuration` longtext,
  `filters` longtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- Volcando datos para la tabla desarrolloweb.tqg2h_ak_profiles: ~1 rows (aproximadamente)
/*!40000 ALTER TABLE `tqg2h_ak_profiles` DISABLE KEYS */;
INSERT INTO `tqg2h_ak_profiles` (`id`, `description`, `configuration`, `filters`) VALUES
    (1, 'Default Backup Profile', '###AES128###+wNhUfQrnLFFGIxJfsqqq3NIRFdOAXfnDfQGV0GFrv0SEKKsrowqaqikqxO/Ej9ikG8kmDCKRq0Yt/vkM11aVkvLFYyDMXC/FilnxIkcT+BF/SNP0IGj0xT4FUmRZjUhhmBNl9cnWfIVxRioEGI/VJ4zk1TyhNIHc8tDhTwKus/ca5WB0nBe1u+N4hwMWMKOiovdCCSM7/BADHg6blaqhcBVL/J8mAE7pJApN/RmO6k3cMMuH5916XdOhJzQOL0ZmoaO3W+42VLTpZPp1vb83IJmXpsifoXSkwWQNF2Glm178VQR9ESI3uEx0yRNSuF2AAzHWMHm2GW9hBWRsiFzIxUitUQoR14wd+7Jvd6hCQPYcV7OjPyn7LBOpDpRkWoswvZINAaDzw2p3/Cmgow4M9SJmdpNv4DfBya9x1OpZv0fWZJ6kfbKkJ7hHOIluINP4laCOf18Y8JR6BKcbntuhNSYQMJLuqfdwQlbhi1dw73yzEmBjLvOlAkDdQ7oEokDZ60K0ItJz/WYfIUVvMU8YTgRYronEVr6D8uQAZHgfgXDCJRM++0gorbqs9WO1IgIsqctNgiXeaekL1Cgor6YYRaDz4Eo98Zpa+DbyAGxoTZQ0hn5SHsgDNsEug8pjVBh4P5aZ38hXndgJkjQ1KjJQSeErRzGE9bgTOyPSR9P0mCyrLWYkxBHMHE1+sHWZbLAwS3YRMY0gYi67MrFHgoWQU+jHXPQGAWXpwQQR2mGylyW5wIBhVqDrUta8sd9XQgGWeJ9z3jAbuO8KvYUQ5JWsXEvTEok0vg89565hF/9pzmHtSA95MqyqOUhsUbkQiW74PNnby4G+GWdroX6UqX+09yEPws08A4uWmQm4TwUk4eM0HOZ3CCvoe2TaUyTOmuDqHWGfWN2cltHUESVGXA16z3BxNOsCtpX/8wV4G2ZvBWAGNERcOGT0N38LDQb9jlcsUt6ZFOkaZ2rtWZY5g71ahh8tGPC4Isa3pDGJ8pIP/cs3+/hyFODw2ffoc/AfVQ6LWQxk2DBL/HPz5aNH/7kjfXgi0oTi60iMNEJ9HH6BvcVwCzIjcuZolM6XSaDlfC6b+OMMLya7gZjkpVz7nkL0W7aKYkg1ucHKTFHDB0Y3Zg/oNaElWKbj1SeQrA+R0NcjD9XiR+sRK9UgOfSxSH7LVHRhmrLIKyYsQ5vPAYmVDHXOKuN8cUNLJrPxV8n4qZpM+2PdRTyURVmnjOZPNQsdlsFo8npc4DMaTk6yeDB6LL08RH0CHcXkk3j8X4dff7QF37Z06DUHn5va81DO4kfBiCgQGa5oMdYR5uS1zQ7ndKPMuyPJKKjxqF/9DFlI0Sm47H5fLEJeLaFGPUnsIyBMITaynr9bC1bf9Sz9XR+3/DLQSEToXR1d1HNiPxF4FpuZkUgsNYp2gtLnb91tfjtlY5bH7KZMZFwXGScEqt1x/5jtJhmEqqcwGCiYUYye3hsCxM/f+4GOequJ3NyX4f3yT++ZtYRlY4SStactDS062SkpWWxDOCLLu6kVRfzB2Hjt5HkpwYBuHWHZTVARWmNWAt7THPm3E8EHv3mzwp5QCkuAZXsXsLaIEehr6QKLGIAAUGS99lrgvjx2fR5sDD7REm7KgLEN4ztQO4oJc1cZfPrLLLvNHv16kn8jvYbXWXCmfdLQZxkKfIMSYK8r2FvXNXRA8MiFRI/upkK6/b933nqZqvvZKryHhaSfvwzhq7uLR63ynjPwnKDt5Ts6FoXfdVe5O/XiL8ais4sgnKYwflvvuE49f06nxgAJkV6HGj3AeqE9SasD+VyREkTiNGzrNpnmobrTLR06+xNej6MbNpNA8AeyMaOsYiT51rbb+RQ92Lgqm4CC31/eZABOMr04AISl9UzGvTM9xf9nDFRhpa+q4KZEW0YDjiKh/oTWcPJ1qx1MTvp50sK1W2IZjPWh0QQnL5nlT5VZkjPn98SO98/3r56ij6YL82LCVyO6h5x7fbsTVX1jW/IEfdrpEkp1bHDr3ultV3KHwmQaTAIqibtyKr81dTFwuDyp9L3AY/4fCYAPyITU1eIEge0A9kVIg1Bl4mAtbny3Rza45fdHjsGcF9BLUHCRrhvWizs+BSZsxRc9XDYx9qXhRVPKKbMzZcOXNUWYZkmicsMn29ImUUhy9fvOTAFyqmTMiNlSWVkMnBgC6gILAKHf24tkiKYjREx1CYfrWx0MJkS3RdQ40ssC/J/ogEQGjoBeoxCavfXqmk124nVtqnbAyVwvxo4e01D4Ytva9h+r4BakNny07vC4S7FGhaOu8JWYp0h8+iRBCvEGk+pDbkBSHudj5JmG6BA1Eju4wyvTRHE7bp/fv293DTooPg79tDZIbcxH411mWdiry/B5k1S0J/C/quYuu8o9duIdrjrxKtzkZ35eAEKBwAA', '');
/*!40000 ALTER TABLE `tqg2h_ak_profiles` ENABLE KEYS */;


-- Volcando estructura para tabla desarrolloweb.tqg2h_ak_stats
CREATE TABLE IF NOT EXISTS `tqg2h_ak_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL,
  `comment` longtext,
  `backupstart` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `backupend` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` enum('run','fail','complete') NOT NULL DEFAULT 'run',
  `origin` varchar(30) NOT NULL DEFAULT 'backend',
  `type` varchar(30) NOT NULL DEFAULT 'full',
  `profile_id` bigint(20) NOT NULL DEFAULT '1',
  `archivename` longtext,
  `absolute_path` longtext,
  `multipart` int(11) NOT NULL DEFAULT '0',
  `tag` varchar(255) DEFAULT NULL,
  `backupid` varchar(255) DEFAULT NULL,
  `filesexist` tinyint(3) NOT NULL DEFAULT '1',
  `remote_filename` varchar(1000) DEFAULT NULL,
  `total_size` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_fullstatus` (`filesexist`,`status`),
  KEY `idx_stale` (`status`,`origin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Volcando datos para la tabla desarrolloweb.tqg2h_ak_stats: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `tqg2h_ak_stats` DISABLE KEYS */;
/*!40000 ALTER TABLE `tqg2h_ak_stats` ENABLE KEYS */;


-- Volcando estructura para tabla desarrolloweb.tqg2h_ak_storage
CREATE TABLE IF NOT EXISTS `tqg2h_ak_storage` (
  `tag` varchar(255) NOT NULL,
  `lastupdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `data` longtext,
  PRIMARY KEY (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Volcando datos para la tabla desarrolloweb.tqg2h_ak_storage: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `tqg2h_ak_storage` DISABLE KEYS */;
/*!40000 ALTER TABLE `tqg2h_ak_storage` ENABLE KEYS */;

Bede

Can't you just try to remove this header and imprt? – Oxymoron

sorry for the later answer. Yes, i tried but nothing... when i remove that part then is giving me error at the first line which is /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; – Bede

Sometimes those errors are misleading. If there is an SQL error in the first actual (non-comment) command in the file, the error may just show you the first part of the bad query, which is the comment. If you post the first few paragraphs of the file more people could join and help. – Oxymoron

i updated the first post with the first 100 lines of the file :) but i don't understand why the error if i am making the export with the same tools that i am making the import (heidisql). – Bede

I just copied the first few lines and it works well for me. My only guess is that the import tool is doing something wrong (I used command line), or that the file ENCODING is somehow messed up. – Oxymoron

Answer 1

In my case the solution was to change character set in sql file. Open your database .sql file and replace /*!40101 SET NAMES utf8mb4 */; to /*!40101 SET NAMES utf8 */; .

Dunno

Answer 2

You should select the right file encoding when you load any SQL file in HeidiSQL. The auto-detection of the file encoding was often enough prone to errors. I already tried several approaches in the HeidiSQL code for fixing the detection but there seem to be a myriad of files outside which are not detected correctly.

In your case, I assume the "Auto detect" setting in the file-open dialog probably misdetected an encoding which must have a BOM (2 to 4 bytes) at the very start of the file.

So, when you open a .sql file in HeidiSQL, avoid selecting "Auto detect (may fail)" in the "Encoding" dropdown box.

Ratite

Answer 3

The solution could be only upload the DB.

If use wp: you can use a plug-in db Migration wp

Else: Other solution it's export in MYSQL40.

in this link there are a better explanation: https://galengidman.com/2015/07/07/migrating-a-wordpress-site-from-utf8mb4-to-utf8/

Atony

Answer 4

I had this same issue.I resolved it, before i importing the SQL file i changed /*!40101 SET NAMES utf8mb4 /; to /!40101 SET NAMES utf8 */;.Then it worked for me.

Maley