On Mon, Jul 03, 2006 at 01:55:26PM +0400, Timur Izhbulatov wrote: > I'm following the instructions [1] to convert character set. Unfortunately I > get > warnings about truncated data for some rows in several columns. All the > truncated columns are text type. > > Assuming the `col' column is text type and actually contains correct utf8 data > but has wrong character set I use the following queries to fix it: > > ALTER TABLE table MODIFY col BLOB; > ALTER TABLE table MODIFY col TEXT CHARACTER SET utf8; > > After the second query I get warnings about truncated data for some rows and I > can actually see the rows truncated. I played a bit with different data types > (LONGBLOB/LONGTEXT) but wasn't successful. > > So my question is what can be the cause of the data loss and how to avoid it?
After some additional investigations I found the cause. It was some "exotic" non-ASCII characters like '–' (long dash). At the same time Russian letters don't cause any problems. Seems the problem arises because the table itself also has wrong default character set (latin1). Setting default character set to utf8 with ALTER TABLE doesn't solve the problem. Bug if I create a new table with utf8 as default character set convertion works fine. What's happening? Please see the testcase attached. My character set settings are: SHOW VARIABLES LIKE '%char%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/share/mysql/charsets/ | +--------------------------+----------------------------------+ Cheers, -- Timur Izhbulatov OILspace, 26 Leninskaya sloboda, bld. 2, 2nd floor, 115280 Moscow, Russia P:+7 495 105 7245 + ext.205 F:+7 495 105 7246 E:[EMAIL PROTECTED] Building Successful Supply Chains - One Solution At A Time. www.oilspace.com
-- MySQL dump 10.9 -- -- Host: localhost Database: tizhbulatov_aquarium -- ------------------------------------------------------ -- Server version 4.1.19 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `stories` -- DROP TABLE IF EXISTS `test_table`; CREATE TABLE `test_table` ( `overview` text ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ALTER TABLE `test_table` DEFAULT CHARACTER SET utf8; INSERT INTO `test_table` SET `overview` = '–'; SELECT * FROM `test_table`; ALTER TABLE `test_table` MODIFY `overview` BLOB; ALTER TABLE `test_table` MODIFY `overview` text CHARACTER SET utf8; SHOW WARNINGS; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */;
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
