Got error 4239 'Trigger with given name already exists' from NDBCLUSTER

Question

We are running a MySQL Cluster Version:

mysql> SELECT VERSION();
+------------------------------+
| VERSION()                    |
+------------------------------+
| 5.6.15-ndb-7.3.4-cluster-gpl |
+------------------------------+

Trying to create a table

CREATE TABLE xy (
  xa VARCHAR(36) NOT NULL DEFAULT '',
  xb VARCHAR(255) NOT NULL,
  xc TIMESTAMP NOT NULL,
  xd VARCHAR(36) DEFAULT NULL,
  xe VARCHAR(36) DEFAULT NULL,
  xf VARCHAR(255) DEFAULT NULL,
  xg VARCHAR(255) DEFAULT NULL,
  xh TEXT,
  xi BIGINT(20) DEFAULT NULL,
  xj VARCHAR(255) DEFAULT NULL,
  xk VARCHAR(255) DEFAULT NULL,
  xl VARCHAR(255) DEFAULT NULL,
  xz VARCHAR(255) DEFAULT NULL,
  xy VARCHAR(255) DEFAULT NULL, 
  PRIMARY KEY (xa)
) engine=ndb;

brings me (using the direct input over command line):

ERROR 1296 (HY000): Got error 4239 'Trigger with given name already exists' from NDBCLUSTER

and via file:

ERROR 1296 (HY000) at line 8: Got error 4239 'Trigger with given name already exists' from NDBCLUSTER

But there are no mysql triggers:

mysql> SHOW triggers;
Empty set (0.00 sec)

and no tables:

mysql> show tables;
Empty set (0.01 sec)

Anyone got an idea?

Did you try to change the name of the table ? I mean, "xy" may be already used by something in MySQL. — Kabulan0lak, Jun 16 '14 at 11:11
Are you sure you don't mix `CREATE TABLE` and `CREATE TRIGGER` statements? — vhu, Jun 16 '14 at 11:13
@Kabulan0lak: I added the output of show tables to the initial post. The name i am using is definitely not reserved by mysql (the create table statements are working on other nodes as expected). — gies0r, Jun 16 '14 at 11:16
@vhu: Yep i am definitely sure that i am not mixing it up. The error message is thrown at the create table statement. — gies0r, Jun 16 '14 at 11:16
Are you sure you are executing the good file ? It is really weird I've never seen an error like that before. — Kabulan0lak, Jun 16 '14 at 11:20
@Kabulan0lak: I tried to create the table by file and by an direct input using the command line. Always the same error message. Using the file import gives me back a row (line 8) — gies0r, Jun 16 '14 at 11:31
@AlvinThompson: Is there an easy way to validate that row based replication is used? "MySQL Cluster. The default binary logging format in all MySQL Cluster NDB 6.x and 7.x releases is ROW. MySQL Cluster Replication always uses row-based replication, and the NDBCLUSTER storage engine is incompatible with statement-based replication. Using NDBCLUSTER sets row-based logging format automatically." [MySQL replecation formats](http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html) — gies0r, Jun 16 '14 at 11:35
@gies0r: so you have to replicate and update your triggers on all the nodes? ewww... — Alvin Thompson, Jun 16 '14 at 11:56
@AlvinThompson: Normally the create table statement just need to be executed on one node and will be replicated afterwards. But anyway - I would execute the create table statements on all nodes, but it fails already on the first node. We already implemented the table creation on a local environment with 2 nodes and 1 daemon.. So the statement by itself should be correct. — gies0r, Jun 16 '14 at 12:40
I don't know enough about MySQL replication to make a real guess, but I imagine the replication is actually done by triggers. The first thing I would check is if a table by that name previously existed. If so, maybe when it was deleted somehow its triggers didn't get deleted. Now, when trying to create a table with the same name, the DB automatically tries to create replication triggers for it, and the trigger name generated is based on the table name, hence the conflict. These triggers wouldn't show up with `show triggers` because they're not user-created triggers. — Alvin Thompson, Jun 16 '14 at 12:56
However, I would guess any such triggers would have to be in the MySQL system tables somewhere. Maybe this theory will point you/someone in the right direction. — Alvin Thompson, Jun 16 '14 at 12:57

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Ok - We got it!

The MaxNoOfTriggers in config.ini has been reached.

From the Official documentation -> MaxNoOfTriggers:

Internal update, insert, and delete triggers are allocated for each unique hash index. (This means that three triggers are created for each unique hash index.) However, an ordered index requires only a single trigger object. Backups also use three trigger objects for each normal table in the cluster.

Replication between clusters also makes use of internal triggers.

This parameter sets the maximum number of trigger objects in the cluster.

The default value is 768.

Got error 4239 'Trigger with given name already exists' from NDBCLUSTER

1 Answers1