[wp-trac] [WordPress Trac] #35249: Unit tests should pass

[WordPress Trac]

#35249: Unit tests should pass
------------------------------+------------------------------
 Reporter:  jrf               |       Owner:
     Type:  defect (bug)      |      Status:  new
 Priority:  normal            |   Milestone:  Awaiting Review
Component:  Build/Test Tools  |     Version:
 Severity:  normal            |  Resolution:
 Keywords:                    |     Focuses:
------------------------------+------------------------------

Comment (by dossy):

 Actually, I figured out exactly how to determine what the value is at
 runtime, just trigger the error... duh:

 {{{
 mysql> CREATE TABLE t (a VARCHAR(1000), b VARCHAR(1000), c VARCHAR(1000),
 d VARCHAR(1000), KEY (a, b, c, d)) ENGINE=MyISAM;
 ERROR 1071 (42000): Specified key was too long; max key length is 1000
 bytes
 }}}

 So, interesting:

 {{{
 > DROP TABLE IF EXISTS t; CREATE TABLE t (a VARCHAR(1), b VARCHAR(1001),
 KEY (b)) ENGINE=MyISAM;
 Query OK, 0 rows affected (0.00 sec)

 Query OK, 0 rows affected, 1 warning (0.00 sec)

 > SHOW WARNINGS;
 +---------+------+----------------------------------------------------------+
 | Level   | Code | Message
 |
 +---------+------+----------------------------------------------------------+
 | Warning | 1071 | Specified key was too long; max key length is 1000
 bytes |
 +---------+------+----------------------------------------------------------+
 1 row in set (0.00 sec)

 > DROP TABLE IF EXISTS t; CREATE TABLE t (a BIGINT, b VARCHAR(1000), KEY
 (a, b)) ENGINE=MyISAM;
 Query OK, 0 rows affected, 1 warning (0.00 sec)

 ERROR 1071 (42000): Specified key was too long; max key length is 1000
 bytes
 }}}

 So, for simple (single-column) indexes, MySQL treats exceeding the
 `MI_MAX_KEY_LENGTH` as a warning, and only uses the max length as a prefix
 index.  But, for compound (multi-column) indexes, it elevates it to an
 error which prevents the table from being created.

 What I don't get is why your MySQL croaked when `255 + 20 = 275`, nowhere
 near the `1000` limit.  On my system, it does the math right:

 {{{
 > DROP TABLE IF EXISTS t; CREATE TABLE t (a BIGINT(20), b VARCHAR(992),
 KEY (a, b)) ENGINE=MyISAM;
 Query OK, 0 rows affected, 1 warning (0.00 sec)

 Query OK, 0 rows affected (0.00 sec)

 > DROP TABLE IF EXISTS t; CREATE TABLE t (a BIGINT(20), b VARCHAR(993),
 KEY (a, b)) ENGINE=MyISAM;
 Query OK, 0 rows affected (0.00 sec)

 ERROR 1071 (42000): Specified key was too long; max key length is 1000
 bytes
 }}}

 `BIGINT` is 8 bytes, so `992 + 8 = 1000`, as expected.  Oh, wait ...

 {{{
 > DROP TABLE IF EXISTS t; CREATE TABLE t (a BIGINT(20), b VARCHAR(992)
 CHARACTER SET utf8mb4, KEY (a, b)) ENGINE=MyISAM;
 Query OK, 0 rows affected, 1 warning (0.00 sec)

 ERROR 1071 (42000): Specified key was too long; max key length is 1000
 bytes

 > DROP TABLE IF EXISTS t; CREATE TABLE t (a BIGINT(20), b VARCHAR(255)
 CHARACTER SET utf8mb4, KEY (a, b)) ENGINE=MyISAM;
 Query OK, 0 rows affected, 1 warning (0.00 sec)

 ERROR 1071 (42000): Specified key was too long; max key length is 1000
 bytes

 > DROP TABLE IF EXISTS t; CREATE TABLE t (a BIGINT(20), b VARCHAR(248)
 CHARACTER SET utf8mb4, KEY (a, b)) ENGINE=MyISAM;
 Query OK, 0 rows affected, 1 warning (0.00 sec)

 Query OK, 0 rows affected (0.00 sec)

 > DROP TABLE IF EXISTS t; CREATE TABLE t (a BIGINT(20), b VARCHAR(249)
 CHARACTER SET utf8mb4, KEY (a, b)) ENGINE=MyISAM;
 Query OK, 0 rows affected (0.00 sec)

 ERROR 1071 (42000): Specified key was too long; max key length is 1000
 bytes
 }}}

 And, there it is.  My `character_set_database = latin1` and I bet yours is
 set to `utf8mb4`.  You can confirm with `SHOW CREATE DATABASE <dbname>`.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/35249#comment:15>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform