This is based on Nathan's own instructions for the Gamma release (4.0.10-0) of MySQL, and he should get all the credit for the howto. I just whipped this up in case some of you didn't know that 4.0.12-0 is OUT and considered production status

How to Upgrade to MySQL 4.0.12-0 on Ensim


This info is provided "as-is" and that I am not to be held responsible for any damages done. MySQL 4 has some issues playing nice with Ensim and it would NOT be a good idea to upgrade right now unless you are prepared to deal with them on a manual level.

You should be aware that after upgrading to MySQL 4, ensim will not be able to add databases when you add a new account. You must create them manually. Again - ensim 3.1.8 and below do NOT detect the presence of MySQL 4 and will NOT create accounts automatically. This isn't a big deal if you are like me and like to do things manually anyway. Hopefully the next version of ensim will offer compatability with MySQL 4.

IT'S YOUR RESPONSIBILITY IF YOU SCREW YOUR BOX UP

log into your server via SSH (or Telnet if you are a nut case) and becoming root, making a tmp dir...
su -
mkdir mysql4
cd mysql4

Next you can grab the files:
wget ftp://mysql.secsup.org/pub/software/mysql...0.12-0.i386.rpm
wget ftp://mysql.secsup.org/pub/software/mysql...0.12-0.i386.rpm
wget ftp://mysql.secsup.org/pub/software/mysql...-0.i386.rpm

! BACKUP BACKUP BACKUP !
Let's backup your databases to be 100% safe!

mysqldump --all-databases -uroot -p > backup.sql

This will prompt you for your root MySQL password, enter it and it will create a backup of your MySQL databases.

Now before the upgrade you really need to stop apache, your current version of MySQL and, all their processes, so do this:

/etc/rc.d/init.d/httpd stop
/etc/rc.d/init.d/mysqld stop
/etc/rc.d/init.d/mysql stop

Then:

killall -9 httpd
killall -9 mysqld
killall -9 mysql

If you are running SIM you are going to need to disable it as it will cause problems. You can do this via:
/usr/local/sim/sim -j


Next we need to figure out which MySQL RPMs to remove. You want to remove mysql, mysql-server and mysql-devel. You can get the exact names via:
rpm -qa | grep mysql

Depending upon how up to date your system is, you should recieve an output similar to (your version numbers might be different):
root [~] #rpm -qa | grep mysql
webppliance-mysql-prolog-3.1.0-25
webppliance-mysql-epilog-3.1.0-25
mysql-devel-3.23.54a-3.72
mysql-server-3.23.54a-3.72
virtualhosting-fst-mysql-3.1.0-25
webppliance-mysql-frontend-3.1.0-25
webppliance-mysql-3.1.3-4
mysql-3.23.54a-3.72
root [~] #

See the ones in bold? MySQL, the server, and the devel? You need to uninstall these packages first (remember, the version numbers on your system may vary):
rpm -e --nodeps mysql-devel-3.23.54a-3.72
rpm -e --nodeps mysql-server-3.23.54a-3.72
rpm -e --nodeps mysql-3.23.54a-3.72

Then you should be able to install the new RPMs for MySQL 4:
rpm -ivh MySQL-server-4.0.12-0.i386.rpm
rpm -ivh MySQL-client-4.0.12-0.i386.rpm
rpm -ivh MySQL-devel-4.0.12-0.i386.rpm

MySQL 4 should then be able to start using:
/etc/rc.d/init.d/mysql start

It will come up as:

root [~] #/etc/rc.d/init.d/mysql start
root [~] #Starting mysqld daemon with databases from /var/lib/mysql

You need to press [ENTER] here to start using those databases. If all goes well you will have MySQL 4 running and you will be able to check the version by:

mysql -V

It shoudl return this:


root [~] #mysql -V
mysql Ver 12.18 Distrib 4.0.12, for pc-linux (i686)
root [~] #

Other Issues

If you have issues starting up MySQL after you have installed it
first try shutting down MySQL again with:

/etc/rc.d/init.d/mysql stop

And then killing all MySQL processes:

killall -9 mysql

Then try starting MySQL with:

/etc/rc.d/init.d/mysql start

Also check your log file which should be located in /var/lib/mysql/ and you can do so by:

cat cat /var/lib/mysql/boxhostname.com.err

If it gives an error about a mysql library. Just symlink it.

Other issue is with how MySQL 4 hangs at startup, SIM has some problems with this and it might cause your server to crash. To fix this you can just run "autoconf" in the SIM directory and disable MySQL monitoring.

If PHP is having problems connecting to MySQL you will need to upgrade your phpmysql package and recompile/reload PHP for MySQL to work. This hasn't happened on any of my boxes (Mine neither - Nacho) but it is a possibility for other boxes.

I think that pretty much about covers the entire upgrade...pretty simple IMO....and now you have the supperdooper power of subselects...w00t! If you have any questions or anything about why something was done or if something should be added or if something went wrong durning the install, etc just post and hopefully someone can try and help you out.

Nathan's original howto can be found here and as I said he deserves all the credit for this one as well. I just updated version numbers and filenames and added a thing or two. I hope this is ok...

You should also most definately run mysql_fix_privelige_tables that comes with MySQL after upgrading to 4.0, as the table structure has changed slightly.

mysql_fix_privelige_tables

How many people have actually upgraded to this version yet?

How did it go etc.......

Did it on two servers since version 4.0.6 a few months ago.

I've done it to 2 different servers with great results.... the ONLY bad thing is for some reason ensim refuses to add a database when you create a new account. It always returns 'error - database already exists'. This isn't a huge deal to me as it's pretty easy to create a database manually until a fix is found.

Anyone out there who has discovered a fix for the above issue PLEASE let me know!

Overall since installing MySQL4 and enabling the query cache, I've seen some pretty wicked performance increases especially on my vBulletin based forum.

this is certainly 1 bug that i would like resolved.........

But like you said isnt major as can just create it manually....

The only thing I can figure is that ensim is using a depreciated method to create the database. If you notice it creates the user just fine, it just doesn't create a database. In fact I find that if I go in and create the database that ensim should have, named the same way ensim would (domain_com) the user is tied to that database name automatically! Weird, but then again everything with ensim is weird.

I wish I could figure out where the code lies for ensim to create a db, and if it's editable. I know it's just a matter of a depreciated command being used... oh well. *shrug*

If your PHP no longer connects to mysql, and/or you get warnings about libmysqlclient.so.10, here's a fix:

Go to http://www.mysql.com/downloads/mysql-3.23.html and download the RPM for "Dynamic Client Libraries" (it should be named MySQL-shared-3.23.56-1.i386.rpm).

Run this command:
rpm -ivh --force --oldpackage MySQL-shared-3.23.56-1.i386.rpm

Then re-install your MySQL 4.0 shared rpm (use -i not -U):
rpm -ivh --force MySQL-shared-4.0.12-0.i386.rpm

Problem solved. You now hae both the old and new mysql client libraries available.

I'm having some trouble here. I've done the upgrade and now it seems root no longer has access to MySQL and I can't access PHPmyadmin I get a access forbidden error. I can't even start mysql/phpmyadmin from ensim.

Any suggestions? I've searched everywhere and am at my wits end. I don't want to have to restore the server.

have you run mysql_fix_privelige_tables?

Am I supposed to run that from the bash shell or a mysql shell?

I tried from the bash shell with no success.

bash, or whatever your user shell is.

run it with the root password supplied as an argument like:

mysql_fix_privelige_tables myrootpassword

Where 'myrootpassword' is the mysql root password.

This is what I get when i do that, my password edited for obvious reasons.


[root@svr1 root]# mysql_fix_privelige_tables xxxxxxx
bash: mysql_fix_privelige_tables: command not found
[root@svr1 root]#

I can't spell, lol, try:

mysql_fix_privilege_tables

ok, that seemed to work, but now when I start mysql it says

Starting mysqld daemon with databases from /var/lib/mysql

and just hangs there.

Press enter... lol

lol, i did i'm still having the same problem, i can start mysql from command line but i get access denied when using phpmyadmin in ensim also it says mysql is down in ensim.

I'm beginning to think i should just do a system restore & cut my losses. I'd hate to have to do that though, that's a pansy way out, but If i can't figure it out i'm stuck.

ok, i think this might shed some light. I just decided what the hell i'm going delete all the sites i have on it right now and start over(they're all test sites) and I got this error in ensim:

mysql - Disable service
( ERROR ):Failed to revoke permissions on database secure-web-hosts_com: Traceback (most recent call last):
File "/home/build/qa/webppliance/3.1.3/4/lwp/gauri4/WebGui/base/services/mysql/revoke_db.py", line 29, in ?
File "/usr/lib/python2.1/site-packages/MySQLdb.py", line 19, in ?
import _mysql
ImportError: libmysqlclient.so.10: cannot open shared object file: No such file or directory

See the post earlier in this thread by Edgewize

grrr, I did that without a hitch, but it didn't resolve my problem. Anyone have a gun?

Hi Da`Nacho.

Tried to use your procedure and didn't work here, had to go back to the old 3.23 version.

No errors at all during the installation process but:
1.- Ensim was not seeing the MySQL service as running (from SSH I vwrified it and was running)
2.- None of the php applications using PHP/Mysql could connect to the database... error loading the mySQL module... mysql_connect function not found... that kind of errors...

Any idea what is going on? Would love to start using the 4.0.12 version.

Will Ensim 3.5 have MySQL 4 already in it?

have upgraded with Edgewize fixed

thanks!

We can only hope...

I'm really impressed with MySQL 4 and it's performance on my pokey little Celeron 1.3 box. Turning on query caching seems to have made a huge difference in response times for things like my forum.

I'm still keeping a cautious eye out for problems though...

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''',
activity datetime NOT NULL default '0000-00-00 00:00


can you help me? I get that error.

Da`Nacho, i think you are gonna make many people mad here heh, ensim system won't work with this version of MySQL, it differ a lot from version that ensim is currently using. New MySQL database rocks, no wonder but my opinion is that we should wait for ensim to upgrade it...this is just too many errors.

It would help more if you could tell us where you are seeing this error exactly...

That is, indeed, your opinion.

MySQL 4 doesn't differ 'a lot' from 3. There have been a few depreciated commands and the new Query Caching capability. Unfortunately one of the depreciated commands appears to be one that gives ensim some problems adding databases.

These problems are minor annoying ones, but problems nonetheless. Thats why there is a DISCLAIMER at the beggining of the original post. It's not anyone's fault but yours if you skip over it.

i got that error when i was using.

mysql> SHOW VARIABLES LIKE 'mysql_query_cache';

and another weird thing is that when i go to my webppliace i dont see mysql turned on but it's on because i see my forums working perfectly.

Go back and read the instructions more in that thread ... you're not supposed to type the 'mysql>' part, thats why it's not in bold. That's just the prompt you get when you are in the command line MySQL client.

You can do these status commands in phpMyAdmin if it would be easier, just put the queries in the SQL query box:

SHOW VARIABLES LIKE 'mysql_query_cache';

When you click 'Go' you should get a nice result.

I'm very tempted to install MySQL 4, but so far I've tried to only use RPMs that gpan has created for things like PHP and MySQL. (at least, since my last server restore) I e-mailed him a second ago to find out.

I'm also not wanting to install something ATM that may mess up stuff on my box. I'm going to also have to do some research if some of the more popular programs that I use (vBulletin, Invision Board, ModernBill) will work with it.

Will see if we are lucky and we get gpan's hands on a compatible set of PHP and MySQL4 rpms!!!

Hi ,

I've done the update but when i look in the .err file i get these errors...

ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[admin@plesk admin]$

Similar errors here... that's why I went back to the 3 version of MySQL.

how do i go back to 3.23 ?
i don't have the rpm files ?! do you have them ? or a link ?

gpan said "Not when it breaks ensim at the moment" -- However, I installed it anyway, and after fixing the PHP error for libsomething.so it worked fine.

Understand gpan's answer... How did you fix the PHP error?... I'm getting the same problems.

please give us n00bs the answer we need... we've got the problem so we know with somebodies help we can fix it !

I've found something on the PLESK site.. but i don't know what it means...

I am having troubles logging into mysql from the command line. I keep getting the following error:
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
This can be fixed by creating a link from the /tmp/mysql.sock file.

Sorry didn't see your post before.

I simply stopped MySQL 4 that I installed, uninstalled the rpm packages of MySQL4 (hint: see the first post of this thread on how to uninstall the rpms), and then installed back the latest 3.xx MySQL... there is a thread on the How To forum about how to do the whole thing.

I don't remembebr if I had to restart apache after the whole thing.

I did a source installation a month ago and I have been really really really happy with the performance gains using the query cache. My overall server load dropped 70% and the best part is that my php pages are loading with amazing speed, faster than static html pages.

How did you reinstall PLESK ?!!

Yeah, I run a forum with vBulletin and even though I am a die hard vBulletin fan there are certain issues with speed that sometimes get on my nerves. Typically when loading a vBulletin page even when there are no other users online, there was always a slight delay which I assume was due to vBulletin's rather large number of tiny DB queries.

After installing MySQL 4 and enabling a 16MB query cache page generation times in vBulletin are quite literally instantaneous. I am horribly impressed to say the least.

vBulletin has installed 4.0.12 on their vbulletin.com server (that was the final decision in my choice to upgrade) and have seen considerable improvements as well.

when i enter a query in phpmyadmin it says

Warning: file_exists() [function.file-exists]: Unable to access in /var/www/html/phpMyAdmin-2.2.0/db_readdump.php on line 53

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/phpMyAdmin-2.2.0/db_readdump.php:53) in /var/www/html/phpMyAdmin-2.2.0/ob_lib.inc.php on line 51

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/phpMyAdmin-2.2.0/db_readdump.php:53) in /var/www/html/phpMyAdmin-2.2.0/header.inc.php on line 23

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/phpMyAdmin-2.2.0/db_readdump.php:53) in /var/www/html/phpMyAdmin-2.2.0/header.inc.php on line 24

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/phpMyAdmin-2.2.0/db_readdump.php:53) in /var/www/html/phpMyAdmin-2.2.0/header.inc.php on line 25

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/phpMyAdmin-2.2.0/db_readdump.php:53) in /var/www/html/phpMyAdmin-2.2.0/header.inc.php on line 26

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/phpMyAdmin-2.2.0/db_readdump.php:53) in /var/www/html/phpMyAdmin-2.2.0/header.inc.php on line 27

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/phpMyAdmin-2.2.0/db_readdump.php:53) in /var/www/html/phpMyAdmin-2.2.0/header.inc.php on line 29

this started happening when i upgraded my mysql to 4

That might be because you are running a really old version of phpMyAdmin... 2.2.0 was from like early 2002.

gpan has a really nice rpmized phpMyAdmin 2.4.0 upgrade for ensim:

http://forum.rackshack.net/showthread.php?...&threadid=18510

Da'Nacho thanks for all your help!

I'd love to hear where you did that. I run a couple of php-based bulletin boards (I use UBB.threads) and while I noticed a performance increase after installing 4.0.12, I'd love to see more.

http://forum.rackshack.net/showthread.php?...&threadid=19872