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

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

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.