[MDEV-6902] Script For Creating Unicode Fields in MySQL Fails on MariaDB Created: 2014-10-21  Updated: 2016-04-18  Due: 2014-10-28  Resolved: 2014-10-29

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 5.3.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Zian Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None
Environment:

Apache/2.2
Database client version: libmysql - mysqlnd 5.0.8-dev - 20102224
PHP extension: mysqli

Protocol version: 10
Server charset: UTF-8 Unicode (utf8)
Server Environment: NearlyFreeSpeech.net

Attachments: PNG File 5.5.41-settings.png     PNG File MariaDB.png     PNG File MySQL.png     PNG File VM MySQL Screenshot.png     Text File Works on MySQL and Not on Maria DB.txt    

 Description   

The attached SQL script creates a table named "food_items" with an "Ingredients" field that holds Chinese characters without any trouble on MySQL running in the following environment:

  • Window Version 6.01 (Build 7601 : Service Pack 1)
  • MySQL Version: 5.6.20 MySQL Community Server (GPL)

However, if I run it on MariaDB via phpMyAdmin on NearlyFreeSpech.net, then inserting Chinese characters into the database results in question marks.

I've included screenshots of the table structure from MariaDB and MySQL.

The following INSERT statement fails.
INSERT INTO `cake_ht`.`food_items` (`ID`, `Name`, `Ingredients`, `Comments`) VALUES ('', 'Gai-lan', '芥蘭', NULL);

Error Message:
Warning: #1366 Incorrect integer value: '' for column 'ID' at row 1
Warning: #1366 Incorrect string value: '\xE8\x8A\xA5\xE8\x98\xAD' for column 'Ingredients' at row 1

 Comments   
Comment by Elena Stepanova [ 2014-10-21 ]

Hi,

Judging by the collation values visible in the screenshots, in MariaDB instance your columns are of latin1 charset, while in MySQL they are utf8. Did you try to use utf8 in MariaDB, too?

Comment by Elena Stepanova [ 2014-10-29 ]

Closing as incomplete for now. If you have more information, please comment to re-open.

Comment by Zian [ 2015-02-09 ]

Then we have dueling default character sets.

I installed MySQL on a stock Windows 7 x86 virtual machine using the MySQL Installer for Windows, ran the CREATE script, and then ran the following:

INSERT INTO `food_items` (`ID`, `Name`, `Ingredients`, `Comments`) VALUES ('1', 'Gai-lan', '芥蘭', NULL);

I did not receive an error message and, after performing a SELECT on the food_items table, I could see the Chinese characters correctly.

I have taken a screenshot of the schema using MySQL Workbench and will attach it.

Comment by Elena Stepanova [ 2015-02-09 ]

If you are really using 5.3.12, you're comparing a 2-year-old build (and installer) with the fairly new MySQL version. If you use MariaDB 10.0 which is similar to MySQL 5,6 in terms of being the latest stable release, or at least MariaDB 5.5 which is still closer, you will have the choice of using UTF8 during installation, see the attached screenshot.

Comment by Zian [ 2016-04-18 ]

You're right. NearlyFreeSpeech is still using 5.3.12.

I'm glad to see the new option in the wizard. I will petition my web host to switch to a newer version and use UTF8 by default just like MySQL.

Comment by Zian [ 2016-04-18 ]

For the sake of anyone with the same problem, the instructions for switching to MariaDB 10 are available at https://members.nearlyfreespeech.net/forums/viewtopic.php?t=8441&highlight=mariadb

To quote:
You can choose MariaDB 10 when you create a process.

To switch from an existing 5.x process, you'll have to create a new one and move your data over. There are too many differences to perform in-place upgrades; it is effectively essential that you have the two running side by side and confirm that the new one is working before you blow up the old one.

Generated at Thu Feb 08 07:15:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.