1054 Unknown column 'a.extra_query' in 'field list'

Need help upgrading your Joomla! website or converting to Joomla! 3.x? Please consider the availability of third-party templates and extensions when deciding which Joomla version to use.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Post Reply
MacRicky_1954
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Fri Aug 30, 2013 2:34 pm

1054 Unknown column 'a.extra_query' in 'field list'

Post by MacRicky_1954 » Fri Jun 15, 2018 1:30 pm

Last year I've updated Joomla version 3.1.2 (?) to version 3.4 (?) and further up to my actual version 3.7.5.
This operation was difficult but at the end all works fine with satisfaction!

I tought to upgrade again to the latest version but I receive next error by 'Components' , 'Joomla Update' : 1054 Unknown column 'a.extra_query' in 'field list'. I don't see a name of a table or any other information.

Here ends my knowledge… and my question is in human language if there's anybody who can help?
Eccept a SQL backup using phpMyAdmin I'm really a layman.
I know Internet is full of explanations but there are too many trees in the forest and I don't know where to begin :--)

Thanks a lot !

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 35891
Joined: Sat Apr 05, 2008 9:58 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by Webdongle » Fri Jun 15, 2018 2:05 pm

Do you get any errors then ?
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

MacRicky_1954
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Fri Aug 30, 2013 2:34 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by MacRicky_1954 » Mon Jun 18, 2018 1:15 pm

Hey Webdongle, thank you for your suggestion.
I receive the error diectly after I hit the Joomla Update option.
I downloaded the file but I cannot Upload & Update it, see attachment
You do not have the required permissions to view the files attached to this post.

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 35891
Joined: Sat Apr 05, 2008 9:58 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by Webdongle » Mon Jun 18, 2018 1:47 pm

Make a backup of your site and then try the same process with J3.7.5 etc. https://downloads.joomla.org/cms/joomla3
You may get lucky and one of the installs will insert the column. But backup first https://extensions.joomla.org/extension/akeeba-backup/

Was it a 3rd party template/quickstart install ?
What version did it start as ?

Addendum
A couple of things you could try (best tried on localhost)
  1. Install 3.7.5 (in a separate folder or on localhost) then edit the confirguration.php to connect to the original database. Then update using clean files that will update your database. ... Then install 3.7.5 (in a separate folder or on localhost) again and connect the original configuration.php to the new 3.7.5 database. Then update that will update your files.
    Just connect the configuration.php (of your updated site files to the updated site database.
  2. Install J3.8.8 (in a separate folder or on localhost) and use https://extensions.joomla.org/extension/j2xml/ to move the Articles/Categories/Users etc.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

mbabker
Joomla! Hero
Joomla! Hero
Posts: 2176
Joined: Sun Feb 28, 2010 8:26 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by mbabker » Mon Jun 18, 2018 4:06 pm

Your upgrade definitely did not go as it was supposed to as database changes were not applied. Presumably going to Extensions > Database is going to show you have several schema related issues that need to be addressed.

Absolute best option, restore backup of old version before you started updating and apply updates incrementally through the update component. If the update component cannot work for whatever reason in your environment, you can try updating the filesystem then running the script found in my signature line (though I make no promises that it will actually work back to Joomla 3.1, I wrote it at 3.5 but the API should be pretty similar IIRC) to take care of every other bit of the update process that needs to be done.

Otherwise, you've got some work cut out for you as hinted at by @Webdongle (and truth be told, the install source really doesn't matter unless it was core hacked to begin with).
So long and thanks for all the fish.

Manually updating Joomla? See https://gist.github.com/mbabker/d7bfb4e ... 3607f89281

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 35891
Joined: Sat Apr 05, 2008 9:58 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by Webdongle » Mon Jun 18, 2018 5:19 pm

@mbabker
As a matter of interest ... if the version was altered in the database then would it force the database changes is updated via Joomla update component >>> Upload & Update ?
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

mbabker
Joomla! Hero
Joomla! Hero
Posts: 2176
Joined: Sun Feb 28, 2010 8:26 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by mbabker » Mon Jun 18, 2018 6:34 pm

It is based on the value in the schemas table and doing a comparison with the files in the administrator/components/com_admin/sql/updates directory, Note the schema version does not always align to the current CMS release, that only changes whenever we make database changes (so in the case of 3.8.7 the schema version should be 3.8.6-2018-02-14.sql since the update from 3.8.6 to 3.8.7 didn't include database changes of any kind).
So long and thanks for all the fish.

Manually updating Joomla? See https://gist.github.com/mbabker/d7bfb4e ... 3607f89281

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 35891
Joined: Sat Apr 05, 2008 9:58 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by Webdongle » Mon Jun 18, 2018 8:58 pm

What if the schema was edited to match that for J3.12 or J3.4 ... might that cause an update (via Joomla update component >>> Upload & Update) to run the relevant sql commands ?
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

mbabker
Joomla! Hero
Joomla! Hero
Posts: 2176
Joined: Sun Feb 28, 2010 8:26 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by mbabker » Mon Jun 18, 2018 10:03 pm

That's not much different than trying to use the database fix button, but it has the side effect of if other schema changes had been made (or if there are data insertions/updates) those will potentially cause failures. Database fix is smart enough to only deal with true schema changes but it leaves you trying to fix other problems like not correctly installed core extensions.
So long and thanks for all the fish.

Manually updating Joomla? See https://gist.github.com/mbabker/d7bfb4e ... 3607f89281

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 35891
Joined: Sat Apr 05, 2008 9:58 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by Webdongle » Tue Jun 19, 2018 8:46 am

mbabker wrote:That's not much different than trying to use the database fix button, ...
But that brings the database schema to match the files yes ?

I will try and rephrase what I am asking. Joomla is reporting it is 3.7.5 but is there any way for it to report as 3.4.0? If so then would that make the update run the sql for 3.4.0 (and other changes in between)?
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

MacRicky_1954
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Fri Aug 30, 2013 2:34 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by MacRicky_1954 » Tue Jun 19, 2018 8:50 am

That's a lot of information people... thank you for it.
It isn't easy for me to understand as I'm not very computer minded :--(
I Installed Joomla first in 2013 with version 3.1.5 and worked with it till August 2017.

I upgraded to 3.5.1 using (https://downloads.joomla.org/cms/joomla ... format=zip) and then next to 3.7.4. All this went well without problems, even today...

I'll make a backup and install this locally on a pc. I see what happens and come back to the list.

mbabker
Joomla! Hero
Joomla! Hero
Posts: 2176
Joined: Sun Feb 28, 2010 8:26 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by mbabker » Tue Jun 19, 2018 12:01 pm

Webdongle wrote:
mbabker wrote:That's not much different than trying to use the database fix button, ...
But that brings the database schema to match the files yes ?

I will try and rephrase what I am asking. Joomla is reporting it is 3.7.5 but is there any way for it to report as 3.4.0? If so then would that make the update run the sql for 3.4.0 (and other changes in between)?
Change the schemas table and at the next update attempt through the update component Joomla will try to do all changes from whatever value is in the database up to the newest file in the updates directory. For the most part it should work OK for schema changes (because in that scenario it will basically replay an ALTER TABLE statement that has already been done so there should be no side effects), but it will cause issues with any data statements we have in the files. So no, changing the value in the schemas table is not something that should be considered at any time; we just do not support "rollback" type operations on schema updates.
So long and thanks for all the fish.

Manually updating Joomla? See https://gist.github.com/mbabker/d7bfb4e ... 3607f89281

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 35891
Joined: Sat Apr 05, 2008 9:58 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by Webdongle » Tue Jun 19, 2018 2:55 pm

Then perhaps ... Install J3.8.8 (in a separate folder or on localhost) and use https://extensions.joomla.org/extension/j2xml/ to move the Articles/Categories/Users etc. ... is the OP's best option if he doesn't have a clean backup.


viewtopic.php?f=714&t=793531 would also be a good idea.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

MacRicky_1954
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Fri Aug 30, 2013 2:34 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by MacRicky_1954 » Tue Jun 19, 2018 3:48 pm

As I confessed before I'm not that keen, so much of the information is unclear to me but I understand to problem and the idea to repair the database. I'm agree with mbabker that the best option is to perform regularly an update, that's why I want to do this now after a year. But unfortunately I've got the error right away.

I have made a backup of the online Joomla Data version and the SQL Database so I'm safe to play around locally.
I had a local installation on my laptop after I updated to 3.7.4 in 2017, August 12th and tried to update the same way which gave me the same errors (Extensions  Database), meaning that the problems started already with the earlier backup in August 2017.

Below shows the errors as asked by mbabker:

Next I update the installation of August 2017 with the latest SQL Database. This went perfect without errors. Rechecking the above gave me the same errors.

I applied the postupdate.php from mbabker and received the message 'Update to 3.7.4 completed successfully'. Nevertheless rechecking the database (Extensions  Database) shows me nearly the same errors.

Warning: Database is not up to date!

13 Database Problems Found.

Table 'uephl_update_sites' does not have column 'extra_query'. (From file 3.2.2-2013-12-22.sql.)
Table 'uephl_updates' does not have column 'extra_query'. (From file 3.2.2-2013-12-22.sql.)
Table 'uephl_updates' does not have column 'version' with type varchar(32). (From file 3.2.2-2014-01-18.sql.)
Table 'uephl_users' does not have column 'requireReset'. (From file 3.3.0-2014-02-16.sql.)
Table 'uephl_user_profiles' does not have column 'profile_value' with type TEXT. (From file 3.3.4-2014-08-03.sql.)
Table 'uephl_redirect_links' does not have column 'header'. (From file 3.4.0-2014-09-16.sql.)
Table 'uephl_session' does not have column 'session_id' with type varchar(191). (From file 3.5.0-2015-07-01.sql.)
Table 'uephl_user_keys' does not have column 'series' with type varchar(191). (From file 3.5.0-2015-07-01.sql.)
Table 'uephl_contentitem_tag_map' should not have index 'idx_tag'. (From file 3.5.0-2015-10-26.sql.)
Table 'uephl_contentitem_tag_map' should not have index 'idx_type'. (From file 3.5.0-2015-10-26.sql.)
Table 'uephl_redirect_links' should not have index 'idx_link_old'. (From file 3.5.0-2016-03-01.sql.)
Table 'uephl_redirect_links' does not have column 'old_url' with type VARCHAR(2048). (From file 3.5.0-2016-03-01.sql.)
Table 'uephl_redirect_links' does not have column 'referer' with type VARCHAR(2048). (From file 3.5.0-2016-03-01.sql.)

Other Information

Database schema version (in #__schemas): 3.7.4-2017-07-05.
Update version (in #__extensions): 3.7.4.
Database driver: mysqli.
133 database changes were checked.
182 database changes did not alter table structure and were skipped.


I tried to install new version of 3.7.5 but can't import the database and helped me any further.
I think that these errors must be solved before trying to update. But…. I don't know how and help or suggestions in simple language is welcome.

Thanks in advance !!!

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 35891
Joined: Sat Apr 05, 2008 9:58 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by Webdongle » Tue Jun 19, 2018 10:34 pm

You have problems with your database ... IT IS BROKE !!!

You can recover by using a good (unbroken) backup then updating incrementally as described by mbabker
or
Use install 3.8.8 and rebuild your site. https://extensions.joomla.org/extension/j2xml/ will help you move the Articles/Categories/Users etc.

I would suggest rebuilding the site in case you have been hacked.

But please viewtopic.php?f=714&t=793531 it might shed light on why your previous updates failed.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

MacRicky_1954
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Fri Aug 30, 2013 2:34 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by MacRicky_1954 » Thu Jun 21, 2018 1:51 pm

Hi Webdongle,
Followed your suggestions! Downloaded j2xml and installed successfully into the old website. Extracting the xml files went also correct. Next I installed a fresh 3.8.8 version with a new empty database which also worked fine. Also here I installed j2xml successfully (checked with Extensions/Manage) and followed the import directions listed on the author's website.

Whatever I try or do I get the error: There was an error uploading this file to the server. This happens with all XML files except when I import only few articles from a separate export so I think my procedure is correct.

I'm now completely lost in the joomla jungle and a bit desperate. I understand why the database is broke but at the moment all is working, except an upgrade seems to be impossible…

I don't hope that I am not doomed to work with version 3.7.4
Thanks again for suggestion and your help!
Richard

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 35891
Joined: Sat Apr 05, 2008 9:58 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by Webdongle » Thu Jun 21, 2018 2:52 pm

You appear to have some write issues on the server. Please viewtopic.php?f=714&t=793531

J2xml should have imported the files that were exported from the old site. If we see the output of the fpa then we should be able to pinpoint the problem that caused the previous failed update and is causing the j2xml import.

Best guess at the moment is the session path is not writable.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

MacRicky_1954
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Fri Aug 30, 2013 2:34 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by MacRicky_1954 » Mon Jun 25, 2018 3:59 am

Thanks to everyone who tried to help me with this nasty problem!
I will not mess around with trying to recoved the database, it goes beyond my knowledge.
Therefore I'll to rebuild my site starting from a new 3.8.8 version. After all I can do a lot of copy/paste of the articles since the online version is still operational and I have an very good tutorial of Jen Kramer to guide me.

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 35891
Joined: Sat Apr 05, 2008 9:58 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by Webdongle » Mon Jun 25, 2018 9:36 am

Starting fresh was going to be my next suggestion.

When copy/paste ... paste to notepad++ first then copy/paste from there ... otherwise you will have 'invisible'/unwanted code in your Articles. https://notepad-plus-plus.org/
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

MacRicky_1954
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Fri Aug 30, 2013 2:34 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by MacRicky_1954 » Thu Jun 28, 2018 12:59 pm

Thank you Webdongle! I remarked that Joomla automatically corrects and deletes unwanted code. Great!!!
Meanwhile the new website is updated to 3.8.10 and succesfully launched.
Is it possible to launch updates automatically? I don't go that regularily to the backend of the website.

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 35891
Joined: Sat Apr 05, 2008 9:58 pm

Re: 1054 Unknown column 'a.extra_query' in 'field list'

Post by Webdongle » Fri Jun 29, 2018 12:52 am

MacRicky_1954 wrote:Thank you Webdongle! I remarked that Joomla automatically corrects and deletes unwanted code. Great!!!...
Standard code yes but not 'Hidden code'. By 'Hidden code' I mean ... copy from the browser then paste in an article and check the code view. You will see extra <div> and other tags. They are not seen when pasting in the Article. e.g. When copying the text from the front end of my site then pasting into a new Article.

Code: Select all

<div class="items-row cols-1 row-1 row-fluid"> </div>
<div class="items-row cols-1 row-1 row-fluid">
<div class="item column-1 span12">
<div class="icons"> </div>
<dl class="article-info muted">
<dt class="article-info-term">Details</dt>
<dd class="hits">Hits: 19226</dd>
Does not show in the original Article or it's code view but is added to the pasted Article and can be seen in code view. This is unecessary extra code in the copy Article as it is generated by the browser.

MacRicky_1954 wrote:...
Is it possible to launch updates automatically? I don't go that regularily to the backend of the website.
Make sure System - Joomla! Update Notification is activated.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein


Post Reply

Return to “Migrating and Upgrading to Joomla! 3.x”