Error Executing Batch File (sql)

[Bill Nguyen]

mysql Ver 12.22 Distrib 4.0.27

phpMyAdmin 2.7.0-pl2 – Default language= English UTF-8

I exported a database using the host's phpAdmin. When I tried to use “Import batch File”, I got the following errors:

1. (At the top)

Error occured at:2006-10-04 22:41:07

Error Code: 1193 – Unknown system variable 'NAMES'

Query:

Error occured at:2006-10-04 22:41:31

Error Code: 1193 – Unknown system variable 'NAMES'

Query:

INSERT INTO `mod_article_oldversions` VALUES (513, 36, 4, '2006-09-28 13:07:27', 1, 0, 0, 3, 'O:13:”phpws_article”:35:{s:2:”id”;s:2:”36″;s:5:”title”;s:-719:”test

[cut to save bandwidth]

2. (At the end

Error occured at:2006-10-05 05:49:11

Error Code: 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 7

Thanks

Bill

[peterlaursen]

You arw exporting from a MySQL 3.23 or 4.0 server and importing to 4.1 or higher. That won't work! SQL is different.

You have two option:

1) edit the SQL-file to fit the syntax of of the server version you import to.

2) I think that phpMyAdmin has an option to generate 'compatible backups'. You can try. But be perfectly sure that the charset on boths servers are the same!

MySQL is not really designed to 'work across versions'. We have our problems ourselves to keep STRUCTURE SYNC 'compatible' too for instance!

[Bill Nguyen]

The reason for restoring the database:

A table got corrupted and I cannot repair it using SQLyog table diagnostics tool to repair.

HEre's the error message:

#1033 – Incorrect information in file: './damauorg_website/mod_article.frm'

Thanks

Bill

[peterlaursen]

Code:

Default language= English UTF-8

Understand that right! MySQL 4.0.x does not support UTF8. But the backup can be UTF8 encoded anyway.

Nevertheless phpMyAdmin will need to be configured correctly for 4.0! It should NOT 'SET NAMES' with MySQL 4.0!

If you do not use SQLyog instead!

[Bill Nguyen]

peterlaursen wrote on Oct 5 2006, 01:21 PM:

You arw exporting from a MySQL 3.23 or 4.0 server and importing to 4.1 or higher. That won't work! SQL is different.

You have two option:

1) edit the SQL-file to fit the syntax of of the server version you import to.

2) I think that phpMyAdmin has an option to generate 'compatible backups'. You can try. But be perfectly sure that the charset on boths servers are the same!

MySQL is not really designed to 'work across versions'. We have our problems ourselves to keep STRUCTURE SYNC 'compatible' too for instance!

Peter;

Here's the situation:

my local mySQL server is 5.0

I exported the database from the host (mySQL 4.0) using phpmyAdmin. Then I use SQLyog “Import batch file” to import the .sql file successfully. I assume that this also converted the database into 5.0.

Can I export the database again in mysql 4.0 format ?

Thanks

Bill

[peterlaursen]

I'll try to understand in detail later tonight!

But please try SQLyog 5.2 beta5. Before that there was a problem with /*! ..*/ style comments ('conditional comments') Good chances that this is the issue!

[Bill Nguyen]

Quote:

But please try SQLyog 5.2 beta5. Before that there was a problem with /*! ..*/ style comments ('conditional comments') Good chances that this is the issue!

I installed 5.2 beta5 (Enterprise version) and got the same error:

Error occured at:2006-10-05 09:16:08

Error Code: 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 7

This doesn't make sense to me since I'm connecting directly to the host database (mySQL 4.0.27) and restored the same sql batch created by phpmyAdmin 2.7, how come it didn't work?

Please help!

Thanks

Bill

[peterlaursen]

Quote:

'DEFAULT CHARSET=latin1'

is invalid in a table definition with MySQL 4.0.

I cannot explain why phpMyAdmin writes that. Its configuration files must be 'told' that this is MySQL 4.1 or higher. Can you create a table with this PhpMyAdmin at all?

solutions:

Open that SQL file in an editor and 'search and replace' 'DEFAULT CHARSET=latin1' with '' (empty string).

remove 'SET NAMES .. ' statements a similar way. Replace ENGINE (if it is there) with TYPE (have a copy of course!). We will get rid of all those syntax'es that do not work with 4.0. Create a backup with SQLyog of a small table a see what it should look like!

How big is this file?

[Bill Nguyen]

peterlaursen wrote on Oct 5 2006, 05:25 PM:

Open that SQL file in an editor and 'search and replace' 'DEFAULT CHARSET=latin1' with '' (empty string).

remove 'SET NAMES .. ' statements a similar way. Replace ENGINE (if it is there) with TYPE (have a copy of course!). We will get rid of all those syntax'es that do not work with 4.0. Create a backup with SQLyog of a small table a see what it should look like!

How big is this file?

Peter;

My web host finally repaired the database and the error went away. I was able to execute the batch import to another database without incident.

Thanks

Bill

[Author]

Posts