MariaDB and InnoDB MySQL Row size too large

ERROR 1118 (42000) at line 876: Row size too large (> 8126).
Changing some columns to TEXT or BLOB or using 
ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help.
In current row format, BLOB prefix of 768 bytes is stored inline.

 

So – here we are. Switching to Maria – using the Percona wizard to create a new more optimal my.cnf, then realising that it will create all new tables in InnoDB as opposed to MYI / myISAM format.

Noting this – and having a few moments while a cPanel install occurs, I realise that I could dump the whole thing, and then reimport it again, forcing it all back into InnoDB format of my choice. Great success.

However it transpires there was badness lurking. InnoDB has a limitation on row size, and the above was the harbinger of this doom. Specifically stating the type as Barracuda – the most current innoDB structure means that Antelope will no longer be used. Apparently – and who am I to argue – this is a good thing too.

After a bit of reading around and realising I was missing something – I managed to resolve my issue.

How my issue differed was reasonably simple – I was importing – where as everyone else was talking about updating existing table formats.

Addition to to the /etc/mysql/my.cnf :

# SAFETY #
 max-allowed-packet = 16M
 max-connect-errors = 100000
 sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
 NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,
 NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
 sysdate-is-now = 1
 innodb = FORCE
 innodb-strict-mode = 1
 innodb_file_per_table = 1
 innodb_file_format = barracuda

Addition to the end of that specific CREATE TABLE statement within the SQL dump file I am trying to import for the table in question (which started its definition way back at the line mentioned above) :

DROP TABLE IF EXISTS `blah_bwg_theme`;
  /*!40101 SET @saved_cs_client = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `blah_bwg_theme` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `thumb_margin` int(4) NOT NULL,
  `thumb_padding` int(4) NOT NULL,
  `thumb_border_radius` varchar(32) NOT NULL,
  ...
  `carousel_title_border_radius` varchar(8) NOT NULL,
  `default_theme` tinyint(1) NOT NULL,
  `mosaic_thumb_transition` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 ROW_FORMAT=DYNAMIC;

Removing the DEFAULT there was the key it would seem.

Although – it begs the question as to why you need so many damned rows in a table?! Seriously? Have a word WordPress theme writing peoples.

However – in terms of a nightmare on a whimsical change – this is neither out of the ordinary and furthermore this was one and is now resolved.

Go Team.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this:
Skip to toolbar