I am not exactly sure what when wrong but I can explain how it works and how to fix it.
Normally web2py looks for .table files for the current table structure. If that does not match the fields in the define table, it tried to alter and add or drop columns accordingly. After migration is completed .tables are overwritten. setting migrate=False causes web2py to ignore .table fiels and not perform the migration. Setting migrate=False and fake_migrate causes web2py to write the .table to match the define_table but not DB Alter table is performed. Often this repairs problems. One typical issue is when changing a column type (NOT YOUR PROBLEM HERE but a recurrent problem for MySQL users). When changing the type of of a column 'x' from 'a' to 'b', web2py creates a new column 'x__tmp' moves the data over, drops 'x' of type 'a', recreates 'x' of type 'b' and moves the back back from 'x__tmp'. Normally this is done in a single transaction. On MySQL (because of its limitations) this is broken into steps each with one transaction. If you try to convert a 'string' field into a 'date' field, the date cannot be ported, the database rollsback some parts of the migration and you are left with a "something__tmp" column in the db. Next time you try migrate, it will give your problems. This did not happen in your case but the effect is similar. You have .table that does not match column content. How to fix it? 1) set fake_migrate = True to rebuild the .table (you did that) most of the time this is sufficient. If not, if the SQL tables do not match the .table 2) set migrate=True, make_migrate=False (as default). 3) edit the .table to match the actual SQL table defintions. the .table files are just pickled dictionaries. Open the one creating problem, delete the dict entry corresponding to the column that id not in the db, pickle it again. On Feb 3, 11:40 am, Ted Baker <ted...@gmail.com> wrote: > I seem to have gotten myself in trouble while trying to upgrade from > web2py1.74.6 to 1.74.9, where I am using postgresql as my database and > apache2 as my front end. I am running the admin directory only through > https. > > Q: If I have an existing database, and start with a clean web2py tree > (with no .../databases/*.tables files), is web2py supposed to be able > to figure out the database scheme, or do I need to copy over the > old .tables files? > > I think I know where I made some mistakes, but I'm still puzzled about > exactly what happened. > > Here is what I did, including known apparent mistakes. Keep in mind > that I'm a novice user of this system, just getting started: > > 1) Renamed the old Apache DocumentRoot directory > 2) Fetched and unzipped web2py, and renamed the web2py directory as > DocumentRoot. > 3) Edited the db.py files to refer to my existing postgresql database, > and use the existing userid and password. > 4) Restarted apache2. > > Note: I skipped the "python web2py.py" step, which may have been my > first mistake. > > Web2py tried to start, but died because of a typo in the code of gluon/ > contrib/cron.py, where "starup" was written in place of "startup". > (Apparently, that code had not been tested since it was last > modified. I guess from this that I already had managed to provoke > execution of some unusual control path.) I corrected the spelling > error, and restarted Apache. > > Web2py started up again, and tried to write into a bunch of > directories as it came up. > Apache did not have permission to do so, because I had restricted the > "web2py" user's permissions > to only be able to write into a few directories (errors, cache, > sessions, databases), and it had been working that way for a while > because the other files that web2py writes were written before I > changed the permissions. I had restricted permissions because I do not > like the idea of trusting code to be able to write over other code, > including the entire DocumentRoot. In fact, I'd really like to be > able to put all of the Python code and static web pages into one read- > execute-only directory. > > I started out to work around this by trying to modify permissions on > files and directories, one a time, to find out the minimum writable > set. I finally got tired of this, and changed group ownership of the > entire DocumentRoot tree to allow web2py write access to everything. > That solved the file permissions problem. > > About this time I realized that I should have run "python web2py.py" > once from the shell to set the password. > I did that. > > From that point on I had problems with the database files. Web2py > tried to re-create my tables, but of course postgresql refused, since > the tables already existed. > > Massimo recommended that I "try migrate=False, fake_migrate=True". I > inferred that this referred to the place where the runtime error was > coming from. Tracing the calls back, I figured I should provide the > parameters at the original point of call, and so made the changes to > the calls to define_table() in ../examples/models/db.py. I was not > sure what to do about ../welcome/models/db.py, which uses tools/ > define_tables(), which in turn calls define_table(). Initially, I > simply changed to "migrate=False", but this did not work, so finally > ended up with adding "fake_migrate=True" to all the calls to > define_table() in define_tables(). I restarted Apache again, and > tested. > > At this point, there are no error messages in the Apache log, or in > the ./errors/ directories of web2py, and I can view the welcome page, > but when I try to access the administrative tools I get "admin > disabled because unable to access password file". If I look at > applications/welcome/databases/*auth_user.table, I see what looks like > the new table structure, but my own postgresql table does not have all > those fields. For example, it is missing field reset_password_key. > > If I try the ordinary login option from the Welcome page, I do get a > web2py error. The file in ../welcome/errors has > > in response\n db._execute(query)\n File "gluon/sql.py", line 953, > in <lambda>\n self._execute = la > mbda *a, **b: self._cursor.execute(*a, **b)\nProgrammingError: column > auth_user.reset_password_key does not > exist at character 130\n\n > > This is entirely consistent with my observation above, that the .table > file and the database structure do not match. > > Ted -- You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to web...@googlegroups.com. To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
