MySQL Bugs: #27473: ARCHIVE: silently converts column type in 5.0/5.1

Bug #27473	ARCHIVE: silently converts column type in 5.0/5.1
Submitted:	27 Mar 2007 14:29	Modified:	10 Dec 2007 5:47
Reporter:	Paul DuBois	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Any (all)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
Consider these statements:

mysql> drop table t;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t(c varchar(22000) charset utf8) engine = archive;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1246 | Converting column 'c' from VARCHAR to TEXT | 
+-------+------+--------------------------------------------+
1 row in set (0.01 sec)

Okay, that CREATE TABLE statement succeeds.

Now consider the following statements, which attempt to create
a table with a *shorter* row length than the previous one
(21845 < 22000):

mysql> drop table t;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t(c varchar(21845) charset utf8) engine = archive;
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

It seems a little weird that a length of 22000 works, but
a lesser length like 21845 fails. (Both statements work in
MySQL 4.1, by the way.)

In any case, I think that the bug here isn't the failure to
convert the column type for the second CREATE TABLE
statement.  Aren't silent column type conversions not
supposed to happen in 5.0 and up?  If that's true (and
I think it is), both statements should fail if ARCHIVE
cannot create the requested table without changing
the column type.

How to repeat:
Execute these statements:

drop table if exists t;
create table t(c varchar(22000) charset utf8) engine = archive;
show warnings;
drop table if exists t;
create table t(c varchar(21845) charset utf8) engine = archive;
show warnings;

Suggested fix:
Reject the CREATE TABLE statement in 5.0 and up
if the table cannot be created without changing
column types.

Thank you for the bug report. Verified as described on FC 6.0 32-bit.

The maximum length of all rows combined can not be larger then 4gigs.

Hi, Paul.

We have two different length checks in the code (create table phase):

1. all non-blob fields bigger than MAX_FIELD_VARCHARLENGTH (65535)
are converted to blobs:

mysql> create table t1(a varchar(65536));
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1246 | Converting column 'a' from VARCHAR to TEXT | 
+-------+------+--------------------------------------------+

We have a note (maybe not enough clear) in the 5.0 documentation at
http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes.html
But in the 5.1 docs it's missed, I will file a bug.

Actually, we have another bug (will file it as well), one cannot create a 
table with a varchar(65535) field as the real maximum length of varchar
fields is 65535 - 2 (2 byte lenght we store in the field):

mysql> create table t1(a varchar(65534));
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

Moreover, one even cannot create a table t1(a varchar(65533)) as we have to 
store 1 bit for "row deleted" in the record.

2. if the total record length is bigger than HA_MAX_REC_LENGTH (65535 in most cases), we raise an ER_TOO_BIG_ROWSIZE error:

mysql> create table t1(a varchar(40000), b varchar(40000));
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

So, it's different cases and expected behaviour,
I'm closing this one as not a bug.

Please, let me know if you violently disagree and/or have any questions.
Thanks.

See Bug #33105: "Silent Column Specification Changes" page misses some conversions

See
Bug #19871(Not a Bug): varchar(65535) column cannot CREATE.
Bug #7417(Not a Bug): VARCHAR(65535) doesn't work.

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/47000

ChangeSet@1.2610, 2008-05-23 18:31:53+04:00, kaa@kaamos.(none) +5 -0
  Fix for bug #27473: Casting 'scientific notation type' to 'unsigned 
                      bigint' fails on windows.
  
  Visual Studio does not take into account some x86 hardware limitations
  which leads to incorrect results when converting some DOUBLE values
  to BIGINT UNSIGNED.
  
  Fixed by adding a workaround for double->ulonglong conversion on
  Windows.