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 https://github.com/sordidfellow/MysqlSnippets/blob/master/p_create_federated_table.sql

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 github, and let me know what you think.

Code

Stephen kindly went into the wayback machine and retrieved the code that used to be on the now defunct MySQL Forge website. Thank you stephen! You can also view the code on github.
delimiter $$

drop  procedure if exists 
p_create_federated_table
$$
create procedure 
p_create_federated_table

(
    -- the ip address or name of the remote mysql server host (if NULL, 'localhost')
    p_remote_host     varchar(32)   
    -- the port where the remote  mysql server is listening (if NULL, 3306)
,   p_remote_port     int unsigned  
    -- the user on the remote server that accesses the table (if NULL, 'root') 
,   p_remote_user     varchar(16)   -- 
    -- the password for the remote user (if NULL, omitted)
,   p_remote_password varchar(32)
    -- the schema in which the remote table resides
,   p_remote_schema   varchar(64)
    -- the name of the remote table
,   p_remote_table    varchar(64)
    -- the local schema to create the local FEDERATED table (if NULL, p_remote_schema)
,   p_local_schema    varchar(64)
    -- the name of the local FEDERATED table (if NULL, p_remote_table)
,   p_local_table     varchar(64)
)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT 'Creates a FEDERATED table.'
/*
    Changelog
    WHEN?      WHO? WHAT?
    -----------------------------------------------------------
    2007-02-20 RPB  added handler to ignore warning 1366. Not sure why this appears.
                    lowered the group_concat_max_len (bug #23856)
                    removed the ORDER BY clauses from the GROUP_CONCATs on COLUMN_TYPE (bug #23856)
                    added output so we can see what's taking so long
                    added changelog
    2006-11-20 RPB  Created Initial version  
*/ 
begin
    -- size used for the GROUP_CONCAT buffer if current is lower
    -- please see http://bugs.mysql.com/bug.php?id=23856
    declare v_group_concat_max_len smallint 
        default 16384;
    -- stores the original size of the GROUP_CONCAT buffer to restore it
    declare v_old_group_concat_max_len int unsigned
        default @@group_concat_max_len;
    -- stores the original sql_mode
    declare v_old_sql_mode varchar(255)
        default @@sql_mode;
    -- Used to drop temporary tables
    declare v_drop_table_name varchar(64);

    -- set the GROUP_CONCAT buffer sufficiently large
    set @@group_concat_max_len := greatest(
        v_group_concat_max_len
    ,   v_old_group_concat_max_len
    );
    -- set the sql_mode to default to prevent invalid column defaults
    set @@sql_mode := '';

    -- The following block contains all the meat
    -- We use a separate block to allow for proper error handling.
    -- All errors that might be expected are handled inside this block
    -- This should guarantee that the outer block is always completed.
    -- That is necessary, beause we need to do a little cleaning up 
    -- before exiting the procedure.
    begin
        -- Used for defaulting the specified host
        declare v_remote_host     varchar(32) 
            default coalesce(p_remote_host,'localhost');
        -- Used for defaulting the specified user
        declare v_remote_user     varchar(16) 
            default coalesce(p_remote_user,'root');
        -- Used for defaulting the local schema
        declare v_local_schema    varchar(64)
            default coalesce(p_local_schema,p_remote_schema);
        -- Used for defaulting the local schema
        declare v_local_table     varchar(64)
            default coalesce(p_local_table,p_remote_table);
        -- Holds the connectstring prefix for FEDERATED tables
        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)
                )
            ,   '/'
            );

        -- Various conditions we might encounter
        -- We rename them just for clarity
        declare TABLE_EXISTS_ERROR condition FOR 1050;
        declare UNKNOWN_COLUMN_ERROR condition FOR 1054;
        declare SYNTAX_ERROR condition FOR 1064;
        declare GROUP_CONCAT_TRUNCATION_ERROR condition FOR 1260;
        declare INCORRECT_VALUE_ERROR condition FOR 1366;
        declare TRUNCATION_ERROR condition FOR 1406;
        declare FEDERATION_ERROR condition FOR 1429;

        -- Various handlers. These will execute if one of the conditions occur.
        -- They all show a friendly error message and the exit the inner block.
        -- Execution is resumed at the clean up code, just before 
        -- the end of the proceudre
        declare exit handler for FEDERATION_ERROR 
            select  'Federation error' error_type
            ,       'Check the connectstring.' error_message
            ,       v_connectstring connectstring
            ;
        declare exit handler for GROUP_CONCAT_TRUNCATION_ERROR 
            select  'GROUP_CONCAT Truncation' error_type
            ,       'Increase GROUP_CONCAT_MAX_LEN.' error_message
            ,      @@group_concat_max_len group_concat_max_len
            ;
/*
        declare exit handler for SYNTAX_ERROR 
            select 'Syntax Error' error_type
            ,      'Check this statement.' error_message
            ,      @create_table_statment statement
            ;
*/
        declare exit handler for TABLE_EXISTS_ERROR 
            select 'Table Exists' error_type
            ,      'Drop the table first.' error_message
            ,      concat(v_local_schema,'.',v_local_table) table_identifier
            ;
        declare exit handler for UNKNOWN_COLUMN_ERROR 
            select 'Unknown Column' error_type
            ,      'Unexpected error, check the statement.' error_message
            ,      @create_table_statement table_identifier
            ;
        declare exit handler for NOT FOUND 
            select 'No such Table' error_type
            ,      'The requested table was not found on the remote host.' error_message
            ,      concat(p_remote_schema,'.',p_remote_table) table_identifier
            ;
 declare continue handler for INCORRECT_VALUE_ERROR 
            select 'We hit warning 1366. It''s probably nothing serious.'
            ;
        declare exit handler for SQLEXCEPTION
            select 'SQL Exception' error_type
            ,      'Unexpected generic error. Debug the procedure.' error_message
            ,      concat(
                           'CALL ',schema(),'.', p_create_federated_table,'(' 
                   ,       if( p_remote_host is null
                           ,   'NULL'
                           ,   concat('''',p_remote_host,'''')
                           )
                   ,',',   if( p_remote_port is null
                           ,   'NULL'
                           ,   p_remote_port
                           )
                   ,',',   if( p_remote_user is null
                           ,   'NULL'
                           ,   concat('''',p_remote_user,'''')
                           )
                   ,',',   if( p_remote_password is null
                           ,   'NULL'
                           ,   concat('''',p_remote_password,'''')
                           )
                   ,',',   if(
                               p_remote_schema is null
                           ,   'NULL'
                           ,   concat('''',p_remote_schema,'''')
                           )
                   ,',',   if( p_remote_table is null
                           ,   'NULL'
                           ,   concat('''',p_remote_table,'''')
                           )
                   ,',',   if( p_local_schema is null
                           ,   'NULL'
                           ,   concat('''',p_local_schema,'''')
                           )
                   ,',',   if( p_local_table is null
                           ,   'NULL'
                           ,   concat('''',p_local_table,'''')
                           )
                   ,   ')'
                   ) call_command
            ; 

        -- The following block creates temporary federated tables 
        -- on the remote information_schema. 
        -- These are needed to generate the structure 
        -- of the local federated table. 
 select 'Getting remote metadata...';
        begin
            -- Prefix used for the temporary tables
            declare v_temp_table_prefix char(25)
                default 'p_create_federated_table$';
            -- Used to fetch the generated DDL from the cursor
            declare v_create_table_statment text;
            -- Cursor loop control variable
            declare v_no_more_rows boolean
                default FALSE;
            -- Cursor generates DDL for createing temporary federated
            -- tables on the remote information_schema.
            -- We need this to generate the DDL to create the actual 
            -- federated table specified by the user.
            declare csr_metadata cursor for 
                select      table_name
                ,           concat(
                                'create temporary table'
                            ,'\n',schema(),'.'
                            ,v_temp_table_prefix,table_name,'('
                            ,'\n',group_concat(
                                    column_name
                                ,   ' '
                                ,   column_type
                                ,   if(
                                        character_set_name is null
                                    ,   ''
                                    ,   concat(
                                            ' character set '
                                        ,   character_set_name
                                        ,   ' collate '
                                        ,   collation_name
                                        )
                                    )
                                ,   if( is_nullable='NO'
                                    ,   ' NOT NULL'
                                    ,   ''
                                    )
                                    separator '\n,'
                                )
                            ,'\n',')'
                            ,'\n','engine = federated'
                            ,'\n','connection = '
                            ,'\n',''''
                            ,     v_connectstring
                            ,     table_schema
                            ,     '/'
                            ,     table_name
                            ,     '''' 
                            )
                from        information_schema.columns
                where       table_schema = 'information_schema'
                and         table_name IN (
                                'COLUMNS'
                            ,   'STATISTICS'
                            ,   'TABLE_CONSTRAINTS'
                            )
                group by    table_schema
                ,           table_name
                ;
            -- handler to control the cursor loop
            declare continue handler for NOT FOUND 
                set v_no_more_rows := TRUE;

            set @drop_temporary_tables_statement := null;

            -- loop through the cursor
            open csr_metadata;
            my_loop: loop
                -- get the DDL for the temporary federated 
                -- information_schema table
                fetch csr_metadata 
                into  v_drop_table_name
                ,     v_create_table_statment;
  
                -- basic cursor loop control exits if cursor is exhausted
                if v_no_more_rows then
                    close csr_metadata;
                    leave my_loop;
                end if;
                -- build a statement to drop all temporary tables
                set @drop_temporary_tables_statement := if (
                    @drop_temporary_tables_statement is null
                ,   concat(
                        'DROP TEMPORARY TABLE IF EXISTS '
                    ,   v_temp_table_prefix
                    ,   v_drop_table_name
                    )
                ,   concat(
                        @drop_temporary_tables_statement
                    ,   ','
                    ,   v_temp_table_prefix
                    ,   v_drop_table_name
                    )
                );

                -- kludge: need a user variable to execute the DDL string
                -- dynamically with the PREPARE syntax
                set @create_table_statment := v_create_table_statment;

                -- create the temporary federated information_schema table 
                prepare stmt from @create_table_statment;
                execute stmt;
                deallocate prepare stmt;

            end loop;
        end;
 -- Reset the variable. Mainly to simplify debugging
 select 'Generating CREATE TABLE statement for FEDERATED table...';
 set @create_table_statment:='...generating statement...';
        -- This creates the actual ddl for the requested local FEDERATED table.
        -- It selects the DDL directly into the user variable. 
        -- It does this by querying the remote information_schema.
        -- This DDL includes the index definitions of the remote table.
        select      concat(
                        'create table if not exists'
                    ,'\n','`',v_local_schema,'`'
                    ,'.' ,'`',v_local_table,'`'
                    ,     '('
                    ,'\n',column_definitions
                    ,     coalesce(index_definitions,'')
                    ,'\n',')'
                    ,'\n','engine = federated'
                    ,'\n','connection = '
                    ,'\n',''''
                    ,     v_connectstring
                    ,     column_definitions.table_schema
                    ,     '/'
                    ,     column_definitions.table_name
                    ,     '''' 
                    ) stmt
        into        @create_table_statement
        from        (
                    select      table_schema
                    ,           table_name 
                    ,           group_concat(
                                        '`',column_name,'` '
                                    ,   column_type
                                    ,   if(
                                            character_set_name is null
                                        ,   ''
                                        ,   concat(
                                                ' character set '
                                            ,   character_set_name
                                            ,   ' collate '
                                            ,   collation_name
                                            )
                                        )
                                    ,   if( is_nullable='NO'
                                        ,   ' not null'
                                        ,   ''
                                        )
                                    ,   if( column_default is null
                                        ,   ''
                                        ,   concat(
                                                ' default '
                                            ,   case
                                                    when data_type = 'TIMESTAMP' 
                                                    and  column_default = 'CURRENT_TIMESTAMP'
                                                        then column_default
                                                    when data_type like '%char' 
                                                    or   data_type like 'date%'
                                                    or   data_type like 'time%'
                                                    or   data_type in ('set','enum')
                                                        then concat('''',column_default,'''')
                                                    else column_default
                                                end
                                            )
                                        )
                                    ,   if(extra='','',concat(' ',extra))
                                    ,   ' comment '
                                    ,   '''',column_comment,''''                                        
                                        separator '\n,'
                                ) as column_definitions
                    from        p_create_federated_table$columns
                    where       table_schema = p_remote_schema
                    and         table_name   = p_remote_table
                    group by    table_schema
                    ,           table_name
                    ) column_definitions
        left join   (
                    select      index_definitions.table_schema
                    ,           index_definitions.table_name 
                    ,           concat(
                                    '\n,'
                                ,   group_concat(
                                        case c.constraint_type 
                                            when 'PRIMARY KEY' then 
                                                constraint_type
                                            when 'UNIQUE' then 
                                                concat(
                                                    'CONSTRAINT '
                                                ,   constraint_name
                                                ,   ' '
                                                ,   constraint_type
                                                )
                                            else
                                                concat(
                                                    if( index_type in (
                                                            'FULLTEXT'
                                                        ,   'SPATIAL'
                                                        )
                                                    ,   concat(
                                                            index_type
                                                        ,   ' '
                                                        )
                                                    ,   ''
                                                    ) 
                                                ,   if( non_unique
                                                    ,   ''
                                                    ,   'UNIQUE '
                                                    )
                                                ,   'INDEX '
                                                ,   '`',index_name,'`'
                                                )
                                        end
                                    ,   index_columns
                                    ,   if( index_type in (
                                                'BTREE'
                                            ,   'HASH'
                                            )
                                        ,   concat(
                                                ' USING '
                                            ,   index_type
                                            )
                                        ,   ''
                                        )
                                        order by c.constraint_type
                                        separator '\n,'
                                    )
                                )    as index_definitions
                    from        (
                                select      table_schema
                                ,           table_name
                                ,           index_name
                                ,           index_type
                                ,           non_unique
                                ,           concat(
                                                '('
                                            ,   group_concat(
                                                    '`',column_name,'`'
                                                ,   if( sub_part is null
                                                    ,   ''
                                                    ,   concat(
                                                            '(',sub_part,')'
                                                        )
                                                    )
                                                    order by seq_in_index
                                                )
                                            ,   ')'
                                            ) index_columns
                                from        p_create_federated_table$statistics
                                where       table_schema = p_remote_schema
                                and         table_name   = p_remote_table
                                and         index_type not in ('FULLTEXT')
                                group by    table_schema
                                ,           table_name
                                ,           index_name
                                ,           index_type
                                ,           non_unique
                                ) index_definitions
                    left join   (
                                select      table_schema
                                ,           table_name
                                ,           constraint_name
                                ,           constraint_type
                                from        p_create_federated_table$table_constraints c
                                where       table_schema    = p_remote_schema
                                and         table_name      = p_remote_table
                                and         constraint_type in (
                                                'PRIMARY KEY'
                                            ,   'UNIQUE'
                                            )
                                group by    table_schema
                                ,           table_name
                                ,           constraint_name
                                ,           constraint_type
                                ) c
                    on          index_definitions.table_schema = c.table_schema
                    and         index_definitions.table_name   = c.table_name
                    and         index_definitions.index_name   = c.constraint_name
                    group by    table_schema
                    ,           table_name
                    ) index_definitions
        on          column_definitions.table_schema = index_definitions.table_schema
        and         column_definitions.table_name = index_definitions.table_name
        ;

        -- Create the actual FEDERATED table by dynamically executing
        -- the generated DDL for the requested FFEDERATED table. 
 select 'Creating FEDERATED table...';

        prepare stmt from @create_table_statement;
        execute stmt;
        deallocate prepare stmt;

        -- Print a friendly message that we succeeded
        select   'Success' completion_type
        ,        concat(
                        'Created FEDERATED table '
                    ,   v_connectstring,'/',p_remote_schema,'/',p_remote_table
                    ) completion_message
        ,        concat(v_local_schema,'.',v_local_table) table_identifier
        ;
    end;

    -- Cleanup: restore the original sql mode 
    set @@sql_mode := v_old_sql_mode;
    -- Cleanup: restore the original GROUP_CONCAT buffer size
    set @@group_concat_max_len := v_old_group_concat_max_len;

    -- Cleanup: drop the temporary federated information_schema tables.
    prepare stmt from @drop_temporary_tables_statement;
    execute stmt;
    deallocate prepare stmt;

    -- Cleanup: reset the user defined variables.
    set @create_table_statment := null
    ,   @drop_temporary_tables_statement := null
    ;
end;
$$

delimiter ;

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

William Anthony said...

Hi Mr.Bouman,

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.

Thanks.

Roland Bouman said...

Hi William,

thanks for your interest in this procedure.

I actually don't think I have it anymore. I also don't have a setup like yours at the moment so it is not so easy for me to help you debug or fix it.

If you have a very specific question, I could give it a shot but I don't feel like updating the proc on my own.

I hope this helps, sorry if I'm not being more helpful.

Best regards,

Roland

Stephen said...

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

Roland Bouman said...

@Stephen, thank you so much! It is much appreciated. I added a new section at the bottom of the blog which lists the code.