Mysql 5 & db_rewrite_sql issue
dww - February 21, 2006 - 18:40
| Project: | Project |
| Version: | x.y.z |
| Component: | Issues |
| Category: | bug report |
| Priority: | critical |
| Assigned: | dww |
| Status: | closed |
Description
Mysql 5.0.12 and up has changed the join semantics slightly. This patch fixes a few places where the use of db_rewrite_sql could potentially generate the error: "Unknown column 'n.nid' in 'on clause'", when the node_privacy_byrole module (or a different access control module like simple_access) is active.
For more info see: http://drupal.org/node/43735#comment-92865
The MySQL docs talking about this change are: http://dev.mysql.com/doc/refman/5.0/en/join.html (search for "Previously, the ON clause could refer to columns in tables named to its right. Now an ON clause can refer only to its operands.")
thanks!
-derek
| Attachment | Size |
|---|---|
| project_issue_mysql5.patch | 2.26 KB |
#1
i should add: there might be other places in the project module that are vulnerable to this bug. this particular patch just fixes one i actually saw on my site (so i could verify the patch actually solved the problem). if anyone's interested, i'd be happy to provide a patch to preemptively change all SQL queries in project to put the node table first whenever using db_rewrite_sql(). however, that'd require more changes, which would make a larger patch and more work to review and test, so i just wanted to post a small patch initially.
#2
Thanks for the patch. Please go ahead and apply it to 4.6 and to HEAD.
Then, if you were able to go through and find and fix other occurrences, that would be great too.
#3
i just applied the fix against 4.6 and generated a new patch for HEAD (attached here). i also did a more thorough review of the rest of issue.inc, release.inc, mail.inc, and project.module. all the other places we're using db_rewrite_sql() either already have {node} as the first table, or they use the $primary_table argument to specify some table other than {node} as the primary one. i've done fairly thorough testing on my own MySQL 5.0.18-based 4.7 test site and I haven't run into any other SQL problems. So, i believe project is hereby safe for MySQL 5.0.12+.
I'll commit this patch to HEAD right now to finally close this issue.
#4
patch applied to HEAD (revision 1.1.46). this issue is now fixed.
#5
typo in last comment, should be: "revision 1.146". ;)
#6