Is it Possible to downgrade from MySQL 5 to MySQL 4 - Ran into problem...BIG TIME

Hey Leif,

<i>Is there some other way to get these things set up from a new install? If I uninstall MySQL and PHP, and then uninstall or reinstall Virtualmin... Don't the install.sh then pick up the missing things? or am I totally wrong here?</i>

And if you do so, which version of MySQL will be installed? Somebody has to <i>build</i> those packages, if you don't want the ones that come from the Fedora Core 6 software repository (even our custom PHP4 packages are built to talk to MySQL 5, as provided by FC6...so, it won't work in your case, either).

If you need MySQL 4 on Fedora Core 6, we have to build it. If you then want PHP to talk to that MySQL version, we'll have to build it, too. Things get complicated when we go against the defaults in the OS, which is why we avoid it as much as possible.

Hey Leif,

<i>So... it end up with this question, is it possible to get this up and running in, lets say 12 hours, if so, it leavs me time for transfering the domains. If not, I'm stuck for another month with the old server. This gives me a month to upgrade/rewrite our code to work with MySQL 5.</i>

I don't know.

I'm setting up a pristine FC6 build environment as we speak...but I've gotta hit the sack (it's nearly 2AM here, and I've been up a long time...). I am hopeful that rebuilding PHP with bindings for MySQL 4 will not be difficult. It built OK on CentOS (for the same MySQL version that I built for you), so I suspect it'll build OK on FC6. I believe it'll probably work fine--but it'll still take an hour or so to build even if nothing goes wrong and it builds on the first try (which is probably more than we can hope for).

So, 12 hours is probably too much to hope for, but 24 is almost certainly do-able.

I don't mind building it--it's not actually taking a lot of human time. It's just that builds of big software in a sandbox environment (qemu in this case, since I can't easily use mock for this) takes a pretty long time, and if anything goes wrong I won't know until it's spent a bunch of time grinding away on the build. It's just a slow process, generally.

Hi Leif,

i do recall seeing they changed the way joins were processed in 5.0.12, luckily i've never had an issue with that.

To change the default character set for the server add the following to /etc/my.cnf under the [[mysqld]] header, replacing utf8 with your prefered defaults (latin1 &amp; probably latin1_swedish_ci)

[[mysqld]]
character-set-server=utf8
collation-server=utf8_general_ci

restart mysql, and to check that its worked login to mysql and execute the following

mysql&gt; show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql&gt; show variables like 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

This won't affect databases that have already been created, or new databases that are created and specify a default charset/collation. If your backups contain charset information for each table (or column) definition then they should be imported correctly. If not you will need to set them manually

mysql&gt; alter schema blah charset utf8 collate utf8_general_ci;
mysql&gt; alter table blah convert to charset utf8 collate utf8_general_ci;

Generally mysql backups do contain the charset information, and the conversion errors you are seeing are most likely because the connection to mysql from php is not using the right character set. I used to see this alot, until i enforced utf8 for everything, including the jdbc connections.

HTH, Cheers Chris

Hey Leif,

Downgrading MySQL can be a serious challenge...but in your case, I'm assuming you don't have any new data in the MySQL 5 database? That's the biggest problem with downgrading is getting the data to go downward (data that takes advantage of new features are impossible to import into the older version).

Let me see if I can build MySQL 4 packages for you real quick. I'll let you know in about an hour whether it'll be easy or not.

Oh, yeah...You're going to have to make sure any remnants of MySQL 5 are removed from the system after uninstalling. If there's anything in /var/lib/mysql, you'll need to get rid of it. It'd be asking for trouble to leave MySQL 5 data lying around during the switch to version 4.

Hey Leif,

That's also potentially a hard problem. ;-)

yum remove mysql might do the trick...I just realized, though that you'll also need PHP rebuilt, if your scripts are in PHP. This is going to get ugly. You're traveling in uncharted waters here.

Anyway...let's cross that bridge when we come to it. Removing MySQL can probably be done safely with:

rpm -qa | grep mysql

rpm -e whatever the previous command listed

This rpm -e command will probably be:

rpm -e mysql mysql-server mysql-devel php-mysql

But there might be more packages than that.

Once that's done, you can install the new ones (probably, though there might be some extra dependencies). And finally, we'll need to build the PHP MySQL bindings. I'll have to figure out how to do that for you...

Hey Leif,

<i>Is there some other way to get these things set up from a new install? If I uninstall MySQL and PHP, and then uninstall or reinstall Virtualmin... Don't the install.sh then pick up the missing things? or am I totally wrong here?</i>

And if you do so, which version of MySQL will be installed? Somebody has to <i>build</i> those packages, if you don't want the ones that come from the Fedora Core 6 software repository (even our custom PHP4 packages are built to talk to MySQL 5, as provided by FC6...so, it won't work in your case, either).

If you need MySQL 4 on Fedora Core 6, we have to build it. If you then want PHP to talk to that MySQL version, we'll have to build it, too. Things get complicated when we go against the defaults in the OS, which is why we avoid it as much as possible.

Hey Leif,

<i>So... it end up with this question, is it possible to get this up and running in, lets say 12 hours, if so, it leavs me time for transfering the domains. If not, I'm stuck for another month with the old server. This gives me a month to upgrade/rewrite our code to work with MySQL 5.</i>

I don't know.

I'm setting up a pristine FC6 build environment as we speak...but I've gotta hit the sack (it's nearly 2AM here, and I've been up a long time...). I am hopeful that rebuilding PHP with bindings for MySQL 4 will not be difficult. It built OK on CentOS (for the same MySQL version that I built for you), so I suspect it'll build OK on FC6. I believe it'll probably work fine--but it'll still take an hour or so to build even if nothing goes wrong and it builds on the first try (which is probably more than we can hope for).

So, 12 hours is probably too much to hope for, but 24 is almost certainly do-able.

I don't mind building it--it's not actually taking a lot of human time. It's just that builds of big software in a sandbox environment (qemu in this case, since I can't easily use mock for this) takes a pretty long time, and if anything goes wrong I won't know until it's spent a bunch of time grinding away on the build. It's just a slow process, generally.

Hi Leif,

i do recall seeing they changed the way joins were processed in 5.0.12, luckily i've never had an issue with that.

To change the default character set for the server add the following to /etc/my.cnf under the [[mysqld]] header, replacing utf8 with your prefered defaults (latin1 &amp; probably latin1_swedish_ci)

[[mysqld]]
character-set-server=utf8
collation-server=utf8_general_ci

restart mysql, and to check that its worked login to mysql and execute the following

mysql&gt; show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql&gt; show variables like 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

This won't affect databases that have already been created, or new databases that are created and specify a default charset/collation. If your backups contain charset information for each table (or column) definition then they should be imported correctly. If not you will need to set them manually

mysql&gt; alter schema blah charset utf8 collate utf8_general_ci;
mysql&gt; alter table blah convert to charset utf8 collate utf8_general_ci;

Generally mysql backups do contain the charset information, and the conversion errors you are seeing are most likely because the connection to mysql from php is not using the right character set. I used to see this alot, until i enforced utf8 for everything, including the jdbc connections.

HTH, Cheers Chris