SQL Error on Front End after Submitting New Issue

9 years 1 month ago - 9 years 1 month ago #1 by mronayne
I just installed version 1.6.6 and the first time I submitted a new issue I received the following on the Front End:
"Error 1054 - Unknown column 'R000000031' in 'field list' SQL=UPDATE `gbo82_it_issues` SET alias = R000000031 WHERE id = 31"

I have the configuration set on the "General Settings" tab for:
Site Issue Prefix: 'R'
Issue Alias Format: 'Simple'

The 'R000000031' in the error ends up being the Issue Number in the back end. The issue actually does get logged in spite of the Front End error but this is unacceptable for production use.

It appears that this error only occurs on the first creation of a new issue - before there are any issues in the database.

Please Log in or Create an account to join the conversation.

9 years 1 month ago - 9 years 1 month ago #2 by geoffc
Hmm, definitely shouldn't occur. Didn't see it in the testing either, what version of MySQL is this?.

Either way try the following fix:

In the file admin/components/com_issuetracker/helpers/issuetracker.php. Change line 2541 and line 2551 to the following:
$query = 'UPDATE `#__it_issues` SET alias = '.$db->quote($nalias).' WHERE id = '.$rid;

Two lines require changed just in case an alternative alias format is chosen. We are placing quotes around the alias field in the update statement.

The code would only be encountered if one of the alternative alias formats were chosen, so would not be encountered if the 'default' alias format is used which might explain why it hasn't been raised before, but doesn't explain why it never showed up intesting.

Regards
Geoff

Please Log in or Create an account to join the conversation.

9 years 1 month ago #3 by mronayne
My MySQL version is: 5.5.40-cll

After the patch was applied the error changed to:
1064 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 '' at line 1 SQL=SELECT COUNT(*) FROM `gbo82_it_progress` WHERE issue_id =

The behavior is still the same. This only occurs when all issues are Trashed and then the Trash is Emptied so the issues table is empty.

As before, the issue gets created in spite of the error and then all new issues get created without any errors.

Please Log in or Create an account to join the conversation.

9 years 1 month ago - 9 years 1 month ago #4 by geoffc
Well I have sort of duplicated the problem. I get the error (without and fix) on the first save and with the fix I get a cache error, but not the specific error you saw.

I have a modified fix for the save and the cache error. Now I am trying to duplicate the error from the syntax error on the 'SELECT COUNT' statement that you saw. Will probaby not get that until tomorrow though.

I agree that the first problem doesn't seem to occur on the second or subsequent issue create though. I will update the post when I have more information.

Regards
Geoff

Please Log in or Create an account to join the conversation.

9 years 1 month ago #5 by geoffc
Found a small logic problem that caused the last SQL (SELECT COUNT) error message.
Have worked out a fix and now need to test it out in a more controlled test build.

Unfortunately I am fighting a very slow internet connection problem which means that even entering this update is taking a long time. Will update this post when I have finished the testing.

Regards
Geoff

Please Log in or Create an account to join the conversation.

9 years 1 month ago #6 by mronayne
Not a problem. This is not a major issue for me since it only appears on the first issue create.
But, I must say, you are doing a remarkable job at responding to my posts - the best response time I've seen for any Joomla extension developer in a long, long time! Keep up the great work!
Regards,
Mike

Please Log in or Create an account to join the conversation.

Time to create page: 0.173 seconds
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries