Transferring mySQL databases to a new server with vB

[richard8667]

I've undertaken the horrid task of moving from a server running mySQL 4.1 to a server running mySQL 4.0. I'm attempting to import the database (19 megs), and get the error:

Starting at the line: 141001
Error at the line 141791: INSERT INTO `word` VALUES (32813, '‘,………………………………');
Query: INSERT INTO `word` VALUES (32813, '‘,………………………………');
MySQL: Duplicate entry '‘,………………' for key 2
Stopped on error

I've looked through the sql dump (which I exported in 4.0 compatability mode) and see a bunch of lines similar to this around there, but am not sure what they mean, since I am not THAT familiar with the vB sql tables. Any ideas?

[Colin F]

I'd recommend removing all the lines that insert something into the 'word' table.
You can rebuild these later on by rebuilding the search index.

[richard8667]

I did that, and got past the errors in that part. I'm now getting random errors throughout the forums.

I just attempted doing a fresh install (uploaded fresh versions of the vB software, put in a backup of the database, tried to go from there), but it did not work either. My guess is its issues exporting to a database in the mySQL 4.0 format. I'm going to check with my host to see if he is going to be upgrading anytime soon.

[Colin F]

Please post the exact errors to troubleshoot this.

[richard8667]

I took the database, re-exported it from my previous server in mySQL 4.0, then went in and removed all of the insertions for the word table (though leaving the invocation of the table). I then got a fresh copy of the vB software from the member area and put it on a different account on the server, using a new mysql database and user.

Indexing worked this time, but when making a new post, I get the error:

Database error in vBulletin 3.5.4:
Invalid SQL:
   REPLACE INTO threadread
    (threadid, userid, readtime)
   VALUES
    (, 2, 1148405475);
MySQL Error  : 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 ' 2, 1148405475)' at line 4
Error Number : 1064
Date         : Tuesday, May 23rd 2006 @ 08:31:15 PM
Script       : http://sf.digitalxero.com/forums/newthread.php
Referrer     : http://sf.digitalxero.com/forums/new...=newthread&f=2
IP Address   : 
Username     : Richard
Classname    : vb_database

I took the IP out of the error.

[Steve Machol]

SQL Syntax errors are almost always related to modified code. To troubleshoot this, the first thing you need to do is remove any hacks or plugins, then see if you still have this problem.

If so, reupload all the original vB non-image files (except install.php). Make sure you upload these in ASCII format and overwrite the ones on the server.

Next, create a new style and choose no parent style. This will force it to use the default templates. Finally empty your browser cache, close all browser windows then try again. Make sure you change to the new style and view your forums with it. Do you have the same problem?

[richard8667]

Disabled all plugins, reuploaded files, and got this when attempting to create a new style...

Database error in vBulletin 3.5.4:
Invalid SQL:
  INSERT INTO style
  (title)
  VALUES
  ('New Default');
MySQL Error  : Duplicate entry '0' for key 1
Error Number : 1062
Date         : Tuesday, May 23rd 2006 @ 08:46:39 PM
Script       : http://sf.digitalxero.com/forums/admincp/template.php
Referrer     : http://sf.digitalxero.com/forums/adm...hp?do=addstyle
IP Address   : 
Username     : Richard
Classname    : vb_database

[Steve Machol]

This was most likely a problem with your backup/restore which stripped out auto-incrementing. Please see this post for the queries to run to reset auto-incrementing:

http://www.vbulletin.com/forum/showt...50#post1079950

[richard8667]

Steve, ran all those queries *phew* and posting and everything is working...

I attempted to create another new style, as recommended, and now receive the error:

Fatal error: Invalid row in the style table in /includes/adminfunctions_template.php on line 2246

[Steve Machol]

This is a problem with your custom style. Try this. Create a new style and choose no parent style. This will force it to use the default templates. Then change to this style and view your forums with it. Do you have the same problem?

[richard8667]

The problem just mentioned WAS me trying to make the new style to browse the forums with. I went into the AdminCP, chose Add New Style, and after hitting the Save button, received the above error.

*trying again*

Same thing. When choosing "Save" I get:

Fatal error: Invalid row in the style table in /includes/adminfunctions_template.php on line 2246

[Steve Machol]

There is something in the change you are making that is causing this.

[richard8667]

I'm afraid I don't follow you.

[Steve Machol]

Ahhh...never mind. I misunderstood.

To troubleshoot this, remove any hacks and disable your plugins, then see if you still have this problem.

Note: To temporarily disable the plugin system, edit config.php and add this line right under <?php

define('DISABLE_HOOKS', true);

If so, reupload all the original vB non-image files (except install.php). Make sure you upload these in ASCII format and overwrite the ones on the server.

[richard8667]

Disabled the plugin system using the command listed above...
Reuploaded all non-image files in ASCII format.

Still have the exact same issue.

One thing I will note is that I downloaded the mySQL dump from the previous server, and noticed a large amount of non-Latin characters in the user area of the dump. I checked an older backup from another server, and did not notice those.

Could my previous host simply have an extremely corrupted version of mysql installed? If so, I may be stuck using an old version of the database, but if I have to, I'll take it.