DbMetal exception when mapping MySQL database: The given key was not present in the dictionary

[GoogleCodeExporter]

What steps will reproduce the problem?
1. restore attached DB into MySQL server
2. run command: DbMetal.exe -provider=MySql -database:monotest -
server:www.xxx.zz -user:root -password:mypass -namespace:MonoLinqx -
code:filenamex.cs -sprocs

What is the expected output? 
Map DB into classes.

What do you see instead?
DbMetal failed:System.Collections.Generic.KeyNotFoundException: The given 
key was not present in the dictionary.

What version of the product are you using? On what operating system?
- compiled r1285 from Nov 30, Windows 7

Please provide any additional information below.

Command:
DbMetal.exe -provider=MySql -database:monotest -server:www.xxx.zz -
user:root -password:mypass -namespace:MonoLinqx -code:filenamex.cs -sprocs

Output:
DbLinq Database mapping generator 2008 version 0.19
for Microsoft (R) .NET Framework version 3.5
Distributed under the MIT licence (http://linq.to/db/license)

>>> Reading schema from MySQL database
DbMetal failed:System.Collections.Generic.KeyNotFoundException: The given 
key was not present in the dictionary.
   at System.ThrowHelper.ThrowKeyNotFoundException()
   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at DbLinq.Vendor.Implementation.SchemaLoader.LoadForeignKey(Database 
schema,
Table table, String columnName, String tableName, String tableSchema, 
String ref
erencedColumnName, String referencedTableName, String 
referencedTableSchema, Str
ing constraintName, NameFormat nameFormat, Names names)
   at DbLinq.MySql.MySqlSchemaLoader.LoadConstraints(Database schema, 
SchemaName
 schemaName, IDbConnection conn, NameFormat nameFormat, Names names)
   at DbLinq.Vendor.Implementation.SchemaLoader.Load(String databaseName, 
INameA
liases nameAliases, NameFormat nameFormat, Boolean loadStoredProcedures, 
String
contextNamespace, String entityNamespace)
   at DbMetal.Generator.Implementation.Processor.ReadSchema(Parameters 
parameter
s, ISchemaLoader& schemaLoader)
   at DbMetal.Generator.Implementation.Processor.ProcessSchema(Parameters 
parame
ters)

Don't know but maybe it's related with this:
http://forums.mysql.com/read.php?38,247128,247128

Original issue reported on code.google.com by bosak.to...@gmail.com on 3 Dec 2009 at 11:09

Attachments:

• [Monotest 20091203 1153.sql](https://storage.googleapis.com/google-code-attachments/dblinq2007/issue-167/comment-0/Monotest 20091203 1153.sql)

[GoogleCodeExporter]

Issue dissapeared after recreation of foreign keys in database.

Original comment by bosak.to...@gmail.com on 3 Dec 2009 at 12:46

[GoogleCodeExporter]

Closed as there doesn't appear to be an issue (as per the previous comment).

Original comment by jonmpr...@gmail.com on 3 Dec 2009 at 5:18

• Changed state: Invalid

[GoogleCodeExporter]

Hi,

I stumbled in this issue too.

It happens when you create your FK constraints named with uppercase chars 
(e.g.: 
FK_Foo_Bar instead of fk_foo_bar). 

Alessandro Pilotti [ MVP / IIS ]

P.S.: Thanks for another excellent project! :-)

Original comment by alexv...@gmail.com on 24 Mar 2010 at 11:34

[GoogleCodeExporter]

Can you elaborate?  (Without line numbers it's hard to track things down.)

There are two ways I can see this happening.

1. There's some bizarro DbLinq requirement that FK constraint names always be 
lower 
case. (I can't rule this out as DbLinq has lots of bizarro code.)

2. The SQL Schema itself isn't case-sensitive-safe.  For example:

    CREATE TABLE A (ID INTEGER NOT NULL);
    CREATE TABLE B (OtherID INTEGER NULL,
        CONSTRAINT "FK_B_OtherID" FOREIGN KEY ([otherid]) REFERENCES [a] ([id]);

In a case-insensitive environment, that should be fine; in a case sensitive 
environment, that should blow up rather quickly (as "FK_B_OtherID" uses the 
wrong 
case for the FK source column ([otherid]), FK target table ([a]), abd FK target 
column ([id]).

This is where line numbers would be useful, to distinguish between case (1) 
(DbLinq 
bug) and (2) (Schema isn't self-consistent in a case-sensitive environment, in 
which 
case DbLinq likely needs to e.g. always uppercase identifiers to ensure 
consistency).

Thanks!

Original comment by jonmpr...@gmail.com on 25 Mar 2010 at 4:22

• Changed state: StandBy

[GoogleCodeExporter]

I recently ran into this myself. In the process I discovered some weirdness 
with mysql: 

CREATE  TABLE IF NOT EXISTS `wgd`.`SiteUser` (
  `idSiteUser` INT NOT NULL AUTO_INCREMENT ,
  `FirstName` VARCHAR(45) NULL ,
  `LastName` VARCHAR(45) NULL ,
  `Nickname` VARCHAR(45) NULL ,
  `EmailAddress` VARCHAR(256) NOT NULL ,
  PRIMARY KEY (`idSiteUser`) )
ENGINE = InnoDB;

CREATE UNIQUE INDEX `id_UNIQUE` ON `wgd`.`SiteUser` (`idSiteUser` ASC) ;

CREATE UNIQUE INDEX `email_address_UNIQUE` ON `wgd`.`SiteUser` (`EmailAddress` 
ASC) ;

actually creates a table called "siteuser" but all of the foreign keys refer to 
SiteUser.  So when dbmetal calls "BuildForeignKey" to get the reverse foreign 
key the lookup in names.ColumnsNames[referencedTableName] fails.

Original comment by donavan....@gmail.com on 25 Feb 2012 at 6:42

[GoogleCodeExporter]

@donovan, you may be using MySQL on Windows. In this case there is a special 
option lower_case_table_names that is applied, forcing all your table names to 
go lowercase. This is a very bad behavior of MySQL on Windows. You should 
disable it by setting the option to 0 (zero). This way your tables will be 
created with the original names. 
http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html

I also encounter the issue with the very same exception. I have FKs with upper 
case chars. When converting all FK names to lower case: it FAILS.

Original comment by Antoine....@gmail.com on 25 Jan 2013 at 9:09

[GoogleCodeExporter]

I'm debugging the app. MySQL is a very bad guy. In DbLinq, there's a 
MySqlSchemaLoader.ReadConstraints method that executes a SQL query against the 
info_schema. 

            string sql = @"
SELECT constraint_name,table_schema,table_name
    ,GROUP_CONCAT(column_name SEPARATOR ',') AS column_name,referenced_table_schema,referenced_table_name,GROUP_CONCAT(referenced_column_name SEPARATOR ',') AS referenced_column_name
FROM information_schema.`KEY_COLUMN_USAGE`
WHERE table_schema='gwtimers' GROUP BY 
constraint_name,table_schema,table_name,referenced_table_name";

MySQL returns WRONG data. The referenced_table_name columns contains the 
lowercased table name instead of the original table name. If I fix the query to 
subquery the correct table name, it works.

            string sql = @"
SELECT constraint_name,table_schema,table_name,GROUP_CONCAT(column_name 
SEPARATOR ',') AS column_name,referenced_table_schema, (SELECT TABLE_NAME FROM 
information_schema.TABLES WHERE TABLE_NAME LIKE referenced_table_name AND 
TABLE_SCHEMA=referenced_table_schema) AS 
referenced_table_name,GROUP_CONCAT(referenced_column_name SEPARATOR ',') AS 
referenced_column_name
FROM information_schema.`KEY_COLUMN_USAGE`
WHERE table_schema=?db
GROUP BY constraint_name,table_schema,table_name,referenced_table_name";

Now dblinq executes correctly.
Nice work guys for building dblinq. 
MySQL has very strange behaviors.

Original comment by Antoine....@gmail.com on 25 Jan 2013 at 9:56

Attachments:

• mysql-fails.png

[GoogleCodeExporter]

This is how I made this work for MySQL:

File: src/DbLinq.MySql/MySqlSchemaLoader.Constraints.cs

Line :75

The change needs to be done to the sql query sent to mySQL:

ON columns referenced_table_schema, referenced_table_name and 
referenced_column_name mySQL returns NULL for the PRIMARY keys. This is what 
breaks the process and returns the 'given key was not present' error. So we 
need to put IFNULL(column_name,'') in order for the query to work!

Since we handle NULL values with the IFNULL, it should now play nicely!

Original comment by gsovat...@gmail.com on 3 Jul 2014 at 3:39