MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations

errorsMySQLmysql-clusterupdate

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

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

Best Answer

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.

INDEXING

You should index the table fully to support all possible ways you will be querying the data. Notwithstanding, you must alternate between SELECT ... FOR UPDATE and UPDATE.
Since you have both row1 and row2 in the WHERE clause, you should have an index with both columns in it.
There is one warning: If these columns are indexed, expect some slowness because the column is being updated and the BTREE index pages are being updated per row. You should also expect rapid growth of the insert buffer section of ibdata1 (See InnoDB Map)

I have many posts on the subject of SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARED MODE.

Case Study I went through with @RedBlueThing
Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
Oct 18, 2011 : Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE
Jan 02, 2012 : LOCK IN SHARE MODE
Nov 19, 2012 : How long can Deadlock info hold in innodb status page?
Dec 13, 2012 : MySQL InnoDB locks primary key on delete even in READ COMMITTED
Feb 03, 2013 : MySQL InnoDB locking on combined UPDATE-JOIN statements
Mar 12, 2013 : How I prevent deadlock occurrence in my application?

UPDATE 2013-03-17 19:21 EDT

Since you have 9 WebServers hitting the DB Server, try this

On WebServer1 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 0,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 0,100;

On WebServer2 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 100,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 100,100;

On WebServer3 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 200,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 200,100;

All the way to WebServer9, run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 800,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 800,100;

You will have to place some PHP header file that unique identifies which machine runs which version of the query.

Mysql – Update one table from another table while sorting that table based on one column

There are two options

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM 
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.