ProgrammingError: column "worker_stats__tmp" is of type json but expression is of type text

[Jose C]

Greetings,

My postgresql server was upgraded from 9.1 to 9.3.  Web2py now crashes on startup with this error code:

Traceback (most recent call last):
  File "/home/jose/python-environments/silves/web2py/gluon/restricted.py", line 227, in restricted
    exec ccode in environment
  File "/home/jose/python-environments/silves/web2py/applications/silves/models/5_scheduler.py", line 24, in <module>
    scheduler = Scheduler(db, heartbeat=5 if RUNNING_LIVE else 20)
  File "/home/jose/python-environments/silves/web2py/gluon/scheduler.py", line 587, in __init__
    self.define_tables(db, migrate=migrate)
  File "/home/jose/python-environments/silves/web2py/gluon/scheduler.py", line 679, in define_tables
    migrate=self.__get_migrate('scheduler_worker', migrate)
  File "/home/jose/python-environments/silves/web2py/gluon/packages/dal/pydal/base.py", line 817, in define_table
    table = self.lazy_define_table(tablename,*fields,**args)
  File "/home/jose/python-environments/silves/web2py/gluon/packages/dal/pydal/base.py", line 856, in lazy_define_table
    polymodel=polymodel)
  File "/home/jose/python-environments/silves/web2py/gluon/packages/dal/pydal/adapters/base.py", line 491, in create_table
    fake_migrate=fake_migrate
  File "/home/jose/python-environments/silves/web2py/gluon/packages/dal/pydal/adapters/base.py", line 604, in migrate_table
    self.execute(sub_query)
  File "/home/jose/python-environments/silves/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1326, in execute
    return self.log_execute(*a, **b)
  File "/home/jose/python-environments/silves/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1320, in log_execute
    ret = self.cursor.execute(command, *a[1:], **b)
ProgrammingError: column "worker_stats__tmp" is of type json but expression is of type text
LINE 1: UPDATE scheduler_worker SET worker_stats__tmp=worker_stats;
                                                      ^
HINT:  You will need to rewrite or cast the expression.

Not sure if this is a scheduler issue or pydal issue.  My migrate settings are explicitly set to False in the model definitions.

web2py version: 2.10.4

Suggestions appreciated.

Jose

[Niphlod]

Please drop the scheduler_worker table from the database and remove the file *_scheduler_worker.table from the databases/ folder of your application, then set migrate to True (a single request with migrate=True will suffice). The table will be recreated properly and the error will go away.

The error comes from the fact that the adapter chooses "at connection time" the "nicest" column type to play with based on the backend version. The definition of the scheduler_worker table has a "json" Field, that on postgresql 9.3 maps to a "json" column, while on 9.1 was a "text" column, hence the misbehaviour if you don't drop and re-create the table.

[Jose C]

Please drop the scheduler_worker table from the database and remove the file *_scheduler_worker.table from the databases/ folder of your application, then set migrate to True (a single request with migrate=True will suffice). The table will be recreated properly and the error will go away.

Confirmed that doing the above resolves the problem.  Thanks for your assistance Niphlod, and for the explanation of why it happened.

Jose
 

[Ben Lawrence]

Hi Niphlod,

I deleted all the scheduler tables , created a new database and then rebooted with DAL(..migrate=True..) yet still get this error

column "worker_stats__tmp" is of type json but expression is of type text'

using postgresql 9.4 on raspberry pi.

In http://stackoverflow.com/questions/32501027/casting-text-type-column-to-json-type-in-postgresql

there is a mention of recasting. If this is a solution, would you know how I can incorporate this into web2py?

thanks,

Ben

[Niphlod]

if you get any error with __tmp is because you didn't drop tables on the backend AND .table files before hitting the app with migrate=True.

[Ben Lawrence]

Thanks, I must have got the sequence wrong.

[Ben Lawrence]

OK,

I have two machines, one with web2py+nginx (machine A) and the other with postgresql (machine B)

On machine A:

I check migrate = 0

I delete all the scheduler tables

I check that I deleted all the scheduler tables

On machine B:

I delete all the scheduler tables in postgres

On machine A:

I check that there are no scheduler tables.

I reboot it

I check that there are no scheduler tables

I turn on migrate = 1

There are now scheduler tables.

I go to scheduler tables in web2py admin, scheduler_tasks show new tasks that have since completed,

scheduler_run show new tasks that have completed,

but scheduler_worker has error:

Traceback (most recent call last):
  File "/home/www-data/web2py/applications/temperature/controllers/appadmin.py:select", line 270, in select
  File "/home/www-data/web2py/gluon/packages/dal/pydal/objects.py", line 2045, in select
    return adapter.select(self.query, fields, attributes)
  File "/home/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 746, in select
    return self._select_aux(sql, fields, attributes, colnames)
  File "/home/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 727, in _select_aux
    return processor(rows, fields, colnames, cacheable=cacheable)
  File "/home/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 305, in parse
    for row in rows
  File "/home/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 229, in _parse
    value = self.parse_value(value, fit, ft, blob_decode)
  File "/home/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 196, in parse_value
    return self.parser.parse(value, field_itype, field_type)
  File "/home/www-data/web2py/gluon/packages/dal/pydal/parsers/__init__.py", line 101, in parse
    return self.registered[field_itype](value, field_type)
  File "/home/www-data/web2py/gluon/packages/dal/pydal/parsers/__init__.py", line 76, in __call__
    return self.call(value, field_type)
  File "/home/www-data/web2py/gluon/packages/dal/pydal/parsers/__init__.py", line 73, in _call
    return self.f(self.parser, value)
  File "/home/www-data/web2py/gluon/packages/dal/pydal/parsers/base.py", line 129, in _json
    raise RuntimeError('json data not a string')
RuntimeError: json data not a string

[Ben Lawrence]

forget it, I am going back to mysql. 

[Ramos]

I had the same problem this week and did all 

Please drop the scheduler_worker table from the database and remove the file *_scheduler_worker.table from the databases/ folder of your application, then set migrate to True (a single request with migrate=True will suffice). The table will be recreated properly and the error will go away.

Now i get the error

<class 'psycopg2.ProgrammingError'> relation "scheduler_worker" does not exist

any help?

[Ben Lawrence]

Use mysql, less head-ache for the scheduler.

--
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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/c3DXeJ8aq-Y/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[António Ramos]

I want to keep postgres...

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
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 from it, send an email to web2py+unsubscribe@googlegroups.com.

[Niphlod]

you didn't let migration happen after dropping both the .table files and the underlying tables on the database.

[António Ramos]

Solved. I asked for help from Webfaction and those guys just know a "litle" about web2y...

Thanks