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.
