Description:
There are a number of misleading and inconsistent errors and warnings issued when trying to create large UTF8 VARCHAR columns.
This problem exists for both InnoDB and MyISAM.
How to repeat:
SET @@sql_mode='';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c VARCHAR(21844) CHARACTER SET utf8);
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c VARCHAR(21845) CHARACTER SET utf8);
SHOW WARNINGS;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c VARCHAR(21846) CHARACTER SET utf8);
SHOW WARNINGS;
DROP TABLE IF EXISTS t1;
SET @@sql_mode='STRICT_ALL_TABLES';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c VARCHAR(21844) CHARACTER SET utf8);
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c VARCHAR(21845) CHARACTER SET utf8);
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c VARCHAR(21846) CHARACTER SET utf8);
DROP TABLE IF EXISTS t1;
Suggested fix:
mysql> SET @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1 (c VARCHAR(21844) CHARACTER SET utf8);
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE t1 (c VARCHAR(21845) CHARACTER SET utf8);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1118 | Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
OK, no problem with this one, it makes enough sense, even if it could specify "65535 bytes" to be a bit more clear.
mysql> CREATE TABLE t1 (c VARCHAR(21846) CHARACTER SET utf8);
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------+
| Note | 1246 | Converting column 'c' from VARCHAR to TEXT |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)
More silent conversions? We should stop doing this. Better said, we should *never* do this. Why not issue an error instead? This should behave exactly the same as the "21845" value.
mysql> SET @@sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1 (c VARCHAR(21844) CHARACTER SET utf8);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t1 (c VARCHAR(21845) CHARACTER SET utf8);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
Good, this is the same error that was thrown without STRICT mode.
mysql> CREATE TABLE t1 (c VARCHAR(21846) CHARACTER SET utf8);
ERROR 1074 (42000): Column length too big for column 'c' (max = 21845); use BLOB or TEXT instead
This is a totally new error, thrown only when STRICT mode is enabled. Why not just reuse the same error as used for the 21845 value? Or, why isn't this significantly more descriptive error referring to the column by name *and* giving the max in the same units as those used by the user? Also, why does it suggest that the maximum is "21845"? Trying to use that value throws the previous error, saying the maximum is "65535"!
There should be greater consistency with regard to
a) the errors and warnings issued
b) the values for which errors and warnings are issued
c) the units displayed in error messages, and the explicit reference to the unit of measure in the message (characters? bytes?)
d) eliminating implicit data type conversions entirely
