MySQL 5.5.25 released - Do Not Update!

ditto

Verified User
Joined
Apr 27, 2009
Messages
2,577
MySQL repair and optimize

Hello people,

I have a problem with MySQL 5.5.25 after the upgrade.
I cant repair and optimize in DirectAdmin, see the error below.

Code:
The storage engine for the table doesn't support repair

Code:
Table does not support optimize, doing recreate + analyze instead

Before the upgrade there was no problem.

Information:

CentOS release 5.8 (Final)
centos-release-5-8.el5.centos
Linux 2.6.18-308.el5.028stab099.3
Php 5.3.14 suPHP CGI
MySQL 5.5.25



Anybody know this how to fix it?



Thank you
 
Last edited:
I don't think this is anything wrong. I think it is because Innodb is the default engine in MySQL 5.5.x, and Innodb does not support optimize command. If you try to repair and optimize another database wich only have MyISAM, then there should not be any warnings, I beleive.
 
If someone who knows the answer to the following will reply it will help make this thread more complete:

User ditto brings up another point with his reply. What happens if we upgrade to a version of MySQL (for example 5.5.25) which by default uses innodb, what happens to databases built with another engine, for example MyISAM? Does it continue to work, but not allow optimize command, though otherwise keeping the MyISAM format, or does it convert the database? Or something else entirely?

And is it possible to update but still keep MyISAM, so optimize will continue to work?

Thanks to anyone who can complete this for us.

Jeff
 
I upgraded a shared hosting production server with several hundreds existing users and databases, I upgraded from MySQL 5.1.x to 5.5.x Almost all existing databases was using MyISAM. After the upgrade, they continued to work as before, and they was not converted. Also of course optimize command still work on all tables that is still using MyISAM. So existing databases already using MyISAM was not affected by the fact that Innodb was the new default engine in 5.5.x

What I did notice, was that if a user now create a brand new database and install WordPress (for example), then the engine for tables in that database will automatically become Innodb

If you want to disable Innodb and set MyISAM as default, you could try to add the following in my.cnf under the [mysqld] group (I have not tested):

Code:
innodb=OFF
default-storage-engine=MyISAM
From: http://www.webhostingtalk.com/showpost.php?p=8145243&postcount=13

However, personally I think it is best to get used to Innodb, because it is the new default engine in MySQL.

However after upgrading, and then several months later I read this forum post http://www.directadmin.com/forum/showthread.php?t=43886 (regarding ibdata1 and innodb) , and need to decide what to do about that, and then figure out how to do it without creating troubles.
 
Be sure to let us know what you decide. :)

It may very well be that MyISAM is a better engine for shared hosting. Are there specific advantages to innodb which wouldl negate that?

Jeff
 
From my understanding, "Repair/Optimize" does always NOT work for InnoDB. I simply ignore this.

I have production server running with MySQL 5.5 with "default-storage-engine=MyISAM" (i.e. default to MyISAM, but also support InnoDB if customer select)
It runs for a year, and works fine up to now

As I know, some PHP application (e.g. Magento) requires InnoDB.
InnoDB supports transaction/commit (but I think it is not so useful in shared hosting)

ditto:
Yes, I also think better to get used to InnoDB, as it is the default engine since MySQL 5.5 :(

May I know any customer website encounter problems after MySQL upgrading from 5.1 to 5.5 ? (as I know some syntax changes in create table, and still have MySQL running older version, thinking to upgrade)

Thank you for your sharing.
 
Last edited:
@ccto, I did not encounter any problems with any of my customers websites after upgrade to MySQL 5.5.x. However, I warned all my customer about Joomla before I upgraded, so they first could upgrade Joomla.

The thing is that Joomla 1.5.22 and older is not compatible with MySQL 5.5.x, Joomla 1.5.23 and newer is compatible. Here is the bug tracker about the problem http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemEdit&tracker_item_id=25162

If you have customer running Joomla 1.5.22 or older, and if they need help to upgrade to newest Joomla 1.5.x, then you can give them this link: http://extensions.joomla.org/extensions/core-enhancements/installers/9332 - that is a module that will upgrade Joomla 1.5.x to newest 1.5.x, and it is super easy to use for everyone.
 
Also, when upgrading from MySQL 5.1.x to MySQL 5.5.x, you will most likely need to add a symlink from so.16 to so.18 (I needed to do that), this is because some perl modules like dbd and dbi will still point to so.16 and therfor not work. Here is the code (CentOS 6 64bit server) to add the symlink after the upgrade:

Code:
cd /usr/lib64
ln -s libmysqlclient.so.18 libmysqlclient.so.16
 
Last edited:
Be sure to let us know what you decide. :)

It may very well be that MyISAM is a better engine for shared hosting. Are there specific advantages to innodb which wouldl negate that?

Jeff

I have been running several survey sites which make use of MySQL databases. When an opinion is added to the
database the procedure is very simple:
- If there is an answer to the same question from the same user, delete it
(DELETE FROM answers WHERE survey_id=xxx AND user_id=yyy AND question_id=zzz)
- Add a new answer (the normal MySQL way)

There is no other activity between these two SQL queries.

In MyISAM there were duplicates after every 30000 or so transactions - so the deletion did
not work all times. After switching to InnoDB the duplicates have vanished. There were other
weirdities as well. I cannot say that MyISAM is a very reliable engine.

I did not try to solve the problem by creating multi-field unique keys because that would
very possibly have led to some other problems like the old answers being retained had the
deletion been unsuccessful.

Anyhow, after switching to InnoDB there have been no problems.

Markku
 
need advice please

been readin posts but I cannot (yet) find out how to use CB to update mysql.
can someone point it out to me?
thanks.
 
Code:
cd /usr/local/directadmin/custombuild
./build update
./build mysql

After custombuild finish the above, then you should recompile php (should always be done after mysql upgrade):

Code:
./build php n

You could also do ./build versions before upgrade, so that you see a list over what updates you can apply.
 
thank you

for some reason I was thinking I needed to include version numbers but I could not verify.
will try this right now.
BTW these random questions are a mess. one three five seven is not a question LOL
 
for some reason I was thinking I needed to include version numbers but I could not verify.

If you do:
Code:
cd /usr/local/directadmin/custombuild
./build versions

Then you will see what mysql version you have installed, and what mysql version it will be upgraded to.
 
awesome

will mess with that asap, I really appreciate the advice.
I'm a whm/cpanel/easyapache user so am in a learning curve.
 
If you have not seen it yet, there is a custombuild faq here: http://www.directadmin.com/forum/showthread.php?t=29824

I understand very well that it is a learning curve (it is for me also), and the documentation could have been a lot better.

If you do this:
Code:
cd /usr/local/directadmin/custombuild
./build

Then it will show you all commands available in custombuild.
 
Edit this file:
/usr/local/directadmin/custombuild/options.conf

Find this line:
mysql=5.0

Change it to this and save change:
mysql=5.5

Then you do this:
Code:
cd /usr/local/directadmin/custombuild
./build update
./build versions

Then please confirm that the new mysql version is the one you want. Then do this:

Code:
./build mysql

and then

Code:
./build php n

Edit: Also remember that your operating system should be a very minimal installation, if you previous installed mysql from source, it can cause trouble. You should only install mysql, php, apache etc using custombuild, those should never be installed by source, it can cause a lot of trouble then.
 
Last edited:
that seemed tohave done it

I think thats also the file edit I was thinking of.
superb help, thank you.
 
Back
Top