After upgrading SQLAlchemy from 0.7.8 to 0.9.4, we got the following
exception:
Traceback (most recent call last):
File "/opt/2.0/flx/pylons/flx/flx/controllers/assignment.py", line 1824,
in getGroupAssignments
pageSize=pageSize)
File "/opt/2.0/flx/pylons/flx/flx/model/api.py", line 13638, in
_getAssignmentsByCreatorID
page = p.Page(query, pageNum, pageSize)
File "/opt/2.0/flx/pylons/flx/flx/model/page.py", line 53, in __init__
Subset.__init__(self, query, offset, self.size)
File "/opt/2.0/flx/pylons/flx/flx/model/page.py", line 9, in __init__
self.results = query.offset(offset).limit(limit).all()
File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py",
line 2292, in all
return list(self)
File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py",
line 2404, in __iter__
return self._execute_and_instances(context)
File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py",
line 2419, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py",
line 720, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/sql/elements.py",
line 317, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py",
line 817, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py",
line 947, in _execute_context
context)
File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py",
line 1108, in _handle_dbapi_exception
exc_info
File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/util/compat.py",
line 185, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py",
line 940, in _execute_context
context)
File
"/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/default.py", line
435, in do_execute
cursor.execute(statement, parameters)
File
"/usr/local/lib/python2.6/dist-packages/MySQL_python-1.2.3-py2.6-linux-x86_64.egg/MySQLdb/cursors.py",
line 174, in execute
self.errorhandler(self, exc, value)
File
"/usr/local/lib/python2.6/dist-packages/MySQL_python-1.2.3-py2.6-linux-x86_64.egg/MySQLdb/connections.py",
line 36, in defaulterrorhandler
raise errorclass, errorvalue
InternalError: (InternalError) (126, "Incorrect key file for table
'/tmp/#sql_52d_2.MYI'; try to repair it") 'SELECT
substring_index(`Artifacts`.name, %s, %s) AS `sortableName`, `Artifacts`.id
AS `Artifacts_id`, `Artifacts`.`artifactTypeID` AS
`Artifacts_artifactTypeID`, `Artifacts`.`encodedID` AS
`Artifacts_encodedID`, `Artifacts`.name AS `Artifacts_name`,
`Artifacts`.description AS `Artifacts_description`, `Artifacts`.handle AS
`Artifacts_handle`, `Artifacts`.`creatorID` AS `Artifacts_creatorID`,
`Artifacts`.`ancestorID` AS `Artifacts_ancestorID`, `Artifacts`.`licenseID`
AS `Artifacts_licenseID`, `Artifacts`.`creationTime` AS
`Artifacts_creationTime`, `Artifacts`.`updateTime` AS
`Artifacts_updateTime`, `Licenses_1`.id AS `Licenses_1_id`,
`Licenses_1`.name AS `Licenses_1_name`, `Licenses_1`.description AS
`Licenses_1_description`, `ArtifactTypes_1`.id AS `ArtifactTypes_1_id`,
`ArtifactTypes_1`.name AS `ArtifactTypes_1_name`,
`ArtifactTypes_1`.`extensionType` AS `ArtifactTypes_1_extensionType`,
`ArtifactTypes_1`.description AS `ArtifactTypes_1_description`,
`ArtifactTypes_1`.modality AS `ArtifactTypes_1_modality` \nFROM
`Artifacts`, (SELECT anon_2.`sortableName` AS `sortableName`, anon_2.id AS
id, anon_2.`artifactTypeID` AS `artifactTypeID`, anon_2.`encodedID` AS
`encodedID`, anon_2.name AS name, anon_2.description AS description,
anon_2.handle AS handle, anon_2.`creatorID` AS `creatorID`,
anon_2.`ancestorID` AS `ancestorID`, anon_2.`licenseID` AS `licenseID`,
anon_2.`creationTime` AS `creationTime`, anon_2.`updateTime` AS
`updateTime` \nFROM (SELECT substring_index(`Artifacts`.name, %s, %s) AS
`sortableName`, `Artifacts`.id AS id, `Artifacts`.`artifactTypeID` AS
`artifactTypeID`, `Artifacts`.`encodedID` AS `encodedID`, `Artifacts`.name
AS name, `Artifacts`.description AS description, `Artifacts`.handle AS
handle, `Artifacts`.`creatorID` AS `creatorID`, `Artifacts`.`ancestorID` AS
`ancestorID`, `Artifacts`.`licenseID` AS `licenseID`,
`Artifacts`.`creationTime` AS `creationTime`, `Artifacts`.`updateTime` AS
`updateTime` \nFROM `Artifacts` \nWHERE `Artifacts`.`creatorID` = %s AND
`Artifacts`.`artifactTypeID` = %s AND `Artifacts`.id NOT IN (%s, %s) ORDER
BY `Artifacts`.id DESC) AS anon_2 UNION ALL SELECT anon_3.`sortableName` AS
`sortableName`, anon_3.id AS id, anon_3.`artifactTypeID` AS
`artifactTypeID`, anon_3.`encodedID` AS `encodedID`, anon_3.name AS name,
anon_3.description AS description, anon_3.handle AS handle,
anon_3.`creatorID` AS `creatorID`, anon_3.`ancestorID` AS `ancestorID`,
anon_3.`licenseID` AS `licenseID`, anon_3.`creationTime` AS `creationTime`,
anon_3.`updateTime` AS `updateTime` \nFROM (SELECT
substring_index(`Artifacts`.name, %s, %s) AS `sortableName`, `Artifacts`.id
AS id, `Artifacts`.`artifactTypeID` AS `artifactTypeID`,
`Artifacts`.`encodedID` AS `encodedID`, `Artifacts`.name AS name,
`Artifacts`.description AS description, `Artifacts`.handle AS handle,
`Artifacts`.`creatorID` AS `creatorID`, `Artifacts`.`ancestorID` AS
`ancestorID`, `Artifacts`.`licenseID` AS `licenseID`,
`Artifacts`.`creationTime` AS `creationTime`, `Artifacts`.`updateTime` AS
`updateTime` \nFROM `Artifacts` INNER JOIN `Assignments` ON
`Assignments`.`assignmentID` = `Artifacts`.id AND `Assignments`.`groupID` =
%s \nWHERE `Artifacts`.`creatorID` = %s AND `Artifacts`.`artifactTypeID` =
%s ORDER BY isnull(`Assignments`.due), `Assignments`.due ASC) AS anon_3) AS
anon_1 LEFT OUTER JOIN `Licenses` AS `Licenses_1` ON `Licenses_1`.id =
anon_1.`licenseID` LEFT OUTER JOIN `ArtifactTypes` AS `ArtifactTypes_1` ON
`ArtifactTypes_1`.id = anon_1.`artifactTypeID` ORDER BY
`Artifacts`.`artifactTypeID` DESC \n LIMIT %s, %s' ('-::of::-', 1,
'-::of::-', 1, 12L, 55, 1444153L, 1444204L, '-::of::-', 1, '3021', 12L, 56,
0, 10)
So we downgraded it to 0.8.6 and it worked again. The query generated was
different from that from 0.9.4:
SELECT anon_1.`sortableName` AS `anon_1_sortableName`, anon_1.id AS
anon_1_id, anon_1.`artifactTypeID` AS `anon_1_artifactTypeID`,
anon_1.`encodedID` AS `anon_1_encodedID`, anon_1.name AS anon_1_name,
anon_1.description AS anon_1_description, anon_1.handle AS anon_1_handle,
anon_1.`creatorID` AS `anon_1_creatorID`, anon_1.`ancestorID` AS
`anon_1_ancestorID`, anon_1.`licenseID` AS `anon_1_licenseID`,
anon_1.`creationTime` AS `anon_1_creationTime`, anon_1.`updateTime` AS
`anon_1_updateTime`, `Licenses_1`.id AS `Licenses_1_id`, `Licenses_1`.name
AS `Licenses_1_name`, `Licenses_1`.description AS `Licenses_1_description`,
`ArtifactTypes_1`.id AS `ArtifactTypes_1_id`, `ArtifactTypes_1`.name AS
`ArtifactTypes_1_name`, `ArtifactTypes_1`.`extensionType` AS
`ArtifactTypes_1_extensionType`, `ArtifactTypes_1`.description AS
`ArtifactTypes_1_description`, `ArtifactTypes_1`.modality AS
`ArtifactTypes_1_modality` FROM (SELECT anon_2.`sortableName` AS
`sortableName`, anon_2.id AS id, anon_2.`artifactTypeID` AS
`artifactTypeID`, anon_2.`encodedID` AS `encodedID`, anon_2.name AS name,
anon_2.description AS description, anon_2.handle AS handle,
anon_2.`creatorID` AS `creatorID`, anon_2.`ancestorID` AS `ancestorID`,
anon_2.`licenseID` AS `licenseID`, anon_2.`creationTime` AS `creationTime`,
anon_2.`updateTime` AS `updateTime` FROM (SELECT
substring_index(`Artifacts`.name, %s, %s) AS `sortableName`, `Artifacts`.id
AS id, `Artifacts`.`artifactTypeID` AS `artifactTypeID`,
`Artifacts`.`encodedID` AS `encodedID`, `Artifacts`.name AS name,
`Artifacts`.description AS description, `Artifacts`.handle AS handle,
`Artifacts`.`creatorID` AS `creatorID`, `Artifacts`.`ancestorID` AS
`ancestorID`, `Artifacts`.`licenseID` AS `licenseID`,
`Artifacts`.`creationTime` AS `creationTime`, `Artifacts`.`updateTime` AS
`updateTime` FROM `Artifacts` WHERE `Artifacts`.`creatorID` = %s AND
`Artifacts`.`artifactTypeID` = %s AND `Artifacts`.id NOT IN (%s, %s) ORDER
BY `Artifacts`.id DESC) AS anon_2 UNION ALL SELECT anon_3.`sortableName` AS
`sortableName`, anon_3.id AS id, anon_3.`artifactTypeID` AS
`artifactTypeID`, anon_3.`encodedID` AS `encodedID`, anon_3.name AS name,
anon_3.description AS description, anon_3.handle AS handle,
anon_3.`creatorID` AS `creatorID`, anon_3.`ancestorID` AS `ancestorID`,
anon_3.`licenseID` AS `licenseID`, anon_3.`creationTime` AS `creationTime`,
anon_3.`updateTime` AS `updateTime` FROM (SELECT
substring_index(`Artifacts`.name, %s, %s) AS `sortableName`, `Artifacts`.id
AS id, `Artifacts`.`artifactTypeID` AS `artifactTypeID`,
`Artifacts`.`encodedID` AS `encodedID`, `Artifacts`.name AS name,
`Artifacts`.description AS description, `Artifacts`.handle AS handle,
`Artifacts`.`creatorID` AS `creatorID`, `Artifacts`.`ancestorID` AS
`ancestorID`, `Artifacts`.`licenseID` AS `licenseID`,
`Artifacts`.`creationTime` AS `creationTime`, `Artifacts`.`updateTime` AS
`updateTime` FROM `Artifacts` INNER JOIN `Assignments` ON
`Assignments`.`assignmentID` = `Artifacts`.id AND `Assignments`.`groupID` =
%s WHERE `Artifacts`.`creatorID` = %s AND `Artifacts`.`artifactTypeID` = %s
ORDER BY isnull(`Assignments`.due), `Assignments`.due ASC) AS anon_3) AS
anon_1 LEFT OUTER JOIN `Licenses` AS `Licenses_1` ON `Licenses_1`.id =
anon_1.`licenseID` LEFT OUTER JOIN `ArtifactTypes` AS `ArtifactTypes_1` ON
`ArtifactTypes_1`.id = anon_1.`artifactTypeID` ORDER BY
anon_1.`artifactTypeID` DESC LIMIT %s, %s ('-::of::-', 1, 12L, 55,
1444153L, 1444204L, '-::of::-', 1, '3021', 12L, 56, 0, 10)
Here is the python code for that query:
def _getAssignmentsByCreatorID(session, creatorID, typeIDs,
artifactTypeDict, groupID=None, sort=None, pageNum=1, pageSize=10):
stID = artifactTypeDict['study-track']
asID = artifactTypeDict['assignment']
if stID in typeIDs and asID in typeIDs:
#
# Get the study tracks for this creator.
#
query = session.query(model.Artifact.id)
query = query.filter_by(creatorID=creatorID)
query = query.filter_by(artifactTypeID=stID)
idList = query.all()
ids = []
for id in idList:
ids.append(id[0])
#
# Find out the study tracks that are already assigned.
#
query = session.query(model.ArtifactAndChildren.childID)
query = query.filter_by(artifactTypeID=asID)
if groupID:
query = query.join(model.Assignment,
and_(model.Assignment.assignmentID ==
model.ArtifactAndChildren.id,
model.Assignment.groupID == groupID))
query = query.filter(model.ArtifactAndChildren.childID.in_(ids))
idList = query.all()
ids = []
for id in idList:
ids.append(id[0])
#
# Get the qualified artifacts.
#
stQuery = session.query(model.Artifact)
stQuery = stQuery.filter_by(creatorID=creatorID)
stQuery = stQuery.filter_by(artifactTypeID=stID)
if stID in typeIDs and asID in typeIDs:
#
# Skip the assigned study tracks.
#
stQuery = stQuery.filter(not_(model.Artifact.id.in_(ids)))
stQuery = stQuery.order_by(model.Artifact.id.desc())
if asID not in typeIDs:
asQuery = None
else:
asQuery = session.query(model.Artifact)
asQuery = asQuery.filter_by(creatorID=creatorID)
asQuery = asQuery.filter_by(artifactTypeID=asID)
if not groupID:
asQuery = asQuery.join(model.Assignment,
model.Assignment.assignmentID ==
model.Artifact.id)
else:
asQuery = asQuery.join(model.Assignment,
and_(model.Assignment.assignmentID ==
model.Artifact.id,
model.Assignment.groupID ==
groupID))
#
# Sorting order for assignment specific.
#
sortedByDueDate = False
if sort:
asgn = model.Assignment
col, order = sort.split(',')
if col == 'due':
oby = asgn.due
if order == 'asc':
asQuery = asQuery.order_by(func.isnull(oby), asc(oby))
else:
asQuery = asQuery.order_by(func.isnull(oby), desc(oby))
sortedByDueDate = True
sort = None
if not sortedByDueDate:
asQuery = asQuery.order_by(func.isnull(model.Assignment.due),
model.Assignment.due)
if not asQuery:
query = stQuery
else:
#
# SQLAlchemy does not put parentheses on queries between union
# so the order by clause won't work unless we use subquery.
#
stQuery = stQuery.subquery()
asQuery = asQuery.subquery()
query = session.query(model.Artifact)
query = query.select_from(union_all(stQuery.select(),
asQuery.select()))
#
# Sorting order.
#
if not sort:
query = query.order_by(model.Artifact.artifactTypeID.desc())
else:
art = model.Artifact
col, order = sort.split(',')
oby = None
if col == 'name':
oby = art.name
elif col == 'assign':
oby = art.artifactTypeID
if oby:
if order == 'asc':
query = query.order_by(asc(oby))
else:
query = query.order_by(desc(oby))
page = p.Page(query, pageNum, pageSize)
return page
Thanks,
Stephen
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
