Duplicate entry mysql problems - but there isn't a duplicate!

[tekp]

Hi,

I'm trying to update my blog with the following query:

INSERT INTO `journal` ( `id` , `title` , `type` , `date` , `year` , `month` , `day` , `description` , `main` , `category` , `directory` , `visibility` )
VALUES ( '128', 'Stephen Fry Book Synopsis', 'note', '2005-11-10 02:21:55', '2005', '11', '10', '<p> I thought the synopsis of Stephen Fry\'s Ti... </p> ', '<p> I thought the synopsis of <a href=\"http://www.stephenfry.com\">Stephen Fry</a>\'s <em><a href=\"http://www.amazon.co.uk/exec/obidos/ASIN/1840244666/ref=pd_bxgy_img_2_cp/026-4396765-9207609\">Tish and Pish</a></em> on Amazon was too good not to share: </p> <blockquote cite=\"http://www.amazon.co.uk/exec/obidos/ASIN/1840244666/ref=pd_bxgy_img_2_cp/026-4396765-9207609\"> <div class=\"quote\"> The English tongue has never tasted more delicious than in the mouth of Stephen Fry: his chokingly brilliant sesquipedalian prose is like a shaft of sunlight through the drizzle of quotidian language. Now, with this bound monograph, we can all emit a similarly exquisite effulgence and enjoy the bright shaft of Stephen Fry locution in the privacy of our own smallest pavilion. May his shaft continue to pleasure us for many years to come. After all, what could be fluffier? </div> <div class=\"source\"> - <a href=\"http://www.amazon.co.uk/exec/obidos/ASIN/1840244666/ref=pd_bxgy_img_2_cp/026-4396765-9207609\">Amazon Book Synopsis</a> </div> </blockquote>', 'humour', 'frysynopsis', 'visible'
)

But whenever I do I get this message from MySQL:

#1062 - Duplicate entry '127' for key 1

At first, in my query, the ID field was left to '', so it would auto-increment. But then when it said this I figured it was just a problem with the auto-incrementing, so I put in '128' manually.

But it still says it.

Why does it still tell me there's a duplicate mysql entry when I'm specifying a different ID? Can anybody help? :D

Thanks a bundle.

[dmmh]

just because a key doesnt show up as a row doesnt mean it 'isnt there'
try re-building your table indexes with PHPMyadmin or whatever you use :)
That should solve it I think

[jc_armor]

just a thought, since it's an auto-increment field, you can just remove the 'id' field and value in your insert statement, and mysql will automatically assign an id to it.

[dmmh]

thats his problem, that dont work either, as he stated in his post ;)

[tekp]

try re-building your table indexes with PHPMyadmin or whatever you use :)

Yep I'm on PHPMyAdmin 2.6.3.

How would I go about re-building my table indexes?

I've looked under Operations but can't seem to find anything? Or is this a manual process?

Thanks :)

[Span]

REPAIR TABLE tbl_name QUICK;

rebuilds your table's index
[dev.mysql.com...]

[tekp]

Thanks but it didn't work :( that is, your query worked, but it hasn't solved the problem.

I asked the same question on another forum but nobody could help, I reckon it's a real humdinger, though it's most likely just some stupid mistake.

I'm thinking it's something to do with mysql? Oooh! Maybe I've exceeded my mysql space? But I doubt it...

Any other ideas?

[jatar_k]

well I would look through a couple things

first run a simple query like

select max(id) as topid from journal;

just to see what the highest id in there is. What can happen is you can insert a higher value and screw up the auto_increment on the table. This could be fixed with the repair statement mentioned.

You can also set the value of the auto increment as well, though the syntax I found for that doesn't quite seem the same as the one I remember.

start on this page and there are a ton of links
[dev.mysql.com...]

my next option would be to export the whole table to file, drop the table, recreate the table and reimport the data. Though I have no idea how to do all that in phpmyadmin, it would only take me a minute or so via the commandline.

[tekp]

I tried the query but all it returned was 127. I'm beginning to despise that number.

I'll try the last method when I have more time.

Thanks so far.

[jatar_k]

you could also try hand inserting a row or two, maybe even a much higher number and see what happens

[ergophobe]

[edit: assuming you've tried all of the above first!]

How important is the potential content of row 127? Would you consider the "throw caution to the wind" solution?

DELETE FROM table WHERE `id` LIKE '127';

Needless to say, a prior backup would be prudent!

[jc_armor]

quick question, what's the Data Type of your ID field? It could be a reason that the data type can only handle up to 127 :)

[tekp]

you could also try hand inserting a row or two, maybe even a much higher number and see what happens

I've tried that already, it's what I did first after my CMS didn't do it.

quick question, what's the Data Type of your ID field? It could be a reason that the data type can only handle up to 127 :)

:O Good point! The field's type is 'tinyint(6)', if that means anything?

DELETE FROM table WHERE `id` LIKE '127';

As a last resort :P

Thanks for your help so far, I greatly appreciate it.

[edited by: ergophobe at 5:09 pm (utc) on Dec. 8, 2005]
[edit reason] personal URL snipped as per usage agreement [/edit]

[jatar_k]

>> tinyint

hehe

check this out
[dev.mysql.com...]

then look at the table that says the maximum value for a tinyint column

nice jc_armor ;)

[ergophobe]

what's the Data Type of your ID field?

Damn you're smart! How obvious is that. 127 (aka 2^7 -1) should have sent up red flags all over. Good call JC and welcome to WebmasterWorld!

[tekp]

THANK YOU so much!

Would it be best to change it to a BIGINT or just leave it blank?

Eugh, you don't know how grateful I am.

Thanks again dudes :D

[jatar_k]

you could make it INT(8) or something, that should be more than large enough

[ergophobe]

Make it unsigned INT (I always use unsigned for auto-increment fields).

When you see the error message

#1062 - Duplicate entry '4294967295' for key 1

You'll know it's time to switch that col to BIGINT!

[tekp]

Ok I put it as a simple INT.

Thanks again :D

[jc_armor]

glad to contribute to this forum :) Thanks for welcoming me :)