Bug #19236 bad COLUMNS.CHARACTER_MAXIMUM_LENGHT and CHARACTER_OCTET_LENGTH
Submitted: 21 Apr 2006 0:37 Modified: 13 May 2006 4:36
Reporter: Timothy Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0.21 OS:Any (any)
Assigned to: Sergey Glukhov

[21 Apr 2006 0:37] Timothy Smith
Description:
The INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH and INFORMATION_SCHEMA.COLUMNS.CHARACTER_OCTET_LENGTH return wrong values when using a multibyte character set.

How to repeat:
create table bad_info (f1 char(1) not null, f2 char(9) not null) default character set utf8;
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from information_schema.columns where table_schema='test' and table_name = 'bad_info';
insert into bad_info (f1, f2) values ('a', 'abcdefghi');
show warnings;
select * from bad_info;
show variables like '%version%';

mysql> create table bad_info (f1 char(1) not null, f2 char(9) not null) default character set utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from information_schema.columns where table_schema='test' and table_name = 'bad_info';
+--------------------------+------------------------+
| CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------------------+------------------------+
| 0                        | 1                      |
| 3                        | 9                      |
+--------------------------+------------------------+
2 rows in set (0.01 sec)

mysql> insert into bad_info (f1, f2) values ('a', 'abcdefghi');
Query OK, 1 row affected (0.02 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select * from bad_info;
+----+-----------+
| f1 | f2        |
+----+-----------+
| a  | abcdefghi |
+----+-----------+
1 row in set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+-----------------------------------------------+
| Variable_name           | Value                                         |
+-------------------------+-----------------------------------------------+
| protocol_version        | 10                                            |
| version                 | 5.0.20-debug-log                              |
| version_comment         | Latest delta: 2006/03/28 1.2107 bar@mysql.com |
| version_compile_machine | i386                                          |
| version_compile_os      | unknown-freebsd6.1                            |
+-------------------------+-----------------------------------------------+
5 rows in set (0.01 sec)

Suggested fix:
Looks like some formula is entered wrong.  It is dividing both values by the size of a single character.
[25 Apr 2006 8:10] Bugs System
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/5456
[25 Apr 2006 10:37] Alexander Barkov
The patch looks ok to push.
[12 May 2006 10:05] Bugs System
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/6294
[12 May 2006 10:55] Sergey Glukhov
Fixed in 5.0.22
[13 May 2006 4:36] Paul Dubois
Noted in 5.0.22 changelog.

In the <literal>INFORMATION_SCHEMA.COLUMNS</literal> table,
the values for the <literal>CHARACTER_MAXIMUM_LENGTH</literal>
and <literal>CHARACTER_OCTET_LENGTH</literal> columns were
incorrect for multi-byte character sets.