Bug #81559 Incorrect WHERE clause in dump_servers fanction
Submitted: 24 May 2016 5:24 Modified: 18 Aug 2016 3:33
Reporter: Tsubasa Tanaka (OCA) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Fabric Severity:S2 (Serious)
Version:1.5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: dump servers, Mysql Fabric, STATUS

[24 May 2016 5:24] Tsubasa Tanaka
Description:
Query from dump_servers's WHERE clause is incorrect.

https://github.com/mysql/mysql-fabric/blob/release/1.5.6/lib/mysql/fabric/server.py#L838-L...

`status` compares (not equal) with string 'FAULTY' but `status` has integer datatype.

```
mysql> show create table servers\G
*************************** 1. row ***************************
       Table: servers
Create Table: CREATE TABLE `servers` (
  `server_uuid` varchar(40) NOT NULL,
  `server_address` varchar(128) NOT NULL,
  `mode` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `weight` float NOT NULL,
  `group_id` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`server_uuid`),
  UNIQUE KEY `idx_server_address` (`server_address`),
  KEY `idx_group_id` (`group_id`),
  CONSTRAINT `fk_group_id_servers` FOREIGN KEY (`group_id`) REFERENCES `groups` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
```

How to repeat:
```
mysql> SELECT server_uuid, group_id, server_address, mode, status, weight FROM servers WHERE group_id LIKE '%%' AND group_id IS NOT NULL AND status != 'FAULTY' ORDER BY group_id, server_address, server_uuid;
+--------------------------------------+--------------+---------------------+------+--------+--------+
| server_uuid                          | group_id     | server_address      | mode | status | weight |
+--------------------------------------+--------------+---------------------+------+--------+--------+
| 8658f0e6-fb9e-11e5-8c6f-001a4a571800 | xxxx_yyyyyyy | 172.19.132.100:3306 |    3 |      3 |      1 |
| fbaeec10-e507-11e5-b2af-001a4a5718ee | zzzzzzzzzz   | 172.19.132.110:3306 |    3 |      3 |      1 |
+--------------------------------------+--------------+---------------------+------+--------+--------+
2 rows in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: 'FAULTY'
```

Suggested fix:
Cast string to integer or `servers`.`status` has to use ENUM datatype.

https://github.com/mysql/mysql-fabric/blob/release/1.5.6/lib/mysql/fabric/server.py#L875-L...
[24 May 2016 6:19] MySQL Verification Team
Hello Tanaka-San,

Thank you for the report.

Thanks,
Umesh
[18 Aug 2016 3:33] Tsubasa Tanaka
I fixed this as https://github.com/gmo-media/mikasafabric/commit/8f5b2613f3c40b9520f726122f35889218fe4a81
[6 Jul 2017 19:20] Bugs System
Status updated to 'Won't fix' (Fabric is now covered under Oracle Lifetime Sustaining Support)