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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.