Bug #13567 VARCHAR columns require migration (TRIM) from MySQL 4.0 to MySQL 5.0

• Submitted: 28 Sep 2005 14:01
• Modified: 28 Sep 2005 15:11
• Reporter: Emmanuel KARTMANN
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.13-rc
• OS: Windows (Windows)
• CPU Architecture: Any

[28 Sep 2005 14:01] Emmanuel KARTMANN

Description:
When you migrate your data from MySQL 4.1.14 to MySQL 5.0.13-rc, some SQL requests using VARCHARs and GROUP BY stop working!!!

There is a painful workaround: TRIMming all VARCHAR columns... Clearly not enough!

How to repeat:

-- *****************
-- *** MySQL 4.0 ***
-- *****************
CREATE DATABASE IF NOT EXISTS temporary
;
DROP TABLE IF EXISTS temporary.mysql5_group_by_varchar_bug
;
CREATE TABLE temporary.mysql5_group_by_varchar_bug(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(25)
)
;
INSERT INTO temporary.mysql5_group_by_varchar_bug(code) VALUES('001'),('001'),('002')
;
-- Select with group by does work because spaces are trimmed: MAX(id) = 2
SELECT MAX(id) FROM temporary.mysql5_group_by_varchar_bug WHERE code = '001' GROUP BY code
;

-- *****************
-- *** MySQL 5.0 ***
-- *****************
-- MIGRATE TO MySQL 5.0.13-rc
CREATE DATABASE IF NOT EXISTS temporary
;
-- NET STOP MYSQL
-- COPY "C:\Program Files\MySQL\MySQL Server 4.1\data\temporary\mysql5_group_by_varchar_bug.*" "C:\Program Files\MySQL\MySQL Server 5.0\data\temporary\"
-- NET START MYSQL

-- Simple select works fine
SELECT * FROM temporary.mysql5_group_by_varchar_bug WHERE code = '001'
;
-- Select with "group by" DOESN'T WORK, probably because spaces are not trimmed (used to work on MySQL 4.0)
SELECT MAX(id) FROM temporary.mysql5_group_by_varchar_bug WHERE code = '001' GROUP BY code
;

-- Fix data with UPDATE
UPDATE temporary.mysql5_group_by_varchar_bug SET code = TRIM(code)
;
-- Now it works: MAX(id) = 2
SELECT MAX(id) FROM temporary.mysql5_group_by_varchar_bug WHERE code = '001' GROUP BY code
;

Suggested fix:
Well - fix GROUP BY (if it's a bug). Or add extra document on how to migrate data from MySQL 4.1 to 5.0, especially in the "What's New in MySQL 5.0" page:
    http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html

[28 Sep 2005 15:11] Hartmut Holzgraefe

see http://dev.mysql.com/doc/mysql/en/upgrading-from-4-1.html

* Incompatible change: The indexing order for end-space in TEXT  columns for InnoDB and MyISAM tables has changed. Starting from 5.0.3, TEXT indexes are compared as space-padded at the end (just like the way MySQL sorts CHAR, VARCHAR and TEXT fields). If you have a index on a TEXT column, you should run CHECK TABLE on it. If the check reports errors, rebuild the indexes: Dump and reload the table if it is an InnoDB table, or run OPTIMIZE TABLE or REPAIR TABLE if it is a MyISAM table.

* #

As of MySQL 5.0.3, trailing spaces no longer are removed from values stored in VARCHAR and VARBINARY columns. The maximum length for VARCHAR or VARBINARY now is 65,535 characters or bytes, respectively.

Note: If you create a table with new VARCHAR or VARBINARY columns in MySQL 5.0.3 or up, the table will not be usable if you downgrade to a version older than 5.0.3. Dump the table before downgrading and then reload it after downgrading.