Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Wednesday, November 22, 2006

MySQL: A Stored Procedure to Create FEDERATED Tables.

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:

http://forge.mysql.com/snippets/view.php?id=54

Sample1:


call p_create_federated_table(
'192.168.0.100'
, 3306
, 'my_remote_username'
, 'my_remote_password'
, 'my_remote_schema'
, 'my_remote_table'
, 'my_local_schema'
, 'my_local_table'
);


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

Sample2:


call p_create_federated_table(
'myhost.mydomain.org'
, NULL
, NULL
, NULL
, 'my_schema'
, 'my_table'
, NULL
, NULL
);


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'.

If you want to know how it works exactly: the procedure has got pretty good comments. Go get it at http://forge.mysql.com/snippets/view.php?id=54, and let me know what you think.

25 comments:

Anonymous said...

Got the follow error:
+----------------+----------------------------------------+------------------+
| error_type | error_message | table_identifier |
+----------------+----------------------------------------+------------------+
| Unknown Column | Unexpected error, check the statement. | NULL |
+----------------+----------------------------------------+------------------+

Roland Bouman said...

Hi!

I am at a loss why the Unknown Column error is thrown, but I suspect that the underlying problem is that the generated statement is NULL (hence the NULL) value for table_identifier.

The most likely cause for this is that the table you are trying to point to does not exist, or is not accessible to the user that is connecting to the remote server.

Can you please check this and post back if that does not shed more light on the problem?

TIA,

Roland

Michael said...

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!

Aloha - Michael

Anonymous said...

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?

Roland Bouman said...

Hi there!

Did you check the SQL statements that are being generated?

Michael said...

To: Anonymous posting on Wednesday, March 11, 2009 9:09:00 PM CET

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.

Does your local user have "create temporary table" priv's?

Aloha,

Michael

Anonymous said...

"Did you check the SQL statements that are being generated?"

How would I do that?

"Does your local user have "create temporary table" priv's?"

I am using root (even though that's a no-no).

Roland Bouman said...

Hi!

do a

SELECT @drop_temporary_tables_statement
, @create_table_statment

to see what is being executed.

Roland Bouman said...

BTW - looks like i made a speling error...statment i.o. statement (in the code I mean, I should fix that....)

Anonymous said...

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 Bouman said...

Hi!

I just fixed the error and updated the code at sourceforge. If I start a clean session and run it, it seems to work properly. Can you please check again?

Anonymous said...

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?

Brian

Roland Bouman said...

Hi Brian!

This is how the connectstring is generated:

declare v_connectstring varchar(255)
default concat(
'mysql://'
, v_remote_user
, if(p_remote_password is null
, ''
, concat(':',p_remote_password)
)
, '@'
, v_remote_host
, if(p_remote_port is null
, ''
, concat(':',p_remote_port)
)
, '/'
);

So - it should just work. This is what I get when I use NULL as password:

mysql> call p_create_federated_table(
-> 'localhost'
-> , 3351
-> , 'root'
-> , null
-> , 'sakila'
-> , 'customer'
-> , schema()
-> , 'customer'
-> );
+----------------------------+
| progress |
+----------------------------+
| Getting remote metadata... |
+----------------------------+
1 row in set (0.00 sec)

+----------------------------------------------------------+
| progress |
+----------------------------------------------------------+
| Generating CREATE TABLE statement for FEDERATED table... |
+----------------------------------------------------------+
1 row in set (0.03 sec)

+------------------+--------------------------+------------------------------+
| error_type | error_message | connectstring |
+------------------+--------------------------+------------------------------+
| Federation error | Check the connectstring. | mysql://root@localhost:3351/ |
+------------------+--------------------------+------------------------------+
1 row in set (0.05 sec)

Did you start a new session? Just in case some old variables are bugging you?

Anonymous said...

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?

Brian

Roland Bouman said...

Hi Brian!

sorry to hear you're still having trouble getting it to work.

I'm running MySQL 5.1, but AFAIK, I am not using any 5.1 specific features.

You could plunge in and try to debug. I don't have time for it myself ATM. Sorry...

Anonymous said...

Not a problem. I'll keep working at it. When I figure it out I'll post the solution. Thanks again for all the help Roland.

Brian

Anonymous said...

Thanks for that procedure. It saved me a lot of time writing it on my own.

Here is the fix for the "Unknown column p_create_federated_table" bug. It's in the bottom error handler for SQLEXCEPTION:

'CALL ',SCHEMA(),'.', p_create_federated_table, '('

Change to:

=> 'CALL ',SCHEMA(),'.p_create_federated_table('

Sometimes MySQL calls undefinded variables "columns"... ;-)

Roland Bouman said...

Hi Anonymous!

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 :)

Francisco Javier Solans Benedí said...

Hi,

When I use yor script from forge.mysql.com, I have a mistake with Mysql 5.1 (Debian and Fedora) because the database is case sensitive.

mysql> call p_create_federated_table('localhost' ,3306,'root','','mysql','user','test','acls');
+----------------------------+
| progress |
+----------------------------+
| Getting remote metadata... |
+----------------------------+
1 row in set (0.01 sec)

+----------------------------------------------------------+
| progress |
+----------------------------------------------------------+
| Generating CREATE TABLE statement for FEDERATED table... |
+----------------------------------------------------------+
1 row in set (0.01 sec)

+---------------+------------------------------------------------+--------------------------------------------------------------------------------------------------------------+
| error_type | error_message | call_command |
+---------------+------------------------------------------------+--------------------------------------------------------------------------------------------------------------+
| SQL Exception | Unexpected generic error. Debug the procedure. | CALL controller_delegation.p_create_federated_table('localhost',3306,'root','','mysql','user','test','acls') |
+---------------+------------------------------------------------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

This are changes I have made:

Change:
from p_create_federated_table$table_constraints c
and put this:
from p_create_federated_table$TABLE_CONSTRAINTS c

Change:
from p_create_federated_table$statistics
and put this:
from p_create_federated_table$STATISTICS

Change:
from p_create_federated_table$table_constraints c
and put this:
from p_create_federated_table$TABLE_CONSTRAINTS c

AND NEXT ALL OK !!!

Roland Bouman said...

Fransisco,

thanks! I had a habit of running MySQL with lower_case_table_names, which I felt made it easier when working with MySQL on both linux annd Windows. This is probably why I didn't spot it.

Anyway, I updated the script, and attributed you in the change log. Thanks again!!

Anonymous said...

This procedure looks to be exactly what I have been looking for; however, when I try to use it I consistently get the following error:

#1312 - PROCEDURE mla_test.p_create_federated_table can't return a result set in the given context

I have tried with multiple different databases and I believe that the user has full access rights. I suspect I must be dong something stupid. Any ideas?

Phill

Anonymous said...

My apololgies. I have sorted this out myself. My error.

Phill

federated_life said...

If your using mysql 5.0, the index btree/hash throws an error since they are not supported till 5.1. I changed lines 423 to 432 to add a check for 5.0


IF( index_type IN ('BTREE', 'HASH'),
IF( left(@@version, 3) < 5.1, '', concat(' USING ', index_type))
, '')


Great script!!

cheers

Alex

Anders Jorsal said...

Unfortunataly, the link to the script is dead:
http://forge.mysql.com/snippets/view.php?id=54

I have tried to search for it on Google, but they all refer to the same dead link.

Roland Bouman said...

Hi Anders,

yeah, the forge site was removed a while ago. Unfortunately I don't seem to have this procedure anymore. I'll try and find it on an old backup.