3

I was importing my website' database on to another server but it is showing this error which I have no idea about:

I tried importing the database multiple times but failed. I also checked 'Add Drop Table' field that comes when exporting the database file but it didn't do anything.

  • It is not unusual to see this collation this seems to be the best as WordPess is concerned. – prosti Jan 6 '17 at 14:48
6

This issue is as a result of your server not supporting the utf8mb4_unicode_520_ci collation type.

To resolve this you should convert the collation for all tables with utf8mb4_unicode_520_ci to utf8_general_ci

If you're exporting through phpmyadmin, you can:

  1. Click the "Export" tab for the database

  2. Click the "Custom" radio button

  3. Go the section titled "Format-specific options" and change the dropdown for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40.

  4. Scroll to the bottom and click "GO".

OR run the following query on each of the affected tables:

ALTER TABLE myTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

UPDATE: You should also replace in your sql exported file TYPE=MyISAM with ENGINE=MyISAM

  • It is still showing an error i.imgur.com/lYAYMiw.png – Rahul Dec 29 '16 at 12:41
  • You should replace in your sql exported file TYPE=MyISAM with ENGINE=MyISAM – Tunji Dec 29 '16 at 12:44
  • How do I replace it? Is this editable? – Rahul Dec 29 '16 at 12:45
  • Yes it is. you can do a search and replace using any good text editor. – Tunji Dec 29 '16 at 12:46
  • if so, dont forget to upvote his answer ;) – T.Todua Dec 29 '16 at 12:52
1

For some, the collation utf8mb4_unicode_520_ci looks strange but WordPress uses this collation when possible. Other collations are the second best.

Note this line:

// _unicode_520_ is a better collation, we should use that when it's available.

Some plugins will create utf8mb4_unicode_520_ci collation tables no matter what.

File: /var/www/html/test100.com/wp-includes/wp-db.php
761:    /**
762:     * Determines the best charset and collation to use given a charset and collation.
763:     *
764:     * For example, when able, utf8mb4 should be used instead of utf8.
765:     *
766:     * @since 4.6.0
767:     * @access public
768:     *
769:     * @param string $charset The character set to check.
770:     * @param string $collate The collation to check.
771:     * @return array The most appropriate character set and collation to use.
772:     */
773:    public function determine_charset( $charset, $collate ) {
774:        if ( ( $this->use_mysqli && ! ( $this->dbh instanceof mysqli ) ) || empty( $this->dbh ) ) {
775:            return compact( 'charset', 'collate' );
776:        }
777: 
778:        if ( 'utf8' === $charset && $this->has_cap( 'utf8mb4' ) ) {
779:            $charset = 'utf8mb4';
780:        }
781: 
782:        if ( 'utf8mb4' === $charset && ! $this->has_cap( 'utf8mb4' ) ) {
783:            $charset = 'utf8';
784:            $collate = str_replace( 'utf8mb4_', 'utf8_', $collate );
785:        }
786: 
787:        if ( 'utf8mb4' === $charset ) {
788:            // _general_ is outdated, so we can upgrade it to _unicode_, instead.
789:            if ( ! $collate || 'utf8_general_ci' === $collate ) {
790:                $collate = 'utf8mb4_unicode_ci';
791:            } else {
792:                $collate = str_replace( 'utf8_', 'utf8mb4_', $collate );
793:            }
794:        }
795: 
796:        // _unicode_520_ is a better collation, we should use that when it's available.
797:        if ( $this->has_cap( 'utf8mb4_520' ) && 'utf8mb4_unicode_ci' === $collate ) {
798:            $collate = 'utf8mb4_unicode_520_ci';
799:        }
800: 
801:        return compact( 'charset', 'collate' );
802:    }

The utf8mb4_unicode_520_ci (Unicode Collation Algorithm 5.2.0, October 2010) collation is an improvement over utf8mb4_unicode_ci (UCA 4.0.0, November 2003).

There is no word on when MySQL will support later UCAs.

The very latest UCA is 9.0.0 http://www.unicode.org/reports/tr10/, but MySQL doesn't support that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.