I am moving a fairly large system over to a new server and new MySQL database.
The system has been running for a few years on MySQL using ODBC data drivers, I am now using MySQL drivers and have started running into a problem.
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
A bit of Googling brought me to this article:
Having read through that a few others including the MySQL docs I have identified the problem to being some text fields in my database which have quite large content.
I traced one instance of the problem to an individual record, amended the data in one of the text fields so it was shorter and it removed the problem.
Having put the content into MS Word, the character count is 30,000, running LENGTH(report) (report is the field name) in a Query on phpMyAdmin (which will happily show all the rows) agrees, it shows a length of 33108
So, this must be where the problem is, but surely I can handle this kind of data?
I'm not sure where the problem is though, is it the new database setup, or the use of the MySQL Drivers?
I am trying to use the data as a DataTable as follows:
Public Shared Function getTable() as DataTable
Dim connection As MySqlConnection = Common.getConnection()
connection.Open()
Dim connect As MySqlCommand = Common.createCommand(sql, connection)
Dim getData As New DataTable()
getData.Load(connect.ExecuteReader(CommandBehavior.CloseConnection))
return getData
End Function
Public Shared Function getConnection() As MySqlConnection
Dim DBConnection As String
DBConnection = ConfigurationManager.ConnectionStrings("dbConn").ConnectionString
Dim oConnection As MySqlConnection = New MySqlConnection(DBConnection)
getConnection = New MySqlConnection(DBConnection)
End Function
Public Shared Function createCommand(sql As String, connection As MySqlConnection) As MySqlCommand
createCommand = New MySqlCommand(sql, connection)
End Function
(Previously I was using OdBcCommand and OdBcConnections and it all worked!)
I am not sure where to go from here, without filtering through all my data, which I don't want to have to do, and removing all long fields I am unsure how to fix this problem?
Any advice much appreciated!
============
UPDATE IN CASE IT HELPS ANYONE ELSE, FROM THE ACCEPTED ANSWER (thanks very much):
changing my code:
Dim getData As New DataTable()
getData.Load(connect.ExecuteReader(CommandBehavior.CloseConnection))
To:
Dim dataset As New DataSet()
Dim getData As New DataTable()
dataset.Tables.Add(getData)
getData.DataSet.EnforceConstraints = False
getData.Load(connect.ExecuteReader(CommandBehavior.CloseConnection))
Removed the issue, now long fields work fine.
Few solutions i found.
Clear the Dataset / DataTable and rebind bindings. Continue reading here.
Either not calling FillSchema or disabling constraint checking on the Datatable. Continue reading here.
Set EnforceConstraints to false and load data after that.
Dim getData As New DataTable()
getData.DataSet.EnforceConstraints = false;
getData.Load(connect.ExecuteReader(CommandBehavior.CloseConnection))
return getData
Continue reading here.