Anyone good with MySQL?

[GilesGuthrie]

I've built a development server using PHP5 on Apache 2 and MySQL 5.something. It works.

I've used phpMyAdmin to export the database off my live server, but when I try to import the database into the dev box, I get "ERROR 1060 (42S21) at line 70: Duplicate column name 'ID'".

I know it's a MySQL thing because I've tried using phpMyAdmin on the dev box and also the MySQL command line.

My database consists of a number of discrete tables that are managed using application-side logic. Consequently, the field name ID is used in most tables as an internal Primary Key.

Is MySQL seriously telling me that I have to uniquely identify the primary key in all 148 tables? And is this something that can be switched off in the MySQL config (because it's not a behaviour that the live box exhibits).

 

[Flerbizky]

Have you tried to manually, without phpMyAdmin, to dump the DB structure only and see if you can import that on your test box ?.

 

[GilesGuthrie]

I don't have terminal access to the live box, but I can dump the DB structure using PMA and try to import that. Good plan!

Edit: No, I get the same error.

 

[GilesGuthrie]

UPDATE (hence the new post)

I can manually create a new table with the duplicate column name. so it looks like I will have to manually rebuild the data structure, then import the data. It's a bit of a bind.

 

[Flerbizky]

Sorry - Was out for the last of tuesday so didn't see your post G.

Can you do a -no-data dump, get the DB structure - and send it to me ?. I'll see if I can replicate the error..

What version of MySQL are the boxes running ?..

 

[NLxAROSA]

Is MySQL seriously telling me that I have to uniquely identify the primary key in all 148 tables? And is this something that can be switched off in the MySQL config (because it's not a behaviour that the live box exhibits).

No, you can most definitely have a primary key column called 'ID' in every table. At work, all our tables have an 'ID' column.

Have you checked the table structure in the export for errors? I have run into the problems with an export from a database with phpMyAdmin where the import (in the same database even) resulted in errors because phpMyAdmin exported the database in a slightly different format. If I'm not mistaken, you can also tell phpMyAdmin what export format it should use.

Both the 'live' and 'dev' machines have the same database and version installed?

 

[GilesGuthrie]

Yeah, I'm beginning to suspect that there's something amiss with the way it's exporting at least one of the tables. I imported the rest of them on a table-by-table basis last night, so I'm down to the problem being in table-related, rather than database-related.

The boxes are quite different in versions.

MySQL Server version: (Live) 4.0.27-max-log; (Dev) 5.0.51a-3ubuntu5.4
MySQL Client version: (Live) 5.0.67; (Dev) 5.0.51a
phpMyAdmin version: (Live) 2.8.0.3; (Dev) 3.2.1

I don't control the config of the live box. I think it's running some version of Fedora, but I'm not sure. I think it's Apache 1.3 (again, not sure) and it's PHP 4.4.9. The dev box is running Ubuntu 8.04 (Hardy) with Apache 2.0, PHP 5.2.4-2ubuntu5.6 and MySQL 5.0.x.

I know that this could cause me problems in the future, but I reckon the dev project I'm starting is going to take at least 12 months, so I wanted the dev environment to be stricter than live. In other words, as the live box 'catches up' with dev, they'll tend toward the same software versions.

Fler, I'll send you the DB structure soon, if it continues to bother me.

 

[yeti]

What does the export file contain?

Is it raw SQL?

Can you show us the offending line? (70) - and perhaps the ones before it?

C.

PS - apologies if this is a necro-bump and already been solved!

 

[GilesGuthrie]

I've moved away from this project at the minute. I did manage to get most of the tables created though. I think there's something else awry with the dataset. If I run into the problem again, I'll re-revive the thread.