Wednesday, November 22, 2006

The FEDERATED storage engine is a MySQL feature that lets you access the tables (and views) on a remote MySQL server. In terms of purpose and functionality, it best compares to a "linked table" in MS Access, and to a lesser extent, to Oracle database links.

Creating a federated table is tedious, because in the local database, you need to define a table with exactly the same structure as the remote table. That includes the index definitions, because these are used by the local optimizer to create a query plan (although the statistics for the index will not be locally available)

I created a procedure to make life easier (well, at least as far as creating FEDERATED/ tables is concerned). You can grab if here:

This sample will create a FEDERATED table named 'my_local_table' in the schema 'my_local_schema'. The created table will point to the table 'my_remote_table' in the schema 'my_remote_schema' on the remote MySQL Server on the host 192.168.0.100 that is listening on port 3306

This sample will create a FEDERATED table with the same name as the specified remote table, and in the local schema with the same name as the specified remote schema. The remote mysql server is listening on the default mysql port (usually 3306) on the host myhost.mydomain.org'.

I encountered the same problem as the Anonymous user above (post dated Tuesday, December 9, 2008 11:39:00 AM CET). It turns out that the problem was that my remote user account on the remote database didn't have privileges to "SELECT" on the information_schema database. Once I granted priv's to that user, all went well. Thanks for the contribution Roland!

I'm having the same issue. It's not privilege related though, as I'm skipping the grant tables on my testing server. I commented out the "Unknown" portion of the Error Handler and got the following: ERROR 1054 (42S22): Unknown column 'p_create_federated_table' in 'field list' Any thoughts?

Commenting out the "Unknown portion of the Error Handler" doesn't address the fact that errors are occurring (Or maybe you commented something else out as well?).

It's not "grants" that are being queried from the remote server, it's actually the metadata information for the table being "federated".

The "p_create_federated_table" is actually a prefix for a temp table. If your user on the remote machine doesn't have SELECT priv's on the information_schema, I don't think that you'll be able to create the temp tables necessary to complete the procedure.

Roland,Interestingly enough, when I fixed the spelling error that you noticed I no longer had a "NULL" value for the table_identifier. Now it shows up as "...generating statement..." I also added the selects that you suggested. For the @drop_ statement I got "DROP TEMPORARY TABLE IF EXISTS p_create_federated_table$COLUMNS,p_create_federated_table$STATISTICS,p_create_federated_table$TABLE_CONSTRAINTS" and for the create_ statement I had "...generating statement..." Is this what I should have? I apologize, I am fairly new to stored procedures and federated tables. Thanks in advance!!!

Roland,I grabbed the new sourceforge code, created the procedure and tested it again with the same results. If NULL is specified for the password does it make an attempt at using a password or would that be the same as mysql -u username with no "-p" flag?

I started a new session and I'm still getting the same result. It has to be something simple that I'm missing. I can create temporary tables on both servers with the user I am using. The user has privileges to perform any action on both servers. What version of MySQL are you running this on? Maybe there is something with the version I'm running. 5.0.51a on both the local server and remote server. Any other ideas?

glad the sp was useful to you. And thanks a bunch for the bugfix...I looked into it several times, but coulnd't find it. I figured it must be a code path that wasn't triggered all the time. Thanks again :)

I don't know if you still maintain this blog and read my comment here. I still have the code and still using it for my operational database.

Unfortunately, the procedure failed to create the table recently, because the remote and local mysql server has different version, local is original mysql 5.0.83, remote is mariadb 10.0.21.

After debugging the process, I got this error: "Got error 10000 Error on remote system 1054 Unknown column DATETIME PRECISION in field list from FEDERATED". Mariadb has DATETIME_PRECISION column in information_schema DB while original mysql doesn't.

I'm still in process of modifying the procedure to make it work again, I appreciate if you could give me some help.

I've scavenged the code from the Wayback machine. It was a bit infuratiating that the top Google search results for 'mysql stored procedure to generate federated tables' all seem to point here, and yet the code was gone.

Code is here:https://github.com/sordidfellow/MysqlSnippets/blob/master/p_create_federated_table.sql

Search This Blog

About Me

I'm Roland Bouman (@rolandbouman on twitter). I'm a software (web) application developer and I work on both the front end as well as the back end. I do data modeling, database design, ETL, Analytics, and Business Intelligence.