List:General Discussion« Previous MessageNext Message »
From:P. Evans Date:April 28 2006 8:35pm
Subject:Re: Can I select from remote tables in mysql 5.0.18-standard-log?
View as plain text  
Thanks all,
   this looks exactly like what i'm looking for.
   
  However, when I create the federated table, it says it was successful but creates the
table as Myisam.
   
  CREATE TABLE `petestdb.backup_pp_line_code` (
  `catalog_id` int(10) unsigned NOT NULL,
  `line_code` char(3) NOT NULL,
  `product_typ_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`catalog_id`,`product_typ_id`),
  KEY `line_code_Index_2` (`line_code`)
) ENGINE=federated DEFAULT CHARSET=latin1
connection='mysql://myuser@myserver2:3306/database2/backup_pp_line_code';
   
  Show create table gives me :
   
  backup_pp_line_code | CREATE TABLE `backup_pp_line_code` (
  `catalog_id` int(10) unsigned NOT NULL,
  `line_code` char(3) NOT NULL,
  `product_typ_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`catalog_id`,`product_typ_id`),
  KEY `line_code_Index_2` (`line_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CONNECTION='mysql://myuser@myserver2:3306/database2/backup_pp_line_code' |

  When I query the federated table, it says 0 rows although the target has over 12000
rows. Could the mysql-max distribution be a factor ?  How do I verify if this is what i'm
running ?
   
  
Paul DuBois <paul@stripped> wrote:
  At 14:38 -0700 4/27/06, P. Evans wrote:
>Hello Listers,
> Is it possible to run a query on one mysql server to another 
>database on a different server ?
> eg creating an alias in database A on server A to table B on 
>database B on server B ?
>
> Like a federated nickname on db2 udb or synonym on informix ?

You can use FEDERATED to access tables on other MySQL servers.

http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html

-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com


		
---------------------------------
How low will we go? Check out Yahoo! Messenger’s low  PC-to-Phone call rates.
Thread
Can I select from remote tables in mysql 5.0.18-standard-log?P. Evans27 Apr
  • Re: Can I select from remote tables in mysql 5.0.18-standard-log?Paul DuBois27 Apr
    • Re: Can I select from remote tables in mysql 5.0.18-standard-log?P. Evans28 Apr
      • Re: Can I select from remote tables in mysql 5.0.18-standard-log?Kishore Jalleda28 Apr
  • Re: Can I select from remote tables in mysql 5.0.18-standard-log?Kishore Jalleda27 Apr