Duplicate Column in Migration Script
SubutaDan opened this issue · 6 comments
Hi,
This happens from time to time. Eventually it goes away, but I haven't identified what sequence of steps, drawn from:
- restarting the app
- restarting the IDE
- restarting the code generator
- deleting various combinations of .g files in various orders
- flushing the app's local storage
solves it.
Might my use of 'type' as a column name be a problem? I have experimented with changing it, but the results were inconclusive.
Can you provide any guidance?
E/SQLiteLog( 7015): (1) duplicate column name: type in "ALTER TABLE `KokoType` ADD `type` VARCHAR NULL"
E/flutter ( 7015): [ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: DatabaseException(duplicate column name: type (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE `KokoType` ADD `type` VARCHAR NULL) sql 'ALTER TABLE `KokoType` ADD `type` VARCHAR NULL' args []
E/flutter ( 7015): #0 wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #1 BasicLock.synchronized (package:synchronized/src/basic_lock.dart:33:16)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #2 SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:517:14)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #3 AlterColumnHelper.execute (package:brick_sqlite/src/helpers/alter_column_helper.dart:104:7)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #4 SqliteProvider.migrate.<anonymous closure> (package:brick_sqlite/src/sqlite_provider.dart:192:13)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #5 ReentrantLock.synchronized.<anonymous closure> (package:synchronized/src/reentrant_lock.dart:37:18)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #6 BasicLock.synchronized (package:synchronized/src/basic_lock.dart:33:16)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #7 SqliteProvider.migrate (package:brick_sqlite/src/sqlite_provider.dart:190:9)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #8 OfflineFirstRepository.migrate (package:brick_offline_first/src/offline_first_repository.dart:299:12)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #9 OfflineFirstWithRestRepository.migrate (package:brick_offline_first_with_rest/src/offline_first_with_rest_repository.dart:145:5)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #10 OfflineFirstRepository.initialize (package:brick_offline_first/src/offline_first_repository.dart:291:5)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #11 OfflineFirstWithRestRepository.initialize (package:brick_offline_first_with_rest/src/offline_first_with_rest_repository.dart:136:5)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015): #12 _KokosListScreenState.initState.<anonymous closure> (package:kokodoko_flutter_client/screens/kokos_list_screen.dart:77:40)
E/flutter ( 7015): <asynchronous suspension>
E/flutter ( 7015):
Many thanks.
-Dan
This is an annoying error on the part of sqflite. The best thing would be to not create it if the column already exists, but I don't think that's possible. The error appears when we are in development and we have already created the table and initialized the migration. It is saved and for some reason we have to run the migration again due to deleting the migration file among other causes.
What I do is delete the app cache on the development device and verify that the same table is not being created twice in the migration files.
Thanks, @hortigado . I will try your procedure the next time it happens. -Dan
type shouldn't interfere with reserved words since it's wrapped in backticks. Have you verified this column is not being inserted by another migration? This reads like you have some other file that is inserting it. Or the column has not been added to your schema.g.dart
Thanks for the reply, @tshedor .
The column is in schema.g.dart:
final schema =
Schema(20240803213342, generatorVersion: 1, tables: <SchemaTable>{
SchemaTable('KokoType', columns: <SchemaColumn>{
SchemaColumn('_brick_id', Column.integer,
autoincrement: true, nullable: false, isPrimaryKey: true),
SchemaColumn('type', Column.varchar, unique: true)
}, indices: <SchemaIndex>{}),
There is only one migration file:
dan@Dans-MacBook-Air kokodoko_flutter_client % find . -name "*.migration.dart"
./lib/brick/db/20240803213342.migration.dart
dan@Dans-MacBook-Air kokodoko_flutter_client %
Here are its contents:
// GENERATED CODE EDIT WITH CAUTION
// THIS FILE **WILL NOT** BE REGENERATED
// This file should be version controlled and can be manually edited.
part of 'schema.g.dart';
// While migrations are intelligently created, the difference between some commands, such as
// DropTable vs. RenameTable, cannot be determined. For this reason, please review migrations after
// they are created to ensure the correct inference was made.
// The migration version must **always** mirror the file name
const List<MigrationCommand> _migration_20240803213342_up = [
InsertTable('KokoType'),
InsertTable('Koko'),
InsertColumn('type', Column.varchar, onTable: 'KokoType', unique: true),
InsertColumn('koko_id', Column.varchar, onTable: 'Koko', unique: true),
InsertColumn('koko_name', Column.varchar, onTable: 'Koko'),
InsertColumn('main_photo_id', Column.varchar, onTable: 'Koko'),
InsertColumn('main_photo_name', Column.varchar, onTable: 'Koko'),
InsertColumn('longitude', Column.Double, onTable: 'Koko'),
InsertColumn('latitude', Column.Double, onTable: 'Koko'),
InsertColumn('tags', Column.varchar, onTable: 'Koko'),
InsertColumn('created_on', Column.varchar, onTable: 'Koko'),
InsertColumn('description', Column.varchar, onTable: 'Koko'),
InsertColumn('type', Column.varchar, onTable: 'Koko'),
InsertColumn('address', Column.varchar, onTable: 'Koko'),
InsertColumn('location_provenance', Column.integer, onTable: 'Koko'),
InsertColumn('kokoer_id', Column.integer, onTable: 'Koko')
];
const List<MigrationCommand> _migration_20240803213342_down = [
DropTable('KokoType'),
DropTable('Koko'),
DropColumn('type', onTable: 'KokoType'),
DropColumn('koko_id', onTable: 'Koko'),
DropColumn('koko_name', onTable: 'Koko'),
DropColumn('main_photo_id', onTable: 'Koko'),
DropColumn('main_photo_name', onTable: 'Koko'),
DropColumn('longitude', onTable: 'Koko'),
DropColumn('latitude', onTable: 'Koko'),
DropColumn('tags', onTable: 'Koko'),
DropColumn('created_on', onTable: 'Koko'),
DropColumn('description', onTable: 'Koko'),
DropColumn('type', onTable: 'Koko'),
DropColumn('address', onTable: 'Koko'),
DropColumn('location_provenance', onTable: 'Koko'),
DropColumn('kokoer_id', onTable: 'Koko')
];
//
// DO NOT EDIT BELOW THIS LINE
//
@Migratable(
version: '20240803213342',
up: _migration_20240803213342_up,
down: _migration_20240803213342_down,
)
class Migration20240803213342 extends Migration {
const Migration20240803213342()
: super(
version: 20240803213342,
up: _migration_20240803213342_up,
down: _migration_20240803213342_down,
);
}
"type" it is the only column in the table. I don't know whether that is significant. I have just added a second column and am not experiencing the duplicate column issue now. I don't know, however, whether the second column caused the change. The problem seemed to appear and disappear from time to time before I added the column and so may not be resolved now.
Can you think of anything else I should check?
Thanks again for your help.
-Dan
So far the issue has not reoccurred since I added a second column to the table. Shall I close the issue?
Yeah I think this is fine to close. I have a lot of confidence in the migration script and schema architecture, as it's been personally battle tested in production apps for years.