Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We want to create the equivalent of some materialized views in a read-only slave for really performant reporting. The queries used to generate these tables span 19 tables with a fair number of largish varchar columns. So when trying to materialize with a create table as select statement, we get an "ERROR 1118 (42000): Row size too large." error. Here is a very simple example illustrating what is happening:

create table foo (
  id int primary key auto_increment,
  foo varchar(40000)
);

create table bar (
  id int primary key auto_increment,
  bar varchar(40000)
);

create table baz as
select
  foo.id as id,
  foo.foo,
  bar.bar
from
  foo,
  bar
where
  foo.id = bar.id;

When running this, you get the following error:

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

A few sources suggest we might be able to use the InnoDB engine, barracuda file format and row_format of dynamic or compressed to get around some of these problems. I've updated the my.cnf as follows:

innodb_file_format = Barracuda
innodb_file_format_check = 1
innodb_file_format_max = Barracuda
innodb_file_per_table = 1

These settings look like they are in effect:

mysql> show variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

and I've modified the query as such:

create table baz engine=InnoDB row_format=dynamic key_block_size=16 as
select
  foo.id as id,
  foo.foo,
  bar.bar
from
  foo,
  bar
where
  foo.id = bar.id;

But get the same error. So my questions are:

  1. Can anyone verify that this approach has worked for them in a similar circumstance?

  2. Can anyone pinpoint what I may be missing in getting this to work correctly?

Thanks for any responses/additional information.

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.