Databasese Error

[gaile]

My webstie suddnly accurence 'Database Error' as follows

---

Error message returned from the database:

Could not execute query [Native Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as valueID FROM SpecificationDateValue LEFT JOIN SpecField ON specFieldID ' at line 1] [User Info: SELECT SpecificationDateValue., NULL AS valueID, NULL AS specFieldValuePosition, SpecFieldGroup.position AS SpecFieldGroupPosition, SpecFieldGroup.name AS SpecFieldGroupName, SpecField. as valueID FROM SpecificationDateValue LEFT JOIN SpecField ON specFieldID = SpecField.ID LEFT JOIN SpecFieldGroup ON SpecField.specFieldGroupID = SpecFieldGroup.ID WHERE productID IN (38147, 38146, 38145, 38144, 38143, 38141, 38140, 38139, 38138, 38137, 38136, 38135, 38134, 38133, 38132, 38131, 38130, 38129, 38128, 38127, 38126, 38125, 38124, 37959, 37958, 37957, 37956, 37955, 37954, 37953, 37952, 37951) AND SpecField.isDisplayedInList = 1 UNION SELECT SpecificationStringValue., NULL, NULL AS specFieldValuePosition, SpecFieldGroup.position AS SpecFieldGroupPosition, SpecFieldGroup.name AS SpecFieldGroupName, SpecField. as valueID FROM SpecificationStringValue LEFT JOIN SpecField ON specFieldID = SpecField.ID LEFT JOIN SpecFieldGroup ON SpecField.specFieldGroupID = SpecFieldGroup.ID WHERE productID IN (38147, 38146, 38145, 38144, 38143, 38141, 38140, 38139, 38138, 38137, 38136, 38135, 38134, 38133, 38132, 38131, 38130, 38129, 38128, 38127, 38126, 38125, 38124, 37959, 37958, 37957, 37956, 37955, 37954, 37953, 37952, 37951) AND SpecField.isDisplayedInList = 1 UNION SELECT SpecificationNumericValue., NULL, NULL AS specFieldValuePosition, SpecFieldGroup.position AS SpecFieldGroupPosition, SpecFieldGroup.name AS SpecFieldGroupName, SpecField. as valueID FROM SpecificationNumericValue LEFT JOIN SpecField ON specFieldID = SpecField.ID LEFT JOIN SpecFieldGroup ON SpecField.specFieldGroupID = SpecFieldGroup.ID WHERE productID IN (38147, 38146, 38145, 38144, 38143, 38141, 38140, 38139, 38138, 38137, 38136, 38135, 38134, 38133, 38132, 38131, 38130, 38129, 38128, 38127, 38126, 38125, 38124, 37959, 37958, 37957, 37956, 37955, 37954, 37953, 37952, 37951) AND SpecField.isDisplayedInList = 1 UNION SELECT SpecificationItem.productID, SpecificationItem.specFieldID, SpecFieldValue.value, SpecFieldValue.ID, SpecFieldValue.position, SpecFieldGroup.position AS SpecFieldGroupPosition, SpecFieldGroup.name AS SpecFieldGroupName, SpecField.* FROM SpecificationItem LEFT JOIN SpecFieldValue ON SpecificationItem.specFieldValueID = SpecFieldValue.ID LEFT JOIN SpecField ON SpecificationItem.specFieldID = SpecField.ID LEFT JOIN SpecFieldGroup ON SpecField.specFieldGroupID = SpecFieldGroup.ID WHERE productID IN (38147, 38146, 38145, 38144, 38143, 38141, 38140, 38139, 38138, 38137, 38136, 38135, 38134, 38133, 38132, 38131, 38130, 38129, 38128, 38127, 38126, 38125, 38124, 37959, 37958, 37957, 37956, 37955, 37954, 37953, 37952, 37951) AND SpecField.isDisplayedInList = 1 ORDER BY productID, SpecFieldGroupPosition, position, specFieldValuePosition]

How can i solive this problem?
Look forward to hearing from you soon.

Thank you very much.
Jeong

[gaile]

The file
/application/model/eavcommon/EavSpecificationManagerCommon.php

[alejandrolsca]

I have the same problem, how do you fix it? can you tell me please?

[littlekosh]

Appears to be a problem with selecting everything from a table and then attempting to store it as a single column in the query, specifically "SpecField.* as valueID"

Below is a patch file for application/model/eavcommon/EavSpecificationManagerCommon.php. I have no idea if it causes issues or not, but it does allow the site to properly load.

--- EavSpecificationManagerCommon.bad.php    2012-11-28 21:09:45.000000000 -0700
+++ EavSpecificationManagerCommon.php    2012-11-28 21:21:06.000000000 -0700
@@ -769,11 +769,11 @@
        $group = $groupClass . '.position AS SpecFieldGroupPosition, ' . $groupClass . '.name AS SpecFieldGroupName, ';

        $query = '
-        SELECT ' . $dateClass . '.*, NULL AS valueID, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* as valueID FROM ' . $dateClass . ' ' . $cond . '
+        SELECT ' . $dateClass . '.*, NULL AS valueID, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* FROM ' . $dateClass . ' ' . $cond . '
        UNION
-        SELECT ' . $stringClass . '.*, NULL, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* as valueID FROM ' . $stringClass . ' ' . $cond . '
+        SELECT ' . $stringClass . '.*, NULL, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* FROM ' . $stringClass . ' ' . $cond . '
        UNION
-        SELECT ' . $numericClass . '.*, NULL, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* as valueID FROM ' . $numericClass . ' ' . $cond . '
+        SELECT ' . $numericClass . '.*, NULL, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* FROM ' . $numericClass . ' ' . $cond . '
        UNION
        SELECT ' . $valueItemClass . '.' . $objectColumn . ', ' . $valueItemClass . '.' . $fieldColumn . ', ' . $valueClass . '.value, ' . $valueClass . '.ID, ' . $valueClass . '.position, ' . $group . $fieldClass . '.*
                 FROM ' . $valueItemClass . '