...which I did...
Ummm... that sort of suggests I might as well ALWAYS set
migrate=False in my DAL connector for MySQL?
I don't mind--it's not too hard to make the changes and it helps focus
on db design.
Sounds like Postgres is a better longterm choice.
On Jan 14, 10:39 pm, Massimo Di Pierro <massimo.dipie...@gmail.com>
wrote:
> Mysql does not support multiple alter table in one transaction that is
> why it is possible for mysql to get into this state. notice this
> cannot happen with postgresql which perform the entire migration in
> one transaction.
>
> You need to manually ALTER TABLE and DELETE joke_id__tmp
>
> On Jan 14, 10:02 pm, Likit <lewis_le...@hotmail.com> wrote:
>
>
>
>
>
>
>
> > I am trying to use the many-2-many DAL syntax suggested by the manual
> > and the examples. In this approach, a field name is associated with a
> > TABLE name.
>
> > I have not been able to get this to work with MySQL. So, I have used
> > the more conventional approach of using an integer field to hold the
> > key value from the relationship table. Instead of dogs and persons I
> > have jokes and categories, but it's the same thing. The goal is to be
> > able to express relations for jokes with no category, jokes with one
> > or more categories, categories with one or more jokes, and categories
> > with no jokes. The ideal query returns all of these, if they exist.
>
> > Here is the model for my "conventional" approach:
>
> > jodb.define_table('joke',
> > Field('joketext', 'text',length=2048),
> > Field('created_on', 'datetime', default=request.now),
> > Field('created_by', jodb.auth_user, default=auth.user_id))
>
> > jodb.define_table('category',
> > Field('name', 'text'))
>
> > jodb.define_table('joke_category',
> > Field('joke_id', 'integer'),
> > Field('category_id', 'integer'))
>
> > jodb.category.name.requires = IS_NOT_EMPTY()
> > jodb.joke.joketext.requires = IS_NOT_EMPTY()
>
> > All was good except I couldn't get the query to work using the persons/
> > dogs many-to-many approach. I was not getting jokes with no
> > categories and categories with no jokes. So, I decided to follow the
> > manual more closely to see if I could try the queries from the
> > example.
>
> > Thus, I tried to change the field definitions in the relations table
> > to associate the id field with a TABLE as in:
>
> > jodb.define_table('joke_category',
> > Field('joke_id', jodb.joke),
> > Field('category_id', 'integer'))
>
> > Yes, I would need to do the same for category, but I wanted to do one
> > at a time because I had read somewhere that MySQL migrations did not
> > work with multiple pending changes (don't know if that is really
> > true...). Anyway, seemed easier to do one at a time. So, I made the
> > immediately preceding change in the model. In attempting to do the
> > migration, MySQL got an error 150. When this happens, either web2py or
> > MySQL will hang. So, I stopped everything.
>
> > Upon resuming everything, I tried to go back to the original way I had
> > it (above). This appears to cause a second migration with the
> > following result:
>
> > 127.0.0.1.2012-01-14.19-48-00.189cb495-7851-429d-a0cb-bd23156431f1
>
> > <class 'gluon.contrib.pymysql.err.InternalError'> (1060, u"Duplicate
> > column name 'joke_id__tmp'")
> > VERSION
>
> > web2py™ (1, 99, 4, datetime.datetime(2011, 12, 14, 14, 46, 14),
> > 'stable')
> > Python
> > TRACEBACK
>
> > Traceback (most recent call last):
> > File "c:\web2py\gluon\restricted.py", line 204, in restricted
> > exec ccode in environment
> > File "c:/web2py/applications/pyjokes/models/db.py", line 55, in
> > <module>
> > Field('category_id', 'integer'))
> > File "c:\web2py\gluon\dal.py", line 5097, in define_table
> > polymodel=polymodel)
> > File "c:\web2py\gluon\dal.py", line 728, in create_table
> > fake_migrate=fake_migrate)
> > File "c:\web2py\gluon\dal.py", line 816, in migrate_table
> > self.execute(sub_query)
> > File "c:\web2py\gluon\dal.py", line 1359, in execute
> > return self.log_execute(*a, **b)
> > File "c:\web2py\gluon\dal.py", line 1353, in log_execute
> > ret = self.cursor.execute(*a, **b)
> > File "c:\web2py\gluon\contrib\pymysql\cursors.py", line 108, in
> > execute
> > self.errorhandler(self, exc, value)
> > File "c:\web2py\gluon\contrib\pymysql\connections.py", line 184, in
> > defaulterrorhandler
> > raise errorclass, errorvalue
> > InternalError: (1060, u"Duplicate column name 'joke_id__tmp'")
>
> > Note the duplicate column name. It seems that the first migration
> > created a tmp field to move the column values over to the newly
> > created column. This hung, but the tmp column must have been
> > created. The second migration--to get back (which I erroneously
> > thought wouldn't happen at all, assuming the first migration had
> > failed completely--clearly needed to create the second tmp column,
> > which was a dupe.
>
> > I can get myself back to the "conventional" approach working.
>
> > So, my real question is: how can I formulate the query that returns
> > all jokes and categories with their relations including no
> > corresponding joke or category? Should I do this using the
> > "conventional' way or should I use the suggested web2py model
> > syntax.
>
> > I know--a long question for what is probably a shorter answer.
>
> > Many thanks.
