Mysql error

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.