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

According to the documentation:

Although InnoDB supports row sizes larger than 65,535 bytes internally, MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns:

mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
    -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
    -> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;

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

(Unfortunately, this example does not provide the character set so we don't really know how large the columns are.)

The utf8 encoding uses 1, 2, or 3 bytes per character. So, the maximum number of characters that can safely fit in a page of 65,535 bytes (the MySQL maximum) is 21,845 characters (21,845*3 = 65,535).

Despite the versions being similar, it would appear the Windows is being conservative in its space allocation and guaranteeing that you can store any characters in the field. Linux seems to have a more laissez-faire attitude. You can store some strings with over 21,845 characters, depending on the characters.

I have no idea why this difference would exist in the same version. Both methods are "right" in some sense. There are simple enough work-arounds:

• Use TEXT.
• Switch to a collation that has shorter characters (which is presumably what you want to store).
• Reduce the size of the field.

Answer 2

please simply use TEXT to declare txt column

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` TEXT DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Answer 3

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.)

Answer 4

If you absolutely must use varchar - which is a bad solution to this problem! - then here's something you can try:

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(20000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `texts2` (
  `id` BINARY(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `txt` VARCHAR(20000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `texts3` (
  `id` BINARY(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `txt` VARCHAR(10000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

There's 50000 characters. Now your client application will have to manage breaking up the text into the separate chunks, and creating the records in each table. Likewise reading the text back in will require you to do 3 select statements, but you will then have 50000 characters.

It's just not at all recommended to do this with any database implementation.

I've worked in a few environments where large text was stored in columns in the database, and it always wound up causing more problems than it solved.

These should really be spooled to files on disk, and a reference to the full path to the file stored in the database.

Then run some indexing engine over this corpus of documents.

you will get greater scalability from this, and easier management.

Answer 5

Just to add for more clarity. If you are using a solution that definitely requires a long VarChar. Like in my case when trying to configure WatchDog.NET to use mysql database for a .NET web api log. You can sign into mysql database as root user and then run:

SET GLOBAL sql_mode = ""