Professional Web Applications Themes

re-installed 4.1 -- can't read tables.... - MySQL

Hi, due to some problems I was having I re-intalled 4.1 the other day (had orig zip from when first downloaded (mysql-4.1.9-win32.zip), so was able to install exact same version I had previously) only problem is, I can't read tables... I had backed up my data, copied my db's back to 'data' dir but can't read the tables.. I get this error if I do describe table, or run a query: ERROR 1016 (HY000): Can't open file: '<tableName>.ibd' (errno: 1) (tables have extension .frm, though..) would very much appreciate some suggestions.. thank you.....

  1. #1

    Default re-installed 4.1 -- can't read tables....

    Hi, due to some problems I was having I re-intalled 4.1 the other day
    (had orig zip from when first downloaded (mysql-4.1.9-win32.zip), so was
    able to install exact same version I had previously) only problem is, I
    can't read tables... I had backed up my data, copied my db's back to
    'data' dir but can't read the tables.. I get this error if I do
    describe table, or run a query:

    ERROR 1016 (HY000): Can't open file: '<tableName>.ibd' (errno: 1)
    (tables have extension .frm, though..)

    would very much appreciate some suggestions.. thank you..




    maya Guest

  2. #2

    Default Re: re-installed 4.1 -- can't read tables....

    maya wrote:
    > I had backed up my data, copied my db's back to
    > 'data' dir but can't read the tables..
    It's amazing how many people do this. Copying files to/from the data
    dir is not a safe or effective way to back them up or restore them. You
    should use the "mysqldump" tool to back up, and the "mysql" tool to restore.
    > ERROR 1016 (HY000): Can't open file: '<tableName>.ibd' (errno: 1)
    > (tables have extension .frm, though..)
    The ".frm" file is just a description of the table structure; it
    contains no data from that table.

    This error indicates that you have a file
    '<datadir>/<database>/<tableName>.frm" but MySQL cannot find any InnoDB
    data for the table, either in the shared tablespace "<datadir>/ibdata1"
    or in the per-table tablespace "<datadir>/<database>/<tableName>.ibd".

    So you didn't restore the data when you copied files back to the
    datadir, only the structure of the table.

    By default, all your InnoDB tables share the ibdata1 tablespace file
    (unless you deliberately enabled per-table InnoDB files).

    I'm very sorry to say so, but if your ibdata1 file didn't get backed up
    and restored, then the data in that file is now gone.

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: re-installed 4.1 -- can't read tables....

    Bill Karwin wrote:
    > maya wrote:
    >
    >> I had backed up my data, copied my db's back to 'data' dir but can't
    >> read the tables..
    >
    >
    > It's amazing how many people do this. Copying files to/from the data
    > dir is not a safe or effective way to back them up or restore them. You
    > should use the "mysqldump" tool to back up, and the "mysql" tool to
    > restore.
    >
    >> ERROR 1016 (HY000): Can't open file: '<tableName>.ibd' (errno: 1)
    >> (tables have extension .frm, though..)
    >
    >
    > The ".frm" file is just a description of the table structure; it
    > contains no data from that table.
    >
    > This error indicates that you have a file
    > '<datadir>/<database>/<tableName>.frm" but MySQL cannot find any InnoDB
    > data for the table, either in the shared tablespace "<datadir>/ibdata1"
    > or in the per-table tablespace "<datadir>/<database>/<tableName>.ibd".
    >
    > So you didn't restore the data when you copied files back to the
    > datadir, only the structure of the table.
    >
    > By default, all your InnoDB tables share the ibdata1 tablespace file
    > (unless you deliberately enabled per-table InnoDB files).
    >
    > I'm very sorry to say so, but if your ibdata1 file didn't get backed up
    > and restored, then the data in that file is now gone.
    >
    > Regards,
    > Bill K.
    Bill, thank you very much for yr response.. it's not so grave.. mainly
    I was doing stuff as a learning exercise.. I found the 'create table'
    statements that I had copied into a text file and can at least recreate
    the same tables; however, even though I can't read the tables they DO
    show up when I do 'show tables', and problem is I want to delete them to
    create new ones w/same names but I can't event delete them!!

    (I may purchase a new machine in a few months, better figure out how to
    do this for when I do that.. certainly am saving yr response in a safe
    place..) again, many thanks...
    maya Guest

  4. #4

    Default Re: re-installed 4.1 -- can't read tables....

    maya <maya778899> wrote:
    > Bill Karwin wrote:
    >> By default, all your InnoDB tables share the ibdata1 tablespace file
    >> (unless you deliberately enabled per-table InnoDB files).
    >>
    >> I'm very sorry to say so, but if your ibdata1 file didn't get backed up
    >> and restored, then the data in that file is now gone.
    > I found the 'create table'
    > statements that I had copied into a text file and can at least recreate
    > the same tables; however, even though I can't read the tables they DO
    > show up when I do 'show tables', and problem is I want to delete them to
    > create new ones w/same names but I can't event delete them!!
    Right. If those tables were InnoDB tables and if you failed to restore
    the ibdata file, then your MySQL database is completely f*cked up now.
    You have to remove all data files and start over again.

    The reason is: the InnoDB engine does it's own housekeeping. So i.e. it
    has it's own data dictionary, containing a list of tables. Because of
    the MySQL architecture (SQL layer separated from storage engines) the
    same data is kept by the SQL layer in the .frm files. If you issue a
    SHOW TABLES statement, it is answered from the SQL layer, using the
    ..frm files. If you issue DROP TABLE, this is handled by the storage
    engine. Since the InnoDB data dictionary is empty, you get an "no such
    table" error on DROP TABLE.
    > (I may purchase a new machine in a few months, better figure out how to
    > do this for when I do that.. certainly am saving yr response in a safe
    > place..)
    Please consult the manual for recommended backup/recovery strategies:
    [url]http://dev.mysql.com/doc/refman/5.0/en/disaster-prevention.html[/url]

    Probably the easiest way to backup a complete MySQL instance is to use
    'mysqldump --all-databases --single-transaction > dumpfile'.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

Similar Threads

  1. WHY .SWF can not RUN after M F P installed?!
    By namviet in forum Macromedia Flash Player
    Replies: 0
    Last Post: April 13th, 10:08 AM
  2. Acrobat 6.0 installed (with no errors) but no printer was installed
    By Dale_Kalsow@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 4
    Last Post: May 3rd, 08:15 PM
  3. File system get auto change from read-write to read-oly
    By RC in forum Linux / Unix Administration
    Replies: 1
    Last Post: October 23rd, 10:51 PM
  4. Read & Read/Write Groups
    By Damien Harrod in forum Sun Solaris
    Replies: 1
    Last Post: July 22nd, 01:52 PM
  5. Pre-installed
    By John River in forum Windows Setup, Administration & Security
    Replies: 11
    Last Post: July 8th, 02:34 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139