Plotselinge MySQL-crash zonder duidelijke oorzaak

Pagina: 1
Acties:

Vraag


Acties:
  • 0 Henk 'm!

  • vmsw
  • Registratie: Juli 2006
  • Laatst online: 24-02 19:47
Mijn vraag
Vanwaar is mijn MySQL-server gecrashed en hoe kan ik dit voorkómen in toekomst?

Relevante software en hardware die ik gebruik
Ubuntu 18.04 LTS
MySQL 5.7.33

Wat ik al gevonden of geprobeerd heb:

Logboeken bekijken en op basis van foutmelding zoeken naar mogelijke oorzaak. Ik kom geen mensen met gelijke bug tegen die hiermee te maken hebben. Wel dingen met apparmor uiteraard, maar niet vergelijkbaar aan deze, waardoor ik nog geen relatie tot een potentiëel probleem kan vinden. Ik weet niet waar te beginnen/zoeken.

MySQL is al enkele maanden niet bijgewerkt en er zijn geen aanwijzingen in monitoring dat er piekbelasting was (cpu, hvh connecties, netwerkverkeer etc.) of geheugentekort optrad. Het was lijkt "opeens" te zijn. De andere 3 mysql-servers hebben dit ook nog nooit gehad.

Overigens herstartte hij zichzelf en sindsdien werkt alles weer. Paar corrupte databasetabellen die hijzelf wist te repareren.

syslog:
code:
1
2
3
4
5
6
7
8
Apr  1 12:05:01 db13 kernel: [7793383.775592] audit: type=1400 audit(1617271501.564:14): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/21384/task/28026/mem" pid=21384 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=1005 ouid=1005
Apr  1 12:05:04 db13 systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Apr  1 12:05:04 db13 systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr  1 12:05:04 db13 systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Apr  1 12:05:04 db13 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 1.
Apr  1 12:05:04 db13 systemd[1]: Stopped MySQL Community Server.
Apr  1 12:05:04 db13 systemd[1]: Starting MySQL Community Server...
Apr  1 12:05:31 db13 systemd[1]: Started MySQL Community Server.


code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
10:05:01 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=171
max_threads=250
thread_count=116
connection_count=115
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 115729 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f4cec5be1e0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f4e52315e50 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xeb8fdb]
/usr/sbin/mysqld(handle_fatal_signal+0x377)[0x77bb47]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12980)[0x7f53aa1d0980]
/usr/sbin/mysqld(my_hash_sort_bin+0x20)[0x1290350]
/usr/sbin/mysqld[0xe987ed]
/usr/sbin/mysqld(my_hash_insert+0x198)[0xe98fa8]
/usr/sbin/mysqld(_ZN11Query_cache11store_queryEP3THDP10TABLE_LIST+0x400)[0xc1f6c0]
/usr/sbin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x1f9)[0xcaca29]
/usr/sbin/mysqld[0x746966]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x48ca)[0xc6d23a]
/usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3a5)[0xc6f8a5]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xaf6)[0xc70456]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x220)[0xc71f40]
/usr/sbin/mysqld(handle_connection+0x298)[0xd37c18]
/usr/sbin/mysqld(pfs_spawn_thread+0x154)[0x121e9d4]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7f53aa1c56db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f53a95af71f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f4cec024650): is an invalid pointer
Connection ID (thread ID): 77698920
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2021-04-01T10:05:04.973902Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 10240)
2021-04-01T10:05:04.974266Z 0 [Warning] Changed limits: table_open_cache: 2370 (requested 4000)
2021-04-01T10:05:05.200035Z 0 [Warning] option 'slow_query_log': boolean value '/var/log/mysql/mysql-slow.log' wasn't recognized. Set to OFF.
2021-04-01T10:05:05.200084Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-04-01T10:05:05.200120Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-04-01T10:05:05.202734Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.33-0ubuntu0.18.04.1-log) starting as process 30754 ...
2021-04-01T10:05:05.208780Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-04-01T10:05:05.208805Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-04-01T10:05:05.208811Z 0 [Note] InnoDB: Uses event mutexes
2021-04-01T10:05:05.208817Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-04-01T10:05:05.208822Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-04-01T10:05:05.208831Z 0 [Note] InnoDB: Using Linux native AIO
2021-04-01T10:05:05.209853Z 0 [Note] InnoDB: Number of pools: 1
2021-04-01T10:05:05.210245Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2021-04-01T10:05:05.212984Z 0 [Note] InnoDB: Initializing buffer pool, total size = 20G, instances = 20, chunk size = 128M
2021-04-01T10:05:07.145024Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-04-01T10:05:07.469471Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-04-01T10:05:07.484263Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-04-01T10:05:07.693907Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 945919095417
2021-04-01T10:05:08.127402Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 945924338176
2021-04-01T10:05:08.236873Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 945929581056
2021-04-01T10:05:08.338605Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 945934823936
2021-04-01T10:05:08.430227Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 945940066816
2021-04-01T10:05:08.488032Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 945942154567
2021-04-01T10:05:08.488697Z 0 [Note] InnoDB: Database was not shutdown normally!
2021-04-01T10:05:08.488710Z 0 [Note] InnoDB: Starting crash recovery.
2021-04-01T10:05:08.677988Z 0 [Note] InnoDB: Transaction 8061128555 was in the XA prepared state.
2021-04-01T10:05:08.684340Z 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 0 row operations to undo
2021-04-01T10:05:08.684357Z 0 [Note] InnoDB: Trx id counter is 8061128960
2021-04-01T10:05:08.684496Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2021-04-01T10:05:10.456359Z 0 [Note] InnoDB: Apply batch completed
2021-04-01T10:05:10.456417Z 0 [Note] InnoDB: Last MySQL binlog file position 0 46922584, file name mysql-bin.012671
2021-04-01T10:05:31.006475Z 0 [Note] InnoDB: Starting in background the rollback of uncommitted transactions
2021-04-01T10:05:31.006524Z 0 [Note] InnoDB: Rollback of non-prepared transactions completed
2021-04-01T10:05:31.006871Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-04-01T10:05:31.006902Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-04-01T10:05:31.006963Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-04-01T10:05:31.012547Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-04-01T10:05:31.016397Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-04-01T10:05:31.016425Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-04-01T10:05:31.016798Z 0 [Note] InnoDB: Waiting for purge to start
2021-04-01T10:05:31.066927Z 0 [Note] InnoDB: 5.7.33 started; log sequence number 945942154567
2021-04-01T10:05:31.066952Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 23598ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2021-04-01T10:05:31.067228Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysql/mysql/ib_buffer_pool
2021-04-01T10:05:31.069429Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-04-01T10:05:31.086995Z 0 [Note] Recovering after a crash using /mysqllog/mysql-bin
2021-04-01T10:05:31.234381Z 0 [Note] Starting crash recovery...
2021-04-01T10:05:31.234442Z 0 [Note] InnoDB: Starting recovery for XA transactions...
2021-04-01T10:05:31.234451Z 0 [Note] InnoDB: Transaction 8061128555 in prepared state after recovery
2021-04-01T10:05:31.234457Z 0 [Note] InnoDB: Transaction contains changes to 1 rows
2021-04-01T10:05:31.234463Z 0 [Note] InnoDB: 1 transactions in prepared state after recovery
2021-04-01T10:05:31.234468Z 0 [Note] Found 1 prepared transaction(s) in InnoDB
2021-04-01T10:05:31.235026Z 0 [Note] Crash recovery finished.
2021-04-01T10:05:31.321941Z 0 [Note] Skipping generation of SSL certificates as options related to SSL are specified.
2021-04-01T10:05:31.324198Z 0 [Warning] CA certificate /etc/mysql/cacert.pem is self signed.
2021-04-01T10:05:31.324673Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-04-01T10:05:31.325132Z 0 [Note] Server hostname (bind-address): '192.168.0.97'; port: 3306
2021-04-01T10:05:31.325145Z 0 [Note]   - '192.168.0.97' resolves to '192.168.0.97';
2021-04-01T10:05:31.325180Z 0 [Note] Server socket created on IP: '192.168.0.97'.
2021-04-01T10:05:31.345678Z 0 [Note] Failed to start slave threads for channel ''
2021-04-01T10:05:31.372580Z 0 [Note] Event Scheduler: Loaded 3 events
2021-04-01T10:05:31.372769Z 1 [Note] Event Scheduler: scheduler thread started with id 1
2021-04-01T10:05:31.372944Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.33-0ubuntu0.18.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2021-04-01T10:05:35.809918Z 279 [ERROR] /usr/sbin/mysqld: Table './dbnaam/data_print_cue' is marked as crashed and should be repaired
2021-04-01T10:05:35.810121Z 279 [Warning] Checking table:   './dbnaam/data_print_cue'
... diverse kapotte tabellen herstellen ...


Overigens zie ik het pad niet staan in apparmor-configuratie, geen idee of dit wel bedoeling zou zijn, maar gekke is dan 'waarom' dit plots nodig is na jarenlang geen problemen en al paar maanden geen mysql-udpate (?).

/etc/apparmor.d/usr.sbin.mysqld
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
# vim:syntax=apparmor
# Last Modified: Tue Feb 09 15:28:30 2016
#include <tunables/global>

/usr/sbin/mysqld {
  #include <abstractions/base>
  #include <abstractions/nameservice>
  #include <abstractions/user-tmp>
  #include <abstractions/mysql>
  #include <abstractions/winbind>

# Allow system resource access
  /proc/*/status r,
  /sys/devices/system/cpu/ r,
  /sys/devices/system/node/ r,
  /sys/devices/system/node/** r,
  capability sys_resource,
  capability dac_override,
  capability dac_read_search,
  capability setuid,
  capability setgid,

# Allow network access
  network tcp,

  /etc/hosts.allow r,
  /etc/hosts.deny r,

# Allow config access
  /etc/mysql/** r,

# Allow pid, socket, socket lock file access
  /var/run/mysqld/mysqld.pid rw,
  /var/run/mysqld/mysqld.sock rw,
  /var/run/mysqld/mysqld.sock.lock rw,
  /run/mysqld/mysqld.pid rw,
  /run/mysqld/mysqld.sock rw,
  /run/mysqld/mysqld.sock.lock rw,

# Allow systemd notify messages
  /{,var/}run/systemd/notify w,

# Allow execution of server binary
  /usr/sbin/mysqld mr,
  /usr/sbin/mysqld-debug mr,

# Allow plugin access
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,

# Allow error msg and charset access
  /usr/share/mysql/ r,
  /usr/share/mysql/** r,

# Allow data dir access
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,

# Allow data files dir access
  /var/lib/mysql-files/ r,
  /var/lib/mysql-files/** rwk,

# Allow keyring dir access
  /var/lib/mysql-keyring/ r,
  /var/lib/mysql-keyring/** rwk,

# Allow log file access
  /var/log/mysql.err rw,
  /var/log/mysql.log rw,
  /var/log/mysql/ r,
  /var/log/mysql/** rw,

# Allow read access to OpenSSL config
  /etc/ssl/openssl.cnf r,
  # Site-specific additions and overrides. See local/README for details.
  #include <local/usr.sbin.mysqld>
}

[ Voor 18% gewijzigd door vmsw op 01-04-2021 15:44 ]

Alle reacties


Acties:
  • 0 Henk 'm!

  • vmsw
  • Registratie: Juli 2006
  • Laatst online: 24-02 19:47
Helaas herhaling zojuist. Heeft langere tijd geduurd, maar blijkbaar toch iets dat terugkomt.
Iemand enig idee waar/hoe ik kan gaan zoeken?

inkomend/uitgaand netwerkverkeer, cpu, geheugen, swap lijkt rustig/ruim beschikbaar. Hoeveelheid openstaande connecties e.d. ook. Ik ben niet bekend met vreemd gedrag op het moment.

Helaas kan ik op google geen vergelijkbaar incident terugvinden hierover.

In syslog:
code:
1
May 22 10:05:07 db13 kernel: [4174276.829568] audit: type=1400 audit(1621670707.829:12): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/2031/task/29487/mem" pid=2031 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=1005 ouid=1005


MySQL error log:
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
08:05:07 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=182
max_threads=250
thread_count=106
connection_count=105
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 115729 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fd2655b2bf0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fd2f6006e50 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xeb8fdb]
/usr/sbin/mysqld(handle_fatal_signal+0x377)[0x77bb47]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12980)[0x7fd84fba9980]
/usr/sbin/mysqld(_ZN11Query_cache14free_old_queryEv+0x41)[0xc1c081]
/usr/sbin/mysqld(_ZN11Query_cache14allocate_blockEmcm+0x40)[0xc1c120]
/usr/sbin/mysqld(_ZN11Query_cache19allocate_data_chainEPP17Query_cache_blockmS1_c+0x96)[0xc1d936]
/usr/sbin/mysqld(_ZN11Query_cache17write_result_dataEPP17Query_cache_blockmPhS1_NS0_10block_typeE+0x30)[0xc1da10]
/usr/sbin/mysqld(_ZN11Query_cache18append_result_dataEPP17Query_cache_blockmPhS1_+0x15e)[0xc1dbee]
/usr/sbin/mysqld(_ZN11Query_cache6insertEP15Query_cache_tlsPKcmj+0x10c)[0xc1dd0c]
/usr/sbin/mysqld(net_write_packet+0x3e)[0xbbecbe]
/usr/sbin/mysqld(net_flush+0x23)[0xbbeef3]
/usr/sbin/mysqld(_Z12net_send_eofP3THDjj+0x125)[0xbcd885]
/usr/sbin/mysqld(_ZN3THD21send_statement_statusEv+0x9c)[0xc289ec]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x338)[0xc6fc98]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x220)[0xc71f40]
/usr/sbin/mysqld(handle_connection+0x298)[0xd37c18]
/usr/sbin/mysqld(pfs_spawn_thread+0x154)[0x121e9d4]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7fd84fb9e6db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fd84ef8871f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fd264d9c350): is an invalid pointer
Connection ID (thread ID): 73291354
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2021-05-22T08:05:12.206273Z 0 [Warning] option 'slow_query_log': boolean value '/var/log/mysql/mysql-slow.log' wasn't recognized. Set to OFF.
2021-05-22T08:05:12.206441Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-22T08:05:12.206461Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-05-22T08:05:12.208111Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.33-0ubuntu0.18.04.1-log) starting as process 32527 ...
2021-05-22T08:05:12.213310Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-05-22T08:05:12.213337Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-05-22T08:05:12.213342Z 0 [Note] InnoDB: Uses event mutexes
2021-05-22T08:05:12.213346Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-05-22T08:05:12.213351Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-05-22T08:05:12.213357Z 0 [Note] InnoDB: Using Linux native AIO
2021-05-22T08:05:12.214178Z 0 [Note] InnoDB: Number of pools: 1
2021-05-22T08:05:12.214529Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2021-05-22T08:05:12.216493Z 0 [Note] InnoDB: Initializing buffer pool, total size = 20G, instances = 20, chunk size = 128M
2021-05-22T08:05:13.628535Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-05-22T08:05:13.892126Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-05-22T08:05:13.906937Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-05-22T08:05:14.077608Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 996136925766
2021-05-22T08:05:14.583653Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 996142168576
2021-05-22T08:05:14.730197Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 996147411456
2021-05-22T08:05:14.870901Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 996152654336
2021-05-22T08:05:15.055917Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 996157897216
2021-05-22T08:05:15.140093Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 996160862555
2021-05-22T08:05:15.140411Z 0 [Note] InnoDB: Database was not shutdown normally!
2021-05-22T08:05:15.140417Z 0 [Note] InnoDB: Starting crash recovery.
2021-05-22T08:05:15.359221Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2021-05-22T08:05:17.161190Z 0 [Note] InnoDB: Apply batch completed
2021-05-22T08:05:17.161243Z 0 [Note] InnoDB: Last MySQL binlog file position 0 30299371, file name mysql-bin.013194
2021-05-22T08:05:38.643871Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-05-22T08:05:38.643916Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-05-22T08:05:38.643983Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-05-22T08:05:38.649816Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-05-22T08:05:38.655336Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-05-22T08:05:38.655360Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-05-22T08:05:38.655994Z 0 [Note] InnoDB: Waiting for purge to start
2021-05-22T08:05:38.706129Z 0 [Note] InnoDB: 5.7.33 started; log sequence number 996160862555
2021-05-22T08:05:38.706161Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 24814ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2021-05-22T08:05:38.706457Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysql/mysql/ib_buffer_pool
2021-05-22T08:05:38.707105Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-05-22T08:05:38.715124Z 0 [Note] Recovering after a crash using /mysqllog/mysql-bin
2021-05-22T08:05:38.819504Z 0 [Note] Starting crash recovery...
2021-05-22T08:05:38.819590Z 0 [Note] Crash recovery finished.
2021-05-22T08:05:38.869929Z 0 [Note] Skipping generation of SSL certificates as options related to SSL are specified.
2021-05-22T08:05:38.871501Z 0 [Warning] CA certificate /etc/mysql/cacert.pem is self signed.
2021-05-22T08:05:38.871552Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-05-22T08:05:38.871993Z 0 [Note] Server hostname (bind-address): '192.168.0.97'; port: 3306
2021-05-22T08:05:38.872006Z 0 [Note]   - '192.168.0.97' resolves to '192.168.0.97';
2021-05-22T08:05:38.872043Z 0 [Note] Server socket created on IP: '192.168.0.97'.
2021-05-22T08:05:38.891909Z 0 [Note] Failed to start slave threads for channel ''
2021-05-22T08:05:38.918294Z 0 [Note] Event Scheduler: Loaded 3 events
2021-05-22T08:05:38.918541Z 1 [Note] Event Scheduler: scheduler thread started with id 1
2021-05-22T08:05:38.918608Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.33-0ubuntu0.18.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

(en dan nog wat tabellen-databases die hersteld moeten worden)

Acties:
  • +1 Henk 'm!

  • Cyphax
  • Registratie: November 2000
  • Laatst online: 06:08

Cyphax

Moderator LNX
De stacktrace van de tweede crash is wel heel anders dan de eerste... dat is vreemd. Bij de eerste lijkt ie te crashen in
code:
1
my_hash_sort_bin
en de tweede keer in
code:
1
free_old_query
na wat allocates... ik weet niet of die zaken met elkaar te maken hebben, lijken me heel andere functies. Ik vraag me af of dit een softwareprobleem is.
Je zou het geheugen van die machine kunnen doortesten met memtest ofzo, misschien is daar iets niet goed? Als het een bug in MySQL zou zijn is dat uit die stacktrace niet echt op te maken en dan had je ook wel wat gevonden. Wat lastig. :(

Saved by the buoyancy of citrus


  • vmsw
  • Registratie: Juli 2006
  • Laatst online: 24-02 19:47
Opvolging heeft even geduurd. Het blijft een bijzonder probleem. Want de MySQL-servers die er eerst last van hadden (DB06 en DB13) hebben dit inmiddels niet meer. Deze staan overigens op 2 verschillende fysieke hosts. Oorzaak en waarom dit opgelost is, is mij helaas niet bekend, zover ik weet heb ik ook niks gewijzigd/gedaan, hoogstens wat databases gemigreerd, maar dit geldt dan uitsluitend voor db06, want db13 is al lange tijd stabiel qua inhoud... erg frustrerend dus.

Spijtig genoeg heeft een nieuwe server, DB22, een vergelijkbaar probleem. Zeker nu hij wat intensiever gebruikt begint te worden, een crash iedere 1 à 2 dagen. Ik heb hem reeds van fysieke host verplaatst, zonder positieve gevolgen helaas.
Het is een schoon geïnstalleerde server, Ubuntu 18.04.5 en MySQL 5.7.35 op het moment.

Hieronder 4 willekeurige crashes. Het is er gemiddeld 1 per dag, doorgaans 's nachts. 's Nachts lopen er vrijwel uitsluitend geplande taken (complexe synchronisaties in MySQL, voornamelijk veel SELECT-queries)

code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
00:26:00 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=57
max_threads=151
thread_count=27
connection_count=26
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 76389 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fa72c4d5700
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fa723779e50 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xee8b5b]
/usr/sbin/mysqld(handle_fatal_signal+0x377)[0x777357]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12980)[0x7fa76e642980]
/usr/sbin/mysqld(_ZN11Query_cache26double_linked_list_excludeEP17Query_cache_blockPS1_+0x0)[0xc49b40]
/usr/sbin/mysqld(_ZN11Query_cache12unlink_tableEP23Query_cache_block_table+0x57)[0xc4a067]
/usr/sbin/mysqld(_ZN11Query_cache19free_query_internalEP17Query_cache_block+0x76)[0xc4a106]
/usr/sbin/mysqld(_ZN11Query_cache14free_old_queryEv+0x7b)[0xc4a4cb]
/usr/sbin/mysqld(_ZN11Query_cache14allocate_blockEmcm+0x40)[0xc4a530]
/usr/sbin/mysqld(_ZN11Query_cache19allocate_data_chainEPP17Query_cache_blockmS1_c+0x96)[0xc4bd46]
/usr/sbin/mysqld(_ZN11Query_cache17write_result_dataEPP17Query_cache_blockmPhS1_NS0_10block_typeE+0x30)[0xc4be20]
/usr/sbin/mysqld(_ZN11Query_cache18append_result_dataEPP17Query_cache_blockmPhS1_+0xd3)[0xc4bf73]
/usr/sbin/mysqld(_ZN11Query_cache6insertEP15Query_cache_tlsPKcmj+0x10c)[0xc4c11c]
/usr/sbin/mysqld(net_write_packet+0x3e)[0xbec6be]
/usr/sbin/mysqld[0xbec984]
/usr/sbin/mysqld(my_net_write+0x2de)[0xbecd2e]
/usr/sbin/mysqld(_ZN16Protocol_classic7end_rowEv+0x5b)[0xbf978b]
/usr/sbin/mysqld[0xc6d1a0]
/usr/sbin/mysqld[0xc69520]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x1b5)[0xc6efb5]
/usr/sbin/mysqld[0xc69520]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x1b5)[0xc6efb5]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x3a0)[0xc677e0]
/usr/sbin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x23a)[0xcdacaa]
/usr/sbin/mysqld[0x74215c]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x461f)[0xc9b7ef]
/usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3bd)[0xc9dbfd]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xb26)[0xc9e7e6]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x220)[0xca02d0]
/usr/sbin/mysqld(handle_connection+0x298)[0xd66ff8]
/usr/sbin/mysqld(pfs_spawn_thread+0x154)[0x124e504]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7fa76e6376db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fa76da2171f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fa72cde8190): is an invalid pointer
Connection ID (thread ID): 457499
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2021-09-30T00:26:00.555853Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 10240)
2021-09-30T00:26:00.932531Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-30T00:26:00.932564Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-09-30T00:26:00.934263Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.35-0ubuntu0.18.04.1-log) starting as process 23972 ...
2021-09-30T00:26:00.938559Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-09-30T00:26:00.938582Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-30T00:26:00.938588Z 0 [Note] InnoDB: Uses event mutexes
2021-09-30T00:26:00.938594Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-09-30T00:26:00.938599Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-09-30T00:26:00.938646Z 0 [Note] InnoDB: Using Linux native AIO
2021-09-30T00:26:00.938994Z 0 [Note] InnoDB: Number of pools: 1
2021-09-30T00:26:00.939121Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2021-09-30T00:26:00.941071Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-09-30T00:26:00.949834Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-09-30T00:26:00.952228Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-09-30T00:26:00.965737Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-30T00:26:00.967476Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 52225682701
2021-09-30T00:26:00.971735Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 52225815258
2021-09-30T00:26:00.971901Z 0 [Note] InnoDB: Database was not shutdown normally!
2021-09-30T00:26:00.971907Z 0 [Note] InnoDB: Starting crash recovery.
2021-09-30T00:26:00.997865Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2021-09-30T00:26:01.503659Z 0 [Note] InnoDB: Apply batch completed
2021-09-30T00:26:01.503685Z 0 [Note] InnoDB: Last MySQL binlog file position 0 95249320, file name mysql-bin.000423
2021-09-30T00:26:03.330576Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-09-30T00:26:03.330605Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-09-30T00:26:03.330653Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-09-30T00:26:03.343990Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-09-30T00:26:03.345410Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-09-30T00:26:03.345425Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-09-30T00:26:03.346016Z 0 [Note] InnoDB: 5.7.35 started; log sequence number 52225815258
2021-09-30T00:26:03.346264Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-09-30T00:26:03.351104Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-09-30T00:26:03.364895Z 0 [Note] Recovering after a crash using /var/lib/mysql/mysql-bin
2021-09-30T00:26:03.515213Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210930  2:26:03
2021-09-30T00:26:03.730859Z 0 [Note] Starting crash recovery...
2021-09-30T00:26:03.731013Z 0 [Note] Crash recovery finished.
2021-09-30T00:26:03.900620Z 0 [Note] Skipping generation of SSL certificates as options related to SSL are specified.
2021-09-30T00:26:03.900641Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-09-30T00:26:03.900646Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-09-30T00:26:03.901353Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed
2021-09-30T00:26:03.901379Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-09-30T00:26:03.902145Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2021-09-30T00:26:03.902168Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2021-09-30T00:26:03.902200Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2021-09-30T00:26:03.908143Z 0 [Note] Failed to start slave threads for channel ''
2021-09-30T00:26:03.914090Z 0 [Note] Event Scheduler: Loaded 2 events
2021-09-30T00:26:03.914318Z 1 [Note] Event Scheduler: scheduler thread started with id 1
2021-09-30T00:26:03.914462Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.35-0ubuntu0.18.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2021-09-30T00:26:07.424287Z 36 [Note] Start binlog_dump to master_thread_id(36) slave_server(23), pos(mysql-bin.000423, 95249320)


code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
06:29:22 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=42
max_threads=151
thread_count=35
connection_count=34
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 76389 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f84ac47bbe0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f852e3a7e50 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xee8b5b]
/usr/sbin/mysqld(handle_fatal_signal+0x377)[0x777357]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12980)[0x7f8568f82980]
/usr/sbin/mysqld(my_hash_sort_bin+0x20)[0x12bfe70]
/usr/sbin/mysqld[0xec836d]
/usr/sbin/mysqld(my_hash_first_from_hash_value+0x13f)[0xec869f]
/usr/sbin/mysqld(my_hash_search+0x20)[0xec87d0]
/usr/sbin/mysqld(_ZN11Query_cache21send_result_to_clientEP3THDRK25st_mysql_const_lex_string+0x33f)[0xc4b50f]
/usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x6e)[0xc9d8ae]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xb26)[0xc9e7e6]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x220)[0xca02d0]
/usr/sbin/mysqld(handle_connection+0x298)[0xd66ff8]
/usr/sbin/mysqld(pfs_spawn_thread+0x154)[0x124e504]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7f8568f776db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f856836171f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f84ac7e68f0): is an invalid pointer
Connection ID (thread ID): 3964
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2021-09-28T06:29:23.386273Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 10240)
2021-09-28T06:29:23.607182Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-28T06:29:23.607247Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-09-28T06:29:23.609761Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.35-0ubuntu0.18.04.1-log) starting as process 15667 ...
2021-09-28T06:29:23.614940Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-09-28T06:29:23.614974Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-28T06:29:23.614981Z 0 [Note] InnoDB: Uses event mutexes
2021-09-28T06:29:23.614987Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-09-28T06:29:23.614994Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-09-28T06:29:23.615011Z 0 [Note] InnoDB: Using Linux native AIO
2021-09-28T06:29:23.615440Z 0 [Note] InnoDB: Number of pools: 1
2021-09-28T06:29:23.615636Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2021-09-28T06:29:23.618039Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-09-28T06:29:23.628204Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-09-28T06:29:23.630941Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-09-28T06:29:23.648571Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-28T06:29:23.650543Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 50772386969
2021-09-28T06:29:23.650562Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 50772388090
2021-09-28T06:29:23.651544Z 0 [Note] InnoDB: Database was not shutdown normally!
2021-09-28T06:29:23.651556Z 0 [Note] InnoDB: Starting crash recovery.
2021-09-28T06:29:23.733892Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2021-09-28T06:29:24.238560Z 0 [Note] InnoDB: Apply batch completed
2021-09-28T06:29:24.238603Z 0 [Note] InnoDB: Last MySQL binlog file position 0 10993577, file name mysql-bin.000414
2021-09-28T06:29:25.312685Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-09-28T06:29:25.312725Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-09-28T06:29:25.312798Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-09-28T06:29:25.324322Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-09-28T06:29:25.326051Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-09-28T06:29:25.326069Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-09-28T06:29:25.326470Z 0 [Note] InnoDB: Waiting for purge to start
2021-09-28T06:29:25.376993Z 0 [Note] InnoDB: 5.7.35 started; log sequence number 50772388090
2021-09-28T06:29:25.377354Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-09-28T06:29:25.377843Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-09-28T06:29:25.385173Z 0 [Note] Recovering after a crash using /var/lib/mysql/mysql-bin
2021-09-28T06:29:25.417745Z 0 [Note] Starting crash recovery...
2021-09-28T06:29:25.417835Z 0 [Note] Crash recovery finished.
2021-09-28T06:29:25.455071Z 0 [Note] Skipping generation of SSL certificates as options related to SSL are specified.
2021-09-28T06:29:25.455100Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-09-28T06:29:25.455105Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-09-28T06:29:25.470220Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed
2021-09-28T06:29:25.470273Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-09-28T06:29:25.470573Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2021-09-28T06:29:25.470590Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2021-09-28T06:29:25.470630Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2021-09-28T06:29:25.485049Z 0 [Note] Failed to start slave threads for channel ''
2021-09-28T06:29:25.492273Z 0 [Note] Event Scheduler: Loaded 2 events
2021-09-28T06:29:25.492674Z 1 [Note] Event Scheduler: scheduler thread started with id 1
2021-09-28T06:29:25.492765Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.35-0ubuntu0.18.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2021-09-28T06:29:26.057462Z 9 [Note] Start binlog_dump to master_thread_id(9) slave_server(23), pos(mysql-bin.000414, 10993577)
2021-09-28T06:29:26.727607Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210928  8:29:26


code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
16:21:45 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=56
max_threads=151
thread_count=32
connection_count=31
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 76389 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f183873afb0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f191b5fbe50 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xee8b5b]
/usr/sbin/mysqld(handle_fatal_signal+0x377)[0x777357]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12980)[0x7f195978c980]
/usr/sbin/mysqld(my_hash_insert+0x3cb)[0xec8d5b]
/usr/sbin/mysqld(_ZN11Query_cache12insert_tableEmPKcP23Query_cache_block_tablemhPFcP3THDPcjPyEy+0x141)[0xc4af11]
/usr/sbin/mysqld(_ZN11Query_cache25register_tables_from_listEP10TABLE_LISTmP23Query_cache_block_table+0x136)[0xc4d506]
/usr/sbin/mysqld(_ZN11Query_cache19register_all_tablesEP17Query_cache_blockP10TABLE_LISTm+0x15)[0xc4d6c5]
/usr/sbin/mysqld(_ZN11Query_cache11store_queryEP3THDP10TABLE_LIST+0x445)[0xc4db15]
/usr/sbin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x1f9)[0xcdac69]
/usr/sbin/mysqld[0x74215c]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x461f)[0xc9b7ef]
/usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3bd)[0xc9dbfd]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xb26)[0xc9e7e6]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x220)[0xca02d0]
/usr/sbin/mysqld(handle_connection+0x298)[0xd66ff8]
/usr/sbin/mysqld(pfs_spawn_thread+0x154)[0x124e504]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7f19597816db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f1958b6b71f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f1838946850): is an invalid pointer
Connection ID (thread ID): 184115
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2021-09-27T16:21:45.631666Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 10240)
2021-09-27T16:21:45.844782Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-27T16:21:45.844821Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-09-27T16:21:45.846805Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.35-0ubuntu0.18.04.1-log) starting as process 20974 ...
2021-09-27T16:21:45.851713Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-09-27T16:21:45.851741Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-27T16:21:45.851757Z 0 [Note] InnoDB: Uses event mutexes
2021-09-27T16:21:45.851764Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-09-27T16:21:45.851769Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-09-27T16:21:45.851781Z 0 [Note] InnoDB: Using Linux native AIO
2021-09-27T16:21:45.852163Z 0 [Note] InnoDB: Number of pools: 1
2021-09-27T16:21:45.852339Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2021-09-27T16:21:45.854779Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-09-27T16:21:45.865025Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-09-27T16:21:45.867524Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-09-27T16:21:45.885250Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-27T16:21:45.887241Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 50678698062
2021-09-27T16:21:45.887264Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 50678700952
2021-09-27T16:21:45.888708Z 0 [Note] InnoDB: Database was not shutdown normally!
2021-09-27T16:21:45.888721Z 0 [Note] InnoDB: Starting crash recovery.
2021-09-27T16:21:45.983049Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2021-09-27T16:21:46.489166Z 0 [Note] InnoDB: Apply batch completed
2021-09-27T16:21:46.489205Z 0 [Note] InnoDB: Last MySQL binlog file position 0 56275250, file name mysql-bin.000412
2021-09-27T16:21:47.619317Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-09-27T16:21:47.619350Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-09-27T16:21:47.619405Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-09-27T16:21:47.629646Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-09-27T16:21:47.631079Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-09-27T16:21:47.631098Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-09-27T16:21:47.631452Z 0 [Note] InnoDB: Waiting for purge to start
2021-09-27T16:21:47.682026Z 0 [Note] InnoDB: 5.7.35 started; log sequence number 50678700952
2021-09-27T16:21:47.682395Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-09-27T16:21:47.682760Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-09-27T16:21:47.689602Z 0 [Note] Recovering after a crash using /var/lib/mysql/mysql-bin
2021-09-27T16:21:47.843277Z 0 [Note] Starting crash recovery...
2021-09-27T16:21:47.843358Z 0 [Note] Crash recovery finished.
2021-09-27T16:21:47.951306Z 0 [Note] Skipping generation of SSL certificates as options related to SSL are specified.
2021-09-27T16:21:47.951334Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-09-27T16:21:47.951339Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-09-27T16:21:47.951809Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed
2021-09-27T16:21:47.951827Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-09-27T16:21:47.952047Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2021-09-27T16:21:47.952061Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2021-09-27T16:21:47.952094Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2021-09-27T16:21:47.963014Z 0 [Note] Failed to start slave threads for channel ''
2021-09-27T16:21:47.969861Z 0 [Note] Event Scheduler: Loaded 2 events
2021-09-27T16:21:47.970108Z 1 [Note] Event Scheduler: scheduler thread started with id 1
2021-09-27T16:21:47.970277Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.35-0ubuntu0.18.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2021-09-27T16:21:48.523223Z 4 [Note] Start binlog_dump to master_thread_id(4) slave_server(23), pos(mysql-bin.000412, 56275250)
2021-09-27T16:21:48.910326Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210927 18:21:48


code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
00:41:04 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=37
max_threads=151
thread_count=34
connection_count=33
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 76389 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fa18004b5d0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fa1b7e30e50 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xee8b5b]
/usr/sbin/mysqld(handle_fatal_signal+0x377)[0x777357]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12980)[0x7fa28ece0980]
/usr/sbin/mysqld(_ZN11Query_cache14free_old_queryEv+0x38)[0xc4a488]
/usr/sbin/mysqld(_ZN11Query_cache14allocate_blockEmcm+0x40)[0xc4a530]
/usr/sbin/mysqld(_ZN11Query_cache19allocate_data_chainEPP17Query_cache_blockmS1_c+0x96)[0xc4bd46]
/usr/sbin/mysqld(_ZN11Query_cache17write_result_dataEPP17Query_cache_blockmPhS1_NS0_10block_typeE+0x30)[0xc4be20]
/usr/sbin/mysqld(_ZN11Query_cache18append_result_dataEPP17Query_cache_blockmPhS1_+0x15e)[0xc4bffe]
/usr/sbin/mysqld(_ZN11Query_cache6insertEP15Query_cache_tlsPKcmj+0x10c)[0xc4c11c]
/usr/sbin/mysqld(net_write_packet+0x3e)[0xbec6be]
/usr/sbin/mysqld(net_flush+0x23)[0xbec8f3]
/usr/sbin/mysqld(_Z12net_send_eofP3THDjj+0x125)[0xbfb305]
/usr/sbin/mysqld(_ZN3THD21send_statement_statusEv+0x9c)[0xc5710c]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x338)[0xc9dff8]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x220)[0xca02d0]
/usr/sbin/mysqld(handle_connection+0x298)[0xd66ff8]
/usr/sbin/mysqld(pfs_spawn_thread+0x154)[0x124e504]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7fa28ecd56db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fa28e0bf71f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fa180130a00): is an invalid pointer
Connection ID (thread ID): 73114
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2021-09-27T00:41:04.389746Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 10240)
2021-09-27T00:41:04.617850Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-27T00:41:04.617888Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-09-27T00:41:04.620350Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.35-0ubuntu0.18.04.1-log) starting as process 23419 ...
2021-09-27T00:41:04.626511Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-09-27T00:41:04.626549Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-27T00:41:04.626557Z 0 [Note] InnoDB: Uses event mutexes
2021-09-27T00:41:04.626563Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-09-27T00:41:04.626570Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-09-27T00:41:04.626588Z 0 [Note] InnoDB: Using Linux native AIO
2021-09-27T00:41:04.626986Z 0 [Note] InnoDB: Number of pools: 1
2021-09-27T00:41:04.627168Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2021-09-27T00:41:04.630244Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-09-27T00:41:04.641083Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-09-27T00:41:04.643810Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-09-27T00:41:04.661871Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-27T00:41:04.663909Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 50244250861
2021-09-27T00:41:04.663947Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 50244257206
2021-09-27T00:41:04.666768Z 0 [Note] InnoDB: Database was not shutdown normally!
2021-09-27T00:41:04.666785Z 0 [Note] InnoDB: Starting crash recovery.
2021-09-27T00:41:04.764149Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2021-09-27T00:41:05.271681Z 0 [Note] InnoDB: Apply batch completed
2021-09-27T00:41:05.271719Z 0 [Note] InnoDB: Last MySQL binlog file position 0 89026991, file name mysql-bin.000408
2021-09-27T00:41:06.351949Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-09-27T00:41:06.351987Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-09-27T00:41:06.352047Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-09-27T00:41:06.359535Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-09-27T00:41:06.361003Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-09-27T00:41:06.361020Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-09-27T00:41:06.361429Z 0 [Note] InnoDB: Waiting for purge to start
2021-09-27T00:41:06.411985Z 0 [Note] InnoDB: 5.7.35 started; log sequence number 50244257206
2021-09-27T00:41:06.412298Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-09-27T00:41:06.412651Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-09-27T00:41:06.419381Z 0 [Note] Recovering after a crash using /var/lib/mysql/mysql-bin
2021-09-27T00:41:06.785923Z 0 [Note] Starting crash recovery...
2021-09-27T00:41:06.786038Z 0 [Note] Crash recovery finished.
2021-09-27T00:41:06.896781Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210927  2:41:06
2021-09-27T00:41:07.022946Z 0 [Note] Skipping generation of SSL certificates as options related to SSL are specified.
2021-09-27T00:41:07.022975Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-09-27T00:41:07.022980Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-09-27T00:41:07.023471Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed
2021-09-27T00:41:07.023491Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-09-27T00:41:07.023715Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2021-09-27T00:41:07.023734Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2021-09-27T00:41:07.023772Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2021-09-27T00:41:07.035336Z 0 [Note] Failed to start slave threads for channel ''
2021-09-27T00:41:07.042349Z 0 [Note] Event Scheduler: Loaded 2 events
2021-09-27T00:41:07.042556Z 1 [Note] Event Scheduler: scheduler thread started with id 1
2021-09-27T00:41:07.042695Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.35-0ubuntu0.18.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2021-09-27T00:41:07.240887Z 4 [Note] Start binlog_dump to master_thread_id(4) slave_server(23), pos(mysql-bin.000408, 89026991)


In syslog staat er niet heel veel informatie, vannacht was dit bijv.
code:
1
2
3
4
5
6
7
8
Sep 30 00:26:00 db22 kernel: [13429633.343035] audit: type=1400 audit(1632961560.046:67): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/15667/task/10617/mem" pid=15667 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=110 ouid=110
Sep 30 00:26:00 db22 systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Sep 30 00:26:00 db22 systemd[1]: mysql.service: Failed with result 'exit-code'.
Sep 30 00:26:00 db22 systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Sep 30 00:26:00 db22 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 14.
Sep 30 00:26:00 db22 systemd[1]: Stopped MySQL Community Server.
Sep 30 00:26:00 db22 systemd[1]: Starting MySQL Community Server...
Sep 30 00:26:03 db22 systemd[1]: Started MySQL Community Server.


code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
### MANAGED BY PUPPET ###

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[isamchk]
key_buffer_size = 16M

[mysqld]
basedir = /usr
bind-address = 0.0.0.0
datadir = /var/lib/mysql
event_scheduler = 1
expire_logs_days = 10
group_concat_max_len = 102400
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
key_buffer_size = 16M
log-bin = /var/lib/mysql/mysql-bin
log-error = /var/log/mysql/error.log
log_bin_trust_function_creators = 1
long_query_time = 0
max_allowed_packet = 64M
max_binlog_size = 100M
max_connections = 151
open_files_limit = 10240
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
query_cache_type = 1
relay-log = /var/lib/mysql/mysqld-relay-bin
report-host = db22.lan01.troublefree.nl
server-id = 22
skip-external-locking
skip_name_resolve = 1
socket = /var/run/mysqld/mysqld.sock
sql_mode = NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ssl = false
ssl-ca = /etc/mysql/cacert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
sync_binlog = 1
tmpdir = /tmp
user = mysql

[mysqld-5.0]
myisam-recover = BACKUP

[mysqld-5.1]
myisam-recover = BACKUP

[mysqld-5.5]
myisam-recover = BACKUP

[mysqld-5.6]
myisam-recover-options = BACKUP

[mysqld-5.7]
myisam-recover-options = BACKUP

[mysqld_safe]
log-error = /var/log/mysql/error.log
nice = 0
socket = /var/run/mysqld/mysqld.sock

[mysqldump]
max_allowed_packet = 16M
quick
quote-names



!includedir /etc/mysql/conf.d

[ Voor 6% gewijzigd door vmsw op 30-09-2021 09:39 . Reden: extra info ]


Acties:
  • +1 Henk 'm!

  • Kees
  • Registratie: Juni 1999
  • Laatst online: 07-07 15:41

Kees

Serveradmin / BOFH / DoC
Ik zou de query cache uitzetten, die is niet perse nuttig, en zorgt hier wel voor de crashes.

"Een serveradmin, voluit een serveradministrator, is dan weer een slavenbeheerder oftewel een slavendrijver" - Rataplan


Acties:
  • +2 Henk 'm!

  • vmsw
  • Registratie: Juli 2006
  • Laatst online: 24-02 19:47
Vermoedelijk oorzaak gevonden! Heb even afgewacht, maar inmiddels al langere tijd geen fouten meer.
Bleek dat mijn configuratie geen query_cache_size ingesteld had staan op de server die zo vaak een crash had, waardoor hij op default kwam: 1048576 (1mb).

Opgehoogd naar iets zinnigs en sindsdien geen foutmeldingen meer. Mijn gok is dat MySQL instabiel wordt wanneer query_cache_limit en query_cache_size beiden op 1mb staan en er wellicht een soort concurrentie volgt zodra er teveel queries langs komen.

P.S. ik besef me dat query cache uitgefaseerd gaat/moet worden, enfin, voor nu rust de software erop dus vandaar het wel ingeschakeld is.

[ Voor 15% gewijzigd door vmsw op 11-10-2021 14:33 ]


Acties:
  • 0 Henk 'm!

  • HollowGamer
  • Registratie: Februari 2009
  • Niet online
vmsw schreef op maandag 11 oktober 2021 @ 14:17:
Vermoedelijk oorzaak gevonden! Heb even afgewacht, maar inmiddels al langere tijd geen fouten meer.
Bleek dat mijn configuratie geen query_cache_size ingesteld had staan op de server die zo vaak een crash had, waardoor hij op default kwam: 1048576 (1mb).

Opgehoogd naar iets zinnigs en sindsdien geen foutmeldingen meer. Mijn gok is dat MySQL instabiel wordt wanneer query_cache_limit en query_cache_size beiden op 1mb staan en er wellicht een soort concurrentie volgt zodra er teveel queries langs komen.

P.S. ik besef me dat query cache uitgefaseerd gaat/moet worden, enfin, voor nu rust de software erop dus vandaar het wel ingeschakeld is.
Ik was ook aan het kijken naar iets als query cache, maar MariaDB raad het sterk af inderdaad.

Je verhoogt nu de query cache, maar naar mijn weten kan je het toch gewoon helemaal uitzetten zonder problemen? Al zou ik sowieso voor configuratie changes een snapshot maken.

Mysqltuner is overigens wel handig, deze zou je ook wat tips kunnen geven over je configuratie.
Pagina: 1