Re: [Spock Proxy Devel] Universal tables question...

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Zach,

You're almost there.  It sounds like you have everything set up  
correctly but you are missing one piece.  That is - in each shard  
schema you need to create a view to every table in the local universal  
schema.  And I have a script for you that  does the too.

The reason for doing it this way is so your app can join on all the  
tables within each shard.  So if you have a federated table which had  
a 'status_id' field and then a universal table 'status' that has the  
status_id and the status_name you need to be able to join the two; the  
status table would need to be inside each shard.

Here is the shell script to fix this for you:

---------- rebuildViews.sh starts on the next line ------------
#! /bin/bash

# create all the views for the shards.

# WARNING - this will DROP ALL THE EXISTING VIEWS in the shard.
# so if you have any other views that are not related to the universal  
DB
# you would need to recreate them

# ALSO you need to enter your name, password and the DB and server array

echo "starting at: "
date

NAME='frank'
PASSWORD='KC6evp01'

# for i in is expecting a 2x array of shards in the form of (quote "  
marks are important):
# "schema_name server_name" "schema_name server_name" "schema_name  
server_name"
# where schema_name and server_name are values that would work in a  
mysql command

for i in "site_01_production s035" "site_02_production s036"  
"site_03_production s037" "site_04_production s038"
do
     set  $i
     echo $1 $2
     /usr/bin/mysql -u $NAME -p$PASSWORD -h $2 -D $1 -e "SELECT  
CONCAT('DROP VIEW ', TABLE_NAME, ';') AS '--'  FROM  
INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$1' AND TABLE_TYPE =  
'VIEW' ;" > tmp.sql
     /usr/bin/mysql -u $NAME -p$PASSWORD -h $2 -D $1 -e "SELECT  
CONCAT('CREATE VIEW ', t.TABLE_NAME, ' AS SELECT ',  
group_concat(COLUMN_NAME), ' FROM site_universal_production.',  
t.TABLE_NAME, ';') AS '--' FROM INFORMATION_SCHEMA.TABLES t,  
INFORMATION_SCHEMA.COLUMNS c  WHERE t.TABLE_SCHEMA =  
'site_universal_production' AND c.TABLE_SCHEMA =  
'site_universal_production' and t.TABLE_NAME = c.TABLE_NAME GROUP BY  
t.TABLE_NAME;" >> tmp.sql
# comment the next line if you do NOT what the script to actually drop  
and create the views
     /usr/bin/mysql -u $NAME -p$PASSWORD -h $2 -D $1 < tmp.sql
done

rm tmp.sql

echo "done at: "
date

------------------ end of shell script ---------------

Good luck,
Frank

On Oct 15, 2008, at 12:25 PM, Zach Garner wrote:

> I seem to be having trouble with Universal tables under read/write  
> scenarios (all my earlier testing only involved writes on federated  
> tables). After reading the spock section universal tables I thought  
> I understood it, but my results aren't quite what I'd expected:
> We have solved it by having a 'universal' database which contains  
> all of these tables as well as some directory tables. Each shard  
> database is a slave of this universal database server and the  
> universal database is replicated to each shard. There are two  
> databases on each shard, the shard itself which is unique to that  
> shard (and perhaps it's slaves) and the universal database which is  
> identical to all other universal databases.
> So I now have two schemas on each shard. The configuration schema  
> (site_universal_production) which in addition to the shard_* tables  
> now contains each universal table as well and the federated schemas  
> which only contain copies of the tables we'll be federating.
>
> I've setup replication for this schema (site_universal_production,  
> to my 2 shard nodes) and have them successfully working as slave  
> nodes; replicating updates to site_universal_production locally as  
> I'd expect.
>
> So now things like updates work with universal tables and the  
> changes ripple down nicely to each shard.
>
> The trouble I'm having now is that selects on universal tables are  
> still looking for the tables in the federated shard databases (so  
> they fail) and I can't seem to see where or how to explicitly map  
> the universal tables to the site_universal_production database.
>
> Of course I could try table level replication and bypass the two  
> databases per shard (and therefor the lookup issue, I probably will  
> now for fun!) but since your notes seem to be pretty clear on the  
> use of separate databases I figure I'm actually missing something  
> very obvious.
>
> :-)
>
>
> -- 
> _______________________________________________________________________
> 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