MySQL Cluster - Tables not synced

[tfunky]

Hello,

I'm very confused. I'm working to get my mysql database migrated to the mysql cluster built using the mysql cluster howto from this site.

The cluster did well on all of the testing in the howto, so I thought everything was okay.

I created my new database on each cluster member using the ndbengine setting and that seemed to go okay.

Then did a dump of my current database off the old server and changed the engine setting throughout.

I restored the dump to the cluster (with the right engine setting) and it seemed to go in just fine, but after a few minutes the tables didn't match.

Here is a example of the problem I'm seeing

Box1 ||| Box2
-------------------|||---------------
Key | Color | size ||| Key | Color | size
------------------------------------------
4 |Red |Large ||| 5 | Blue | Small
5 |Blue |Small |||3 |Purple | Large
2 |Green |Meduim|||1 |Pink | Small
1 |Pink |Small |||2 |Green | Medium
3 |Purple |Large |||4 |Red | Large
--------------------------------------------

The data is there and the key matches the color matches the size, but they are completely out of order. On the export the database has them ordered by the primary key. On the cluster neither of them are ordered by the primary key.

I thought that maybe something just went haywire with the dump restore to the cluster, so I purged the databases from both of the cluster members, then re-restored to the first Mysql cluster member.

After giving it a min or two to sync I checked it again and got the same problem. The data is getting synced across the boxes....but it's jumbled when it gets to the other side.

I've dug around on the mysql site and I'm not having any luck resolving this. I used the process of converting myisam to ndbengine that mysql recommends (export it, find/replace the engine statement) and it loads okay without erroring out, but when the data gets to the system it gets screwy.

Can any offer any recommendations on what I can do to resolve this?

Thanks!

Tfunk

[falko]

Are the records in the right order if you add

ORDER BY Key

to your SQL query?

[tfunky]

If I do order by in the query it works...problem is the php coder DIDN'T use order by in their queries...they just relied on the natural order in the database (lazy I know...but that's besides the point)

What makes me think something is screwy is that in the old database there in the right order, in the export from the old database their in the right order, if I load the dump to the #1 cluster member they get out of order...and when they replicate/sync to the #2 cluster membe they are in another different order (not in the "correct" order from the original, and not even in the same order as the #1 cluster member)

I would have thought that the 2 cluster members tables should be 100% identical.

Is that not the case?

[falko]

I'm not sure if this is a problem, because I see the same happening in a normal phpMyAdmin (on a single, not clustered MySQL database) quite frequently (i.e., rows not ordered by keys), but this hasn't caused any problems for me.
I think you should use ORDER BY where necessary in your applications.

[tfunky]

yeah...that's kind of the pain...IF the code was written correctly the first time (with order by) then this wouldn't really be an issue at all.

The problem is that it wasn't and the coder is complaining how her code USED to work now it's not

Other than this annoyance the cluster seems to be working (though it seems to execute queries slower than a single box...but I'm sure that's just a tuning issue)

I guess I'll just have to dig my heals in and have the code changed

ThankS!

Tfunky