D7 upgrades: Crash due to different column name in url_alias table - `language` vs `langcode` (in specific MySQL/MariaDB versions)

[irinaz]

Description of the bug

When I am setting D7 to backdrop on Pantheon following steps described in https://fibonacciwebstudio.com/nota-bene/d7-backdropcms-migration-pantheon-webuisftp-option I get the following error

SQLSTATE[HY000]: General error: 1034 Incorrect key file for table 'url_alias'; This is similar to the problem described in a forum post https://forum.backdropcms.org/forum/d7-conversion-crash

This problem is fixed when I run code ​​ ALTER TABLE url_alias CHANGE language langcodeVARCHAR(12) NOT NULL DEFAULT '';

Steps To Reproduce

1. Setup Backdrop using Pantheon upstream https://dashboard.pantheon.io/sites/create?upstream_id=bf703821-4c18-45a1-88b8-3d9ec302273d
2. Install Backup and migrate module
3. Restore Drupal 7 db from /admin/config/system/backup_migrate/restore
4. Run /core/update.php

To reproduce the behavior follow steps described in detail in this blog post https://fibonacciwebstudio.com/nota-bene/d7-backdropcms-migration-pantheon-webuisftp-option

Actual behavior

D7 upgrade crashes if I do not modify url_alias table

Expected behavior

Expected behaviour is completion of the upgrade without interruption of additional steps to fix url_alias table.

[indigoxela]

@irinaz many thanks for reporting.

One thing baffles me: the conversion of that column should actually happen in function update_prepare_bootstrap() in file includes/update.inc.

I wonder what went wrong with your update, that you had to do it manually.

For reference: includes/update.inc

[irinaz]

@indigoxela, thank you, it is good to know that code is already there! I had this same issue very consistently on each site that I was converting on Pantheon platform. What would be best way to troubleshoot why it is happening? This is very naughty small bug and it would be great to fix it.

[indigoxela]

What would be best way to troubleshoot why it is happening?

I wish I could suggest something, but I'm absolutely not familiar with Pantheon.

Are there other problems or errors when upgrading? Anything afterwards in dblog? What's your PHP and database version?

[argiepiano]

I had this same issue very consistently on each site that I was converting on Pantheon platform

@irinaz, the error reported here may be the reason that paragraphs_update_1003() from Paragraphs is not running and getting you that other error you reported in Pararagraphs: https://github.com/backdrop-contrib/paragraphs/issues/119#issuecomment-1031061317

[irinaz]

PHP 7.2, documentation says "The default database version for new sites is MariaDB 10.4", I can check if that is the case. I created fresh site, got the same error, run same db ALTER TABLE url_alias CHANGE language langcodeVARCHAR(12) NOT NULL DEFAULT ''; After that upgrade completed and I found the original error message(s) in the log

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'langcode' in 'where clause': SELECT source FROM {url_alias} WHERE alias = :alias AND langcode IN (:langcode, :language_none) ORDER BY langcode ASC, pid DESC; Array ( [:alias] => home [:langcode] => en [:language_none] => und ) in backdrop_lookup_path() (line 160 of /code/core/includes/path.inc).

EntityStorageException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'auto' in 'field list' in EntityDatabaseStorageController->save() (line 786 of /code/core/modules/entity/entity.controller.inc).

I am not sure if this helps to identify where code related to url_alias path in includes/update.inc , line 149 is not being executed. There is a pointer to line 160 of /code/core/includes/path.inc, but I do not know that is helpful.

Thanks you all for helping with this!!

[indigoxela]

Column not found: 1054 Unknown column 'auto' in 'field list'...

That one has its own issue: #5497 - it might also play a role here. @irinaz are you able to test the other patch to see if you get better progress without manual fixes?

[irinaz]

In the local install I also have an issue with new role "administrator" created. Will post updates later this week when I have time to do good testing.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'role' in 'field list': SELECT role FROM {users_roles} WHERE uid = :uid; Array ( [:uid] => 1 )

Here is the error message that I am getting for that

Update #1001
    Failed: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '3-administer account settings' for key 'PRIMARY': INSERT INTO {role_permission} (`rid`, `permission`, `module`) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2), (:db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5); Array ( [:db_insert_placeholder_0] => 3 [:db_insert_placeholder_1] => administer account settings [:db_insert_placeholder_2] => user [:db_insert_placeholder_3] => 7 [:db_insert_placeholder_4] => administer account settings [:db_insert_placeholder_5] => user ) in user_update_1001() (line 238 of /code/core/modules/user/user.install).

[irinaz]

@indigoxela, I will reach out to you on zulip - I am not sure what is best way to apply both patches in the same way. thanks in advance!

[irinaz]

@indigoxela , Happy new year! I am back on this issue hoping to figure out how it can be fixed. I am working with the most recent version of Backdrop CMS 1.23.1 on Pantheon and assume that patches made it to the new release. I import database using Backup and migrate module, but still get the same error

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'langcode' in 'where clause': SELECT source FROM {url_alias} WHERE alias = :alias AND langcode IN (:langcode, :language_none) ORDER BY langcode ASC, pid DESC; Array ( [:alias] => home [:langcode] => en [:language_none] => und ) in backdrop_lookup_path() (line 160 of /code/core/includes/path.inc).

Thank you very much in advance for looking into this!!

I am working with the most recent version of Backdrop CMS 1.23.1 Install profile (pantheon) MySQL, MariaDB, or equivalent version 5.5.30

PHP 7.2, documentation says "The default database version for new sites is MariaDB 10.4", I can check if that is the case. I created fresh site, got the same error, run same db ALTER TABLE url_alias CHANGE language langcodeVARCHAR(12) NOT NULL DEFAULT ''; After that upgrade completed and I found the original error message(s) in the log

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'langcode' in 'where clause': SELECT source FROM {url_alias} WHERE alias = :alias AND langcode IN (:langcode, :language_none) ORDER BY langcode ASC, pid DESC; Array ( [:alias] => home [:langcode] => en [:language_none] => und ) in backdrop_lookup_path() (line 160 of /code/core/includes/path.inc).

EntityStorageException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'auto' in 'field list' in EntityDatabaseStorageController->save() (line 786 of /code/core/modules/entity/entity.controller.inc).

I am not sure if this helps to identify where code related to url_alias path in includes/update.inc , line 149 is not being executed. There is a pointer to line 160 of /code/core/includes/path.inc, but I do not know that is helpful.

Thanks you all for helping with this!!

[klonos]

@jenlampton I believe that you work with Pantheon, right? Have you seen this before, or can you help reproduce/troubleshoot? (I'm assuming that the D7 db needs to be from a multi-lingual site)

@irinaz do you happen to have a sanitized version of any specific D7 db that causes this issue? It would help us at least test and see if this is happening in our various locals (to illuminate this being a Pantheon-specific issue).

Wondering is the if (db_table_exists('url_alias') && db_field_exists('url_alias', 'language')) conditional in update_prepare_bootstrap() is getting itself in some race condition where the table or the filed are not loaded/detected properly, therefore not performing the required conversion 🤔

[argiepiano]

@irinaz, to me, the "red flag" in your last comment is this:

I import database using Backup and migrate module

If you are using the module Backup and migrate to import a D7 database into an already existing installation of Backdrop, you will run into all sort of issues. The problem here is that you need all the update processes to run before any bootstrapping happens in the site. The update processes will take care of dropping the column language and adding langcode in url_alias among many other things. This happens in update_prepare_bootstrap().

So, first thing I would do is avoid using Backup and migrate to import a D7 database to an existing B site. Instead, follow the tried-and-true steps.

Now, reading the first post, it seems like you did originally follow the official steps, but still run into this issue? I don't have Pantheon, so I can't really reproduce the original issue.

[irinaz]

@argiepiano , I am trying to set up process for D7 to Backdrop Update that will work well for sitebuiders implying that almost everything can be done through UI. I import D7 into fresh install of Backdrop either through "import" option in Pantheon UI or through Backup and Update now. Hopefully similar work can be done using https://github.com/backdrop-contrib/d2b_migrate when issues are solved there.

Also there is a forum post about same problem from 2019 referenced in issue summary, not sure if that is on Pantheon or not.

[argiepiano]

I import D7 into fresh install of Backdrop either through "import" option in Pantheon UI or through Backup and Update now

You probably mean Backup and migrate. Using that module will never work as things are in Backdrop. The problem is that after you import your DB from the UI, Backdrop bootstraps the site without having done any of the required update process. Plus there are tons of other issues, since you are importing a D7 database into an existing instance of a Backdrop database.

There is a reason why the steps outlined here specifically say that you have to "wipe out" all the Backdrop files and database before importing the D7 database and then run core/update.php

[irinaz]

This is a major blocker for D7 Soft landing initiative - I would like to advocate for solution of this challenge - @quicksketch @indigoxela @klonos

[jenlampton]

@jenlampton I believe that you work with Pantheon, right? Have you seen this before, or can you help reproduce/troubleshoot?

I don't actually do any development on the pantheon platform, I do it locally and push my completed project up to Pantheon. I also always follow the recommended steps and haven't ever tried to upgrade a Drupal 7 site inside an existing Backdrop site.

I expect that whatever tool we are using for assisting with the "upgrade inside" option would need to handle deleting all the config and otherwise making it match those recommended steps as closely as possible.

[irinaz]

@jenlampton @quicksketch , I tested importing db on local instance and on Pantheon, and it seems to be a Pantheon issue. I will try to find a different host and import db via backup and migrate. However, it would be a huge benefit to figure out why core/update.php runs differently on Pantheon, as Pantheon is currently the hosting company that provides my favorite "one-click" install for Backdrop in the easiest possible way.

[klonos]

@irinaz I just @-mentioned you on another thread in Zulip, relevant to an issue with slow config file read/write performance, which also only seems to be an issue in Pantheon: https://backdrop.zulipchat.com/#narrow/stream/218635-Backdrop/topic/Config.20and.20distributed.20filesystem.20performance

[argiepiano]

The title of this issue is misleading - it's a symptom of a bigger problem. The main issue here is that you are trying to migrate a D7 site by importing the database (with Backup and migrate) into an installed Backdrop site. That will never work with the current architecture. As @jenlampton mentioned in the recommended steps, the idea is that you have to completely wipe out your existing Backdrop database and config files, then import your database (Backup and migrate will not help here, since your Backdrop site is now wiped out and unbootable), and then visit /core/update.php.

Currently that's the only way in which you can upgrade a D7 site to Backdrop. The "one click" idea sounds great, but it just doesn't work with the way things are with Backdrop at the moment. Making it possible to import a whole D7 site into a an existing Backdrop site would take a lot of work and coding.

[irinaz]

@argiepiano , I was able to successfully run this process on my local MAMP installation. I believe that something is different in how Pantheon handles db import, maybe additional tables or some caching?

[klonos]

@argiepiano although I agree with what you said re it not being possible as it is currently, and re it requiring lots of work to make it happen, and also with what @jenlampton said re this not being the official/supported way, I have always found the way the D7 upgrades are being done with https://github.com/backdrop-contrib/d2b_migrate more appealing and straight-forward. But I'm digressing...

In this specific issue here, we know what the problem is (the specific db column does not have the expected name), and we have a fix that is confirmed to work. So we could explore the following options:

• at least for the 1.x cycle, anticipate the different column name, and accept both (fall back to language if langcode doesn't exist)
• if the language column exists, either automatically and transparently rename it, or throw a message in the status page and allow a manual trigger of the conversion (still via the UI with prompts)

I believe that we should be doing that if possible, instead of allowing the site to be "blowing up".

[klonos]

[comment moved to #5951 to avoid derailing the discussion here]

[argiepiano]

@klonos, I think it's great you are giving this a try. The d2b_migrate module uses a very different approach: it connects to a separate D7 database that's separate from the site's backdrop database. That approach is workable.

The approach tried above (importing a D7 db into an existing Backdrop's site's db) is, in my view, a very complex endeavor. It may work with simple sites (perhaps that's why @irinaz was successful in her local to make this work), but I don't think it will work on a more complex site.

I bet the missing column crash is just the tip of the iceberg here. In my experience after working with upgrades when developing for Rules and Organic Groups, there will be a score of other thing that will need fixing. Sorry to be a party pooper...

[klonos]

Sorry to be a party pooper...

Not a party pooper at all @argiepiano 😅 ...I don't mind exploring ideas that might make what people expect to work to actually work.

[irinaz]

@argiepiano I am using exactly same approach as d2b_migrate. d2b_migrate is based on backup_and_migrate - I am using a separate D7 db (local copy or accessing through mysql) and import it via UI.

[klonos]

...I've moved all my comments re virtual SQL tables/columns to #5951.

[argiepiano]

@argiepiano I am using exactly same approach as d2b_migrate. d2b_migrate is based on backup_and_migrate - I am using a separate D7 db (local copy or accessing through mysql) and import it via UI.

It's unclear to me what you mean. Can you explain? So, in your existing Backdrop site, you are going to admin/config/system/backup_migrate/restore and restoring your D7 backup into your "Default Database"? That will overwrite the existing Backdrop database.

I'm interested. Can you please explain how you are using Backup and migrate to move a D7 database into a separate database that's not your site's main Backdrop database?

[irinaz]

Can you explain? So, in your existing Backdrop site, you are going to admin/config/system/backup_migrate/restore and restoring your D7 backup into your "Default Database"? That will overwrite the existing Backdrop database.

@argiepiano Yes, that is what I am doing. It is my understanding that when I use backup and migrate module I overwrite the existing Backdrop database with the one that I import.

[irinaz]

This might be related to https://docs.backdropcms.org/api/backdrop/core%21includes%21update.inc/function/update_prepare_bootstrap/1

[klonos]

FTR, during the dev meeting today, we said that we should start by running select schema_version from system where name = “system“; before and after the problem occurs, in an effort to try to pinpoint what's happening.

We also summarized the following points:

• This issue is currently only reproducible in Pantheon - not on people's locals or in any other hosting.
• It doesn't seem to be some PHP 8.x incompatibility/breakage (since @irinaz mentions that this happens in PHP 7.2).
• This happens regardless of the method to import the D7 db (either via the UI provided by Pantheon, or via the Backup and Migrate module)

[jenlampton]

we should start by running

Some backstory: The query that Irina is running manually is supposed to be run during the upgrade process, in update_prepare_bootstrap(), on Pantheon, for some reason, it is not.

That query will only run if Backdrop has determined that the database belongs to a Drupal 7 site. it does that by checking the schema version of the system module, which lives in in the system table of the database.

The mysql query select schema_version from system where name = “system“; will tell us if the problem is with the value that is actually in the database at the time the code is running, or perhaps it is in one of Pantheon's fancy caching layers. (This is my leading theory, mostly because the problem only happens on Pantheon)

Before importing the Drupal 7 database, we would expect the result to be in the 1000s. After it should be in the 7000s. After running update.php, it will return to the 1000s.