MySQL error #1064 while importing databases to new host

t0ma · 2006-12-15 09:10:28

Hi all

I get strange error while importing databases to a new host:

#1064 - You have an error in your SQL syntax near 'ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 PACK_KEYS=1 AUTO_INCREMENT=' at line 46

I have to say that I am not very good with MySQL altough I am familiar with txp. Tried to install RSS_admin_database_manager but didn´t got it working with my 4.0.4.

Any help would be appreciated, as I should go for xmass holidays and want to get this off my back.

Cheers

Sencer · 2006-12-15 09:23:00

Which mysql-version is the server where you are trying to install your backup to? Looks like youare moving your data between different versions. The manual says:

The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE is supported throughout the 4.x series, but likely will be removed in the future.

Since you are using ENGINE in that statement, my guess is you are “downgrading”. Depending on which versions are involved, you might want to look at the mysql-manual for specific relevant information. If you are using mysqldump, it has a -compatible switch which will come in handy.

--compatible=name

Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See Section 5.2.5, “SQL Modes”.

This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example, —compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.

Source: http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html.

Last edited by Sencer (2006-12-15 09:24:00)

t0ma · 2006-12-15 09:27:53

Thank you sencer for fast reply.

Again, you were correct with versions. I am moving from MySQL 5.0.24 to MySQL 3.23.52.

I will post the solution here when get it working.

t0ma · 2006-12-15 12:24:35

I still get this warning:

#1064 - You have an error in your SQL syntax near 'ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 PACK_KEYS=1 AUTO_INCREMENT=' at line 46

Does this mean that textpattern databases are not backward compatible from Mysql 5.x to 3.2.3 version.

My dump goes like this:

mysqldump --compatible=323 -u myUser -p myDB > myDBDump.sql

Any ideas?

ruud · 2006-12-15 12:32:40

try: --compatible=mysql323 instead of --compatible=323

t0ma · 2006-12-15 12:37:02

My bad, I just wrote code wrongly on forum but correctly on dump sentence. Problem still exists.

Sencer · 2006-12-15 13:05:19

Does this mean that textpattern databases are not backward compatible from Mysql 5.x to 3.2.3 version.

This has nothing to do with textpattern. This is a pure mysql problem. If you’re asking if it is possible to downgrade data from mysql5 to mysql3, I believe it is.

My furst guess would be that you are using the wrong (i.e. the older) file. When doing a backup with the parameter as above, it should not contain ENGINE=MYISAM anymore (also not the charset stuff). Look for error-messages when doing the dump. Also make sure that you have deleted all dumpfiles on all computers, before starting the dump, to prevent that you accidently use the old file.

Of course it is possible that it is a mysqlbug, like this one:
http://bugs.mysql.com/bug.php?id=2634 (It likely won’t be that one, but could be a similar one). But at this point I wouldn’t jump to conclusions.

mattgilbert · 2007-06-09 14:26:50

I’m having this same problem. I’ve tried changing the “SQL compatibility mode” in phpMyAdmin to “MYSQL323” and “MYSQL40”, since I’m actually moving from MySQL version 4.1.15 to 4.0.27. I still get the #1064 error, and the mysqldump still has “— Server version: 4.1.15” in the header. So maybe this phpMyAdmin is ignoring the compatibility setting for some reason. Any ideas?

ruud · 2007-06-09 14:47:37

Please post the exact error message you get and also the complete line from the dump file that it refers to.

mattgilbert · 2007-06-09 14:54:17

Sorry, poor form. Here’s the error:

Error
SQL query:
— phpMyAdmin SQL Dump
— version 2.9.0.2
— http://www.phpmyadmin.net
—
— Host: remotemysqlhost
— Generation Time: Jun 09, 2007 at 09:15 AM
— Server version: 4.1.15
— PHP Version: 4.4.2
—
— Database: `uncohel8_mattgilbert`
—
CREATE DATABASE `uncohel8_mattgilbert` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
MySQL said: Documentation

- 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

The line in the mysqldump was in the header, which is copied in the error message above. “— Server version: 4.1.15” is the entire line, as you can see above.

ruud · 2007-06-09 15:00:39

You can try manually removing the “DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci” parts from the dump file and import it again in Mysql 4.0

mattgilbert · 2007-06-09 15:16:34

Ok, that worked. Thanks a lot!

Textpattern CMS

Textpattern CMS support forum

#1 2006-12-15 09:10:28

MySQL error #1064 while importing databases to new host

#2 2006-12-15 09:23:00

Re: MySQL error #1064 while importing databases to new host

#3 2006-12-15 09:27:53

Re: MySQL error #1064 while importing databases to new host

#4 2006-12-15 12:24:35

Re: MySQL error #1064 while importing databases to new host

#5 2006-12-15 12:32:40

Re: MySQL error #1064 while importing databases to new host

#6 2006-12-15 12:37:02

Re: MySQL error #1064 while importing databases to new host

#7 2006-12-15 13:05:19

Re: MySQL error #1064 while importing databases to new host

#8 2007-06-09 14:26:50

Re: MySQL error #1064 while importing databases to new host

#9 2007-06-09 14:47:37

Re: MySQL error #1064 while importing databases to new host

#10 2007-06-09 14:54:17

Re: MySQL error #1064 while importing databases to new host

#11 2007-06-09 15:00:39

Re: MySQL error #1064 while importing databases to new host

#12 2007-06-09 15:16:34

Re: MySQL error #1064 while importing databases to new host

Board footer