Yarko,
deep down in the reference manual of mysql 5.1 I've found the reason
for the issue, http://dev.mysql.com/doc/refman/5.1/en/join.html.
Basically as of mysql version 5.0.12 there where some changes
introduced which are not backward compatible (on older mysql versions
the select/joins work on newer I get the described error). The change
was caused by mysql moving to SQL 2003 compliance.
Thanks god the docu and manuals of mysql are very good and it was easy
to re-write the select command in order to work on SQL 2003 based on
the information in the docu. Basically it was down to avoiding the
comma join which causes in combination with ON joins headache as of
SQL 2003.
In my case the solution looks like:
records=SQLTABLE(db(db.model_master.id==mid).select(
db.model_master.code,
db.subsidary.name,
db.ipst.id,
ips1.id,
ips2.id,
ips3.id,
ips4.id,
ips5.id,
left=[db.subsidary.on(db.subsidary.id>0),
db.ipst.on(rows_ipst),
ips1.on(rows_ips1),
ips2.on(rows_ips2),
ips3.on(rows_ips3),
ips4.on(rows_ips4),
ips5.on(rows_ips5)
],
orderby=db.model_master.code|db.subsidary.line),
headers=headers)
Here db.subsidary is not a comma join any more but a join with ON
clause - making it SQL 2003 compliant.
Hans
On Apr 29, 4:42 pm, Yarko Tymciurak <yark...@gmail.com> wrote:
> I'd be interested to hear a little more about what the mysql issue is...
> (I don't use, but am/will be forced to on one work server, so want to hear
> what others are going thru)
>
> Thanks,
> - Yarko
>
> 2009/4/29 mdipierro <mdipie...@cs.depaul.edu>
>
>
>
> > there lots of mysql issues unfortunately.
>
> > On Apr 29, 7:08 am, Hans <johann.scheibelho...@easytouch-edv.com>
> > wrote:
> > > Update: I've found that its a mysql issue not a web2py one.
>
> > > On Apr 28, 4:52 pm, Hans <johann.scheibelho...@easytouch-edv.com>
> > > wrote:
>
> > > > I'm experiencing a strange behavior within the code below. I tried to
> > > > split the problem in the smallest pieces which I'm able to debug. My
> > > > interpretation of the findings is that most likely I run into a web2py
> > > > internal variable length issue/limitation. Let me tell you why I think
> > > > that:
>
> > > > The tables ips1, ips2, ips3, ips4 and ips5 are identical aliases of
> > > > db.ipst.
> > > > There code works without error like it is below (watch the 3 commented
> > > > lines!).
> > > > The code also works without error if I comment out *ANY 3* out of the
> > > > db.ipst.id and its 5 aliases.
> > > > Each code line below (also the 3 commented ones) works without error
> > > > as long as any 3 other lines from db.ipst.id and its 5 aliases are
> > > > commented out - all combinations work.
> > > > The problem only occurs if I have less than 3 of the lines below
> > > > commented out.
>
> > > > Unfortunately I'm not able to debug into web2py framework code - and
> > > > despite hours of finding a solution for that I could not find one. And
> > > > based on the tools I have I'm at the end of all ideas I had.
>
> > > > If it helps I can also send you login information (and instructions to
> > > > get to the function with the issue) for the development server via
> > > > personal email upon request.
>
> > > > I'm using MySQL and Web2Py 1.61.1.
>
> > > > records=SQLTABLE(db(db.model_master.id==mid).select(
> > > > db.model_master.code,
> > > > db.subsidary.name,
> > > > db.ipst.id,
> > > > ips1.id,
> > > > # ips2.id,
> > > > # ips3.id,
> > > > # ips4.id,
> > > > ips5.id,
> > > > left=[db.ipst.on(rows_ipst),
> > > > ips1.on(rows_ips1),
> > > > ips2.on(rows_ips2),
> > > > ips3.on(rows_ips3),
> > > > ips4.on(rows_ips4),
> > > > ips5.on(rows_ips5)
> > > > ],
> > > > orderby=db.model_master.code|db.subsidary.line),
> > > > headers=headers)
>
> > > > Error traceback
>
> > > > Traceback (most recent call last):
> > > > File "/usr/lib/web2py/gluon/restricted.py", line 98, in restricted
> > > > exec ccode in environment
> > > > File "/usr/lib/web2py/applications/ips/controllers/default.py", line
> > > > 657, in <module>
> > > > File "/usr/lib/web2py/gluon/globals.py", line 75, in <lambda>
> > > > self._caller = lambda f: f()
> > > > File "/usr/lib/web2py/applications/plugin_t2/modules/t2.py", line
> > > > 1188, in h
> > > > return f(*a,**b)
> > > > File "/usr/lib/web2py/applications/ips/controllers/default.py", line
> > > > 537, in fcc
> > > > orderby=db.model_master.code|db.subsidary.line),
> > > > File "/usr/lib/web2py/gluon/sql.py", line 1977, in select
> > > > r = response(query)
> > > > File "/usr/lib/web2py/gluon/sql.py", line 1972, in response
> > > > self._db._execute(query)
> > > > File "/usr/lib/web2py/gluon/sql.py", line 691, in <lambda>
> > > > self._execute = lambda *a, **b: self._cursor.execute(*a,**b)
> > > > File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line
> > > > 166, in execute
> > > > self.errorhandler(self, exc, value)
> > > > File "/var/lib/python-support/python2.5/MySQLdb/connections.py",
> > > > line 35, in defaulterrorhandler
> > > > raise errorclass, errorvalue
> > > > OperationalError: (1054, "Unknown column 'subsidary.id' in 'on
> > > > clause'")
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
