databases/mysql51-server and beginner's InnoDB questions

[Jeremy Chadwick]

On Tue, Sep 16, 2008 at 08:50:54PM +0200, Morgan Wesström wrote:
> I have a few questions regarding enabling InnoDB but I'm not an expert  
> on MySQL so I'm not even sure I know how to ask them correctly. But the  
> only way to learn is to ask and hope nobody is offended by stupid  
> questions. :-)

I'm wondering why you're asking MySQL-specific questions on
freebsd-ports.  Questions I didn't answer should be punted to the MySQL
folks, they're quite helpful.

> I realized today actually that there are different storage engines  
> available for MySQL and that InnoDB seems to be preferred so I naturally  

First and foremost: I don't know where you got the idea that InnoDB is
preferred.  Whoever told you that is flat out wrong.  You need to spend
some more time reading up on the pros/cons to all of the MySQL storage
engine types.  InnoDB happens to be one of the most horrendous ones to
deal with from an administrative point of view.  It's always great when
the InnoDB part is out of sync with /var/db/mysql/database/whatever.*,
which can often happen during replication errors or bugs.

My advice to people is to avoid InnoDB unless you *specifically* have
engineered an application that will make use of it.  MyISAM is a lot
easier to deal with.

> wanted to use it. I can see with "show create table <sometable>" that  
> Mediawiki's tables for example are already created with ENGINE=InnoDB.  
> But in my MySQL config file, which is simply a copy of my-large.cnf,  
> there is a whole section for InnoDB that is commented out. It begins 
> with:
> "# Uncomment the following if you are using InnoDB tables"

Ignore that.  I can tell you're flailing around with config files.  :-)
You can look at the compile-time defaults of InnoDB by using "SHOW
VARIABLES", and performance using "SHOW STATUS".  Please read the
MySQL docs.

> _First question:_
> Is InnoDB enabled by default regardless of the settings in my.cnf and  
> how can I verify it?

It's enabled by default.

Look at ports/databases/mysql51-server/Makefile; see WITHOUT_INNODB?  If
that's set (e.g. make WITHOUT_INNODB=true, or WITHOUT_INNODB=true in
your /etc/make.conf), then the InnoDB storage engine will not be
included.

You can also disable InnoDB at runtime using mysqld --skip-innodb.

If InnoDB stats are seen in "SHOW STATUS", then InnoDB is enabled.  You
can also try creating a table using the InnoDB storage engine type.  The
CREATE TABLE will fail if the engine is disabled or unavailable.

> _Third question:_
> Is this an issue with the FreeBSD port specifically? Should I report  
> this to someone and how would I do that the correct way?

None of what you've described (I snipped the portions out) are specific
to the FreeBSD port.  They are purely configuration issues, and are
with MySQL.  You should discuss your issues with the MySQL people.

Cheers!

-- 
| Jeremy Chadwick                                jdc at parodius.com |
| Parodius Networking                       http://www.parodius.com/ |
| UNIX Systems Administrator                  Mountain View, CA, USA |
| Making life hard for others since 1977.              PGP: 4BD6C0CB |