Galcon Games
Phil Hassey - game dev blog
Phil Hassey as Snidely Whiplash
"You can't buy awesomeness.
You're born that way."

MySql 5 weirdness ..

So, 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)

Comments are closed.