[CLOUDSTACK-5534] MySQL exception raised when searching for users with keyword

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.2.0
• Fix Version/s: 4.3.0
• Component/s: API
• Security Level: Public (Anyone can view this level - this is the default.)
• Assignee: Min Chen
• Reporter: Min Chen
• Created: 18/Dec/13 00:33
• Updated: 18/Dec/13 01:09
• Resolved: 18/Dec/13 01:09

Description

The following error dialog is shown when you attempt to search for users.

Caught: com.mysql.jdbc.JDBC4PreparedStatement@541ee970: SELECT user_view.id, user_view.uuid, user_view.username, user_view.password, user_view.firstname, user_view.lastname, user_view.email, user_view.state, user_view.api_key, user_view.secret_key, user_view.created, user_view.removed, user_view.timezone, user_view.registration_token, user_view.is_registered, user_view.incorrect_login_attempts, user_view.account_id, user_view.account_uuid, user_view.account_name, user_view.account_type, user_view.domain_id, user_view.domain_uuid, user_view.domain_name, user_view.domain_path, user_view.job_id, user_view.job_uuid, user_view.job_status, user_view.default FROM user_view WHERE user_view.id != 1 AND user_view.domain_id = 1 AND user_view.account_name = _binary'admin' AND (user_view.username LIKE _binary'%test%' OR user_view.firstname LIKE _binary'%test%' OR user_view.lastname LIKE _binary'%test%' OR user_view.email LIKE _binary'%test%' OR user_view.state LIKE _binary'%test%' OR user_view.account_name LIKE _binary'%test%' OR OR ) AND user_view.removed IS NULL ORDER BY user_view.id ASC LIMIT 0, 20

REPRO STEPS
====================
1. From CCP UI Home screen.
2. Click Accounts.
3. Click Admin.
4. Click View Users.
5. Type anything in search window and press ENTER.
6. Error dialog will appear.
7. Look closely, the query contains two consecutive OR's (e.g. "OR OR") and this is the reason it results in an error.
8. If you run the query manually after removing the 2 OR's, it will be successful.