我对某些数据收到以下错误,概念很清楚：
android.database.sqlite.sqliteConstraintException: FOREIGN KEY constraint Failed (code 787)
但是,这并没有帮助我找到哪个特定记录具有无效的FK.而不是颠覆我的代码并尝试用新事务隔离每个插入,是否有任何方法可以打开(或提取)日志记录,使用有用的详细信息,例如有问题的表,甚至导致问题的FK值？
此外,我正在使用sqlBrite,并已启用调试日志记录(只记录操作,我仍然没有获得有关错误的更多信息)
更新：
这是我自己的代码上面的所有logcat;本质上是在尝试关闭先前打开的事务(BriteDatabase.Transaction)时捕获的异常(BriteDatabase.Transaction对象是一个新的添加;我只是从sqlBrite 0.1.0迁移到0.4.1).
android.database.sqlite.sqliteConstraintException: FOREIGN KEY constraint Failed (code 787)
at android.database.sqlite.sqliteConnection.nativeExecute(Native Method)
at android.database.sqlite.sqliteConnection.execute(sqliteConnection.java:555)
at android.database.sqlite.sqliteSession.endTransactionUnchecked(sqliteSession.java:437)
at android.database.sqlite.sqliteSession.endTransaction(sqliteSession.java:401)
at android.database.sqlite.sqliteDatabase.endTransaction(sqliteDatabase.java:522)
at com.squareup.sqlbrite.BriteDatabase$1.end(BriteDatabase.java:85)
解决方法
解决方案1：
您可以使用adb shell dumpsys dbinfo -v命令查看有关数据库的一些信息.
在我的例子中,我创建了一个小例子,它插入相同的记录两次强制sqliteConstraintException：
MyDB db = new MyDB(this);
// Insert the same record twice to force a sqliteConstraintException
db.insertIntoMyTable("1","MyName");
db.insertIntoMyTable("1","MyName");
这是我的Nexus 4中adb shell dumpsys dbinfo -v命令的输出(我不确定,但我认为设备之间的输出信息可能不同)：
Connection pool for /data/data/com.your.package.name/databases/MyDB:
Open: true
Max connections: 1
Available primary connection:
Connection #0:
connectionPtr: 0xffffffffb883aaf0
isPrimaryConnection: true
onlyAllowReadOnlyOperations: false
Most recently executed operations:
0: [2015-11-13 17:59:16.227] executeForLastInsertedRowId took 1ms - Failed,sql="INSERT INTO MyTable(name,_id) VALUES (?,?)",bindArgs=["MyName","1"],exception="UNIQUE constraint Failed: MyTable._id (code 1555)"
1: [2015-11-13 17:59:16.227] prepare took 0ms - succeeded,?)"
2: [2015-11-13 17:59:16.209] executeForLastInsertedRowId took 18ms - succeeded,"1"]
3: [2015-11-13 17:59:16.209] prepare took 0ms - succeeded,?)"
4: [2015-11-13 17:59:16.208] executeForLong took 1ms - succeeded,sql="PRAGMA user_version;"
5: [2015-11-13 17:59:16.208] prepare took 0ms - succeeded,sql="PRAGMA user_version;"
6: [2015-11-13 17:59:16.208] executeForString took 0ms - succeeded,sql="SELECT locale FROM android_Metadata UNION SELECT NULL ORDER BY locale DESC LIMIT 1"
7: [2015-11-13 17:59:16.207] execute took 1ms - succeeded,sql="CREATE TABLE IF NOT EXISTS android_Metadata (locale TEXT)"
8: [2015-11-13 17:59:16.207] executeForLong took 0ms - succeeded,sql="PRAGMA wal_autocheckpoint=100"
9: [2015-11-13 17:59:16.207] executeForLong took 0ms - succeeded,sql="PRAGMA wal_autocheckpoint"
10: [2015-11-13 17:59:16.207] executeForLong took 0ms - succeeded,sql="PRAGMA journal_size_limit=524288"
11: [2015-11-13 17:59:16.207] executeForLong took 0ms - succeeded,sql="PRAGMA journal_size_limit"
12: [2015-11-13 17:59:16.206] executeForString took 0ms - succeeded,sql="PRAGMA synchronous"
13: [2015-11-13 17:59:16.206] executeForString took 0ms - succeeded,sql="PRAGMA journal_mode=PERSIST"
14: [2015-11-13 17:59:16.205] executeForString took 1ms - succeeded,sql="PRAGMA journal_mode"
15: [2015-11-13 17:59:16.204] executeForLong took 0ms - succeeded,sql="PRAGMA foreign_keys"
16: [2015-11-13 17:59:16.204] executeForLong took 0ms - succeeded,sql="PRAGMA page_size"
Prepared statement cache:
0: statementPtr=0xffffffffb8839558,numParameters=0,type=1,readOnly=true,sql="SELECT locale FROM android_Metadata UNION SELECT NULL ORDER BY locale DESC LIMIT 1"
Available non-primary connections:
<none>
Acquired connections:
<none>
Connection waiters:
<none>
分析输出,在最近执行的操作中：我得到以下信息(一个记录成功插入,另一个记录失败)：
0: [2015-11-13 17:59:16.227] executeForLastInsertedRowId took 1ms - Failed,exception="UNIQUE constraint Failed: MyTable._id (code 1555)" 2: [2015-11-13 17:59:16.209] executeForLastInsertedRowId took 18ms - succeeded,"1"]
解决方案2：
这个解决方案有点棘手,但您可以使用android.database.sqlite.sqliteDebug类通过Java代码获取类似的信息.
如果你看一下this,你会发现这个类被标记为@hide,这意味着sqliteDebug类被排除在api文档之外,但它仍然可以通过反射访问.
因此,要打印sqlite数据库的调试信息,可以使用以下代码：
// The messages will be printed as Log.ERROR using the tag "DB"
Printer p = new LogPrinter(Log.ERROR,"DB");
// We will invoke the "dump" method of the "android.database.sqlite.sqliteDebug" class
// We eill use the "-v" parameter because we want the output to be verbose
Class<?> c = Class.forName("android.database.sqlite.sqliteDebug");
Method method = c.getmethod("dump",new Class[]{Printer.class,String[].class});
method.invoke(null,p,new String[]{"-v"});
一个简单的例子来说明解决方案：
MyDB db = new MyDB(this);
// Insert the same record twice to force a sqliteConstraintException
db.insertIntoMyTable("1","MyName");
try {
Printer p = new LogPrinter(Log.ERROR,"DB");
Class<?> c = Class.forName("android.database.sqlite.sqliteDebug");
Method method = c.getmethod("dump",String[].class});
method.invoke(null,new String[]{"-v"});
}
catch (Exception e) {
Log.e("mytag",e.getMessage());
}
在这种情况下,我使用LogPrinter将调试信息转储到logcat,但您可以使用自己的Printer实现.
在我的Nexus 4中,logcat看起来像这样(我不确定,但我认为设备之间的输出信息可能不同)：
E/sqliteLog(22296): (1555) abort at 10 in [INSERT INTO MyTable(name,?)]: UNIQUE constraint Failed: MyTable._id E/sqliteDatabase(22296): Error inserting name=MyName _id=1 E/sqliteDatabase(22296): android.database.sqlite.sqliteConstraintException: UNIQUE constraint Failed: MyTable._id (code 1555) E/sqliteDatabase(22296): at android.database.sqlite.sqliteConnection.nativeExecuteForLastInsertedRowId(Native Method) E/sqliteDatabase(22296): at android.database.sqlite.sqliteConnection.executeForLastInsertedRowId(sqliteConnection.java:782) E/sqliteDatabase(22296): at android.database.sqlite.sqliteSession.executeForLastInsertedRowId(sqliteSession.java:788) E/sqliteDatabase(22296): at android.database.sqlite.sqliteStatement.executeInsert(sqliteStatement.java:86) E/sqliteDatabase(22296): at android.database.sqlite.sqliteDatabase.insertWithOnConflict(sqliteDatabase.java:1471) E/sqliteDatabase(22296): at android.database.sqlite.sqliteDatabase.insert(sqliteDatabase.java:1341) E/sqliteDatabase(22296): at com.example.antonio.prueba3.MyDB.insertIntoMyTable(MyDB.java:32) E/sqliteDatabase(22296): at com.example.antonio.prueba3.MainActivity.onCreate(MainActivity.java:24) E/sqliteDatabase(22296): at android.app.Activity.performCreate(Activity.java:5990) E/sqliteDatabase(22296): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106) E/sqliteDatabase(22296): at android.app.ActivityThread.performlaunchActivity(ActivityThread.java:2278) E/sqliteDatabase(22296): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2387) E/sqliteDatabase(22296): at android.app.ActivityThread.access$800(ActivityThread.java:151) E/sqliteDatabase(22296): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303) E/sqliteDatabase(22296): at android.os.Handler.dispatchMessage(Handler.java:102) E/sqliteDatabase(22296): at android.os.Looper.loop(Looper.java:135) E/sqliteDatabase(22296): at android.app.ActivityThread.main(ActivityThread.java:5254) E/sqliteDatabase(22296): at java.lang.reflect.Method.invoke(Native Method) E/sqliteDatabase(22296): at java.lang.reflect.Method.invoke(Method.java:372) E/sqliteDatabase(22296): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903) E/sqliteDatabase(22296): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698) E/DB (22296): Connection pool for /data/data/com.example.antonio.prueba3/databases/MyDB: E/DB (22296): Open: true E/DB (22296): Max connections: 1 E/DB (22296): Available primary connection: E/DB (22296): Connection #0: E/DB (22296): connectionPtr: 0xffffffffb883aaf0 E/DB (22296): isPrimaryConnection: true E/DB (22296): onlyAllowReadOnlyOperations: false E/DB (22296): Most recently executed operations: E/DB (22296): 0: [2015-11-13 17:59:16.227] executeForLastInsertedRowId took 1ms - Failed,exception="UNIQUE constraint Failed: MyTable._id (code 1555)" E/DB (22296): 1: [2015-11-13 17:59:16.227] prepare took 0ms - succeeded,?)" E/DB (22296): 2: [2015-11-13 17:59:16.209] executeForLastInsertedRowId took 18ms - succeeded,"1"] E/DB (22296): 3: [2015-11-13 17:59:16.209] prepare took 0ms - succeeded,?)" E/DB (22296): 4: [2015-11-13 17:59:16.208] executeForLong took 1ms - succeeded,sql="PRAGMA user_version;" E/DB (22296): 5: [2015-11-13 17:59:16.208] prepare took 0ms - succeeded,sql="PRAGMA user_version;" E/DB (22296): 6: [2015-11-13 17:59:16.208] executeForString took 0ms - succeeded,sql="SELECT locale FROM android_Metadata UNION SELECT NULL ORDER BY locale DESC LIMIT 1" E/DB (22296): 7: [2015-11-13 17:59:16.207] execute took 1ms - succeeded,sql="CREATE TABLE IF NOT EXISTS android_Metadata (locale TEXT)" E/DB (22296): 8: [2015-11-13 17:59:16.207] executeForLong took 0ms - succeeded,sql="PRAGMA wal_autocheckpoint=100" E/DB (22296): 9: [2015-11-13 17:59:16.207] executeForLong took 0ms - succeeded,sql="PRAGMA wal_autocheckpoint" E/DB (22296): 10: [2015-11-13 17:59:16.207] executeForLong took 0ms - succeeded,sql="PRAGMA journal_size_limit=524288" E/DB (22296): 11: [2015-11-13 17:59:16.207] executeForLong took 0ms - succeeded,sql="PRAGMA journal_size_limit" E/DB (22296): 12: [2015-11-13 17:59:16.206] executeForString took 0ms - succeeded,sql="PRAGMA synchronous" E/DB (22296): 13: [2015-11-13 17:59:16.206] executeForString took 0ms - succeeded,sql="PRAGMA journal_mode=PERSIST" E/DB (22296): 14: [2015-11-13 17:59:16.205] executeForString took 1ms - succeeded,sql="PRAGMA journal_mode" E/DB (22296): 15: [2015-11-13 17:59:16.204] executeForLong took 0ms - succeeded,sql="PRAGMA foreign_keys" E/DB (22296): 16: [2015-11-13 17:59:16.204] executeForLong took 0ms - succeeded,sql="PRAGMA page_size" E/DB (22296): Prepared statement cache: E/DB (22296): 0: statementPtr=0xffffffffb8839558,sql="SELECT locale FROM android_Metadata UNION SELECT NULL ORDER BY locale DESC LIMIT 1" E/DB (22296): Available non-primary connections: E/DB (22296): <none> E/DB (22296): Acquired connections: E/DB (22296): <none> E/DB (22296): Connection waiters: E/DB (22296): <none>
分析输出,我得到一个sqliteConstraintException：
E/sqliteDatabase(22296): android.database.sqlite.sqliteConstraintException: UNIQUE constraint Failed: MyTable._id (code 1555)
在最近执行的操作中：我得到以下信息(成功插入一条记录,另一条记录失败)：
E/DB (22296): 0: [2015-11-13 17:59:16.227] executeForLastInsertedRowId took 1ms - Failed,exception="UNIQUE constraint Failed: MyTable._id (code 1555)" E/DB (22296): 2: [2015-11-13 17:59:16.209] executeForLastInsertedRowId took 18ms - succeeded,"1"]
希望能帮助到你.