DB/table problem

--001636ed66f0f74f7d04733d5a55
Content-Type: text/plain; charset=ISO-8859-1

I am running mysql 5.0 on a windows 2003 host with php and apache
I am trying to restore a database from 08/28/2009. The backup is done by
windows and all I do is copy the "data/scroller612" folder to the DATA
folder in the MYSQL directory.

when I restore/copy it I get the following errors my .err file found on the
data directory:
==========================================================
090910 11:04:03090910 11:04:03 [ERROR] Cannot find table scroller612/cache
from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
See http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshootin g.html
how you can resolve the problem.
=========================================================

When I go to the above link it saids to run the "check table" statement,
but when I do this, I get below response:
=======================================================
mysql> check table scroller612.access;
+--------------------+-------+
----------+------------------------------------------+
| Table | Op | Msg_type | Msg_text
|
+--------------------+-------+----------+------------------- -----------------------+
| scroller612.access | check | Error | Table 'scroller612.access' doesn't
exist |
| scroller612.access | check | error | Corrupt
|
+--------------------+-------+----------+------------------- -----------------------+
2 rows in set (0.00 sec)
========================================================

It says that the table is corrupt. I can actually log into mysql and see the
list
of tables, but I can NOT desc or select the tables.

how do I restore the back up?
Do I need to remove the .frm files , but that would kill my data.

Thanks,

Nestor :-)

--001636ed66f0f74f7d04733d5a55--

RE: DB/table problem

When using innodb, your data is stored in the data/ibdata* files. Copying =
the database directory is not a valid backup.

In the future, I would suggest using mysqldump. However if you want to mak=
e a file-system copy backup with innodb you need to 1. Shutdown the server =
2. Copy *everything* in the data directory 3. Store a copy of your my.ini w=
ith the backup as well. 4. Restart mysql

Regards,
Gavin Towey

-----Original Message-----
From: N=E9stor [mailto:rotsen@gmail.com]
Sent: Thursday, September 10, 2009 11:28 AM
To: mysql@lists.mysql.com
Subject: DB/table problem

I am running mysql 5.0 on a windows 2003 host with php and apache
I am trying to restore a database from 08/28/2009. The backup is done by
windows and all I do is copy the "data/scroller612" folder to the DATA
folder in the MYSQL directory.

when I restore/copy it I get the following errors my .err file found on the
data directory:
==================== =====3D=
==================== =====3D=
========
090910 11:04:03090910 11:04:03 [ERROR] Cannot find table scroller612/cache
from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
See http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshootin g.html
how you can resolve the problem.
==================== =====3D=
==================== =====3D=
=======3D

When I go to the above link it saids to run the "check table" statement,
but when I do this, I get below response:
==================== =====3D=
==================== =====3D=
=====3D
mysql> check table scroller612.access;
+--------------------+-------+
----------+------------------------------------------+
| Table | Op | Msg_type | Msg_text
|
+--------------------+-------+----------+------------------- ---------------=
--------+
| scroller612.access | check | Error | Table 'scroller612.access' doesn'=
t
exist |
| scroller612.access | check | error | Corrupt
|
+--------------------+-------+----------+------------------- ---------------=
--------+
2 rows in set (0.00 sec)
==================== =====3D=
==================== =====3D=
======

It says that the table is corrupt. I can actually log into mysql and see th=
e
list
of tables, but I can NOT desc or select the tables.

how do I restore the back up?
Do I need to remove the .frm files , but that would kill my data.

Thanks,

Nestor :-)

The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: DB/table problem

On Thu, Sep 10, 2009 at 2:49 PM, Gavin Towey wrote:
> When using innodb, your data is stored in the data/ibdata* files. =A0Copy=
ing the database directory is not a valid backup.
>
> In the future, I would suggest using mysqldump. =A0However if you want to=
make a file-system copy backup with innodb you need to 1. Shutdown the ser=
ver 2. Copy *everything* in the data directory 3. Store a copy of your my.i=
ni with the backup as well. 4. Restart mysql

Do you exclude xtrabackup as a potential solution because he's using
Windows (it doesn't build on Windows) or because you have some issue
or problem with xtrabackup? I'm not aware of any show-stopper bugs.
I'm just curious, because it works well for us. The backup is online
with the only lock held occuring while it's copying over the *.frm and
*.MY* files, which is not very much for us.
--=20
Regards... Todd

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: DB/table problem

--001636b2bca0014c5a047341a525
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

OK, All I have then is a bunch of .frm files.

Can I recover the data from the FRM files if I install mysql 5.0 without
INNODB?

Thanks,


On Thu, Sep 10, 2009 at 2:49 PM, Gavin Towey wrote:

> When using innodb, your data is stored in the data/ibdata* files. Copyin=
g
> the database directory is not a valid backup.
>
> In the future, I would suggest using mysqldump. However if you want to
> make a file-system copy backup with innodb you need to 1. Shutdown the
> server 2. Copy *everything* in the data directory 3. Store a copy of your
> my.ini with the backup as well. 4. Restart mysql
>
> Regards,
> Gavin Towey
>
> -----Original Message-----
> From: N=E9stor [mailto:rotsen@gmail.com]
> Sent: Thursday, September 10, 2009 11:28 AM
> To: mysql@lists.mysql.com
> Subject: DB/table problem
>
> I am running mysql 5.0 on a windows 2003 host with php and apache
> I am trying to restore a database from 08/28/2009. The backup is done by
> windows and all I do is copy the "data/scroller612" folder to the DATA
> folder in the MYSQL directory.
>
> when I restore/copy it I get the following errors my .err file found on t=
he
> data directory:
> ==================== =====
==================== =====3D=
=========3D
> 090910 11:04:03090910 11:04:03 [ERROR] Cannot find table scroller612/cach=
e
> from the internal data dictionary
> of InnoDB though the .frm file for the table exists. Maybe you
> have deleted and recreated InnoDB data files but have forgotten
> to delete the corresponding .frm files of InnoDB tables, or you
> have moved .frm files to another database?
> See http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshootin g.html
> how you can resolve the problem.
> ==================== =====
==================== =====3D=
========
>
> When I go to the above link it saids to run the "check table" statement,
> but when I do this, I get below response:
> ==================== =====
==================== =====3D=
======
> mysql> check table scroller612.access;
> +--------------------+-------+
> ----------+------------------------------------------+
> | Table | Op | Msg_type | Msg_text
> |
>
> +--------------------+-------+----------+------------------- -------------=
----------+
> | scroller612.access | check | Error | Table 'scroller612.access'
> doesn't
> exist |
> | scroller612.access | check | error | Corrupt
> |
>
> +--------------------+-------+----------+------------------- -------------=
----------+
> 2 rows in set (0.00 sec)
> ==================== =====
==================== =====3D=
=======3D
>
> It says that the table is corrupt. I can actually log into mysql and see
> the
> list
> of tables, but I can NOT desc or select the tables.
>
> how do I restore the back up?
> Do I need to remove the .frm files , but that would kill my data.
>
> Thanks,
>
> Nestor :-)
>
> The information contained in this transmission may contain privileged and
> confidential information. It is intended only for the use of the person(s=
)
> named above. If you are not the intended recipient, you are hereby notifi=
ed
> that any review, dissemination, distribution or duplication of this
> communication is strictly prohibited. If you are not the intended recipie=
nt,
> please contact the sender by reply email and destroy all copies of the
> original message.
>

--001636b2bca0014c5a047341a525--

RE: DB/table problem

--_000_30B3DF511CEC5C4DAE4D0D290504753414A64BBF9EAAApmgiloca l_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

No, the .frm files don't contain data. They are only the table structure. =
You should always test your backup and restore procedure nefore you need t=
o use it.

Regards,
Gavin Towey

From: N=E9stor [mailto:rotsen@gmail.com]
Sent: Thursday, September 10, 2009 4:35 PM
To: Gavin Towey
Cc: mysql@lists.mysql.com
Subject: Re: DB/table problem

OK, All I have then is a bunch of .frm files.

Can I recover the data from the FRM files if I install mysql 5.0 without I=
NNODB?

Thanks,

On Thu, Sep 10, 2009 at 2:49 PM, Gavin Towey ffn.com>> wrote:
When using innodb, your data is stored in the data/ibdata* files. Copying =
the database directory is not a valid backup.

In the future, I would suggest using mysqldump. However if you want to mak=
e a file-system copy backup with innodb you need to 1. Shutdown the server =
2. Copy *everything* in the data directory 3. Store a copy of your my.ini w=
ith the backup as well. 4. Restart mysql

Regards,
Gavin Towey

-----Original Message-----
From: N=E9stor [mailto:rotsen@gmail.com]
Sent: Thursday, September 10, 2009 11:28 AM
To: mysql@lists.mysql.com
Subject: DB/table problem

I am running mysql 5.0 on a windows 2003 host with php and apache
I am trying to restore a database from 08/28/2009. The backup is done by
windows and all I do is copy the "data/scroller612" folder to the DATA
folder in the MYSQL directory.

when I restore/copy it I get the following errors my .err file found on the
data directory:
==================== =====3D=
==================== =====3D=
========
090910 11:04:03090910 11:04:03 [ERROR] Cannot find table scroller612/cache
from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
See http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshootin g.html
how you can resolve the problem.
==================== =====3D=
==================== =====3D=
=======3D

When I go to the above link it saids to run the "check table" statement,
but when I do this, I get below response:
==================== =====3D=
==================== =====3D=
=====3D
mysql> check table scroller612.access;
+--------------------+-------+
----------+------------------------------------------+
| Table | Op | Msg_type | Msg_text
|
+--------------------+-------+----------+------------------- ---------------=
--------+
| scroller612.access | check | Error | Table 'scroller612.access' doesn'=
t
exist |
| scroller612.access | check | error | Corrupt
|
+--------------------+-------+----------+------------------- ---------------=
--------+
2 rows in set (0.00 sec)
==================== =====3D=
==================== =====3D=
======

It says that the table is corrupt. I can actually log into mysql and see th=
e
list
of tables, but I can NOT desc or select the tables.

how do I restore the back up?
Do I need to remove the .frm files , but that would kill my data.

Thanks,

Nestor :-)
The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.


________________________________
The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--_000_30B3DF511CEC5C4DAE4D0D290504753414A64BBF9EAAApmgiloca l_--

RE: DB/table problem

Hi Todd,

I didn't mention xtrabackup because I have no experience with it =3D) I kn=
ow the other methods work, and honestly, for most mysql users just starting=
out, it's easier to use mysqldump. It's simple, and it's easy to get help=
with, and it works across storage engines.

Regards,
Gavin Towey

-----Original Message-----
From: tlyons@sitehelp.org [mailto:tlyons@sitehelp.org] On Behalf Of Todd Ly=
ons
Sent: Thursday, September 10, 2009 3:44 PM
To: Gavin Towey
Cc: mysql@lists.mysql.com
Subject: Re: DB/table problem

On Thu, Sep 10, 2009 at 2:49 PM, Gavin Towey wrote:
> When using innodb, your data is stored in the data/ibdata* files. Copyin=
g the database directory is not a valid backup.
>
> In the future, I would suggest using mysqldump. However if you want to m=
ake a file-system copy backup with innodb you need to 1. Shutdown the serve=
r 2. Copy *everything* in the data directory 3. Store a copy of your my.ini=
with the backup as well. 4. Restart mysql

Do you exclude xtrabackup as a potential solution because he's using
Windows (it doesn't build on Windows) or because you have some issue
or problem with xtrabackup? I'm not aware of any show-stopper bugs.
I'm just curious, because it works well for us. The backup is online
with the only lock held occuring while it's copying over the *.frm and
*.MY* files, which is not very much for us.
--
Regards... Todd

The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg