MySQL 用戶端 8.0 在使用分頁器時在大型查詢上顯示“fwrite:損壞的管道”

MySQL 用戶端 8.0 在使用分頁器時在大型查詢上顯示“fwrite:損壞的管道”

我一直使用尋呼機(--pager="less -SFX"更具體地說)作為 MySQL 用戶端。之前我使用的是 MySQL 客戶端/伺服器 5.6 版本,它運作得很好。最近我開始使用MySQL客戶端/伺服器版本8.0,客戶端開始fwrite: Broken pipe在大型查詢結果上顯示:

+------+-------------------------------------------+-----------------+-----------+-------+--------+-------------+-----------------+----------------------------+----------------------------+-------------+---------------------+----------------------------+----------------
| id   | file                                      | content_type    | file_size | width | height | orientation | cln_customer_id | created_at                 | updated_at                 | uploader_id | uploader_type       | taken_at                   | customer_visibl
+------+-------------------------------------------+-----------------+-----------+-------+--------+-------------+-----------------+----------------------------+----------------------------+-------------+---------------------+----------------------------+----------------
|    1 | 9f3c8b92-9501-475e-8848-a115141a546f.png  | image/png       |     41665 |  1300 |    551 |        NULL |              15 | 2019-11-18 02:50:10.000000 | 2019-11-18 02:50:10.000000 |       16054 | Clover::User        | 2019-11-18 02:50:10.000000 |
|    2 | 655c35f2-d7a7-41a9-8814-a56d7398a5fd.png  | image/png       |     36674 |  1300 |    551 |        NULL |              15 | 2019-11-18 02:50:20.000000 | 2019-11-18 02:50:20.000000 |       16054 | Clover::User        | 2019-11-18 02:50:20.000000 |
|    3 | a1209273-3df6-4a7f-a94e-adcebe005e46.jpg  | image/jpeg      |     76467 |   600 |    600 |           1 |              16 | 2019-11-18 07:03:56.000000 | 2019-11-18 07:03:56.000000 |       16054 | Clover::User        | 2019-11-18 07:03:56.000000 |
|    4 | 09ef833b-128c-42bb-b2f8-63a3c91e5266.jpg  | image/jpeg      |     76467 |   600 |    600 |           1 |              16 | 2019-11-18 07:04:13.000000 | 2019-11-18 07:04:13.000000 |       16054 | Clover::User        | 2019-11-18 07:04:13.000000 |
|    5 | 52744941-2e06-450b-b4d4-3e996158bc5b.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              16 | 2019-11-19 08:17:21.000000 | 2019-11-19 08:17:21.000000 |        NULL | NULL                | 2019-11-19 08:17:21.000000 |
|    6 | 45d952c0-2d28-4a55-a620-02cbb1a21108.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              16 | 2019-11-19 08:17:37.000000 | 2019-11-19 08:17:37.000000 |        NULL | NULL                | 2019-11-19 08:17:37.000000 |
|    7 | ee3ae126-a639-4c67-97de-36355cd2abd1.jpg  | image/jpeg      |     95989 |  1200 |   1600 |           1 |              16 | 2019-11-19 08:18:00.000000 | 2020-08-15 10:21:30.000000 |        NULL | NULL                | 2018-01-11 00:27:13.000000 |
|    8 | b2c1e2f0-28aa-4c97-9041-76227cf2bc2e.jpg  | image/jpeg      |     76467 |   600 |    600 |           1 |              16 | 2019-11-19 08:18:21.000000 | 2019-11-19 08:18:21.000000 |        NULL | NULL                | 2019-11-19 08:18:21.000000 |
|   23 | f9436ac5-1d6e-4dcf-bd38-ab26078d37ec.jpg  | image/jpeg      |     76467 |   600 |    600 |           1 |              16 | 2019-11-19 08:39:06.000000 | 2019-11-19 08:39:06.000000 |        NULL | NULL                | 2019-11-19 08:39:06.000000 |
|   24 | de272f71-c397-4059-b7f8-da62038de195.jpg  | image/jpeg      |     63100 |   960 |   1280 |        NULL |              28 | 2019-11-19 08:59:06.000000 | 2019-11-21 10:11:08.000000 |        NULL | NULL                | 2019-11-19 08:59:06.000000 |
|   25 | 043c98b5-fe90-46fa-948c-e40ef53e1d8c.jpg  | image/jpeg      |     51291 |   960 |   1280 |        NULL |              28 | 2019-11-19 08:59:16.000000 | 2019-11-19 08:59:16.000000 |        NULL | NULL                | 2019-11-19 08:59:16.000000 |
|   26 | 10481989-e723-420f-9cfc-ffb16a467b55.jpg  | image/jpeg      |     49650 |  1280 |    960 |        NULL |              28 | 2019-11-19 08:59:27.000000 | 2019-11-19 08:59:27.000000 |        NULL | NULL                | 2019-11-19 08:59:27.000000 |
|   27 | 1449a37b-a1e2-4ba2-89b2-8baa48eb9908.jpg  | image/jpeg      |     63100 |   960 |   1280 |        NULL |              28 | 2019-11-19 08:59:27.000000 | 2019-11-19 08:59:27.000000 |        NULL | NULL                | 2019-11-19 08:59:27.000000 |
|   28 | d2ab04c2-debe-46b5-a85a-c52d2c96edf9.jpg  | image/jpeg      |     51291 |   960 |   1280 |        NULL |              28 | 2019-11-19 08:59:27.000000 | 2019-11-19 08:59:27.000000 |        NULL | NULL                | 2019-11-19 08:59:27.000000 |
|   29 | 655f1911-6ec5-491e-b2f6-c328051c3b31.jpg  | image/jpeg      |     63759 |   960 |   1280 |        NULL |              28 | 2019-11-19 08:59:27.000000 | 2019-11-21 10:10:51.000000 |        NULL | NULL                | 2019-11-19 08:59:27.000000 |
|   30 | 5329c902-fe4a-4b1e-b71d-d30b2fbbb34b.JPG  | image/jpeg      |   2751465 |  5184 |   3456 |           1 |              28 | 2019-11-19 08:59:45.000000 | 2020-08-15 10:21:31.000000 |        NULL | NULL                | 2019-07-03 08:45:22.000000 |
|   31 | e03c9a83-d282-4e57-b6f4-aab09b342561.JPG  | image/jpeg      |   2697520 |  5184 |   3456 |           1 |              28 | 2019-11-19 08:59:45.000000 | 2020-08-15 10:21:31.000000 |        NULL | NULL                | 2019-07-03 08:43:40.000000 |
|   58 | 1941f326-78b9-445d-adce-69631964136e.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              28 | 2019-11-19 10:41:53.000000 | 2019-11-19 10:41:53.000000 |        NULL | NULL                | 2019-11-19 10:41:53.000000 |
|   59 | ae3b1113-c4e5-4481-94de-ab492aad334f.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              28 | 2019-11-19 10:45:51.000000 | 2019-11-19 10:45:51.000000 |        NULL | NULL                | 2019-11-19 10:45:51.000000 |
|   60 | 10f254a6-d1b4-45f5-af0e-450a5ecfca05.png  | image/png       |     23282 |   175 |    175 |        NULL |              28 | 2019-11-19 10:45:51.000000 | 2019-11-21 10:11:03.000000 |        NULL | NULL                | 2019-11-19 10:45:51.000000 |
|   61 | dfb94c00-d28a-404b-8c11-b3dcf3653eef.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              28 | 2019-11-19 10:52:06.000000 | 2019-11-21 10:10:54.000000 |        NULL | NULL                | 2019-11-19 10:52:06.000000 |
|   62 | b2e24738-c140-45ca-89c1-2349e4244379.jpg  | image/jpeg      |     95989 |  1200 |   1600 |           1 |              28 | 2019-11-19 10:52:24.000000 | 2020-08-15 10:21:32.000000 |        NULL | NULL                | 2018-01-11 00:27:13.000000 |
|   63 | 7c049beb-2a37-4bc5-af07-ae102f70e4ce.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              28 | 2019-11-19 10:52:24.000000 | 2019-11-19 10:52:24.000000 |        NULL | NULL                | 2019-11-19 10:52:24.000000 |
|   64 | 4428ff0d-2714-4c96-b649-405b60fc6833.jpg  | image/jpeg      |   4186415 |  4000 |   3000 |           1 |              25 | 2019-11-20 02:11:10.000000 | 2019-11-20 02:11:10.000000 |       15054 | Clover::User        | 2019-11-20 02:11:10.000000 |
|  146 | 483cf7ac-4a96-4cc2-bf79-4ed1002bd047.png  | image/png       |     76578 |  2481 |   2481 |        NULL |              28 | 2019-11-21 15:41:43.000000 | 2019-11-26 05:48:08.000000 |       15054 | Clover::User        | 2019-11-21 15:41:43.000000 |
|  147 | e401414e-7b2d-4588-9d53-825d5d614694.png  | image/png       |     23282 |   175 |    175 |        NULL |              28 | 2019-11-22 03:30:50.000000 | 2019-11-26 05:07:40.000000 |       16054 | Clover::User        | 2019-11-22 03:30:50.000000 |
|  148 | 4d965b81-0f0b-4967-b402-27c0be360994.jpeg | image/jpeg      |     94853 |  1080 |   1440 |        NULL |              26 | 2019-11-22 09:44:12.000000 | 2019-11-22 09:50:53.000000 |       15054 | Clover::User        | 2019-11-22 09:44:12.000000 |
|  149 | 890f70a8-2dc6-4287-ba63-d2eb821ffce6.jpeg | image/jpeg      |     95042 |  1080 |   1440 |        NULL |              26 | 2019-11-22 09:44:12.000000 | 2019-11-22 09:49:19.000000 |       15054 | Clover::User        | 2019-11-22 09:44:12.000000 |
|  150 | bc107f90-3784-4080-ab5b-24a1d514a95f.jpeg | image/jpeg      |    130836 |  1080 |   1440 |        NULL |              26 | 2019-11-22 09:44:13.000000 | 2019-11-22 09:44:13.000000 |       15054 | Clover::User        | 2019-11-22 09:44:13.000000 |
|  151 | f4c71856-64a7-424d-a012-3309bc57f2a6.jpeg | image/jpeg      |    144424 |  1080 |   1440 |        NULL |              26 | 2019-11-22 09:44:13.000000 | 2019-11-22 09:50:12.000000 |       15054 | Clover::User        | 2019-11-22 09:44:13.000000 |
|  152 | 775be35d-c0db-4c6d-8b90-90a8542c3b86.jpg  | image/jpeg      |    228831 |   960 |   1280 |           1 |              29 | 2019-11-22 09:44:42.000000 | 2019-11-22 09:48:53.000000 |       16054 | Clover::User        | 2019-11-22 09:44:42.000000 |
|  153 | 576e2e9b-227e-4472-ab90-2dbc4d6567a1.JPG  | image/jpeg      |   4576317 |  5184 |   3456 |           8 |              29 | 2019-11-22 09:44:47.000000 | 2019-11-22 09:52:37.000000 |       16054 | Clover::User        | 2019-11-20 03:00:09.000000 |
|  154 | 650af5ce-23a5-4bc1-ac20-34a6aaad4d6a.JPG  | image/jpeg      |   5773855 |  5184 |   3456 |           8 |              29 | 2019-11-22 09:44:48.000000 | 2019-11-22 09:52:38.000000 |       16054 | Clover::User        | 2019-11-20 02:59:39.000000 |
|  155 | b6b3830a-0649-4d6c-88ee-b66b85a6b76f.JPG  | image/jpeg      |   4744838 |  5184 |   3456 |           8 |              29 | 2019-11-22 09:44:49.000000 | 2019-11-22 09:52:40.000000 |       16054 | Clover::User        | 2019-11-20 02:59:31.000000 |
|  156 | 9fe39409-4604-4ce0-941d-007a9010dd2f.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              29 | 2019-11-22 09:44:59.000000 | 2019-11-22 10:00:10.000000 |       16054 | Clover::User        | 2019-11-22 09:44:59.000000 |
|  157 | ed17d28c-9172-4fdb-8305-edf9f8fe049f.jpeg | image/jpeg      |   1732122 |  4032 |   3024 |           6 |              29 | 2019-11-22 09:47:23.000000 | 2019-11-22 09:47:23.000000 |       16054 | Clover::User        | 2019-11-05 04:36:39.000000 |
|  158 | f982f60f-fdcb-4666-8865-420ee545da29.jpeg | image/jpeg      |    497403 |  3648 |   2736 |           0 |              29 | 2019-11-22 09:48:03.000000 | 2019-11-22 09:48:03.000000 |       16054 | Clover::User        | 2019-09-13 15:39:54.000000 |
|  159 | 3c9eeb96-47ef-4e34-b081-4f52679bc71b.jpeg | image/jpeg      |   2047502 |  4032 |   3024 |           1 |              26 | 2019-11-22 09:51:41.000000 | 2019-11-22 09:51:52.000000 |       15054 | Clover::User        | 2019-11-21 08:11:08.000000 |
|  160 | 13ca5568-7c1d-4c80-b833-b75d7a13d44d.JPG  | image/jpeg      |   4576317 |  5184 |   3456 |           8 |              55 | 2019-11-29 08:29:07.000000 | 2019-11-29 10:01:53.000000 |       16054 | Clover::User        | 2019-11-20 03:00:09.000000 |
|  161 | ec66d057-4ab2-4886-a935-074b13446e88.jpg  | image/jpeg      |    228831 |   960 |   1280 |           1 |              55 | 2019-11-29 08:29:17.000000 | 2019-12-01 09:46:57.000000 |       16054 | Clover::User        | 2019-11-29 08:29:17.000000 |
|  162 | 3d76aed4-0f58-43f8-9825-339119bf5958.JPG  | image/jpeg      |   5773855 |  5184 |   3456 |           8 |              55 | 2019-11-29 08:29:23.000000 | 2019-12-01 09:46:40.000000 |       16054 | Clover::User        | 2019-11-20 02:59:39.000000 |
|  163 | c0485c94-f792-46af-92fe-6cd36d169b2b.JPG  | image/jpeg      |   4744838 |  5184 |   3456 |           8 |              55 | 2019-11-29 08:29:33.000000 | 2019-12-01 09:47:32.000000 |       16054 | Clover::User        | 2019-11-20 02:59:31.000000 |
|  164 | 5fd036b3-53d0-40c0-9bae-c712bd8cb983.jpg  | image/jpeg      |    228831 |   960 |   1280 |           1 |              55 | 2019-12-05 03:26:27.000000 | 2019-12-05 03:29:53.000000 |       16054 | Clover::User        | 2019-12-05 03:26:27.000000 |
|  165 | 482030f8-69f4-44fc-8b7b-d18e62bdf7f1.JPG  | image/jpeg      |   4744838 |  5184 |   3456 |           8 |              55 | 2019-12-05 03:27:51.000000 | 2019-12-05 03:29:06.000000 |       16054 | Clover::User        | 2019-11-20 02:59:31.000000 |
|  166 | bf0f6e65-4922-4407-bc4f-f2a73684aceb.JPG  | image/jpeg      |   5773855 |  5184 |   3456 |           8 |              55 | 2019-12-05 03:27:51.000000 | 2019-12-05 03:27:51.000000 |       16054 | Clover::User        | 2019-11-20 02:59:39.000000 |
|  167 | 4638d444-3bd6-4a82-a1a0-4e340d24a813.JPG  | image/jpeg      |   4576317 |  5184 |   3456 |           8 |              57 | 2019-12-05 03:35:21.000000 | 2019-12-09 02:05:20.000000 |       16054 | Clover::User        | 2019-11-20 03:00:09.000000 |
|  168 | 56a78d51-254b-4c76-88a4-df13596251c1.JPG  | image/jpeg      |   5773855 |  5184 |   3456 |           8 |              57 | 2019-12-05 03:35:21.000000 | 2019-12-09 02:05:13.000000 |       16054 | Clover::User        | 2019-11-20 02:59:39.000000 |
|  169 | 84974ca6-c000-4a0d-88f8-7693019784bc.jpg  | image/jpeg      |    228831 |   960 |   1280 |           1 |              57 | 2019-12-05 03:35:21.000000 | 2019-12-05 07:36:20.000000 |       16054 | Clover::User        | 2019-12-05 03:35:21.000000 |
|  170 | 41563fe0-5945-4c83-bd6b-ad1594109457.JPG  | image/jpeg      |   4744838 |  5184 |   3456 |           8 |              57 | 2019-12-05 03:35:22.000000 | 2019-12-06 09:42:38.000000 |       16054 | Clover::User        | 2019-11-20 02:59:31.000000 |
|  171 | 6f5c7a6d-81c0-43be-ae84-af5c256363ff.png  | image/png       |     38667 |  1300 |    551 |        NULL |               3 | 2019-12-05 05:10:38.000000 | 2019-12-05 05:11:09.000000 |       16055 | Clover::User        | 2019-12-05 05:10:38.000000 |
|  172 | 2a583861-6779-4cff-b1be-1d3f20ba58d1.jpeg | image/jpeg      |     95042 |  1080 |   1440 |        NULL |              58 | 2019-12-08 04:46:30.000000 | 2019-12-08 04:47:14.000000 |       15054 | Clover::User        | 2019-12-08 04:46:30.000000 |
|  173 | 461e86df-b3c6-4c2b-9d65-494fd9892001.jpeg | image/jpeg      |     94853 |  1080 |   1440 |        NULL |              58 | 2019-12-08 04:46:30.000000 | 2019-12-08 04:47:53.000000 |       15054 | Clover::User        | 2019-12-08 04:46:30.000000 |
|  174 | 654db564-d3ae-4d96-a9d5-23bd9ac2e444.jpeg | image/jpeg      |     83010 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  175 | 91e8dddc-5ef7-4fea-876e-18aa9968c789.jpeg | image/jpeg      |     77288 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  176 | ec391232-8c9d-4c77-801b-351f6509a9fd.jpeg | image/jpeg      |     85038 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  177 | df130d1b-aa6e-46a2-b11d-df5fae6fe289.jpeg | image/jpeg      |     89938 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  178 | 977a8600-683d-40ca-b603-6a09f7c83930.jpeg | image/jpeg      |     73886 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  179 | 3049c423-dc8c-4262-8ee0-87b086c754db.jpeg | image/jpeg      |     72657 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  180 | 507e8b77-5e3c-4aad-b272-3be276eee2fb.jpeg | image/jpeg      |    144424 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:17:41.000000 | 2019-12-08 14:17:41.000000 |       16065 | Clover::User        | 2019-12-08 14:17:41.000000 |
|  181 | 3dac7119-704b-4389-a387-c3d5482785aa.jpeg | image/jpeg      |    130836 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:17:41.000000 | 2019-12-08 14:17:41.000000 |       16065 | Clover::User        | 2019-12-08 14:17:41.000000 |
|  182 | f750220a-8860-4f49-92df-8165501841c4.jpg  | image/jpeg      |   1768676 |  4032 |   3024 |           1 |              58 | 2019-12-08 14:21:26.000000 | 2019-12-08 14:21:26.000000 |       16065 | Clover::User        | 2019-08-07 03:14:54.000000 |
|  183 | c4a9edd6-a0a9-4403-8b55-4e90e7ee9290.jpg  | image/jpeg      |   1785633 |  4032 |   3024 |           1 |              58 | 2019-12-08 14:21:26.000000 | 2019-12-08 14:21:26.000000 |       16065 | Clover::User        | 2019-08-07 03:15:06.000000 |
fwrite: Broken pipe
fwrite: Broken pipe
fwrite: Broken pipe
2632 rows in set (0.01 sec)
mysql>

關於錯誤的一些註解:

  • 當我按下按鍵時,錯誤訊息就會列印在螢幕上q
  • 當查看查詢結果的更多部分時(例如,透過在pagedown按下鍵之前向下導航q),錯誤訊息的行數會減少。如果到達查詢結果的最後一頁(無論是一直使用該pagedown鍵還是直接使用該end鍵跳過中間的所有頁面),那麼在我按下該q鍵後不會顯示任何錯誤訊息。
  • 說到“大查詢結果”,我還沒有找到一個精確的衡量標準。這絕對不僅僅是“大於一個螢幕頁面”。看來這不只是查詢結果的行數。該錯誤發生在 22 列 2632 行結果上,但未發生在 5 列 157161 行結果上。
  • 當查詢結果更大時,問題會變得更糟。我故意在大表上執行了多個查詢JOIN,但分頁器似乎根本沒有完成其工作。查詢結果充斥在螢幕上,我沒有停止鍵盤輸入,MySQL 用戶端進程最終耗盡了機器的所有運行記憶體。以前當我使用 5.6 用戶端和尋呼機時,無論查詢結果有多大,都不會出現這種情況。
  • 該問題似乎僅與客戶端程式有關。我嘗試使用8.0客戶端連接5.6伺服器(之前與5.6客戶端配合良好的伺服器),但仍然彈出錯誤。
  • less在不使用選項的情況下使用時,問題仍然存在-SFX
  • 該問題與特定的尋呼機程序無關less。我嘗試使用more尋呼機,結果大致相同。
  • 有問題的 MySQL 客戶端的確切版本是8.0.31
  • 我在Centos 7.9.

我對類似的問題進行了相當多的搜索,但到目前為止還沒有運氣。

答案1

當您將 MySql 用戶端與分頁程式進程一起使用時,會發生「fwrite:管道損壞」錯誤。

當分頁器進程(在您的情況下less)在 MySQL 用戶端完成寫入其輸出之前關閉輸出流時,會觸發該問題。有時,當您「暴力」停止尋呼機或輸出達到一定限制時,您會發現此錯誤。

現在,如何解決這個問題?有很多方法可以避免這種情況。

首先,您可以嘗試-F為尋呼機添加標誌less,以避免在發生長流時突然中斷。使用-F標誌,您允許 MySql 用戶端完成輸出。

其次,即使命令停止,您也可以使用-X-S標誌來格式化輸出並檢查輸出。less

另一個有用的標誌是-j從特定點開始查看輸出的標誌。如果您知道您的 MySQL 將嘔吐一個長輸出。

如果我處在你的位置,我會在less指揮中使用不同的旗幟組合

例如--pager="less -SFX -j<line_number>"

找到最適合我的情況。

相關內容