MySQL CREATE TABLE IF NOT EXISTS in PHPmyadmin import

Question

I have the following code

CREATE TABLE IF NOT EXISTS `abuses` (
  `abuse_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0',
  `abuser_username` varchar(100) NOT NULL DEFAULT '',
  `comment` text NOT NULL,
  `reg_date` int(11) NOT NULL DEFAULT '0',
  `id` int(11) NOT NULL,
  PRIMARY KEY (`abuse_id`),
  KEY `reg_date` (`reg_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table with abuse reports' AUTO_INCREMENT=2 ;

this table already exists in the database, but when i import an sql file with phpmyadmin, the following error occurs

--
-- Dumping data for table `probid_abuses`
--
INSERT INTO  `abuses` (  `abuse_id` ,  `user_id` ,  `abuser_username` ,  `comment` ,  `reg_date` , `auction_id` ) 
VALUES ( 1, 100020,  'artictundra', 'I placed a bid for it more than an hour ago. It is still active. I thought I was supposed to get an email after 15 minutes.', 1338052850, 108625 ) ;

#1062 - Duplicate entry '1' for key 'PRIMARY' 

i thought because it already exists it won't attempt to create it, why is it behaving as such?

Integrator

Drop the table before creating and populating it. – Pandolfi

the table already exists, why would i drop it, the problem is that it should not be creating it in the first place – Integrator

Or delete * from table – Heretical

It's not recreating it - your first query is all but ignored since it already exists. It's trying to insert data that's already in there. – Pandolfi

@Heretical I think you mean TRUNCATE. Truncate will reset the autoincrement value where as DELETE won't. – Pandolfi

@MikeB Thanks,I didn't know. – Heretical

CREATE TABLE IF NOT EXISTS prevents the CREATE from causing an error, the error you're getting is from the INSERT statements that come after the create. – Manumission

Answer 1

On the CREATE TABLE,

The AUTO_INCREMENT of abuse_id is set to 2. MySQL now thinks 1 already exists.

With the INSERT statement you are trying to insert abuse_id with record 1. Please set AUTO_INCREMENT on CREATE_TABLE to 1 and try again.

Otherwise set the abuse_id in the INSERT statement to 'NULL'.

How can i resolve this?

Krill

Answer 2

it is because you already defined the 'abuse_id' as auto increment, then there is no need to insert its value. it will be inserted automatically. the error comes because you are inserting 1 many times that is duplication of data. the primary key should be unique. should not be repeated.

the thing you have to do is to change your insertion query as below

INSERT INTO  `abuses` (  `user_id` ,  `abuser_username` ,  `comment` ,  `reg_date` , `auction_id` ) 
VALUES ( 100020,  'artictundra', 'I placed a bid for it more than an hour ago. It is still active. I     thought I was supposed to get an email after 15 minutes.', 1338052850, 108625 ) ;

Reichsmark

Answer 3

Depending on what you want to accomplish, you might replace INSERT with INSERT IGNORE in your file. This will avoid generating an error for the rows that you are trying to insert and already exist.

See http://dev.mysql.com/doc/refman/5.5/en/insert.html.

Catalano

Answer 4

If you really want to insert this record, remove the `abuse_id` field and the corresponding value from the INSERTstatement :

INSERT INTO  `abuses` (  `user_id` ,  `abuser_username` ,  `comment` ,  `reg_date` , `auction_id` ) 
VALUES ( 100020,  'artictundra', 'I placed a bid for it more than an hour ago. It is still active. I thought I was supposed to get an email after 15 minutes.', 1338052850, 108625 ) ;

Smallpox

A fine suggestion but could break foreign keys elseware if you're doing a full database restore. Depends on what you're doing. – Pandolfi

@MikeB Right ... in case of a restore...but OP didn't mention that...simply said that he tried to import an sql file... – Smallpox

Answer 5

In your case, the first value to insert must be NULL, because it's AUTO_INCREMENT.

Karynkaryo

Answer 6

The code mentions the abuses table:

CREATE TABLE IF NOT EXISTS `abuses` (

And the error mentions probid_abuses, the another table:

-- Dumping data for table `probid_abuses`

I guess you assumed these two are the same table.

Neoimpressionism