federated table query

Hi,

I have 2 databases on 2 different servers (local and remote). I am trying to creat federated table and use it for query.
I have created a federated table name temp_table based on table customer in datbase kkkkk1 at remote server.

drop table if exists temp_table;
create table temp_table (
id varchar(100) not null primary key,
SHIP_TO_NUMBER text null,
BILL_TO_NUMBER text null,
SHIP_NAME text null,
SHIP_ADDRESS_1 text null,
.......
CUST_PO text null
)
engine=federated
DEFAULT CHARSET=latin1
connection='mysql://yyyy1:zzzzzz1@xx.xx.xx.11:3306/kkkkk1/customer';


$con_remote = mysql_connect("xx.xx.xx.11","yyyy1","zzzzzz1");
$db_selected_1 = mysql_select_db("kkkkk1", $con_remote);

$con_local = mysql_connect("xx.xx.xx.22","yyyy2","zzzzzz2");
$db_selected_2 = mysql_select_db("kkkkk2", $con_local);

$result = mysql_query("SELECT * FROM temp_table" , $con_local);

echo mysql_num_rows($result); // this give out 0 as result, why?? the remote table has tons of record

Thanks for any help in advance.

 

Did you grant the correct permissions on the remote server, to the local server?

Should have at least select privileges. Also, if you have a program like navicat or phpmyadmin, try opening the federated table and see if it throws an error.

Also, you may want to have your script throw errors to help debug some...

if(!$result = mysql_query("SELECT * FROM temp_table" , $con_local)) {

die(mysql_error());

}

You can do the same for the connection.

 

Thanks for the response.
It turns out that the mysql doesn't have Federated storage engine turn on. (show engines)
http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html mentions the following. I am not sure how to do it after the database is setup and has tons of data in it already.

"To include the FEDERATED storage engine if you build MySQL from source, invoke configure with the --with-federated-storage-engine option."

I also noticed that it is introduced for 5.1.3 and removed 5.1.9. (wonder why it was removed). Does this mean it will only work if your mysql is between 5.1.3 and 5.1.9???
http://dev.mysql.com/doc/refman/5.1/en/configure-options.html

 

If you have ssh access, log into your db, edit my.cnf

Add under [mysqld]

federated

If you're using cpanel and many other hosts, you just need to enable it. Also make sure to comment out skip-federated if it is in my.cnf

Restart mysql and then log into mysql. Run:

show engines;

See if federated is available.

 

Thanks for the response.

I only have navicat and PLESK access. How do I go about it?

 

Contact your host and ask them to enable it. Without ssh access, you wont be able to enable it even if mysql is compiled with it.