Will I lose data when upgrading MySQL?

[bcr07548@creighton.edu]

Apparently my Linux distro came with a fairly old version of MySQL and
I need to upgrade before I can continue what I am working on. But if I
upgrade MySQL by installing the latest RPM package, will I lose my old
data? Or will the new MySQL software "automatically" find the old
files and let me use them?

-Brandon

[Kai Ruhnau]

[email]bcr07548@creighton.edu[/email] wrote:

> Apparently my Linux distro came with a fairly old version of MySQL and
> I need to upgrade before I can continue what I am working on. But if I
> upgrade MySQL by installing the latest RPM package, will I lose my old
> data? Or will the new MySQL software "automatically" find the old
> files and let me use them?

It would be easier, if you said which from which to which version you
will upgrade.

Reading <1137090247.715494.50730@f14g2000cwb.googlegroups. com> I guess
you will upgrade from 3.23 and since you want to use subquerys you may
want to upgrade to 4.1 (or even 5.0).

MySQL should have no problems reading the files from your @datadir and
use/upgrade them. Although it is wise to upgrade only over one release
series at a time (3.23->4.0->4.1->5.0). Have a look at:

[url]http://dev.mysql.com/doc/refman/4.1/en/upgrade.html[/url]
[url]http://dev.mysql.com/doc/refman/5.0/en/upgrade.html[/url]

Have a close look at the "Incompatible change" for every upgrade and
check which affectes your application(s).

The main problem you might have is when upgrading from MySQL 4.0 to 4.1.
4.1 introduced charactersets and collations. You should read the manual
about the implications and new options.

I myself dumped my data after upgrading to MySQL 4.1 and converted the
characterset and collation statements within the dump as well as the
dump itself to utf8/utf8_general_ci. But you might choose a different way.
Be carefull with setting a default caracter set for server or clients,
that might damage your character data if you for example stored utf8
data in (latin1) (VAR)CHAR columns.

HTH and greetings
Kai

--
This signature is left as an exercise for the reader.

[Bill Karwin]

<bcr07548@creighton.edu> wrote in message
news:1137090479.511519.291970@g14g2000cwa.googlegr oups.com...

> Apparently my Linux distro came with a fairly old version of MySQL and
> I need to upgrade before I can continue what I am working on. But if I
> upgrade MySQL by installing the latest RPM package, will I lose my old
> data? Or will the new MySQL software "automatically" find the old
> files and let me use them?

It is supposed to, but it is always prudent to assume something will go
wrong that "shouldn't happen". Backing up your databases with mysqldump is
the best way to ensure that it all goes smoothly. ;-)

Another tip about incompatibility between MySQL 3.x and 4.1: the passwords
are stored in a different encryption format by default in 4.1. So if you
restore the 3.x "mysql" database containing passwords (or any other database
with fields containing strings encoded with the PASSWORD() builtin
function), the 4.1 server may not be able to validate passwords.

Read these pages for more explanation and instructions on running the MySQL
4.1 server in a mode that understands old password encryption.
[url]http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html[/url]
[url]http://dev.mysql.com/doc/refman/4.1/en/old-client.html[/url]

Regards
Bill K.

[Benedictum]

Any other issues from 4.1 to 5.0?

"Bill Karwin" <bill@karwin.com> wrote in message
news:dq6d5e0em9@enews2.newsguy.com...

> <bcr07548@creighton.edu> wrote in message
> news:1137090479.511519.291970@g14g2000cwa.googlegr oups.com...

>> Apparently my Linux distro came with a fairly old version of MySQL and
>> I need to upgrade before I can continue what I am working on. But if I
>> upgrade MySQL by installing the latest RPM package, will I lose my old
>> data? Or will the new MySQL software "automatically" find the old
>> files and let me use them?

>
> It is supposed to, but it is always prudent to assume something will go
> wrong that "shouldn't happen". Backing up your databases with mysqldump
> is the best way to ensure that it all goes smoothly. ;-)
>
> Another tip about incompatibility between MySQL 3.x and 4.1: the
> passwords are stored in a different encryption format by default in 4.1.
> So if you restore the 3.x "mysql" database containing passwords (or any
> other database with fields containing strings encoded with the PASSWORD()
> builtin function), the 4.1 server may not be able to validate passwords.
>
> Read these pages for more explanation and instructions on running the
> MySQL 4.1 server in a mode that understands old password encryption.
> [url]http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html[/url]
> [url]http://dev.mysql.com/doc/refman/4.1/en/old-client.html[/url]
>
> Regards
> Bill K.
>

[Bill Karwin]

"Benedictum" <dominus@vobis.com> wrote in message
news:A7CdneFV1Iy_g1TeRVn-ig@comcast.com...

> Any other issues from 4.1 to 5.0?

I am not aware of any specific gotchas. Make sure you read
[url]http://dev.mysql.com/doc/refman/5.0/en/upgrade.html[/url]

Regards,
Bill K.