Error 1273 exporting wordpress db and importing elsewhere

CMS & WordPress
1

Hi,

I have developed a wordpress site, and need to export the db and then import that db at another host, all would be good, but I think my side is older than their side and when I try to import the db I get the following error -

Error

SQL query:

-- --------------------------------------------------------
--
-- Table structure for table `wp_nf3_actions`
--
CREATE TABLE IF NOT EXISTS `wp_nf3_actions` (
`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`title` longtext COLLATE utf8mb4_unicode_520_ci,
`key` longtext COLLATE utf8mb4_unicode_520_ci,
`type` longtext COLLATE utf8mb4_unicode_520_ci,
`active` tinyint( 1 ) DEFAULT '1',
`parent_id` int( 11 ) NOT NULL ,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`updated_at` datetime DEFAULT NULL ,
UNIQUE KEY `id` ( `id` )
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci AUTO_INCREMENT =21;

MySQL said: Documentation
#1273 - Unknown collation: 'utf8mb4_unicode_520_ci'

I looked into it and found the following fix, but I dont know how to do it

Error 1273 means that your database is using a collation, which the MySQL server does not understand. This can happen, when you use a database, which has been created with a newer version of MySQL, with an older MySQL server version. This problem can be solved by converting the wrong collations from utf8mb4_unicode_ci to utf8_general_ci. There is a script on stackoverflow, which does exactly that.

Can anyone help with this

2

I think it’s saying your new host is using an older version than your old one. I find that rather disturbing. Can you establish which version of MySQL (I’m assuming it IS MySQL) your new host is using?

3

Oh I see, I just assumed it was the other way around, I’ll ask, the client has gone with a local host, much against my opinion of just going with one of the big boys, far easier and much cheaper

4

Ye your right, the one Im on is -

Server version: 5.6.35-log - MySQL Community Server (GPL)

and the one its going to is -

Server version: 5.5.13

mmm

5

Indeed. The latest release is 5.7.17

6

Umm, well its all set up now, and as I’m only the web developer its their choice I suppose, the client decided to go with these guys, as i expect its a friend of a friend sort of thing.

So am I not going to be able to export this db, so that the older one can accept it?

Thanks as always Gandalf458

1 Like
7

Have you looked on SO for the script mentioned? I have no idea what’s involved there, I’m afraid.

8

I did a quick search and as best as I can tell there are two options.

  • edit the sql file to remove the “mb4” portion of the charset and collation lines
  • create a new sql file using “compatibility” option

One problem might be if there is any content that needs to be multi-byte and is imported to non-multi-byte

9

I thin this is the thread they are talking about -

I did this point which didnt work -
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.

and I also cleared my coockies, which didnt do anything, the other bits are a bit over my head

10

Actually I did the NONE to MYSQL40 change in the drop down, and the error did change to below.

CREATE TABLE IF NOT EXISTS `wp_commentmeta` (
`meta_id` bigint( 20 ) unsigned NOT NULL AUTO_INCREMENT ,
`comment_id` bigint( 20 ) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar( 255 ) DEFAULT NULL ,
`meta_value` longtext,
PRIMARY KEY ( `meta_id` ) ,
KEY `comment_id` ( `comment_id` ) ,
KEY `meta_key` ( `meta_key` ( 191 ) )
) TYPE = InnoDB AUTO_INCREMENT =1;

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 'TYPE=InnoDB AUTO_INCREMENT=1' at line 19

But wasnt sure if it was progress, so didnt take it further

11

I would have expected

) TYPE = InnoDB AUTO_INCREMENT =1;

to look more like

) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci AUTO_INCREMENT =21;

but without the "mb4"s

The trouble with “solutions” found at SO and elsewhere on the web is that depending on the particulars what works for one may not work for others.

Going by the AUTO_INCREMENT =21 the table has only 21 rows. Maybe it would be easier to edit the original file than experimenting with the various solutions until you find the one you need?

12

Ye this is it, I have come across loads of this works do this answers and it doesnt fit my problem.

So can you just give me a old my hand type answer on this one Mittineague, I’m not very sure what to do.

Thanks though for helping

13

If it were me, my first choice would be to research what to use with the dump tool command line and create the sql file.
My second would be to open my text editor and use a “replace all” something like
first, Find

 utf8mb4_unicode_520_ci

Replace with

 utf8_unicode_ci

Then, Find

utf8mb4

Replace with

utf8

BUT, As tedious as it might be, before doing any replacing, I would use Find to look at all occurrences of the string “utf8mb4” to make sure they were only in the charset and collation lines.

15

Ok I have made some progress it seems, its at least further down the tables.

What i did was open the sql file up in Notepad, and did the find a nd replace on both those utf8 issues you pinted out, I imported the saved sql file, and then have a new error, but its more a number error it seems

SQL query:

-- -- Dumping data for table `wp_nf3_action_meta` -- INSERT INTO `wp_nf3_action_meta` (`id`, `parent_id`, `key`, `value`) VALUES (83, 5, 'objectType', 'Action'), (84, 5, 'objectDomain', 'actions'), (85, 5, 'editActive', ''), (86, 5, 'label', 'Success Message'), (87, 5, 'message', 'Your form has been successfully submitted.'), (88, 5, 'order', '1'), (89, 5, 'payment_gateways', ''), (90, 5, 'payment_total', ''), (91, 5, 'tag', ''), (92, 5, 'to', ''), (93, 5, 'reply_to', ''), (94, 5, 'email_subject', ''), (95, 5, 'email_message', ''), (96, 5, 'email_message_plain', ''), (97, 5, 'from_name', ''), (98, 5, 'from_address', ''), (99, 5, 'email_format', 'html'), (100, 5, 'cc', ''), (101, 5, 'bcc', ''), (102, 5, 'attach_csv', ''), (103, 5, 'redirect_url', ''), (104, 5, 'success_msg', '<p style="color:#09459D">Thank you for sending us your study enquiry form.</p><p style="color:#09459D">We should be in contact with you again to discuss your study within five working days. However, if your enqu[...]

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 'll get back to you to discuss it more within five working days. However, if your' at line 79
16

You could also try contacting the host and ask if they are likely to upgrade to a more recent version of MySQL…

17

I’ve sort of hinted that in an earlier email to them, and they havent got back to me yet, as you can imagine an absolute nuisance really, and as usual out of my knowledge zone in honesty.

18

Ok Im working my way through all these issues by looking at the error and opening it up in notepad and manually changing things, but have got one below which Im not sure about

SQL query:

-- -- Dumping data for table `wp_nf3_actions` -- INSERT INTO `wp_nf3_actions` (`id`, `title`, `key`, `type`, `active`, `parent_id`, `created_at`, `updated_at`) VALUES (5, NULL, NULL, 'successmessage', 1, 2, '2017-03-07 18:56:59', NULL), (6, NULL, NULL, 'email', 1, 2, '2017-03-07 18:56:59', NULL), (7, NULL, NULL, 'save', 1, 2, '2017-03-07 18:56:59', NULL), (8, NULL, NULL, 'email', 1, 2, '2017-03-08 10:12:51', NULL), (9, '', '', 'save', 1, 3, '2016-08-24 15:39:20', NULL), (10, '', '', 'email', 1, 3, '2016-08-24 15:39:20', NULL), (11, '', '', 'email', 1, 3, '2016-08-24 15:47:39', NULL), (12, '', '', 'successmessage', 1, 3, '2016-08-24 15:39:20', NULL), (13, NULL, NULL, 'successmessage', 1, 4, '2017-04-01 09:30:44', NULL), (14, NULL, NULL, 'email', 1, 4, '2017-04-01 09:30:45', NULL), (15, NULL, NULL, 'save', 1, 4, '2017-04-01 09:30:45', NULL), (16, NULL, NULL, 'email', 1, 4, '2017-04-03 10:45:05', NULL), (17, NULL, NULL, 'save', 1, 5, '2017-03-07 18:56:59', NULL), (18, NULL, NULL, 'emai[...]

MySQL said: Documentation

#1062 - Duplicate entry '5' for key 'id'

This maybe more clear and is it all I think

    Dumping data for table `wp_nf3_actions`
--

INSERT INTO `wp_nf3_actions` (`id`, `title`, `key`, `type`, `active`, `parent_id`, `created_at`, `updated_at`) VALUES
(5, NULL, NULL, 'successmessage', 1, 2, '2017-03-07 18:56:59', NULL),
(6, NULL, NULL, 'email', 1, 2, '2017-03-07 18:56:59', NULL),
(7, NULL, NULL, 'save', 1, 2, '2017-03-07 18:56:59', NULL),
(8, NULL, NULL, 'email', 1, 2, '2017-03-08 10:12:51', NULL),
(9, '', '', 'save', 1, 3, '2016-08-24 15:39:20', NULL),
(10, '', '', 'email', 1, 3, '2016-08-24 15:39:20', NULL),
(11, '', '', 'email', 1, 3, '2016-08-24 15:47:39', NULL),
(12, '', '', 'successmessage', 1, 3, '2016-08-24 15:39:20', NULL),
(13, NULL, NULL, 'successmessage', 1, 4, '2017-04-01 09:30:44', NULL),
(14, NULL, NULL, 'email', 1, 4, '2017-04-01 09:30:45', NULL),
(15, NULL, NULL, 'save', 1, 4, '2017-04-01 09:30:45', NULL),
(16, NULL, NULL, 'email', 1, 4, '2017-04-03 10:45:05', NULL),
(17, NULL, NULL, 'save', 1, 5, '2017-03-07 18:56:59', NULL),
(18, NULL, NULL, 'email', 1, 5, '2017-03-08 10:12:51', NULL),
(19, NULL, NULL, 'email', 1, 5, '2017-03-07 18:56:59', NULL),
(20, NULL, NULL, 'successmessage', 1, 5, '2017-03-07 18:56:59', NULL);

I though it was the ones with ‘’ instead of NULL so cleared all those up, but it wasnt that, and dont understand a duplicate entry when all the id’s are different

19

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.