Database created with Python 3.5 cannot be read with Python 2.7

[jasperges]

Continuing from this issue: #43

When first using stalker I followed the tutorial and used Python 3.5. Then later I tried to connect with Python 2.7 but that fails. It seems like it's related to sqlalchemy which uses pickle to dump and load data. In Python 3.5 the default pickle protocol is 4, while on Python 2.7 the highest supported pickle protocol is 2. It seems at least some data is stored with pickle protocol 4.
I didn't have time yet to investigate thoroughly yet.

For complete reference: running in Python 2.7.13 I do the following:

from stalker import db
db.setup()

And this is the error and traceback:

DEBUG:stalker.db:no settings given, using the default: {'sqlalchemy.echo': False, 'sqlalchemy.url': 'postgresql://localhost:5432'}
DEBUG:stalker.db:settings: {'sqlalchemy.echo': False, 'sqlalchemy.url': 'postgresql://localhost:5432'}
DEBUG:stalker.db:engine: Engine(postgresql://localhost:5432)
DEBUG:stalker.db:current_alembic_version: 0063f547dc2e
DEBUG:stalker.db:creating the tables
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-5-c49e9dc02e34> in <module>()
----> 1 db.setup()

/Users/jasperge/dev/stalker/venv-2.7/lib/python2.7/site-packages/stalker-0.2.17.6-py2.7.egg/stalker/db/__init__.pyc in setup(settings)
     76
     77     # update defaults
---> 78     update_defaults_with_studio()
     79
     80     # create repo env variables

/Users/jasperge/dev/stalker/venv-2.7/lib/python2.7/site-packages/stalker-0.2.17.6-py2.7.egg/stalker/db/__init__.pyc in update_defaults_with_studio()
     89         with DBSession.no_autoflush:
     90             from stalker.models.studio import Studio
---> 91             studio = Studio.query.first()
     92             if studio:
     93                 logger.debug('found a studio, updating defaults')

/Users/jasperge/dev/stalker/venv-2.7/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in first(self)
   2688             ret = list(self)[0:1]
   2689         else:
-> 2690             ret = list(self[0:1])
   2691         if len(ret) > 0:
   2692             return ret[0]

/Users/jasperge/dev/stalker/venv-2.7/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in __getitem__(self, item)
   2480                 return list(res)[None:None:item.step]
   2481             else:
-> 2482                 return list(res)
   2483         else:
   2484             if item == -1:

/Users/jasperge/dev/stalker/venv-2.7/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in instances(query, cursor, context)
     88     except Exception as err:
     89         cursor.close()
---> 90         util.raise_from_cause(err)
     91
     92

/Users/jasperge/dev/stalker/venv-2.7/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
    201     exc_type, exc_value, exc_tb = exc_info
    202     cause = exc_value if exc_value is not exception else None
--> 203     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    204
    205 if py3k:

/Users/jasperge/dev/stalker/venv-2.7/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in instances(query, cursor, context)
     73             if single_entity:
     74                 proc = process[0]
---> 75                 rows = [proc(row) for row in fetch]
     76             else:
     77                 rows = [keyed_tuple([proc(row) for proc in process])

/Users/jasperge/dev/stalker/venv-2.7/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in polymorphic_instance(row)
    629             if _instance:
    630                 return _instance(row)
--> 631         return instance_fn(row)
    632     return polymorphic_instance
    633

/Users/jasperge/dev/stalker/venv-2.7/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in _instance(row)
    435             _populate_full(
    436                 context, row, state, dict_, isnew, load_path,
--> 437                 loaded_instance, populate_existing, populators)
    438
    439             if isnew:

/Users/jasperge/dev/stalker/venv-2.7/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc in _populate_full(context, row, state, dict_, isnew, load_path, loaded_instance, populate_existing, populators)
    496
    497         for key, getter in populators["quick"]:
--> 498             dict_[key] = getter(row)
    499         if populate_existing:
    500             for key, set_callable in populators["expire"]:

/Users/jasperge/dev/stalker/venv-2.7/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.pyc in process(value)
   1476                 if value is None:
   1477                     return None
-> 1478                 return loads(value)
   1479         else:
   1480             def process(value):

ValueError: unsupported pickle protocol: 4

[jasperges]

For completeness I also paste @fredrikaverpil's comment here:

@jasperges Am I correct to believe the sqlalchemy/pickle issue when using both Python 2 and 3 > comes down to when you store data using PickleType?:
http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.PickleType

It seems you could define which protocol to use. In our case, perhaps we should look into specifying protocol 2, even when using Python 3?:

PickleType(protocol=2, pickler=None, comparator=None)
protocol – defaults to pickle.HIGHEST_PROTOCOL.

As far as I can see, Stalker currently only uses PickleType for the following:

• WorkingHoursMixin.working_hours
• Studio.last_schedule_message

I don't use this, which is perhaps why I haven't stumbled upon any issues when using both Python 2 and 3 to read/write to/from the same db (?).

Perhaps, in case this is an issue, we could switch out PickleType for something else and create a new alembic revision which will take care of upgrading the db?

[jasperges]

The problem is indeed caused due to the use of PickleType. When you create a studio it gets default working hours, which uses WorkingHoursMixin.working_hours. So if you create a studio in Python 3.5, you can't use the database anymore in Python 2.7. @fredrikaverpil I guess you initialized the database and created a studio in Python 2.7 and after that used the database in Python 3.5. As far as I can see that should work.

As @fredrikaverpil suggested, the fix is quite simple. You can just change PickleType to PickleType(protocol=2) and it works. @eoyilmaz Shall I make a pull request for this?

[fredrikaverpil]

This is correct. I initialized the db using Python 2.x.

About setting the protocol to version 2; Somehow I'm not sure about holding on to old versions is the best solution. Let's discuss. @eoylimaz what do you think?

Is there any chance we can use another data type?

[jasperges]

I do agree the best solution is not to hold on to old versions.

As you suggest another data type is viable option. Or the protocol version could be a setting the user can control somehow. But that might make matters needlessly complicated.

[eoyilmaz]

PickleType is really unnecessary for this data. And because with my next push, Stalker will be mainly using PostgreSQL for its database backend, I think I should feel free to convert the data type to something like JSON.

[fredrikaverpil]

Sounds good to me!

[jasperges]

Perfect. Thank you for looking into this.

[fredrikaverpil]

@eoyilmaz will stalker still work with SQLite?

[fredrikaverpil]

Just answering my own question... yes, it seems SQLite is working just fine with the latest 0.2.18 release 👍

[eoyilmaz]

It may work with SQLite superficially but there are a couple of code paths (generating the whole tjp representation of a project which uses recursive CTE queries, the new ExcludeConstraint in TimeLogs table etc.) that will not work properly.

…

On Mar 13, 2017 9:50 PM, "Fredrik Averpil" ***@***.***> wrote: Just answering my own question... yes, it seems SQLite is working just fine with the latest 0.2.18 release 👍 — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#44 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABtDtO77u3kdaP_r-Y-iSh8fcc0eFUeNks5rlY_xgaJpZM4L-N3q> .

[fredrikaverpil]

Aha. It was actually quite convenient to be able to run a SQLite db for development purposes. But I could spin up a Postgres Docker container instead.

I'm running Postgres 9.1 in production. Would you advise me to upgrade to a newer version due to these new features?

I see the docs still mention SQLite. I'd propose the docs should be updated to reflect these new changes.

On a similar note, I noticed that you can now use SQL for Postgres in Google Cloud. I wish to explore the possibilities to run Stalker either locally (using our own production database) or in the cloud, e.g. using the new SQL for Postgres in GCE. Most features are still in beta, but I'm hoping that it'll be possible to sync a local database with the GCE one somehow (replication?).
The reason behind this is we could then have freelancers work remotely using Stalker and this would be very useful. Have you explored any such options yourself?

[eoyilmaz]

Considering how we connect to a database is to give the username, password and an address, reaching to a database on the internet should not be problem (other than being slow may be).

The main problem should be the pipeline tools, I believe. The tools that freelancers should have installed on their system (i.e. anima for our studio).

[eoyilmaz]

Ahh yes I should update the Docs also yes.

[fredrikaverpil]

Is this still an issue with 0.2.18?

I'm asking because I've been running 0.2.18 in development for quite some time now with Python 3.5.

[jasperges]

Yep, it still is. The studio working hours are stored as the Python class in the database. If it was stored as a json object (or something similar), there would be no need for pickle and the problem would be solved I think.

[eoyilmaz]

Ok, I've released Stalker v0.2.19, but didn't have time to fix this one.
And I plan to fix this, in one of the next couple of commits that I'm planning to do soon.
FYI.

[fredrikaverpil]

Ok, I've released Stalker v0.2.19

Wow, that's an impressive release! 👍
I'm updating here in my dev environment.

[fredrikaverpil]

Just double-checking; the 0.2.19 release doesn't require an alembic upgrade, correct?

[jasperges]

Awesome!
On a sidenote: do you have any news on the development of Stalker Pyramid? Is there any way I can help to make it fully open source?

[eoyilmaz]

Oh, I've completely unnoticed these messages for the last 2 days.

@fredrikaverpil yes it doesn't require an alembic upgrade.

@jasperges Oh boy (sigh!), we're still working on Stalker Pyramid actively (by we I mean me and my wife). The only reason that I do not release the source is that it contains HTML and CSS templates that we've bought (for 15 bucks actually!) and build the code on top of it. And the code in its current form (both the development and master branch) is crap and I don't like it.

On the other hand, for the last 1 and a half year or so I've created another branch called angularjs, where I treated the code like I do in Stalker, so I've beautifully coded it with TDD. What we've planned with this branch is to completely convert the server side code in to a RESTFul service and leave everything else (rendering the HTML templates and running the JavaScript codes) to client side with AngularJS. The server side is 80-90% completed, and I liked it a lot.

But the client side is around %5-10 percent coded. I can release the source code at its current form without the HTML and CSS templates (and probably need to remove a lot of *.js files that comes with the template). And then if you like the idea, you can help us on developing Stalker Pyramid.

[eoyilmaz]

I've forgot to mention that I've fixed this issue in v0.2.20 by properly deriving the WorkingHours class from Entity class. So no need to store it in Studio instance in a PickleType column (and the other PickleType column was the Studio.last_schedule_message which was really unnecessary).

But I don't want to get in to the hassle of writing a proper migration script which extracts the old Pickled WorkingHours data and creates a proper new WorkingHours instance.

So what do you say, do you mind the migration script to throw away the data which is very simple to recreate later on? or should I try to find a way to read the Pickled data and recreate the WorkingHours instances (which will take time) inside the migration script.

[fredrikaverpil]

I've forgot to mention that I've fixed this issue in v0.2.20

Awesome!

So what do you say, do you mind the migration script to throw away the data which is very simple to recreate later on?

We actually don't use this feature of Stalker (not yet, at least) in our studio. So this is fine by me.

[jasperges]

@eoyilmaz Wow, thanks! For me it's also perfectly fine to throw away the data.
And about Stalker Pyramid: I will reply after the weekend. Don't have much time right now.

[eoyilmaz]

This has been fixed in v0.2.20 (fd6f14d)

NOTE: After the alembic upgrade, if there is a Studio instance created before the upgrade, connecting the database will raise an AttributeError due to the missing WorkingHours instance of previously created Studio instance, just ignore it and create a new WorkingHours instance and assign it to the Studio instance. Next time you connected to the database, everything will work fine.

[eoyilmaz]

Because we've started talking about Stalker Pyramid here in this issue, I'm going to send a one final message about it. I've created a new repository for Stalker Pyramid and uploaded the stripped down version. So it doesn't have any closed source code dependency in it.

Now we can start talking about it there (probably in a new issue).