Mysql error

[osgood_]

Anyone encountered this error before.

I export my local database file .sql to my remote database server with phpMyAdmin - all works fine,  I make changes directly to the remote server database and now want to export that file back to my local environment using phpMyAdmin.

I get this error for any tables or databases I save from the remote server when I try and import the .sql file into the local database:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Thanks


Os

Humm.... I dunno what's happening. The local .sql file exports and works ok remotely but when I try and get the remote file back to local it's damaged goods?

[sinious]

Fairly common error, as I think you know you can only specify one primary key column. Are you using a typical 'id' column with AUTO_INCREMENT on it? If so, are you specifying that as primary key either in the column creation (id INT AUTO_INCREMENT primary key NOT NULL) or later on (id INT AUTO_INCREMENT NOT NULL, ..., primary key (id))? Either way just do a quick search for 'primary key' and 'AUTO_INCREMENT' and make sure there isn't 2.

[osgood_]

sinious wrote:

Fairly common error, as I think you know you can only specify one primary key column. Are you using a typical 'id' column with AUTO_INCREMENT on it? If so, are you specifying that as primary key either in the column creation (id INT AUTO_INCREMENT primary key NOT NULL) or later on (id INT AUTO_INCREMENT NOT NULL, ..., primary key (id))? Either way just do a quick search for 'primary key' and 'AUTO_INCREMENT' and make sure there isn't 2.

Hi Sinious,

Yup for sure there is only one Primary Key allocated to the auto increment id column - no other primary key is allocated to the other columns.

This is whats confusing me. I imported the locally exported .sql file to the remote database without any warning errors but when I came to do the reverse - that's when the errors started flagging up locally.

Here's my post from the DW forum (I know a couple of the varchar columns exceed 255 but they will be truncated if more than 255 characters are inserted - I'll put those right)

Weird - The below is what is saved from the remote database - note 'auto_increment' AFTER the 'product_id' - the rest is the same as saved from the local database - see below.

CREATE TABLE IF NOT EXISTS `products_services` (

  `product_id` int(11) NOT NULL auto_increment,

This is what is saved from the 'Local' database - no 'auto_increment' after the 'product_id'

CREATE TABLE `products_services` (

  `product_id` int(11) NOT NULL,

  `product_sector` varchar(200) NOT NULL,

  `product_category` varchar(200) NOT NULL,

  `product_year` varchar(50) NOT NULL,

  `product_title` varchar(500) NOT NULL,

  `product_sub_title` varchar(200) NOT NULL,

  `product_keywords` text NOT NULL,

  `product_route` varchar(50) NOT NULL,

  `product_introduction` text NOT NULL,

  `product_description` text NOT NULL,

  `product_date` date NOT NULL,

  `product_days` varchar(10) NOT NULL,

  `product_hotel` text NOT NULL,

  `product_includes` text NOT NULL,

  `product_url` varchar(500) NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=544 DEFAULT CHARSET=latin1;

-- Indexes for dumped tables

--

--

-- Indexes for table `products_services`

--

ALTER TABLE `products_services`

ADD PRIMARY KEY (`product_id`);

--

-- AUTO_INCREMENT for dumped tables

--

--

-- AUTO_INCREMENT for table `products_services`

--

ALTER TABLE `products_services`

MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=544;

The only way I could get the local database to suck in the information is add 'primary key' after 'auto_increment' (see below) but then I get the error #1068 - Multiple primary key defined. This error doesnt seem to affect the database - all the information still works - I just dont know why its happening.


`product_id` int(11) NOT NULL auto_increment primary key,

There's no issue with exporting locally to importing remotely - it's the return journey exporting remotely to importing locally which is throwing up the errors.

[sinious]

If that full table creation is what you're dumping then you can't have AUTO_INCREMENT or PRIMARY KEY enabled on that row, unless you're using a really incompatible/old version of mysql and phpmyadmin. If you haven't already tried, you might want to try a command-line dump locally to rule out phpmyadmin isn't an issue, because you really should have those properties in that definition. (e.g. mysqldump -u root -p dbname products_services > products_services.sql). Check out the schema and see if it matches what phpmyadmin is generating. If it does, it's time to hop back in your local DB and make sure you really have it set how you think you do (AI/PK product_id).

How different is the version of mysql and phpmyadmin from your local and remote server? Also you're using InnoDB on both of them correct? If your remote DB is MyISAM AUTO_INCREMENT can be added to a column as a multiple column index so if you're running between two different DB types AUTO_INCREMENT has differences.

You posted the entire create table from the local, can you post a full example from the remote? That'll shed a little more light on it. Specifically I don't know if you're dumping data back and forth but I do see you're setting AUTO_INCREMENT to start at a specific value (AUTO_INCREMENT=544). The more details the better.

[osgood_]

sinious wrote:

If that full table creation is what you're dumping then you can't have AUTO_INCREMENT or PRIMARY KEY enabled on that row, unless you're using a really incompatible/old version of mysql and phpmyadmin.

It's the only way I can get the information back into the local database. If I don't include 'primary key' on the product_id line the local version of phpMyAdmin just throws an error and the table is NOT created, If I add 'primary key' the table IS created but throws another error but that error doesnt seem to affect the data.

`product_id` int(11) NOT NULL auto_increment primary key,

I'm begining to think this is some kind of compatibility issue with the remote version of phpAdmin not being set up correctly to export the table or the local version not setting up teh database correctly in the first instance. What's troubling is local to remote goes smoothly so remote to local, the reverse should do as well - I've not changed any database setting on the remote server

When I have time I'm going to try and dump some data from other sites and see what happens when I try and bring that back into a local environment.

For now I've got the info back into the local setting and all is working locally but I've yet to export it again and put it back to the remote.

[sinious]

Just curious, what's the error it gives when you locally try to add the primary key (even though it's added)? That sounds a little dangerous if something errors yet works. Depends on the error though. Perhaps it found the PK was previously specified in the table or index and it's just warning you it's moving the PK to another column but that'd be a pretty big (potentially catastrophic) dialog.