Database Import - Posts not loading

[Ezaco]

Hello all,

I'm trying to move my forum to a new server host. I've downloaded all the server files and uploaded to the new one with no issue. Where I'm running into problems is the import of the SQL database.

I'm doing this through phpMyAdmin, and the DB file is 173,000 kb. I've installed the exact same version of PHP and MySQL that my old host was using, and all directories are writable. At first I was getting an error partway through the upload (which has to be done by restarting the import a couple of times due to the script timeout):

Code Select Expand

MySQL said: Documentation

#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 '`id_pm`, `id_pm_head`, `id_member_from`, `deleted_by_sender`, `from_name`, `msgt' at line 1

I've managed to get it to skip that error and continue the upload and it finishes. But after that when trying to load topics the entire body is empty:

http://imgur.com/FyvSFQK [nofollow]

I've also already used the repair_settings.php tool and fixed all the directories.

Does anyone have any insight?

[Sir Osis of Liver]

What's in the _messages table?

[Ezaco]

After the import? 17,500 pages of posts. They're there, it looks like, just not displaying.

[a10]

^^^ 'something' missing in table after import \ timeouts ?

If on php 5.x, try mysqldumper, overcomes timeout problems (excellent tool, sadly not for php 7 ...yet, hopefully someone will look into it).

Or try importing the single table with phpmyadmin.

[Ezaco]

After trying that 3 times now I've encountered this error every time:

MySQL-ERROR
MySQL says:
Variable 'character_set_client' can't be set to the value of 'NULL'

Error in Query:
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; -> Variable 'character_set_client' can't be set to the value of 'NULL'


Googling that I've added the following to the beginning and had the same error on the last two uploads:

/*!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 */;

This in addition to the header already:

-- phpMyAdmin SQL Dump
-- version 4.3.8
-- http://www.phpmyadmin.net [nofollow]
--
-- Host: localhost
-- Generation Time: Jun 04, 2017 at 01:03 PM
-- Server version: 5.6.32-78.1-log
-- PHP Version: 5.6.20

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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 utf8 */;


Doing this on phpMyAdmin fixed it, but doesn't seem to be doing it on MySQLDumper.

[Ezaco]

Bumping.

[Ezaco]

Now getting this error:

Code Select Expand

MySQL says:
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 ''Appreiciate it man, my door is always open, whether you need advice or just to ' at line 1

Error in Query:
INSERT INTO `smf_personal_messages` (`id_pm`, `id_pm_head`, `id_member_from`, `deleted_by_sender`, `from_name`, `msgtime`, `subject`, `body`) VALUES(215301, 215298, 1687, 0, 'danc0320', 1439560128, 'Re: (No subject)', 'Appreiciate it man, my door is always open, whether you need advice or just to shoot the ******.

Once your all squared away, we should look into doing some trainings together. Either H?>

INSERT INTO `smf_personal_messages` (`id_pm`, `id_pm_head`, `id_member_from`, `deleted_by_sender`, `from_name`, `msgtime`, `subject`, `body`) VALUES(215301, 215298, 1687, 0, 'danc0320', 1439560128, 'Re: (No subject)', 'Appreiciate it man, my door is always open, whether you need advice or just to shoot the poo.

Once your all squared away, we should look into doing some trainings together. Either H -> 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 ''Appreiciate it man, my door is always open, whether you need advice or just to ' at line 1

Anyone have any suggestions?

[shawnb61]

I've seen that error a lot on phpMyAdmin imports - and it is usually erroneous. 

Sometimes that error really means that the import .sql file is too big for it to eat in one chunk. 

This usually happens on .sql files > 50MB.   

An additional note on .sql file size:  Sometimes even a much smaller file will have an issue - IF - there are too many rows in there.  Somtimes phpMyAdmin will export one row per record.  Something like log_topics may have millions of rows in it.  If you have one line each, even a file much smaller than 50MB will choke.   

So...  You gotta break it up.  I make sure that no is file > 40MB ***AND*** no file has > 400,000 rows in it. 

I wrote a little utility in .php that operates on my local WAMP server if interested, that honors both rules.  It always works...  If interested, let me know. 


Regarding those "/*!40014 SET @OLD_ ***"  at the beginning of each file.  Those attempt to save off your DB settings at the beginning of an import, and restore them at the end.  So there are a few lines at the beginning of your .sql file and a few lines at the end to return the DB to the state it was in before you imported. 

If you wanted the most perfect, safe behavior, after splitting your .sql file up, you would put the 'backup' ones (SET OLD to yadayada) at the beginning of each .sql file, and the 'restore' ones (SET yadayada to OLD) at the end of each .sql file. 

To be honest, I just delete 'em and everything works perfectly.  Since my DBs are dedicated for that purpose, it's OK to let 'em use the settings affected by the import.  No need to restore the old, prior settings. 

Hope this helps. 

[shawnb61]

Of course, your host can usually do an import with no restrictions. 

Give it one more try, and if you still have issues, open a ticket. 

[Sir Osis of Liver]

Is your host running MariaDB?

[Ezaco]

I've seen that error a lot on phpMyAdmin imports - and it is usually erroneous. 

Sometimes that error really means that the import .sql file is too big for it to eat in one chunk. 

This usually happens on .sql files > 50MB.   

An additional note on .sql file size:  Sometimes even a much smaller file will have an issue - IF - there are too many rows in there.  Somtimes phpMyAdmin will export one row per record.  Something like log_topics may have millions of rows in it.  If you have one line each, even a file much smaller than 50MB will choke.   

So...  You gotta break it up.  I make sure that no is file > 40MB ***AND*** no file has > 400,000 rows in it. 

I wrote a little utility in .php that operates on my local WAMP server if interested, that honors both rules.  It always works...  If interested, let me know. 

Would it work in a LAMP environment? That's what I'm working in.

Regarding those "/*!40014 SET @OLD_ ***"  at the beginning of each file.  Those attempt to save off your DB settings at the beginning of an import, and restore them at the end.  So there are a few lines at the beginning of your .sql file and a few lines at the end to return the DB to the state it was in before you imported. 

If you wanted the most perfect, safe behavior, after splitting your .sql file up, you would put the 'backup' ones (SET OLD to yadayada) at the beginning of each .sql file, and the 'restore' ones (SET yadayada to OLD) at the end of each .sql file. 

To be honest, I just delete 'em and everything works perfectly.  Since my DBs are dedicated for that purpose, it's OK to let 'em use the settings affected by the import.  No need to restore the old, prior settings. 

Hope this helps.

The first file I tried that kicked the first errors is 714 MB and 5,755,484 lines. The second one I tried, using some different export settings, is 1.25 GB and 5,749,813 lines.

Of course, your host can usually do an import with no restrictions. 

Give it one more try, and if you still have issues, open a ticket. 

Unfortunately I can't file a ticket. I'm self-hosting this.

Is your host running MariaDB?

No. Just PHP, phpMyAdmin, and MySQL.

[vbgamer45]

If you self hosted you can import from the command line in mysql.
The source command can get the sql script
Example
source /mypath/myfile.sql
Just make sure do a use databasename first.

[shawnb61]

Two things that might help...

First, search indexes are huge and may even be 1/3+ of your export.  I normally do not export the DATA (structure only) for log_search*.  DB exports are much smaller.   Just remove & rebuild the search index in your target environment.

Second, if you have command line access to mysql, that's the way you should probably do the import .  Don't use phpmyadmin. 

[Ezaco]

*whistle* That's way faster. And doesn't produce any errors. But when I load the topics the same error as occurring in my OP: Nothing loads in the topic.

[Sir Osis of Liver]

Gave you tried running the database in a clean test install?

[Ezaco]

Gave you tried running the database in a clean test install?

Just did. A fresh install with a new database works fine. Can't import my database into the default one, but switching the database on the test install to the other database results in the same error.

[Ezaco]

After using the button to Reset All Themes it now loads posts on the fresh install. But that doesn't do anything for my carried over posts. Not a huge deal, since this will just be an archive, but still curious.

[Sir Osis of Liver]

Do you have an unmodified backup of your production database?  If I understand correctly, it is (or was) working fine on your original server, but you've made some changes while attempting to import it to new server.  I would start with the original, empty all logs, get rid of the search index, import to new server, and try connecting it to clean test install.