Problem with web2py on Heroku

[Chris DeGroot]

Hello;

I have tried everything I could think of to solve this problem, but I just can't find a solution.  I am trying to deploy my application to Heroku, following the instructions in the web2py manual.  The welcome app works fine, so I think generally I have deployed the app properly.  The application works on my local machine.  The error I am getting looks like this:

Traceback (most recent call last):
  File "/app/web2py/gluon/restricted.py", line 217, in restricted
    exec ccode in environment
  File "/app/web2py/applications/CaeSuite/models/db.py", line 16, in <module>
    db = get_db(name=None, pool_size=10)
  File "/app/web2py/gluon/contrib/heroku.py", line 26, in get_db
    current.session.connect(current.request, current.response, db=db)
  File "/app/web2py/gluon/globals.py", line 869, in connect
    migrate=table_migrate,
  File "/app/web2py/gluon/dal.py", line 8223, in define_table
    table = self.lazy_define_table(tablename,*fields,**args)
  File "/app/web2py/gluon/dal.py", line 8260, in lazy_define_table
    polymodel=polymodel)
  File "/app/web2py/gluon/dal.py", line 1102, in create_table
    query), table)
  File "/app/web2py/gluon/dal.py", line 850, in log
    logfile = self.file_open(table._loggername, 'a')
  File "/app/web2py/gluon/dal.py", line 4578, in file_open
    return DatabaseStoredFile(self.db,filename,mode)
  File "/app/web2py/gluon/dal.py", line 4506, in __init__
    self.db.executesql(sql)
  File "/app/web2py/gluon/dal.py", line 8433, in executesql
    adapter.execute(query)
  File "/app/web2py/gluon/dal.py", line 1969, in execute
    return self.log_execute(*a, **b)
  File "/app/web2py/gluon/dal.py", line 1963, in log_execute
    ret = self.cursor.execute(command, *a[1:], **b)
InternalError: current transaction is aborted, commands ignored until end of transaction block

Variables
self.cursor.execute
<built-in method execute of psycopg2._psycopg.cursor object>
self
<gluon.contrib.heroku.HerokuPostgresAdapter object>
self.cursor
<cursor object at 0x287a338; closed: 0>
b
{}
a
('CREATE TABLE IF NOT EXISTS web2py_filesystem (path VARCHAR(255), content TEXT, PRIMARY KEY(path));',)
ret
undefined
command
'CREATE TABLE IF NOT EXISTS web2py_filesystem (path VARCHAR(255), content TEXT, PRIMARY KEY(path));'

I would really appreciate any help!!  Thanks,

Chris

[Chris DeGroot]

The Heroku logs might also help (password removed):

2014-03-13T01:37:17.022191+00:00 heroku[web.1]: Starting process with command `python web2py/web2py.py -a '{PASSWORD}' -i 0.0.0.0 -p 35330`

2014-03-13T01:37:20.361879+00:00 app[web.1]: web2py Web Framework

2014-03-13T01:37:20.361879+00:00 app[web.1]: Version 2.9.3-stable+timestamp.2014.03.03.16.38.00

2014-03-13T01:37:20.362162+00:00 app[web.1]: please visit:

2014-03-13T01:37:20.361879+00:00 app[web.1]: Created by Massimo Di Pierro, Copyright 2007-2014

2014-03-13T01:37:20.361879+00:00 app[web.1]: Database drivers available: SQLite(sqlite3), MySQL(pymysql), PostgreSQL(psycopg2), PostgreSQL(pg8000), IMAP(imaplib)

2014-03-13T01:37:20.362162+00:00 app[web.1]: http://127.0.0.1:35330/

2014-03-13T01:37:20.362283+00:00 app[web.1]: use "kill -SIGTERM 2" to shutdown the web2py server

2014-03-13T01:37:20.555609+00:00 heroku[web.1]: State changed from starting to up

2014-03-13T01:43:05.311936+00:00 app[web.1]: Traceback (most recent call last):

2014-03-13T01:43:05.311936+00:00 app[web.1]:     if db.executesql(query):

2014-03-13T01:43:05.311936+00:00 app[web.1]: ERROR:web2py.dal:Could not retrieve applications/CaeSuite/databases/9e6a7c1bb77d791a89631de258057aed_web2py_session_CaeSuite.table

2014-03-13T01:43:05.311936+00:00 app[web.1]:   File "/app/web2py/gluon/dal.py", line 4560, in exists

2014-03-13T01:43:05.311936+00:00 app[web.1]:   File "/app/web2py/gluon/dal.py", line 8433, in executesql

2014-03-13T01:43:05.311936+00:00 app[web.1]:   File "/app/web2py/gluon/dal.py", line 1969, in execute

2014-03-13T01:43:05.311936+00:00 app[web.1]:     adapter.execute(query)

2014-03-13T01:43:05.311936+00:00 app[web.1]:     return self.log_execute(*a, **b)

2014-03-13T01:43:05.312193+00:00 app[web.1]: 

2014-03-13T01:43:05.311936+00:00 app[web.1]:   File "/app/web2py/gluon/dal.py", line 1963, in log_execute

2014-03-13T01:43:05.312193+00:00 app[web.1]:                          ^

2014-03-13T01:43:05.312193+00:00 app[web.1]: 

2014-03-13T01:43:05.311936+00:00 app[web.1]:     ret = self.cursor.execute(command, *a[1:], **b)

2014-03-13T01:43:05.312193+00:00 app[web.1]: ProgrammingError: relation "web2py_filesystem" does not exist

2014-03-13T01:43:05.312193+00:00 app[web.1]: LINE 1: SELECT path FROM web2py_filesystem WHERE path='applications/...

[Massimo Di Pierro]

Is this is a problem that worked before and then stopped working? Have you changed anything on the database side? Do you have data that you need to recovered.

If at all an option, try delete the database and make a new one. 

Somehow there is a problem with the web2py_filesystem table where web2py stores metadata on platforms that lack a persistent filesystem. This an be recovered but it is complicated to explain without direct access to the system. It really depends on how it got there and what is in there.

Massimo

[Chris DeGroot]

Hi Massimo,

This application worked until I switched to the Postgresql database, so it is definitely a database problem.  Originally I was using the default database, which I guess is file-based, so the data would be lost about once a day when Heroku cleaned out its files.  That was fine for testing, but obviously I need a persistent database for production.  

Right now, I don't care about the data in the database; in fact it should be empty since it has not worked since I changed the database.

I have already tried deleting the database by removing the add-on from Heroku and adding a new one.  According to the Heroku admin panel there are no tables existing in the new database.

I also tried deleting the app from Heroku and deploying it all over again, which also did not work.

Can you elaborate or point to some information on how web2py treats the metadata?  There is no web2py_filesystem in my database.  Can you think of a reason why it would not have been created?

Chris

[Chris DeGroot]

I am looking again at the Heroku logs, specifically the line

ERROR:web2py.dal:Could not retrieve applications/CaeSuite/databases/9e6a7c1bb77d791a89631de258057aed_web2py_session_CaeSuite.table

Why is it looking for a file in */databases? 

Chris

On Thursday, March 13, 2014 10:14:32 AM UTC-4, Massimo Di Pierro wrote:

[Chris DeGroot]

I think I may have made sense of the error above.  Based on the code below, DatabaseStoredFile.web2py_filesystem is set to true even if the table creation fails (which it does), as I don't think the error will be caught before it gets to that line.  A few lines later, it tries to access that table, causing the error logged by Heroku.  Therefore, the key error seems to be that the creation of the web2py_filesystem table is failing.

        if not self.web2py_filesystem:

            if db._adapter.dbengine == 'mysql':

                sql = "CREATE TABLE IF NOT EXISTS web2py_filesystem (path VARCHAR(255), content LONGTEXT, PRIMARY KEY(path) ) ENGINE=InnoDB;"

            elif db._adapter.dbengine in ('postgres', 'sqlite'):

                sql = "CREATE TABLE IF NOT EXISTS web2py_filesystem (path VARCHAR(255), content TEXT, PRIMARY KEY(path));"

            self.db.executesql(sql)

            DatabaseStoredFile.web2py_filesystem = True

[Massimo Di Pierro]

web2py stores metadata about DB tables which have been created. This is necessary for migrations. Normally this metadata is store in *.table files. Heroku has a strange filesystem that gets wiped up at random times. For this reason the *.table files are not in the "normal filesystem". Web2py creates a filesystem in database in a table called "web2py_filesystem" and stores the *.table files in there.

One possible cause of problems is that heroku can have more than one database. When you connect with web2py you need to specify which one. For example:

db = get_db('HEROKU_POSTGRESQL_YELLOW_URL').

If you do not specify web2py may pick up the wrong one.

Massimo

[Zakariya Dehlawi]

I'm having the same issue. I only have one DB associated with my Heroku account, and it's properly being set in get_db(), as it shows up in the stack trace. Heroku says that the DB has no tables in it.

I was curious to see if perhaps there were leftover .table files in the filesystem when I was using sqlite. However, I looked through and did not find any, nor was there a database folder.

Unfortunately with the free tier of the Postgresql addon I haven't figured out how to see the Postgresql error logs. The command you're supposed to use, "heroku logs -p postgres", always comes back empty for me.

This is basically a critical show stopper for me, and I have no idea how to work around it.

Thank you,

Zak

[Louis Amon]

Has anyone found a solution to this ?

I just tried deploying on Heroku today and had the exact same problem.

Heroku logs :

2014-08-04T19:12:36.276606+00:00 app[web.1]:   File "/app/gluon/dal.py", line 4560, in exists

2014-08-04T19:12:36.276608+00:00 app[web.1]:     if db.executesql(query):

2014-08-04T19:12:36.276612+00:00 app[web.1]:   File "/app/gluon/dal.py", line 1969, in execute

2014-08-04T19:12:36.276574+00:00 app[web.1]: ERROR:web2py.dal:Could not retrieve applications/jestocke/databases/d3136d7e8c7d9a81c4da148ca6f4d239_web2py_session_jestocke.table

2014-08-04T19:12:36.276616+00:00 app[web.1]: ProgrammingError: relation "web2py_filesystem" does not exist

2014-08-04T19:12:36.276613+00:00 app[web.1]:     return self.log_execute(*a, **b)

2014-08-04T19:12:36.276619+00:00 app[web.1]:                          ^

2014-08-04T19:12:36.276579+00:00 app[web.1]: Traceback (most recent call last):

2014-08-04T19:12:36.276614+00:00 app[web.1]:   File "/app/gluon/dal.py", line 1963, in log_execute

2014-08-04T19:12:36.276615+00:00 app[web.1]:     ret = self.cursor.execute(command, *a[1:], **b)

2014-08-04T19:12:36.276609+00:00 app[web.1]:   File "/app/gluon/dal.py", line 8433, in executesql

2014-08-04T19:12:36.276618+00:00 app[web.1]: LINE 1: SELECT path FROM web2py_filesystem WHERE path='applications/...

2014-08-04T19:12:36.276610+00:00 app[web.1]:     adapter.execute(query)

[Zakariya Dehlawi]

So I did find a workaround. However, I've switched over to PythonAnywhere, much more seamless to my workflow for Python projects.

The workaround, if I recall correctly, was that I had to use a one-off dynamo to connect to the DB, and manually created the necessary tables based on the Web2Py source code. The most important being the "filesystem" table. I don't remember exactly what I did, as it was a while ago.

Massimo

<pre style="padding:5px;font-family:'Bitstream Vera Sans Mono',monospace;font-size:11px;color:rgb(51,51,51);border-top-left-radius:4px;border-top-right-radius:4px;border-bottom-right-radius:4px;border-bottom-left-radius:4px;line-height:20px;word-break:normal

...