MySQL Bugs: #21681: ALTER TABLE CONVERT TO CHARACTER SET utf8, BLOB coumns

Bug #21681	ALTER TABLE CONVERT TO CHARACTER SET utf8, BLOB coumns
Submitted:	16 Aug 2006 23:03	Modified:	24 Aug 2006 13:24
Reporter:	Alexandre Pereira	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.11	OS:	Linux (Linux 2.6)
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, BLOB, CONVERT TO CHARACTER SET utf8, innodb

Description:
Although the Mysql manual says that I can change the CHARACTER SET of a table that contains BLOB columns (it even says to change VARCHAR columns to BLOB, so their values won't be affected by the conversion), I didn't work with me.

I tried to alter all tables in a database from charset latin1 to utf8.
I used the following syntax:
ALTER TABLE tab CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
When I changed a table with a BLOB column, the 'mysql' server started restarting every 2 minutes.

I had to drop the database and restore it from a backup. I tried this twice with always the same results.
Then I changed all tables except for those with BLOB columns and had no more problems.

How to repeat:
Here are some of my tables:

CREATE TABLE IF NOT EXISTS obj (
  obj_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  obj_nome VARCHAR(255) DEFAULT NULL,
  obj_tipo VARCHAR(255) DEFAULT NULL,
  obj_tam INT DEFAULT NULL,
  obj_cobid INT,
  INDEX fkix_objcobid (obj_cobid),
  FOREIGN KEY (obj_cobid) REFERENCES catobj (cob_id) ON UPDATE CASCADE
  )ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS bin (
  bin_objid INT NOT NULL PRIMARY KEY,
  bin_bloco MEDIUMBLOB NOT NULL,
  FOREIGN KEY (bin_objid) REFERENCES obj (obj_id) ON DELETE CASCADE
  )ENGINE=INNODB DEFAULT CHARSET=latin1;

I use them to store images (6kb size) [bin] and metadata [obj].
I only have 12 rows in each table.

The problem happened with the following SQL string:

ALTER TABLE bin CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I changed the severity to critical.

Thank you for the report.

I can not repeat it neither using 5.1.11-beta, nor last 5.1 BK sources.

Could you please provide us content of error log, dump of table bin, dump of table obj, accurate version of your platform and operation system?

I'm using MySQL 5.1.11-Beta, not a snap.
I'm using Slackware Linux, pre-11 releas, a snap from last month, compiled by myself for 2 processors [Pentium D] and 4GB RAM)

# uname -a
Linux agua 2.6.16.9A #2 SMP Sat May 6 23:26:53 WEST 2006 i686 pentium4 i386 GNU/Linux

I don´t have the error log because I had to reinstall MySQL, and I deleted the mysql directory. I'll try to repeat the bug and send that information to you asap.

Thank you, Alexandre, for the feedback. Could you please provide dump of table bin and dump of table obj?

I installed a new snap of the operating system (Slackware Linux 2.6.16.9; Aug 19, 2006) and compiled MySQL again in this OS.

Now I can't repeat the submitted bug, so I come to the conclusion it was not a MySQL bug. I'm changing the status to "Closed".

"Not a Bug" will be more appropriate status I think.