FOREIGN KEY constraint violation

[Alessandro Gherardi]

Hi,
While trying to upload a large number (9+ million) records from a JSON file into a couple of NDB tables using a custom-built Java/JDBC application, I encountered a few (4 or 5) FOREIGN KEY constraint violation errors.

The first table is called EVENTS, the second is called EVENT_PROPERTIES. The only foreign key is the EVENT_PROPERTIES.EVENTID column referring to EVENTS.ID. The constraint violation occurred when inserting records into EVENT_PROPERTIES.

The weird thing is that my program first inserts the EVENT record, then it inserts the one or more associated EVENT_PROPERTIES. Also, when I resumed the upload from the line in the JSON file at which the failure occurred, the INSERT worked - so I was eventually able to upload all records. In other words, it seems like the FOREIGN KEY constraint violation's were only temporary.

I copied the application's code - simplified - below.

Any suggestions?

Thank you in advance.


import java.io.*;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Map;
import java.util.TimeZone;
import java.util.UUID;

import javax.xml.bind.DatatypeConverter;

import com.fasterxml.jackson.core.type.TypeReference;

public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://192.168.20.22/test";

// Database credentials
static final String USER = "...";
static final String PASS = "...";

...

@SuppressWarnings("unchecked")
public static void main(String[] args) {
String fileName = args[0];
int toSkip = Integer.parseInt(args[1]);

Connection conn = null;
PreparedStatement stmt = null;
PreparedStatement propStmt = null;

try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");

System.out.println("Prearing statement...");
String sql = "INSERT INTO events VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
stmt = conn.prepareStatement(sql);

sql = "INSERT INTO event_properties VALUES (?,?,?)";
propStmt = conn.prepareStatement(sql);
System.out.println("Statement prepared...");

BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(fileName)));
int count = 0;
while (true) {
String json = br.readLine();
if (json == null) {
break;
}
if (count < toSkip) {
count++;
continue;
}

String id = ...;
Timestamp eventTime = ...;
String situationType = ...;
String situationId = ...;
String ackState = ...;
Timestamp ackTime = ...;
String ackUser = ...;
String sourceDeviceId = ...;
String generationDeviceId = ...;
String sourceUserName = ...;
int severity = ...;
long etag = ...;
Integer wakeupInSec = ...;
Timestamp modifiedTime = ...;
String siteId = ...;
Boolean audibleNotify = ...;
Boolean bigNotify = ...;
boolean notify = ...;
Map<String, Object> props = ...;

stmt.setString(1, id);
stmt.setTimestamp(2, eventTime);
stmt.setString(3, situationType);
stmt.setString(4, situationId);
stmt.setString(5, ackState);

if (ackTime != null) {
stmt.setTimestamp(6, ackTime);
} else {
stmt.setNull(6, java.sql.Types.TIMESTAMP);
}

if (ackUser != null) {
stmt.setString(7, ackUser);
} else {
stmt.setNull(7, java.sql.Types.VARCHAR);
}

if (sourceDeviceId != null) {
stmt.setString(8, sourceDeviceId);
} else {
stmt.setNull(8, java.sql.Types.VARCHAR);
}

if (sourceUserName != null) {
stmt.setString(9, sourceUserName);
} else {
stmt.setNull(9, java.sql.Types.VARCHAR);
}

if (wakeupInSec != null) {
stmt.setInt(10, wakeupInSec);
} else {
stmt.setNull(10, java.sql.Types.INTEGER);
}

stmt.setInt(11, severity);

if (generationDeviceId != null) {
stmt.setNull(12, java.sql.Types.VARCHAR);
} else {
stmt.setString(12, generationDeviceId);
}

if (audibleNotify != null) {
stmt.setBoolean(13, audibleNotify);
} else {
stmt.setNull(13, java.sql.Types.BOOLEAN);
}

stmt.setBoolean(14, notify);

if (siteId != null) {
stmt.setString(15, siteId);
} else {
stmt.setNull(15, java.sql.Types.VARCHAR);
}

stmt.setTimestamp(16, modifiedTime);
stmt.setLong(17, etag);

stmt.executeUpdate();

for (Map.Entry<String, Object> entry : props.entrySet()) {
String propName = entry.getKey();
String propValue = entry.getValue().toString();
propStmt.setString(1, propName);
propStmt.setString(2, propValue);
propStmt.setString(3, id);
propStmt.executeUpdate();
}

count++;
System.out.println("Inserted record " + count);
}

} catch (Exception e) {
e.printStackTrace();
} finally {

try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException se) {
}

try {
if (propStmt != null) {
propStmt.close();
}
} catch (SQLException se) {
}

try {
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
se.printStackTrace();
}
}

System.out.println("Done");
}
}