Welcome to the blog of Marcel Oelke from Frankfurt, Germany.

Archive for November, 2006

MD5 Hash Database Update - Supports SHA1 Now

27 Nov, 2006  11 Comments

md5.rednoize.com - md5/sha1 hash databaseMd5.rednoize.com has been updated yesterday. The web site features a new design and has gained support for sha1 hashes. The interface has been ajaxified and the amount of hashes stored in the database has been increased again. The database stores now about 35,000,000 md5/sha1 hashes with their plain text counterpart. And md5.rednoize.com got a new logo as well ;)

The “URL API” has not been changed. You still may use the parameter “p” for plain text and “xml” for xml output. The new optional url parameter “s” defines the search mode (currently supported are md5 and sha1).

I would greatly appreciate your feedback ;)

MySQL: Collation matters when using unique indexes

26 Nov, 2006  1 Comment

When using a uniqie index on a text field in mysql, the column collation setting is very important. The collation settings of a column does not only affect sorting and comparsion, but also unique indexes. So you can not insert "a" and "A" into a table that has a unique index on a column that has a case-insensitive collation. The mysql manual about collations: "A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set."

Here is an example:
The column text in table text1 has a case-sensitive collation (_cs suffix), the column in text2 has a case-insensitive collation (_ci suffix).

PLAIN TEXT
CODE:
  1.  
  2. CREATE TABLE text1 (
  3.   `text` varchar(50) character set latin1 collate latin1_general_cs NOT NULL default '',
  4.   UNIQUE KEY uniq_text (text)
  5. ) ENGINE=MyISAM
  6. ;
  7. INSERT INTO text1 (text) VALUES ('a');
  8. Query OK, 1 row affected (0.00 sec)
  9.  
  10. INSERT INTO text1 (text) VALUES ('A');
  11. Query OK, 1 row affected (0.00 sec)
  12.  
  13.  
  14. CREATE TABLE text2 (
  15.   `text` varchar(50) character set latin1 collate latin1_general_ci NOT NULL default '',
  16.   UNIQUE KEY uniq_text (text)
  17. ) ENGINE=MyISAM
  18. ;
  19. INSERT INTO text2 (text) VALUES ('a');
  20. Query OK, 1 row affected (0.00 sec)
  21.  
  22. INSERT INTO text2 (text) VALUES ('A');
  23. ERROR 1062 (23000): Duplicate entry 'A' for key 1
  24.  

Constraints are also affected by collation:
The queries on table text1 give different results (a and A), the two queries on table text2 result in the same row twice (a).

PLAIN TEXT
CODE:
  1.  
  2. SELECT * FROM text1 WHERE text = 'a';
  3. +------+
  4. | text |
  5. +------+
  6. | a    |
  7. +------+
  8. 1 row in set (0.01 sec)
  9.  
  10. SELECT * FROM text1 WHERE text = 'A';
  11. +------+
  12. | text |
  13. +------+
  14. | A    |
  15. +------+
  16. 1 row in set (0.00 sec)
  17.  
  18. SELECT * FROM text2 WHERE text = 'a';
  19. +------+
  20. | text |
  21. +------+
  22. | a    |
  23. +------+
  24. 1 row in set (0.00 sec)
  25.  
  26. SELECT * FROM text2 WHERE text = 'A';
  27. +------+
  28. | text |
  29. +------+
  30. | a    |
  31. +------+
  32. 1 row in set (0.00 sec)
  33.  

You can display all available collations using

PLAIN TEXT
SQL:
  1. SHOW COLLATION;

or

PLAIN TEXT
SQL:
  1. SHOW COLLATION LIKE 'latin%';