No. That suggests you should migrate one field at the time to play it safe. When you are confortable with migrations then you can make more complex migrations.
On Jan 15, 1:19 am, Likit <[email protected]> wrote: > ...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 <[email protected]> > 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 <[email protected]> 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.
