Bug #76999 Foreign key violated when insterting parent and child in the same transaction

• Submitted: 11 May 2015 11:43
• Reporter: gautier berthou
• Status: Open
• Category: MySQL Cluster: Cluster/J
• Severity: S1 (Critical)
• Version: 7.4.4
• OS: Linux (ubuntu 15.04)
• CPU Architecture: Any
• Tags: ClusterJ, foreign key, ndb

[11 May 2015 11:43] gautier berthou

Description:
Hi,

I'm getting "Foreign key constraint violated" errors when inserting a parent row and a children pointing to this parent row within the same transaction.

This happen when running a lot of transactions in parallel or running only one transaction trying to commit several children containing a big quantity of data (ex: 10 children row, each containing a 13000 byte varbinary).

As this happens more easily when running the code on a less powerful machine and as this happens only when the quantity of data committed is big, I suspect that it comes from some buffers on the client side.

How to repeat:
Create two tables as follow:

delimiter $$

CREATE TABLE `master` (
  `masterid` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`masterid`))
ENGINE = ndbcluster DEFAULT CHARSET=latin1
PACK_KEYS = DEFAULT PARTITION BY KEY(masterid)$$

delimiter $$

CREATE TABLE `slave` (
  `masterid` VARCHAR(45) NOT NULL,
  `slaveid` VARCHAR(45) NOT NULL,
  `content` VARBINARY(13000) NOT NULL,
  PRIMARY KEY (`masterid`, `slaveid`),
  INDEX `slaveid` (`slaveid` ASC),
  CONSTRAINT `masterid`
    FOREIGN KEY (`masterid`)
    REFERENCES `master` (`masterid`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY(masterid) $$

run the following client. If you don't obtain any error, it may come from the fact that you have a more powerful machine than me, you can try increasing the nbSlaves values

package foreign_key_bug;

import com.mysql.clusterj.ClusterJHelper;
import com.mysql.clusterj.Session;
import com.mysql.clusterj.SessionFactory;
import com.mysql.clusterj.annotation.PersistenceCapable;
import com.mysql.clusterj.annotation.PrimaryKey;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class ForeignKeyBug {

  public static void main(String[] args) {
    nbSlaves=10
    Properties props = new Properties();
    props.setProperty("com.mysql.clusterj.connectstring", "SERVER");
    props.setProperty("com.mysql.clusterj.database", "DATABASE");

    SessionFactory factory = ClusterJHelper.getSessionFactory(props);

    Session session = factory.getSession();
    session.currentTransaction().begin();

    Master master = session.newInstance(Master.class);
    master.setmasterid("id");
    session.savePersistent(master);

    List<Slave> slaves = new ArrayList<Slave>();

    for (Integer j = 0; j < nbSlaves; j++) {
      Slave slave = session.newInstance(Slave.class);
      slave.setmasterid("id");
      slave.setslaveid(j.toString());
      slave.setcontent(new byte[13000]);
      slaves.add(slave);
    }
    session.savePersistentAll(slaves);

    session.currentTransaction().commit();
    System.out.println("no problem");
  }

  @PersistenceCapable(table = "master")
  public interface Master {

    @PrimaryKey
    String getmasterid();

    void setmasterid(String masterid);
  }

  @PersistenceCapable(table = "slave")
  public interface Slave {

    @PrimaryKey
    String getmasterid();

    void setmasterid(String masterid);

    @PrimaryKey
    String getslaveid();

    void setslaveid(String slaveid);

    byte[] getcontent();

    void setcontent(byte[] content);
  }
}