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/