Announcement

Collapse
No announcement yet.

mysql error: Duplicate entry '65535' for key 1

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

  • #16
    Originally posted by rick
    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.
    what does your /mysql/var/forum/ directory look like with this command

    ls -lah /mysql/var/forum

    do you have your attachment.* table myi, myd, frm files there or just those 3 files with #sql-* ?

    if you have space on another partition and your attachment. myi, myd, frm exist

    1. create new directory on another partition i.e. /home/tmpmysql
    2. shutdown mysql
    3. move

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

    to a /home/tmpmysql

    and reason why you got those files is they're probably the temporary files mysql used on the ALTER command but abruptly ended when you ran out of disk space

    ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed. This is done in such a way that all updates are automatically redirected to the new table without any failed updates. While ALTER TABLE is executing, the original table is readable by other clients. Updates and writes to the table are stalled until the new table is ready
    :: Always Back Up Forum Database + Attachments BEFORE upgrading !
    :: Nginx SPDY SSL - World Flags Demo [video results]
    :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

    Comment


    • #17
      -rw-r----- 1 mysql mysql 3.9G Sep 5 09:24 attachment.MYD
      -rw-r----- 1 mysql mysql 425k Sep 5 09:24 attachment.MYI
      -rw-r----- 1 mysql mysql 8.5k Jan 6 2003 attachment.frm

      All three attachment.* files exist, Eva, thank you for your assistance.

      Unfortunately, I do not have room on a different partition to copy those temp files to for safe keeping. I am going to be able to create 4GB of free space so that if I try it again, the table should have enough room to duplicate itself.

      ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed. This is done in such a way that all updates are automatically redirected to the new table without any failed updates. While ALTER TABLE is executing, the original table is readable by other clients. Updates and writes to the table are stalled until the new table is ready
      From the sounds of this, I MAY be ok. If the alteration is performed on the copy and the copy never finished then no alteration probably occured. However, im sure filling the disk was not to pleasant on the system.

      I am going to delete those temp files and make enough space to ensure that the 4.0GB attachment table has room to duplicate. This will take me a few minutes to complete, so if you or anyone else has any ideas or insights I sure would appreciate them asap.

      Thanks again, Eva - much appreciated.

      Comment


      • #18
        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.
        In MySQL, it's:
        Code:
        INSERT INTO table SELECT ...
        --filburt1, vBulletin.org/vBulletinTemplates.com moderator
        Web Design Forums.net: vB Board of the Month
        vBulletin Mail System (vBMS): webmail for your forum users

        Comment


        • #19
          Originally posted by rick
          -rw-r----- 1 mysql mysql 3.9G Sep 5 09:24 attachment.MYD
          -rw-r----- 1 mysql mysql 425k Sep 5 09:24 attachment.MYI
          -rw-r----- 1 mysql mysql 8.5k Jan 6 2003 attachment.frm

          All three attachment.* files exist, Eva, thank you for your assistance.

          Unfortunately, I do not have room on a different partition to copy those temp files to for safe keeping. I am going to be able to create 4GB of free space so that if I try it again, the table should have enough room to duplicate itself.


          From the sounds of this, I MAY be ok. If the alteration is performed on the copy and the copy never finished then no alteration probably occured. However, im sure filling the disk was not to pleasant on the system.

          I am going to delete those temp files and make enough space to ensure that the 4.0GB attachment table has room to duplicate. This will take me a few minutes to complete, so if you or anyone else has any ideas or insights I sure would appreciate them asap.

          Thanks again, Eva - much appreciated.
          ya need a new disk just for your mysql partition

          but i guess you can delete those files
          :: Always Back Up Forum Database + Attachments BEFORE upgrading !
          :: Nginx SPDY SSL - World Flags Demo [video results]
          :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

          Comment


          • #20
            Originally posted by filburt1
            In MySQL, it's:
            Code:
            INSERT INTO table SELECT ...
            Thank you I was half-asleep when I typed that

            Comment


            • #21
              Ok, I made enough room on the drive to create a full temp file of the 4.0GB attachment file. Once I had enough room for the operation, entered mySQL shell and issued the following command and got the following response;

              mysql> ALTER TABLE attachment CHANGE attachmentid attachmentid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
              Query OK, 61269 rows affected (17 min 47.52 sec)
              Records: 61269 Duplicates: 0 Warnings: 0

              Looks like that worked - and I have got all my space back from the 4.0GB temp file that was created then removed.

              Now, What do I have to do Next? I know there is another table that needs to be altered by I have NO IDEA what the SQL command is to alter the table?!?

              In this thread;
              http://www.vbulletin.com/forum/showthread.php?t=52198

              MRaburn suggests in addition to running the above sql that I also run the command;

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

              However, I know that this is INCORRECT. It is the exact same sql he suggested to run in place of Mikes SQL above, which worked.

              Eva, you still with me?

              MRaburn suggests that I do the following;
              2. Change the INT value in table Post and field attachmentid.

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


              Could anyone help me with the sql to perform this step, please?

              Thank you in advance.

              Rick

              Comment


              • #22
                ALTER TABLE post CHANGE attachmentid attachmentid INT(10) UNSIGNED DEFAULT '0' NOT NULL;

                You *should* run this query now. This will update your post table to accomodate the bigger integers that your attachment table can now use.

                Comment


                • #23
                  Originally posted by tubedogg
                  ALTER TABLE post CHANGE attachmentid attachmentid INT(10) UNSIGNED DEFAULT '0' NOT NULL;

                  You *should* run this query now. This will update your post table to accomodate the bigger integers that your attachment table can now use.
                  mysql> ALTER TABLE post CHANGE attachmentid attachmentid INT(10) UNSIGNED DEFAULT '0' NOT NULL;
                  Query OK, 412948 rows affected (1 min 18.82 sec)
                  Records: 412948 Duplicates: 0 Warnings: 0

                  Thank you everyone who was of assistance in this thread. After running the above two queries my board is running perfectly once again.

                  I admit, I thought all was lost for a time, but in the end the community has come to my aid once again. You all have my communities' deepest appreciation.

                  Thank you once again, Steve, Eva, MRaburn & Tubedogg!

                  Comment


                  • #24
                    You're quite welcome.

                    Comment


                    • #25
                      no probs mate
                      :: Always Back Up Forum Database + Attachments BEFORE upgrading !
                      :: Nginx SPDY SSL - World Flags Demo [video results]
                      :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

                      Comment

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