Select from remote server from stored procedure

Ok, I feel silly for asking this, but I am going to do it anyway.

I have a huge stored procedure that does quite a bit of logic, and
gathering/splitting of data. I currently have our customer database on one
server, and our logging on another. What i need to do, is to pull the
customer id from the other server, so that the logs are tied back to the
customer.

Is this possible to do? To make a connection, inside the stored procedure
to a completely different machine and access the mysql there?

Does my question make sense? Currently what I am doing, is every new
customer that gets created, my php app adds the username/customerid to that
server, then makes a connection to the logging server and creates the same
record, same with deleting and updating... but there just has to be a
simpler way :)

Thanks in advance.


Steven Staples




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Select from remote server from stored procedure

> Is this possible to do? To make a connection, inside the
> stored procedure
> to a completely different machine and access the mysql there?

The only way I know to access tables from different servers
from a single connection is federated tables:
http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

Once you do that, you are accessing it like a local table.

I hope this helps.

Neil

--
Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
Host your MySQL database on a CentOS VPS for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Select from remote server from stored procedure

--001636c5a4bd068b9e047a4e1a66
Content-Type: text/plain; charset=ISO-8859-1

Posted this before, but beware: federated tables do NOT use indices. Every
select is a full table scan, and if you're talking about a logging table
that could become very expensive very fast.

On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal wrote:

> > Is this possible to do? To make a connection, inside the
> > stored procedure
> > to a completely different machine and access the mysql there?
>
> The only way I know to access tables from different servers
> from a single connection is federated tables:
> http://dev.mysql.com/doc/refman/5.0/en/federated-use.html
>
> Once you do that, you are accessing it like a local table.
>
> I hope this helps.
>
> Neil
>
> --
> Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
> Host your MySQL database on a CentOS VPS for $25/mo
> Unmetered bandwidth = no overage charges, 7 day free trial
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>

--001636c5a4bd068b9e047a4e1a66--

Re: Select from remote server from stored procedure

Hello Johan,

On Dec 9, 2009, at 11:22 AM, Johan De Meersman wrote:

> Posted this before, but beware: federated tables do NOT use indices.
> Every
> select is a full table scan, and if you're talking about a logging
> table
> that could become very expensive very fast.

This is not entirely true. If you define an index on the local
federated table, and it makes sense to use it, then a remote WHERE
clause will be passed through and hence use the remote index. Not all
types of index accesses can be passed through such as this, however
for a single row lookup on a primary key, it should be fine.

It is still not as fast as local access, but it's not as bad as always
doing a full table scan remotely.

> On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal
> wrote:
>
>>> Is this possible to do? To make a connection, inside the
>>> stored procedure
>>> to a completely different machine and access the mysql there?
>>
>> The only way I know to access tables from different servers
>> from a single connection is federated tables:
>> http://dev.mysql.com/doc/refman/5.0/en/federated-use.html
>>
>> Once you do that, you are accessing it like a local table.
>>
>> I hope this helps.
>>
>> Neil

Regards,

Harrison
--
Harrison C. Fisk, MySQL Staff Support Engineer
MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Select from remote server from stored procedure

So what I am reading, I guess it would be safer to just do it how I am
currently doing it, as it really isn't that slow... it's just duplicating
the data elsewhere (I suppose maybe making this a slave table to the other
server... nah... lots of work there :P)

Thanks, and I did search it before, but I guess my searching keywords were
insufficient ;)


Steven Staples


-----Original Message-----
From: Harrison.Fisk@Sun.COM [mailto:Harrison.Fisk@Sun.COM]
Sent: December 9, 2009 2:07 PM
To: Johan De Meersman
Cc: Neil Aggarwal; Steven Staples; mysql@lists.mysql.com
Subject: Re: Select from remote server from stored procedure

Hello Johan,

On Dec 9, 2009, at 11:22 AM, Johan De Meersman wrote:

> Posted this before, but beware: federated tables do NOT use indices.
> Every
> select is a full table scan, and if you're talking about a logging
> table
> that could become very expensive very fast.

This is not entirely true. If you define an index on the local
federated table, and it makes sense to use it, then a remote WHERE
clause will be passed through and hence use the remote index. Not all
types of index accesses can be passed through such as this, however
for a single row lookup on a primary key, it should be fine.

It is still not as fast as local access, but it's not as bad as always
doing a full table scan remotely.

> On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal
> wrote:
>
>>> Is this possible to do? To make a connection, inside the
>>> stored procedure
>>> to a completely different machine and access the mysql there?
>>
>> The only way I know to access tables from different servers
>> from a single connection is federated tables:
>> http://dev.mysql.com/doc/refman/5.0/en/federated-use.html
>>
>> Once you do that, you are accessing it like a local table.
>>
>> I hope this helps.
>>
>> Neil

Regards,

Harrison
--
Harrison C. Fisk, MySQL Staff Support Engineer
MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=sstaples@mnsi.net

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.709 / Virus Database: 270.14.97/2550 - Release Date: 12/09/09
02:32:00


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Select from remote server from stored procedure

Steve:

> I suppose maybe making this a slave table
> to the other
> server... nah... lots of work there

Setting your local server to be a slave of the
remote server is not too hard and would
be a MUCH better solution.

The steps are fairly staightforward:

1. Add a slave user to the remote database
2. Tell the remote server to create a binary
log
3. Tell the local server to be a slave of
the remote
4. Start the slave

It should take less than 1 hour to set it up.
I have done it many times. It is probably not
as hard as you are thinking it will be.

Neil


--
Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
Host your MySQL database on a CentOS VPS for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Use stored proc result set from another stored proc without temptable?

I have a stored proc I need to call from yet another stored proc, which
then needs to use the results from the called proc ... I could probably
use temp table but really dont want to do that unless absolutely
required. I cant use OUT parameters, because the called stored proc
returns multiple rows.. can this be done (see the two samples below) or
is a temp table the only way?

This generates a result set
DELIMITER $$

DROP PROCEDURE IF EXISTS `getCMDBdata`$$
CREATE PROCEDURE `getCMDBdata`(IN ipadd VARCHAR(16))
BEGIN
DECLARE iid text;
select ifnull(group_concat(distinct `ia`.`ITEMID`),'0') into iid
from `federated_itemattributes` `ia` left join
`federated_attributesvarchar2` `v` on(`ia`.`ITEMATTRIBUTEID` =
`v`.`ITEMATTRIBUTEID`)
where v.value = ipadd and ia.typeattributeid = 8259;

SELECT i.itemid, i.typeattributeid, i.itemattributeid, `VALUE`
, y.typeid
, y.datatypeid
, y.description
, p.typename
FROM federated_itemattributevalues i
join itemattributes a on a.itemattributeid = i.itemattributeid
join items t on t.itemid = i.itemid
join typeattributes y on y.typeattributeid = i.typeattributeid
join types p on p.typeid = y.typeid
left outer join relationships r on r.ci1 = i.itemid and
r.relationshiptypeid IN (2667, 2684, 2704)
WHERE r.ci1 IN (iid) OR ci2 IN (iid)
ORDER BY i.itemid, y.typeid;

END$$

DELIMITER ;


This calls the above proc and will eventually want to do things with the
results:

DELIMITER $$

DROP PROCEDURE IF EXISTS `getMatch`$$
CREATE PROCEDURE `getMatch`()
BEGIN
DECLARE done0 INT DEFAULT 0;
DECLARE CID BIGINT (20);
DECLARE HIP VARCHAR (16);
DECLARE cur1 CURSOR FOR select distinct hostid, ip from hosts;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done0 = 1;

OPEN cur1;
REPEAT
FETCH cur1 INTO CID,HIP;
IF NOT done0 THEN
CALL `getCMDBdata`(HIP);
/* Do something with result above*/
END IF;
UNTIL done0
END REPEAT;
CLOSE cur1;

END$$

DELIMITER ;


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org