kev wood Posted December 23, 2008 Share Posted December 23, 2008 i am trying to add a new entry in a mysql db and keep getting this error. Duplicate entry '127' for key 1 i have never seen this error before and dont know what it means. anyone now where i can get an explanation of this error and maybe a pointer on how to get rid of this problem. Link to comment https://forums.phpfreaks.com/topic/138161-solved-cannot-add-new-entry-to-db/ Share on other sites More sharing options...
Yesideez Posted December 23, 2008 Share Posted December 23, 2008 It means you're trying to add a record to your table and whatever you've got set as the primary key already has the same value existing that you're trying to add. You'll need to remove the primary key field from your INSERT query in order to make it work. Link to comment https://forums.phpfreaks.com/topic/138161-solved-cannot-add-new-entry-to-db/#findComment-722175 Share on other sites More sharing options...
kev wood Posted December 23, 2008 Author Share Posted December 23, 2008 no that is what i was thinking it would be but i was wrong. well not entirely wrong any way. it is a website that i had not built so the db was not set up by me and not set up in the same way as i would have set it up. the primary id had been set up as a tinyint and unknown to me until about 5 min ago a tiny int can only hold up to 128 pieces of information so it is 0 > 127 which equates to 128. the db was already holding 127 rows so it could not add another row to the db so it was trying to add the new row in row 127 which already exists. to fix the problem i have just changed the data type of the row to an int and it is all working again now. well at least i have reached my daily goal already in learning something new. i will now have a relaxing day since i have only been in for 2 hours. ha ha if only. Link to comment https://forums.phpfreaks.com/topic/138161-solved-cannot-add-new-entry-to-db/#findComment-722190 Share on other sites More sharing options...
Yesideez Posted December 23, 2008 Share Posted December 23, 2008 If you set the field to TINYINT(3) UNSIGNED it can hold up to 255. If there's a possibility that there's going to be more than 255 then change the datatype to something like MEDIUMINT. Link to comment https://forums.phpfreaks.com/topic/138161-solved-cannot-add-new-entry-to-db/#findComment-722193 Share on other sites More sharing options...
kev wood Posted December 23, 2008 Author Share Posted December 23, 2008 i have just read something that said that a tinyint could only hold up to 127 pieces of information and the tinyint in the db had been set as a tinyint(4) and would not hold anymore information than 127 rows. strange. if you say it can hold up to 255 with a tinyint(3) why would it not go passed 127 for a tinyint(4). is tinyint(4) the wrong syntax for this data type can it only be set as a tinyint(3). Link to comment https://forums.phpfreaks.com/topic/138161-solved-cannot-add-new-entry-to-db/#findComment-722201 Share on other sites More sharing options...
Yesideez Posted December 23, 2008 Share Posted December 23, 2008 There are two states a numerical datatype can be in: SIGNED or UNSIGNED. SIGNED can hold negative values and UNSIGNED can't SIGNED TINYINT: -127 to +127 UNSIGNED TINYINT: 0 to 255 Link to comment https://forums.phpfreaks.com/topic/138161-solved-cannot-add-new-entry-to-db/#findComment-722204 Share on other sites More sharing options...
Yesideez Posted December 23, 2008 Share Posted December 23, 2008 btw, you can't have TINYINT(4) You can have 1-3. Check this page out: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html Lists what values the numerical data types can hold. Link to comment https://forums.phpfreaks.com/topic/138161-solved-cannot-add-new-entry-to-db/#findComment-722206 Share on other sites More sharing options...
kev wood Posted December 23, 2008 Author Share Posted December 23, 2008 so from what i can see the db had been set up with a signed tinyint but the increment had started from 0 so it could have only ever got up to 127 as this is the highest number it can hold when it is a signed data type. didnt think you could have that from what i have just been reading. for what he had set the db up for i would have just used int for the id to start with. i would not have had these problems to begin with and tinyint should not be used for a db as it is to small a number i feel. i had just found this page before your post. http://www.codingforums.com/showpost.php?p=540233&postcount=6 Link to comment https://forums.phpfreaks.com/topic/138161-solved-cannot-add-new-entry-to-db/#findComment-722208 Share on other sites More sharing options...
Yesideez Posted December 23, 2008 Share Posted December 23, 2008 Bearing in mind that INT is a silly large number do you really expect that number of lines? I mostly use SMALLINT - just saves space in the database. Link to comment https://forums.phpfreaks.com/topic/138161-solved-cannot-add-new-entry-to-db/#findComment-722213 Share on other sites More sharing options...
kev wood Posted December 23, 2008 Author Share Posted December 23, 2008 well if i do use int at least i will not have to back to the db for a while. good point about not using the int as it is a ridiculously high number. Link to comment https://forums.phpfreaks.com/topic/138161-solved-cannot-add-new-entry-to-db/#findComment-722216 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.