Bug #69430 MySQL ignoring where clause under some conditions

• Submitted: 7 Jun 2013 21:22
• Modified: 10 Jun 2013 9:03
• Reporter: Karsten Malcher
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.5.31
• OS: Linux
• CPU Architecture: Any

[7 Jun 2013 21:22] Karsten Malcher

Description:
Please refer to this bug:
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=711254

But maybe it's a feature and not a bug?
http://bugs.mysql.com/bug.php?id=57384

But currently "sql safe updates" is "off" so this should not be the reason.

So what's running false?

How to repeat:
One example with an installed wikimedia.

I can't use or update the user public.
The user exists - i can see it with

select * from user WHERE user_name = 'public'

but when i try to update the users password with

UPDATE user SET user_password = CONCAT(':A:', MD5('public')) WHERE user_name = 'public'

no rows are affected.

The user is not found by the wiki when you try to login.
It's the same with the updatescript from wikimedia:

# php changePassword.php --user=public --password=public
No such user: public

[10 Jun 2013 8:10] MySQL Verification Team

Hello Karsten,

Thank you for the report.
I can not repeat described behavior with the latest MySQL version(5.5.32 which is GA).

mysql> select * from user WHERE user_name = 'public';
+---------+-----------+----------------+-------------------------------------+----------------------------------------------+-------------------+------------------+----------------+----------------------------------+--------------------------+------------------+--------------------------+-------------------+----------------+
| user_id | user_name | user_real_name | user_password                       | user_newpassword                             | user_newpass_time | user_email       | user_touched   | user_token                       | user_email_authenticated | user_email_token | user_email_token_expires | user_registration | user_editcount |
+---------+-----------+----------------+-------------------------------------+----------------------------------------------+-------------------+------------------+----------------+----------------------------------+--------------------------+------------------+--------------------------+-------------------+----------------+
|       2 | public    | Public         | :A:4c9184f37cff01bcdc32dc486ec36961 | :B:b50fb7bb:e5fcb263a54546d423589c83cc5b1263 | 20130605082126    | [e-mail] | 20130605082131 | 20071015111247                   | NULL                     | NULL             | NULL                     | NULL              |           NULL |
+---------+-----------+----------------+-------------------------------------+----------------------------------------------+-------------------+------------------+----------------+----------------------------------+--------------------------+------------------+--------------------------+-------------------+----------------+
1 row in set (0.00 sec)

mysql> UPDATE user SET user_password = CONCAT(':C:', MD5('public')) WHERE user_name = 'public';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

mysql> select * from user WHERE user_name = 'public' and user_password=CONCAT(':C:', MD5('public'));
+---------+-----------+----------------+-------------------------------------+----------------------------------------------+-------------------+------------------+----------------+----------------------------------+--------------------------+------------------+--------------------------+-------------------+----------------+
| user_id | user_name | user_real_name | user_password                       | user_newpassword                             | user_newpass_time | user_email       | user_touched   | user_token                       | user_email_authenticated | user_email_token | user_email_token_expires | user_registration | user_editcount |
+---------+-----------+----------------+-------------------------------------+----------------------------------------------+-------------------+------------------+----------------+----------------------------------+--------------------------+------------------+--------------------------+-------------------+----------------+
|       2 | public    | Public         | :C:4c9184f37cff01bcdc32dc486ec36961 | :B:b50fb7bb:e5fcb263a54546d423589c83cc5b1263 | 20130605082126    | [e-mail] | 20130605082131 | 20071015111247                   | NULL                     | NULL             | NULL                     | NULL              |           NULL |
+---------+-----------+----------------+-------------------------------------+----------------------------------------------+-------------------+------------------+----------------+----------------------------------+--------------------------+------------------+--------------------------+-------------------+----------------+
1 row in set (0.00 sec)

mysql>

// general query log

130611 11:51:39     7 Query     select * from user WHERE user_name = 'public'
130611 11:52:14     7 Query     UPDATE user SET user_password = CONCAT(':C:', MD5('public')) WHERE user_name = 'public'
130611 11:53:07     7 Query     select * from user WHERE user_name = 'public' and user_password=CONCAT(':C:', MD5('public'))

This doesn't seems to be a MySQL problem at all, could you check with the wikimedia if it is reported by other users too post up gradation?

Please reopen the defect if required.

Thanks,
Umesh

[10 Jun 2013 8:38] Karsten Malcher

Yes - it seems to be a problem in PHP5 and not in MySQL.

This bug can be closed.

[10 Jun 2013 8:40] MySQL Verification Team

Thank you Karsten for confirming.
I am closing this bug now - please reopen, if needed

Regards,
Umesh

[10 Jun 2013 9:03] Karsten Malcher

Maybe there is a little problem regarding mySQL.
Refer to this bug:
http://bugs.mysql.com/bug.php?id=65111

In mediawiki i found this setting:
$wgDBTableOptions   = "ENGINE=InnoDB, DEFAULT CHARSET=utf8";