Database import error

davidwang · 2006-05-09 15:57:31

Hi, I’ve been having this problem when I try to export/import textpattern databases.

I develop my sites locally on my Mac running PHP Version 4.3.11 (www.entropy.ch Release 1)and MySQL 4.0.26-standard. Then my plan is to export the database and reimport it into my web host (following these instructions ‘Moving Installation From One Host To Another’).

Unfortunately, I always get error messages like this:

<code>
[ERROR in query 1] 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
[ERROR in query 3] 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=utf8 PACK_KEYS=1 AUTO_INCREMENT=13’ at line 42
…
…
</code>

I don’t think it’s a problem with my Mac. I have other software installed (e.g. phpBB and coppermine gallery) and I could export them and re-import them into my web host with no problem. I’ve also experienced this problem with textpattern on more than one host.

What’s the problem and how can I export/import my databases? Would rss_db_manager help?

KurtRaschke · 2006-05-09 15:58:25

What MySQL version is your web host running?

-Kurt

davidwang · 2006-05-09 23:57:41

The latest one I tried to import to is running MySQL 4.1.18-standard with PHP 4.4.2.

I’ve also tried it on one web host that runs MySQL 4.0.25-standard-log with PHP 4.4.2 with the same results.

Devs, any ideas? Anyone else experience the same problems?

hcgtv · 2006-05-10 00:45:52

David,

I think you can take out the Default Charset info.

This is an export from:
Server version: 4.0.24
PHP Version: 4.3.10-16

Textpattern table:
CREATE TABLE `textpattern` (
) TYPE=MyISAM PACK_KEYS=1 AUTO_INCREMENT=70 ;

This is an export from:
Server version: 5.0.21
PHP Version: 5.1.4

CREATE TABLE `textpattern` (
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 AUTO_INCREMENT=2 ;

Try it and see?

Last edited by hcgtv (2006-05-10 00:47:33)

davidwang · 2006-05-10 01:16:34

Hi hcgtv, forgive me but I have only really basic knowledge of MySQL. Are you suggesting to go into the MySQL dump, look for those lines above, delete them and then try to import the database again?

hcgtv · 2006-05-10 01:53:05

David,

In the SQL dump, do something like this:

Original:
CREATE TABLE textpattern (
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 AUTO_INCREMENT=2 ;

Changed:
CREATE TABLE textpattern (
) ENGINE=MyISAM PACK_KEYS=1 AUTO_INCREMENT=2 ;

Just take out the DEFAULT CHARSET=utf8 part or whatever you have in your dump, leave in the rest.

Sencer · 2006-05-10 08:01:25

DEFAULT CHARSET=utf8

If you have that in your dump, you have a mysql-version of at least 4.1 or newer. THe default format for dumps is not compatible with older versions. So when you are moving between different versions, you have to tell whatever utility you are using to do it correctly.

For example the command-line mysqldump utility has a flag named “compatibilitiy” for exactly this purpose. Here is an example of its usage:
http://dev.mysql.com/doc/refman/4.1/en/downgrading-to-4-0.html

Given that you can easily run into character-set issues when downgrading mysql, you should consider using the same mysql version for development/staging that you use for production.

davidwang · 2006-05-10 14:31:19

Hi Sencer, thanks for explaining that.

But umm… how do I do that in phpMyAdmin?

Being the newbie that I am, I went to phpMyAdmin’s SQL tab and typed:

mysqldump --create-options --compatible=mysql40 db_name > dump_file

phpMyAdmin then proceeded to give me an error:

#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 'mysqldump --create-options --compatible=mysql40 db_name > dump_file' at line 1

Btw hcgtv, removing CHARSET=utf-8 didn’t work :o(

Thanks

Last edited by davidwang (2006-05-10 14:37:02)

Sencer · 2006-05-11 10:39:22

I don’t use phpmyadmin much, but googling suggested this solution:

Get yourself the very latest version of phpMyAdmin and then when you do the export make sure you change the compatibility mode on the export screen to MYSQL4.x

Mary · 2006-05-12 23:56:29

mysqldump --create-options --compatible=mysql40 db_name > dump_file

FYI: that’s for running the mysqldump utility from the commandline, which is why it wouldn’t work in phpMyAdmin. :)

Textpattern CMS

Textpattern CMS support forum

#1 2006-05-09 15:57:31

Database import error

#2 2006-05-09 15:58:25

Re: Database import error

#3 2006-05-09 23:57:41

Re: Database import error

#4 2006-05-10 00:45:52

Re: Database import error

#5 2006-05-10 01:16:34

Re: Database import error

#6 2006-05-10 01:53:05

Re: Database import error

#7 2006-05-10 08:01:25

Re: Database import error

#8 2006-05-10 14:31:19

Re: Database import error

#9 2006-05-11 10:39:22

Re: Database import error

#10 2006-05-12 23:56:29

Re: Database import error

Board footer