What steps will reproduce the problem? 1. Create 2 Migrations

Migration001:

    public override void Up()
    {
        TableSchema.Table test1 = CreateTableWithKey("table1");
        TableSchema.Table test2 = CreateTableWithKey("table2");

        test2.AddColumn("test1_id", System.Data.DbType.Int32, 45, false);

        base.Up();
    }

Migration002:

    public override void Up()
    {
        TableSchema.Table table1 = GetTable("table1");
        TableSchema.Table table2 = GetTable("table2");

        CreateForeignKey(table1.GetColumn("id"),

table2.GetColumn("table1_id"));

        base.Up();
    }

2. 3.

What is the expected output? What do you see instead?

The first Step should create tow tables in the DB, the second step should add a foreign key relationship between both. But that fails, because GetTable() return null.

What version of the product are you using? On what operating system? - SubSonic 2.2 r523

What's the problem? First of all, for MySQL/InnoDB I can't add this in one step, because of a limitation in MySQL (Create a table and use it for a fk relationship in one transaction trows an InnoDB engine Error)

The problem is, that the MySQLInnoDBDataProvider stores the Informationschema in a DataTable. However if I add a table to my DB the DataTable is not updated and if I want to access the table, the Method GetTableSchema(tableName, tableType) return null.

In my eyes this is a hevily problem of migrations, because an error can occur if you don't expect one. Let me explain:

You have migration 1-6. In migration3 you create a table "table1". And in migration6 you fetch the table with GetTable("table1") and add a fk relationship. Now you have a customer with migration level 4 and you run an update. Everything works as expected ("table1" exists when InformationSchema is queried) Now you have a customer with migration level 3. If the InformationSchema is queried, you don't have a table "table1". Step 3, 4 and 5 of the migration will work, but migration 6 will fail, because GetTable("table1") returns null and obj.GetColumn("col1") causes a NullReferenceException.

I provided a patch, that, in case a table is not in the cache, resets the cache and calls GetTableSchema again recursively, to be sure we can trust the result. I also added a improved logic to update the MySQL information schema table with FLUSH TABLES that should make my patch (see issue #87 - http://code.google.com/p/subsonicproject/issues/detail?id=87) obsolete.

Patch added