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

120 posts / 0 new
Last post
Sun, 06/07/2009 - 07:02
Joe
Joe's picture

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.

--

Check out the forum guidelines!

Sun, 06/07/2009 - 07:02
Blueforce

Hi,

I told you it was a stupid question... :-)

Ok, the reason for all my questions is that my problem here is NOT caused by any of your software, and I was just thinking if there was some &quot;shortcut&quot; to get this to work. And mainly to NOT give you guys more problems to solve.

I really appreciate all the help we get from you!

Do you think this is the way to go... or should I once more extend our contract for our old box, so I can rewrite those sites to be compatible with MySQL 5. It's a rather big job, but on the other hand I don't know how much work YOU have to put in to get MySQL 4 working on/with FC6.

I'm also is in a rather tight time frame here, the old box is supposed to get &quot;killed&quot; in two days (by the 24th).

I Don't know how much work or time it would take to get MySQL 4, PHP and all other stuff it involves up and running. I think I need about one day to move the domains. I can't do backup and restore on all the domains. Some of them needs to be moved/updated manually. So this gives me just one day for MySQL 4. And of course, to get it to work I NEED your support, and I can't take for granted that you guys are there to &quot;fix&quot; this for me in this short time.

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.

What do you think, should I go for MySQL 4 and the dedline by the 24 of April. Or should I go the &quot;safe&quot; way ???

Regards,
Leif

Sun, 06/07/2009 - 07:02
Joe
Joe's picture

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.

--

Check out the forum guidelines!

Sun, 06/07/2009 - 07:02
Blueforce

Hi Joe,

I will talk to the guys at our server hall when they open(in about 2 hours) to see if I can get some extra days before the server is shut down.

I have also updated most of our ecommerce code during the night so it should now work with MySQL 5 and it's new way of handling JOINS. I have changed/updated most of the code(100 files manually updated) and it should now run fine(hopefully), I'll do the final testrun before I make the call to the serverguys in yhe morning.

If the code runs fine then I probably have time to update the other sites and code so all customers can be switched ower to our new box by end of day.

I'll will now try to get 2-3 hours of sleep before our serverguys is back at work, and of course do the final test of our code. If the guys says NO I'll need the day for the rest of the site code update and transfers witch of course needs our new box up and running. Also if I at this time should go for MySQL 4 I most certanly will need your assistance... and I guess you are sleeping(or anyway should be ;-) ) when we have morning/day here in Sweden.

I hope everything will be up and running by the end of day so we finally can drop the old box.

Bottom line...
Joe, if I get a NO for a few extra days I'll have to stick with version 5. I'll drop a message here in a few hours and let you know what way we are going.

Thanks for all the help!

Regards,
Leif

Sun, 06/07/2009 - 07:02
Blueforce

Joe,

I have now done the testrun with the new code and it runs ok and make database queries correctly.
(some of our customers databases have 40000+ records)

I have also been talking to our serverguys and our old server will go offline by noon tomorrow(22 hours from now), instead of by midnight.

By this time I can have all pages updated for MySQL 5.

Can I somehow get MySQL 5 to use iso-8859-1(latin1) instead of utf-8, although it looks like it use latin1 but when importing databases it gets converted to utf-8.
Utf-8 gives us problem, for example when we edit/write some text in Swedish we use iso-8895-1 coding like &quot;&amp; a r i n g ;&quot; for the swedish a with the ring above.
Some times i get files in Swedish that don't use &quot;&amp; a r i n g ;&quot; or &quot;&amp; # 2 2 9 ;&quot;, the character show like it should but with some other utf format like &quot;&Atilde;

Sun, 06/07/2009 - 07:02
ChrisBlackwell

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

Sun, 06/07/2009 - 07:02
Joe
Joe's picture

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.

--

Check out the forum guidelines!

Sun, 06/07/2009 - 07:02
Blueforce

Joe,

Have you hade time to check if it's possible to do this?

Or could you just give me a hint on when you have time to check this up, would be nice to know because i'm starting to get these horrible thoughts of getting a couple hours of sleep... thats not a good thought ;-)

(time here in Sweden is now 5:45am)

Regards,
Leif

Sun, 06/07/2009 - 07:02
Joe
Joe's picture

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.

--

Check out the forum guidelines!

Sun, 06/07/2009 - 07:02
Blueforce

Thanks,

Yes i'm on i386.

All files are downloaded.

How do I uninstall MySQL 5 without ending up like last time I was told to do a yum remove ending up with a almost totally deleted box *smile*

Can you just give me some short advice, for how to safely remove and then how I install the MySQL4, should they go in a specific order?

Sun, 06/07/2009 - 07:02
Joe
Joe's picture

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...

--

Check out the forum guidelines!

Sun, 06/07/2009 - 07:02
Blueforce

Ok... somehow i'm not sleepy any more :-)

Feels like i'm walking on very thin ice here!

I have a (maybee silly) question.
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?

Sun, 06/07/2009 - 07:02
Joe
Joe's picture

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.

--

Check out the forum guidelines!

Sun, 06/07/2009 - 07:02
Blueforce

Hi,

I told you it was a stupid question... :-)

Ok, the reason for all my questions is that my problem here is NOT caused by any of your software, and I was just thinking if there was some &quot;shortcut&quot; to get this to work. And mainly to NOT give you guys more problems to solve.

I really appreciate all the help we get from you!

Do you think this is the way to go... or should I once more extend our contract for our old box, so I can rewrite those sites to be compatible with MySQL 5. It's a rather big job, but on the other hand I don't know how much work YOU have to put in to get MySQL 4 working on/with FC6.

I'm also is in a rather tight time frame here, the old box is supposed to get &quot;killed&quot; in two days (by the 24th).

I Don't know how much work or time it would take to get MySQL 4, PHP and all other stuff it involves up and running. I think I need about one day to move the domains. I can't do backup and restore on all the domains. Some of them needs to be moved/updated manually. So this gives me just one day for MySQL 4. And of course, to get it to work I NEED your support, and I can't take for granted that you guys are there to &quot;fix&quot; this for me in this short time.

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.

What do you think, should I go for MySQL 4 and the dedline by the 24 of April. Or should I go the &quot;safe&quot; way ???

Regards,
Leif

Sun, 06/07/2009 - 07:02
Joe
Joe's picture

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.

--

Check out the forum guidelines!

Sun, 06/07/2009 - 07:02
Blueforce

Hi Joe,

I will talk to the guys at our server hall when they open(in about 2 hours) to see if I can get some extra days before the server is shut down.

I have also updated most of our ecommerce code during the night so it should now work with MySQL 5 and it's new way of handling JOINS. I have changed/updated most of the code(100 files manually updated) and it should now run fine(hopefully), I'll do the final testrun before I make the call to the serverguys in yhe morning.

If the code runs fine then I probably have time to update the other sites and code so all customers can be switched ower to our new box by end of day.

I'll will now try to get 2-3 hours of sleep before our serverguys is back at work, and of course do the final test of our code. If the guys says NO I'll need the day for the rest of the site code update and transfers witch of course needs our new box up and running. Also if I at this time should go for MySQL 4 I most certanly will need your assistance... and I guess you are sleeping(or anyway should be ;-) ) when we have morning/day here in Sweden.

I hope everything will be up and running by the end of day so we finally can drop the old box.

Bottom line...
Joe, if I get a NO for a few extra days I'll have to stick with version 5. I'll drop a message here in a few hours and let you know what way we are going.

Thanks for all the help!

Regards,
Leif

Sun, 06/07/2009 - 07:02
Blueforce

Joe,

I have now done the testrun with the new code and it runs ok and make database queries correctly.
(some of our customers databases have 40000+ records)

I have also been talking to our serverguys and our old server will go offline by noon tomorrow(22 hours from now), instead of by midnight.

By this time I can have all pages updated for MySQL 5.

Can I somehow get MySQL 5 to use iso-8859-1(latin1) instead of utf-8, although it looks like it use latin1 but when importing databases it gets converted to utf-8.
Utf-8 gives us problem, for example when we edit/write some text in Swedish we use iso-8895-1 coding like &quot;&amp; a r i n g ;&quot; for the swedish a with the ring above.
Some times i get files in Swedish that don't use &quot;&amp; a r i n g ;&quot; or &quot;&amp; # 2 2 9 ;&quot;, the character show like it should but with some other utf format like &quot;&Atilde;

Sun, 06/07/2009 - 07:02
ChrisBlackwell

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

Sun, 06/07/2009 - 07:02
Blueforce

Hi Chris,

First of all, we have solved all join issuses in our code to match MySQL 5. If the db querys hade been formated in another way(probably the right way) in the first place they should have worked in ver 5. But it doesen't look like i'm the only one that formated the querys wrong. ;-)

Thanks for the info about character sets in MySQL.
It's a real pain with our Swedish character in utf. I thought that using utf would make it more &quot;worldwide&quot; compatible. But it definitely looks like software codes/decodes the characters in different ways.

Regards,
Leif

Pages

Topic locked