Thread

I am moving an EE site from a development server to the final host server and are having some problems moving the database.

I have exported the database through phpMyAdmin with all the default settings.

I don’t have as much access to the new server as I would like but have had a new database created which I can access through phpMyAdmin.

When I import the file I created earlier I am getting this error.

Error
SQL query:

-- phpMyAdmin SQL Dump
-- version 2.8.2.4
-- <a href="http://www.phpmyadmin.net">http://www.phpmyadmin.net</a>
-- 
-- Host: localhost:3306
-- Generation Time: Jun 24, 2008 at 10:13 AM
-- Server version: 4.1.20
-- PHP Version: 5.0.5
-- 
-- Database: `numidia_db`
-- 
-- --------------------------------------------------------
-- 
-- Table structure for table `exp_actions`
-- 
CREATE TABLE  `exp_actions` (
 `action_id` INT( 4 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
 `class` VARCHAR( 50 ) NOT NULL DEFAULT  '',
 `method` VARCHAR( 50 ) NOT NULL DEFAULT  '',
PRIMARY KEY (  `action_id` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =20

MySQL said: 

#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 'ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=20' at line 2

Any ideas. I’ve never done this before.

What version of mysql are you moving from- and to?  And when you export- set it to ‘compatibility mode’- I suspect that will import.

I am moving from MySQL - 4.1.20 using phpMyAdmin - 2.8.2.4
I am moving to MySQL - 4.0.16 standard using phpMyAdmin - 2.6.2-pl1

When I export I have several options under compatibility.
They are:
NONE
ANSI
DB2
MAXDB
MYSQL323
MYSQL40
MSSQL
ORACLE
POSTGRESQL

I’ve just tried them all and these are the errors I am getting:

NONE:
#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 ‘ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=20’ at line 2

ANSI:
#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 ‘“exp_actions” (
  “action_id” int(4) unsigned NOT NULL auto_inc

DB2:
#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 ‘“exp_actions” (
  “action_id” int(4) unsigned NOT NULL,
  “clas

MAXDB:
#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 ‘“exp_actions” (
  “action_id” int(4) unsigned NOT NULL,
  “clas

MYSQL323:
#1046 - No Database Selected

MYSQL40:
#1046 - No Database Selected

MSSQL:
#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 ‘“exp_actions” (
  “action_id” int(4) unsigned NOT NULL,
  “clas

ORACLE:
#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 ‘“exp_actions” (
  “action_id” int(4) unsigned NOT NULL,
  “clas

POSTGRESQL:
#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 ‘“exp_actions” (
  “action_id” int(4) unsigned NOT NULL,
  “clas

You might read over this wiki entry- you’re running into issues because you’re going from 4.1.x->4.0.x.

If the wiki doesn’t work for you- no command line access, no need to convert, etc- I’d say you want compatibility mode- mysql 4.0.  I’m not sure why it says you don’t have a db selected, though.  Might try selecting all of the tables- then going to export- setting compatibility and trying that?  phpmyadmin isn’t my strong point.

(One other option- see if the new host will upgrade mysql.  I prefer the 4.1+ myself- adds some nice features.)

Thanks for the help. I have moved the database and can connect now.

I have another problem though. The site is up and running but I am getting really strange url’s.

<a href="http://www.numidia.com/index.php/site/news/?phpMyAdmin=7jnFlnUT4OZGPWWMhVdcXOVkHj6">http://www.numidia.com/index.php/site/news/?phpMyAdmin=7jnFlnUT4OZGPWWMhVdcXOVkHj6</a>

I’m guessing there is a path in my preferences wrong somewhere but I’m not sure where.

Crud- phpmyadmin is doing it- search this form for phpMyAdmin= - there have been other reports of it.  But I’ve yet to see a fix beyond find/replace.  I’m still poking google to see what setting in the export/import causes it, but haven’t spotted it yet.

OK. After searching the forums the url’s are now correct. It was quite simple in the end just amending the links in the embedded menu section. It’s not a very big site so was pretty painless.

Now…

Sites up OK and I thought I would just make a few test posts to check it was all working. Made the first post which didn’t appear on the relevant template page and when I go to edit it there is nothing there. Tried to make a second post and got this error.

MySQL ERROR:

Error Number: 1062

Description: Duplicate entry '0' for key 1

Query: INSERT INTO `exp_weblog_titles` (`entry_id`, `weblog_id`, `author_id`, `site_id`, `ip_address`, `title`, `url_title`, `entry_date`, `edit_date`, `versioning_enabled`, `year`, `month`, `day`, `expiration_date`, `comment_expiration_date`, `sticky`, `status`, `allow_comments`, `allow_trackbacks`, `forum_topic_id`, `dst_enabled`) VALUES ('', '17', '1', '1', '80.168.247.68', 'Vacancy test 1', 'vacancy_test_1', '1214896740', '20080701111956', 'y', '2008', '07', '01', '0', '0', 'n', 'open', 'y', 'n', '0', 'n')

It was all working fine before I moved it.

Any ideas where I should be looking to try and fix this.

Thanks.

Man- this import is giving you fits, isn’t it?  Looks to me like it dropped the auto-increment.  Go look at the table structure for exp_weblog_titles- is entry_id set to auto-increment:

entry_id int(10) unsigned NOT NULL auto_increment,
and
PRIMARY KEY (entry_id),

Does that make sense?  Also check… exp_field_groups:
group_id int(4) unsigned NOT NULL auto_increment,

Just grabbed a random one to see if it got dropped as well.

YES!

It’s all working.

I just turned auto increment on where you said.

Can’t thank you enough, I wouldn’t have had a clue where to start with that.

This is the first EE site I’ve had to move, so fingers crossed it won’t be so frustrating next time.

Thanks Robin.

Yep- usually it’s easy to do, but if you run into issues- it can be a pain.

I’m still worried you lost other auto_increment settings, though.  If you did, it will cause issues.  You could do another export/import- double check phpmyadmin settings to see if you missed something that would keep auto_increment.  Money bet it was exported w/out it- may have to do w/compatibility mode.

Or- you could do a manual check- search through the install.php to id the fields that need to be auto_incremented and double check them.  I listed them out at one point when we ran into a similar issue.  It was a complete list at the time, but it’s been a few versions now.

But yes- double check if you lost all auto_increment- because if you did, it’s going to have to be fixed to work correctly.

I’m going to leave this open while you poke a bit- but otherwise, make sense what’s up?

Yes your right. Auto increment was missing from everything I think. I have switched it on for everything in the list you linked to but will keep an eye on the site for any strange behaviour and bear this in mind if there is any.

It’s a pretty simple site and is using very few EE features so hopefully as long as the client can post stories to 3 different templates it should all be good.

Yep- if you hit those spots, think you’ll be good to go.  Had to be something in the export/import- so once it’s fixed, it’s fixed.  And if you spot anything odd- auto_increment should be the first thing to check.

But otherwise- sounds like you should be good.  OK to close this one out?

Yep.

Thanks for all your help.

Good deal- and sorry it was a bit of a pain!

Closing this one out.