SQL: old sql dump not loading into new db

BrianK · 07-25-2006, 09:33 PM

My local webserver died so I rebuilt it. The production webserver I'm using runs Mysql 3.23. My local server runs 5.0. If I take my dumped sql db from the webserver & try to load it on the local server, I get:

mysql db < Fri.db-backup.sql
ERROR 1060 (42S21) at line 97350: Duplicate column name 'address_types_id'

the area in question looks like:

Code:

# head -n 97355 Fri.db-backup.sql | tail -n 10
--
-- Table structure for table 'address_types'
--

CREATE TABLE address_types (
  address_types_id int(4) NOT NULL auto_increment,
  address_types_description varchar(25) default NULL,
  address_types_abrv char(3) default NULL,
  PRIMARY KEY  (address_types_id,address_types_id)
) TYPE=MyISAM;

any idea how I can fix this?

b0uncer · 07-25-2006, 11:55 PM

As I have not worked with many versions of MySQL, and my knowledge is limited, I'd try a small trick: create a test database with the MySQL version you're trying to load the database into (the "problematic one"). Then see how the PRIMARY KEY section is written there, since that seems to be the problem in your case (address_types_id is twice there). If the new version only produces the key once and not twice (for example), alter your .sql file accordingly and re-try (remember to back-up the file so you can revert the changes if needed).