Entity Framework Code First Migrations and Firebird

Question

I'm trying to enable migrations on a Firebird 2.5 database.

I'm using VS2015 and the ADO Driver and Entity Provider are installed and working correctly.

I reverse engineered the database, made the necessary changes to make it work.

I can do the enable-migrations on the Package Manager Console, and add a migration.

When I do update-database this happens:

PM> update-database 
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [201705301505265_Initial].
Applying explicit migration: 201705301505265_Initial.

ScriptHalted

If I do a update-database -Verbose I get this:

PM> update-database -Verbose
Using StartUp project 'Visao.Web'.
Using NuGet project 'Visao.Data'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'D:\Upwork\Fernando\SIGECOM.FDB' (DataSource: localhost, Provider: FirebirdSql.Data.FirebirdClient, Origin: Configuration).
Applying explicit migrations: [201705301505265_Initial].
Applying explicit migration: 201705301505265_Initial.
INSERT INTO "__MigrationHistory"("MigrationId", "ContextKey", "Model", "ProductVersion")
VALUES (CAST(_UTF8'201705301505265_Initial' AS VARCHAR(150)), CAST(_UTF8'Visao.Data.Migrations.Configuration' AS VARCHAR(300)), x'1F8B0800000000000400ECBDDD92DC3

and a lot of character because its trying to add a blob to the database.

The __MigrationHistory database was added to the FDB file:

So I guess it's kind of working.

I have no idea what to do next, anyone?

EDIT

Since everything seems to be created, I think the error might be from the Insert on the Migration Table. Maybe a bug?

EDIT 2

I created an SQL script with Update-Database -Script and tried to run on IBExplorer and get the following error:

can't format message 13:896 -- message file C:\WINDOWS\firebird.msg not found.
Dynamic SQL Error.
SQL error code = -104.
Unexpected end of command - line 1, column 226.

Even running just the Insert statement is not working.

EDIT 3

Create an issue in their tracker to see if someone else knows

EDIT 4

It seems that the guys responsible for the Bug Tracker page keeps removing the bug.

Anyway, if I change providers, either MSSQL or MySQL, the migrations works.

EDIT 5

This is the first migration created. It is empty because I'm trying to use an existing database, already populated, and now I want to use Code First.

public partial class Initial : DbMigration
    {
        public override void Up()
        {
        }

        public override void Down()
        {
        }
    }

Soooo your question is, now that everything is done and working what you are supposed to do with a database? — Rand Random, May 30 '17 at 15:36
@RandRandom no, my question is how to make the migrations work, they are not being commited properly. — Bruno Xavier, May 30 '17 at 15:38
Not sure it is supported. See [here](http://tracker.firebirdsql.org/browse/DNET-506) — Steve Greene, May 30 '17 at 15:57
@SteveGreene they say its supported. It even works in parts. Like creating the MigrationHistory table. — Bruno Xavier, May 30 '17 at 16:02
@SteveGreene the issue you posted says "fixed" and "resolved" as there Status. So yes, it is supported. — Rand Random, May 30 '17 at 16:09
Acutally it says "done-ish" and the link is broken. You could be running into some of [these issues](https://stackoverflow.com/questions/35789394/entity-framework-code-first-and-firebird-foreign-key-name-issue). You might go right to the [source](https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient) and ask. Or generate a script and run it in FB to see if that is working. — Steve Greene, May 30 '17 at 16:11
@RandRandom yes. EntityFramework.Firebird and FirebirdSql.Data.FirebirdClient are both 5.9.1.0 — Bruno Xavier, May 30 '17 at 16:16
I'm betting it is the binary field `Model` in __MigrationHistory. Looks like it may have issues with those: http://tracker.firebirdsql.org/browse/DNET-750 — Steve Greene, May 30 '17 at 16:28
@SteveGreene I thought about that too. Do you know if its possible to change how the MigrationHistory table is created? — Bruno Xavier, May 30 '17 at 16:30
"Unexpected end of command - line 1, column 226." - makes me think that HEX literal - x'012345678.....DEF' got too long and was cut in the middle and the ending part of `28C0B00', CAST(_UTF8'6.1.3-40302' AS VARCHAR(32)))` just is not present in the SQL CORE. There are implementation restrictions on maximum script length, maximum statement length or a maximum VARCHAR length limit ( http://firebirdsql.org/refdocs/langrefupd25-hexbinstrings.html ) and seems you just hit one. The end of the statement seems just got cut off. Ergo - do not use literals, use parameters :-D — Arioch 'The, May 30 '17 at 16:42
@Arioch'The the script is generated by EF Update-Database -Script — Bruno Xavier, May 30 '17 at 16:43
@BrunoXavier well, there is no way to work with parameters in plain SQL text scripts, so I kind of can see why it is doing like that. But I wonder, when EF is doing its direct migration - does it use parameters, or does it merely generates the text-only script internally and then tries to force it down the Firebird throat?.. — Arioch 'The, May 30 '17 at 16:45
@Arioch'The I have no idea. I thought that the script generated would work, since it work with MSSQL, for instance. — Bruno Xavier, May 30 '17 at 16:46
" since it work with MSSQL " - well, it perhaps is a feat, that Microsoft EF is compatible to Microsoft SQL Server. But I fail to see how this says anything about other, non-Microsoft SQL servers. Afteral Microsoft Three Es strategy is against that assumption. https://en.wikipedia.org/wiki/Embrace,_extend_and_extinguish — Arioch 'The, May 30 '17 at 16:49
@Arioch'The Yeah, thats was my first thought. Then, I installed MySQL and tried to migrate there, and it worked as well, so yeah... — Bruno Xavier, May 30 '17 at 16:56
Wait until the script exceeds 16MB :-D https://lists.mysql.com/mysql/187971 — Arioch 'The, May 30 '17 at 17:34
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/145486/discussion-between-bruno-xavier-and-arioch-the). — Bruno Xavier, May 30 '17 at 18:02
Still puzzled by "column 226" magic number. 256 = 30 + 226. But the first line of insert stmt is much longer than 30. And hard limits of FB2 are in kilobytes not bytes.... — Arioch 'The, May 30 '17 at 18:07
Jiri (the maintainer of the Firebird ADO.net provider) is rather resolute in removing bugs reports that don't contain any pertinent information and reproduction information, especially when they look more like support requests than bug reports. You might want to try mailing to the Firebird .NET provider mailinglist instead. — Mark Rotteveel, May 30 '17 at 19:52
At any rate, this question is missing sufficient information as well. Please show the definition of the migration itself and related models, and also post the generated script. — Mark Rotteveel, May 30 '17 at 19:55
@MarkRotteveel the script generated from EF6 is in the Question. I added the information about the first migration — Bruno Xavier, May 30 '17 at 19:59
The only thing I know for sure is that the binary string exceeds implementation limits (that string is 106112 characters, and Firebird 2.5 has a limit 32K for string literals and 64K for the entire query string (Firebird 3 allows for 10MB query strings, although literals are still restricted to 32K). The position 226 from the error is the opening quote of the string literal, hence the unexpected end of command: the parser backtracked to that position before giving up. But again, it might be better to take this to the mailinglist; I haven't seen Jiri on SO in a while. — Mark Rotteveel, May 30 '17 at 20:27
@MarkRotteveel From what I gathered, thats accurate. I tried changing the implementation of how the Model bit of the MigrationHistory table works, but without success. I guess I will go to the mailing lists then. — Bruno Xavier, May 30 '17 at 20:32
My, my... why would EF even store 100KB blob into history tables? — Arioch 'The, May 30 '17 at 20:50
@Arioch'The from what I understand, the entire model. This data can even be used to generate an EDMX file. Why that, I have no idea. — Bruno Xavier, May 30 '17 at 21:05
@magicandre1981 could be, but I dont think it will solve the problem, will it? — Bruno Xavier, May 31 '17 at 15:03

score 2 · Accepted Answer · answered May 31 '17 at 09:30

2

As Mark Rotteveel said (and he should get points for this answer). You're hitting the limit for a statement size in Firebird. There's not much I can do with it because this needs to be script (even if it's executed directly), so parameters won't work.

Possible workaround would be to create multiple smaller models and use these for migrations. Or always generate the script into file and manually use parameters.

answered May 31 '17 at 09:30

cincura.net

4,130
16
40

Would it be possible instead of one insert to generate an insert followed by multiple updates to concatenate the rest of the data to the blob? – Mark Rotteveel May 31 '17 at 11:28
That's what I'm wondering. The problem with small migrations is that, eventually, it will get big enough to crap out again. So there's got to be another way, right? – Bruno Xavier May 31 '17 at 13:00
@MarkRotteveel Probably yes. The problem that immediately pops up is half finished set of updates. – cincura.net May 31 '17 at 17:30
@BrunoXavier It's not about migrations, but about model. – cincura.net May 31 '17 at 17:31
True. I was worried more about manual execution from script (which is valid and supported scenario). I created a ticket for it (http://tracker.firebirdsql.org/browse/DNET-769), to keep a reference. – cincura.net Jun 01 '17 at 11:05
@cincura.net Can the MODEL field be made as text BLOB not binary BLOB? because it seems concatenating binary BLOBs is not what Firebird is good at - for example http://tracker.firebirdsql.org/browse/CORE-3476 – Arioch 'The Jun 01 '17 at 15:49
@MarkRotteveel " generate an insert followed by multiple updates " - no, the update should be single command, but the data generation is to be split into several pieces. Something like https://stackoverflow.com/a/43997801/976391 - either add UNIONed and LISTed subselects right into the insert statement - this probably would work on FB3 but would hit FB2 64KB statement limit. Or use GTT just to make the blob large enough BEFORE running the insert. And if creating the BLOB would fail, then the insert would not happen and the GTT would clear itself. – Arioch 'The Jun 01 '17 at 15:58
@Arioch'The Of course everything is possible. It's just a question how ugly it is. And this is damn ugly. – cincura.net Jun 01 '17 at 19:36

Entity Framework Code First Migrations and Firebird

1 Answers1

Linked