Re: [Spock Proxy Devel] Some spockproxy questions.

[Michael W. <mi...@co...>]

So glad to know you like it. Any comments will be welcomed and  
appreciated.

Currently we don't have function like last_insert_id(), but  you may  
find this information in table  
site_universal_production.shard_table_directory which has a column  
"next_id".
If you minus "next_id" value by 1, you will get exactly what you want.

We may add this function in future version. For now, you can get  
last_insert_id by querying  
site_universal_production.shard_table_directory.

_
  Best Regards,
  Michael Wang






On Jul 29, 2008, at 12:26 PM, Zach Garner wrote:

> I've got a question. I've been working on a configuration using  
> spockproxy (which I'm really enjoying btw). So far I've got  
> everything working pretty well, which is exciting.
>
> My question is, is there a way to return the incremented column  
> value after performing an insert? Or to retrieve the value just  
> before performing the insert? I'm working with an application that  
> has been using an incrementing value in combination with the  
> last_insert_id() function to track the data and continue making some  
> changes to it.
>
> Anyway, I'll keep playing around. Chances are I've missed something  
> fairly obvious.
>
> It's been a real pleasure using such a nicely thought out  
> implementation like this.
>
> Regards!
>
> Zach
>
> On 7/21/2008 11:15 AM, Frank Flynn wrote:
>>
>> Karel,
>>
>>> [Spock Proxy Devel] Some spockproxy questions.
>>> From: Karel Vervaeke <karel@ou...> - 2008-07-18 15:24
>>> Hi, I just have a couple of questions
>>>
>>> About transactions:
>>> Suppose I need to store data in two partitions in one transaction,
>>> how does spockproxy the cross-server transactions?
>>
>> When  you issue the command "BEGIN TRANSACTION" the proxy sends  
>> this command to each database shard and holds those connections for  
>> you.  Then you can issues whatever commands you want and the proxy  
>> will send them to one or all the proxys (queries that do not have  
>> the sharding key in them will go to all the shards).  When you  
>> issue the COMMIT or ROLL BACK the proxy sends that to each shard  
>> and releases the connections.
>>
>>
>>> About the dynamic application pool:
>>> The spockproxy homepage mentions: "Upon startup the proxy  
>>> establishes a
>>> connection pool with the minimum number of connections. After  
>>> that, each
>>> time a client uses a connection, it is his for the life of the
>>> connection"
>>>
>>> For the life of the connection... does that imply that a once a
>>> connection between spockproxy and the database is reserved for a  
>>> client,
>>> that the client can only get data from that partition?
>>
>> Not at all.  The proxy looks at each query  and will send it to one  
>> or all of the shards depending on what it thinks is appropriate  
>> (based on your set up in the shard_range_directory and  
>> shard_table_directory tables).  The idea that a connection is  
>> reserved and dedicated to a particular client is only during  
>> transactions
>>
>>> If I understand correctly, it means that the when a client  
>>> connects to
>>> spockproxy and it requests data from two partitions in that same
>>> connection, the two connections spockproxy<->database connections
>>> created will remain reserved to that client connection until the  
>>> client
>>> connection is closed.
>>
>> No, only during a transaction.
>>
>>> Would that mean that the client should not use a
>>> connection pool to spockproxy (otherwise each pooled connection  
>>> will be
>>> assigned server-connections for all partitions after a while?
>>
>> No, connections are pooled at the proxy level.  When you connect to  
>> the proxy the database connection are already established so there  
>> is not the same overhead.  You can use connection pooling at the  
>> client level too with no particular problems, although since the  
>> proxy is pooling for you it might not give you the same  
>> advantages.  It depends on your client I suppose.
>>
>>> About the choice of the 'federated' keyword.
>>> That does not imply 'federated' as in the FEDERATED storage engine
>>> right? (Like you would need for proper cross-server sharding with  
>>> HScale
>>> on mysqlproxy?)
>>
>> When we say 'federated' we mean it in the same sense as the  
>> federated storage engine; that is you see what looks like one  
>> database but it is in fact several databases running on different  
>> servers.  But Spockproxy does not require any particular storage  
>> engine and although you could use a federated table with the proxy  
>> it's probably pointless since the proxy is federating it already.   
>> We use the INNODB storage engine primarily and have tested the  
>> MyISAM storage engine, but none of the others.  There is no reason  
>> they wouldn't work but we have not tested them.
>>
>> From the HScale Documentation at: http://hscale.org/display/HSCALE/Home
>>   "HSCALE is a plugin written for MySQL Proxy which allows you to  
>> transparently split up tables into multiple tables called partitions.
>>    In later versions you will be able to put each partition on a  
>> different MySQL server."
>>
>> Spockproxy splits all of your data into different databases  
>> (typically on different servers) and allows you to access these  
>> database shards as one database.  So it's more like their future  
>> version; we do not do 'partitioning' as in MySQL 5.1 where within a  
>> single database a table is divided into multiple shards.
>>
>> Finally the version in Sourceforge is a bit old - look for us to  
>> push a new version in the next day or two; no new features just  
>> fewer bugs.
>>
>> Good Luck,
>> Frank
>>
>>
>>
>> -------------------------------------------------------------------------
>> This SF.Net email is sponsored by the Moblin Your Move Developer's  
>> challenge
>> Build the coolest Linux based applications with Moblin SDK & win  
>> great prizes
>> Grand prize is a trip for two to an Open Source event anywhere in  
>> the world
>> http://moblin-contest.org/redirect.php?banner_id=100&url=/
>>
>> _______________________________________________
>> spockproxy-devel mailing list
>> spo...@li...
>> https://lists.sourceforge.net/lists/listinfo/spockproxy-devel
>>
>
>
> -- 
> _______________________________________________________________________
> Zach Garner | Sr. Systems Administrator / Product Analyst | iiON  
> Corporation | www.iion.com | Office: 858-713-0450 x 20
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> This message (and any associated files) is intended only for the  
> individual named and may contain information that is confidential,  
> subject to copyright or constitutes a trade secret. If you are not  
> the named addressee you are hereby notified that any dissemination,  
> copying or distribution of this message, or files associated with  
> this message, is strictly prohibited. If you have received this  
> message in error, please notify us immediately by replying to the  
> message and deleting it from your computer. Messages sent to and  
> from us may be monitored. Any views or opinions presented are solely  
> those of the author and do not necessarily represent those of iiON  
> Corporation.
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> -------------------------------------------------------------------------
> This SF.Net email is sponsored by the Moblin Your Move Developer's  
> challenge
> Build the coolest Linux based applications with Moblin SDK & win  
> great prizes
> Grand prize is a trip for two to an Open Source event anywhere in  
> the world
> http://moblin-contest.org/redirect.php?banner_id=100&url=/_______________________________________________
> spockproxy-devel mailing list
> spo...@li...
> https://lists.sourceforge.net/lists/listinfo/spockproxy-devel