Learn why the unknown character set error on PHPMyAdmin appears and how to solve it.

This error usually happens when you try to import an entire database, or single table that was exported from a newer version of PHPMyAdmin. So when you try to import this file into an older version of MySQL that doesn't support the utf8mb4 charset, you will face the exception. The most probably case is that locally you have a major version of MySQL, and on stage/hosted VPS you have MySQL server version less than 5.5.3.

Check the following users.sql file:

-- phpMyAdmin SQL Dump
-- version 4.6.5.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 29, 2017 at 04:27 PM
-- Server version: 10.1.21-MariaDB
-- PHP Version: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;

As you can see, if this table is imported in some database of your PHPMyAdmin with a version of MySQL lower to 5.5.3, the following line will throw the known exception:

/*!40101 SET NAMES utf8mb4 */;

How to solve it

There's a good chance (depending of the kind of characters stored on your table) that you can solve this issue by simply changing the utf8mb4 with just utf8. According to the MySQL docs, the utf8mb4 charset is a superset of utf8 that uses a maximum of four bytes per character and supports supplementary characters (instead of 3 that uses utf8).

Alternatively, if you are able to, you can update the version of MySQL installed on your server that supports the utf8mb4 charset.

Happy coding !


Interested in programming since he was 14 years old, Carlos is a self-taught programmer and founder and author of most of the articles at Our Code World.

Become a more social person

Sponsors