Page 1 of 4 123 ... LastLast
Results 1 to 10 of 36

Thread: mysql Charset Problem after Upgrade 11.1 - 11.2

  1. #1

    Default mysql Charset Problem after Upgrade 11.1 - 11.2

    Hi,

    i did an upgrade of opensyse 11.1 to 11.2. When i upgraded last times from 10.1 to 10.2 aso. i never had any Problems with my data.

    This time there is an hugh trouble of charset violations.
    This exactly means, dumped or upgraded data from mysql 5.0 aren't usable in mysql 5.1.36 and i still didn' find out how to convert them to get them run.

    The problem occours with some php Webapplications.
    The only thing i got fixed was to use the old data if i set manually character-set-result to latin1 or explicit tell the application to use utf8.
    I'm only able to solve the problem by setting manualy:
    global character_set_results=latin1;

    I fear, this is a general issue or any idea how i could get it permanent working?

  2. #2
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: mysql Charset Problem after Upgrade 11.1 - 11.2

    Quote Originally Posted by matzewe01 View Post
    I'm only able to solve the problem by setting manualy:
    global character_set_results=latin1;
    That's the solution until you are ready to migrate your data to utf-8.

  3. #3

    Default Re: mysql Charset Problem after Upgrade 11.1 - 11.2

    Very funny.

    Could you explain how to migrate the data from an old standard latin1 environment to utf8?

    I tried it several times to export the data via mysqldump
    for example:
    mysqldump --skip-set-charset --default-character-set=UTF8

    replaced settings like:
    ENGINE=MyISAM DEFAULT CHARSET=latin1;
    by
    ENGINE=MyISAM DEFAULT CHARSET=utf8;

    The result of all.
    Maybe the old data is show correctly. But all new data don't work.
    Maybe you have an simple idea how to.
    The only point i got my old data / Applications running was:

    By this Configuration
    character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | utf8 |
    | character_set_filesystem | binary |
    | character_set_results | latin1 |
    | character_set_server | utf8 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    | collation_connection | utf8_unicode_ci |
    | collation_database | utf8_unicode_ci |
    | collation_server | utf8_unicode_ci
    and and standard export from following sourcedb 5.0

    character_set_client | latin1 |
    | character_set_connection | latin1 |
    | character_set_database | latin1 |
    | character_set_filesystem | binary |
    | character_set_results | latin1 |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    | collation_connection | latin1_swedish_ci |
    | collation_database | latin1_swedish_ci |
    | collation_server | latin1_swedish_ci
    The only way it works currently is:
    If i add Data with above uft8 configuration.
    For example by vBB with option default-character-set='latin1' character_set_results = latin1 it works but not with utf8.
    The Tables are all utf8 instead of latin1 before.

    thx

  4. #4
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: mysql Charset Problem after Upgrade 11.1 - 11.2

    No, quite serious.

    You have to convert all your string data from latin-1 to utf-8. Because there are byte sequences in latin-1 that are not legal utf-8 sequences. Otherwise you have to make the declaration of the charset match the data for the time being in all operations, including export and import.

    It could be that when you imported the data, you had utf-8 in effect so there were lots of "illegal chars". Normally the dump should contain a declaration of the charset for each table but maybe that was wrong or missed out.

    So many things could have gone wrong, it's hard to be very definite.

  5. #5

    Default Re: mysql Charset Problem after Upgrade 11.1 - 11.2

    Ok, here an small example of my Data:

    DROP TABLE IF EXISTS `vbadminhelp`;
    SET @saved_cs_client = @@character_set_client;
    SET character_set_client = utf8;
    CREATE TABLE `vbadminhelp` (
    `adminhelpid` int(10) unsigned NOT NULL auto_increment,
    `script` varchar(50) NOT NULL default '',
    `action` varchar(25) NOT NULL default '',
    `optionname` varchar(100) NOT NULL default '',
    `displayorder` smallint(5) unsigned NOT NULL default '1',
    `volatile` smallint(5) unsigned NOT NULL default '0',
    `product` varchar(25) NOT NULL default '',
    PRIMARY KEY (`adminhelpid`),
    UNIQUE KEY `phraseunique` (`script`,`action`,`optionname`)
    ) ENGINE=MyISAM AUTO_INCREMENT=27637 DEFAULT CHARSET=utf8;
    SET character_set_client = @saved_cs_client;
    And now the import:

    INSERT INTO `vbadminhelp` VALUES (27442,'usergroup','add,edit','canusecustomtitle',200,1,'vbulletin'),(27441,'usergroup','add,edit','canjoingroups',200,1,'vbulletin'),(27440,'usergroup','add,edit','canseeraters',190,1,'vbulletin'),(27439,'usergroup','add,edit','showeditedby',180,1,'vbulletin'),(27438,'usergroup','add,edit','caninvisible',170,1,'vbulletin'),(27437,'usergroup','add,edit','canmodifyprofile',160,1,'vbulletin')

    DROP TABLE IF EXISTS `vbword`;SET @saved_cs_client = @@character_set_client;SET character_set_client = utf8;CREATE TABLE `vbword` ( `wordid` int(10) unsigned NOT NULL auto_increment, `title` char(50) NOT NULL default '', PRIMARY KEY (`wordid`), UNIQUE KEY `title` (`title`)) ENGINE=MyISAM AUTO_INCREMENT=16229 DEFAULT CHARSET=utf8;SET character_set_client = @saved_cs_client;---- Dumping data for table `vbword`--LOCK TABLES `vbword` WRITE;/*!40000 ALTER TABLE `vbword` DISABLE KEYS */;INSERT INTO `vbword` VALUES (1,'aprilscherz'),(2,'heise'),(3,'thema'),(4,'prozessorkhlung'),(5,'bertragen'),(6,'wrmestrahlung'),(7,'elektromagnetische'),(8,'strahlung')
    [client]
    #password = your_password
    port = 3306
    socket = /var/run/mysql/mysql.sock
    default_character_set = utf8
    And this is, what phpMyAdmin tells me about the field title:
    char(50) utf8_general_ci

    At the old System it was:
    char(50) latin1_swedish_ci

    On both Systems: LC_CTYPE=de_DE.UTF-8

    I should be able to import this data by mysql client correctly or?

  6. #6
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: mysql Charset Problem after Upgrade 11.1 - 11.2

    There should be some comments in front of each table declaring the charset. Check those. Perhaps you should not try to force the charset while exporting and let mysqldump do its thing. You have to realise that forcing the charset doesn't do any byte conversions on the data. You may claim that the table is utf-8 but it will still contain latin-1 sequences.

  7. #7

    Default Re: mysql Charset Problem after Upgrade 11.1 - 11.2

    --
    -- Table structure for table `vbadminhelp`
    --

    DROP TABLE IF EXISTS `vbadminhelp`;
    SET @saved_cs_client = @@character_set_client;
    SET character_set_client = utf8;
    CREATE TABLE `vbadminhelp` (
    `adminhelpid` int(10) unsigned NOT NULL auto_increment,
    `script` varchar(50) NOT NULL default '',
    `action` varchar(25) NOT NULL default '',
    `optionname` varchar(100) NOT NULL default '',
    `displayorder` smallint(5) unsigned NOT NULL default '1',
    `volatile` smallint(5) unsigned NOT NULL default '0',
    `product` varchar(25) NOT NULL default '',
    PRIMARY KEY (`adminhelpid`),
    UNIQUE KEY `phraseunique` (`script`,`action`,`optionname`)
    ) ENGINE=MyISAM AUTO_INCREMENT=27637 DEFAULT CHARSET=utf8;
    SET character_set_client = @saved_cs_client;

    --
    -- Dumping data for table `vbadminhelp`
    --
    Thats all.

    i also tried ut8 encoding by perl.
    The result was an nightmare.

  8. #8
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: mysql Charset Problem after Upgrade 11.1 - 11.2

    You have a comment in front of the table claiming that the charset is utf-8, but in fact the chars in the strings has not undergone any conversion. So I think you should not specify any charset when dumping.

    As I explained, the charset attached to a table is only metadata. It doesn't ensure that the data is in that charset. You can put anything you like into a varchar or text type, it's just a sequence of octets. But if your application relies on the metadata to specify the charset of the HTML page, then it had better match the data.

  9. #9

    Default Re: mysql Charset Problem after Upgrade 11.1 - 11.2

    yk's stuff: MySQL: CHARSET from latin1 to utf8

    How about this example and voncerting the data / sqldump by iconv?

  10. #10

    Default Re: mysql Charset Problem after Upgrade 11.1 - 11.2

    ok, last but not least.
    If i install the application new. With all the utf8 Settings.
    The default data of it's application specially for works correctly.

    And as far as i know, for my apps better than or this example, we don't use characters which aren't utf8 konform.
    But the same Problem with all the apps.

    My own apps, just uses the defaults.

Page 1 of 4 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •