search

inovector / mixpost

Mixpost - Self-hosted social media management software (Buffer alternative)
https://mixpost.app
MIT License
1.17k stars 179 forks source link

MySQL out of sort memory buffer. Error: MY-013131 #115

Closed TxTechnician closed 2 months ago

TxTechnician commented 2 months ago

Steps to reproduce the problem

  1. Setup Mixpost Pro.
  2. Use "Pages" feature to add some web pages.
  3. Insert screenshots into the web page by using Copy/Paste.
  4. Attempt to edit the webpage again after saving. ...

That is what I did to get this to happen back in May. I upgraded to version 2.3 in August, and still have the same error:

mixpost-mysql-1 | 2024-08-27T16:12:51.578596Z 78 [ERROR] [MY-013131] [Server] Out of sort memory, consider increasing server sort buffer size!

I assume this is a setting I need to change in the MySQL container.

image

That's a shot of my home page. I pasted a few photos and saved. And that's the last thing I did before getting this error.

MySql Docker logs

txtechnician-server@tx-suse-server:~> docker logs mixpost-mysql-1 
[Entrypoint] MySQL Docker Image 8.0.32-1.2.11-server
[Entrypoint] Starting MySQL 8.0.32-1.2.11-server
2024-08-27T15:59:42.384511Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2024-08-27T15:59:42.385482Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 1
2024-08-27T15:59:42.395689Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-08-27T15:59:42.666749Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-08-27T15:59:43.016769Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-08-27T15:59:43.016807Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-08-27T15:59:43.056612Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-08-27T15:59:43.056700Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2024-08-27T16:00:37.018243Z 14 [ERROR] [MY-013131] [Server] Out of sort memory, consider increasing server sort buffer size!
2024-08-27T16:01:09.002683Z 23 [ERROR] [MY-013131] [Server] Out of sort memory, consider increasing server sort buffer size!
2024-08-27T16:02:48.083543Z 34 [ERROR] [MY-013131] [Server] Out of sort memory, consider increasing server sort buffer size!
2024-08-27T16:12:51.578596Z 78 [ERROR] [MY-013131] [Server] Out of sort memory, consider increasing server sort buffer size!
2024-08-27T16:17:40.202823Z 108 [ERROR] [MY-013131] [Server] Out of sort memory, consider increasing server sort buffer size!
[Entrypoint] MySQL Docker Image 8.0.32-1.2.11-server
[Entrypoint] Starting MySQL 8.0.32-1.2.11-server
2024-09-01T19:43:14.455381Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2024-09-01T19:43:14.458178Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 1
2024-09-01T19:43:14.486732Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-09-01T19:43:15.320415Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-09-01T19:43:15.845479Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2024-09-01T19:43:15.849797Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2024-09-01T19:43:15.896844Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-09-01T19:43:15.896882Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-09-01T19:43:15.931263Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-09-01T19:43:15.931771Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2024-09-04T19:17:10.374864Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.32).
2024-09-04T19:17:13.993354Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32)  MySQL Community Server - GPL.
[Entrypoint] MySQL Docker Image 8.0.32-1.2.11-server
[Entrypoint] Starting MySQL 8.0.32-1.2.11-server
2024-09-04T19:17:26.167911Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2024-09-04T19:17:26.170201Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 1
2024-09-04T19:17:26.178855Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-09-04T19:17:26.319742Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-09-04T19:17:26.605143Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-09-04T19:17:26.605186Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-09-04T19:17:26.635014Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-09-04T19:17:26.635126Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2024-09-05T20:17:42.586270Z 6002 [ERROR] [MY-013131] [Server] Out of sort memory, consider increasing server sort buffer size!
2024-09-05T20:31:44.472860Z 6061 [ERROR] [MY-013131] [Server] Out of sort memory, consider increasing server sort buffer size!
txtechnician-server@tx-suse-server:~>

Mixpost Info:

## Describe your issue

--- 
## Health

**Environment:**: production 
**Debug Mode**: Disabled 
**Horizon**: Active 
**Queue connection**: OK 
**Schedule**: Ran 0 minute(s) ago 

## Technical details:

**App directory**: /var/www/html 
**Upload Media Disk**: public 
**Log Channel**: mixpost 
**User agent**: Mozilla/5.0 (X11; Linux x86_64; rv:129.0) Gecko/20100101 Firefox/129.0 
**PHP**: 8.2.18 
**Laravel**: 10.48.20 
**Horizon**: 5.28.0.0 
**Mixpost**: 2.3.0.0

Expected behaviour

Should be able to edit a webpage/ create a page

Actual behaviour

Error when trying to edit or create a page

Detailed description

No response

Specifications

Describe your issue

Health

Environment:: production Debug Mode: Disabled Horizon: Active Queue connection: OK Schedule: Ran 0 minute(s) ago

Technical details:

App directory: /var/www/html Upload Media Disk: public Log Channel: mixpost User agent: Mozilla/5.0 (X11; Linux x86_64; rv:129.0) Gecko/20100101 Firefox/129.0 PHP: 8.2.18 Laravel: 10.48.20 Horizon: 5.28.0.0 Mixpost: 2.3.0.0

TxTechnician commented 2 months ago

Increasing the sort_buffer_size solved the problem.

Here is the SO question that lead me to the answer: Stack Overflow

I increased the size using a command in docker compose:

mysql:
        image: 'mysql/mysql-server:8.0'
        ports:
            - '3306:3306'
        command: --sort_buffer_size=256M

I'll might write a my.cnf with the updated params.