Re: effective_cache_size vs units

Am Mittwoch, 20. Dezember 2006 13:42 schrieb Tom Dunstan:
> I suppose we should think about mysql refugees at some point, though. I
> wonder what they do. The documentation is silent on the matter (and all
> their examples are in lower case). Mysql is generally case insensitive,
> right?

Maybe you can make sense of this, but I can't ...


mysql> create table test (a int, b enum ('x', 'X', 'y'));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1291 | Column 'b' has duplicated value 'x' in ENUM |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test values (1, 'x');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (1, 'X');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (1, 'y');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (1, 'Y');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (1, 'z');
Query OK, 1 row affected, 1 warning (0.00 sec)

## You think that was funny -- now watch this:

mysql> select * from test;
+------+------+
| a    | b    |
+------+------+
|    1 | x    |
|    1 | x    |
|    1 | y    |
|    1 | y    |
|    1 |      |
+------+------+
5 rows in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (a int, b enum ('ä', 'Ä', ' ', '  '));
## Above is a-diaeresis, A-diaeresis.
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1291 | Column 'b' has duplicated value '?' in ENUM | # literal ?
| Note  | 1291 | Column 'b' has duplicated value '' in ENUM  |
+-------+------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql> insert into test values (1, ' ');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (1, '  ');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (1, '   ');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values (1, '   |');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'b' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select distinct * from test;
+------+------+
| a    | b    |
+------+------+
|    1 | ä    |
|    1 |      |
|    1 |      |
+------+------+


Better not imitate that.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/