binary compatibility between different versions of MySQL?

[Tim Smith]

OK, what is supposed to happen in this situation? We have a mysqldump of a
database. We restore it on two different mysql servers, one running 5.0,
one running the latest in the 4.0.x series. MyISAM tables on both.

We then stop both servers, and do myisamchk on each. They are both happy.

We then copy the MYI and MYD files from each server to a temp directory on
the other, and check them again, so that we are using the 5.0 myisamchk to
check tables created by the 4.0 server, and vice versa.

Each reports that the tables have bad indexes and are corrupt.

I thought that MyISAM tables were binary compatable across versions of
MySQL?

Does this mean that when settup replication from a master to a slave, where
the slave is running a later version of mysql, that after taking the
snapshot on the master, it needs to by myisamchk'ed on the slave before
being used?

--
--Tim Smith

[Jerry Stuckle]

Tim Smith wrote:

> OK, what is supposed to happen in this situation? We have a mysqldump of a
> database. We restore it on two different mysql servers, one running 5.0,
> one running the latest in the 4.0.x series. MyISAM tables on both.
>
> We then stop both servers, and do myisamchk on each. They are both happy.
>
> We then copy the MYI and MYD files from each server to a temp directory on
> the other, and check them again, so that we are using the 5.0 myisamchk to
> check tables created by the 4.0 server, and vice versa.
>

What made you think you could do this?

> Each reports that the tables have bad indexes and are corrupt.
>
> I thought that MyISAM tables were binary compatable across versions of
> MySQL?

Why would you think so? It's never been documented to be the case.

>
> Does this mean that when settup replication from a master to a slave, where
> the slave is running a later version of mysql, that after taking the
> snapshot on the master, it needs to by myisamchk'ed on the slave before
> being used?
>

You just can't copy files from one server to another.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Tim Smith]

In article <RtOdnSiS7--ykhPZnZ2dnUVZ_v6dnZ2d@comcast.com>, Jerry Stuckle
wrote:

>> We then copy the MYI and MYD files from each server to a temp directory
>> on the other, and check them again, so that we are using the 5.0
>> myisamchk to check tables created by the 4.0 server, and vice versa.
>>

>
> What made you think you could do this?

One of my IT guys said he read that you could. :-) Doing some checking, it
looks like you can from 3.23.x to 4.0, but are supposed to dump and restore
if you have to go from 4.0 to 4.1, and 4.1 to 5.0. 5.0 and 5.1 are
compatible.

....

> You just can't copy files from one server to another.

Well, the procedure given in the manual for setting up replication suggests
doing just that. Flush tables with read lock, make a tarball of the mysql
directory, copy to the new slave, unpack the tarball into the mysql
directory.

--
--Tim Smith

[Gordon Burditt]

>OK, what is supposed to happen in this situation? We have a mysqldump of a

>database. We restore it on two different mysql servers, one running 5.0,
>one running the latest in the 4.0.x series. MyISAM tables on both.
>
>We then stop both servers, and do myisamchk on each. They are both happy.
>
>We then copy the MYI and MYD files from each server

DO NOT copy MYI and MYD files, unless the sole purpose is to create
I/O and network load.

>to a temp directory on
>the other, and check them again, so that we are using the 5.0 myisamchk to
>check tables created by the 4.0 server, and vice versa.

>Each reports that the tables have bad indexes and are corrupt.
>
>I thought that MyISAM tables were binary compatable across versions of
>MySQL?

Do not copy .MYI and .MYD files from one system to another using the
SAME version of MySQL. Or worse, a different one.

>Does this mean that when settup replication from a master to a slave, where
>the slave is running a later version of mysql, that after taking the
>snapshot on the master,

You take the snapshot with mysqldump, not cp, tar, ftp, or any other
file copy program.

Gordon L. Burditt

[Axel Schwenke]

Tim Smith <reply_in_group@mouse-potato.com> wrote:

>
> We then copy the MYI and MYD files from each server to a temp directory on
> the other, and check them again, so that we are using the 5.0 myisamchk to
> check tables created by the 4.0 server, and vice versa.
>
> Each reports that the tables have bad indexes and are corrupt.

This is correct.

> I thought that MyISAM tables were binary compatable across versions of
> MySQL?

Nope. MyISAM data files (.MYD) are compatible across different MySQL
versions and platforms. MyISAM indexes are compatible across platforms
but only compatible across MySQL instances of the same major version
using exactly the same settings for charsets/collations/stopwords etc.

Between 4.0 and 5.0 (that is: exactly in 4.1) MySQL changed a lot of
things around character sets and collations. It is hence neccessary
to check and recreate indexes. Read it here:

[url]http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html[/url]

> Does this mean that when settup replication from a master to a slave, where
> the slave is running a later version of mysql, that after taking the
> snapshot on the master, it needs to by myisamchk'ed on the slave before
> being used?

Yes. The recommended way to exchange data between different MySQL
versions is to use mysqldump. To initially seed an replication slave
you can also use LOAD TABLE FROM MASTER.


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]

[Jerry Stuckle]

Tim Smith wrote:

> In article <RtOdnSiS7--ykhPZnZ2dnUVZ_v6dnZ2d@comcast.com>, Jerry Stuckle
> wrote:
>

>>>We then copy the MYI and MYD files from each server to a temp directory
>>>on the other, and check them again, so that we are using the 5.0
>>>myisamchk to check tables created by the 4.0 server, and vice versa.
>>>

>>
>>What made you think you could do this?

>
>
> One of my IT guys said he read that you could. :-) Doing some checking, it
> looks like you can from 3.23.x to 4.0, but are supposed to dump and restore
> if you have to go from 4.0 to 4.1, and 4.1 to 5.0. 5.0 and 5.1 are
> compatible.
>

So? You can find almost anything on the internet. This was never officially
supported nor endorsed by MySQL, AFAIK. You can try it - but you're on your own.

> ...
>

>>You just can't copy files from one server to another.

>
>
> Well, the procedure given in the manual for setting up replication suggests
> doing just that. Flush tables with read lock, make a tarball of the mysql
> directory, copy to the new slave, unpack the tarball into the mysql
> directory.
>

Yes - when you're running THE SAME VERSIONS!


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================