About migrate and fake_migrate

[Tom Clerckx]

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.

• In the database-metadata files. These files are located in the database folder. For each table a metadata file is created. If you want to inspect these files, you can inspect them by unpickling them

• In the database.sqlite file. This is the database itself.The new field will be added to the sqlite schema

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: blablabla

Solution:

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!

Error 2:

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.blablabla

Solution 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:

1. First remove again the field in question from our model.
2. Set the fake_migrate=True option
3. Remove the meta-data file (keep a backup of it elsewhere, just to make sure)
4. 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.
5. Set fake_migrate=False (or just remove it, as False is the default value)
6. Put back the field in question in the model
7. Reload the database once more
8. 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.

[Dave S]

On Wednesday, March 16, 2022 at 1:56:33 AM UTC-7 Tom Clerckx wrote:

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.

--

I have to [sigh] relearn this frequently.   I am finally beginning to take to heart your last comment below.

/dps

 

[mostwanted]

Tom Thank you, i will re-read this and come back to it when i encounter one of these because they used to taunt me alot, i cant count the number of times I deleted & re-created & re-named a table because of this error.