Announcement

Collapse
No announcement yet.

mysql error: Duplicate entry '65535' for key 1

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql error: Duplicate entry '65535' for key 1

    A few hours ago I began to receive the following error;

    Database error in vBulletin 2.2.8:

    Invalid SQL: INSERT INTO attachment (attachmentid,userid,dateline,filename,filedata,visible) VALUES (NULL,16032
    ...
    ?<_c\0,THp'V','1')
    mysql error: Duplicate entry '65535' for key 1

    mysql error number: 1062

    I realise that others have gotten this error and that it has something to do with a hard limit being reached in the vb db, but I have not found a definative fix from the vb developers on this as of yet.

    I have read: http://www.vbulletin.com/forum/showthread.php?t=52198

    I'd very much like to hear from a vb developer on the exact steps to take to correct this issue, and any gotchas that might be encountered during the process. Please keep in mind that the attachment table on my db is 4.0GB in size.

    Will this effect the commands issued?
    Would this be better to perform from mysql command line -vs- phpMyAdmin.

    Thank you for your attention.

    Rick

  • #2
    I'm not a Dev so I can't speak for any 'gotchas' although I highly doubt there are any. However as with any changes made to the database you should make a backup first.

    The 'definitive fix posted by Mike (a Dev) in the thread you linked to is to run this query in phpMyAdmin:

    ALTER TABLE attachment CHANGE attachmentid attachmentid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
    Change CKEditor Colors to Match Style (for 4.1.4 and above)

    Steve Machol Photography


    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


    Comment


    • #3
      But what about the INT value in table Post and field attachmentid? This is what MRaburn was referring to in the linked thread. Should this also be changed? According to MRaburn, attachments did not work after he made the change Mike had suggested. He also had to alter the other table.

      Hrm, guess Im just gonna have to give this a try...

      Comment


      • #4
        Sorry I didn't read the rest of that thread. It looks like MRaburn's post should cover it.
        Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
        Change CKEditor Colors to Match Style (for 4.1.4 and above)

        Steve Machol Photography


        Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


        Comment


        • #5
          ok, now a good deal of nervousness sets in.

          I followed step 1;

          1. Change the INT value in table Attachement and field attachmentid.
          (This can be done with a query or PhpMyAdmin.)

          Change SMALLINT to INT and change Length from 5 to 10.

          ALTER TABLE `forum`.`post` CHANGE `attachmentid` `attachmentid` INT(10) UNSIGNED DEFAULT '0' NOT NULL

          I did this through phpMyAdmin using the "change" link for the field - not using the query.

          After submitting the request the browser stalled indefinately. It never finished, im guessing. I have not closed the window yet, but the browser appears to be stalled for certain.

          I opened up another browser window and accessed phpMyAdmin once again. The attachmentid field still lists that it is smallint(5). I dont think my update through the browser worked...but I am guessing that it did something

          Whats going on? Whats the best way to deal with this. Should I open a trouble ticket and reference this thread?

          Rick

          Comment


          • #6
            Actually step 1 should have been to make a backup of your db before attempting any changes.

            Honestly I think you did not give the process enough time to complete. After all you have a huge attachment table. However this is beyond my level of expertise so I would wait for a more authoritative answer.
            Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
            Change CKEditor Colors to Match Style (for 4.1.4 and above)

            Steve Machol Photography


            Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


            Comment


            • #7
              MySQL 3.23.54

              I have tried to change the attachementid field as instructed in the above thread. This did not work. Now DB hangs and eventually crashes the entire server!

              The tried to first run make the changes through phpMyAdmin, which caused the browser to stall and timeout. I then tried to issue the command;
              ALTER TABLE `forum`.`post` CHANGE `attachmentid` `attachmentid` INT(10) UNSIGNED DEFAULT '0' NOT NULL
              at the mysql> prompt and it also appears to be hanging. I have let the command run for over 20 minutes so far and no response from mysql yet.
              I dont know what I should do now. Other databases on the mysql server are also inaccessable due to the increased mysql processes that accumulate when I start apache.

              Comment


              • #8
                Did you try creating a new table named attachment2 using the current schema (this is an option with phpMyAdmin on the Operations tab I believe) then changing the field to INT?

                Then simply use
                SELECT FROM attachment INSERT INTO attachment2
                (I believe that syntax is correct, though as always BACKUP FIRST) and then drop the first attachment table, then rename attachment2 to attachment.

                Comment


                • #9
                  Can anyone please tell me the correct sql to run to try to alter my attachmentid field correctly? After restarting mySQL and running the above sql command I get the following error...

                  mysql> ALTER TABLE `forum`.`post` CHANGE `attachmentid` `attachmentid` INT(10) UNSIGNED DEFAULT '0' NOT NULL;

                  ERROR 1146: Table 'forum.post' doesn't exist

                  Any help would be greatly appreciated.

                  Comment


                  • #10
                    Originally posted by tubedogg
                    Did you try creating a new table named attachment2 using the current schema (this is an option with phpMyAdmin on the Operations tab I believe) then changing the field to INT?

                    Then simply use
                    SELECT FROM attachment INSERT INTO attachment2
                    (I believe that syntax is correct, though as always BACKUP FIRST) and then drop the first attachment table, then rename attachment2 to attachment.
                    Tubedogg, unfortunately, it is too late for your suggestion. I now have a problem with my attachment table that I need to get repaired/corrected before I can do anything else.

                    my attachment table is 4.0GB in size. phpMyAdmin is out of the question. I need help with mysql commands. Please.

                    rick

                    Comment


                    • #11
                      oh jesus, that SQL CANT BE RIGHT!!!

                      Mike suggests running this;
                      ALTER TABLE attachment CHANGE attachmentid attachmentid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

                      then a few posts down MRaburn says to run;
                      ALTER TABLE `forum`.`post` CHANGE `attachmentid` `attachmentid` INT(10) UNSIGNED DEFAULT '0' NOT NULL;

                      which is much differnet that what Mike suggests.

                      MIKE!!! Are you out there, bro! I need desperate help on this. The instructions in the linked thread appear to be incorrect, at least for my version of vb (2.2.8).

                      hrm...

                      Comment


                      • #12
                        ok, well - I tried to run the SQL Command that Mike suggested and got the following results...

                        mysql> ALTER TABLE attachment CHANGE attachmentid attachmentid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
                        ERROR 1030: Got error 28 from table handler

                        Anyone know about error 28 offhand?

                        Comment


                        • #13
                          Error 28 means the disk ran out of space.

                          Run
                          df
                          at the bash prompt to see free space on the various drives. It might just not have enough temporary space to complete the operation, especially if your attachment table is 4GB.

                          Comment


                          • #14
                            looks like my drive filled up suddenly while performing some of these commands. I had 2.0GB available before issuing commands, now 0K is available.

                            After searching for what ate up the remaining space, I ran into these files in the mysql/var/forum/ directory...

                            1.9G #sql-6a0d_b47de6.MYD
                            4.0k #sql-6a0d_b47de6.MYI
                            12k #sql-6a0d_b47de6.frm

                            Did mysql try to copy the entire table to backup or something? Is it ok to just delete these files? am I going to have to free up enough space to make a 4.0GB temp file?

                            I was desperately awaiting the release of vb3 so I could get all this data out of the database - is it too late now?

                            Any suggestions would be most welcome.

                            Comment


                            • #15
                              You can *probably* delete those files, but I do not want to say for certain. I would suggest trying to trim space in other places, turning off attachments for now, and I will ask our MySQL guru to step into this thread to help you.

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...
                              X