Can MYISAM tables live together with InnoDB on Galera?

[SyRenity]

Hi.

While I know Galera only works on InnoDB level, I'd like to know, can
MyISAM tables work fine on Galera MySQL server, acting as regular
tables?

Thanks.

[Seppo Jaakola]

MySQL/Galera treats MyISAM tables in quite illogical way:

1. All DDL (create, drop, alter table...) on MyISAM will be
replicated.
2. DML (update, delete, insert) on MyISAM tables only, will not be
replicated
3. Transactions containing both InnoDB and MyISam access will be
replicated

So, you cannot have node private MyISAM tables, MyISAM tables will
appear in all nodes.
If you access the MyISAM tables outside of InnoDB transactions, then
all data changes in MyISAM tables will remain locally in each node.
If you access MyISAM tables inside InnoDB transactions, then MyISAM
changes are replicated along InnoDB changes. However, if there happen
cluster wide conflicts, MyISAM changes cannot be rolled back and your
MyISAM tables will remain inconsistent.

As a conclusion, you probably don't want to use MyISAM tables in MySQL/
Galera cluster.

We can support MyISAM replication in master-slave model with quite low
effort. There is a design for this, but there is very low demand for
this feature and we have not yet scheduled MyISAM support.

Seppo

[meman]

Would be nice with basic myisam support, in order to allow for apps
like mediawiki, bugzilla etc. to run on a galera cluster without any
modifications regarding their fulltext search features

[Alex Yurchenko]

On Sun, 15 Aug 2010 02:27:22 -0700 (PDT), meman <jon.ska...@gmail.com>
wrote:

> Would be nice with basic myisam support, in order to allow for apps
> like mediawiki, bugzilla etc. to run on a galera cluster without any
> modifications regarding their fulltext search features

The certification approach that galera implements explicitly rules out
non-transactional storage engines in multimaster operation. But we are
considering to support MyISAM in master-slave. Will it make sense?

[meman]

That is what a 'regular' mysql server supports, and would help solve
any issues with fulltext search integrated in solutions like bugzilla
and mediawiki. Like mediawiki supports load balanced reads using
master/slave setup, so it would make sense in order to move entire
database infrastructure into one mysql instance, instead of having a
separate solution for applications needing myisam.

On 16 Aug, 21:14, Alex Yurchenko <alexey.yurche...@codership.com>
wrote:
> On Sun, 15 Aug 2010 02:27:22 -0700 (PDT), meman <jon.skarpet...@gmail.com>

[SyRenity]

Hi.

> Like mediawiki supports load balanced reads using
> master/slave setup, so it would make sense in order to move entire
> database infrastructure into one mysql instance, instead of having a
> separate solution for applications needing myisam.

+1 for this - I also planned to use it this way.

I actually hit an issue with MYISAM on Galera, running on 2-node DB
system, so there probably some other pitfalls.
Will post a report separately.

[Seppo Jaakola]

Hi,

The MyISAM pitfalls should have been discussed in the first reply to
this thread. All in all, it works in quite illogical way.
I kind of hoped that everyone would move gradually to InnoDB (or some
other transactional engine), and we would not need to care about
MyISAM. But but, hopefully master-slave mode for MyISAM will turn out
good enough compromise.

Seppo

[SyRenity]

Hi.

> The MyISAM pitfalls should have been discussed in the first reply to
> this thread. All in all, it works in quite illogical way.
> I kind of hoped that everyone would move gradually to InnoDB (or some
> other transactional engine), and we would not need to care about
> MyISAM. But but, hopefully master-slave mode for MyISAM will turn out
> good enough compromise.

While our own apps for example are InnoDB from the start, 3rd party
apps such as Joomla and Drupal are still MYISAM based, bringing whole
new range of issues if one tries to change the tables to InnoDB.

So with Galera the only thing left is running 2 servers on same
machine, which is quite inconvenient. Having master-slave stable
operation will be great and much better then standard (non-HA)
installation anyhow.

Regards.