MySQL and Federated Storage Engine

hrolofs · September 17, 2008, 3:23pm

Hello,

in the past I use the database binary from the mysql web-site with openSUSE 11.0.

Now i try to migrate to the MySQL Database mysql-5.0.67-11.1 from the server:database/openSUSE_11.0 respoitory.

When i start my Application i got a messages like this:

DBD::mysql::db do failed: The ‘FEDERATED’ feature is disabled; you need MySQL built with ‘FEDERATED’ to have it working at Modules/OVOMsgV4Base.pm line 477.

How i can use the ‘FEDERATED’ feature with the database from repository? Must I compile the database from the source or is it possible to activate this feature with a parameter or config file?

Regards, Henry

ken_yap · September 17, 2008, 4:44pm

Interesting question and worthy of a search. Here’s the answer:

MySQL :: MySQL 5.0 Reference Manual :: 13.7 The FEDERATED Storage Engine

In particular:

Beginning with MySQL 5.0.64, the FEDERATED storage engine is not enabled by default in the running server; to enable FEDERATED, you must start the MySQL server binary using the --federated option.

So that means, edit /etc/my.cnf and in the [mysqld] section, add the line:


federated

since that’s equivalent to specifying --federated on the command line.

Learnt something useful today.

hrolofs · September 17, 2008, 5:10pm

Hi,

thanks for your answer but the option federated in the configuration file doesn’t works.

Important Change: Incompatible Change: The FEDERATED storage engine is now disabled by default in the .cnf files shipped with MySQL distributions (my-huge.cnf, my-medium.cnf, and so forth). This affects server behavior only if you install one of these files.

The federated option is “only” disabled in the default config files with skip-federated.

I have test it now with the mysql-5.0.67-linux-i686-glibc23.tar.gz binary from the mysql web-site and it works.

It looks more like a “problem” with the binary from the openSUSE repository. Mabey the binarys are not compiled with the federated feature.

Regards, Henry

ken_yap · September 18, 2008, 1:20am

The federated option is “only” disabled in the default config files with skip-federated.

That sounds illogical if it isn’t compiled with FEDERATED. What happens if you add --federated to the startup options in /etc/init.d/mysql? But since you have elected to go with MySQL’s binary, maybe you can’t test this anymore. Maybe what was needed was federated = yes in /etc/my.cnf?

If it’s really true that OpenSUSE’s binary is compiled without FEDERATED support, than this should be reported as a bug to Novell since it will break perl-DBD-mysql.

hrolofs · September 18, 2008, 3:19pm

Hi,

i have done some tests with two mysql binaries.

a: mysql-5.0.67-11.1.i586.rpm
from http://download.opensuse.org/repositories/server:/database/openSUSE_11.0/i586/mysql-5.0.67-11.1.i586.rpm

b: mysql-5.0.67-linux-i686-glibc23.tar.gz
from MySQL :: MySQL 5.0 Downloads

For the tests i started both binaries without any federated option, with the option --skip-federated and with the option --federated

The i looked with “show engines” which engines are supported and enabled.

a0: mysql-5.0.67-11.1.i586.rpm, without any federated option

“Engine”,“Support”,“Comment”
“MyISAM”,“DEFAULT”,“Default engine as of MySQL 3.23 with great performance”
“MEMORY”,“YES”,“Hash based, stored in memory, useful for temporary tables”
“InnoDB”,“DISABLED”,“Supports transactions, row-level locking, and foreign keys”
“BerkeleyDB”,“NO”,“Supports transactions and page-level locking”
“BLACKHOLE”,“NO”,"/dev/null storage engine (anything you write to it disappears)"
“EXAMPLE”,“NO”,“Example storage engine”
“ARCHIVE”,“NO”,“Archive storage engine”
“CSV”,“NO”,“CSV storage engine”
“ndbcluster”,“NO”,“Clustered, fault-tolerant, memory-based tables”
“FEDERATED”,“NO”,“Federated MySQL storage engine”
“MRG_MYISAM”,“YES”,“Collection of identical MyISAM tables”
“ISAM”,“NO”,“Obsolete storage engine”

a1: mysql-5.0.67-11.1.i586.rpm, with --skip-federated option

“Engine”,“Support”,“Comment”
“MyISAM”,“DEFAULT”,“Default engine as of MySQL 3.23 with great performance”
“MEMORY”,“YES”,“Hash based, stored in memory, useful for temporary tables”
“InnoDB”,“DISABLED”,“Supports transactions, row-level locking, and foreign keys”
“BerkeleyDB”,“NO”,“Supports transactions and page-level locking”
“BLACKHOLE”,“NO”,"/dev/null storage engine (anything you write to it disappears)"
“EXAMPLE”,“NO”,“Example storage engine”
“ARCHIVE”,“NO”,“Archive storage engine”
“CSV”,“NO”,“CSV storage engine”
“ndbcluster”,“NO”,“Clustered, fault-tolerant, memory-based tables”
“FEDERATED”,“NO”,“Federated MySQL storage engine”
“MRG_MYISAM”,“YES”,“Collection of identical MyISAM tables”
“ISAM”,“NO”,“Obsolete storage engine”

a2: mysql-5.0.67-11.1.i586.rpm, with --federated option

“Engine”,“Support”,“Comment”
“MyISAM”,“DEFAULT”,“Default engine as of MySQL 3.23 with great performance”
“MEMORY”,“YES”,“Hash based, stored in memory, useful for temporary tables”
“InnoDB”,“DISABLED”,“Supports transactions, row-level locking, and foreign keys”
“BerkeleyDB”,“NO”,“Supports transactions and page-level locking”
“BLACKHOLE”,“NO”,"/dev/null storage engine (anything you write to it disappears)"
“EXAMPLE”,“NO”,“Example storage engine”
“ARCHIVE”,“NO”,“Archive storage engine”
“CSV”,“NO”,“CSV storage engine”
“ndbcluster”,“NO”,“Clustered, fault-tolerant, memory-based tables”
“FEDERATED”,“NO”,“Federated MySQL storage engine”
“MRG_MYISAM”,“YES”,“Collection of identical MyISAM tables”
“ISAM”,“NO”,“Obsolete storage engine”

b0: mysql-5.0.67-linux-i686-glibc23.tar.gz, without any federated option

“Engine”,“Support”,“Comment”
“MyISAM”,“DEFAULT”,“Default engine as of MySQL 3.23 with great performance”
“MEMORY”,“YES”,“Hash based, stored in memory, useful for temporary tables”
“InnoDB”,“DISABLED”,“Supports transactions, row-level locking, and foreign keys”
“BerkeleyDB”,“NO”,“Supports transactions and page-level locking”
“BLACKHOLE”,“YES”,"/dev/null storage engine (anything you write to it disappears)"
“EXAMPLE”,“NO”,“Example storage engine”
“ARCHIVE”,“YES”,“Archive storage engine”
“CSV”,“YES”,“CSV storage engine”
“ndbcluster”,“DISABLED”,“Clustered, fault-tolerant, memory-based tables”
“FEDERATED”,“YES”,“Federated MySQL storage engine”
“MRG_MYISAM”,“YES”,“Collection of identical MyISAM tables”
“ISAM”,“NO”,“Obsolete storage engine”

b1: mysql-5.0.67-linux-i686-glibc23.tar.gz, with --skip-federated option

“Engine”,“Support”,“Comment”
“MyISAM”,“DEFAULT”,“Default engine as of MySQL 3.23 with great performance”
“MEMORY”,“YES”,“Hash based, stored in memory, useful for temporary tables”
“InnoDB”,“DISABLED”,“Supports transactions, row-level locking, and foreign keys”
“BerkeleyDB”,“NO”,“Supports transactions and page-level locking”
“BLACKHOLE”,“YES”,"/dev/null storage engine (anything you write to it disappears)"
“EXAMPLE”,“NO”,“Example storage engine”
“ARCHIVE”,“YES”,“Archive storage engine”
“CSV”,“YES”,“CSV storage engine”
“ndbcluster”,“DISABLED”,“Clustered, fault-tolerant, memory-based tables”
“FEDERATED”,“DISABLED”,“Federated MySQL storage engine”
“MRG_MYISAM”,“YES”,“Collection of identical MyISAM tables”
“ISAM”,“NO”,“Obsolete storage engine”

b2: mysql-5.0.67-linux-i686-glibc23.tar.gz, with --federated option

“Engine”,“Support”,“Comment”
“MyISAM”,“DEFAULT”,“Default engine as of MySQL 3.23 with great performance”
“MEMORY”,“YES”,“Hash based, stored in memory, useful for temporary tables”
“InnoDB”,“DISABLED”,“Supports transactions, row-level locking, and foreign keys”
“BerkeleyDB”,“NO”,“Supports transactions and page-level locking”
“BLACKHOLE”,“YES”,"/dev/null storage engine (anything you write to it disappears)"
“EXAMPLE”,“NO”,“Example storage engine”
“ARCHIVE”,“YES”,“Archive storage engine”
“CSV”,“YES”,“CSV storage engine”
“ndbcluster”,“DISABLED”,“Clustered, fault-tolerant, memory-based tables”
“FEDERATED”,“YES”,“Federated MySQL storage engine”
“MRG_MYISAM”,“YES”,“Collection of identical MyISAM tables”
“ISAM”,“NO”,“Obsolete storage engine”

For me it looks like:

the FEDERATED engine is enabled without any federated option or the option --federated and with the option --skip-federated disabled (test b0, b1, b2)
the FEDERATED engine is not included in the binary from the openSUSE repository (test a0, a1, a2) and also some other engines are not include compared to the binary from the mysql-site.

Regards, Henry

ken_yap · September 18, 2008, 3:25pm

Ok, looks like this bug ought to be reported at Novell’s bugzilla before it makes it to 11.1.