help needed with migration: ProgrammingError: column does not exist

[VP]

Well, I tried to modify my model by changing a column. I am using
postgres. That apparently messed things up. Setting migration to
True does not work.

Viewing the database using psql, I saw that that column was deleted,
but adding the same field (in web2py) does not recreate that column.
Is this a glitch with web2py's migration? Or am I doing something
wrong?

Please help as my app is currently not usable.

[pbreit]

We might need to see your model and then information about your database columns.

I'm just getting ready to switch from development mode to production and am getting worried about managing my Postgres (since now I don't worry about it and just re-create the DB when I make big schema changes).

[villas]

See the book, chapter 6 fixing broken migration. Here is an excerpt,
good luck:

The solution consists of disabling migrations for all tables and
enabling fake migrations:

db.define_table(....,migrate=False,fake_migrate=True)

This will rebuild web2py metadata about the table according to the
table definition. Try multiple table definitions to see which one
works (the one before the failed migration and the one after the
failed migration). Once successful remove the fake_migrate=True
attribute.

Before attempting to fix migration problems it is prudent to make a
copy of "applications/yourapp/databases/*.table" files.

[Alex Glaros]

I have the same problem. Fake migrate didn't work. Tried deleting table in "databases" folder, stopped and started PostGres and w2p servers, etc.

Any ideas I can try?

db = DAL('postgres://postgres:myPasswrod@localhost:5432/postgres', migrate=False,fake_migrate=True )

ProgrammingError: column relationshiptype.relationship_type_name does not exist

    Traceback (most recent call last):
  File "C:\alex\alt_web2py\applications\ES1\controllers/appadmin.py", line 227, in select
    limitby=(start, stop))
  File "C:\alex\alt_web2py\gluon\packages\dal\pydal\objects.py", line 2025, in select
    return adapter.select(self.query,fields,attributes)
  File "C:\alex\alt_web2py\gluon\packages\dal\pydal\adapters\base.py", line 1293, in select
    return self._select_aux(sql,fields,attributes)
  File "C:\alex\alt_web2py\gluon\packages\dal\pydal\adapters\base.py", line 1250, in _select_aux
    self.execute(sql)
  File "C:\alex\alt_web2py\gluon\packages\dal\pydal\adapters\postgres.py", line 360, in execute
    return BaseAdapter.execute(self, *a, **b)
  File "C:\alex\alt_web2py\gluon\packages\dal\pydal\adapters\base.py", line 1385, in execute
    return self.log_execute(*a, **b)
  File "C:\alex\alt_web2py\gluon\packages\dal\pydal\adapters\base.py", line 1379, in log_execute
    ret = self.get_cursor().execute(command, *a[1:], **b)
ProgrammingError: column relationshiptype.relationship_type_name does not exist
LINE 1: SELECT  RelationshipType.id, RelationshipType.relationship_t...

Thanks,

Alex Glaros

[Alex Glaros]

This finally worked.

ALTER TABLE RelationshipType ADD COLUMN relationship_type_name varchar(512);

Can it be assumed that it is generally easier doing DB work on the Postgres side and just leaving w2p set to migrate = False?

Alex

[Antonio Salazar]

As a web2py user with a few months of experience, this is my experience with migrations:

• Most of the time they work, specially if you use PostgreSQL
• Sometimes they won't, and fake_migrate plus the sql log usually help to fix it.
• In very rare occasions, fake_migrate might actually make things worse, like not resizing a char field when you change it in the model.

I think migrations are one of the strengths of web2py, but they're not perfect. You learn to work around what breaks them, and to double check on deployment.

Yes, occasionally you will fix the production database because it didn't migrate smoothly, even if testing did.