Mysql Duplicate entry problem, help!

[Anon]

hi, I had some problem about mysql error.

I created the table like that.
CREATE TABLE questions (
id tinyint(4) NOT NULL auto_increment,
sectionid tinyint(4) DEFAULT '0' NOT NULL,
question text,
type text,
bin_data BLOB,
filename char(50),
filesize char(50),
filetype char(50),
KEY id (id),
UNIQUE id_2 (id)
);
It worked fine for a little while. But now i can't insert any data into the table. For example, when i input the following

insert into questions(id,sectionid)
values(0,'45');

ERROR 1062: Duplicate entry '127' for key 1

any idea?

thanks in advance
Angela

[[deleted]]

Apparently you already have a record with id=0.
The id field is auto_increment, so you don't have to give a value to it when you insert records.

[angela]

hi, i tried the following:
insert into questions(id,sectionid)
values('','23');

the error is the same.
by the way, i had no record with id=0.

[Anon]

id tinyint(4) NOT NULL auto_increment and

insert into questions(id,sectionid)
values(0,'45');

are conflicting.

just use: insert into questions(sectionid) values(45);

[[deleted]]

Leave out the value for 'id' completely, like Joost said.

use:

insert into table (field) values (value);

not:

insert into table (id,field) value (0,value);

[angela]

what difference between tinyint and int data type?

does tinyint mean 1-128?

[[deleted]]

tinyint = -128 to 127 (check manual)

which makes it a very bad choice for auto_increment id's (only 128 usable values)

[Anon]

Thanks that fixed my problem too!!!!!!!!!

[Anon]

Thank you, I had this problem too, and it caused me a lot of stress! Next time I will remember to not use tiny-int again for id fields!!

[Anon]

helped me fix my problem, damn tiny integers, i am wondering, whats the best setting for an auto increment?

[[deleted]]

INT. not tinyint, not smallint,not mediumint, just int.

[Anon]

Hi,

I looked at Angela's duplcate entry problem and thought some of the solutions would solve it for me, but I'm not using a tinyint auto increment field. I've simplified by database to a primary key and a single date field and I still get duplicate entries when inserting with PHP4. When I use the identical insert statement in the mySQL interface only a single record is inserted.

create table tm_test (
id int unsigned primary key auto_increment,
first char (4)
);

I've tried:
$sql = "insert into tm_test (id, first) values ('','56')";

and

$sql = "insert into tm_test (first) values ('56')";

either way I get duplicate entries.

Any ideas, solutions out there?

Thanks

Steve

[[deleted]]

So you say you get a 'duplicate record' error when adding the record through PHP, but all is fine when you do it through the commandline?

Are you absolutely sure that both methods use the same table in the same database?

[Anon]

Vincent,

Thanks for the response. A few minutes after I sent the request I went through my PHP code again and found that I was adding a second record when I was checking for the result of the insert....

Steve

vincent wrote:

So you say you get a 'duplicate record' error when adding the record through PHP, but all is fine when you do it through the commandline?

Are you absolutely sure that both methods use the same table in the same database?