Bug #86835 session_connect_attrs.processlist_id should be bigint unsigned
Submitted: 27 Jun 2017 10:40 Modified: 6 Sep 2017 14:20
Reporter: Xin Wu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S2 (Serious)
Version:5.7.17, 5.7.18 OS:Any
Assigned to: CPU Architecture:Any

[27 Jun 2017 10:40] Xin Wu
Description:
Performance_schema.session_connect_attrs.processlist_id is int instead of bigint unsigned like everywhere else. It caused the processlist_id column overflow in this table.

CREATE TABLE `session_connect_attrs` (
  `PROCESSLIST_ID` int(11) NOT NULL,
  `ATTR_NAME` varchar(32) COLLATE utf8_bin NOT NULL,
  `ATTR_VALUE` varchar(1024) COLLATE utf8_bin DEFAULT NULL,
  `ORDINAL_POSITION` int(11) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 COLLATE=utf8_bin

How to repeat:
see Description.
[27 Jun 2017 10:46] Daniël van Eeden
https://github.com/mysql/mysql-server/pull/149
[27 Jun 2017 10:50] Daniël van Eeden
Workaround: Drop the table and re-create it.

mysql [localhost] {root} (performance_schema) > DROP TABLE session_connect_attrs;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} (performance_schema) > CREATE TABLE `session_connect_attrs` (
    ->   `PROCESSLIST_ID` bigint unsigned NOT NULL,
    ->   `ATTR_NAME` varchar(32) COLLATE utf8_bin NOT NULL,
    ->   `ATTR_VALUE` varchar(1024) COLLATE utf8_bin DEFAULT NULL,
    ->   `ORDINAL_POSITION` int(11) DEFAULT NULL
    -> ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {root} (performance_schema) > select * from session_connect_attrs;
+----------------+-----------------+----------------+------------------+
| PROCESSLIST_ID | ATTR_NAME       | ATTR_VALUE     | ORDINAL_POSITION |
+----------------+-----------------+----------------+------------------+
|              4 | _os             | linux-glibc2.5 |                0 |
|              4 | _client_name    | libmysql       |                1 |
|              4 | _pid            | 9278           |                2 |
|              4 | _client_version | 5.7.18         |                3 |
|              4 | _platform       | x86_64         |                4 |
|              4 | program_name    | mysql          |                5 |
+----------------+-----------------+----------------+------------------+
6 rows in set (0.00 sec)
[27 Jun 2017 11:48] MySQL Verification Team
## Looks like at-least 2 of P_S table has INT(11)

root@localhost [performance_schema]> desc performance_schema.threads;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| THREAD_ID           | bigint(20) unsigned | NO   |     | NULL    |       |
| NAME                | varchar(128)        | NO   |     | NULL    |       |
| TYPE                | varchar(10)         | NO   |     | NULL    |       |
| PROCESSLIST_ID      | bigint(20) unsigned | YES  |     | NULL    |       |
| PROCESSLIST_USER    | varchar(32)         | YES  |     | NULL    |       |
| PROCESSLIST_HOST    | varchar(60)         | YES  |     | NULL    |       |
| PROCESSLIST_DB      | varchar(64)         | YES  |     | NULL    |       |
| PROCESSLIST_COMMAND | varchar(16)         | YES  |     | NULL    |       |
| PROCESSLIST_TIME    | bigint(20)          | YES  |     | NULL    |       |
| PROCESSLIST_STATE   | varchar(64)         | YES  |     | NULL    |       |
| PROCESSLIST_INFO    | longtext            | YES  |     | NULL    |       |
| PARENT_THREAD_ID    | bigint(20) unsigned | YES  |     | NULL    |       |
| ROLE                | varchar(64)         | YES  |     | NULL    |       |
| INSTRUMENTED        | enum('YES','NO')    | NO   |     | NULL    |       |
| HISTORY             | enum('YES','NO')    | NO   |     | NULL    |       |
| CONNECTION_TYPE     | varchar(16)         | YES  |     | NULL    |       |
| THREAD_OS_ID        | bigint(20) unsigned | YES  |     | NULL    |       |
+---------------------+---------------------+------+-----+---------+-------+
17 rows in set (0.00 sec)

root@localhost [performance_schema]> desc sys.io_by_thread_by_latency;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| user           | varchar(128)        | YES  |     | NULL    |       |
| total          | decimal(42,0)       | YES  |     | NULL    |       |
| total_latency  | text                | YES  |     | NULL    |       |
| min_latency    | text                | YES  |     | NULL    |       |
| avg_latency    | text                | YES  |     | NULL    |       |
| max_latency    | text                | YES  |     | NULL    |       |
| thread_id      | bigint(20) unsigned | NO   |     | NULL    |       |
| processlist_id | bigint(20) unsigned | YES  |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

root@localhost [performance_schema]> desc sys.x$io_by_thread_by_latency;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| user           | varchar(128)        | YES  |     | NULL    |       |
| total          | decimal(42,0)       | YES  |     | NULL    |       |
| total_latency  | decimal(42,0)       | YES  |     | NULL    |       |
| min_latency    | bigint(20) unsigned | YES  |     | NULL    |       |
| avg_latency    | decimal(24,4)       | YES  |     | NULL    |       |
| max_latency    | bigint(20) unsigned | YES  |     | NULL    |       |
| thread_id      | bigint(20) unsigned | NO   |     | NULL    |       |
| processlist_id | bigint(20) unsigned | YES  |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

root@localhost [performance_schema]> desc performance_schema.session_account_connect_attrs;
+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| PROCESSLIST_ID   | int(11)       | NO   |     | NULL    |       |
| ATTR_NAME        | varchar(32)   | NO   |     | NULL    |       |
| ATTR_VALUE       | varchar(1024) | YES  |     | NULL    |       |
| ORDINAL_POSITION | int(11)       | YES  |     | NULL    |       |
+------------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

root@localhost [performance_schema]> desc performance_schema.session_connect_attrs;
+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| PROCESSLIST_ID   | int(11)       | NO   |     | NULL    |       |
| ATTR_NAME        | varchar(32)   | NO   |     | NULL    |       |
| ATTR_VALUE       | varchar(1024) | YES  |     | NULL    |       |
| ORDINAL_POSITION | int(11)       | YES  |     | NULL    |       |
+------------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec
[27 Jun 2017 11:53] MySQL Verification Team
Hello Xin Wu,

Thank you for the report and feedback.

Thanks,
Umesh
[27 Jun 2017 13:31] OCA Admin
Contribution submitted via Github - Fix Bug#86835 session_connect_attrs.processlist_id should be bigint 
(*) Contribution by Daniël van Eeden (Github dveeden, mysql-server/pull/149#issuecomment-311343523): I confirm the code being submitted is offered under the terms of the OCA, 
and that I am authorized to contribute it.

On 27 June 2017 1:49:01 p.m. mysql-oca-bot <notifications@github.com> wrote:

> Hi, thank you for your contribution. Please confirm this code is submitted 
> under the terms of the OCA (Oracle''s Contribution Agreement) you have 
> previously signed by cutting and pasting the following text as a comment:
> "I confirm the code being submitted is offered under the terms of the OCA, 
> and that I am authorized to contribute it."
> Thanks
>
> --
> You are receiving this because you authored the thread.
> Reply to this email directly or view it on GitHub:
> https://github.com/mysql/mysql-server/pull/149#issuecomment-311334573

Contribution: git_patch_127663812.txt (text/plain), 838 bytes.

[3 Jul 2017 12:50] Marc ALFF
Thanks for the bug report AND for the contribution.

Please be aware that the contributed patch is incomplete,
and even dangerous to apply in production, which I do not recommend.

The patch changed the table definition,
but did not change the code of the server itself for this table and column,
so that the server binary and table structure are now out of sync.
[12 Jul 2017 13:55] OCA Admin
Contribution submitted via Github - Bug86835 
(*) Contribution by Daniël van Eeden (Github dveeden, mysql-server/pull/154#issuecomment-314558626): 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_129577352.txt (text/plain), 2.46 KiB.

[6 Sep 2017 14:20] Paul DuBois
Posted by developer:
 
Fixed in 8.0.3.

The PROCESS_ID column in the Performance Schema session_connect_attrs
and session_account_connect_attrs tables was changed from INT to
BIGINT UNSIGNED to accommodate larger process ID values. Thanks to
Daniël van Eeden for the patch.