Bug #61141 I am unable to insert/update only blank space in any field.
Submitted: 12 May 2011 6:13 Modified: 7 Jul 2011 6:37
Reporter: rayiez fakerdey Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.1.56-log OS:Any
Assigned to: CPU Architecture:Any
Tags: cannot insert only a blank space in any field.

[12 May 2011 6:13] rayiez fakerdey
Description:
I am unable to insert/update only blank space in any field.

mysql> update country set thousands_sep = ' '  where iso_code = 'CN';
Query OK, 0 rows affected (0.07 sec)
Rows matched: 1  Changed: 0  Warnings: 0

changed is 0 . no update if i update it to a blank space and length is also 0.

mysql> select thousands_sep,length(thousands_sep) from country where iso_code = 'CN';
+---------------+-----------------------+
| thousands_sep | length(thousands_sep) |
+---------------+-----------------------+
|               |                     0 |
+---------------+-----------------------+
1 row in set (0.00 sec)

How to repeat:
update country set thousands_sep = ' '  where iso_code = 'CN';
[12 May 2011 8:57] Valeriy Kravchuk
Please, send the output of

show create table country\G
[12 Jun 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[17 Jun 2011 10:16] rayiez fakerdey
CREATE TABLE `country` ( `iso_code` char(2) NOT NULL, `name_fallback` varchar(80) NOT NULL, `code` char(3) DEFAULT NULL, `default_language` char(2) DEFAULT NULL, `zipregex` varchar(255) DEFAULT NULL, `zip_numlen` int(11) NOT NULL DEFAULT '0', `phone_prefix` int(11) DEFAULT NULL, `distance_unit` enum('km','miles') NOT NULL DEFAULT 'km', `decimal_sep` char(1) NOT NULL, `thousands_sep` char(1) NOT NULL, `entry_modtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`iso_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
[7 Jul 2011 6:37] Valeriy Kravchuk
So, this is CHAR(1) column... Then it is NOT a bug, read http://dev.mysql.com/doc/refman/5.1/en/char.html about this:

"When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled."

Use VARCHAR(1), or that SQL mode above, whatever...