I was a bit confused about the explanation of the fake_migrate option in the web2py documentation. After some experimenting, I wrote up the following information for myself, just sharing it here with you. Maybe someone can merge this information in the web2py documentation, if you think it contributes to making this topic more clear.
The explanation here assumes sqlite as a database, but I believe holds also for other databases.
When changing a model, e.g. adding a field there are two places where information is updated.
The database parameter migrate (=True by default) pushes changes that are made in your model back into the meta-data file and in the sqlite file.
Another option fake_migrate (=False by default) pushes changes that are made in your model back into the meta-data file, but not in the sqlite database file.
Note also that this option will only push new Fields back into the meta-data file. It does not remove fields from the metadata file that you may have removed in your model!!
For each field described in your model (and thus also in the metadata file), you should have a corresponding element in the sqlite schema.
If that is not the case, you can have one of two errors:Error 1:
The field is present in the sqlite database, it also exists in your model file, but for some reason it does not exist in the metadata file. Because it is defined in the model and it is not yet present in the metadata file, web2py wants to create it by doing a migrate.
This migrate, includes updating both the metadata file and the sqlite schema. The problem however, is that in the sqlite schema, this field already exists!
This will cause an error similar as:
sqlite3.OperationalError: duplicate column name: blablablaSolution
We can fix this by telling web2py to do a 'fake_migrate'.
This is done by adding the parameter fake_migrate=True in the table definition.
By doing a fake migrate, web2py will only update the metadata file and NOT the sqlite database. As such, the fields in the database and in the metadata file will get back in sync.
It is important to remove the fake_migrate parameter again after the data sources are back in sync!
The field is in the metadata file, it also exists in your model file, but for some reason it does not exist in the sqlite schema. Because it is defined in the model and in the metadata file, web2py will try to load this field from the database.
But because it is not defined in the database, you will get an error such as:
sqlite3.OperationalError: no such column: person.blablablaSolution 2:
In this case we cannot do the fake-migrate directly, because the fake-migrate option can add the new fields to the metadata file, but it won't push the field to the database. So what we should do is:
First remove again the field in question from our model.
Set the fake_migrate=True option
Remove the meta-data file (keep a backup of it elsewhere, just to make sure)
Reload the database (e.g. by doing a screen refresh in the page where you use the table in question)... this will recreate the meta-data file based on your current model.
Set fake_migrate=False (or just remove it, as False is the default value)
Put back the field in question in the model
Reload the database once more
By reloading the database, web2py will now push the new field again in the metadatafile and it will also update the sqlite schema. Now all data is back in sync.
Sidenote: Why you should not have fake_migrate=True in a normal situation
If you would leave fake_migrate=True, you would actually cause Error 2 above to happen when you add a new field to your table, because in that case the new field
is only committed to the metadata-file and not to the sqlite database.