Update Joomla 3.7.5 to 3.8 failing, error 1054, no backend anymore

[Marek-Moehling]

Steps to reproduce the issue

update either through admin backend, patch, or full update package

Expected result

update successful

Actual result

error msg when accessing the backend:

1054 - Es ist ein Fehler aufgetreten!*
Unknown column 'a.client_id' in 'where clause'

* an error occurred

The frontend works all right, including after login in. Installing a fresh version 3.8 works fine, both frontend and backend

System information (as much as possible)

5.5.9-1ubuntu4.22 (PHP 5.6 or later is commended, but I can't update, shared hosting)
cf phpinfo().png

Additional comments

[ghost]

can you please take a Look at #18003 if this Discussion help?

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/18012.}

[wilsonge]

https://forum.joomla.org/viewtopic.php?f=9&t=954795&p=3493297#p3493297

Looks like the generic issue updating from 3.5.1

[brianteeman]

@wilsonge I suspect it is related to #17967

[mbabker]

Just shooting from the hip, you're probably going to hit that error if you are upgrading from a version that doesn't have the client_id column to a version that does and you hit the second login screen in the update component (either through upload and update or the CSRF check fails; 3.5.1 will hit the latter 100% of the time).

[gwsdesk]

Confirmed probably this post of the forums will help? https://forum.joomla.org/viewtopic.php?f=9&t=954795&p=3493308#p3493299

[mbabker]

The only fix I can think of is to force everyone on 3.6.4 or earlier to update to the 3.6.5 release first, then update from 3.6.5 to current.

Even then, it's a hack and not a fix, but I don't know if we can actually fix it because the framework is not in a reliable state; that still leaves the upload and update option off the table to go from 3.6.5 or earlier to 3.7.0 or later (that feature is in 3.6, right?) because the client_id column is added in 3.7.0 and if you hit the second login screen for any reason that's where you're going to run into trouble.

[alikon]

for the missed client_id column issue, the forum proposed fix should work
running https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_admin/sql/updates/mysql/3.7.0-2017-01-17.sql should fix

so we should seriously think about some kind of "upgrade fixed path"

[mbabker]

The update queries are in the change deltas, we aren't missing anything.

The problem is if you hit that second login screen, the framework is in a mixed state between updates; the filesystem has been (mostly) updated but none of the steps in our PHP update script (which includes the database updates) have been run. And since that screen has the full admin UI, including the menu, the menu table gets queried and the data rendered by the module.

Running any part of the update "out of sequence" needs to be handled carefully. If you're hitting that second screen you've either hit a CSRF validation error (where we really shouldn't be running ANY update steps) or you're on the upload and update path and that one should not error out when hitting that screen.

[alikon]

the forced updated path should help in this scenario or i'm missing ?

[mbabker]

There is not a "forced update path" that can be used without bypassing security mechanisms that are in place.

[gwsdesk]

So what we do people since this is a MAJOR ISSUE? Can we get our brains together?

[Webdongle]

Is it not possible to deliver an update depending on the version of Joomla that is being updated ? Or is there going to have to be a 3.8.1 update to deal with the issue ? I agree with Leo this is a MAJOR ISSUE

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/18012.}

[csthomas]

What about copy this file https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_admin/sql/updates/mysql/3.7.0-2017-01-17.sql
to 3.8.1-2017-01-17.sql for a new joomla 3.8.1

[mbabker]

A 3.8.1 release cannot fix this issue without bypassing security mechanisms that exist in the update component.

As I have already said, the root of the problem is the user is being redirected to the second login screen in the middle of the update at a state where the system is not reliable. No code fix can account for that consistently and correctly, you just cannot run Joomla with logic from two different versions in place (especially across minor branches where new APIs are added).

The problem is not that the SQL updates are not being applied. The problem is that the SQL updates are applied after you hit the second login screen if you are directed there, and this redirect happens at a point after the filesystem has been updated with the new release's files.

This is a major inconvenience, this is an issue that needs addressing in some form. But unless someone has a solution that does not involve compromising the security checks of the update component, the ONLY possible solution is to restrict the update system so that 3.6.0 and earlier are only proposed the 3.6.5 update and 3.6.1 and later can be proposed the current version; additionally. Even without that change, the upload and update mechanism cannot be used for updates from 3.6 to 3.7 or later because of this issue.

[Marek-Moehling]

@ alikon

for the missed client_id column issue, the forum proposed fix should work running https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_admin/sql/updates/mysql/3.7.0-2017-01-17.sql should fix
=======================================================================
Thank you, but hat doesn't seem to work. I first tried it unchanged, that got me an error msg (no such table). Then I changed #__menu to te_de_menu according to my table prefix. Result:
=======================================================================
SQL query:
UPDATE te_de_menu_types SET menutype = 'main_is_reserved_133C585' WHERE client_id =0 AND menutype = 'main';
MySQL said: Documentation
#1054 - Unknown column 'client_id' in 'where clause'
=======================================================================
cf te_de_.sql.tar.gz (some private values replaced by xxxx)

[Webdongle]

@mbabker

The problem is not that the SQL updates are not being applied. The problem is that the SQL updates are applied after you hit the second login screen if you are directed there, and this redirect happens at a point after the filesystem has been updated with the new release's files.

Is there any way for the update to recognise that the Joomla version is 3.5.1 (or less) then serving an update to 3.7.5 before serving the update to 3.8.x ?

[mbabker]

We can restrict what updates are shown through the update component (and we already do so for various scenarios). In the code we cannot reliably detect the version that was upgraded from. Even in the current release, we do not store the from version anywhere that can be used in this way.

[mbabker]

Also, as I've pointed out before, it is not 3.5.1 direct to 3.8.0 that is the issue. 3.6.0 and earlier to 3.6.1 or later have extra steps because of the added CSRF checks (IIRC 3.6.3 introduced the second login screen instead of having a "hard" error message), any of these releases will most likely run into the same SQL related problem if they try to direct upgrade to 3.7.0 or later.

The absolute safest upgrade path is anything from 3.6.0 or earlier are only offered to upgrade to 3.6.5 at the latest. 3.6.1 or later, as long as using the "normal" online update method (as in not using the upload feature) should be able to safely direct upgrade to the current version.

Regardless of what changes we make to the update server and what updates are offered to users, as I have repeatedly said, there is not a code solution that can make Joomla reliably work if using the upload and update mechanism and going from 3.6.5 or earlier to 3.7.0 or later because of the schema changes to the menu table and the fact that the second login screen renders the admin menu module.

This is a big if. But, if it is possible to change that second login screen to a screen that is very close to the normal login screen plus the extra notification message without loading the rest of the administrator modules, we might be able to circumvent this issue. Even if we can circumvent this specific issue, we are still left with the "problem" of trying to perform actions on a site while it is in an unstable mixed state which means we probably will run into it again in the future.

[rdeutz]

(thinking out loud)
The script.php has the from_version so we could save this in a file somewhere, with this info we can hack the menu module to use a different sql when coming from a version 3.6.0 or earlier. It is an option but it sounds wrong to me.

[Webdongle]

@mbabker

The absolute safest upgrade path is anything from 3.6.0 or earlier are only offered to upgrade to 3.6.5
That's what I was trying to say but got the version numbers mixed up.

[mbabker]

script.php isn't used/included until after the checks which redirect the update to the second login screen. The preflight action in the script would have to be moved before the CSRF check or whatever handles redirecting the upload and update mechanism.

[joomla-cms-bot]

Set to "closed" on behalf of @franz-wohlkoenig by The JTracker Application at issues.joomla.org/joomla-cms/18012

[ghost]

closing this Issue, please comment at #18020

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/18012.}

[Webdongle]

Please reopen this as #18020 is a different issue.

[joomla-cms-bot]

Set to "open" on behalf of @franz-wohlkoenig by The JTracker Application at issues.joomla.org/joomla-cms/18012

[ghost]

reopened as stated in Comment

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/18012.}

[wojsmol]

@franz-wohlkoenig I mean devs discussion above. Additionally see #18012 (comment)

[gwsdesk]

@Marek-Moehling Post your issue on the Forums. This is not a support platform

[gwsdesk]

@Marek-Moehling If you cannot login to the forums use this form https://community.joomla.org/helpdesk/forum-un-ban-request.html

[theexx]

to solve this problem it took 1 minute

it is a database issue and need to be fixed

but without backend is not possible to access extention database, so y u need to do with full link

[Webdongle]

@theexx

to solve this problem it took 1 minute

Your fix was ?

[theexx]

you need as i wrote to perform a joomla database fix, normally it is an option inside extention menu

due is not possible to access it directly because no backend access you need to launch fix writing full link on your bronwer

got it ?

[Webdongle]

That does not fix all errors of a failed update. Please post in the forum for support as requested by @gwsdesk . Someone will help you fix the failed update correctly.

[gwsdesk]

@theexx as mentioned by Kevin (webdongle) the DB-fix hides the real issues. You still have a lot of issues though not showing. Your next 'update' will be botched. You have not resolved the issue and as mentioned post on the forums since this on Github is not support channel.

Got it?

Leo

[theexx]

in my case it fixed the backend problem and "1054 Unknown column 'a.client_id' in 'where clause'" error

the issue was related to converting from utf8 to utf8mb4

@gwsdesk maybe i not got what you wrote due my bad english

[Webdongle]

@theexx

the issue was related to converting from utf8 to utf8mb4

No it wasn't ... that was just one symptom that the update didn't complete correctly. You will have more problems in the future. Got it ???

[theexx]

maybe my case is different.

i compare file by file and table by table affected site with a twin on we have (update was ok with it) and all is identical

[JoshWobbles]

For the life of me I do not understand why this hasn't been fixed yet....

[Quy]

@franz-wohlkoenig Close and post to Joomla forum as this is an upgrade issue?

[ghost]

Closed as stated above. Please ask help on the forums - Migrating and Upgrading to Joomla! 3.x for similar Issues.. This repository concerns in first Place Joomla coding.

[joomla-cms-bot]

Set to "closed" on behalf of @franz-wohlkoenig by The JTracker Application at issues.joomla.org/joomla-cms/18012

[JoshWobbles]

Its a major bug that is crippling multiple users, not a localized "upgrade issue". Why is it nobody takes this seriously....

This should NOT be closed until the issue is fixed.

[gwsdesk]

@JoshWobbles We do take bugs seriously. This is not a bug but an isolated upgrade error. Post on the forums and we will assist you. It is correct that this is closed and should not be re-opened

[JoshWobbles]

Doesn't seem isolated to me, seems like it was easily reproducible making it a BUG!

[gwsdesk]

@JoshWobbles please listen to this community and hear what this community is telling to you. You can continue posting your views but those views do not reflect what the majority of the developers think
and believe so your messages will not lead to any further actions on the Issue Tracker or on Github. So again post on the forums and we will help you at that platform

[JoshWobbles]

I looked on the forums, from what I see nobody with this issue has been helped because the BUG has not yet been fixed.

[gwsdesk]

Did I see your post already on the forums? Please make your post wwith all details as requested including FPA-output and we address your issue and it is NOT a bug

[JoshWobbles]

https://forum.joomla.org/viewtopic.php?f=708&t=955172

But AGAIN, this is a BUG, so it belongs HERE until it is fixed.... The issue is reproducible and experienced by multiple users.

[gwsdesk]

@franz-wohlkoenig Please lock this thread since this person is not accepting anything being explained related to this issue.... I will attend to this person's post on the forums and he simply should be stopped posting here

[JoshWobbles]

Why cant you just admit and address the issue? Why do you ignore everybody who tries to bring it up?

[ghost]

@brianteeman can you please lock this Thread as @gwsdesk comment above?

[brianteeman]

Please use the forum AND provide the requested information

[brianteeman]

This is now locked.