No announcement yet.

Think i have a db problem, please help.

  • Filter
  • Time
  • Show
Clear All
new posts

  • Think i have a db problem, please help.

    i get the following message when i try to make a thread:

    Database error in vBulletin 3.0.0 Release Candidate 2:
    Invalid SQL: 
       SELECT COUNT(*) AS count
       FROM attachment
       WHERE posthash = '96d005af14823c84acf16e8ba4b0f8f7'
    	AND userid = 41466
    mysql error: Can't open file: 'attachment.MYD'. (errno: 144)
    mysql error number: 1016
    Date: Monday 22nd of March 2004 10:22:40 AM
    and the following message when i run the repair.php file:

    I have plent of DB space left.

    Please help i have my forums down till i can fix this

    Many thanks

  • #2
    Try running it again, some times it takes a few trys to get a table fully repaired


    • #3
      I have tried to run it a few times.

      this is the message i got from my host:

      hohoho. its passed 2gb in size. Unfortunately, you've hit
      the limit of mysql myISAM tables.

      Ouch, what can i do??


      • #4
        2 gigs in the attachment table? O.o

        Wow. um I really couldnt tell you at this point, ill point steve and or eva at this.


        • #5

          I am going to try to empty my attachment table i think []


          • #6
            i just tried to go into phpmyadmin to backup the attachment table and i get:
            MySQL said:

            Can't open file: 'attachment.MYD'. (errno: 144)Hmm. i want to delete the whole table and start afresh but i would like a backup first.


            • #7
              Originally posted by Jacko
              I have tried to run it a few times.

              this is the message i got from my host:

              hohoho. its passed 2gb in size. Unfortunately, you've hit
              the limit of mysql myISAM tables.

              Ouch, what can i do??
              Yeah hitting the OS file size limit will be a problem it's not a mysql table limit but OS limit

              With the MyISAM storage engine in MySQL Version 3.23, the maximum table size was increased to 8 million terabytes (2 ^ 63 bytes). With this larger allowed table size, the maximum effective table size for MySQL databases now normally is determined by operating system constraints on file sizes, not by MySQL internal limits.
              older stated limits
              Operating System  File-Size Limit  
              Linux-Intel 32-bit  2GB, much more when using LFS  
              Linux-Alpha  8TB (?)  
              Solaris 2.5.1  2GB (4GB possible with patch)  
              Solaris 2.6  4GB (can be changed with flag)  
              Solaris 2.7 Intel  4GB  
              Solaris 2.7 UltraSPARC  512GB
              Several people have commented that the maximum file sizes quoted here are out of date. Here is the most recent information I was able to find:

              ext2/3: Filesystem up to 16 TB, individual files to 2 TB.
              Reiser: Filesystem up to 17 TB, individual files to 2 TB.
              JFS: Filesystem up to 32 PB, individual files to 4 PB.
              XFS: Filesystem up to 16 EB, individual files to 8 EB.
              NTFS: Varies, but with default block size the maximum filesystem size is 16 TB. Files are limited only by the size of the volume.

              Note: The 2.4 Linux kernel has a 2 TB limitation on the size of a block device, so the very large limits above are, for the moment, theoretical. 2.5/2.6 should fix this limitation.

              Note: For purposes of this description, 1 EB = 1024 PB, 1 PB = 1024 TB, 1 TB = 1024 GB, etc
              really you should never let your attachment table grow so large in the database - using vB3 move attachments out of the database into the filesystem

              1. What linux version you're using ? PHPINFO url well tell you
              2. What file system is mysql partition on ? type in ssh telnet df -hT and it will show you something like below which indicates ext3 filesystem type
              3. how many rows are in the attachment table ? i.e. how many attachment files ?
              4. also what is the exact size of your attachement.MYD file ? you can find out by typing in ssh telnet mysqlshow -i vbdatabasename attachment and report data file length and max data file length

              5. post output of command

              mysqladmin -u mysqlusername -p variables

              > df -hT
              Filesystem    Type    Size  Used Avail Use% Mounted on
              /dev/sda1     ext3    342M  210M  114M  65% /
              /dev/sda5     ext3     12G  7.7G  3.4G  70% /home
              none         tmpfs   1008M     0 1008M   0% /dev/shm
              /dev/sda2     ext3    3.8G  2.8G  882M  77% /usr
              /dev/sdb1     ext3     17G  7.4G  8.6G  46% /var

              Other ways to work around file-size limits for MyISAM tables are as follows:

              - If your large table is read-only, you can use myisampack to compress it. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. myisampack also can merge multiple tables into a single table. See section 8.2 myisampack, the MySQL Compressed Read-only Table Generator.

              - Another way to get around the operating system file limit for MyISAM datafiles is by using the RAID options. See section 13.2.5 CREATE TABLE Syntax.

              - MySQL includes a MERGE library that allows you to handle a collection of MyISAM tables that have identical structure as a single MERGE table. See section 14.2 The MERGE Storage Engine
              unfortunately, i have no personal experience with these options
              Last edited by George L; Mon 22 Mar '04, 2:43am.
              :: 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 [ blog summary ]


              • #8
                Many thanx eva, will look into them great points. Am working closely with my host to resolve this.!