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.
On Wednesday, March 16, 2022 at 10:56:33 AM UTC+2 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.
> --
>
> 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.
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails
