Error importing Database

  • igloobob

    (@igloobob)


    9 years, 6 months ago

    Hi I’m doing a manual migration and when I ty and import my exported SQL file into my new database I get the following error..

    Error
SQL query:

CREATE TABLE IF NOT EXISTS <code>wplmlet_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 utf8mb4_unicode_ci DEFAULT NULL,
  <code>meta_value</code> longtext COLLATE utf8mb4_unicode_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=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1
MySQL said: Documentation

#1273 - Unknown collation: 'utf8mb4_unicode_ci'

    Does this mean anything to anyone and can anyone please help?

    I’m importing it into a PLESK Linux environment if that matters…

    The dev site it has come from is working fine.

    Thanks!

Viewing 11 replies - 1 through 11 (of 11 total)
  • ThemeSumo

    (@themesumo)

    9 years, 6 months ago

    Although I’m no MySQL expert, have you tried this solution on StackOverflow, it should resolve the issue you’re having?

    Hope this helps.

    RemedyWP

    (@esicairos)

    9 years, 6 months ago

    I think what is going on here is that you maybe coming from a different mysql version and the new hosting service maybe running an older version of mysql is what I am suspecting. In my past experience I had to do a search and replace for utf8mb4_unicode_ci and you can try to replace it with utf8mb4_general_ci. That has worked for me before I would try that. Let me know if this helps, I hope it does.

    Thread Starter igloobob

    (@igloobob)

    9 years, 6 months ago

    thanks for the suggestions, you both seem to be mentioning the same thing so looks right. Will give it a whirl later and report back

    Thread Starter igloobob

    (@igloobob)

    9 years, 6 months ago

    Hi, so I have tried the search and replace method and checked the collation matched before import. Gets further than last time then I get this error:

    Error
SQL query:

CREATE TABLE IF NOT EXISTS <code>wplmlet_ph_realtimefeed_logs_error</code> (
  <code>id</code> bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  <code>portal_id</code> bigint(20) unsigned NOT NULL,
  <code>post_id</code> bigint(20) unsigned NOT NULL,
  <code>severity</code> tinyint(1) unsigned NOT NULL,
  <code>message</code> varchar(255) COLLATE utf8_unicode_520_ci NOT NULL,
  <code>request</code> text COLLATE utf8_unicode_520_ci,
  <code>response</code> text COLLATE utf8_unicode_520_ci,
  <code>error_date</code> datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (<code>id</code>)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_520_ci AUTO_INCREMENT=158
MySQL said: Documentation

#1273 - Unknown collation: 'utf8_unicode_520_ci'

    Arghhh!!!! Any ideas?

    Thanks!

    Thread Starter igloobob

    (@igloobob)

    9 years, 6 months ago

    should I just go throuhg the DB in text edit and find any instance of utf8_unicode_520_ci and any variation and just change them all to utf8_general_ci do you think?

    RemedyWP

    (@esicairos)

    9 years, 6 months ago

    Yup that’s exactly why you want to do

    Thread Starter igloobob

    (@igloobob)

    9 years, 6 months ago

    tried my idea above and got a bit further then other errors…

    Error
Static analysis:

54 errors were found during analysis.

Unexpected character. (near "{" at position 1447)
Unexpected character. (near ":" at position 1455)
Unexpected character. (near ":" at position 1486)
Unexpected character. (near ":" at position 1519)
Unexpected character. (near ":" at position 1567)
Unexpected character. (near ":" at position 1590)
Unexpected character. (near ":" at position 1613)
Unexpected character. (near ":" at position 1640)
Unexpected character. (near ":" at position 1668)
Unexpected character. (near ":" at position 1694)
Unexpected character. (near ":" at position 1715)
Unexpected character. (near ":" at position 1741)
Unexpected character. (near ":" at position 1758)
Unexpected character. (near ":" at position 1775)
Unexpected character. (near ":" at position 1817)
Unexpected character. (near ":" at position 1844)
Unexpected character. (near ":" at position 1873)
Unexpected character. (near ":" at position 1893)
Unexpected character. (near ":" at position 1918)
Unexpected character. (near ":" at position 1932)
Unexpected character. (near ":" at position 1947)
Unexpected character. (near ":" at position 1971)
Unexpected character. (near ":" at position 1996)
Unexpected character. (near ":" at position 2020)
Unexpected character. (near ":" at position 2045)
Unexpected character. (near ":" at position 2069)
Unexpected character. (near ":" at position 2094)
Unexpected character. (near ":" at position 2118)
Unexpected character. (near ":" at position 2141)
Unexpected character. (near ":" at position 2163)
Unexpected character. (near ":" at position 2186)
Unexpected character. (near ":" at position 2208)
Unexpected character. (near ":" at position 2231)
Unexpected character. (near ":" at position 2243)
Unexpected character. (near ":" at position 2260)
Unexpected character. (near ":" at position 2278)
Unexpected character. (near ":" at position 2295)
Unexpected character. (near ":" at position 2315)
Unexpected character. (near ":" at position 2340)
Unexpected character. (near ":" at position 2358)
Unexpected character. (near ":" at position 2378)
Unexpected character. (near ":" at position 2400)
Unexpected character. (near "[" at position 2401)
Unexpected character. (near "]" at position 2550)
Unexpected character. (near ":" at position 2562)
Unexpected character. (near ":" at position 2584)
Unexpected character. (near ":" at position 2602)
Unexpected character. (near ":" at position 2618)
Unexpected character. (near ":" at position 2635)
Unexpected character. (near ":" at position 2651)
Unexpected character. (near ":" at position 2671)
Unexpected character. (near ":" at position 2687)
Unexpected character. (near ":" at position 2708)
Ending quote " was expected. (near "" at position 2977)
SQL query:

INSERT INTO <code>wplmlet_revslider_sliders</code> (<code>id</code>, <code>title</code>, <code>alias</code>, <code>params</code>) VALUES (2, 'featured properties', 'featuredproperties', '{"title":"featured properties","alias":"featuredproperties","shortcode":"[rev_slider featuredproperties]","source_type":"posts","post_types":"property","post_category":"featured_71","post_sortby":"ID","posts_sort_direction":"DESC","max_slider_posts":"8","excerpt_limit":"55","slider_template_id":"5","posts_list":"","slider_type":"fullwidth","fullscreen_offset_container":"","fullscreen_min_height":"","full_screen_align_force":"off","auto_height":"off","force_full_width":"off","width":"635","height":"330","responsitive_w1":"940","responsitive_sw1":"770","responsitive_w2":"780","responsitive_sw2":"500","responsitive_w3":"510","responsitive_sw3":"310","responsitive_w4":"0","responsitive_sw4":"0","responsitive_w5":"0","responsitive_sw5":"0","responsitive_w6":"0","responsitive_sw6":"0","delay":"9000","shuffle":"off","lazy_load":"off","use_wpml":"off","stop_slider":"off","stop_after_loops":0,"stop_at_slide":2,"load_googlefont":"false","google_font":["<link href=\\\\''http:\\/\\/fonts.googleapis.com\\/css?family=PT+Sans+Narrow:400,700\\\\'' rel=\\\\''stylesheet\\\\'' type=\\\\''text\\/css\\\\''>"],"position":"center","margin_top":0,"margin_bottom":0,"margin_left":0,"margin_right":0,"shadow_type":"2","show_timerbar":"top","padding":0,"background_color":"(5, 'Main Banner Template', 'mainbannertemplate', '{"title":"Main Banner Template","alias":"mainbannertemplate","shortcode":"[rev_slider mainbannertemplate]","source_type":"gallery","post_types":"post","post_category":"category_1","post_sortby":"ID","posts_sort_direction":"DESC","max_slider_posts":"30","excerpt_limit":"55","slider_template_id":"5","posts_list":"","slider_type":"fullwidth","fullscreen_offset_container":"","fullscreen_min_height":"","full_screen_align_force":"off","auto_height":"off","force_full_width":"off","width":"960","height":"500","responsitive_w1":"940","responsitive_sw1":"770","responsitive_w2":"780","responsitive_sw2":"500","responsitive_w3":"510","responsitive_sw3":"310","responsitive_w4":"0","responsitive_sw4":"0","responsitive_w5":"0","responsitive_sw5":"0","responsitive_w6":"0","responsitive_sw6":"0","delay":"6000","shuffle":"off","lazy_load":"off","use_wpml":"off","stop_slider":"off","stop_after_loops":0,"stop_at_slide":2,"load_googlefont":"false","google_font":["<link href=\\\\''http:\\/\\/fonts.googleapis.com\\/css?family=PT+Sans+Narrow:400,700\\\\'' rel=\\\\''stylesheet\\\\'' type=\\\\''text\\/css\\\\''>"],"position":"center","margin_top":0,"margin_bottom":0,"margin_left":0,"margin_right":0,"shadow_type":"0","show_timerbar":"top","padding":0,"background_color":" CREATE TABLE IF NOT EXISTS <code>wplmlet_revslider_slides</code> ( <code>id</code> int(9) NOT NULL AUTO_INCREMENT, <code>slider_id</code> int(9) NOT NULL, <code>slide_order</code> int(11) NOT NULL, <code>params</code> text NOT NULL, <code>layers</code> text NOT NULL, PRIMARY KEY (<code>id</code>) ) TYPE=InnoDB AUTO_INCREMENT=4

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Main Banner Template', 'mainbannertemplate', '{"title":"Main Banner Template","a' at line 2
    RemedyWP

    (@esicairos)

    9 years, 6 months ago

    I would have to look at the whole database in a text editor do you have a back up?

    RemedyWP

    (@esicairos)

    9 years, 6 months ago

    Only other thing I could think of would be go to those lines and see if there is a duplicate character or if that table is a table that’s generated by a plug in drop it for now and have the plugin regenerate it. I wouldn’t proceed without a back up

    Thread Starter igloobob

    (@igloobob)

    9 years, 6 months ago

    Thanks for your reply. I do have a backup yes. I have no idea what i’m looking for though. Would you mind taking a look please?

    RemedyWP

    (@esicairos)

    9 years, 6 months ago

    Email me the copy at i[removed]

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

The topic ‘Error importing Database’ is closed to new replies.