Installation problems with

mambax7 commented 3 years ago

I ran into issues today when installing XOOPS - some of the extensions and modules would not install, showing error like this one:

Error : An exception occurred while executing 'CREATE TABLE comments (id INT UNSIGNED AUTO_INCREMENT NOT NULL, pid INT UNSIGNED DEFAULT 0 NOT NULL, ***id INT UNSIGNED DEFAULT 0 NOT NULL, modid SMALLINT UNSIGNED DEFAULT 0 NOT NULL, itemid INT UNSIGNED DEFAULT 0 NOT NULL, icon VARCHAR(25) DEFAULT '' NOT NULL, created INT UNSIGNED DEFAULT 0 NOT NULL, modified INT UNSIGNED DEFAULT 0 NOT NULL, uid INT UNSIGNED DEFAULT 0 NOT NULL, ip VARCHAR(45) DEFAULT '' NOT NULL, title VARCHAR(255) DEFAULT '' NOT NULL, text LONGTEXT DEFAULT NULL, sig TINYINT(1) DEFAULT '0' NOT NULL, status TINYINT(1) DEFAULT '0' NOT NULL, exparams VARCHAR(255) DEFAULT '' NOT NULL, dohtml TINYINT(1) DEFAULT '0' NOT NULL, dosmiley TINYINT(1) DEFAULT '0' NOT NULL, doxcode TINYINT(1) DEFAULT '0' NOT NULL, doimage TINYINT(1) DEFAULT '0' NOT NULL, dobr TINYINT(1) DEFAULT '0' NOT NULL, INDEX comments_pid (pid), INDEX comments_itemid (itemid), INDEX comments_uid (uid), INDEX comments_title (title), INDEX comments_status (status), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB': SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.

Similar problems with "protector_access" and "plugins_plugin" tables.

I realized that it was related to utf8mb4_unicode_ci collate, so I've tried to lower the sizes in the indexes, e.g. by setting it to a lower number, which helped me in the past:

KEY title (title(30)),

but it didn't help.

Only after I've added this line to the MySQL's my.ini file: innodb_default_row_format=dynamic

I was able to install it.

Maybe we could add it to the Installation Readme file?

zyspec commented 3 years ago

Are you using MySQL? If so, which version of database are you using? Was there a previous value set for 'innodb_default_row_format' in your MySQL .ini file? Compact is the default for v5.6 but Dynamic is the default for v5.7+. I haven't done much with v2.6 codebase but here's a couple of observations.

Since the 'com_' prefix has been removed from the column names there are 2 columns that now use SQL reserved words (status and text). Since these reserve names are used it could be causing some issues - for example you might try deleting the 'KEY status (status)' line to see if that changes anything.
It's also interesting the error message you stated shows rootid column name as ***id. Not sure exactly what that's about.
Protector has similar 'issues' with column names (protector_log -> type column, protector_access-> expire column (depending on MySQL version)).
However this doesn't explain what might be going on with plugins_plugin.

mambax7 commented 3 years ago

I was using MySQL 5.7.36, and there was no previous default set for "innodb_default_row_format" Here is the part of the my.ini related to InnoDB:

innodb-adaptive-hash-index=on
innodb-buffer-pool-dump-now=off
innodb-buffer-pool-dump-at-shutdown=off
innodb-buffer-pool-instances=2
innodb-buffer-pool-load-at-startup=off
innodb-buffer-pool-size=1G
innodb-data-file-path=ibdata1:12M:autoextend:max:500M
innodb-default-row-format=compact
innodb-doublewrite=on
;skip-innodb_doublewrite
innodb-file-per-table=1
innodb-flush-log-at-trx-commit=1
innodb-flush-method=normal
;innodb-force-recovery=1
innodb-ft-enable-stopword=off
innodb-ft-max-token-size=10
innodb-ft-min-token-size=0
innodb-io-capacity=2000
innodb-max-dirty-pages-pct=90
innodb-lock-wait-timeout=600
innodb-log-buffer-size=16M
innodb-log-file-size=20M
innodb-log-files-in-group=2
innodb-max-dirty-pages-pct=80
innodb-optimize-fulltext-only=1
innodb-page-size=16K
innodb-purge-threads=10
innodb-read-io-threads=10
innodb-stats-on-metadata=0
;innodb-strict-mode=off
innodb-thread-concurrency=16
innodb-undo-log-truncate=on
innodb-write-io-threads=4

The other errors that I saved are here below, but there were few other cases:

Error : An exception occurred while executing 'CREATE TABLE protector_access (ip VARCHAR(255) DEFAULT '0.0.0.0' NOT NULL, request_uri VARCHAR(255) DEFAULT '' NOT NULL, malicious_actions VARCHAR(255) DEFAULT '' NOT NULL, expire INT DEFAULT 0 NOT NULL, INDEX ip (ip), INDEX request_uri (request_uri), INDEX malicious_actions (malicious_actions), INDEX expire (expire)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB': SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.

Error : An exception occurred while executing 'CREATE TABLE plugins_plugin (plugin_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, plugin_caller VARCHAR(255) DEFAULT '' NOT NULL, plugin_listener VARCHAR(255) DEFAULT '' NOT NULL, plugin_status TINYINT(1) DEFAULT '1' NOT NULL, plugin_order SMALLINT DEFAULT 0 NOT NULL, INDEX idxcaller (plugin_caller), INDEX idxlistener (plugin_listener), INDEX idxstatus (plugin_status), PRIMARY KEY(plugin_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB': SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.

I found the solution that I used here: https://github.com/go-gitea/gitea/issues/2979

zyspec commented 3 years ago

The 8th line of your my.ini looks like it hadinnodb-default-row-format=compactOn Oct 30, 2021 6:20 PM, Michael Beck @.***> wrote: I was using MySQL 5.7.36, and there was no previous default set for "innodb_default_row_format" Here is the part of the my.ini related to InnoDB: innodb-adaptive-hash-index=on innodb-buffer-pool-dump-now=off innodb-buffer-pool-dump-at-shutdown=off innodb-buffer-pool-instances=2 innodb-buffer-pool-load-at-startup=off innodb-buffer-pool-size=1G innodb-data-file-path=ibdata1:12M:autoextend:max:500M innodb-default-row-format=compact innodb-doublewrite=on ;skip-innodb_doublewrite innodb-file-per-table=1 innodb-flush-log-at-trx-commit=1 innodb-flush-method=normal ;innodb-force-recovery=1 innodb-ft-enable-stopword=off innodb-ft-max-token-size=10 innodb-ft-min-token-size=0 innodb-io-capacity=2000 innodb-max-dirty-pages-pct=90 innodb-lock-wait-timeout=600 innodb-log-buffer-size=16M innodb-log-file-size=20M innodb-log-files-in-group=2 innodb-max-dirty-pages-pct=80 innodb-optimize-fulltext-only=1 innodb-page-size=16K innodb-purge-threads=10 innodb-read-io-threads=10 innodb-stats-on-metadata=0 ;innodb-strict-mode=off innodb-thread-concurrency=16 innodb-undo-log-truncate=on innodb-write-io-threads=4

The other errors that I saved are here below, but there were few other cases: Error : An exception occurred while executing 'CREATE TABLE protector_access (ip VARCHAR(255) DEFAULT '0.0.0.0' NOT NULL, request_uri VARCHAR(255) DEFAULT '' NOT NULL, malicious_actions VARCHAR(255) DEFAULT '' NOT NULL, expire INT DEFAULT 0 NOT NULL, INDEX ip (ip), INDEX request_uri (request_uri), INDEX malicious_actions (malicious_actions), INDEX expire (expire)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB': SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes. Error : An exception occurred while executing 'CREATE TABLE plugins_plugin (plugin_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, plugin_caller VARCHAR(255) DEFAULT '' NOT NULL, plugin_listener VARCHAR(255) DEFAULT '' NOT NULL, plugin_status TINYINT(1) DEFAULT '1' NOT NULL, plugin_order SMALLINT DEFAULT 0 NOT NULL, INDEX idxcaller (plugin_caller), INDEX idxlistener (plugin_listener), INDEX idxstatus (plugin_status), PRIMARY KEY(plugin_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB': SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes. I found the solution that I used here: go-gitea/gitea#2979

—You are receiving this because you commented.Reply to this email directly, view it on GitHub, or unsubscribe.Triage notifications on the go with GitHub Mobile for iOS or Android.

mambax7 commented 3 years ago

Ouch! I was only searching for the whole thing to see if I already have it:

innodb_default_row_format=dynamic

because then I would google for another solution, so as a result, I've missed this line;

innodb-default-row-format=compact

MekDrop commented 2 years ago

There is a way to change this without changing configuration. You need to run this line for every just created table:

ALTER TABLE `test`  ROW_FORMAT=DYNAMIC;

Same ROW_FORMAT=DYNAMIC; can be used in CREATE TABLE statement.

This issue can happen not only because of different MySQL version but also due OS. For unknown reasons for me different OS'es uses different default my.cnf config. So it's better to alter installation scripts.

Another way to fix this issue - everywhere where is possible instead of VARCHAR(255) use TINYTEXT (same with bigger VARCHARS) or specify index length (I didn't tried this option yet).

mambax7 commented 2 years ago

Thank you for the tips - I saved them for my future references. Normally I've specified the index length, and it worked without any problems.

XOOPS / XoopsCore

Installation problems with #632