GetSchema not returning expected columns

GetSchema not returning expected columns

Postby MNorgren » Wed 10 Feb 2016 18:02

Code: Select all
mySqlConnection.GetSchema("Tables");
is not returning what I (or NHibernate) expects

What determines what comes back in DataTable.Columns? NHibernate expects to find the table name via a column named "TABLE_NAME", but the columns I get back are

Code: Select all
+      [0]   {Database}   object {System.Data.DataColumn}
+      [1]   {Name}   object {System.Data.DataColumn}
+      [2]   {Type}   object {System.Data.DataColumn}
+      [3]   {Created}   object {System.Data.DataColumn}
+      [4]   {Modified}   object {System.Data.DataColumn}
+      [5]   {AutoIncrementSeed}   object {System.Data.DataColumn}
+      [6]   {RowFormat}   object {System.Data.DataColumn}
+      [7]   {Rows}   object {System.Data.DataColumn}
+      [8]   {AvgRowLength}   object {System.Data.DataColumn}
+      [9]   {DataLength}   object {System.Data.DataColumn}
+      [10]   {MaxDataLength}   object {System.Data.DataColumn}
+      [11]   {IndexLength}   object {System.Data.DataColumn}
+      [12]   {DataFree}   object {System.Data.DataColumn}
+      [13]   {CheckType}   object {System.Data.DataColumn}
+      [14]   {CreateOptions}   object {System.Data.DataColumn}
+      [15]   {Comment}   object {System.Data.DataColumn}
+      [16]   {Collation}   object {System.Data.DataColumn}


Connection string currently looks like -
"User Id=<id>;Host=localhost;Port=3306;Database=OurCompaniesData;Direct=True;Pooling=False;Character Set=utf8;"

The only SQL command I see get run is
SHOW TABLE STATUS FROM mysql LIKE '%'
which gives roughly those column headings

Can I fix this to get back what NHibernate expects?
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE ... etc etc

see
https://github.com/nhibernate/nhibernate-core/blob/2b6c39811d893f649ca6a1e901bc565a9a762cc8/src/NHibernate/Dialect/Schema/MySQLMetaData.cs
MNorgren
 
Posts: 5
Joined: Wed 10 Feb 2016 15:41

Re: GetSchema not returning expected columns

Postby Shalex » Fri 12 Feb 2016 13:18

Thank you for your report. We will notify you when the issue is fixed for Devart.Data.MySql.NHibernate.NHibernateMySqlConnection.
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44

Re: GetSchema not returning expected columns

Postby Shalex » Thu 18 Feb 2016 14:07

The bug is fixed. We will notify you when the corresponding public build of dotConnect for MySQL is available for download.
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44

Re: GetSchema not returning expected columns

Postby MNorgren » Thu 18 Feb 2016 18:03

Great! What changes can I expect?

Our bottom line (as seen in the test program) is that we need nHibernate GenerateSchemaUpdateScript() to work. This relies on calling GetSchema with Tables, Columns, Foreign Keys, Indexes and IndexColumns as the "Collection Name" parameter. GetSchema can be called with many other "Collection Name" inputs than that though and I'm interested in the scope of the changes here.
MNorgren
 
Posts: 5
Joined: Wed 10 Feb 2016 15:41

Re: GetSchema not returning expected columns

Postby Shalex » Thu 18 Feb 2016 18:34

The name of the fix in a public build will be "The bug with metadata returned by NHibernateMySqlConnection is fixed".

With NHibernate, please use Devart.Data.MySql.NHibernate.NHibernateMySqlConnection instead of Devart.Data.MySql.MySqlConnection:
Code: Select all
var assembly = Assembly.Load("Devart.Data.MySql, Version=8.4.597.0, Culture=neutral, PublicKeyToken=09af7300eec23701");
var type = assembly.GetType("Devart.Data.MySql.NHibernate.NHibernateMySqlConnection");
var conn = (DbConnection)Activator.CreateInstance(type);
conn.ConnectionString = "...";
conn.Open();
var myNHResult = conn.GetSchema("Tables");

Devart.Data.MySql.NHibernate.NHibernateMySqlConnection is internal because NHibernate uses it only via reflection.

There is an example of creating a custom NHibernate driver: http://blog.devart.com/nhibernate_and_oracle.html.
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44

Re: GetSchema not returning expected columns

Postby MNorgren » Thu 18 Feb 2016 20:47

Thanks for the help so far, I can successfully use the NHibernateMySqlConnection but we have more problems. I'll modify the test program and send it again to show what I mean.

Our goal is to use nHibernate's GenerateSchemaUpdateScript() to update our database from release to release. It doesn't crash anymore thanks to the changes so far, but it still can't find existing tables. The result is that nHibernate tries to re-add every table in the database when you generate and execute an update script. I think the issue will be with how nHibernate calls GetTables... its like this

Code: Select all
public virtual DataTable GetTables(string catalog, string schemaPattern, string tableNamePattern, string[] types)
{
   var restrictions = new[] { catalog, schemaPattern, tableNamePattern };
   return connection.GetSchema("Tables", restrictions);
}


dotConnect expects the first restriction to be database name and the second to be table name. So I think stuff still doesn't line up and we restrict out all the answers. Like I said, I'll submit a modified program to show update failing.
MNorgren
 
Posts: 5
Joined: Wed 10 Feb 2016 15:41

Re: GetSchema not returning expected columns

Postby Shalex » Fri 19 Feb 2016 15:37

We have just sent you an updated build with the fix.
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44

Re: GetSchema not returning expected columns

Postby MNorgren » Mon 22 Feb 2016 21:10

Thanks for the quick turnaround. We don't want to have to set the schema for every entity. It breaks our product (we use two different databases) and our integration tests (which use the same entities in test databases).

According to this -
https://github.com/jagregory/fluent-nhibernate/wiki/Using-multiple-or-different-schemas
That capability is usually used to map in tables from other databases, not to force you to declare the schema for every entity. Schema should be declared as part of the database configuration.
MNorgren
 
Posts: 5
Joined: Wed 10 Feb 2016 15:41

Re: GetSchema not returning expected columns

Postby Shalex » Wed 24 Feb 2016 08:22

We have just sent you an updated build with the fix. Please try it and notify us about the result.
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44

Re: GetSchema not returning expected columns

Postby Shalex » Thu 25 Feb 2016 16:59

The new build of dotConnect for MySQL 8.4.602 is available for download: http://forums.devart.com/viewtopic.php?f=2&t=33272.
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44

Re: GetSchema not returning expected columns

Postby MNorgren » Fri 26 Feb 2016 14:04

I think 8.4.602 resolves our issues.

Thanks!
MNorgren
 
Posts: 5
Joined: Wed 10 Feb 2016 15:41


Return to dotConnect for MySQL