CODEWITHSUNDEEP
mysqlsql
gotali4396
gotali4396

Reputation: 53

Total row size is not above 65535 but I get "Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535" error

I would like to create this table:

CREATE TABLE IF NOT EXISTS `my-database`.`user_product` (
  `user_product_id` INT NOT NULL AUTO_INCREMENT,
  `FK_user_id` INT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `long_column_1` VARCHAR(10000) NOT NULL,
  `long_column_2` VARCHAR(10000) NULL DEFAULT NULL,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`user_product_id`),
  INDEX `user_id_idx` (`FK_user_id` ASC) VISIBLE,
  CONSTRAINT `FK_user_id`
    FOREIGN KEY (`FK_user_id`)
    REFERENCES `my-database`.`user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

client needs of two columns that can be very short or very long, "long_column_1" and "long_column_2", (ignore their name, it's just for the example). I read that TEXT type is slow compared to VARCHAR, probably 99% of times both columns will be shorter than 255, so I created them VARCHAR(10000) and not TEXT.

Why does it give "Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535" error?

The total size is not greater than 65535, because 10000+10000 = 20000.

This is the "user" table, just in case you need it:

CREATE TABLE IF NOT EXISTS `my-database`.`user` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) VISIBLE)
ENGINE = InnoDB;

I'm using MySql 8

Upvotes: 4

Views: 4127

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562240

MySQL must assume that the row will still fit in the worst case. That is, the row must fit even if you store long strings instead of short strings in both VARCHAR(10000) columns, and if you store multibyte characters in every position in the string.

In utf8mb4, if you store '😅' for 10000 characters, that takes 40000 bytes. Two such strings takes 80000 bytes. That's well over MySQL's row size limit of 65535.

You can fix this by defining the columns as TEXT instead of long VARCHARs. BLOB and TEXT (and their variations) don't count toward the row size limit.

Upvotes: 2

Related Questions