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: 7
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: 8247
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: 7
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: 7
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: 8247
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: 8247
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.

Post Reply