MySQL Bugs: #35217: Strange ndb_binlog_index error inserting rows in MyISAM table

Bug #35217	Strange ndb_binlog_index error inserting rows in MyISAM table
Submitted:	11 Mar 2008 15:48	Modified:	10 Nov 12:28
Reporter:	Emanuele Guarneri
Status:	Need Feedback
Category:	Server: ClusterRep	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Linux (RedHat)
Assigned to:	Frazer Clement	Target Version:
Tags:	5.1.22-ndb-6.3.6, mysqld binlog writing row ndb_binlog_index
Triage:	Triaged: D3 (Medium) / R6 (Needs Assessment) / E6 (Needs Assessment)

Description:
I've build a REPLICATED environment with two clusters and a bunch of mysqld each.
There's a master-slave architecture .

When I insert a lot of rows (using a script) in a table (MyISAM) using a script (so VERY
FAST WAY) connected to the MASTER,    I see a lot of errors in the log (SLAVE side) of
mysqld that acts as a slave.

080311  8:16:07 [Note] NDB Binlog: logging ./all_tog/lemon (UPDATED,USE_WRITE)

080311  8:17:27 [ERROR] NDB Binlog: Writing row to ndb_binlog_index: 121

080311  8:17:27 [ERROR] NDB Binlog: Writing row to ndb_binlog_index: 121

080311  8:17:28 [ERROR] NDB Binlog: Writing row to ndb_binlog_index: 121

The rows are inserted correctly.

But I don't know why I got these errors in my log.

How to repeat:
Run again my script.

Suggested fix:
Do not run in a FAST WAY.

I've tried to put "sleep 2/3" to slow my script.

And it works !!!!
So it's something concerned the speed of writing rows.

Could it be ??

Thank you for the report.

Could you please provide your script which shows the problem?

Script that insert any rows (very fast, of course) in a test-table

Attachment: seed_gen.sh (application/octet-stream, text), 478 bytes.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Another observation:

we see those 121 errors for inserting into ndb_binlog_index on a cluster with two API
nodes writing binlog. Both API nodes get the same errors at the same time. This must be
something systematic and may be related to the actual epoch numbers.

Also receiving this error and willing to work with dev team to find resolution.

[ERROR] NDB Binlog: Writing row to ndb_binlog_index: 121

2 datanodes; 2a pi; 4 slaves

In our setup it seems also to be related with REPLACE commands; or at least;

081119 14:48:20 [ERROR] Slave SQL: Could not execute Write_rows event on table db.table;
Duplicate entry '12253' for key 'PRIMARY', Error_code: 1
062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log
daisycon-sql02-bin.000128, end_log_pos 4341916, Error_code: 1062
081119 14:48:20 [Warning] Slave: Duplicate entry '12253' for key 'PRIMARY' Error_code:
1062
081119 14:48:20 [ERROR] Error running query, slave SQL thread aborted. Fix the problem,
and restart the slave SQL thread with "SLAVE START". We stopped at log
 'daisycon-sql02-bin.000128' position 4340627

Funny a duplicate error with a REPLACE command ?

Same problem on 5.1.29-ndb-6.3.19-telco here!

Regards,
Robert

(repost)
Workaround, preventing the flooding:

Something that worked fine in one case, is to remove the primary key from the
mysql.ndb_binlog_index table (which is MyISAM btw).
If you do this, you should consider that this table can become big, and if you use
ndb_binlog_index to lookup things, it will be dead slow.

So, for those that do not do (geographic) replication of any NDB table, but still have a
Cluster attached in their Master/Slave(s) setups:

 mysql> USE mysql;
 mysql> SET SQL_LOG_BIN = 0;
 mysql> TRUNCATE ndb_binlog_index;
 mysql> ALTER TABLE ndb_binlog_index DROP PRIMARY KEY;

It should prevent the flooding of the logs. Note that binary logging is disabled for that
session! Just in case so it doesn't break MySQL servers which don't have the
mysql.ndb_binlog_index table.

BTW, truncating this table from time to time is also good.

The current code in 6.2.19+, 6.3.22+, 7.0.2+ branches includes more debugging output in
this error scenario.  If an insert into the ndb_binlog_index table fails, the failing
primary key will be output.

None of the logs in the attached issues contain this debugging output, so it's not clear
that this problem has been reproduced recently.

A reproduction with the debugging output should help clarify whether the problem is due
to coding error (a bug), or procedural error in the management of the ndb_binlog_index
table.