Bug #20388 (documented!) system variable not recognized
Submitted: 12 Jun 2006 6:48 Modified: 22 Jun 2006 16:45
Reporter: Roman Lamotkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.22 OS:Microsoft Windows (Windows 98SE)
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D4 (Minor)

[12 Jun 2006 6:48] Roman Lamotkin
Description:
I took courage to reproduce the following message
of mine from forum just because of the documented
'convert_character_set' variable recognition error:

I created a table entirely on UTF8 string field(s).
Suppose I want to 'LOAD DATA LOCAL INFILE ...'
from a file containing strings in CPXXXX charset.
Should I set one (some) of 'convert_character_set',
'character_set_client', 'character_set_connection',
'character_set_server' variables prior to execute
'LOAD ...', or just fire away all-in-one
'SET CHARACTER SET'?
Tried every possible option ('convert_character_set'
not recognized in anyway), and the results were
all the same unhappy ...
Any ideas?

How to repeat:
DROP TABLE IF EXISTS `sometable`;
CREATE TABLE `sometable` (
  `id` smallint unsigned NOT NULL,
  `name` char(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

SET CHARACTER SET cp1251;
-- try every combination below
# SET convert_character_set = 'cp1251';
# SET character_set_client = 'cp1251';
# SET character_set_results = 'cp1251';
# SET character_set_connection = 'utf8';

LOAD DATA LOCAL INFILE 'DataFile.txt' INTO TABLE `sometable`
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\r\n';
SET CHARACTER SET default;

Select * from `sometable`;

# SET character_set_client = 'utf8';
# SET character_set_results = 'utf8';
# SET character_set_connection = 'utf8';

Suggested fix:
something deep inside the sources ...
[12 Jun 2006 11:43] Roman Lamotkin
Got the problem fixed, the 'character_set_database'
system variable was the clue.

However, the documentation still need to be corrected:
what is the 'convert_character_set' for?
[13 Jun 2006 14:13] Valeriy Kravchuk
convert_character_set varable should be removed from http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html, as it does not really "work" in 5.0.22:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET convert_character_set = 'cp1251';
ERROR 1193 (HY000): Unknown system variable 'convert_character_set'
mysql> SET GLOBAL convert_character_set = 'cp1251';
ERROR 1193 (HY000): Unknown system variable 'convert_character_set'
mysql> SET SESSION convert_character_set = 'cp1251';
ERROR 1193 (HY000): Unknown system variable 'convert_character_set'

Or proper way to use it, if any, should be described.
[22 Jun 2006 16:45] Paul Dubois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

This variable was removed in MySQL 4.1.  I've removed
mention of it from the 5.0 and 5.1 manuals.