Re: Is mySql 5.0.10 beta-max is ready for clustering using stored procedures

[Mikael Ronström]

Hi,
vick wrote:
> I set up a cluster with two data and sql node and
> one mgm node.
> I can do simple inserts,selects,deletes using SQL
> statements on both of SQL node without any
> problem.
> But when I use stored procedures on any of my sql
> mode and try to insert anything, it crashes.
> For eg.
> I have a table called t1(id int PK,name
> varchar(20)) and a stored procedure as get_all().
> This proc. has only one st. as select * from t1.
> Also, when I create this proc. on one node I
> CANNOT see that proc. on the other node.
>

Stored procedures, triggers and views are local to the MySQL Server and not
cluster-wide in 5.0.

> After firing some simple inserts, selects, try
> calling the stored proc. as call get_all().
> It would work fine.
> Now if I would try to Insert a row(using INSERT
> st.) like INSERT INTO T1(name) values('mysql');,
> it shows 1 row affected.
> Now fire SELECT * from t1;
> you will see a row like 4006 (id), mysql (name).
> All subsequent Insert fail thereafter becuase of
> PK constraint (here 4006)
>

Am I missing something here? Shouldn't the PK be specified in the INSERT statement?
Seems like the INSERT gets a random PK = 4006 and obviously the next INSERT which will
most likely get the same random PK = 4006 will not work since this is a violation of a primary
key constraint.

Rgrds Mikael

> Does anyone has any idea.
> Am I missing anything ? or 5.0.10-beta is not
> ready for clustering if stored procedure is used.

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog: http://mikaelronstrom.blogspot.com