Joomla-cms: [4.0] Alpha 4 Installation problem Index column size too large. The maximum column size is 767 bytes.

[uzielweb]

Steps to reproduce the issue

Perform default installation

Expected result

Proceed to finish the installation

Actual result

Installation does not proceed after insert database form data (clicking in Install) and this message appears for a little moment before redirect to the first installation step:
"Index column size too large. The maximum column size is 767 bytes."

System information (as much as possible)

PHP 7.2
Joomla 4 alpha 4
My database version is MySQLi 5.5.5-10.1.34-MariaDB

Additional comments

[infograf768]

at which precise stage does it redirect to first step

[uzielweb]

Database installation step. After insert database user, name and host and click in Install

[ggppdk]

Which exact DB (MySql or MariaDB) do you have and which version of it ?

The default settings for some version of MySql <5.7.7 and also for Maria DB < 10.2 will behave like this

See here:
https://stackoverflow.com/questions/43379717/how-to-enable-large-index-in-mariadb-10

Joomla should not have such requirement for this kind of custom configuration of the database

[uzielweb]

MYSQLi mysqlnd 5.0.12-dev - 20150407 is my client
My database version is MySQLi 5.5.5-10.1.34-MariaDB

All Joomla installations works fine, except Joomla 4 Alpha 4

[csthomas]

Index on #__finder_logging.searchterm column should be limited to the first 191 characters, the same is in the #__session.session_id.

[C-Lodder]

Does J4 even support 5.0.12-dev @mbabker?

[csthomas]

5.0.12-dev is the client version, not mysql server version. See at https://stackoverflow.com/questions/35260305/database-client-version-in-phpmyadmin

[uzielweb]

MySQLi 5.5.5-10.1.34-MariaDB
This is my correct database version. :)

[uzielweb]

Table structure for table #__finder_logging can be the problem
steps to reproduce

• copy/paste joomla.sql into phpmyadmin SQL query form and execute
• message appears like this: Index column size too large. The maximum column size is 767 bytes.

[uzielweb]

Solution Found:
Modify joomla.sql installation file in the line 862 adding ROW_FORMAT=DYNAMIC before ";" character

like this:
CREATE TABLE IF NOT EXISTS#__finder_logging( searchtermVARCHAR(255) NOT NULL DEFAULT '', md5sumVARCHAR(32) NOT NULL DEFAULT '', queryBLOB NOT NULL, hitsINT(11) NOT NULL DEFAULT '1', resultsINT(11) NOT NULL DEFAULT '0', UNIQUE INDEXmd5sum(md5sum), INDEXsearchterm(searchterm) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

[uzielweb]

Now the steps stucks on the installation of Sample Data because the file sampledata does not exist. I will skip this step.

[uzielweb]

All is fine for now, Except sample data and idiom additional packages installation that cannot proceed.

[infograf768]

In 4.0, you can install blog sample data or multilingual sample data from the Control Panel.

Concerning ROW_FORMAT=DYNAMIC
See https://dev.mysql.com/worklog/task/?id=8307

What do you think @csthomas @mbabker @ggppdk
and @Hackwar who created this new table see https://github.com/joomla/joomla-cms/pull/20681#pullrequestreview-139531293

[csthomas]

The table is:

CREATE TABLE IF NOT EXISTS `#__finder_logging` (
  `searchterm` VARCHAR(255) NOT NULL DEFAULT '',
  `md5sum` VARCHAR(32) NOT NULL DEFAULT '',
  `query` BLOB NOT NULL,
  `hits` INT(11) NOT NULL DEFAULT '1',
  `results` INT(11) NOT NULL DEFAULT '0',
  UNIQUE INDEX `md5sum` (`md5sum`),
  INDEX `searchterm` (`searchterm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_general_ci;

The column searchterm can be reduced to 191 chars:

...
  `searchterm` VARCHAR(191) NOT NULL DEFAULT '',
...

or only the index should be shortened:

...
  INDEX `searchterm` (`searchterm`(191))
...

Additional note: this table does not have PRIMARY KEY. IMO should have it.

[alikon]

as i can see a lot of the new finder tables lacks of a primary key

[joomla-cms-bot]

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

[ghost]

closed as having Pull Request #21242