MySQL Syntax error

[Blake_Ivey]

I recently stopped hosting my own blog and purchased a hosting provider. On my server I was running MySQL5 and the new host runs mySQL4. I did a mysqldumb of my database and got my backup.sql file. I changed the local settings to reflect a new database name. But anyway, I get this error when use phpMyAdmin to try and restore the sql file and get this:

SQL query:

CREATE TABLE `serendipity_access` (
`groupid` int( 10 ) unsigned NOT NULL default '0',
`artifact_id` int( 10 ) unsigned NOT NULL default '0',
`artifact_type` varchar( 64 ) NOT NULL default '',
`artifact_mode` varchar( 64 ) NOT NULL default '',
`artifact_index` varchar( 64 ) NOT NULL default '',
KEY `accessgroup_idx` ( `groupid` ) ,
KEY `accessgroupT_idx` ( `artifact_id` , `artifact_type` , `artifact_mode` ) ,
KEY `accessforeign_idx` ( `artifact_id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1

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 'DEFAULT CHARSET=latin1' at line 10
------------------------------

I dunno what to do. Any info would be great.
Blake
blakeivey.com

[judebert]

Okay, I can't just give you the magic bullet on this one, sorry. The error seems to indicate that your server doesn't support the latin1 charset, but could also indicate that an error occurred anywhere else in the query. (There's been posts in the forum on using UTF-8; I just don't remember if it's "do" or "don't". Search it up and find out.)

If you don't want to wait for Garvin or some other supergenius, what I can give you is a debugging technique.

Copy that whole query. Enter it in mysqladmin. Delete one bit at a time until the query works. If you're not familiar with SQL (although it seems you are), the bits in this case are the DEFAULT CHARSET bit, the ENGINE bit, then each KEY line and the comma at the end of the preceeding line, then each other line and the comma at the end of the preceeding line.

Eventually it'll work. The line you just deleted is the problem. Delete the table. Start over with the whole query and modify the offending line until it works. Delete the whole database (backup first), edit the SQL file, modify the offending line, and run it again.

Voila! You've discovered the next problem! It's probably the same as this problem. Change it in the entire SQL file, delete the database, and run it again.

Tah-dah! You're ready to post about your new problem on the forum!

[garvinhicking]

MySQL 4.0 does not support charsets.

You'll need to make a SQL dumpfile that is compatible to MySQL 4; recent phpMyAdmin Versions offer this in the "SQL compatibility" dropdown.

Apart from that, I can only nod at Judebert.You can alter your SQL dumpfile manually, if you care.

Regards,
Garvin

[Blake_Ivey]

Ok, I installed phpMyAdmin on my old server and exported my database with compatability to MYSQL4 (since I used 5, but now need 4) and get this when I try uploading the sql file:

-------
SQL query:

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 05, 2006 at 07:52 PM
-- Server version: 5.0.2
-- PHP Version: 5.0.2
--
-- Database: `blakeivey_blog`
--
CREATE DATABASE `blakeivey_blog` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci

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 'DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci' at line
-------

I'm pretty new to MySQL and PhpMyAdmin so I really dont want to edit the SQL file manually, and due to the Army, I am very limited to time. Any other ideas on what I'm doing wrong? Maybe not checking something properly in the exporting of the database?

Thanks,
Blake

[Guest]

Its Blake again...
I love the command line. I ddi the same command that phpMyAdmin ran but in the command line and it was accpeted. Now I just gotta tweak some of the permissions to the files and all should be working I hope.

Thanks.

Ill keep yall posted

Blake