Modify schema structure fails on iOS 14

[JamesMcIntosh]

During opening of the database I alter the original SQL and add foreign keys to the table and encounter a table sqlite_master may not be modified error. The only happens when I run on iOS 14.

I have checked and pragma writable_schema is 1, so my expectation is that the table is writable.

The only similar issue I could find is:
Nozbe/WatermelonDB#772

flutter: error DatabaseException(Error Domain=FMDatabase Code=1 "table sqlite_master may not be modified" UserInfo={NSLocalizedDescription=table sqlite_master may not be modified}) sql 'UPDATE sqlite_master SET sql = replace(sql, 'cloud_file_id INT,', 'cloud_file_id INT REFERENCES cloudfile (id),') WHERE name = 'attachment' AND type = 'table'' args []} during open, closing...

flutter: DatabaseException(Error Domain=FMDatabase Code=1 "table sqlite_master may not be modified" UserInfo={NSLocalizedDescription=table sqlite_master may not be modified}) sql 'UPDATE sqlite_master SET sql = replace(sql, 'cloud_file_id INT,', 'cloud_file_id INT REFERENCES cloudfile (id),') WHERE name = 'attachment' AND type = 'table'' args []}
flutter: #0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
<asynchronous suspension>
#1      SqfliteDatabaseFactoryImpl.wrapDatabaseException (package:sqflite/src/factory_impl.dart:78:7)
#2      SqfliteDatabaseMixin.safeInvokeMethod (package:sqflite_common/src/database_mixin.dart:208:15)
#3      SqfliteDatabaseMixin.txnRawUpdate.<anonymous closure> (package:sqflite_common/src/database_mixin.dart:408:14)
#4      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:312:26)
#5      SqfliteDatabaseMixin.txnWriteSynchronized (package:sqflite_common/src/database_mixin.dart:345:7)
#6      SqfliteDatabaseMixin.txnRawUpdate (package:sqflite_common/src/database_mixin.dart:407:12)
#7      SqfliteDatabaseExecutorMixin._rawUpdate (package:sqflite_common/src/database_mixin.dart:143:15)
#8      SqfliteDatabaseExecutorMixin.rawUpdate (package:sqflite_common/src/database_mixin.dart:135:12)
#9      DatabaseMeta.changeIntToForeignKey (package:sa_jaguar_orm_base/config/DatabaseMeta.dart:202:14)
#10     DatabaseMeta.addForeignKey.<anonymous closure> (package:sa_jaguar_orm_base/config/DatabaseMeta.dart:30:66)
#11     DatabaseMeta.alterDatabaseInPlace.<anonymous closure> (package:sa_jaguar_orm_base/config/DatabaseMeta.dart:114:20)
<asynchronous suspension>
#12     DatabaseMeta.alterDatabaseInPlace.<anonymous closure> (package:sa_jaguar_orm_base/config/DatabaseMeta.dart)
#13     SqfliteDatabaseMixin._runTransaction (package:sqflite_common/src/database_mixin.dart:475:28)
<asynchronous suspension>
#14     SqfliteDatabaseMixin.transaction.<anonymous closure> (package:sqflite_common/src/database_mixin.dart:492:14)
#15     SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:312:26)
#16     SqfliteDatabaseMixin.txnWriteSynchronized (package:sqflite_common/src/database_mixin.dart:345:7)
#17     SqfliteDatabaseMixin.transaction (package:sqflite_common/src/database_mixin.dart:491:12)
#18     DatabaseMeta.alterDatabaseInPlace (package:sa_jaguar_orm_base/config/DatabaseMeta.dart:109:23)
#19     DatabaseMeta.addForeignKey (package:sa_jaguar_orm_base/config/DatabaseMeta.dart:30:13)
<asynchronous suspension>
#20     Version0000.addForeignKeysAndIndexes (package:resolution_app/resolution/db/versions/0000.dart:73:24)
<asynchronous suspension>
#21     Version0000.execute (package:resolution_app/resolution/db/versions/0000.dart:27:11)
#22     BaseDatabaseSetup.onCreate (package:sa_jaguar_orm_base/config/BaseDatabaseSetup.dart:40:25)
<asynchronous suspension>
#23     BaseDatabaseSetup.onOpen (package:sa_jaguar_orm_base/config/BaseDatabaseSetup.dart:23:15)
<asynchronous suspension>
#24     SqfliteDatabaseMixin.doOpen (package:sqflite_common/src/database_mixin.dart:727:29)
<asynchronous suspension>
#25     SqfliteDatabaseOpenHelper.openDatabase (package:sqflite_common/src/database.dart:46:22)
#26     SqfliteDatabaseFactoryMixin.openDatabase.<anonymous closure> (package:sqflite_common/src/factory_mixin.dart:104:43)
<asynchronous suspension>
#27     SqfliteDatabaseFactoryMixin.openDatabase.<anonymous closure> (package:sqflite_common/src/factory_mixin.dart)
#28     ReentrantLock.synchronized.<anonymous closure>.<anonymous closure> (package:synchronized/src/reentrant_lock.dart:35:24)
#29     _rootRun (dart:async/zone.dart:1190:13)
#30     _CustomZone.run (dart:async/zone.dart:1093:19)
#31     _runZoned (dart:async/zone.dart:1630:10)
#32     runZoned (dart:async/zone.dart:1550:10)
#33     ReentrantLock.synchronized.<anonymous closure> (package:synchronized/src/reentrant_lock.dart:34:24)
#34     BasicLock.synchronized (package:synchronized/src/basic_lock.dart:32:26)
#35     ReentrantLock.synchronized (package:synchronized/src/reentrant_lock.dart:30:17)
#36     SqfliteDatabaseFactoryMixin.openDatabase (package:sqflite_common/src/factory_mixin.dart:71:17)
#37     openDatabase (package:sqflite/sqflite.dart:145:26)
#38     DatabaseProvider._createDatabase (package:sa_jaguar_orm_base/DatabaseProvider.dart:76:39)
<asynchronous suspension>
#39     ReentrantLock.synchronized.<anonymous closure>.<anonymous closure> (package:synchronized/src/reentrant_lock.dart:35:24)
#40     _rootRun (dart:async/zone.dart:1190:13)
#41     _CustomZone.run (dart:async/zone.dart:1093:19)
#42     _runZoned (dart:async/zone.dart:1630:10)
#43     runZoned (dart:async/zone.dart:1550:10)
#44     ReentrantLock.synchronized.<anonymous closure> (package:synchronized/src/reentrant_lock.dart:34:24)
#45     BasicLock.synchronized (package:synchronized/src/basic_lock.dart:32:26)
#46     ReentrantLock.synchronized (package:synchronized/src/reentrant_lock.dart:30:17)
#47     DatabaseProvider.adapter (package:sa_jaguar_orm_base/DatabaseProvider.dart:38:19)
#48     UserBean.get (package:resolution_app/resolution/user/model/UserBean.dart:27:70)
#49     AuthenticationServiceImpl.onLogin (package:resolution_app/api/AuthenticationServiceImpl.dart:134:46)
<asynchronous suspension>
#50     BaseHttpApiAction.process.<anonymous closure> (package:api_companion/api/http/BaseHttpApiAction.dart:95:21)
...

[alextekartik]

I was not aware of such function and I considered sqlite_master as a read-only table and that schema should be modified using CREATE TABLE, ALTER TABLE, CREATE TRIGGER...

Personnally I find it scary to modify sqlite_master directly.

sqflite does not do anything special here and uses whatever sqlite version iOS 14 decides to ship so you might get better help asking some iOS 14 forums.

[JamesMcIntosh]

Thanks @alextekartik for your quick reply,

FYI: I follow the instructions from the SQLite alter table page (the 9 step example).
https://sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes
I would love not to but the ORM I use generates the SQL for the tables but doesn't understand foreign keys.

I've had a bit of a dive further down into the stack around SQLite and FMDB and nothing was raising red flags.
The docs did note that the writable_schema pragma may be ignored if the DB connection parameter DBCONFIG_DEFENSIVE has now been set to ON but nobody seems to be setting it.
https://www.sqlite.org/c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive
https://www.sqlite.org/pragma.html#pragma_writable_schema

I'll close the issue and see what I can find out in the iOS ecosystem.

[JamesMcIntosh]

https://sqlite.org/forum/forumpost/2521947534

[JamesMcIntosh]

Hi @alextekartik,

Looks like defensive mode is the culprit. To disable it a connection parameter needs to be passed down to SQLite.
This will require changes in FMDB and SQFlite to set the connection property. I've raised a ticket on FMDB.

ccgus/fmdb#805

[nikopolidi]

Hey guys is there any workaround to make it work on iOS 14? My app stopped working since iOS 14 update

[alextekartik]

I think sqflite could call sqlite3_db_config function directly as a workaround. I already call sqlite3 directly (without FMDB) to turn extended code on. I don't know what is the best way to expose that without introducing a new API though, or at least the easiest to maintain.

[alextekartik]

I published a workaround in in a dev version 1.3.3-dev.1. Since that is not something recommended (doc talks about potential db corruption so you have to know what you are doing), I won't document it yet. The workaround is to execute the following command on iOS 14:
await db.execute('PRAGMA sqflite -- db_config_defensive_off');

The String PRAGMA sqflite -- db_config_defensive_off must be an exact match (including case) as it is a hardcoded comparison. Such method should be ignored on other platform.

Could you let me know if that works for you @nikopolidi @JamesMcIntosh Thanks!

[JamesMcIntosh]

Hi @alextekartik, thanks for the quick solution, sorry for the super-slow reply.

I have given it a test on iOS 14.2 and the upgrade process work as expected.

For my case I'm not too worried but were you going to have a PRAGMA command to flick the switch back off again?

Where the docs talk about potential corruption it is pretty much a disclaimer to think about what you are doing,
i.e. changing a column from a string to an integer or not conforming to SQL syntax would probably end badly...

Many thanks
James

[developer-so]

hi @alextekartik

On MacOS 10.13.6 getting error: use of undeclared identifier 'SQLITE_DBCONFIG_DEFENSIVE'.

Works when I comment this out:

// Handle Hardcoded workarounds
// Handle issue #525
 if ([SqfliteSqlPragmaSqliteDefensiveOff isEqualToString:sql]) {
    sqlite3_db_config(db.sqliteHandle, SQLITE_DBCONFIG_DEFENSIVE, 0, 0);
 }

[JamesMcIntosh]

Hi @developer-so, you should probably be opening a new ticket and mentioning this one as this ticket was closed.

[developer-so]

Hi @JamesMcIntosh,

Ok! I want to know if it's an issue or if it's my setup tho, before opening a new issue.

[JamesMcIntosh]

Hi @developer-so ,

The constant comes directly from SQLite:
https://www.sqlite.org/c3ref/c_dbconfig_defensive.html

It was added in SQlite 3.26.0
https://www.sqlite.org/releaselog/3_26_0.html

Which version of SQLite are you using?

[developer-so]

sqflite: ^2.2.0+3.

[JamesMcIntosh]

@developer-so That is the SQFLite version, not SQLite.
I assume that your app is running against the local version of SQLite installed on your Mac not one included as a dependency.

Try running this query

SELECT sqlite_version() AS version;

[developer-so]

OH! Ok! Never thought of it. Let me check...

[developer-so]

Thanks @JamesMcIntosh,

That was it. Sqlite3 version on the osx was 3.19.3. The error went away when I used the sqflite_common_ffi package. I assume it comes with a later version. However querying SELECT sqlite_version() AS version; still returns version 3.19.3

[JamesMcIntosh]

You're welcome @developer-so

FYI the reason it is working for you is that the "defensive" parameter check is only in this project (sqflite). See:

sqflite/sqflite/macos/Classes/SqfliteDatabase.m

Line 145 in 90f37ea

if ([SqfliteSqlPragmaSqliteDefensiveOff isEqualToString:sql]) {

If you upgrade the version of SQLite you have on your Mac then you would be able to use this project sqflite rather than sqflite_common_ffi. Up to you which better suits you needs.

I would still recommend upgrading SQLite anyway as 3.19.3 was release on 2017-06-08.

[developer-so]

Hmm.. Ok! Tried to upgrade. But eventualy ended up with with workarounds to point to different a version. The app was still reading from the default version on the system.

It's an old mac I'm using as a dev env and I'm at an early stage of dev'ing this app. But this raised the point of safegaurding against external dependencies issues.