mahara-contributors team mailing list archive

[Jasminl <2002038@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

While troubleshooting a failing upgrade from 22.04.2 to 22.10 (it was
complaining of mixed character set, mainly utf8 and utf8mb4 columns) , i
tried doing a fresh install of 22.10 to take a look at the database
schema and character sets. While running the installer, i get the
following error in my logs:

[DBG] 62 (lib/dml.php:180) mysqli error: [1071: Specified key was too long; max key length is 767 bytes] in EXECUTE("CREATE TABLE config (
[DBG] 62 (lib/dml.php:180)     field VARCHAR(255) NOT NULL,
[DBG] 62 (lib/dml.php:180)     value LONGTEXT,
[DBG] 62 (lib/dml.php:180) CONSTRAINT  PRIMARY KEY (field)
[DBG] 62 (lib/dml.php:180) )ENGINE=innodb")Command was: CREATE TABLE config (
[DBG] 62 (lib/dml.php:180)     field VARCHAR(255) NOT NULL,
[DBG] 62 (lib/dml.php:180)     value LONGTEXT,
[DBG] 62 (lib/dml.php:180) CONSTRAINT  PRIMARY KEY (field)
[DBG] 62 (lib/dml.php:180) )ENGINE=innodb

It seems the index should be of 191 instead of 255 since moving to
utf8mb4 from utf8. I can't say for sure if there are other cases where
the index is too long for utf8mb4 besides the "config" table.


The database was created with the following command: 
create database maharanew character set utf8mb4;

The database server is running these settings:
MariaDB [(none)]> SHOW VARIABLES LIKE 'c%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | utf8mb4                      |
| character_set_connection | utf8mb4                      |
| character_set_database   | utf8mb4                      |
| character_set_filesystem | binary                       |
| character_set_results    | utf8mb4                      |
| character_set_server     | utf8mb4                      |
| character_set_system     | utf8                         |
| character_sets_dir       | /usr/share/mariadb/charsets/ |
| collation_connection     | utf8mb4_unicode_ci           |
| collation_database       | utf8mb4_unicode_ci           |
| collation_server         | utf8mb4_unicode_ci           |

And in the my.cnf:

[client]
default-character-set = utf8mb4
[mysqld]
max_allowed_packet=500M
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_default_row_format = Dynamic
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake
[mysql]
default-character-set = utf8mb4


The O/S is CentOS 7.9, running mariadb 10.1 and 7.1.31.

** Affects: mahara
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: mahara-contributors
https://bugs.launchpad.net/bugs/2002038

Title:
  Fresh install of 22.10 failing with utf8mb4 related issues

Status in Mahara:
  New

Bug description:
  While troubleshooting a failing upgrade from 22.04.2 to 22.10 (it was
  complaining of mixed character set, mainly utf8 and utf8mb4 columns) ,
  i tried doing a fresh install of 22.10 to take a look at the database
  schema and character sets. While running the installer, i get the
  following error in my logs:

  [DBG] 62 (lib/dml.php:180) mysqli error: [1071: Specified key was too long; max key length is 767 bytes] in EXECUTE("CREATE TABLE config (
  [DBG] 62 (lib/dml.php:180)     field VARCHAR(255) NOT NULL,
  [DBG] 62 (lib/dml.php:180)     value LONGTEXT,
  [DBG] 62 (lib/dml.php:180) CONSTRAINT  PRIMARY KEY (field)
  [DBG] 62 (lib/dml.php:180) )ENGINE=innodb")Command was: CREATE TABLE config (
  [DBG] 62 (lib/dml.php:180)     field VARCHAR(255) NOT NULL,
  [DBG] 62 (lib/dml.php:180)     value LONGTEXT,
  [DBG] 62 (lib/dml.php:180) CONSTRAINT  PRIMARY KEY (field)
  [DBG] 62 (lib/dml.php:180) )ENGINE=innodb

  It seems the index should be of 191 instead of 255 since moving to
  utf8mb4 from utf8. I can't say for sure if there are other cases where
  the index is too long for utf8mb4 besides the "config" table.

  
  The database was created with the following command: 
  create database maharanew character set utf8mb4;

  The database server is running these settings:
  MariaDB [(none)]> SHOW VARIABLES LIKE 'c%';
  +--------------------------+------------------------------+
  | Variable_name            | Value                        |
  +--------------------------+------------------------------+
  | character_set_client     | utf8mb4                      |
  | character_set_connection | utf8mb4                      |
  | character_set_database   | utf8mb4                      |
  | character_set_filesystem | binary                       |
  | character_set_results    | utf8mb4                      |
  | character_set_server     | utf8mb4                      |
  | character_set_system     | utf8                         |
  | character_sets_dir       | /usr/share/mariadb/charsets/ |
  | collation_connection     | utf8mb4_unicode_ci           |
  | collation_database       | utf8mb4_unicode_ci           |
  | collation_server         | utf8mb4_unicode_ci           |

  And in the my.cnf:

  [client]
  default-character-set = utf8mb4
  [mysqld]
  max_allowed_packet=500M
  innodb_file_format = Barracuda
  innodb_file_per_table = 1
  innodb_default_row_format = Dynamic
  character-set-server = utf8mb4
  collation-server = utf8mb4_unicode_ci
  skip-character-set-client-handshake
  [mysql]
  default-character-set = utf8mb4

  
  The O/S is CentOS 7.9, running mariadb 10.1 and 7.1.31.

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/2002038/+subscriptions