View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0006394 | mantisbt | sql | public | 2005-11-07 03:13 | 2006-09-12 00:54 |
Reporter | naib | Assigned To | thraxisp | ||
Priority | normal | Severity | major | Reproducibility | always |
Status | closed | Resolution | duplicate | ||
Platform | Sun | OS | Solaris | OS Version | 8 |
Product Version | 0.19.3 | ||||
Fixed in Version | 1.0.0a1 | ||||
Summary | 0006394: Unknown column p.field_id when closing bug when using MySQL >= 5.0.12 | ||||
Description | Things were working fine with MySQL Max 4.1.14 and Mantis 0.19.2, but since upgrading MySQL to Max 5.0.15, an application error is produced whenever I close a bug (it may also require using an empty note). APPLICATION ERROR 0000401 Database query failed. Error received from database was #1054: Unknown column 'p.field_id' in 'on clause' for the query: SELECT f.name, f.type, f.access_level_r, f.default_value, f.type, s.value Upgrading to 0.19.3 and making sure the admin upgrades were all done did not fix the issue. I note that all 24 tables exist and the two tables mentioned above are empty (as expected) because I have not (knowingly) defined any custom fields. Bug ID 980 is the correct bug_id for the issue I'm attempting to close. What exactly is the problem? | ||||
Tags | No tags attached. | ||||
duplicate of | 0005380 | closed | thraxisp | Invalid LEFT JOIN syntax for Oracle |
has duplicate | 0006428 | closed | ryandesign | MySQL 5: Unknown column 'p.field_id' in 'on clause' |
has duplicate | 0006937 | closed | ryandesign | MySQL Compability v.4 and v.5 |
child of | 0005460 | closed | vboctor | Critical Issues to Fix for Mantis 1.0.0 Release |
Incidentally, I get a similar error when attempting to change the priority of another issue from urgent to high: APPLICATION ERROR 0000401 Database query failed. Error received from database was #1054: Unknown column 'p.field_id' in 'on clause' for the query: SELECT f.name, f.type, f.access_level_r, f.default_value, f.type, s.value |
|
OK, I've found the problem. The issue comes from a recent change in MySQL 5, and I quote from the MySQL Reference Manual (http://dev.mysql.com/doc/refman/5.0/en/join.html): "Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was intrepreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are. "Example: "CREATE TABLE t1 (i1 INT, j1 INT); "Prior to 5.0.12, the SELECT is legal due to the implicit grouping of t1,t2 as (t1,t2). From 5.0.12 on, the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3: "SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3); "This change also applies to INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN." As a result, the first query I noted above can be corrected in code for all versions of MySQL as follows: "SELECT f.name, f.type, f.access_level_r, f.default_value, f.type, s.value Note addition of brackets around the tables in the FROM clause. |
|
Modified lines as follows to get things working: "core/custom_field_api.php" line 754 No other query I could find required the change. |
|
See another possible solution in 0006428. |
|
Is this still an issue in 1.0.0rc4? |
|
Yes, this is now working for me in 1.0.0r5. |
|
This was actually fixed in 1.0.0a1 as part of other fixes. This query is changed in 1.0.1. |
|