Bug #81557 MySQL Fabric uses wrong argument of MAKETIME in prune_log Event

• Submitted: 24 May 2016 4:25
• Modified: 26 Jul 2016 2:14
• Reporter: Tsubasa Tanaka (OCA)
• Status: Won't fix
• Category: MySQL Fabric
• Severity: S2 (Serious)
• Version: 1.5.6
• OS: Any
• CPU Architecture: Any
• Tags: maketime, mysqlfabric, prune_error_log, prune_log

[24 May 2016 4:25] Tsubasa Tanaka

Description:
MySQL Fabric uses wrong argument of MAKETIME in prune_log and prune_error_log events.

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

https://github.com/mysql/mysql-fabric/blob/release/1.5.6/lib/mysql/fabric/error_log.py#L62...

MAKETIME function's arguments are (hour, minute, second) but MySQL Fabric passes prune_time as **hour**

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_maketime

How to repeat:
Run query which is written in prune_log event.

```
mysql> \W
Show warnings enabled.

mysql> show create event prune_log\G
*************************** 1. row ***************************
               Event: prune_log
            sql_mode: NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`fabric`@`127.0.0.1` EVENT `prune_log` ON SCHEDULE EVERY 3600 SECOND STARTS '2016-04-04 15:49:10' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM log WHERE TIMEDIFF(UTC_TIMESTAMP(), reported) > MAKETIME(3600,0,0)
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql> DELETE FROM log WHERE TIMEDIFF(UTC_TIMESTAMP(), reported) > MAKETIME(3600,0,0);
Query OK, 0 rows affected, 1 warning (0.03 sec)

Warning (Code 1292): Truncated incorrect time value: '3600:00:00'

mysql> SELECT maketime(3600, 0, 0);
+----------------------+
| maketime(3600, 0, 0) |
+----------------------+
| 838:59:59            |
+----------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect time value: '3600:00:00'
```

Suggested fix:
See attached Pull-Request.

[24 May 2016 4:29] Tsubasa Tanaka

https://github.com/mysql/mysql-fabric/pull/4

[24 May 2016 6:19] MySQL Verification Team

Hello Tanaka-San,

Thank you for the report.

Thanks,
Umesh

[24 May 2016 13:28] OCA Admin

Contribution submitted via Github - Fix Bug#81557 
(*) Contribution by tsubasa tanaka (Github yoku0825, mysql-fabric/pull/4#issuecomment-221252281): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_71156377.txt (text/plain), 1.31 KiB.

[25 Jul 2016 11:00] Tsubasa Tanaka

It's my mistake, MAKETIME(0, 0, %s) returns NULL when %s is greater than 59.

mysql> SELECT MAKETIME(0,0,3600);
+--------------------+
| MAKETIME(0,0,3600) |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT MAKETIME(0,0,60);
+------------------+
| MAKETIME(0,0,60) |
+------------------+
| NULL             |
+------------------+
1 row in set (0.00 sec)

So, prune_log Event should not use MAKETIME, just compare them as second(int value) like this.

CURRENT_TIMESTAMP() - TIMESTAMP(reported) > %s.

[26 Jul 2016 2:14] Tsubasa Tanaka

One more for optimizer, `DELETE FROM log WHERE reported < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %s SECOND)` is better.

See also, Bug#81558

[6 Jul 2017 19:20] Bugs System

Status updated to 'Won't fix' (Fabric is now covered under Oracle Lifetime Sustaining Support)