This is the technical support forum for WPML - the multilingual WordPress plugin.

Everyone can read, but only WPML clients can post here. WPML team is replying on the forum 6 days per week, 22 hours per day.

This thread is resolved. Here is a description of the problem and solution.

Problem: I'm trying to migrate a site from Polylang to WPML. After I clicked the Migrate button it began and then just stuck. Solution: The issue was caused by an Illegal mix of collations in the database tables. Before trying the migration, you need to set the collation of the tables to the same type.

Tagged: 

This topic contains 17 replies, has 2 voices.

Last updated by veraK 1 year, 5 months ago.

Assigned support staff: Vincenzo.

Author Posts
May 15, 2018 at 6:15 pm

veraK

I am trying to: migrate a test site from Polylang to WPML

Link to a page where the issue can be seen: I don't think you can see the issue here, but this is the site: hidden link

I expected to see: I have done everything as told in your post https://wpml.org/documentation/related-projects/migrate-polylang-wpml/
It was supposed to migrate.

Instead, I got: After I clicked Migrate it began and then just stuck for hours. I refreshed - there was nothing in the database. I restored the database from the backup and did it again. Twice, Same results.

May 17, 2018 at 11:26 am #2174731

Vincenzo
Supporter

Languages: English (English ) Italian (Italiano )

Timezone: Europe/Rome (GMT+02:00)

Hello, thank you for contacting WPML Support!

1. From what I can see, the WP Memory Limit needs to be increased. PHP memory limit is fine but WordPress uses 40Mb as default. Minimum requirements for WPML are 128Mb.

Add this in the middle of the wp-config.php file (after the php opening tag) to increase WP memory:

/** WP Memory Limit */
define( 'WP_MEMORY_LIMIT', '256M' );

The wp-config.php file is located in your WordPress root directory. It's the same file where the database configuration settings are. You will have to access it by FTP or SFTP in order to edit it.

You can check the current WP Memory Limit value from WPML -> Support in the Info section

More info here:
- https://codex.wordpress.org/Editing_wp-config.php#Increasing_memory_allocated_to_PHP
- https://wpml.org/home/minimum-requirements/

2. It is possible that there is some kind of conflict during the migration.

Can you please try disabling all the other plugins and set a default WordPress theme (eg. Twenty Sixteen) before testing the migration again?

3. If this does not fix the issue, it is possible that there is a PHP error.
Can you please temporarily enable the WordPress debug mode to find the error?

- Open the wp-config.php file and search for this line:

define('WP_DEBUG', false);

- Change it to:

define('WP_DEBUG', true);
define('WP_DEBUG_LOG', true);
define('WP_DEBUG_DISPLAY', false);

Now the errors will be logged in the debug.log file inside /wp-content/ folder.

The wp-config.php file is inside your WordPress root folder.

Try the migration again.
Now, please post the content of wp-content/debug.log file here in the forum or on pastebin.com if it is too big.

Related documentation:
- https://wpml.org/documentation/support/debugging-wpml/
- http://codex.wordpress.org/Debugging_in_WordPress
- https://wpml.org/faq/500-error/

Thank you

May 17, 2018 at 1:55 pm #2177042

veraK

Hello,
Thank you for your reply.
I have tried the first two instructions - no change.
Did as instructed in 3.
When I clicked on the tools/Migrate... it gave me this on top:

Notice: the method constructor in WP_Widget in Whistles_Widget doesn't exist since version 4.3.0! use -

instead. in /home/eichut2/public_html/wp-includes/functions.php on line 3902
error in wordpress database: [Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=']
SELECT term_taxonomy_id FROM wp_term_taxonomy tt LEFT JOIN wp_icl_translations i ON CONCAT('tax_', tt.taxonomy ) = i.element_type AND i.element_id = tt.term_taxonomy_id WHERE tt.taxonomy='nav_menu' AND i.language_code IS NULL

error in wordpress database: [Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=']
SELECT p.ID FROM wp_posts p LEFT JOIN wp_icl_translations i ON CONCAT('post_', p.post_type ) = i.element_type AND i.element_id = p.ID WHERE p.post_type = 'nav_menu_item' AND i.language_code IS NULL

Strict Standards: Declaration of WOWSlider_List_Table::single_row() should be compatible with WP_List_Table::single_row($item) in /home/eichut2/public_html/wp-content/plugins/wowslider/list-table.php on line 239

What does that mean? Should I continue?
Thank you
Vera

May 17, 2018 at 2:16 pm #2177366

Vincenzo
Supporter

Languages: English (English ) Italian (Italiano )

Timezone: Europe/Rome (GMT+02:00)

Hello Vera,

thank you for the debug log details.

It seems like the main issue here is about this:

Illegal mix of collations 

Some database table or field has the utf8mb4_general_ci collation while other have the utf8mb4_unicode_ci

Before trying the migration, you may need to set the collation of the tables to the same type (eg. utf8mb4_unicode_ci)

You may try doing it with some query like this one (please edit the query before using it)

ALTER TABLE wp_icl_strings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Alternatively, you can use applications like phpMyAdmin to set the collation for tables and fields.

Please let me know if this solves the issue or if you need further details.

Thank you

May 17, 2018 at 4:03 pm #2179275

veraK

Thank you, Vincenzo.
I have gone to PHPMyAdmin and changed all to utf8mb4_unicode_ci - but nothing changed.
Could you help, please?

May 17, 2018 at 4:30 pm #2179790

Vincenzo
Supporter

Languages: English (English ) Italian (Italiano )

Timezone: Europe/Rome (GMT+02:00)

Hello,

are you still getting the same "Illegal mix of collations" errors?

1. If WPML is enabled, can you please try going to WPML -> Support -> Troubleshooting and pressing the "Fix element_type collation" button?

2. You can also try checking the fields inside the tables to see if their collation has been updated too.
See the attached screenshot for the location.

Thank you

May 18, 2018 at 4:42 pm #2190731

veraK

Well, I went again and gone through every line in both places: operations and structure, and changed everything to utf8mb4_general_ci.
Then I went and clicked again the Migrate tool.
This time it has migrated 36 out of 116 posts and got stuck.
There is not more error of the kind there was before with the collation problem.
Under the Migrate button it only shows

Migration started, please don't close this window...
Moving your language settings...

What now?

May 18, 2018 at 4:44 pm #2190732

veraK

Also, on the front page under each of these posts it now says:

Notice: Undefined index: plus_sharedcount_enabled in /home/eichut2/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 670

Notice: Undefined index: bar_sharedcount_enabled in /home/eichut2/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 672

May 18, 2018 at 4:53 pm #2190741

Vincenzo
Supporter

Languages: English (English ) Italian (Italiano )

Timezone: Europe/Rome (GMT+02:00)

Hello,

1. Please keep the browser console open during the migration (f12 key)
- Can you see any error in the console?

2. Can you please try the migration on a minimal install?
- For minimal install, I mean all the other plugins temporarily disabled and a default WordPress theme set (eg. Twenty SevenTeen)

3. The error seems related to the simple-share-buttons-adder plugin. It is possible that some of the options of the plugin or some custom field added by that plugin to the posts have not been translated.
- After the migration will be complete, re-enable that plugin and try updating one of those post and its translation to see if the error message disappears for that post

Thank you

May 19, 2018 at 3:32 pm #2194569

veraK

OK. So I went back and started all from the beginning.
Had the site cloned anew. There are a couple of problems, for some reason it isn't exactly the same as the live site.
Made the changes in the database so Collation is for all tables utf8mb4_general_ci.
Activated Twenty Seventeen theme.
Started the Migration.
How long should it take? It still shows the same on the page:

Migration started, please don't close this window...
Moving your language settings...

And it's been a couple of hours.
When I check Posts in another tab, it appears that all the posts are there, but they don't recognize their counterparts (language and translation).

Also, I don't understand this error at the bottom of the Dashboard screen, I don't know if it has anything to do with the problems, but perhaps you know and what to do about it:
Notice: ob_end_flush(): failed to send buffer of zlib output compression (0) in /home/eichut2/public_html/wp-includes/functions.php on line 3730

Thank you.

May 19, 2018 at 3:57 pm #2194572

Vincenzo
Supporter

Languages: English (English ) Italian (Italiano )

Timezone: Europe/Rome (GMT+02:00)

Hello,

for the error in the dashboard, please try opening the wp-config.php file and adding this line in the middle of that file.

ini_set('zlib.output_handler', '');

About the main issue, is there any error in the browser console (F12 key) or in the debug.log file?

Thank you

May 19, 2018 at 9:02 pm #2195531

veraK

No change.
And no other errors.
BTW, the F12 key opens the source code at the bottom of the screen, not any errors.

May 19, 2018 at 9:09 pm #2195667

veraK

I missed something. On the front page now it shows another error in a pugin:

Notice: Undefined variable: animate_effect in /home/eichut2/public_html/wp-content/plugins/easy-facebook-likebox/public/easy-facebook-likebox.php on line 402

All this is extremely strange, because this site is cloned yesterday from the live site, eichut.net. There is no problem there, with any of the plugins OR any errors.

May 20, 2018 at 9:30 am #2196475

veraK

Forgive me, I know it's not the main issue, but could you please help with this one, as well?
I have done some searching on this and understand it's rather common, but I don't understand PHP.
Here is something I got with people who do understand:
https://core.trac.wordpress.org/ticket/18525
hidden link
Could you, perhaps, make heads and tails out of it and help me remove the problem?
Thank you,
Vera

May 21, 2018 at 10:18 am #2200481

Vincenzo
Supporter

Languages: English (English ) Italian (Italiano )

Timezone: Europe/Rome (GMT+02:00)

Hello Vera,

1. When you press the F12 key, it opens the developer's tools. To check the console messages you may need to select the Console tab.
See the attached screenshot:

2. ob_end_flush(): failed to send buffer of zlib output compression is a common WordPress error, which pops up when you have zlib output compression enabled. Disable it and it should be solved. For more info on how to disable it, see this thread:
https://stackoverflow.com/questions/38693992/notice-ob-end-flush-failed-to-send-buffer-of-zlib-output-compression-1-in

Thank you