mysql - 网站迁移，phpmyadmin 导出/导入语法错误#1064

Question

我正在迁移 wordpress 网站。使用 phpmyadmin 从旧服务器导入 SQLdump 返回语法错误。

导出自:

phpMyAdmin SQL Dump
version 4.2.8.1
Serverversie: 5.5.37
PHP-versie: 5.3.29

导入:

phpMyAdmin
version 4.5.0.2
Serverversie: 5.5.44-MariaDB
PHP-versie: 5.5.30

语法错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'about', 'about', '{"title":"about","alias":"about","shortcode":"[rev_slider abou' at line 2

SQL 查询。注意，前 13 个表导入没有问题

INSERT INTO `d8up675e4c_revslider_sliders` (`id`, `title`, `alias`, `params`) VALUES
(2, 'Full-Screen-1', 'Full-Screen-1', '{"title":"Full-Screen-1","alias":"Full-Screen-1","shortcode":"[rev_slider Full-Screen-1]","source_type":"gallery","post_types":"post","post_category":"category_41","post_sortby":"ID","posts_sort_direction":"DESC","max_slider_posts":"30","excerpt_limit":"55","slider_template_id":"","posts_list":"","slider_type":"fullscreen","fullscreen_offset_container":"","fullscreen_min_height":"","full_screen_align_force":"off","auto_height":"on","force_full_width":"on","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","width":"1160","height":"1000","delay":"5000","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":"hide","padding":0,"background_color":"#000000","background_dotted_overlay":"none","show_background_image":"false","background_image":"http:\\/\\/domain.com\\/wp-content\\/","bg_fit":"normal","bg_repeat":"repeat","bg_position":"center top","touchenabled":"on","stop_on_hover":"off","navigaion_type":"bullet","navigation_arrows":"solo","navigation_style":"round","navigaion_always_on":"false","hide_thumbs":200,"navigaion_align_hor":"center","navigaion_align_vert":"bottom","navigaion_offset_hor":"0","navigaion_offset_vert":20,"leftarrow_align_hor":"left","leftarrow_align_vert":"center","leftarrow_offset_hor":20,"leftarrow_offset_vert":0,"rightarrow_align_hor":"right","rightarrow_align_vert":"center","rightarrow_offset_hor":20,"rightarrow_offset_vert":0,"thumb_width":100,"thumb_height":50,"thumb_amount":5,"hide_slider_under":0,"hide_defined_layers_under":0,"hide_all_layers_under":0,"hide_thumbs_under_resolution":0,"start_with_slide":"1","first_transition_type":"fade","first_transition_duration":300,"first_transition_slot_amount":7,"reset_transitions":"","reset_transition_duration":0,"0":"Execute settings on all slides","jquery_noconflict":"on","js_to_body":"false","output_type":"none","template":"false"}'),

测试了以下内容。

测试 1。尝试了其他数据库转储(来自旧服务器)，但它们也返回语法错误。

测试 2。命令行 mysqldump 旧服务器，命令行 mysql 导入新服务器。这工作没有错误。但是当我尝试使用 phpmyadmin(新服务器)'export/clear db/import' 时，我得到了同样的错误？

测试 3。清除 params 值:

INSERT INTO `d8up675e4c_revslider_sliders` (`id`, `title`, `alias`, `params`) VALUES
(2, 'Full-Screen-1', 'Full-Screen-1', '{}'),
(3, 'about', 'about', '{}'),
(4, 'our-story', 'our-story', '{}'),
(5, 'Screens', 'screens', '{}');

现在所有 CREATE TABLE 和 INSERT INTO 都已完成且没有错误。但是我收到以下语法错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALTER TABLE `d8up675e4c_commentmeta`
  ADD PRIMARY KEY (`meta_id`),
  ADD KEY `c' at line 133

SQL查询

INSERT INTO `d8up675e4c_wpsl_stores` (`wpsl_id`, `store`, `address`, `address2`, `city`, `state`, `zip`, `country`, `country_iso`, `lat`, `lng`, `description`, `phone`, `fax`, `url`, `email`, `hours`, `thumb_id`, `active`) VALUES
(1, 'Shoes shop | Office', 'street 181', '', 'demo city', 'Noord-Brabant', '1111 TM', 'Nederland', 'NL', 51.675339, 4.995821, '', '', '', 'www.domain.com', 'info@domain.com', '', 3065, 1),
(5, 'April Fashion & Shoes', 'street 1', '', 'VALKENS[...]

错误 ALTER TABLE

ALTER TABLE `d8up675e4c_commentmeta`
  ADD PRIMARY KEY (`meta_id`),
  ADD KEY `comment_id` (`comment_id`),
  ADD KEY `meta_key` (`meta_key`(191));

要特别清楚，这些是我使用 phpmyadmin(在新服务器上)“导出/清除数据库/导入”时的语法错误....我使用命令行从旧服务器导入 SQL 文件。

在旧服务器上尝试了许多不同的 phpmyadmin 导出设置，但每次我在新服务器上都遇到相同的错误。

当我导出/导入在该服务器上创建的网站时，它可以正常工作。这与旧服务器相同..

不知道要测试什么，我找不到解决方案..

显然这与不同的 MySQl 或 phpmyadmin 版本有关。如何解决？

问候，比约恩

Answer 1

发现问题...

经过几个小时的测试后，我发现 MariaDB 不接受以下值:

\\''

我只在少数数据库转储中发现了这一点，每个转储中大约​​出现 10 次，主要在 url 的..旧服务器上的一些插件已经使用 \\'' 保存了它们的设置。

导入失败并出现语法错误的示例

(618, 'wpdm_login_msg', '<a href=\\''http://domain.com/wp-login.php\\'' >Please login to download</a>', 'yes'),

注意 url 中的 \\''，将它们替换为 "可修复所有导入问题。