Insert into MigrationHistory__ fails - DB2

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
ogi
Posts: 12
Joined: Wed 19 Oct 2016 07:19

Insert into MigrationHistory__ fails - DB2

Post by ogi » Fri 07 Jul 2017 10:07

Hi,

I'm having issues with the script that is being generated when I use Update-Database -script. The problem is, that when I run the script via an external SQL IDE for DB2 it fails when trying to insert into the Model field of MigrationHistory__.
The length resulting from "CONCAT" is greater than "0000032700".
The issue is that the script generated uses string concatenation which has a limitation of 32,700 bytes in length.

Code: Select all

INSERT INTO MigrationHistory__ (MigrationId, ContextKey, Model, ProductVersion) VALUES ('201707070625113_Init', 'DB.DB2Migrations.Migrations.Configuration', CAST(X'1F8B' || X'273F76' AS BLOB(66820)), '6.1.3-40302');
The problem is that it does string concatenation inside the CAST function, when it should be doing BLOB concatenation of CAST(string) items.

My questions:
  • is this normal behavior of the SQL generator?
  • if yes, is there an "automatic" workaround (not having to do this manually every time)?
  • if this is a bug, is there an estimate on how soon it can be fixed?
Versions: EF 6.1.3, Devart DB2 2.2.299

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Insert into MigrationHistory__ fails - DB2

Post by Shalex » Fri 14 Jul 2017 14:16

Could you please point how to change a test migration to reproduce concatenation like in your "INSERT INTO MigrationHistory__"?

Code: Select all

namespace ConsoleApplication140.Migrations
{
[...]    
    public partial class _1 : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "DB2ADMIN.TM",
                c => new
                    {
                        Id = c.Int(nullable: false),
                        Name = c.String(maxLength: 13, unicode: false),
                    })
                .PrimaryKey(t => t.Id);
            
            CreateTable(
                "DB2ADMIN.TM1S",
                c => new
                    {
                        Id = c.Int(nullable: false),
                        Name = c.String(maxLength: 13, unicode: false),
                        TMId = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("DB2ADMIN.TM", t => t.TMId)
                .Index(t => t.TMId);
        }
        [...]
    }
}
->

Code: Select all

PM> update-database -script
->

Code: Select all

-- Script was generated by Devart dotConnect for DB2, Version 2.2.314
-- Product home page: http://www.devart.com/dotconnect/db2
-- Database version: DB2 10.1.2
-- Script date 14.07.2017 17:08:33

CREATE TABLE DB2ADMIN.TM ( 
  "Id" INTEGER NOT NULL,
  "Name" VARCHAR(13),
  PRIMARY KEY ("Id")
);

CREATE TABLE DB2ADMIN.TM1S ( 
  "Id" INTEGER NOT NULL,
  "Name" VARCHAR(13),
  "TMId" INTEGER NOT NULL,
  PRIMARY KEY ("Id")
);

CREATE INDEX DB2ADMIN."IX_TM1S_TMId" ON DB2ADMIN.TM1S ("TMId");

ALTER TABLE DB2ADMIN.TM1S
  ADD CONSTRAINT "FK_TM1S_TM_TMId" FOREIGN KEY ("TMId") REFERENCES DB2ADMIN.TM ("Id")
  ON DELETE NO ACTION;

CREATE TABLE "__MigrationHistory" ( 
  "MigrationId" VARGRAPHIC(150) NOT NULL,
  "ContextKey" VARGRAPHIC(300) NOT NULL,
  "Model" BLOB(2147483647) NOT NULL,
  "ProductVersion" VARGRAPHIC(32) NOT NULL,
  PRIMARY KEY ("MigrationId", "ContextKey")
);

INSERT INTO "__MigrationHistory" ("MigrationId", "ContextKey", "Model", "ProductVersion") VALUES ('201707141408023_1', 'ConsoleApplication140.Migrations.Configuration', CAST(X'1F8B0800000000000400ED59CB6EE33614DD17E83F105A1619CB4ABA680D7B069E3C0AA3711244CEB4BB8091AE1DA2142590541063D02FEBA29FD45FE8A55E96483976D2493A408B01820979DF3CF7F250F9EB8F3FC71F1E134E1E402A968A89170C861E0111A53113AB8997EBE5BB1FBC0FEFBFFD667C1A278FE4532D7764E45053A88977AF7536F27D15DD4342D52061914C55BAD483284D7C1AA7FEE170F8A31F043EA0090F6D1132BECE85660914BFE0AFC7A98820D339E5F33406AEAA75DC090BABE48226A0321AC1C40BA7F3ABF3D342CE2353CE288610025F7A840A916AAA31C0D18D8250CB54ACC20C17285FAC3340B925E50AAAC0471BF17D73181E9A1CFC8D626D2ACA954E93671A0C8EAAA2F8B6FA8B4AEB3545C3B29D6279F5DA645D946EE22DE61EB1DD8C8EB934229D9A0E16F30382E7A1520ED32CE32C2A6483EF8707CDF1234ACC3F94CBB9CE254C04E45A527E40AEF23BD4F819D68BF43710139173DE8E0B23C3BDCE022E5DC93403A9D7D7B0ACA29DC51EF1BB7ABEADD8A8B574CA6C66421F1D7AE4029DD33B0ECDB1B7320F752AE1271020A986F88A6A0D124FED2215E078B6FC989FB527C418F68947E6F4F11CC44ADF63071D79E48C3D425C2F54CE6F04C3AE421D2D73D7C7057D60AB2234CBDB621E841EB9065E6CAA7B969568C763BA2DF7CE649A5CA7BC902D966EC33497918931EDAE2FA85C81EEFA1EFB1BA8EC0050B03F8282FF21F4E6107280B33B9BFD41F85C08D650B3215843731F084E954A2356386DE2281D76C33E1531D9D2006585CB58B1C4083566E088CEB0CA4EFEAEA1268DB6A1C0B6F49D630961081284B9794C1F20B099D02E6699885846797FF496F89E2037E56C0CDB3B2790818831AEFE34F7F158C2CAF5D998B65A6E5725C67EEB98DD01843A1A354056EECB3153EC31503D03092FFE6A26A9AA4FEC33366643D02D686FE0D7028B830D5BCF00BF47D385552BC1B67ED53BAD5DABADEC1A6FC779135C998F733ADB81DDD134E1D823B31B7C4FD73627B4E16C7E49DA6A72E76F6177E339CD329C7D2DB657AD90B0A47AC7EFC2E753A1A4B4E147AA871135D1369E708AD31558BBE81A233D6352E913AAE91D35D3F2384E1C310B8F5B30533B6B20E71E508DA35AD4FCBF0DF9FA66ED69D24AE50CF3484C6F17175317128E52C1AC29A7B2E7063C4E799E88ED0366BB7679AFB5F5CB15D7C2D8B7A276068A53120B987671F72C7D6F5FED5BFCE045D5EFD1FAB7CBBFCD4239DDDB16FAE7FD2B1FA0335B6C91C67B3363AC5932AEFA7AF773D269F452C423589A07169B263F81072AF5C0080C4E3E1E6EB6E654B025285D32550FF7C83295E4FCE69703120C07C1E0D07AA17E3DAF455FA998EF7C32BE39EF4672002B90BBB9EA138C1A0F2BBAA7D2A1D41B932E7F7ED633E83F5395A75F154D540E099B21137C9C789F0BA51199FD7A6BF40EC8A5C4A6199121F97D472A5FF249F08F9E01C19B71FFB7E1FB7D37D85747F25DEEF5420E5F8EF262304F4FE6B30B3CCC1272FB11FC6DFCFE09B33D14FA35E8FFF3287F60F3FCD7A1F9EE958BC8687DE745382AB6DA98305F7D05441D4C343233B14C6B605A11D522D6689A83A63102662A355BD248E376044A151F573E519EA3C8697207F14C5CE63ACB35A60CC91D5FB78B6120FE94FFE22DD38D797C99159F45BE440A1826C314E0527CCC198F9BB8CF7A26E41613A677AA8F52E62CB5F938B55A3796DCAF53DB0C55E56B5A7E0149C6D198BA14217D8097C4866FF37358D1685DF3A7ED46761F44B7ECE31346579226AAB2B1D1377FBBF0CD1F2FDEFF0DBE043843EE180000' AS BLOB(1301)), '6.1.3-40302');

ogi
Posts: 12
Joined: Wed 19 Oct 2016 07:19

Re: Insert into MigrationHistory__ fails - DB2

Post by ogi » Mon 17 Jul 2017 07:44

I'm guessing that the model needs to be big enough, so that the value inserted into the Model field is concatenated (if I understand correctly, the value is the whole model in a compressed form - https://stackoverflow.com/a/25501338)?

I can't post my model here (company policy), and I don't have the time to create a big enough test model from scratch (that would take a lot of time).

My current model has about 160 tables with lots of FK's and indexes.

ogi
Posts: 12
Joined: Wed 19 Oct 2016 07:19

Re: Insert into MigrationHistory__ fails - DB2

Post by ogi » Thu 20 Jul 2017 07:37

Is there anything new on the subject? Were you able to reproduce the issue?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Insert into MigrationHistory__ fails - DB2

Post by Shalex » Thu 20 Jul 2017 16:29

We have reproduced the error. We will notify you when the issue is fixed.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Insert into MigrationHistory__ fails - DB2

Post by Shalex » Fri 04 Aug 2017 16:24

The bug with inserting data into MigrationHistory__ by EF6 Code-First Migrations for large model is fixed: viewtopic.php?f=52&t=35772.

tadej
Posts: 2
Joined: Fri 26 Apr 2019 13:25

Re: Insert into MigrationHistory__ fails - DB2

Post by tadej » Mon 06 May 2019 06:41

Hello,

I'm having a similar issue like above with newer version of devart for db2 (2.2.348). It seems like the "model" got too big and it can't be inserted in migrationHistory__.
When i add migration and create script for it (Update-Database -Script), it generates new insert into migrationhistory, where model is setup from 21 blobs. When i try to run this
on database, i get error "DB2 Database Error: ERROR [54001] [IBM][DB2/NT64] Too many concatenation operators."
Because of security reasons i can't share exact migration code or table structure, but my context is configured with cca 200+ tables.
Here is the snapshot of migration script:

Image

Is there any quick solution for this? I need the solution to this as soon as possible.

Thanks,
Tadej

tadej
Posts: 2
Joined: Fri 26 Apr 2019 13:25

Re: Insert into MigrationHistory__ fails - DB2

Post by tadej » Mon 06 May 2019 07:06

Forgot to mention, I also updated db2 provider to latest version (2.4.734), but error is still the same.

Image

Here is also link to random generated blobs of the same size which fails, if it's in any help: https://www.codepile.net/pile/JE2JKeEb

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Insert into MigrationHistory__ fails - DB2

Post by Shalex » Wed 08 May 2019 09:46

Thank you for your report. We are investigating the issue.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Insert into MigrationHistory__ fails - DB2

Post by Shalex » Fri 07 Jun 2019 17:23

The bug with inserting data into MigrationHistory__ by EF6 Code-First Migrations for an enormous model is fixed: viewtopic.php?f=52&t=38880.

Post Reply