0

I am trying to convert some of my DB tables from latin1 character set to utf-8. Many tables got converted fine, but some tables return this error:

alter table xyz convert to CHARACTER SET utf8; 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

Is there a work around for this? What is the min length column sizes I should have in the tables for the conversion to take place without problems? And also, how can I alter the columns that I need to alter without breaking the data in the table?

Here is the table by the way.

mysql> describe trailheads;
+-----------------------+----------------+------+-----+---------+----------------+
| Field                 | Type           | Null | Key | Default | Extra          |
+-----------------------+----------------+------+-----+---------+----------------+
| trailhead_id          | int(10)        | NO   | PRI | NULL    | auto_increment |
| trail_id              | int(10)        | YES  |     | NULL    |                |
| park_id               | int(10)        | YES  |     | NULL    |                |
| editor_member_id      | int(10)        | NO   |     | NULL    |                |
| trailhead_name        | varchar(1000)  | NO   |     | NULL    |                |
| trailhead_description | varchar(20000) | YES  |     | NULL    |                |
| parking               | tinyint(1)     | YES  |     | NULL    |                |
| parking_spots         | int(5)         | YES  |     | NULL    |                |
| is_free               | tinyint(1)     | YES  |     | NULL    |                |
| cost_details          | varchar(5000)  | YES  |     | NULL    |                |
| lat                   | float(9,6)     | NO   |     | NULL    |                |
| lng                   | float(9,6)     | NO   |     | NULL    |                |
| bathrooms_nearby      | tinyint(1)     | YES  |     | NULL    |                |
| wheelchair_accessible | tinyint(1)     | YES  |     | NULL    |                |
| date_added            | date           | NO   |     | NULL    |                |
| last_edit_date        | date           | YES  |     | NULL    |                |
+-----------------------+----------------+------+-----+---------+----------------+
Genadinik
  • 18,153
  • 63
  • 185
  • 284

1 Answers1

4

The problem is probably the trailhead_description field.

In newer mySQL versions, the VARCHAR field length denotes the maximum number of characters as opposed to bytes.

Because a UTF-8 character can be up to four bytes long, a 20,000 character VARCHAR might take up to 80,000 bytes - clearly beyond the maximum limit.

Changing that column to TEXT should work.

Pekka
  • 442,112
  • 142
  • 972
  • 1,088
  • Thank you - what would happen to the data in the column if I change it to text? – Genadinik May 25 '11 at 07:06
  • @Genadinik as long as they're the same encoding, I don't think anything in the data should change. (Still, it's of course always the right thing to do a backup before doing an ALTER TABLE) – Pekka May 25 '11 at 07:13