mysql database export, and the accursed Â

  • ndp

    (@ndp)


    16 years, 9 months ago

    So;
    As some others have noticed, there are weird characters showing up on my site (https://edtechfuture.org) – the dreaded Â, ’, “, and ”, . . . so on and so forth.

    From day 1, my WordPress installation has been set to “UTF8” encoding.

    I did notice that half of the tables somehow were set to “latin” encoding, instead of “UTF8”. On my development system, I *did* do the database conversion documented here: http://codex.ww.wp.xz.cn/Converting_Database_Character_Sets – but that seemed to have ZERO effect.

    I also installed the “UTF8-Sanitize” plugin. No effect.

    I *MANUALLY* edited all the funky characters out of my development site.

    That worked.

    Until I exported my database, and re-imported it (as a test). Then, all the funky characters came back. (though the UTF8 settings on all the tables did remain).

    And yes, my wp-config.php file says:
    define(‘DB_CHARSET’, ‘utf8’);
    define(‘DB_COLLATE’, ‘utf8_unicode_ci’);

    The funky characters appear when I browse the tables in myphpadmin, and they appear when I select them in mysql, and they appear in the text-file output in the export – whether I export from myphpadmin, or mysqldump (with the default_character_set set to the default of utf8).

    So this “change” appears to happen, every time I export my database. Is this a mysql bug?

    One of the things that drives me absolutely batty, is that these characters don’t appear in ALL places. Just SOME.

    Also – my RegisterPlus plugin settings, and my TDOMF forms do not survive a mysql export/import. These settings must be manually re-entered. Is mysql export just broken?

    And if anyone understands this:
    http://www.infini-source.com/blog.php
    Let me know. I get the “content type” tag, but that’s for an html page, right? My php pages don’t have this. Doesn’t WordPress dynamically generate this html based on the settings?

Viewing 8 replies - 1 through 8 (of 8 total)
  • Mark Ratledge

    (@songdogtech)

    16 years, 9 months ago

    Have you recently upgraded WP? Did you remove <?php wp_head(); ?> from header.php which places the correct UTF8 calls in the header and in metadata?

    If not, maybe this will help: Fixing mixed encoded mysql dumpfiles

    Thread Starter ndp

    (@ndp)

    16 years, 9 months ago

    With a little more work, I managed to isolate PART of the problem.

    With the assumptions of my first post;

    I have a page with a Post and Comments.
    This is that page on our production site – I have manually cleaned all the funky characters:
    https://edtechfuture.org/?page_id=56

    The same page on my development server, I cleaned as well.
    When I exported and imported ONLY the wp_comments and wp_posts tables, the funky characters re-appeared – in both the comments, and the post.

    Then I manually cleaned the funky characters from that page – again.

    Then I deactivated the “UTF8-Sanitize” plugin.

    And I did another export/import of just the wp_comments and wp_posts tables.
    THIS time, the comments remained “clean” – but the Post picked up the  characters at the end of sentences.

    I cleaned them manually, again.
    And I again, performed the export/import (fresh export) from wp_comments and wp_posts – and again, the  characters appeared in my post. (but the “, ‘, and – characters in my comments all stayed clean).

    When I check the wp_posts.sql file, I check the file (after the import) in Notepad++, and it’s viewing the file as “ANSI as UTF-8”. I can’t see the  characters – they’re displayed as a “space” in this encoding. But when I look in Hex Edit mode, these characters consist of two-bytes (all the other characters are SINGLE-BYTE), and the value is 0xc2a0. The subsequent ACTUAL space is 0x20.

    So, I go into phpMyAdmin, and check that post’s record in wp_posts, and the  character is clearly visible in post_content.

    Then I clean up the post in WordPress’s editor – check the record again in phpMyAdmin, the Â’s are gone, as you’d expect. Okey dokey?

    This time, I export the table with mysqldump. I re-import it using the same import method I’ve always used (mysql. . . source dumpfile.sql;) . . . and this time, no weird characters are in my post.

    By process of elimination – this appears to be a bug in phpMyAdmin. . . ?
    (MySQL version 5.0.22, phpMyAdmin version 2.10.1, php version 5.2.2)

    When I look at my environment info via GD Press Tools, under the “PHP Info” tab, in the ICONV section, all the ICONV settings are set to ISO-8859-1. . . . is that a problem?

    Thread Starter ndp

    (@ndp)

    16 years, 9 months ago

    Which header.php file?

    There are two, in my theme (modified version of Generic Designer 1.0)

    There’s themes/mytheme/header.php, and themes/mytheme/templates/header.php.

    neither of those have a <?php wp_head(); ?>.
    Nor do the original Generic-Plus theme files.

    I see that statement in my theme’s 404.php file. . . ?
    it’s also in theme/mytheme/templates/start.php;
    theme/mytheme/header.php has a
    <?php include(‘templates/start.php’); ?>

    So I have to conclude that wp_head() is getting called.

    Mark Ratledge

    (@songdogtech)

    16 years, 9 months ago

    You need <?php wp_head(); ?> – which is a standard function call for all WP themes – to make sure doc types, metadata and plugins are fully loaded. Add the wp_head call in header.php of a theme and then check page source and see what is duplicated. And see if the funky characters are gone. wp_head call goes above </head>

    Also, check language settings in the config file and the settings of phpmyadmin and see if that is introducing the funky characters.

    Thread Starter ndp

    (@ndp)

    16 years, 9 months ago

    Okay – so wp_head() is getting called in my start.php file, language settings in my config file are:
    define(‘DB_CHARSET’, ‘utf8’);
    define(‘DB_COLLATE’, ‘utf8_unicode_ci’);

    and phpMyAdmin is set to English.

    Do you think it could be my “iconv” settings?

    Mark Ratledge

    (@songdogtech)

    16 years, 9 months ago

    The iconv settings seem like the last thing to try.

    Did you upgrade from a very old version of WP? That’s where the bad characters come from, according to the link I added above. You might still need to sanitize the DB with that method.

    Mystery.

    gm22

    (@gm22)

    16 years, 2 months ago

    I had the same problem. I solved it by commenting out the encoding line in wp-config file:

    I changed

    define(‘DB_CHARSET’, ‘utf8′);

    to

    // define(‘DB_CHARSET’, ‘utf8′);

    the funny characters have disappeared…

    juanmaguerrero

    (@juanmaguerrero)

    15 years, 10 months ago

    @gm22 I had the same problem and it solved it!!! Works as magic!!! Thanks a lot!!!

Viewing 8 replies - 1 through 8 (of 8 total)

The topic ‘mysql database export, and the accursed Â’ is closed to new replies.