MySql 5 weirdness ..
Tuesday, April 21st, 2009So, a user was having some issues with their ranks being calculated totally wrong. They were seeing numerous games against themselves. Turns out this was the issue – MySql treats varchar() text with trailing spaces the same as varchar text without trailing spaces. So the name “philhassey” is the same as “philhassey ” or “philhassey ” in a ‘select * from ip_stats3 where name = ?’ type of query. Here’s a MySQL session below to demonstrate.
mysql> describe ip_stats3;
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| name | varchar(32) | NO | PRI | NULL | |
| data | longblob | YES | | NULL | |
| s_rank | int(11) | YES | | NULL | |
| s_win | int(11) | YES | | NULL | |
| s_value | int(11) | YES | | NULL | |
| s_total | int(11) | YES | | NULL | |
+———+————-+——+—–+———+——-+
6 rows in set (0.00 sec)
mysql> select name from ip_stats3 where name = ‘philhassey ‘;
+————+
| name |
+————+
| philhassey |
+————+
1 row in set (0.00 sec)
mysql> select name from ip_stats3 where name = ‘philhassey’;
+————+
| name |
+————+
| philhassey |
+————+
1 row in set (0.00 sec)
mysql> select name from ip_stats3 where name like ‘philhassey’;
+————+
| name |
+————+
| philhassey |
+————+
1 row in set (0.00 sec)
mysql> select name from ip_stats3 where name like ‘philhassey ‘;
Empty set (0.00 sec)
mysql> select name from ip_stats3 where name like ‘philhassey%’;
+————+
| name |
+————+
| philhassey |
+————+
1 row in set (0.00 sec)
