#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    33
    Rep Power
    14
    I was using mysql table to store data and it was working fine with php code earlier. Suddenly it has stopped accepting data. There is no change in the php code as well as mysql table structure. Then I tried to enter data through mysql command line using following code.
    INSERT INTO xyz VALUES ( 'ads1' , 'ads2' , 'ads2' , 'ads2' , 'ads2' , 'ads2' , 'ads2' , 'ads2' , 'NULL' , 'ads2' , 'ads2');

    It gave me following error.
    ERROR 1062: Duplicate entry '127' for key 1

    Requesting urgent help to solve problem.
    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    81
    Rep Power
    14
    This error comes about because you've tried to insert another row into the table with the same unique key as an already existing table. This is not allowed as it prevents you from identifying which row you're looking for when you index on a given key. I don't know how much you know about DB design, or what the structure of your table is, so if you could post some more info that would be useful (esp. the meta-data (i.e. column names, not-null, key, index characteristics etc.)). For now, take the table:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    CREATE TABLE EMP
    ( EMP_ID INT(10) NOT NULL PRIMARY KEY,
    FIRST_NAME VARCHAR(50) NOT NULL,
    SURNAME VARCHAR(50) NOT NULL
    );
    [/code]

    When inserting to this table, we must specify all of those three columns (as they're all not null). So an insert might look like:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    INSERT INTO EMP VALUES (1, 'Chris', 'Tucker');
    [/code]
    Now, say we wanted to insert another employee, Joe Bloggs. We might try to do:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    INSERT INTO EMP VALUES (1, 'Joe', 'Bloggs')
    [/code]
    but that would fail because there is already a row in the table with the primary key column set to 1. If the database did allow such an insert to go through, the following query would be ambiguous:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    SELECT * FROM EMP WHERE EMP_ID = 1
    [/code]
    At this point, should it return the row corresponding to Chris Tucker, or the row corresponding to Joe Bloggs?

    The upshot of this is that when you insert you need to specify a different value for the primary key. So, to insert Joe Bloggs, I would want to do:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    INSERT INTO EMP VALUES (2, 'Joe', 'Bloggs');
    [/code]

    If you didn't know this stuff already I would _strongly_ recommend you purchase a book on databases. If you're comfortable with a textbook approach, you won't find much better than CJ Date's book on DB design. However, for a more 'normal' experience you may want to look to, for example, the O'Reilly MySQL and mSQL book.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    33
    Rep Power
    14
    my database table structure is as follows.:
    CREATE TABLE e1947df_replyer1 ( replyer varchar(20), replyer_email varchar(30), replyer_the_date varchar(12), replyer_the_time varchar(10), replyer_category varchar(20), replyer_subject varchar(100), replyer_matter BLOB, poster_sub_category varchar(70), id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID), rlogin varchar(50), remailp varchar(50)) ;

    upto 127 entries it was working fine. I am not giving value of id. It is auto increamental.
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    Your problem lies here:

    id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID),

    You've defined this field as a tinyint signed, (because you didn't declare it unsigned) so allowed values are -128 thru 127. After 127 entries you can't place a higher value in that field which is what the auto-increment is trying to do.

    Auto-increment fields should always be unsigned. Also, make sure the type of integer you use is big enough to hold the number of records you'll have in the table.

    You can fix the problem using the ALTER query to alter the field to a bigger integer and make it unsigned.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    33
    Rep Power
    14
    Thanks for help Rod. It works perfectly fine.

Similar Threads

  1. Very Very urgent please help.......
    By superprogrammer in forum Project Help Wanted
    Replies: 0
    Last Post: February 10th, 2004, 06:43 PM
  2. Javascript and PHP: urgent
    By tanvirgaus in forum JavaScript Development
    Replies: 3
    Last Post: January 20th, 2004, 12:53 PM
  3. (asp) urgent help with this code
    By wlhan in forum ASP Programming
    Replies: 2
    Last Post: October 27th, 2003, 07:08 PM
  4. Wierd Mail problem.. Urgent help required...
    By Jollyguy in forum PHP Development
    Replies: 2
    Last Post: May 2nd, 2002, 12:37 PM
  5. Requesting data from different tables
    By echo in forum MySQL Help
    Replies: 1
    Last Post: September 28th, 2001, 02:47 PM

IMN logo majestic logo threadwatch logo seochat tools logo