Trouble importing MySQL DB

started after updating MySQL

[Storyman]

Just updated MySQL on a testing server along with PHP 4.3 and Apache 1.3.33.

Before updating there was no problem exporting the DB from the testing server, then importing it to the web hosting server.

This is the error message:
#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 'collate latin1_bin default NULL,
`paragraph_1a` text characte

I'm not well versed in MySQL and have no idea what it means with 'collate' or error code #1064.

When exporting this is what is checked:
STRUCTURE
Add Auto Increment
Enclose tabs and field names w/ backquotes

DATA
Use hexadecimal for finary fields.

Export type: Insert

Compression: None

Is there something that needs to be checked or unchecked?

[RonPK]

The problem with MySQL error messages is that they usually leave out the part where things go wrong. Can you please post the line with the full SQL statement?

[Storyman]

Ron,

I want to be sure that I've provided what you are asking for. On the tester server the db table was exported. This includes everything up to where the error message indicates the problem is.

-- Table structure for table `generic_20`

CREATE TABLE `generic_20` (
`user_name` int(3) NOT NULL auto_increment,
`heading_1` varchar(80) character set latin1 collate latin1_bin default NULL,
`paragraph_1a` text character set latin1 collate latin1_bin,

After posting I came up with some additional information. Files that were exported from the previous installed version of MySQL (and not yet imported and then exported from the current MySQL version) imported into the web site's MySQL. However, if the file was saved from the current version it does not import into the web site's MySQL.

Because of your posting I compared an exported file from the previous MySQL and one from the current MySQL. The current version is the one above. Below is from the previous version.

# Table structure for table `states`
#

CREATE TABLE `states` (
`state_id` tinyint(2) NOT NULL auto_increment,
`state` varchar(20) NOT NULL default '',
`abbrev` char(2) default NULL,
PRIMARY KEY (`state_id`)
) TYPE=MyISAM AUTO_INCREMENT=60 ;

#
# Dumping data for table `states`
#

INSERT INTO `states` VALUES (7, 'Colorado', 'CO');
INSERT INTO `states` VALUES (6, 'California', 'CA');
INSERT INTO `states` VALUES (5, 'Arkansas', 'AR');

As you can see there is quite a bit of difference in the was the data is layed out. Is there a way to have the latest version of MySQL to format the export like the previous version? The collate thing isn't needed with the version of MySQL installed on the web site.

[RonPK]

Collation was introduced in MySQL version 4.1, so I guess you're trying to import a 4.1-table into a previous version of MySQL. For some reason your export tool (PHPMyAdmin?) adds character and collation statements to the table definition - probably because that's the way the table was created?

Anyway, if you don't need the collation stuff maybe it's easiest to simply remove 'character set latin1 collate latin1_bin' from the table creation commands in your export file.

CREATE TABLE `generic_20` (
`user_name` int(3) NOT NULL auto_increment,
`heading_1` varchar(80) default NULL,
`paragraph_1a` text ,

Please note that I'm not sure whether this is OK with binary fields.

[Storyman]

Ron,

Thanks for your feedback.

Okay, the problem comes down to the difference of MySQL's 4.0 & 4.1 (So much for reading the fine print.)

Migrating from 3.23 to 4.1 was a mistake because 4.1 has a 'new and better' format that does not work with earlier versions.

The best thing for me is to remove 4.1 and install 4.0. As you mention 4.1 uses collate in the db files.

[Salsa]

Before you do that, what results do you get if you use mysqldump [dev.mysql.com] from the command line on your local machine? eg:

mysqldump -u username -p --skip-opt database_name > export_file_name.sql

The --skip-opt option skips options that are on by default in newer versions of MySQL, and it's recommended to include it when you plan to import the data to older versions.

I hope this helps.

[Storyman]

Salsa,

I've already removed 4.1 and replaced it with 4.0--the issue is gone. The main reason for making the switch is that my web host still uses 4.0 and it makes life a lot easier if I conform to their setup.

[Salsa]

That's probably the best plan. I always try to keep the same versions on development machines as I have on production servers--just for that reason.