[postfixadmin:bugs] #379 Setup fails on MySQL with non-Latin1 as default charset

[Paul P. <pa...@us...>]

New deployment of version 3.02 on Debian Stretch with setup.php?debug=1 shows this:
...

Running software:

    PHP version 7.0.15-1
    Apache/2.4.25 (Debian)
...
   Depends on: MySQL 4.1 - OK
...
Everything seems fine... attempting to create/update database structure

CREATE TABLE IF NOT EXISTS config ( `id` int(11) not null auto_increment primary key, `name` VARCHAR(20) /*!40100 CHARACTER SET latin1 */ NOT NULL DEFAULT '', `value` VARCHAR(20) /*!40100 CHARACTER SET latin1 */ NOT NULL DEFAULT '', UNIQUE name ( `name` ) ) ENGINE = MYISAM COMMENT = 'PostfixAdmin settings'

Updating database:

- old version: 0; target version: 1835
  (If the update doesn't work, run setup.php?debug=1 to see the detailed error messages and SQL queries.)

updating to version 1 (MySQL)...

CREATE TABLE IF NOT EXISTS admin ( `username` varchar(255) NOT NULL default '', `password` varchar(255) NOT NULL default '', `created` datetime NOT NULL default '2000-01-01 00:00:00', `modified` datetime NOT NULL default '2000-01-01 00:00:00', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`username`) ) ENGINE=MyISAM COMMENT='Postfix Admin - Virtual Admins';

DEBUG INFORMATION:
Invalid query: Specified key was too long; max key length is 1000 bytes 


Changing the function to "upgrade_1_mysql_DISABLED()" does not help:

Now the output is like this:

Updating database:

- old version: 1; target version: 1835
  (If the update doesn't work, run setup.php?debug=1 to see the detailed error messages and SQL queries.)

updating to version 2 (MySQL)...

DEBUG INFORMATION:
Invalid query: Table 'postfixadmin.domain' doesn't exist 


---

** [bugs:#379] Setup fails on MySQL with non-Latin1 as default charset**

**Status:** open
**Group:** v3.0
**Created:** Sat Sep 17, 2016 11:54 PM UTC by M. Buecher
**Last Updated:** Sat Dec 17, 2016 08:21 PM UTC
**Owner:** nobody


Experienced on Debian 8 with package postfixadmin 2.3.7-1 with MySQL 5.5.
An initial setup of postfixadmin with MySQL fails when a multi-byte charset is used as default (e.g. utf8mb4) due to key length limitiations. UTF8MB4 is need for 100% Unicode support as UTF8 has limitations in MySQL (https://mathiasbynens.be/notes/mysql-utf8mb4).

Error messages:
Invalid query: Specified key was too long; max key length is 767 bytes  (for [InnoDB key column](http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html))
Invalid query: Specified key was too long; max key length is 1000 bytes  (for [MyISAM table key](http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html))

Please consider adding settings in config.inc.php to specify the lengths of the following fields:
* domain
* username
* address
* alias_domain
* email

This will allow users to reduce the character length to keep the byte count under the key length limitation, while keeping a multi-byte charset as default for names, texts, etc.
Additionally remove please the LATIN1 definitions for complete tables (see list at the end of the report).


Otherwise specify LATIN1 on these fields in the CREATE statements of function upgrade_1_mysql() of upgrade.php, while keeping the MySQL default charset on the table and other fields.
Additionally please remove the LATIN1 definitions for complete tables and replace it with a LATIN1 definition only for the above mentions fields.
upgrade_1_mysql(): vacation table
upgrade_5_mysql(): all tables
upgrade_81_mysql(): vacation
upgrade_473_mysql: all tables



---

Sent from sourceforge.net because pos...@li... is subscribed to https://sourceforge.net/p/postfixadmin/bugs/

To unsubscribe from further messages, a project admin can change settings at https://sourceforge.net/p/postfixadmin/admin/bugs/options.  Or, if this is a mailing list, you can unsubscribe from the mailing list.