Sqlite-net: SQLite.SQLiteException: duplicate column name id in v1.3.3

[dernippel]

Hi,

I'm using connection.CreateTableAsync() with a SQLiteAsyncConnection to create tables.

I've a base-type for all of my tables:
```c#
public abstract class LocalTableBase
{
[PrimaryKey]
public virtual int Id { get; set; }
}

In some cases I need a [AutoIncrement] Attribute for Id, so I'm using inheritance like:
```c#
public class User : LocalTableBase
{
        [PrimaryKey, AutoIncrement]
        public override int Id { get; set; }
}

This works fine up to v1.3.2 but in v1.3.3 it causes an
SQLite.SQLiteException: duplicate column name id

I'm using current Visual Studio 2017 with a Xamarin.Forms App and test against Android (Target Framework Android 7.1) and UWP (Target version: Creators Update). The exception happens on both platforms.

Removing the overriding Properties doe also work, but the tables are created without AutoIncrement for sure. So I think the problem must be in using such a construct.

Thanks for helping.

[UweKeim]

I'm getting similar errors when jumping from 1.3.1 to 1.3.3:

table ArticleLanguages has no column named Article

This seems to happen for ManyToOne detail table mapping properties from the SQLite-Net-Extensions package.

[UweKeim]

I've added an allowedVersions="(,1.3.1]" attribute to the packages.config file to limit the maximum version:

<package id="sqlite-net-pcl" version="1.3.1" targetFramework="net452" allowedVersions="(,1.3.1]" />

[Physikbuddha]

This can be solved in a trivial way by changing parts of the TableMapping function in line 1888, from

``` c#
foreach (var p in props)
{
var ignore = p.CustomAttributes.Any(x => x.AttributeType == typeof(IgnoreAttribute));
if (p.CanWrite && !ignore)
{
cols.Add(new Column(p, createFlags));
}
}

to
``` c#
foreach (var p in props)
{
    var ignore = p.CustomAttributes.Any(x => x.AttributeType == typeof(IgnoreAttribute));

    var actualDefinition = p.GetGetMethod(false);
    var baseDefinition = actualDefinition.GetBaseDefinition();
    bool isOverridden = actualDefinition != baseDefinition;

    if (p.CanWrite && !ignore && !isOverridden)
    {
        cols.Add(new Column(p, createFlags));
    }
}

Source is Stackoverflow: https://stackoverflow.com/a/4505303/3414957

However, I'm not aware of any side effects this may cause, but it's sufficient for my use case. Might want to do the same check for GetSetMethod, though.

Oh, and 1.0.10 is the oldest version I can from NuGet and even this version is broken already. Can't confirm dernippels statement 'This works fine up to v1.3.2 but in v1.3.3 it causes an SQLite.SQLiteException', but I'm not using the async library.

[alberk8]

Please Fix this quick. I can even create a new table, the whole thing just crash after the table is created. If i run it the second time without the createtable method the table exists.

public class TestTable
    {
        [PrimaryKey, AutoIncrement]
        public int? key_id { get; set; }
        public string name { get; set; }
        public float quantity { get; set; }
    public  TestTable(){}
   }

conn.CreateTable<TestTable>();

[alberk8]

After spending some hours doing trail and error, process of elimination, I think I have manage to get this working.

Go to VS Menu -> Project -> Appname Properties -> Android Options -> Linker - Skip linking assemblies
and paste in the following

SQLite-net;SQLitePCLRaw.batteries_v2;SQLitePCLRaw.core

Go to VS Menu Build -> Clean Solution.

Build and Deploy.

[dernippel]

Hi,
your workaround is working for Android only. That's no solution for UWP and/or iOS targets.
But thanks for that for Android-only targets.

[IDispose]

I ran into the duplicate column issue as well and after stepping thru code, realized that the TableMapping function has a "bug".

public class Base{
    public int Id { get; set; }
}

public class Derived : Base {
    [Required]
    public new int Id {get; set;}
}

CreateTable<Derived>()

will throw an exception stating Id column is duplicate. The Table mapping function walks thru the inheritance chain collecting the public properties to determine the SQL to be generated.

The workaround that worked for me is to add [Ignore] attribute to the base class like so

public class Base{
      [Ignore]
      public int Id { get; set; }
}

Hope this helps.

[hvaughan3]

Also seeing this issue. While IDispose's solution would work just fine, my base class is defined in a separate project which is shared with other projects and so I do not want to have to take a dependency on SQLite for that separate project.

I also tried using new to overwrite the Id property but it has the same effect as using override. Any news on when this could be fixed?

Looking at PropertyInfo.IsDefined's inherit parameter and this 1.3.3 commit, I am wondering if this is caused by using PropertyInfo.IsDefined(Type, true) which does not work on inherited properties and events.

One potential fix may be to use Attribute.IsDefined which does take inherited properties into account. Attribute methods only look at custom attributes that inherit from System.Attribute though so that is something to consider.

Also I can confirm that 1.3.2 does not have this problem.

[praeclarum]

Ahh this is very fascinating. Sorry for the bugs - we didn't have a test for this case.

Writing the test now and hope to have a fix soon.

[praeclarum]

This has been fixed by https://github.com/praeclarum/sqlite-net/commit/05e71dc1b9a64d7c539947a967be6c501d711310

I will get a release out today!

[hvaughan3]

@praeclarum
I am still having similar problems after updating to version 1.4.118. Does that version have this latest fix in it?

[aizefler]

@praeclarum I have the same problem, some solution right away?

08-21 15:01:45.774 I/MonoDroid( 4006): UNHANDLED EXCEPTION:
08-21 15:01:45.806 I/MonoDroid( 4006): SQLite.SQLiteException: duplicate column name: Id
08-21 15:01:45.806 I/MonoDroid( 4006): at SQLite.SQLite3.Prepare2 (SQLitePCL.sqlite3 db, System.String query) [0x0000f] in /Users/vagrant/git/src/SQLite.cs:3544
08-21 15:01:45.806 I/MonoDroid( 4006): at SQLite.SQLiteCommand.Prepare () [0x00000] in /Users/vagrant/git/src/SQLite.cs:2463
08-21 15:01:45.806 I/MonoDroid( 4006): at SQLite.SQLiteCommand.ExecuteNonQuery () [0x00030] in /Users/vagrant/git/src/SQLite.cs:2318
08-21 15:01:45.806 I/MonoDroid( 4006): at SQLite.SQLiteConnection.Execute (System.String query, System.Object[] args) [0x0003a] in /Users/vagrant/git/src/SQLite.cs:673
08-21 15:01:45.806 I/MonoDroid( 4006): at SQLite.SQLiteConnection.CreateTable (System.Type ty, SQLite.CreateFlags createFlags) [0x00136] in /Users/vagrant/git/src/SQLite.cs:436
08-21 15:01:45.806 I/MonoDroid( 4006): at SQLite.SQLiteAsyncConnection+<>c__DisplayClass11_0.b__0 () [0x0002b] in /Users/vagrant/git/src/SQLiteAsync.cs:108
08-21 15:01:45.806 I/MonoDroid( 4006): at System.Threading.Tasks.Task1[TResult].InnerInvoke () [0x0000f] in <183b200ee49746d48fc781625979428e>:0 08-21 15:01:45.806 I/MonoDroid( 4006): at System.Threading.Tasks.Task.Execute () [0x00010] in <183b200ee49746d48fc781625979428e>:0 08-21 15:01:45.806 I/MonoDroid( 4006): --- End of stack trace from previous location where exception was thrown --- 08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in <183b200ee49746d48fc781625979428e>:0 08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) [0x0003e] in <183b200ee49746d48fc781625979428e>:0 08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) [0x00028] in <183b200ee49746d48fc781625979428e>:0 08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) [0x00008] in <183b200ee49746d48fc781625979428e>:0 08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.CompilerServices.TaskAwaiter1[TResult].GetResult () [0x00000] in <183b200ee49746d48fc781625979428e>:0
08-21 15:01:45.806 I/MonoDroid( 4006): at MRV.Obras.Mobile.Forms.Core.Repositories.CoreDbMigrations+d__0.MoveNext () [0x00074] in C:JobsobrassrcdevMRV.Obras.Mobile.Forms.CoreRepositoriesCoreDbMigrations.cs:11
08-21 15:01:45.806 I/MonoDroid( 4006): --- End of stack trace from previous location where exception was thrown ---
08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in <183b200ee49746d48fc781625979428e>:0
08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) [0x0003e] in <183b200ee49746d48fc781625979428e>:0
08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) [0x00028] in <183b200ee49746d48fc781625979428e>:0
08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) [0x00008] in <183b200ee49746d48fc781625979428e>:0
08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.CompilerServices.TaskAwaiter.GetResult () [0x00000] in <183b200ee49746d48fc781625979428e>:0
08-21 15:01:45.806 I/MonoDroid( 4006): at MRV.Obras.Mobile.Forms.App+d__4.MoveNext () [0x00025] in C:JobsobrassrcdevMRV.Obras.Mobile.FormsApp.xaml.cs:57
08-21 15:01:45.806 I/MonoDroid( 4006): --- End of stack trace from previous location where exception was thrown ---
08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in <183b200ee49746d48fc781625979428e>:0
08-21 15:01:45.806 I/MonoDroid( 4006): at System.Runtime.CompilerServices.AsyncMethodBuilderCore+<>c.b__6_0 (System.Object state) [0x00000] in <183b200ee49746d48fc781625979428e>:0
08-21 15:01:45.806 I/MonoDroid( 4006): at Android.App.SyncContext+<>c__DisplayClass2_0.b__0 () [0x00000] in <71828dd8fb5f4508815e23d6996c45c2>:0
08-21 15:01:45.806 I/MonoDroid( 4006): at Java.Lang.Thread+RunnableImplementor.Run () [0x00008] in <71828dd8fb5f4508815e23d6996c45c2>:0
08-21 15:01:45.806 I/MonoDroid( 4006): at Java.Lang.IRunnableInvoker.n_Run (System.IntPtr jnienv, System.IntPtr native__this) [0x00008] in <71828dd8fb5f4508815e23d6996c45c2>:0
08-21 15:01:45.806 I/MonoDroid( 4006): at (wrapper dynamic-method) System.Object:22bf24d1-3bfc-4512-a5d1-e370643ea95b (intptr,intptr)
08-21 15:01:45.818 W/art ( 4006): JNI RegisterNativeMethods: attempt to register 0 native methods for android.runtime.JavaProxyThrowable
08-21 15:01:45.822 D/Mono ( 4006): DllImport searching in: '__Internal' ('(null)').
08-21 15:01:45.822 D/Mono ( 4006): Searching for 'java_interop_jnienv_throw'.
08-21 15:01:45.822 D/Mono ( 4006): Probing 'java_interop_jnienv_throw'.
08-21 15:01:45.822 D/Mono ( 4006): Found as 'java_interop_jnienv_throw'.
08-21 15:01:45.825 D/Mono ( 4006): [0x9b77f930] worker unparking, timeout? no interrupted? no
08-21 15:01:45.825 D/Mono ( 4006): [0x9b77f930] worker parking
08-21 15:01:45.827 D/Mono ( 4006): [0x9b93d930] worker unparking, timeout? no interrupted? no
08-21 15:01:45.827 D/Mono ( 4006): [0x9b93d930] worker parking
An unhandled exception occured.

08-21 15:01:46.688 D/Mono ( 4006): [0x9b77f930] worker unparking, timeout? no interrupted? no
08-21 15:01:46.688 D/Mono ( 4006): [0x9b77f930] worker parking
08-21 15:01:46.690 E/mono ( 4006):
08-21 15:01:46.690 E/mono ( 4006): Unhandled Exception:
08-21 15:01:46.690 E/mono ( 4006): SQLite.SQLiteException: duplicate column name: Id
08-21 15:01:46.690 E/mono-rt ( 4006): [ERROR] FATAL UNHANDLED EXCEPTION: SQLite.SQLiteException: duplicate column name: Id
referenceTable GDEF length=814 1
referenceTable GSUB length=11364 1
referenceTable GPOS length=47302 1
referenceTable head length=54 1

[hvaughan3]

@izefler Downgrade to 1.3.2.

[aizefler]

@hvaughan3
Thank you, it worked!

[dernippel]

Hi,

I tried out v1.4.118 on a fresh Xamarin.Forms App (using Prism Template-Pack and .netstandard2.0 libs) today.
The bug is still there and not fixed yet, I think this issue should be reopened.

Luckily the sqlite-net is .Net Standard compatible since v1.3.0 so I was able to use v1.3.2 successfully again.

[jaysacco]

I had this issue on a Xamarin.Forms project, though it's with a .Net target, not a .Net Standard project. 1.4.118 worked fine on Android, but this issue appeared on the iOS build. Per https://forums.xamarin.com/discussion/65328/sqlite-duplicate-column-name-exception-on-device-but-not-on-simulator, adding --linkskip=SQLite-net to the iOS build resolved the problem for me.

[hvaughan3]

Any updates on when this will be fixed?

[WillAutioItrax]

@praeclarum
Is there a combination of Nugets that works? I have tried sqlite-net-pcl v 1.3.2 with v 1.1.8 of the SQLitePCLRaw suite as well as the 1.1.9 version. Also sqlite-net-pcl v 1.4.118.
This is all with Net Standard 1.6.1.
In Android, when I create a simple table with an int ID and a String Name I get:
Exception in CreateTable: System.Exception: Cannot create a table with zero columns (does 'MyApp.Test1' have public properties?)

For the same code in iOS and UWP it works.
How can I get past this? Thanks

[hvaughan3]

@WillAutioItrax That is not the same error others in this thread are talking about so it may be a different issue. Having said that, are you making sure to delete the app after changing library versions? Otherwise the old DB schema will remain intact. 1.3.2 works fine for me.

[WillAutioItrax]

@hvaughan3 Thanks for the response. I am getting two types of exceptions. The "Cannot create a table with zero columns" one and also "duplicate column name: MyId" (which is what brought me here I expect they are related as they appeard at the same time).
I tried your suggestions of deleting the app and using 1.3.2. I still get both types of errors.
Currently the SQLitePCLRaw suite are at 1.1.8.
Some of the other plugins are:
NETStandard.Library 1.6.1
Xamarin Forms 2.4.0.280
Microsoft.NETCore.Platforms 2.0.0
I am using VS2017 v15.5.5
Xamarin 4.8.0.760
Xamarin.Android SDK 8.1.5.0
What versions do you have where it works? What else may affect it?
Thanks!

[alexshakurin]

@WillAutioItrax, try adding SQLiteNetStandard to ignored assemblies list in Android Linker options. This did the trick for me.

[WillAutioItrax]

@alexshakurin Thanks for the suggestion. I tried it but no change. What did work, was comparing my current Droid.csproj with an older one for a project where things behaved. After making the current one look like the old one, I think what did it for me was to do a clean and then delete the bin and obj directories. I did that for both .core and .Droid

[mhmacleod]

This issue shouldn't be closed because of the number of other issues referring to this one which hasn't been solved (merely worked around)

[Stensan]

Why is this issue closed? The problem still exists. The only way for me is to set linking to Sdk Assemblies only, but my app size grows bigger. Could you please reopen it? Or should we open a new issue?

[fgiacomelli]

Please check that the problem is still present, I'm updating the thread in Xamarin Forms Forum here:
https://forums.xamarin.com/discussion/65328/sqlite-duplicate-column-name-exception-on-device-but-not-on-simulator

[dernippel]

Hi,
since there are no updates to this from developers side I decided to use EntityFramework Core with SQLite in my project. EF Core Docu

Inheritance of the models is working very well, and because the key is named "Id" I haven't need to set any Attributes anymore.

However I have one case in that I have to insert around 5700 new entries at once. This was pretty fast in sqlite-net-pcl using a transaction (I guess it was under a second), now it needs around 11 seconds. You may be able to tune this (at least you can use direct SQL in EF Core, if you want). I haven't tried this further because this operation is only done once after installing the app.

I could not publish my current project. If you want further information or need some help moving to EF Core just contact me.

Christopher

[wickedw]

Just adding a note this, upgrading to latest 1.5.166-beta seems to solve this for Android at least.