Firebase Internal Foreign key constraint error

[stephanenicolas]

When enable foreign key constraint in our app using :

//https://stackoverflow.com/a/12531927/693752
db.setForeignKeyConstraintsEnabled(true);

we see an internal error in the logcat coming from an internal database schema definition from firebase:

04-16 21:34:57.852  6738  8269 E SQLiteDatabase: Error inserting period=0 target_class=com.firebase.jobdispatcher.GooglePlayReceiver required_network_type=0 runtime=1555450497848 extras={"com.firebase.jobdispatcher.tag":{"com.company.REGISTER":4},"com.firebase.jobdispatcher.trigger_type":{"1":0},"com.firebase.jobdispatcher.replace_current":{"true":3},"com.firebase.jobdispatcher.persistent":{"2":0},"registration_id":{"dIu14E5WNzw:APA91bEheSYPxZM5T_wHaiFcP_EZTHqEuVU0GWT58D-1wlrjUhMzmbrmyeWTjUfLy3zBCqJrf7mwASZlBFqkqdlNMs7hsRh2WPJwa4I4eLrJeAkzZMYZGIZ-fccj4C8zLRbSEDbuMlvu":4},"com.firebase.jobdispatcher.maximum_backoff_seconds":{"3600":0},"com.firebase.jobdispatcher.initial_backoff_seconds":{"30":0},"com.firebase.jobdispatcher.retry_policy":{"1":0},"com.firebase.jobdispatcher.window_end":{"0":0},"com.firebase.jobdispatcher.service":{"com.company.SomeService":4},"com.firebase.jobdispatcher.recurring":{"false":3},"com.firebase.jobdispatcher.constraints":{"2":0},"com.firebase.jobdispatcher.window_start":{"0":0}} source=8 target_package=com.company.dev source_version=1 last_runtime=0 user_id=0 job_id=-1 requires_charging=0 tag=com.company.REGISTER flex_time=0 task_type=0 retry_strategy={"maximum_backoff_seconds":{"3600":0},"initial_backoff_seconds":{"30":0},"retry_policy":{"0":0}}
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: pending_ops.tag, pending_ops.target_class, pending_ops.target_package, pending_ops.user_id (code 2067)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1474)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1343)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at zxx.a(:com.google.android.gms@16089022@16.0.89 (040700-239467275):148)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at aaat.a(:com.google.android.gms@16089022@16.0.89 (040700-239467275):166)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at aaat.a(:com.google.android.gms@16089022@16.0.89 (040700-239467275):8)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at aaat.a(:com.google.android.gms@16089022@16.0.89 (040700-239467275):157)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at zxu.run(:com.google.android.gms@16089022@16.0.89 (040700-239467275):2)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at rrt.b(:com.google.android.gms@16089022@16.0.89 (040700-239467275):32)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at rrt.run(:com.google.android.gms@16089022@16.0.89 (040700-239467275):21)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1162)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:636)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at rxx.run(Unknown Source:7)
04-16 21:34:57.852  6738  8269 E SQLiteDatabase: 	at java.lang.Thread.run(Thread.java:764)

[google-oss-bot]

I couldn't figure out how to label this issue, so I've labeled it for a human to triage. Hang tight.

[schmidt-sebastian]

@stephanenicolas Thanks for filing this issue. To help you further, can you let us know what Firebase SDKs you are using? Thank you!

[stephanenicolas]

FIREBASE_APPINDEXING_VERSION=16.0.2 FIREBASE_MESSAGING_VERSION=17.3.3 FIREBASE_CORE_VERSION=16.0.4 FIREBASE_IID_VERSION=17.0.3 Le mar. 16 avr. 2019 à 15:11, Sebastian Schmidt <notifications@github.com> a écrit :

…

@stephanenicolas <https://github.com/stephanenicolas> Thanks for filing this issue. To help you further, can you let us know what Firebase SDKs you are using? Thank you! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#361 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABv33dSOMT70CpNkuhoGpvPShEOrgSdTks5vhkqCgaJpZM4czxRL> .

[stephanenicolas]

@schmidt-sebastian Can you confirm that this issue is not a blocker ? We want to enable foreign key constraints in our app but we are wondering if this error would break firebase.

[stephanenicolas]

A co-worker told me that the bug might come from firebase job dispatcher: FIREBASE_JOB_DISPATCHER_VERSION=0.8.5 Le mar. 16 avr. 2019 à 16:45, Stéphane NICOLAS <steff.nicolas@gmail.com> a écrit :

…

FIREBASE_APPINDEXING_VERSION=16.0.2 FIREBASE_MESSAGING_VERSION=17.3.3 FIREBASE_CORE_VERSION=16.0.4 FIREBASE_IID_VERSION=17.0.3 Le mar. 16 avr. 2019 à 15:11, Sebastian Schmidt ***@***.***> a écrit : > @stephanenicolas <https://github.com/stephanenicolas> Thanks for filing > this issue. To help you further, can you let us know what Firebase SDKs you > are using? Thank you! > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub > <#361 (comment)>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/ABv33dSOMT70CpNkuhoGpvPShEOrgSdTks5vhkqCgaJpZM4czxRL> > . >

[schmidt-sebastian]

We believe that this may actually be an issue internal to the GMSCore Scheduler API. Since this code is not available publicly, I will file an internal issue to get this resolved. I can't promise a timeline for this fix, and unfortunately I do not know if this issue should block your change. We also believe that this issue might be unrelated to calling setForeignKeyConstraintsEnabled.

If there are any other customers affected by this issue, I would appreciate that you let us know here.

[philn5d-ar]

I have the same issue:

android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: pending_ops.tag, pending_ops.target_class, pending_ops.target_package, pending_ops.user_id (code 2067 SQLITE_CONSTRAINT_UNIQUE)

not calling any db methods or even using SqlLite ourselves. Probably another package enabling constraints...which kind of points to poor DB design IMPO.

which, after searching led me here because of "pending_ops" schema hit on this search result.

relevant stack line: at sob.run(:com.google.android.gms@201516037@20.15.16 (120400-309763488):7)

other info:

implementation 'com.google.android.gms:play-services-ads:19.0.1'
implementation 'com.google.android.gms:play-services-analytics:17.0.0'
implementation 'com.crashlytics.sdk.android:crashlytics:2.10.1'
implementation 'com.google.firebase:firebase-analytics:17.4.0'
implementation 'com.google.firebase:firebase-crashlytics:17.0.0'

is it firebase? /shrug

UPDATE:

here's the whole stack trace

2020-05-08 14:41:21.082 5299-24447/? E/SQLiteDatabase: Error inserting flex_time=738000 job_id=-1 period=1478000 source=16 requires_charging=0 preferred_network_type=1 target_class=com.google.android.gms.measurement.PackageMeasurementTaskService user_id=0 target_package=com.google.android.gms tag=Measurement.PackageMeasurementTaskService.UPLOAD_TASK_TAG task_type=0 required_idleness_state=0 service_kind=0 source_version=201516000 persistence_level=1 preferred_charging_state=1 required_network_type=0 runtime=1588966881034 retry_strategy={"maximum_backoff_seconds":{"3600":0},"initial_backoff_seconds":{"30":0},"retry_policy":{"0":0}} last_runtime=0
    android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: pending_ops.tag, pending_ops.target_class, pending_ops.target_package, pending_ops.user_id (code 2067 SQLITE_CONSTRAINT_UNIQUE)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:879)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1599)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1468)
        at aplm.a(:com.google.android.gms@201516037@20.15.16 (120400-309763488):76)
        at aplb.a(:com.google.android.gms@201516037@20.15.16 (120400-309763488):173)
        at aplb.a(:com.google.android.gms@201516037@20.15.16 (120400-309763488):21)
        at aplb.a(:com.google.android.gms@201516037@20.15.16 (120400-309763488):167)
        at aphk.run(:com.google.android.gms@201516037@20.15.16 (120400-309763488):8)
        at sob.b(:com.google.android.gms@201516037@20.15.16 (120400-309763488):12)
        at sob.run(:com.google.android.gms@201516037@20.15.16 (120400-309763488):7)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
        at sub.run(:com.google.android.gms@201516037@20.15.16 (120400-309763488):0)
        at java.lang.Thread.run(Thread.java:919)

[schmidt-sebastian]

The Firebase Database SDK doesn't access SQLite through GMSCore. I believe the stacktrace above is for an unrelated issue. Unfortunately, the trace is obfuscated so it is hard to trace this back to the original product.

[philn5d-ar]

Thanks @schmidt-sebastian! Any pointers on how to track it down to the root? I'm only working with a contract dev to get some Android app work done, not well-versed in the inner workings of Android code. I'm building and running (in AVMs) via Android studio and these logs are coming through logcat if knowing that helps. Much appreciated!

[schmidt-sebastian]

I haven't had do deal with obfuscated stacktraces since we moved to GitHub. Unfortunately, that means that I can't provide assistance here.

Maybe @samtstern has some pointers on how to de-obfuscate GMSCore stacktraces.

[angelolloqui]

I am having the same error, and it does not allow me to start the app.

 E/SQLiteDatabase: Error inserting period=6605000 preferred_charging_state=1 preferred_network_type=1 target_class=com.google.android.gms.measurement.PackageMeasurementTaskService required_network_type=0 runtime=1589376728778 required_idleness_state=0 source=16 service_kind=0 target_package=com.google.android.gms source_version=201516000 last_runtime=0 user_id=0 job_id=-1 requires_charging=0 tag=Measurement.PackageMeasurementTaskService.UPLOAD_TASK_TAG flex_time=3302000 task_type=0 retry_strategy={"maximum_backoff_seconds":{"3600":0},"initial_backoff_seconds":{"30":0},"retry_policy":{"0":0}}
    android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: pending_ops.tag, pending_ops.target_class, pending_ops.target_package, pending_ops.user_id (code 2067)
    #################################################################
    Error Code : 2067 (SQLITE_CONSTRAINT_UNIQUE)
    Caused By : Abort due to constraint violation.
    	(UNIQUE constraint failed: pending_ops.tag, pending_ops.target_class, pending_ops.target_package, pending_ops.user_id (code 2067))
    #################################################################
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:987)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:2007)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1874)
        at apnp.a(:com.google.android.gms@201516017@20.15.16 (040306-309763488):76)
        at apne.a(:com.google.android.gms@201516017@20.15.16 (040306-309763488):173)
        at apne.a(:com.google.android.gms@201516017@20.15.16 (040306-309763488):21)
        at apne.a(:com.google.android.gms@201516017@20.15.16 (040306-309763488):167)
        at apjn.run(:com.google.android.gms@201516017@20.15.16 (040306-309763488):8)
        at ssd.b(:com.google.android.gms@201516017@20.15.16 (040306-309763488):12)
        at ssd.run(:com.google.android.gms@201516017@20.15.16 (040306-309763488):7)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1162)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:636)
        at sye.run(:com.google.android.gms@201516017@20.15.16 (040306-309763488):0)
        at java.lang.Thread.run(Thread.java:764)

If I remove the Crashlytics depedency, then it stops crashing and everything seems to work as normal, but of course I will not get crash reporting.

My dependencies:

    ext.google_play_services_version = '17.0.+'
...
  // Play Services
    implementation "com.google.android.gms:play-services-maps:${google_play_services_version}"
    implementation "com.google.android.gms:play-services-analytics:${google_play_services_version}"
    implementation "com.google.android.gms:play-services-ads:${google_play_services_version}"
    implementation "com.google.android.gms:play-services-auth:${google_play_services_version}"
    implementation "com.google.android.gms:play-services-auth-api-phone:17.4.0"

    // Firebase push notifications
    implementation "com.google.firebase:firebase-messaging:20.1.+"
    implementation 'com.google.firebase:firebase-core:17.2.+'

    // Crash and analytics
    implementation "com.google.firebase:firebase-crashlytics:17.0.0"
...

UPDATE: If I remove crashlytics, the log error is still there, but the application does not crash. So, somehow I think the error itself is not triggered by crashlytics, but the crash is. Maybe incorrectly handling the exception?

[samtstern]

Ok there's some crash going on that looks similar to some old GitHub issues I've found:
googlearchive/firebase-jobdispatcher-android#94
https://github.com/evernote/android-job/issues/87

Some internal bugs which may be related (for Googlers);
https://b.corp.google.com/issues/156170585
https://b.corp.google.com/issues/149327697
https://b.corp.google.com/issues/146422649

I can't find the pattern here except that is has something to do with concurrency and job scheduling. Here's a random guess: I assume Crashlytics uses a second process to report crashes (otherwise it would be taken down by them) and maybe that process is not acquiring the right locks before trying to schedule the upload jobs?

Would need someone from the Crashlytics team to comment on what sort of Job scheduling it uses.