Support

The table referenced in the error message has misconfigured indexes. They are not compatible with the utf8mb4 encoding.

You can of course restore without using the utf8mb4 encoding by selecting (checking) the “Force UTF-8 collation on tables” option and deselecting (remove the checkmark from) the “Allow UTF8MB4 auto-detection” option in the Database Restoration step of the restoration script.

Yes, the problem is the database version and the database server's configuration.

The root cause of this issue is that in Joomla! 4.4 the maintainers increased the alias column length from 191 to 400 characters. This means that if you put it in an index it requires 1600 bytes instead of 764 bytes in UTF8MB4 which uses 4 bytes per character (1200 instead of 573 in plain old UTF8 which uses 3 bytes per character). This is a problem because MySQL 5.7 has a default hard limit of 767 bytes for the InnoDB engine, and 1000 bytes for the MyISAM engine.

This change was thoughtlessly backported from Joomla! 5 which requires MySQL 8 or later. In MyQSL 8 the hard limit is higher for InnoDB, 3072 bytes if the DYNAMIC row format is used, this row format being the default for MySQL 8 anyway.

This could all have been avoided if the unique key definition had `alias`(100) instead of `alias`. That's database management 101, but I guess the Joomla! maintainers skipped class the day we were talking about index lengths. Well. You've hit a bug in Joomla! which won't be fixed because Joomla! 4.4 is in security maintenance mode and this is a functional, not security, issue.

As a side note, I can also tell that your MySQL 5.7 server does not support InnoDB which is actually required for Joomla! 4 and later. I can tell that because the restoration script noticed that and tried to use the MyISAM storage engine instead.

That said, there are things you –or, rather, the server administrator– can do.

If you can upgrade the server to MySQL 8 that would be the best idea, since it ensures you can upgrade to Joomla! 5 and beyond. Do note that MySQL 5.7 is already end of life since October 2023. So, upgrading is the sanest option from every perspective: future-proofing, performance, and security.

If upgrading to MySQL 8 is not an option at this time, you can ask the server administrator to enable the InnoDB engine on the MySQL 5.7server, and enable the innodb_large_prefix option. This will work around this problem without causing any functional issues on your site, and without compromising its long term stability. The fact remains that MySQL 5.7 is End of Life and needs to be upgraded ASAP, but at least this workaround gives you a way to have a working site in the meantime.

Yes, as I said the change to that table only took place in 4.4.9. In previous versions of Joomla! 4 (from Joomla! 4.0 alpha 1 up to and including 4.4.8) the alias columns were defined as VARCHAR(191) for the index length reasons I mentioned. I was one of the developers involved in the original conversion of Joomla! 3 to UTF8MB4, this was a discussion we had back in the day. The change took place because one person had a problem with an alias that was longer than that.

I personally think it's having an alias even as long as 191 characters is ludicrous. Beyond around 70 characters of URL path length it's no longer a human-readable URL, and definitely not one a human can remember accurately. If you're not making URLs memorable to humans you might just as well use a random 32-character hex string as an alias, giving you over 2 billion possible articles within the same category with a much faster to index key. In other words, I find this change ridiculous and counter-productive. But I'm no longer involved in Joomla! maintenance in any capacity, so my opinion is just that.

As for your host, making it clear that they will stick with a database version which no longer receives security updates since October 2023 is beyond stupid. It's insane, in the literal sense. No sane person would choose to expose their clients and their infrastructure to security threats. They have their priorities wrong. You should indeed find a new host a.s.a.p. At this point it's more than an inconvenience, it's a security time bomb!

Ah, you are right, the change was actually there in 4.4.1: https://github.com/joomla/joomla-cms/blob/4ee38101e132f2b422a9cd15a2cb69c24bca45bb/installation/sql/mysql/base.sql#L461 I have gotten the timeline wrong. It looks like the change on that table was made on 4.0. Which basically means that Joomla 4.4's claim to support MySQL 5.6 or later is dubious at best.

Hm, this error message can be pretty ominous. It means the autonumber field is no longer autonumber. This requires doing something manually and explicitly to mess it up.

I will ask two questions.

1. Are you able to create a new article? Even an unpublished one, just for testing, would do.
2. How did you transfer your database content? Was it using CSV or XML files with phpMyAdmin?

I have a really bad feeling about this. Can you try creating a new article in the site you backed up from and tell me if you get the same error message?

Kernel, no. Database version, tangentially yes.

Given our discussion so far, I am wondering how you managed to restore the site on a MySQL 5.7 server which does not support InnoDB as this would have been impossible. The root cause probably lies in there.

I think that's the best approach for many reasons. So, I guess, we're leaving this ticket open as likely resolved by future action?

You're welcome!