XF 2.0 Database migration error

[abdfahim]

I am having a hard time migrating my XF 2.0 database from a windows machine (MySql 5.6) to a MacBook (Mysql 5.7, set up through ScotchBox), if anyone can kindly help.

Here are what I have tried so far.

Attempt 1:
First, I tried to export the database using PhpMyAdmin and then import it using Sequel Pro. However, import failed with the following error

An error occurred when reading the file, as it could not be read in the encoding you selected (Autodetect - Unicode (UTF-8)).

Attempt 2:
Based on the solution provided here, I tried to import the same file (from PhpMyAdmin Export) using Encoding = MacOs Roman. This time, the export was successful (at least no error thrown by Sequel Pro), but XF was not loading, as reported here.

Attempt 3:
So, I thought maybe something wrong with PhpMyAdmin. Hence, I used mysqldump to export

cmd> mysqldump -u root -p dbname > backup-file.sql

and tried to import it inside scotchbox

terminal> vagrant ssh
terminal> mysql -u root -p -e "/Users/abdfahim/Google Drive/backup-file.sql"  dbname

Unfortunately, the attempt failed with the following error

ERROR 1064 (42000) at line 1: 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 '/Users/abdfahim/Google Drive/backup-file.sql' at line 1

Attempt 4:
That made me think that something not right with the handling of different MySql version. So, I tried to import the same backup-file.sql on the same machine where I exported the data

mysql -u root -p -e backup-file.sql testdatabasetemp

... and yet I ended up with this error

ERROR 1064 (42000) at line 1: 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 'backup-file.sql' at line 1

Baffled by the fact that I used exactly the same MySql for export and import, I tried to see what was so special at Line 1 of backup-file.sql, but couldn't figure out any abnormality

-- MySQL dump 10.13  Distrib 5.6.15, for Win32 (x86)
--
-- Host: localhost    Database: dbname
-- ------------------------------------------------------
-- Server version    5.6.15-log

/*!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 `xf_abdfahim_ft_featured_thread`
--

DROP TABLE IF EXISTS `xf_abdfahim_ft_featured_thread`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `xf_abdfahim_ft_featured_thread` (
  `thread_id` int(10) unsigned NOT NULL,
  `featured_date` int(10) unsigned NOT NULL,
  PRIMARY KEY (`thread_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `xf_abdfahim_ft_featured_thread`
--

 

[Chris D]

The "Line 1" it is referring to is actually the MySQL query that is being run to start the import of your SQL file, rather than the contents of the SQL file itself.

So, essentially, it seems the commands are incorrect.

The command to dump your database is correct, but note that you should use utf8mb4 character set if your database was a fresh install of XF2 or you converted the tables to utf8mb4:

mysqldump -u root -p dbname > backup-file.sql --default-character-set=utf8mb4

In terms of importing that, I'd use this command:

mysql -u username -p dbname < backup-file.sql