DB Migration (sqlite->pgsql) Problems with 1.99.7 - 2.0.6 - 2.0.7 Versions

[Christian Espinoza]

Hello, I'm in trouble with a webapp that I'have.

I develop it using sqlite only for a best approach of web2py features with it.

And I'm trying to migrate it to a Postgresql DB to put it on Testing phase.

But It doesn't work with PostgreSQL, only work with sqlite, I'd tried with  1.99.7 - 2.0.6 -  2.0.7 Versions

At 2.0.7 and 2.0.6 showme: <type 'exceptions.ValueError'> invalid literal for int() with base 10: 'SELECT'

At 

Traceback (most recent call last):
  File "/opt/web-apps/web2py/gluon/restricted.py", line 209, in restricted
    exec ccode in environment
  File "/opt/web-apps/web2py-2.0.7/applications/sadma/models/db.py", line 236, in <module>
    if db(db.auth_user).isempty():
  File "/opt/web-apps/web2py/gluon/dal.py", line 8722, in isempty
    return not self.select(limitby=(0,1))
  File "/opt/web-apps/web2py/gluon/dal.py", line 8743, in select
    return adapter.select(self.query,fields,attributes)
  File "/opt/web-apps/web2py/gluon/dal.py", line 1583, in select
    return self._select_aux(sql,fields,attributes)
  File "/opt/web-apps/web2py/gluon/dal.py", line 1556, in _select_aux
    self.execute(sql)

My Connection string:

    #db = DAL('sqlite://storage.sqlite')
    db = DAL('postgres://user:pass@localhost/dbapp')

Running on Centos 6.0, external Postgresql Server with 9 version, Python 2.6.6

Thanks in advance

Christian.

[Massimo Di Pierro]

Did you install psycopg2? If not it is probably using pg8000. Can you please help us debug? Edit dal.py and in the function log_excecute can you print the value of the command variable? What does it print when it fails?

[Christian Espinoza]

Hi Massimo, this is:

File /opt/web-apps/web2py/gluon/dal.py in log_execute at line 1653

Code listing

1648.
1649.
1650.
1651.
1652.
1653.

1654.
1655.
1656.
1657.

        command = a[0]

        if self.db._debug:

            logger.debug('SQL: %s' % command)

        self.db._lastsql = command

        t0 = time.time()

        ret = self.cursor.execute(*a, **b)

        self.db._timings.append((command,time.time()-t0))

        del self.db._timings[:-TIMINGSSIZE]

        return ret

Variables

a	('SELECT auth_user.id, auth_user.username, auth_u...r.id > 0) ORDER BY auth_user.id LIMIT 1 OFFSET 0;',)
b	{}
self	<gluon.dal.PostgreSQLAdapter object>
ret	undefined
self.cursor	<gluon.contrib.pg8000.dbapi.CursorWrapper object>
self.cursor.execute	<bound method CursorWrapper._fn of <gluon.contrib.pg8000.dbapi.CursorWrapper object>>

2012/9/5 Massimo Di Pierro <massimo....@gmail.com>

--
 
 
 

[Massimo Di Pierro]

Can you add a 

print command

after

command = a[0]

I want to know what is the invalid command. The ticket only shows part if it.

[Christian Espinoza]

Massimo,

SELECT  auth_user.id, auth_user.username, auth_user.first_name, auth_user.last_name,\

auth_user.sede_id, auth_user.email, auth_user.estado, auth_user.password, auth_user.created_on, auth_user.modified_on, auth_user.registration_key,\

auth_user.reset_password_key, auth_user.registration_id FROM auth_user WHERE (auth_user.id > 0) ORDER BY auth_user.id LIMIT 1 OFFSET 0;

INSERT INTO auth_user(username,first_name,last_name,registration_key,reset_password_key,registration_id,modified_on,created_on,sede_id,password,estado,email)\

VALUES ('11111111-1','admin','admin','','','','2012-09-06 10:36:50','2012-09-06 10:36:50',1,'admin','activo','ad...@live.com');

Thanks in advance

Christian.

2012/9/6 Massimo Di Pierro <massimo....@gmail.com>

--
 
 
 

[Massimo DiPierro]

So, what is wrong with those SQL? I think web2py is generating the correct ones. What am I missing?

[Christian Espinoza]

The last one was with psycopg2, this is the last value of command using  pg8000:

SELECT  auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id > 0) ORDER BY auth_group.id LIMIT 1 OFFSET 0;

The failure appears when I try to init some values in a empty postgresql database, with a empty sqlite db all works fine...

Im using if db(db.auth_group).isempty():

To check if is empty to insert the firsts values...

Christian.

2012/9/6 Christian Espinoza <chesp...@gmail.com>

[Massimo DiPierro]

Again… what is wrong with the generated SQL? I do not know how to fix it if I do not understand why postgres is complaining.

Massimo

[Massimo Di Pierro]

Are you getting errors with both psycopg2 and pg8000? Are these errors new with 2.0.7?

[Christian Espinoza]

I tried with 2.0.7, 2.0.6 and 1.9.7 versions using postgres 9, look at last line below,

on this case I'm checking if the table are empty, when I'm debugging step to step the validation is true, but something fails changing from select to insert I'm think...

this is one of my code:

if db(db.auth_user).isempty():

    db.auth_user.insert(username='11111111-1',first_name='admin',last_name='admin',\

    sede_id=1,email='ad...@live.com',estado='activo',password='admin')

    db.auth_membership.insert(user_id=1,group_id=1)

The same failure appears when I try to do the same with another table:

if db(db.auth_group).isempty():
    db.auth_group.insert(role='admin', description='Usuario - Administrador' )
    db.auth_group.insert(role='jefe_utp', description='Evaluador' )
    db.auth_group.insert(role='evaluador', description='Evaluador' )

<type 'exceptions.ValueError'> invalid literal for int() with base 10: 'SELECT'

 File "C:\Users\christian\Documents\web2py-2.0.7\gluon\restricted.py", line 209, in restricted

    exec ccode in environment
  File "C:/Users/christian/Documents/web2py-2.0.7/applications/sadma/models/db.py", line 236, in <module>

    if db(db.auth_user).isempty():

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\dal.py", line 8723, in isempty

    return not self.select(limitby=(0,1))

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\dal.py", line 8744, in select

    return adapter.select(self.query,fields,attributes)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\dal.py", line 1583, in select

    return self._select_aux(sql,fields,attributes)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\dal.py", line 1556, in _select_aux

    self.execute(sql)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\dal.py", line 1660, in execute

    return self.log_execute(*a, **b)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\dal.py", line 1654, in log_execute

    ret = self.cursor.execute(*a, **b)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\contrib\pg8000\dbapi.py", line 246, in _fn

    return fn(self, *args, **kwargs)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\contrib\pg8000\dbapi.py", line 317, in execute

    self._execute(operation, args)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\contrib\pg8000\dbapi.py", line 322, in _execute

    self.cursor.execute(new_query, *new_args)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\contrib\pg8000\interface.py", line 399, in execute

    self._stmt.execute(*args, **kwargs)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\contrib\pg8000\interface.py", line 176, in execute

    self._fill_cache()

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\contrib\pg8000\interface.py", line 190, in _fill_cache

    end_of_data, rows = self.c.fetch_rows(self._portal_name, self.row_cache_size, self._row_desc)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\contrib\pg8000\protocol.py", line 943, in _fn

    return fn(self, *args, **kwargs)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\contrib\pg8000\protocol.py", line 1249, in fetch_rows

    retval = reader.handle_messages()

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\contrib\pg8000\protocol.py", line 906, in handle_messages

    msg = self._conn._read_message()

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\contrib\pg8000\protocol.py", line 1038, in _read_message

    msg = message_types[message_code].createFromData(bytes)

  File "C:\Users\christian\Documents\web2py-2.0.7\gluon\contrib\pg8000\protocol.py", line 783, in createFromData

    args['rows'] = int(values[-1])

ValueError: invalid literal for int() with base 10: 'SELECT'

Function argument list
(data='SELECT\x00')

Code listing

778.
779.
780.
781.
782.
783.

784.
785.
786.
787.

    def createFromData(data):

        values = data[:-1].split(" ")

        args = {}
        args['command'] = values[0]

        if args['command'] in ("INSERT", "DELETE", "UPDATE", "MOVE", "FETCH", "COPY", "SELECT"):

            args['rows'] = int(values[-1])

            if args['command'] == "INSERT":

                args['oid'] = int(values[1])

        else:
            args['command'] = data[:-1]
Variables

builtinint
<type 'int'>

args
{'command': 'SELECT'}

values
['SELECT']

Context
locals request session response
locals


args
:
{'command': 'SELECT'}
data

:
'SELECT\x00'

values
:

['SELECT']

--
 
 
 

[Massimo DiPierro]

Forget 2.0.6 which is known to be buggy.

Do I understand correctly that you have the problem with both 2.0.7 and 1.99.7?

Can you please try:

db = DAL(….)

db._adapter.check_active_connection = False

[Marin Pranjić]

I have the same problem.
I've never used postgres before, so I could use some help.
I have the same traceback and the same debug output as Christian.
check_active_connection does not help

web2py 2.0.8 stable

Marin

--
 
 
 

[Christian Espinoza]

Hi Marin, are you using Nginx with uwsgi ?

Christian.

2012/9/10 Marin Pranjić <marin....@gmail.com>

--
 
 
 

[Marin Pranjić]

No, I tried with Apache and Rocket.

[Marin Pranjić]

Update: It started working as soon as I installed psycopg2.
This is pg8000 issue.

I am using psql 8.4.11