Support » Fixing WordPress » Importing SQL database Error related to "table structure" for wp_commentmeta

  • Resolved CWCbruce

    (@cwcbruce)


    I am moving a website I built to a different server. Before migrating, I backed up the site and I downloaded the SQL database. I did a Find and Replace using Brackets to reroute the urls. Using MyPHPAdmin on the host server I created a new database, and I am attempting to import the modified SQL. However, I get an error message:

    Database ‘database name’

    Table Structure for table ‘wp-commentmeta’

    CREATE TABLE IF NOT EXISTS ‘wp_commentmeta’ (
    ‘meta_id’ BIGINT(20) UNSIGNED NOTNULL.
    ‘commment_id’BIGINT (20) UNSIGNED NOT NULL DEFAULT ‘0’
    ‘meta_key’ VARCHAR (255)COLLATEutf8mb4_unicode-ci DEFAULT NULL
    ‘meta_value’ LONGTEXT COLLATE utf8mb4_unicode_ci
    ) ENGINE=INNDOB DEFAULT CHARSET utfmb4 COLLATE=utf8mb4_unicode_ci;

    MySQL said: ?
    #1273 Unknown collation: ‘utf8mb4_unicode_ci’

    I am not coder, and I do not know what this means. Can anyone help me out? Thank you!

Viewing 5 replies - 1 through 5 (of 5 total)
  • When you create the new, empty database you need to make sure the collation set matches the collation of the old database. In PHPMyAdmin check the collation of the old database under Operations->Collation.

    In your case, the new database seems to need to be set to utf8mb4_unicode_ci which can be done when you initially create the database. Once set, try importing again.

    Thanks for your help. I played with the collation options, but I still can’t get it to import properly. I called the hosting company and they said the platform I was working on was too old. The new myadminphp reads the database just fine, so I am just going to going with that. I appreciate your input.

    Moderator Samuel Wood (Otto)

    (@otto42)

    WordPress.org Admin

    Old versions of MySQL do not know about utf8mb4. My advice would be to switch to get your host to upgrade the MySQL version, or switch to an alternate hosting service.

    I had the same issue when uploading database to server.

    Error
    SQL query:
    
    #
    # Table structure of table <code>wp_commentmeta</code>
    #
    CREATE TABLE  <code>wp_commentmeta</code> (
    
     <code>meta_id</code> BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
     <code>comment_id</code> BIGINT( 20 ) UNSIGNED NOT NULL DEFAULT  '0',
     <code>meta_key</code> VARCHAR( 255 ) COLLATE utf8_unicode_520_ci DEFAULT NULL ,
     <code>meta_value</code> LONGTEXT COLLATE utf8_unicode_520_ci,
    PRIMARY KEY (  <code>meta_id</code> ) ,
    KEY  <code>comment_id</code> (  <code>comment_id</code> ) ,
    KEY  <code>meta_key</code> (  <code>meta_key</code> ( 191 ) )
    ) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_520_ci;

    ————–

    I Just replace all utf8_unicode_520_ci with utf8_unicode_ci in database. Then im able to upload the database. This works for me.
    You can try replace utf8mb4_unicode_ci with utf8_unicode_ci

    • This reply was modified 3 years, 6 months ago by fbimalayil.

    Hello, I have the same problem but am VERY new to php and sql databases. I can follow instructions though.

    I am getting the same error when I try to import my database utf8mb4 to utf8.

    I know this is a “resolved” topic but I dont know how to make the changes that are recommend by fbimalayil above.

    My error is below.


    Error

    SQL query:


    — Database: clearly1_wp638

    — ——————————————————–

    — Table structure for table wpgv_commentmeta

    CREATE TABLE IF NOT EXISTS wpgv_commentmeta (
    meta_id bigint( 20 ) unsigned NOT NULL AUTO_INCREMENT ,
    comment_id bigint( 20 ) unsigned NOT NULL DEFAULT ‘0’,
    meta_key varchar( 255 ) COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
    meta_value longtext COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY ( meta_id ) ,
    KEY comment_id ( comment_id ) ,
    KEY meta_key ( meta_key ( 191 ) )
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci AUTO_INCREMENT =1;

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Importing SQL database Error related to "table structure" for wp_commentmeta’ is closed to new replies.