I followed to the letter (being logged in as uid1 etc), the instructions in the Civicspace article linked in this thread about moving a working drupal web site to another host.

When I run the backed up sql file in the new database on the new web host, I get the following error:

-- Table structure for table `access`
--
CREATE TABLE `access` (
`aid` tinyint( 10 ) NOT NULL AUTO_INCREMENT ,
`mask` varchar( 255 ) NOT NULL default '',
`type` varchar( 255 ) NOT NULL default '',
`status` tinyint( 2 ) NOT NULL default '0',
PRIMARY KEY ( `aid` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =1

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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=1' at line 25

Looks bad. What should I do now?

Comments

venkat-rk’s picture

I forgot to add that the tables did not load either. Is this a timeout problem? The sql file is 2.0 mb and was taking a long time to load.

maxferrario’s picture

I guess it is a problem with different mysql versions on the two hosts: are you able to compare them?

The server version is shown, for instance, when you connect to the database using mysql command line interface.

# mysql -u my_user -p my_password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 403 to server version: 4.0.22-Max-log

In this case, server version is 4.0.22 (you can ignore Max-log).

Different version are not always compatable: probably you are moving the database from a recent mysql version to a previous version that do not understand/support the DEFAULT CHARSET instruction.

bonobo’s picture

when moving from MySQL 4.1.x to 4.0.x

The "ENGINE = MyISAM" needs to be "TYPE=MyISAM", and most of the charset info can be discarded.

Compare the structure/syntax of the backup to the database.mysql that comes with the base drupal install, and look for areas where the syntax differs.

I usually clean my db backups using a text editor. And, if a db fails to restore cleanly, just drop any tables that were created, re-edit the .sql file, and try again.

Hope this helps.

Cheers,

bonobo
-------
http://www.funnymonkey.com
Tools for Teachers

venkat-rk’s picture

Sigh...

Time to learn the nitty gritties, I guess.

How would I go about comparing the structure/syntax? Also, can I open the .sql file in a text editor and do a find and replace for "ENGINE = MyISAM" to "TYPE=MyISAM"?

Edit: Also, does this mean that I will face the same problem if I make a fresh drupal install on the new host with the lesser version of MySQL?

venkat-rk’s picture

You are right. My old host is running 4.1.14 while my new host is running 4.0.25-standard, as they call it.

Is it possible to upgrade to a higher version of MySQL?

bonobo’s picture

you have over the server. In a shared hosting environment, the MySQL version is usually outside your control.

RE editing the .sql files, I usually do it exactly how you described: in a text editor using find/replace -- it is pretty straightforward once you sort it out, and you get faster as you move more sites.

Cheers,

bonobo

-------
http://www.funnymonkey.com
Tools for Teachers

venkat-rk’s picture

Thank you, bonobo.

Can you tell if I will have a similar problem when I do a fresh install of drupal on the server with the older MySQL version?

bonobo’s picture

The database.mysql file that ships with the standard drupal install is good for MySQL4.0.x and 4.1.x. I'm almost 100 percent sure it also works with MySQL 3.23.x

You will also have no problems if you are migrating a site from 4.0.x to 4.1.x.

About the only time I have had to manually alter the syntax is when I've had to move a site from 4.1.x to 4.0.x.

Hope this helps.

Cheers,

bonobo
-------
http://www.funnymonkey.com
Tools for Teachers

venkat-rk’s picture

Thank goodness! I would have been in a real mess otherwise.

Appreciate all your help.

Thanks,
Ramdak

venkat-rk’s picture

Strangely enough, when I checked the original database.sql of the drupal install related to this site, it says "TYPE=MyISAM" whereas it is "ENGINE-MyISAM" in the backup that I made recently.

bonobo’s picture

that determines the syntax on the db export. So, if you do a clean drupal install on a db using MySQL 4.1, and then do a db export to back up that new install, the syntax will be "ENGINE = MyISAM" -- even though the original file had the "TYPE = MyISAM" syntax.

In other words, a db export from MySQL 4.1 will always use syntax appropriate for 4.1; an export from MySQL 4.0 will always give you syntax appropriate for 4.0 -- the syntax used in the initial install does not play a role in determining the syntax of the export.

Cheers,

bonobo
-------
http://www.funnymonkey.com
Tools for Teachers

venkat-rk’s picture

The move went badly.

After finally getting it right in terms of the install, nothing else works properly. And, I spent nearly 3 hours cleaning the backed up sql file. Apparently much more was messed up (although the tables loaded, finally).

So, a backup doesn't seem to guarantee a fully working restore by any stretch of imagination. Gives me the jitters when I may have to do the same for a site with lots more content. Or, will command line sql give better results?

By the way, is there a text editor that will load two versions of the same file (the drupal.sql before and after), compare them line by line and present the differences between the two so that I can clean up what is not needed? I found it a big pain to clean up the sql file.

bonobo’s picture

I've moved countless sites as I described in this thread, and the only syntax I needed to change was the Engine/Type (as described above); the only syntax I needed to delete related to the "charset" info -- for example, lines like collate latin1_general_ci and DEFAULT CHARSET=latin1 would be deleted --

I do a db backup, then make a copy of that file, and do all my edits on the copy.

I usually use wordpad, and do a find/replace, and it takes 10-15 minutes. The first few times I did this, it took a LOT longer, but after I sorted out what I described in this thread, the whole process became pretty routine.

In reading the comments you posted in your link, I get the impression that some of the rows in your tables were deleted or changed -- in addition to the MySQL syntax. I would recommend trying to edit the file again, and reinstalling the db tables. If you just do a "Drop Tables" on the db for your site, you won't need to change any settings in your settings.php, because you will be keeping the same db and the same db user.

RE comparing files: the linux/unix "diff" command will do this. If you don't have access to a linux box, you can try installing Cygwin on a windows machine. If you are running on a Mac, you are in luck, because you have the Unix terminal right there.

I hope this helps.

Cheers,

bonobo

-------
http://www.funnymonkey.com
Tools for Teachers

venkat-rk’s picture

I got the site working perfectly again on the new host, thanks mainly to this excellent piece of documentation about importing and exporting databases.

I followed the instructions and did the entire move in less than 30 minutes. Didn't have to edit a single thing. I think I could have done it in 5 minutes if I had used command line both for the sql export and import and for copying the backed up drupal files.

What did the trick for me was the info left out in the article to which I was initially pointed. Let me quote midway from the how to part about exporting a database:

Make sure that you do the following:
1. Select all the tables
2. Check the "Drop Tables" checkbox
3. If the MySQL version says "MySQL 4.1.x" make sure you choose "SQL export compatibility: MySQL40" if you're going to import on a server that is running MySQL 4.0.
4. Complete inserts checked
5. Save as file checked
6. Compression set to "none"

Steps 3 and 4 were the most important. I didn't do Step2 as I was not deleting the tables, just exporting them.

Thanks for your help, bonobo. But for you, I would have never known where to look in the first place.

Ha! I really feel a sense of exhilaration now. Backing up and restoring a site is easy, after all! Now, I just need a script to backup the database and files automatically using cron.

ap’s picture

Thanks for this info Ramdak. You saved me from hours of futile work.

I had the same problem and adjusted things as you describe and it works beautifully. :)

inverarityp’s picture

I found that when I used this method, I would always end up with a file which didn't specify the fields that should be auto_increment. So, the access table would show up in the DB dump as:

CREATE TABLE `access` (
`aid` tinyint(10) NOT NULL,
`mask` varchar(255) NOT NULL default '',
`type` varchar(255) NOT NULL default '',
`status` tinyint(2) NOT NULL default '0',
PRIMARY KEY (`aid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

Which is annoying because then you have to go through by hand, and change the line

`aid` tinyint(10) NOT NULL,

to

`aid` tinyint(10) NOT NULL auto_increment,

and so on for each field that's supposed to be auto increment. This is a tedious process because not every table is supposed to have one, so you essentially need to look at a working drupal database and go through by hand adding the auto_increment tag where necessary. I found that an easier way was to skip Ramdak's step 3, and just let the file be exported in mysql 4.1 syntax. Then, you have something like:

CREATE TABLE `access` (
`aid` tinyint(10) NOT NULL auto_increment,
`mask` varchar(255) NOT NULL default '',
`type` varchar(255) NOT NULL default '',
`status` tinyint(2) NOT NULL default '0',
PRIMARY KEY (`aid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

This by itself will give you a syntax error if you try to import it in mysql 4.0, but the error is on account of the ENGINE= and the DEFAULT CHARSET bit. An easy way to do this is use a text editor with a find & replace function and have it replace every instance of:

ENGINE=MyISAM DEFAULT CHARSET=utf8

with:

TYPE=MyISAM

This will, of course, eliminate any trace of the DEFAULT CHARSET directive from your file, but it will allow you to import it into mysql 4.0 while retaining the auto_increment values and flags for every table. Basically, it still involves editing the text of the sql dump directly, but it allows you to make one automated, mechanical change rather than a potentially large number of changes by hand.

In my case, the charset didn't seem to be crucial because it worked fine even without that information. I fear that there would be a situation when this might pose a serious problem, and if anyone else could elucidate the charset matter with respect to importing tables from 4.1 to 4.0, I'd be very curious to know the details.

Hope this helps.

venkat-rk’s picture

Thanks for documenting this :)

Sure looks useful although I am also a bit wary about the charset. Might cause problems when this backed up site is upgraded.