Initial database access fails; migration not properly handled due to difference in Indices

[VerumCH]

In the same vein as a couple of the other issues, I'm encountering a runtime error when attempting to open my database for the first time. However, my issue is that despite my own SQL-created database and my Room-generated database appearing to perfectly coincide, I get an error saying migration was not properly handled. And the problem is, I have no idea what the difference is because the "Found:" line in the error message is truncated part of the way through. I'm unsure if this is an actual real issue with the database representation being broken, or simply an issue with the error message for some reason.

Here's what I see:

Caused by: java.lang.IllegalStateException: Migration didn't properly handle Assist(<package name>).
                   Expected:
                  TableInfo{name='Assist', columns={effect=Column{name='effect', type='TEXT', notNull=true, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', notNull=true, primaryKeyPosition=1}, dance=Column{name='dance', type='INTEGER', notNull=true, primaryKeyPosition=0}, heal=Column{name='heal', type='INTEGER', notNull=true, primaryKeyPosition=0}, name=Column{name='name', type='TEXT', notNull=true, primaryKeyPosition=0}, range=Column{name='range', type='INTEGER', notNull=true, primaryKeyPosition=0}, predecessor=Column{name='predecessor', type='TEXT', notNull=false, primaryKeyPosition=0}, sp_cost=Column{name='sp_cost', type='INTEGER', notNull=true, primaryKeyPosition=0}}, foreignKeys=[ForeignKey{referenceTable='Assist', onDelete='NO ACTION', onUpdate='NO ACTION', columnNames=[predecessor], referenceColumnNames=[name]}], indices=[Index{name='index_Assist_predecessor', unique=false, columns=[predecessor]}, Index{name='index_Assist_name', unique=true, columns=[name]}]}
                   Found:
                  TableInfo{name='Assist', columns={effect=Column{name='effect', type='TEXT', notNull=true, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', notNull=true, primaryKeyPosition=1}, dance=Column{name='dance', type='INTEGER', notNull=true, primaryKeyPosition=0}, heal=Column{name='heal', type='INTEGER', notNull=true, primaryKeyPosition=0}, name=Column{name='name', type='TEXT', notNull=true, primaryKeyPositio

Any ideas?

For reference, the relevant portion of my SQL database generation:

CREATE TABLE 'Assist' (
  'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  'name' TEXT NOT NULL UNIQUE,
  'range' INTEGER NOT NULL DEFAULT 1,
  'effect' TEXT NOT NULL,
  'sp_cost' INTEGER NOT NULL,
  'heal' INTEGER NOT NULL DEFAULT 0,
  'dance' INTEGER NOT NULL DEFAULT 0,
  'predecessor' TEXT NULL,

  -- Foreign Keys
  FOREIGN KEY (predecessor) REFERENCES 'Assist' ('name')
);

and my Room entity class:

@Entity (   indices = { @Index(value = "name", unique = true),
                        @Index(value = "predecessor")},
            foreignKeys = @ForeignKey(  entity = Assist.class,
                                        parentColumns = "name",
                                        childColumns = "predecessor"))
public class Assist {

    @PrimaryKey (autoGenerate = true)
    public int id;

    @NonNull
    public String name;

    public int range;

    @NonNull
    public String effect;

    @ColumnInfo(name = "sp_cost")
    public int spCost;

    public int heal;

    public int dance;

    public String predecessor;

}

EDIT -- Running on a different virtual device (Pixel 2 API 26, was running Nexus 6 API 23) gives me the full error message. Here's what it shows now:

                   Expected:
                  TableInfo{name='Assist', columns={effect=Column{name='effect', type='TEXT', notNull=true, primaryKeyPosition=0}, name=Column{name='name', type='TEXT', notNull=true, primaryKeyPosition=0}, heal=Column{name='heal', type='INTEGER', notNull=true, primaryKeyPosition=0}, range=Column{name='range', type='INTEGER', notNull=true, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', notNull=true, primaryKeyPosition=1}, sp_cost=Column{name='sp_cost', type='INTEGER', notNull=true, primaryKeyPosition=0}, predecessor=Column{name='predecessor', type='TEXT', notNull=false, primaryKeyPosition=0}, dance=Column{name='dance', type='INTEGER', notNull=true, primaryKeyPosition=0}}, foreignKeys=[ForeignKey{referenceTable='Assist', onDelete='NO ACTION', onUpdate='NO ACTION', columnNames=[predecessor], referenceColumnNames=[name]}], indices=[Index{name='index_Assist_name', unique=true, columns=[name]}, Index{name='index_Assist_predecessor', unique=false, columns=[predecessor]}]}
                   Found:
                  TableInfo{name='Assist', columns={effect=Column{name='effect', type='TEXT', notNull=true, primaryKeyPosition=0}, name=Column{name='name', type='TEXT', notNull=true, primaryKeyPosition=0}, heal=Column{name='heal', type='INTEGER', notNull=true, primaryKeyPosition=0}, range=Column{name='range', type='INTEGER', notNull=true, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', notNull=true, primaryKeyPosition=1}, sp_cost=Column{name='sp_cost', type='INTEGER', notNull=true, primaryKeyPosition=0}, predecessor=Column{name='predecessor', type='TEXT', notNull=false, primaryKeyPosition=0}, dance=Column{name='dance', type='INTEGER', notNull=true, primaryKeyPosition=0}}, foreignKeys=[ForeignKey{referenceTable='Assist', onDelete='NO ACTION', onUpdate='NO ACTION', columnNames=[predecessor], referenceColumnNames=[name]}], indices=[]}

So the difference is in the indices. However, Room needs those indices to work properly. How do I make equivalents via SQL?

[humazed]

the error is that there is a difference between your Database file and Room Dao
for the difference between expected vs found see #1

[VerumCH]

the error is that there is a difference between your Database file and Room Dao

Yes - at first the problem was I couldn't see the error since the message was cut off. Now, the problem is that the error is involving Indices, which I thought I found the fix for but doesn't seem to be working.

I added the following lines in my SQL generation code after creating the table 'Assist' (which is the one being picked up first by the runtime error):

-- Indices for Assist
CREATE UNIQUE INDEX index_Assist_name ON Assist (name);
CREATE INDEX index_Assist_predecessor ON Assist (predecessor);

and when I use a separate program to browse the SQLite database, the indices show up.

But the error message from Room/RoomAsset in Android Studio still shows indices=[] in the "Found:" row, rather than the expected indices=[Index{name='index_Assist_name', unique=true, columns=[name]}, Index{name='index_Assist_predecessor', unique=false, columns=[predecessor]}].

[humazed]

I will look into it and help you solve the problem in 24 hours max, but I'm going to sleep now :D

[humazed]

okay, from what I can see my best guess that the problem is the name difference:
in SQL you use index_Assist_name however in your Dao class you use name

[VerumCH]

So in the DAO, "name" in the value field simply indicates what column the index is on. According to Room's documentation, if a specific name is not given to the index, it will be called index_<TableName>_<ColumnName>, which in this case would be index_Assist_name. If you look in the Expected: line, you can see that that is, in fact, happening.

The problem is Room (in this case RoomAsset, since this is not a problem that would ever come up using Room normally) is not recognizing any of the indices specified in the original SQLite database. Hence the indices=[] in the Found: line.

If I had to guess, it may have something to do with the fact that indices in SQLite are not actually "attached" to a specific table, they are in their own space within the SQLite database and simply reference what table they apply to.

In my case, I believe can fix my issue by simply doing away with the UNIQUE constraint on the columns in my tables (since I won't ever be adding to this database at runtime) and removing any FOREIGN KEY references (since they are only there to save a small amount of time/trouble in a database with only a few hundred data points).

However, this could be an issue for people who need those UNIQUE or FOREIGN KEY constraints, particularly if their databases have many thousands of entries and/or they need to ship preset data and add more data during runtime. So I think it is worth looking into.

I think I will go with the solution I mentioned for my particular case, so if you want you can close this issue. But as I mentioned, I think it's worthy of getting to the bottom of in the case of more complex use cases. So I'm not personally going to close this yet.

[humazed]

thank you for the explaining.
and I agree with your initial assumption that it might be related to the SQLite store the indices, I will first try to reproduce this and see if there is a way to easily fix indices problem.

I will leave this issue open until a solution is found.

[niklaspolke]

I have the same problem - is anything happened according to this issue in the last 2 years?

I have a new Entity with an index created by the entity annotation. I try to reach this by an migration sql statement (CREATE INDEX index_< table >_< column > ON < table > (< column >)). A test with SELECT type, name, tbl_name, sql FROM sqlite_master WHERE type= 'index'; successfully shows me the index as well as an other index of my database.
But within the TableInfo the value indices of the table is null (indices=null). So the migration test fails.

Any ideas how to fix this?