Exporting Database – Error 1118

[Brian H.]

I am trying to export a complete database using the “Backup Database as SQL Dump” tool, and getting this error:

Error No.: 1118

Row size too large. The maximum row size for the used table type, not counting BLOBS, is 65525. you have to change some columns to TEXT or BLOBS.

This is the latest SQLyog, 8.12, running against MySQL 5.1.35 community. All options are checked on the export dialog.

Strangely, when I export this database from the command line with mysqldump, I do not get an error at all.

I have attached images of the export dialog and the error message.

Anyone have any clues as to what I'm doing wrong, or what the software might be doing wrong?

Thanks!

[peterlaursen]

It is a restriction with InnoDB. Refer to: http://dev.mysql.com/doc/refman/5.0/en/inn…strictions.html

#Although InnoDB supports row sizes larger than 65535 internally, you cannot define a row containing VARBINARY or VARCHAR columns with a combined size larger than 65535:

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”

.. but I can't explain why mysqldump does not suffer from this. Also not why it happens during export. We will need to study it.

Please tell:

1) the MySQL version

2) Can you paste the CREATE STATEMENT for the table (create a ticket for privacy if you wnat)

[Brian H.]

Thanks Peter for the quick response!

I have done more testing and discovered that this is actually a VIEW that his causing the error, not a “table.” But I'm not the developer, only an admin, so I'm not sure how to get the CREATE statement at this point until I talk to the developers and DBA. I assume that when I do a mysqldump that I am not actually dumping the VIEWs, and that is why I'm not getting the error. I'm not sure why an error isn't being thrown during the running of the application, but I will investigate further.

We are using MySQL version 5.1.35-community directly from MySQL.

peterlaursen wrote on Aug 14 2009, 12:35 PM:

It is a restriction with InnoDB. Refer to: http://dev.mysql.com/doc/refman/5.0/en/inn…strictions.html

#Although InnoDB supports row sizes larger than 65535 internally, you cannot define a row containing VARBINARY or VARCHAR columns with a combined size larger than 65535:

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”

.. but I can't explain why mysqldump does not suffer from this. Also not why it happens during export. We will need to study it.

Please tell:

1) the MySQL version

2) Can you paste the CREATE STATEMENT for the table (create a ticket for privacy if you wnat)

[peterlaursen]

3) tell the complete syntax you use for mysqldump (all parameters). To compare SQLyog and mysqldump the 'options' and 'parameters' must be comparable.

My only guess at the time is that one of the options you use in SQLyog causes the server to create a temporary table. And that fails. Note that all 4-diigit error messages are *MySQL Server errors* – ie. it is the server that returns the error – SQLyog only communicates ti to the user. However if you provide the schema we will probably be able to debug exactly where it happens.

Another important point: even though you can export with mysqldump, are you sure you will be able to import the dump? In my understanding you may get the same error when importing .. and as a result the mysqldump-dump could very well be rather useless (if it cannot be imported). I think you should check that!

[peterlaursen]

oops . . I did not see your last reply when I posted my last reply.

It looks like an issue that should be reported to bugs.mysql.com. The VIEW definition and the definition of all underlying tables would be useful.

[Author]

Posts