MySQL: column size limit

Question

I'm currently working on a Windows OS and I have installed MySQL community server 5.6.30 and everything is fine. I have a script that initializes the DB and again, everything works fine.

Now I'm trying to run this script on a Linux environment -- same MySQL version -- and I get the following error:

ERROR 1074 (42000) at line 3: Column length too big for column 'txt' (max = 21845); use BLOB or TEXT instead

Script -

DROP TABLE IF EXISTS text;
CREATE TABLE `texts` (
  `id` BINARY(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `txt` VARCHAR(50000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Obviously there's some MySQL server configuration on my Windows OS that I need to replicate on Linux; can anyone share an ideas?

Update 1

on AWS's RDS it also works and im pretty sure its just a service on top of linux so obviously its just a config issue.

does any body knows how to reach varchar 50k with UTF8 ?. i dont want to use TEXT or MEDIUMTEXT or any else , just plain old varchar(size)

Update 2

i appreciate the different solutions that were suggested but im not looking for a new solution im only looking for an answer as do why varchar(50k) works under windows and under linux it doesnt. Btw , im using charcter set UTF8 and collation utf8_general_ci .

Answer

to answer my own question , it was an issue with the SQL_MODE it was set to STRICT_TRANS_TABLES and should have been removed.

Answer 1

utf8 needs up to 3 bytes per character. utf8mb4: 4; latin1: 1; ascii: 1; etc. VARCHAR(N) is implemented as a 1- or 2-byte length in front of the bytes for the text. That is allowed to hold N characters (not bytes). So, if you say you want utf8, then 3*N must be less than 65535, the max value for a 2-byte length.

Be glad you are not running in some old version, where VARCHAR had a limit of 255.

If your txt does not need characters other than ascii or English, then use CHARACTER SET latin1.

In InnoDB, when there are 'long' fields (big varchars, texts, blobs, etc), some or all of the column is stored in a separate block(s). There is a limit of about 8000 bytes for what is stored together in the record.

If you really need 50K of utf8, then MEDIUMTEXT is what you need. It uses a 3-byte length and can hold up to 16M bytes (5M characters, possibly more, since utf8 is a variable length encoding).

Most applications can (should?) use either ascii (1 byte per character) or utf8mb4 (1-4 bytes per character). The latter allows for all languages, including Emoji and the 4-byte Chinese characters that utf8 cannot handle.

As for why Windows and Linux work differently here, I don't know. Are you using the same version? Suggest you file a bug report with http://bugs.mysql.com . (And provide a link to it from this Question.)