WordPress.org

Ready to get started?Download WordPress

Forums

mysql database export, and the accursed  (9 posts)

  1. NDP
    Member
    Posted 5 years 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.wordpress.org/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?

  2. 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

  3. NDP
    Member
    Posted 5 years 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?

  4. NDP
    Member
    Posted 5 years 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.

  5. 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.

  6. NDP
    Member
    Posted 5 years 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?

  7. 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.

  8. gm22
    Member
    Posted 4 years 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...

  9. juanmaguerrero
    Member
    Posted 4 years ago #

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

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.