Hi! I am posting this separate note about this change in 4.1.3, because it is unusual that a data conversion is needed in a MySQL server upgrade.
The default charset of MySQL was latin1 in 3.23 and in 4.0, and it is latin1_swedish_ci from 4.1.2 on. InnoDB users who have used a non-default charset in 3.23 or 4.0 or <= 4.1.2, and ALL users of 4.1.0 and 4.1.1, MAY NEED TO REBUILD their tables when upgrading to 4.1.3 or later. http://dev.mysql.com/doc/mysql/en/InnoDB_news-4.1.3.html " Important: Starting from MySQL 4.1.3, InnoDB uses the same character set comparison functions as MySQL for non-latin1_swedish_ci character strings that are not BINARY. This changes the sorting order of space and characters < ASCII(32) in those character sets. For latin1_swedish_ci character strings and BINARY strings, InnoDB uses its own pad-spaces-at-end comparison method, which stays unchanged. If you have an InnoDB table created with MySQL 4.1.2 or earlier, with an index on a non-latin1 character set (in the case of 4.1.0 and 4.1.1 with any character set) CHAR/VARCHAR/or TEXT column that is not BINARY but may contain characters < ASCII(32), then you should do ALTER TABLE or OPTIMIZE table on it to regenerate the index, after upgrading to MySQL 4.1.3 or later. " Some examples that help you in determining if a table rebuild may be needed: * If you have only used the MySQL default charset (latin1) in 3.23 or 4.0, then you do not need to rebuild any InnoDB tables. * If you have only stored normal, printable characters (whose code is >= 32) in your CHAR or VARCHAR columns, then you do not need to rebuild any InnoDB tables. * If you have used the latin1_german charset and stored 'abc' and 'abc<newline>' in an indexed CHAR column, then you NEED to rebuild that InnoDB table. * If you have created InnoDB tables with any charset in 4.1.0 or 4.1.1, you may need to rebuild them. * You can use CHECK TABLE to determine if a table rebuild is needed. --- For MyISAM users, also the users of the default latin1 charset may need to rebuild their tables: http://dev.mysql.com/doc/mysql/en/News-4.1.2.html " Warning: Incompatible change! String comparison now works according to the SQL standard. Because we have that 'a' = 'a ' then from it must follow that 'a' > 'a\t'. (The latter was not the case before MySQL 4.1.2.) To implement it, we had to change how storage engines compare strings internally. As a side effect, if you have a table where a CHAR or VARCHAR column in some row has a value with the last character less than ASCII(32), you will have to repair this table. CHECK TABLES will tell you if this problem exists. (Bug #3152) " Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
