MySQL Forums
Forum List  »  NDB clusters

FOREIGN KEY constraint violation
Posted by: Alessandro Gherardi
Date: April 20, 2016 11:22PM

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");
}
}

Options: ReplyQuote


Subject
Views
Written By
Posted
FOREIGN KEY constraint violation
2530
April 20, 2016 11:22PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.