MySQL Bugs: #65065: ConstraintException when filling a datatable

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:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.5.4	OS:	Windows
Assigned to:	Fernando Gonzalez.Sanchez	CPU Architecture:	Any
Tags:	ConstraintException, DataTable, fill

Description:
If I try to populate a DataTable with a query where I select fields with a unique index or constraint not null, I get an error if a constraint is not respected in my resultset.

The problem dos not occur when I execute the same query in Mysql Workbench.

How to repeat:
Make a query with left join to obtain a resultset that can broke one of those constraints.

Call it with an DotNet ExecuteReader and fill a datatable with the Load method you will get a ConstraintException.

Suggested fix:
Do not set constraints on ExecuteReader method when MySqlCommand contains query with join or do not any Constraints in ExecuteReader method.

Please, provide the exact query used and the results of SHOW CREATE TABLE for all tables involved in it.

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

The problem is not repeatable on empty tables.
Can you please provide the data for both tables too?
Thanks.

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.

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

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();

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.