Backdrop CMS 1.30.0 throws SQLSTATE[42000]: Syntax error or access violation: 1071 error

[prbt2016]

Description of the bug

A short description of what the problem is.

Hello,

I was in the process of manual installation of Backdrop CMS 1.30.0 on CentOS Linux release 7.9.2009 (Core) with PHP 5.6.40, MYSQL 5.5.62, Apache 2.2.34.

However, after entering database details on the 'Database configuration' step and clicking 'Save and continue' the following error is thrown i.e :

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

As shown in the screenshot below :

Could you please replicate and fix this?.

Steps To Reproduce

To reproduce the behavior:

1. Select language.
2. Enter database details on 'Database configuration' step, click 'Save and continue'
3. Above error is thrown.

Actual behavior

Above error is thrown.

Expected behavior

Bakcdrop CMS should populate whole database and move ahead to 'Site Settings' and ahead get installed successfully.

Additional information

Add any other information that could help, such as:

• Backdrop CMS version: 1.30.0
• Web server and its version: Apache 2.2.34
• PHP version: 5.6.40
• Database sever (MySQL or MariaDB?) and its version: MYSQL 5.5.62
• Operating System and its version: CentOS Linux release 7.9.2009 (Core)
• Browser(s) and their versions: 115.19.0esr (64-bit) Firefox.

[stpaultim]

@prbt2016 - One thing that would be helpful would be clarification if you tried the same process with BackdropCMS 1.29.3 or earlier?

It would be helpful for us to understand if this problem is new to 1.30.0 or if it's an existing problem.

[prbt2016]

Hello @stpaultim ,

Thanks for your quick reply. This isn't an existing issue . As I have installed previous version 1.29.3 and before without any issues on the same environment.

[stpaultim]

If this is a new bug in core, it seems like it might be related to this new feature in the 1.30 release:

Switch to a simplified array syntax for database connection information in settings.php
#2231
https://docs.backdropcms.org/change-records/database-configuration-can-now-be-stored-in-a-simple-array

Some other older issues that look like the could be related?
#805
#1588

[olafgrabienski]

To check if there is a general problem with Backdrop 1.30.0, I've installed a fresh 1.30 site in my shared hosting. This works fine. I have however newer PHP and database versions in my shared hosting.

Web search results regarding the error message indicate there may be an issue with the storage engine of the database. I've included "drupal" instead of "backdrop" in the search to get more results. My search phrase: drupal installation Specified key was too long; max key length is 767 bytes

[indigoxela]

I was in the process of manual installation of Backdrop CMS 1.30.0 on CentOS Linux release 7.9.2009 (Core) with PHP 5.6.40, MYSQL 5.5.62, Apache 2.2.34.

Backdrop CMS dropped support for MySQL 5.5 in 1.30.

That database version is EOL since 2018 - it's ancient.

Off topic: CentOS 7 and PHP 5.6 are ancient, too.

CentOS 7 is EOL since June 30, 2024

PHP 5.6 is officially unsupported since 31 Dec 2018

[indigoxela]

I have to correct myself, MySQL 5.5 is still supported, we just dropped support for versions older than 5.5. Sorry for the misleading info.

https://docs.backdropcms.org/change-records/minimum-mysql-version-is-now-550

[indigoxela]

As for the problem that triggered the bug report:

Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

There's loads on info available out there, how to set up the database appropriately to handle that. It may depend a lot on how it's currently set up.

Like switching to innodb and/or enabling multibyte support...

Example: innodb_file_format=Barracuda innodb_file_per_table=TRUE.... The information available for other web CMSes or any software built for mysql is also appropriate for Backdrop.

Whatever happens here - it happens outside Backdrop.

And I'm sorry, if my answer seems a bit rude, but the environment causing the issue is just soooo outdated.

[prbt2016]

Hello @indigoxela ,

Thanks for your reply. Thanks for the clarification . I had checked that here as well regarding MYSQL 5.5. https://docs.backdropcms.org/documentation/system-requirements

I comply with the same using MYSQL 5.5.62 .

Example: innodb_file_format=Barracuda innodb_file_per_table=TRUE.... The information available for other web CMSes or any software built for mysql is also appropriate for Backdrop.

Will have to check this as you mentioned . Since i didn't have any issues installing Backdrop till 1.29.3 on the same environment .

[indigoxela]

If that's a development environment, I'd suggest to consider building a new one. There's cool free software available for that. No need to stick with EOL stuff like that.

I do belief, the key-too-long problem can be solved by a better database configuration, but is the effort really still worth it?

At some point in the future, we'll also have to drop support for PHP 5.6 and possibly MySQL 5.5. We can't support them forever.

[prbt2016]

Hello @indigoxela ,

I do belief, the key-too-long problem can be solved by a better database configuration, but is the effort really still worth it?

At some point in the future, we'll also have to drop support for PHP 5.6 and possibly MySQL 5.5. We can't support them forever.

I got your points. Could you please update the same in documentation here as well
https://docs.backdropcms.org/documentation/system-requirements

I mean to say that the updated minimum PHP and MYSQL requirements on which Backdrop should be installed.

Since as you correctly said that MYSQL 5.5 and PHP 5.6 are very old and cannot be supported forever. It would be really great and helpful to the users if you could update them (PHP / MYSQL requirements) on the documentation link, so that the users wont face such issues, like I faced.

Kindly let me know if that's possible.

[indigoxela]

It would be really great and helpful to the users if you could update them (PHP / MYSQL requirements) on the documentation link, so that the users wont face such issues, like I faced.

The official docs are up to date. So, in fact, PHP 5.6 and MySQL 5.5 are both still supported in Backdrop 1.30.

The problem you encountered is very likely caused by something outside Backdrop, not something that changed in 1.30.

That you're having trouble to install 1.30 on your ancient infrastructure is unfortunate, but we can't reproduce the problem, so we can't offer any help. This means, you have to do some research on your own.

Hint: "Specified key was too long; max key length is 767 bytes" has very likely to do with multibyte support in MySQL. An index gets too long. It's unclear which one, so you have to dig on your own.

Hint 2: check the collation of your (newly created?) database and compare to existing ones (that worked).

Hint 3: check your database setup for innodb_large_prefix setting - if it's innodb anyway, and innodb_file_per_table

[argiepiano]

@prbt2016, question: in your site, were you using the string database setup in your site before updating to 1.30, or did you use the alternative array (which worked in 1.29) that looked like:

$databases = array(
  'default' => array (
    'default' => array (
      'database' => 'MYDATABASE',
      'username' => 'USER',
      'password' => 'PASSWORD',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

and is described here: https://docs.backdropcms.org/documentation/database-configuration

[stpaultim]

That you're having trouble to install 1.30 on your ancient infrastructure is unfortunate, but we can't reproduce the problem, so we can't offer any help. This means, you have to do some research on your own.

I understand the sentiment here, but would like to soften the message a bit. One of the selling points of Backdrop CMS is backward compatibility and ease of use. Lots of folks that use Backdrop CMS are not necessarily good at managing things like PHP or MySQL versions.

I think that @indigoxela is offering good advice, regarding the need to upgrade these tools if you are able. If you are using shared hosting, you might want to contact your provider.

If you are doing local development, you might consider using something like Lando or DDev, which make it quite easy to switch between MySQL or PHP versions.

Currently we do support PHP 5.6 and newer, but this may change VERY soon. It was brought up on our development meeting yesterday, that it may not be possible to support the latest version of PHP (8.4) AND PHP 5.6 at the same time. So, while no decision has been made, we may need to change the level of PHP support we provide very soon - possibly as soon as Backdrop 1.31. But, we will try to provide some advance notice of that as soon as a decision is made.

Ultimately, in my opinion, if we find that we did something in Backdrop that causes a break to sites on a supported version of PHP, we should try to fix it or update our support policy.

@prbt2016 If you need help untangling this and you are available during our weekly office hours, we might be able to offer advice there. You might also try our Zulip chat system to get help. But, if this is a configuration issue with your infrastructure, it's hard for us to provide support here in the core issue queue.

https://backdropcms.org/news/events/backdrop-office-hours-2

FYI - Here is some telemetry data that we collect regarding how many sites are using various versions of PHP and MySQL (providing this data is optional, so it is not comprehensive).
https://backdropcms.org/project/backdrop/telemetry

NOTE: I am puzzled about why you were able to install recent versions of Backdrop and not 1.30.

[quicksketch]

The issue that might have caused this problem is #6466.

Here's the actual lines that were changed: https://github.com/backdrop/backdrop/pull/4881/files

It seems probable that this issue is related because the changed lines were related to table indexes.

@prbt2016 Could you try modifying the lines in your core/includes/database/mysql/schema.inc file to put back the code that was there previously? It's possible that while the work-around was intended for MySQL 5.0 only, it might have continued to have an effect on newer versions of MySQL as well.

If that is the case, we may revert that change and update the code comment, indicating that it still has a purpose in newer MySQL versions.

[prbt2016]

Hello @quicksketch ,

Tried changing that , as you mentioned regarding revert i.e changed line 595 in includes/database/mysql/schema.inc from

    return $this->connection->query("SELECT table_comment FROM information_schema.tables WHERE " . (string) $condition, $condition->arguments())->fetchField();

to

    $comment = $this->connection->query("SELECT table_comment FROM information_schema.tables WHERE " . (string) $condition, $condition->arguments())->fetchField();
    // Work-around for MySQL 5.0 bug http://bugs.mysql.com/bug.php?id=11379
    return preg_replace('/; InnoDB free:.*$/', '', $comment);

That too doesn't work .

[indigoxela]

@prbt2016 my suspicion's still, that your problem has to do with database collation.

It now defaults to utf8mb4_general_ci, which seems to be sort of supported by your database server, but only partly.

Can you check, which collation the empty database you prepared actually has? Is that utf8mb4_general_ci?

Can you check, which collation one of the successfully installed Backdrop has? Is that utf8_general_ci or something completely different?

You should be able to see that with tools like phpmyadmin.

[prbt2016]

Hello @indigoxela ,

This is the db schema for 1.29.3 which was installed successfully with utf8_general_ci collation, without making any of those Barracuda changes to the server.

It's utf8_general_ci . Please have a look .

[indigoxela]

I see... Many thanks for the info. 👍

So, your database simply doesn't support utf8mb4_general_ci, at least, that's my conclusion.

This seem related to #2231

This is the check, that IMO doesn't work as expected: https://github.com/backdrop/backdrop/blob/1.x/core/includes/install.core.inc#L1109

@prbt2016 do you have a testing playground (dev environment) available?

Are you familiar with applying a patch? Here's a PR diff.

My PR strips the condition in install_settings_form_submit(), that can never be met - which prevents the fallback from working properly.

[yorkshire-pudding]

Thanks @indigoxela - In theory I think the condition could be met if someone entered that in the Advanced Configuration of the install form. However, I don't think that adding && $db_settings['charset'] === 'utf8mb4' is actually necessary for that particular check as it doesn't matter whether they have entered that or not; if the database doesn't support utf8mb4 then we should use utf8 The source of that line was https://github.com/backdrop/backdrop/pull/4567/files#r1795904628 though I really should have spotted that it wasn't necessary.

[indigoxela]

In theory I think the condition could be met if someone entered that in the Advanced Configuration of the install form.

The problem is, that this form doesn't even contain such a form item. It's not possible to set the charset in the advanced options. At least, I didn't see such an option in the install form advanced section.

[prbt2016]

Hello @indigoxela ,

Are you familiar with applying a patch? Here's a PR diff.

Well no . But yes i did try replacing

  if (install_verify_database_utf8mb4() === FALSE && $db_settings['charset'] === 'utf8mb4') {
    $db_settings['charset'] = 'utf8';
  }

to

  if (install_verify_database_utf8mb4() === FALSE) {
    $db_settings['charset'] = 'utf8';
  }

under core/includes/install.core.inc

As per the PR. But that didn't work as well . Same error.

For your kind info , here's screenshot of the only two tables getting created and rest get skipped >

[indigoxela]

@prbt2016 many thanks for testing. 🙏 So, this still needs work, as later on it's still attempted to create tables in utf8mb4_general_ci.

Did you change anything in the database server setup since it last worked?

In your phpmyadmin, on the start page, which collations/charset info do you see?

In particular: "Server connection collation" and "Server charset" seem relevant.

[prbt2016]

Hello @indigoxela ,

Well no change. This is the same environment as previous one. collation for database too same as last time i.e utf8_general_ci.

[indigoxela]

I think, if the database doesn't support it, we also have to set the collation accordingly.

@prbt2016 the latest commit does that. Can you give it another try with testing?

One additional line, see the diff here: https://github.com/backdrop/backdrop/pull/5001/files

[prbt2016]

Hello @indigoxela ,

Now whole thing looks like this, right? :

  if (install_verify_database_utf8mb4() === FALSE) {
    $db_settings['charset'] = 'utf8';
	$db_settings['collation'] = 'utf8_general_ci';
  }

Have tested the same . But doesn't work.

[indigoxela]

Hm... running out of ideas now.

My problem is, that I can't actually test the situation. I have no old database server without full support for uft8mb4 available.

[yorkshire-pudding]

Well the check for collation in the install code is:

  if ($db_settings['collation'] && $db_settings['collation'] != 'utf8mb4_general_ci') {
    $settings['database']['value']['collation'] = $db_settings['collation'];
  }

I think @prbt2016 that what you need to do is modify your settings.php file to include the collation that you actually do need on your database.

Apologies that the documentation hasn't been updated yet; I've not had time to complete but I am working on it. However, what you can do is:

  $database = array(
    'database' => 'database_name',
    'username' => 'user',
    'password' => 'pass',
    'host' => 'localhost',
    'charset' => 'utf8',
    'collation' => 'whatever_you_need',
  );

Update database, username, password and host to meet your needs.

Please report back when you know what collation you need. Maybe we can handle this better, but as @indigoxela says, this is hard to test as these conditions aren't supported in many environments.

[indigoxela]

I don't think, it's ideal to make it a requirement to adapt settings.php before installing, if uft8mb4 isn't supported, yet.
It apparently worked before 1.30 without adaptions, so I'd consider this a regression.

Defaulting to utf8mb4 is fine, but not providing a functioning fallback isn't what I'd expect. 😉

[indigoxela]

A quick idea... @prbt2016 did you use the existing (already written) settings.php file when testing? As that might already contain the wrong charset/collation info along with the db credentials. Did you start with a vanilla settings.php file? That's just to make sure, we're not struggling with something unexpected. 😉

[yorkshire-pudding]

@indigoxela - I've made a suggestion on the PR

[indigoxela]

@yorkshire-pudding I played with your suggestion locally ... with mixed success. Simulating no support for utf8mb4 by always returning false in install_verify_database_utf8mb4(), which is of course flaky. PR updated.

@prbt2016 when testing the latest changes, it's crucial that you reset your settings.php to the initial state, vanilla, just like freshly unpacked. Otherwise previous attempts get into the way.

It's also important to completely empty the database. Better drop and create a fresh one. Same for any existing config (if you even got that far). Please double check that your not-yet-installed Backdrop is clean.

And it's really safer to use the patch command, as the change got a bit bigger and every detail's important.

To apply a patch, go to the main directory of your Backdrop (where the settings.php file is), then download the patch there.

Then test if it applies (optional):

patch -p1 --dry-run < 5001.diff

Then apply it:

patch -p1 < 5001.diff

If that worked, give installing another try.

[yorkshire-pudding]

@prbt2016 - Do you have access to the command line?
If so, you should be able to download the patch by navigating to the backdrop folder as @indigoxela said, then:

wget https://patch-diff.githubusercontent.com/raw/backdrop/backdrop/pull/5001.diff

[yorkshire-pudding]

@prbt2016 - You can also get the entire file if that will be easier for you:

Go to https://github.com/backdrop/backdrop/blob/eb29686c7635d0150e5c607e119a6a367554ec16/core/includes/install.core.inc

Either click this button to download the file:

Or click the button next to it to copy the entire file; you can then replace the file