FIX - phpmysql error - #1273 - #1273 - Unknown collation: 'utf8mb4_general_ci' - with sql file only

How to FIX - phpmysql error - #1273 - #1273 - Unknown collation: 'utf8mb4_general_ci'

Why does it happens?

The utf8mb4_unicode_ci is supported only on mysql servers 5.5.3+ and you will not be available on older servers. That's all.

The worst solution

if you have only an sql file you can TRY to

... COLLATE utf8mb4_unicode_ci
to
utf8_general_ci

but it's NOT SAFE because you can probably have problems with the encoding!!! I've tested and it also works but really don't know if there can be consequences.

The best solution

Export from the original database in utf8_general_ci or, if you just have the sql, Import your data in a newer mysql server (5.5.3+) and export again with utf8_general_ci!


Another way to do it
If you want to operate on a live database run this sql for each table.

ALTER TABLE name-of-table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

Sample (crappy) php code

<?php
 $dbuser = 'test2';
 $dbpassword = 'test2';
 $dbhost = 'localhost';
 $dbname = 'test2';
    $dbconn=mysqli_connect($dbhost, $dbuser, $dbpassword);
    mysqli_select_db($dbconn,$dbname);
    $mydata=mysqli_query($dbconn, 'show tables');
    while($mytables = mysqli_fetch_array($mydata)) {
       foreach ($mytables as $myktable => $mytable) {
        mysqli_query($dbconn, "ALTER TABLE $mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");

       }

    }
?> 

and then export your data.




This happened with several cms like wordpress, joomla, magento, opencart, drupal, smf forums, etc.

It's not a problem related to the scripts!