MySQL Bugs: #52653: Record size have changed from 8052B to 8048B

Bug #52653	Record size have changed from 8052B to 8048B
Submitted:	7 Apr 2010 11:05	Modified:	10 Nov 2010 13:59
Reporter:	Johan Andersson	Email Updates:
Status:	In progress	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	6.0, 7.0.14, 7.1, record size

Description:
mysql> create table t_8052B(a integer primary key , b varchar(8048)) engine=ndb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8052. You have to change some columns to TEXT or BLOBs

mysql> create table t_8052B(a integer primary key , b varchar(8046)) engine=ndb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8052. You have to change some columns to TEXT or BLOBs

mysql> create table t_8052B(a integer primary key , b varchar(8044)) engine=ndb;
Query OK, 0 rows affected (0.43 sec)

Hmm.. could only create a record that is 8048B

How to repeat:
see above

Suggested fix:
either document the record size has changed (and change the printouts) or check why 4B has been stolen..

Another table:

CREATE TABLE t1(a integer primary key ,a1 CHAR(200) ,a2 CHAR(200) ,a3 CHAR(200) ,a4 CHAR(200) ,a5 CHAR(200) ,a6 CHAR(200) ,a7 CHAR(200) ,a8 CHAR(200) ,a9 CHAR(200) ,a10 CHAR(200) ,a11 CHAR(200) ,a12 CHAR(200) ,a13 CHAR(200) ,a14 CHAR(200) ,a15 CHAR(200) ,a16 CHAR(200) ,a17 CHAR(200) ,a18 CHAR(200) ,a19 CHAR(200) ,a20 CHAR(200) ,a21 CHAR(200) ,a22 CHAR(200) ,a23 CHAR(200) ,a24 CHAR(200) ,a25 CHAR(200) ,a26 CHAR(200) ,a27 CHAR(200) ,a28 CHAR(200) ,a29 CHAR(200) ,a30 CHAR(200) ,a31 CHAR(200) ,a32 CHAR(200) ,a33 CHAR(200) ,a34 CHAR(200) ,a35 CHAR(200) ,a36 CHAR(200) ,a37 CHAR(200) ,a38 CHAR(200) ,a39 CHAR(200) ,a40 CHAR(200) ,b1 BIGINT ,b2 BIGINT ,b3 BIGINT ,b4 BIGINT ,b5 BIGINT ,b6 BIGINT )engine=ndb

40 columns * 200B = 8000B
6 columns * 8B  = 48B
1 column * 4B = 4B
Sum= 8052B

Thank you for the report.

Verified as described. Although in my case it is not changed, but repeatable with old versions too.

When mysql server calculates the maximum row size, it does not simply add the real sizes of the fields. It adds some extra overhead. This extra overhead may differ for mysql server and the storage engines.

The server uses, e.g., 
- a bitmap vector to represent whether a field has null value or not, alighned to the nearest byte.
- 1 byte for field length < 256 and 2 bytes otherwise for field type VARCHAR.

The following error will be returned by the server if the calculated row sz > 8052:
ERROR 1118 (42000): Row size too large...

However, when a record is stored at the storage engines, they may add even more overhead according to how they store the records. In that case, though mysql server accepts the query, the storage engine may reject it. For example:

Rejected by the server:

mysql> create table t53(a integer primary key , b varchar(2012), c varchar(2012), d varchar(2012), e varchar(2004)) engine=ndb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8052. You have to change some columns to TEXT or BLOBs

Accepted by the server, but rejected by the NDB storage engine:

 create table t52(a integer primary key , b varchar(2012), c varchar(2012), d varchar(2012), e varchar(2002)) engine=ndb;
ERROR 1005 (HY000): Can't create table 'mysql.t52' (errno: 140)
mysql> show warnings; +-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Error | 1296 | Got error 738 'Record too big' from NDB     |
| Error | 1005 | Can't create table 'mysql.t52' (errno: 140) |
+-------+------+---------------------------------------------+
2 rows in set (0.00 sec)

Accepted by both:

mysql> create table t50(a integer primary key , b varchar(2012), c varchar(2012), d varchar(2012), e varchar(1998)) engine=ndb;
Query OK, 0 rows affected (0.40 sec)

To be documented and/or the Error message should be changed as follows:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8052, which includes the real size + some storage overhead. You have to change some columns to TEXT or BLOBs.

I think it should not say "some overhead". The error message should be as simple as possible and state the actual max record length, and nothing else.

I am not so happy if the max record size will change every now and then, since it will have impact on customers, since it will prevent upgrades.

BR
johan

I agree with your first comment, however the fact that max size includes storage overhead must be documented, in order to avoid wondering why ERROR 1118 (42000) is returned even if the real size was < max size.

There's no doubt that max size or the storage format change causes substantial impact on the customer. Therefore, it is not changed that often, unless there's a solid product requirement. When such change occurs, the release notes will document how the upgrade process should be performed. Usually the new code takes care of running both formats at the same time, such that the old tables use the old format while any new table created with the newer version will use the new format; or a procedure to port the old data from the old format to new format.

Jonas already works on increasing number of columns and likely soon also the row size. I propose though to fix this so that we throw a correct error message (as Johan suggests) if possible. Implementation should obviously work now and after any change of row size.

The overhead calculation should be added to the documentation and the error msg should be improved in either of the following ways:

a) the actual calculated size is given together with the max size (extend the err msg with another parameter)

errmsg.txt:

ER_TOO_BIG_ROWSIZE 42000
        eng "Row size too large. The maximum row size for the used table type, not counting BLOBs, is %ld, including overhead. The actual row size for the table being created is %ld. You have to change some columns to TEXT or BLOBs"

b) the err.msg mentions about the overhead, like this:

ER_TOO_BIG_ROWSIZE 42000
        eng "Row size too large. The maximum row size for the used table type, not counting BLOBs, is %ld, including overhead (see the documentation). You have to change some columns to TEXT or BLOBs"

The size calculation and the returning of the error msg is performed in unireg.cc.

Fixing the error message should be done in the server code, so IMHO, this bug should be transferred to server team.

Can we clarify that :
 1) The maximum row size has *not* actually changed
 2) This bug is now concerned with improving the error message?

1) The maximum row size has not changed (NDB_MAX_TUPLE_SIZE (8052) and the check in unireg.cc is the same in the earlier versions, e.g., in 6.2).

2) Yes, as agreed with the reporter of the bug, improving error msg/documentation will be satisfactory. Excerpts from the mails offline:

Johan: Perhaps we can just have the error message as:
" Row size too large. The max record length is 8052B (8048?) including overhead. Your row size including overhead is <calculated size>. You have to change some columns to TEXT or BLOBs". 

Maitrayi: My idea was to mention in the error msg that the max size calculation includes an overhead and the cluster manual will explain how the overhead is calculated.

Johan: Sure this is fine - just so that is possible to find this documentation somewhere! I thought it would have been possible to do this calc when the error message was thrown and put the result in the error message. But if that is too much work, then fine to put in documentation.

New bug report to fix mysql server error message is created: bug#54963  	Enhance the error message to reduce user confusion.

This bug report will be used to enhance the ndb error code:
  { 738,  HA_WRONG_CREATE_OPTION, SE, "Record too big" },

---->

  { 738,  HA_WRONG_CREATE_OPTION, SE, "Record too big. Record size calculation includes storage overhead, see the documentation" },

And the documentation will explain how ndb calculates the overhead.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/113006

3647 Maitrayi Sabaratnam	2010-07-07
      Bug#52653 Record size have changed from 8052B to 8048B

Had a discussion (in ndbdev) with Jonas regarding the different max_record_size used by the server and ndb during to reject a create table, as mentioned in earlier comments.

His idea was to perform the test only at the storage module, not at the server or dictionary. Agreed. This fix will be added in addition to fix the error msg as agreed earlier.