What to do When Your Data Smiles At You…

Posted in: Technical Track

I have *never* had this happen to me.

Maybe it’s because it’s MySQL 6.0.4, maybe it’s because it’s on Windows, or perhaps I am just up working too late.

I have seen mojibake before, but usually it is unintelligible. But this? After I post this I am backing away slowly from my computer.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.0.4-alpha-community MySQL Community Server (GPL)

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

mysql> use test;
Database changed
mysql> create table bits (val bit);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into bits (val) VALUES (1),(0),(1),(1),(0);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from bits;
+------+
| val  |
+------+
| ☺    |
|      |
| ☺    |
| ☺    |
|      |
+------+
5 rows in set (0.00 sec)

mysql>

Has your data ever smiled at you?

Interested in working with Sheeri? Schedule a tech call.

7 Comments. Leave new

Antony Curtis
June 23, 2008 10:56 pm

IBM ASCII 1 looks like the smiley face…

Nothing unusual… trust me. ;)

Reply

Yea – you just have UTF8 working in your console, plus the MySQL connection and everything else on the same wavelength.

Reply
Roland Bouman
June 24, 2008 1:21 am

I think this is due to the encoding used by the windows shell.

Reply

Yeah, and…I stood in open-mouthed!

This is not due to 6.0.4-alpha. For bit fields, please try use the following script:

select version();

use test;

drop table if exists bits;
create table bits (id int auto_increment primary key, val bit(1));

insert into bits (id) values (1),(2),(3);
select * from bits;

update bits set val=b’1′ where id=1;
update bits set val=b’0′ where id=2;
update bits set val=b’1′ where id=3;

select bin(val) from bits;

Output:

mysql> select version();
+————————-+
| version() |
+————————-+
| 5.1.24-rc-community-log |
+————————-+
1 row in set (0.00 sec)

mysql>
mysql> use test;
Database changed
mysql>
mysql> drop table if exists bits;
Query OK, 0 rows affected (0.05 sec)

mysql> create table bits (id int auto_increment primary key, val bit(1));
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> insert into bits (id) values (1),(2),(3);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from bits;
+—-+——+
| id | val |
+—-+——+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
+—-+——+
3 rows in set (0.00 sec)

mysql>
mysql> update bits set val=b’1′ where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update bits set val=b’0′ where id=2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update bits set val=b’1′ where id=3;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>
mysql> select bin(val) from bits;
+———-+
| bin(val) |
+———-+
| 1 |
| 0 |
| 1 |
+———-+

Reply

Yes, it has! :) The smiling only happens on Windows however – I guess it just tries to make you more happy! ;)

http://bugs.mysql.com/bug.php?id=28422

http://dev.mysql.com/doc/refman/5.0/en/bit-field-values.html

Reply

Yes, I’ve had all manner of oddball characters spew out at me, particularly when developing Federated and I break something in the process.

Reply

I’m curious to test it on mysql shipped with opensuse 10.3. It shows diamond for bit 1 and blank for 0. When i paste to this comment text…it’s different.

mysql> select * from bits;
+——+
| val |
+——+
|  |
| |
|  |
| |
+——+

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *