Bug #65065 ConstraintException when filling a datatable
| Submitted: | 22 Apr 2012 18:17 | Modified: | 28 Jun 2013 2:30 |
|---|---|---|---|
| Reporter: | Marc-André Beaulieu | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S1 (Critical) |
| Version: | 6.5.4 | OS: | Windows |
| Assigned to: | Fernando Gonzalez.Sanchez | CPU Architecture: | Any |
| Tags: | ConstraintException, DataTable, fill | ||
[22 Apr 2012 18:17]
Marc-André Beaulieu
[26 Apr 2012 15:59]
Valeriy Kravchuk
Please, provide the exact query used and the results of SHOW CREATE TABLE for all tables involved in it.
[27 Apr 2012 3:27]
Marc-André Beaulieu
Here are my tables: 'trx', 'CREATE TABLE `trx` ( `id_trx` int(10) unsigned NOT NULL AUTO_INCREMENT, `mnt` decimal(9,2) NOT NULL, `dat_trx` date NOT NULL, `typ_trx` varchar(45) COLLATE utf8_bin DEFAULT NULL, `descr` tinytext COLLATE utf8_bin, `id_camn` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id_trx`), UNIQUE KEY `id_trx_UNIQUE` (`id_trx`), KEY `fk_trx_camn` (`id_camn`), CONSTRAINT `fk_trx_camn` FOREIGN KEY (`id_camn`) REFERENCES `camn` (`id_camn`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin' 'camn', 'CREATE TABLE `camn` ( `id_camn` int(10) unsigned NOT NULL AUTO_INCREMENT, `no` int(4) unsigned NOT NULL, `marq` varchar(45) COLLATE utf8_bin DEFAULT NULL, `modl` varchar(45) COLLATE utf8_bin DEFAULT NULL, `no_serie` varchar(17) COLLATE utf8_bin DEFAULT NULL, `no_plaq` varchar(7) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id_camn`), UNIQUE KEY `id_camn_UNIQUE` (`id_camn`), UNIQUE KEY `no_UNIQUE` (`no`), UNIQUE KEY `no_serie_UNIQUE` (`no_serie`), UNIQUE KEY `no_plaq_UNIQUE` (`no_plaq`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_bin' That is my query : SELECT cam.no_serie, t.mnt FROM trx t LEFT JOIN camn cam USING(id_camn) This is my stacktrace: System.Data.ConstraintException: Impossible d'activer les contraintes. Une ou plusieurs lignes contiennent des valeurs qui violent les contraintes de type non null, unique ou de clé externe. à System.Data.DataTable.EnableConstraints() à System.Data.DataTable.set_EnforceConstraints(Boolean value) à System.Data.DataTable.EndLoadData() à System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) à System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) à System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) à System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) à System.Data.DataTable.Load(IDataReader reader) à ITDEVX.Service.COM.UtilitaireMySQL.CommandHelper.ExecuteDataTable() dans C:\Users\Marc-Andre\Documents\Visual Studio 2010\Projects\ITDEVX.Service.COM.UtilitaireMySQL\CommandHelper.vb:ligne 613
[11 May 2012 8:23]
Bogdan Degtyariov
The problem is not repeatable on empty tables. Can you please provide the data for both tables too? Thanks.
[16 May 2012 0:40]
Marc-André Beaulieu
Here are my inserts: INSERT INTO camn(id_camn, no, marq, modl, no_serie, no_plaq) VALUES(9, 3327, null, null, null, null); INSERT INTO trx(id_trx, mnt, dat_trx, typ_trx, descr, id_camn) VALUES(1, 10, '2012-04-30', null, null, 9); INSERT INTO trx(id_trx, mnt, dat_trx, typ_trx, descr, id_camn) VALUES(2, 10, '2012-04-15', null, null, 9); INSERT INTO trx(id_trx, mnt, dat_trx, typ_trx, descr, id_camn) VALUES(3, 10, '2012-04-15', null, null, null); Note that the problem doesn't appear on empty tables.
[21 May 2012 12:21]
Bogdan Degtyariov
Verified.
Fails with the following error:
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. at System.Data.DataTable.EnableConstraints()\r\n at System.Data.DataTable.set_EnforceConstraints(Boolean value)\r\n at System.Data.DataTable.EndLoadData()\r\n at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)\r\n at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)\r\n at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)\r\n at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)\r\n at System.Data.DataTable.Load(IDataReader reader)\r\n at _issues.Form1.b65065_Click(Object sender, EventArgs e)
----------------------------------------------------------------------------
private void b65065_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
try
{
con.ConnectionString = "server=localhost;database=test;" +
"user id=*********;Password=*********;";
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = con;
cmd.CommandText = "DROP TABLE IF EXISTS trx";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP TABLE IF EXISTS camn";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE `camn` (" +
"`id_camn` int(10) unsigned NOT NULL AUTO_INCREMENT," +
"`no` int(4) unsigned NOT NULL," +
"`marq` varchar(45) COLLATE utf8_bin DEFAULT NULL," +
"`modl` varchar(45) COLLATE utf8_bin DEFAULT NULL," +
"`no_serie` varchar(17) COLLATE utf8_bin DEFAULT NULL," +
"`no_plaq` varchar(7) COLLATE utf8_bin DEFAULT NULL," +
"PRIMARY KEY (`id_camn`)," +
"UNIQUE KEY `id_camn_UNIQUE` (`id_camn`)," +
"UNIQUE KEY `no_UNIQUE` (`no`)," +
"UNIQUE KEY `no_serie_UNIQUE` (`no_serie`)," +
"UNIQUE KEY `no_plaq_UNIQUE` (`no_plaq`)" +
") ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_bin";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE `trx` (" +
"`id_trx` int(10) unsigned NOT NULL AUTO_INCREMENT," +
"`mnt` decimal(9,2) NOT NULL," +
"`dat_trx` date NOT NULL," +
"`typ_trx` varchar(45) COLLATE utf8_bin DEFAULT NULL," +
"`descr` tinytext COLLATE utf8_bin," +
"`id_camn` int(10) unsigned DEFAULT NULL," +
"PRIMARY KEY (`id_trx`)," +
"UNIQUE KEY `id_trx_UNIQUE` (`id_trx`)," +
"KEY `fk_trx_camn` (`id_camn`)," +
"CONSTRAINT `fk_trx_camn` FOREIGN KEY (`id_camn`) REFERENCES `camn` (`id_camn`) ON DELETE NO ACTION ON UPDATE NO ACTION" +
") ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO camn(id_camn, no, marq, modl, no_serie, no_plaq) VALUES(9, 3327, null, null, null, null);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO trx(id_trx, mnt, dat_trx, typ_trx, descr, id_camn) VALUES(1, 10, '2012-04-30', null, null, 9);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO trx(id_trx, mnt, dat_trx, typ_trx, descr, id_camn) VALUES(2, 10, '2012-04-15', null, null, 9);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO trx(id_trx, mnt, dat_trx, typ_trx, descr, id_camn) VALUES(3, 10, '2012-04-15', null, null, null);";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT cam.no_serie, t.mnt FROM trx t LEFT JOIN camn cam USING(id_camn) ";
MySqlDataReader dr = cmd.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(dr);
dr.Close();
MessageBox.Show("Success!");
}
catch (Exception ex)
{
string error = ex.Message.ToString();
error += ex.StackTrace;
MessageBox.Show("=============================================\r\n" +
"Error!\r\n" + error + "\r\n" +
"=============================================\r\n");
}
finally
{
con.Close();
}
}
[11 Oct 2012 4:06]
Fernando Gonzalez.Sanchez
We are working on this, lowering the priority because there are two workarounds:
a) Return a query that includes a unique value
b) Use a DataSet to populate the DataTable, to use EnforceConstraints like this:
cmd.CommandText = "SELECT cam.no_serie, t.mnt FROM trx t LEFT JOIN camn cam USING(id_camn) ";
MySqlDataReader dr = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
ds.Tables.Add(dataTable);
ds.EnforceConstraints = false;
dataTable.Load(dr);
dr.Close();
[28 Jun 2013 2:30]
Philip Olson
Fixed as of the upcoming Connector/Net 6.5.7, 6.6.6, and 6.7.4 releases, and here's the changelog entry: An exception was thrown when populating "DataTable" with query fields containing a "UNIQUE" index or constraint "NULL". There is no longer an exception thrown, and the "DataTable.Fill" method terminates correctly (filling the data). Thank you for the bug report.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.057 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
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.