From: Kishore Jalleda
Date: April 28 2006 8:46pm
Subject: Re: Can I select from remote tables in mysql 5.0.18-standard-log?
List-Archive: http://lists.mysql.com/mysql/197450
Message-Id: <78aaf6710604281346p7be8d25dt9514a6087bb3ffbc@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; 
	boundary="----=_Part_6783_12477278.1146257185593"

------=_Part_6783_12477278.1146257185593
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 4/28/06, P. Evans <pevansdba@stripped> wrote:
>
> 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=3Dfederated DEFAULT CHARSET=3Dlatin1
> connection=3D'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=3DMyISAM DEFAULT CHARSET=3Dlatin1 CONNECTION=3D'
> 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.
>


As I said earlier you will need the Mysql-Max Binary for the FEDERATED
engine to work, you can get what version you are running by logging into
mysql via the CLI, or with a SHOW VERSION();

Kishore Jalleda

------=_Part_6783_12477278.1146257185593--