[#35664] - Recovering from a Broken Joomla 4 upgrade
- Closed
- 28 Feb 2022
- Medium
- Build: 4.0-dev
- # 35664
Is your feature request related to a problem? Please describe.
Yes. I migrated a site that has been working fine on 3.10, but there were inconsistencies in the tables from a previous J4 attempt.
A user reported a similar issue here: https://forum.joomla.org/viewtopic.php?f=810&p=3640792#p3640792
In the successive attempts to upgrade the site to Joomla 4, I got these errors:
- Installer: :Installer: Found SQL Duplicate entry '1' for key 'Primary'
- Installer: :Install: Error SQL Duplicate entry 'com_config.test_mail-' for key 'PRIMARY'
- Installer: :Install: Error SQL Table '#__history' already exists
- Installer: :Install: Error SQL Duplicate column name 'extension'
Describe the solution you'd like
To migrate to Joomla 4 on the site with the described condition, I run this script to undo what shouldn't be there before the upgrade in a normal situation.
# Installer: :Installer: Found SQL Duplicate entry '1' for key 'Primary'
DROP TABLE IF EXISTS `#__workflows`;
DROP TABLE IF EXISTS `#__workflow_associations`;
DROP TABLE IF EXISTS `#__workflow_stages`;
DROP TABLE IF EXISTS `#__workflow_transitions`;
DROP TABLE IF EXISTS `#__template_overrides`;
DROP TABLE IF EXISTS `#__finder_links_terms`;
DROP TABLE IF EXISTS `#__finder_logging`;
DROP TABLE IF EXISTS `#__finder_terms_common`;
DROP TABLE IF EXISTS `#__webauthn_credentials`;
# Installer: :Install: Error SQL Duplicate entry 'com_config.test_mail-' for key 'PRIMARY'
DELETE FROM `#__mail_templates` WHERE template_id = 'com_config.test_mail';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_contact.mail';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_contact.mail.copy';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.massmail.mail';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.password_reset';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.reminder';
DELETE FROM `#__mail_templates` WHERE template_id = 'plg_system_updatenotification.mail';
DELETE FROM `#__mail_templates` WHERE template_id = 'plg_user_joomla.mail';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_actionlogs.notification';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_privacy.userdataexport';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_privacy.notification.export';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_privacy.notification.remove';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_privacy.notification.admin.export';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_privacy.notification.admin.remove';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.registration.user.admin_activation';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.registration.user.admin_activation_w_pw';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.registration.user.self_activation';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.registration.user.self_activation_w_pw';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.registration.user.registration_mail';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.registration.user.registration_mail_w_pw';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.registration.admin.new_notification';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.registration.user.admin_activated';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_users.registration.admin.verification_request';
DELETE FROM `#__mail_templates` WHERE template_id = 'plg_system_privacyconsent.request.reminder';
DELETE FROM `#__mail_templates` WHERE template_id = 'com_messages.new_message';
# Installer: :Install: Error SQL Table 'j_history' already exists
DROP TABLE IF EXISTS `#__history`;
# Installer: :Install: Error SQL Duplicate column name 'extension'
ALTER TABLE `#__mail_templates` DROP `extension`;Can it be incorporated into the upgrading script to support broken upgrades?
Additional context
If the feature is beyond what the installer can support, this request will help future migrations.
joomla-cms-bot
- | Labels |
Added:
No Code Attached Yet
|
||
The clean way to restore database updates is to do it into an empty database. The issue only happens if people don't do that but use their messed database with all what's inside so the database backup only restores the J3 stuff and keeps the J4 stuff. So I'm not sure if we shall handle that. But I'd like to know opinions of more people.
P.S.: The "DROP TABLE" statements we can only add because currently the database schema checker completely ignores "DROP TABLE" statements. If that would be corrected, we would face the problem that the database checker cannot handle the sequence of statements, it only checks for the first one which fails, and so it would end in a loop of error "table exists but should be dropped" and after fixing that "table should exist but it doesn't" and then after another fix again the first one and so on.
P.P.S: For the ALTER TABLE #__mail_templates DROP extension; we will already have that problem. First the checker finds the drop to be done, and when that is done the checker will find the later statement to add the column and complain, and so on and so on.
The alternative would be not to make it in any update SQL script but with script.php before the database update scripts are run when the database schema version is a 3.x version.
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2022-02-28 14:07:53 |
| Closed_By | ⇒ | richard67 |
It would have to be added to old update SQL script like e.g. "4.0.0-2018-05-15.sql" and others.
I could do that, but I always get trouble with @brianteeman when doing that because he thinks these script never should be touched again because otherwise someone will see they have updated and so run them again - which is silly and always was wrong to run an update SQL with a version older than the database schema version.
So at the moment I'm a bit reluctant to do that. I don't want trouble.