MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations

Question

I had a table with ~0.5M rows and tried to execute a simple query, something like:

UPDATE table SET col1=-90 + RAND()*90, col2=-180+RAND()*360 WHERE col1=9999

but the result was

ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER

I have MaxNoOfConcurrentOperations=100000 and slightly more than 100000 rows which satisfy the where condition, so I have changed to this query:

UPDATE table 
SET col1=-90 + RAND()*90, col2=-180+RAND()*360 
WHERE col1=9999 ORDER BY ID LIMIT 100000

...and got the same error. And same error with LIMIT 1000.

I have updated rows using a query with LIMIT 100 in loop, but I think that it is a bad solution.

Questions

1. Why does MySQL Cluster behave this way?
2. How can I update many rows in a single table?

Answer 1

To solve the error message ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER you can use one of the possible solutions:

1) Increase MaxNoOfConcurrentTransactions even more until it works (note it take more resources (RAM))

2) Select the data from innodb into an outfile and load infile into cluster (will be faster than 2).

Note: you faced this error message since there is not enough resource on Dedicated for the cluster evnironment.

• ckorzhik: As you can see from question I use UPDATE (not INSERT) and it doesn't work even with small limits (100 times smaller than MaxNoOfConcurrentOperations). Actually, sometimes it doesn't work with LIMIT 1
• Ahmad Abuhasna: @ckorzhik removed two of the solutions one sincee the limit you used in the comment is 1 and number three.