Can't open the mysql.plugin table

[tomotom]

Nachdem main jessie server nach einem reboot kein netz mehr hatte, dieses nach einem workaround nun wieder da ist, läuft mysql leider nicht mehr.

Code: Select all

root@h1:/etc/udev/rules.d# systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql; generated; vendor preset: enabled)
   Active: failed (Result: exit-code) since Do 2016-09-15 09:47:19 CEST; 18min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 13648 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Sep 15 09:46:49 h1 mysqld[13832]: 160915  9:46:49 [Note] Plugin 'FEEDBACK' is disabled.
Sep 15 09:46:49 h1 mysqld[13832]: 160915  9:46:49 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
Sep 15 09:46:49 h1 mysqld[13832]: 160915  9:46:49 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
Sep 15 09:46:49 h1 mysqld[13832]: 160915  9:46:49 [Note] Server socket created on IP: '::'.
Sep 15 09:46:49 h1 mysqld[13832]: 160915  9:46:49 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
Sep 15 09:47:19 h1 mysql[13648]: Starting MariaDB database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
Sep 15 09:47:19 h1 systemd[1]: mysql.service: Control process exited, code=exited status=1
Sep 15 09:47:19 h1 systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Sep 15 09:47:19 h1 systemd[1]: mysql.service: Unit entered failed state.
Sep 15 09:47:19 h1 systemd[1]: mysql.service: Failed with result 'exit-code'.

habe dann das ohne Erfolg versucht

Code: Select all

root@h1:/tmp# mysql_upgrade 
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")
FATAL ERROR: Upgrade failed

[Joe User]

Deine my.cnf enthält noch Altlasten in Form nicht mehr existierender Parameter. Sobald Du diese Altlasten entsorgt hast, sollte MySQL/MariaDB starten und Du musst dann mysql_upgrade ausführen.

[tomotom]

Wie erkenne ich denn die Altlasten? Ist eigentlich ziemlich standardmäßig.

Code: Select all

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

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
tmp_table_size	= 32M
max_heap_table_size = 32M
key_buffer		= 512M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 80
query_cache_limit	= 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days	= 10
max_binlog_size         = 100M
innodb_buffer_pool_size	= 512M
innodb_log_file_size 	= 128M

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]

[isamchk]
key_buffer		= 16M

!includedir /etc/mysql/conf.d/

und folgendes auch nachdem ich mysqld.sock manuell erstell und chown -R mysql /var/run/mysqld/ gemacht hatte.

Code: Select all

root@h1:/var/run/mysqld# mysql -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

[Joe User]

Ich habe die my.cnf mal etwas überarbeitet, so dass der MySQLd zumindest starten sollte, das Feintuning können wir später erledigen.
Bitte ein Backup Deiner aktuellen my.cnf anlegen.

Code: Select all

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

[mysqld_safe]
socket                          = /var/run/mysqld/mysqld.sock
nice                            = 0

[mysql]
prompt                          = \u@\h [\d]>\_
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /var/run/mysqld/mysqld.sock
bind-address                    = 127.0.0.1
pid-file                        = /var/run/mysqld/mysqld.pid
basedir                         = /usr
datadir                         = /var/lib/mysql
tmpdir                          = /tmp
lc-messages-dir                 = /usr/share/mysql
log-bin                         = mysql-bin
log-output                      = FILE
log_error                       = /var/log/mysql/error.log
slow-query-log                  = 1
server-id                       = 1
sync_binlog                     = 1
binlog_cache_size               = 16M
expire_logs_days                = 10
explicit-defaults-for-timestamp = 1
myisam-recover-options          = BACKUP,FORCE
open_files_limit                = 32768
table_open_cache                = 16384
table_definition_cache          = 8192
key_buffer_size                 = 256M
max_allowed_packet              = 64M
tmp_table_size                  = 32M
max_heap_table_size             = 32M
query_cache_type                = 0
query_cache_size                = 0
long_query_time                 = 0.5
innodb_buffer_pool_size         = 512M
innodb_log_file_size            = 128M

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick

!includedir /etc/mysql/conf.d/

[tomotom]

Besten Dank. Lässt sich aber leider mit der modifizierten my.cnf auch nicht starten. Fehlermeldung von systemctl status mysql.service sieht so aus

Code: Select all

Sep 15 15:18:38 h1 mysqld[6935]: 160915 15:18:38 [Note] Crash recovery finished.
Sep 15 15:18:38 h1 mysqld[6935]: 160915 15:18:38 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
Sep 15 15:18:38 h1 mysqld[6935]: 160915 15:18:38 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
Sep 15 15:18:38 h1 mysqld[6935]: 160915 15:18:38 [Note] Server socket created on IP: '::'.
Sep 15 15:18:38 h1 mysqld[6935]: 160915 15:18:38 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
Sep 15 15:19:08 h1 mysql[6734]: Starting MariaDB database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
Sep 15 15:19:08 h1 systemd[1]: mysql.service: Control process exited, code=exited status=1

[Joe User]

Füge diese Zeile direkt vor "myisam-recover-options" in der my.cnf ein:

Code: Select all

enforce-gtid-consistency        = 1
gtid-mode                       = OFF

[tomotom]

Mit den neuen Zeilen sieht es so aus, startet nicht.

Sep 15 17:05:43 h1 mysqld[14803]: /usr/sbin/mysqld[0xa31efb]
Sep 15 17:05:43 h1 mysqld[14803]: /lib/x86_64-linux-gnu/libpthread.so.0(+0x80a4)[0x7fd91a5460a4]
Sep 15 17:05:43 h1 mysqld[14803]: /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fd9191a987d]
Sep 15 17:05:43 h1 mysqld[14803]: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html con
Sep 15 17:05:43 h1 mysqld[14803]: information that should help you find out what is causing the crash.
Sep 15 17:06:14 h1 mysql[14598]: Starting MariaDB database server: mysqld . . . . . . . . . . . . . . .
Sep 15 17:06:14 h1 systemd[1]: mysql.service: Control process exited, code=exited status=1

Leider riecht das für mich so langsam nach einer Neuinstallation. Ich habe keine Ahnung wie das doch noch zu retten wäre.

[Joe User]

Hmm, hilft es, wenn Du hinter "innodb_log_file_size" wieder die Zeile "skip-external-locking" einfügst?

[tomotom]

Nein, mysql startet auch damit nicht, leider.

[Joe User]

Ist das der komplette Output des Stacktrace?
Da müssen vorher noch mehr Zeilen sein, die sind etwas hilfreicher.

Eine Neuinstallation kann funktionieren, muss aber nicht.

Von welcher Version auf welche Version genau hast Du aktualisiert?

[tomotom]

Nach diesem ging das Netz nicht mehr und anschließend mysql, obwohl kein mysql installiert wurde. Daher reboot.

Start-Date: 2016-09-14 10:32:44
Upgrade: e2fslibs:amd64 (1.43-3~bpo8+1, 1.43.3-1~bpo8+1), linux-image-3.16.0-4-amd64:amd64 (3.16.7-ckt25-2+deb8u3, 3.16.36-1+deb8u1), e2fsprogs:amd64 (1.43-3~bpo8+1, 1.43.3-1~bpo8+1), libcomerr2:amd64 (1.43-3~bpo8+1, 1.43.3-1~bpo8+1), libss2:amd64 (1.43-3~bpo8+1, 1.43.3-1~bpo8+1), libidn11:amd64 (1.29-1+deb8u1, 1.29-1+deb8u2), hhvm:amd64 (3.14.5~jessie, 3.15.0~jessie), flex:amd64 (2.5.39-8+deb8u1, 2.5.39-8+deb8u2), libfl-dev:amd64 (2.5.39-8+deb8u1, 2.5.39-8+deb8u2), comerr-dev:amd64 (2.1-1.43-3~bpo8+1, 2.1-1.43.3-1~bpo8+1)
End-Date: 2016-09-14 10:33:19

Hier war das letze mysql upgrade ohne reboot. Danach lief aber alles.

Start-Date: 2016-08-26 15:13:55
...mysql-common:amd64 (5.5.50-0+deb8u1, 5.6.30-1~bpo8+1)

Die Meldungen kommen von

root@h1:/var/log# systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql; generated; vendor preset: enabled)
Active: failed (Result: exit-code) since Do 2016-09-15 23:40:17 CEST; 9h ago
Docs: man:systemd-sysv-generator(8)
Process: 25907 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Sep 15 23:39:47 h1 mysqld[26121]: /usr/sbin/mysqld[0xa31efb]
Sep 15 23:39:47 h1 mysqld[26121]: /lib/x86_64-linux-gnu/libpthread.so.0(+0x80a4)[0x7f8fe17b30a4]
Sep 15 23:39:47 h1 mysqld[26121]: /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f8fe041687d]
Sep 15 23:39:47 h1 mysqld[26121]: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
Sep 15 23:39:47 h1 mysqld[26121]: information that should help you find out what is causing the crash.
Sep 15 23:40:17 h1 mysql[25907]: Starting MariaDB database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . fa
Sep 15 23:40:17 h1 systemd[1]: mysql.service: Control process exited, code=exited status=1
Sep 15 23:40:17 h1 systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Sep 15 23:40:17 h1 systemd[1]: mysql.service: Unit entered failed state.
Sep 15 23:40:17 h1 systemd[1]: mysql.service: Failed with result 'exit-code'.

[Joe User]

Steht im MySQL-Log mehr?

5.6.30-1~bpo+1
Ist das ein offizielles Debian-Paket, oder stammt es aus einem anderen Repo?

[tomotom]

bpo steht für Debian Backports, also nicht ganz offiziell.

[Joe User]

OK. Trotzdem würde ich gerne den Rest des Traces aus dem Logfile noch sehen ;)

[tomotom]

Aus dem syslog

Sep 17 15:54:57 h1 systemd[1]: Starting LSB: Start and stop the mysql database server daemon...
Sep 17 15:54:57 h1 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Warning] 'explicit-defaults-for-timestamp' is MySQL 5.6 compatible option. To be implemented in later versions.
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] /usr/sbin/mysqld (mysqld 10.0.26-MariaDB-0+deb8u1) starting as process 14404 ...
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: Using mutexes to ref count buffer pool pages
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: The InnoDB memory heap is disabled
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: Memory barrier is not used
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: Compressed tables use zlib 1.2.8
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: Using Linux native AIO
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: Not using CPU crc32 instructions
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: Initializing buffer pool, size = 512.0M
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: Completed initialization of buffer pool
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: Highest supported file format is Barracuda.
Sep 17 15:54:57 h1 mysqld: 2016-09-17 15:54:57 7f0a7b5a6780 InnoDB: Operating system error number 2 in a file operation.
Sep 17 15:54:57 h1 mysqld: InnoDB: The error means the system cannot find the path specified.
Sep 17 15:54:57 h1 mysqld: InnoDB: If you are installing InnoDB, remember that you must create
Sep 17 15:54:57 h1 mysqld: InnoDB: directories yourself, InnoDB does not create them.
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/gtid_slave_pos'. See http://dev.mysql.com/doc/refman/5.6/en/ ... adict.html for how to resolve the issue.
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [ERROR] InnoDB: Tablespace open failed for '"mysql"."gtid_slave_pos"', ignored.
Sep 17 15:54:57 h1 mysqld: 2016-09-17 15:54:57 7f0a7b5a6780 InnoDB: Operating system error number 2 in a file operation.
Sep 17 15:54:57 h1 mysqld: InnoDB: The error means the system cannot find the path specified.
Sep 17 15:54:57 h1 mysqld: InnoDB: If you are installing InnoDB, remember that you must create
Sep 17 15:54:57 h1 mysqld: InnoDB: directories yourself, InnoDB does not create them.
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/innodb_index_stats'. See http://dev.mysql.com/doc/refman/5.6/en/ ... adict.html for how to resolve the issue.
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [ERROR] InnoDB: Tablespace open failed for '"mysql"."innodb_index_stats"', ignored.
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: 128 rollback segment(s) are active.
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [Note] InnoDB: Waiting for purge to start
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [ERROR] InnoDB: Failed to find tablespace for table '"mysql"."innodb_index_stats"' in the cache. Attempting to load the tablespace with space id 2.
Sep 17 15:54:57 h1 mysqld: 2016-09-17 15:54:57 7f0a327fa700 InnoDB: Operating system error number 2 in a file operation.
Sep 17 15:54:57 h1 mysqld: InnoDB: The error means the system cannot find the path specified.
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/innodb_index_stats'. See http://dev.mysql.com/doc/refman/5.6/en/ ... adict.html for how to resolve the issue.
Sep 17 15:54:57 h1 mysqld: InnoDB: Error: tablespace id is 1 in the data dictionary
Sep 17 15:54:57 h1 mysqld: InnoDB: but in file ./mysql/innodb_table_stats.ibd it is 1138!
Sep 17 15:54:57 h1 mysqld: 2016-09-17 15:54:57 7f0a327fa700 InnoDB: Assertion failure in thread 139681773627136 in file fil0fil.cc line 635
Sep 17 15:54:57 h1 mysqld: InnoDB: We intentionally generate a memory trap.
Sep 17 15:54:57 h1 mysqld: InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
Sep 17 15:54:57 h1 mysqld: InnoDB: If you get repeated assertion failures or crashes, even
Sep 17 15:54:57 h1 mysqld: InnoDB: immediately after the mysqld startup, there may be
Sep 17 15:54:57 h1 mysqld: InnoDB: corruption in the InnoDB tablespace. Please refer to
Sep 17 15:54:57 h1 mysqld: InnoDB: http://dev.mysql.com/doc/refman/5.6/en/ ... overy.html
Sep 17 15:54:57 h1 mysqld: InnoDB: about forcing recovery.
Sep 17 15:54:57 h1 mysqld: 160917 15:54:57 [ERROR] mysqld got signal 6 ;
Sep 17 15:54:57 h1 mysqld: This could be because you hit a bug. It is also possible that this binary
Sep 17 15:54:57 h1 mysqld: or one of the libraries it was linked against is corrupt, improperly built,
Sep 17 15:54:57 h1 mysqld: or misconfigured. This error can also be caused by malfunctioning hardware.
Sep 17 15:54:57 h1 mysqld:
Sep 17 15:54:57 h1 mysqld: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
Sep 17 15:54:57 h1 mysqld:
Sep 17 15:54:57 h1 mysqld: We will try our best to scrape up some info that will hopefully help
Sep 17 15:54:57 h1 mysqld: diagnose the problem, but since we have already crashed,
Sep 17 15:54:57 h1 mysqld: something is definitely wrong and this may fail.
Sep 17 15:54:57 h1 mysqld:
Sep 17 15:54:57 h1 mysqld: Server version: 10.0.26-MariaDB-0+deb8u1
Sep 17 15:54:57 h1 mysqld: key_buffer_size=268435456
Sep 17 15:54:57 h1 mysqld: read_buffer_size=131072
Sep 17 15:54:57 h1 mysqld: max_used_connections=0
Sep 17 15:54:57 h1 mysqld: max_threads=153
Sep 17 15:54:57 h1 mysqld: thread_count=0
Sep 17 15:54:57 h1 mysqld: It is possible that mysqld could use up to
Sep 17 15:54:57 h1 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 598077 K bytes of memory
Sep 17 15:54:57 h1 mysqld: Hope that's ok; if not, decrease some variables in the equation.
Sep 17 15:54:57 h1 mysqld:
Sep 17 15:54:57 h1 mysqld: Thread pointer: 0x0x0
Sep 17 15:54:57 h1 mysqld: Attempting backtrace. You can use the following information to find out
Sep 17 15:54:57 h1 mysqld: where mysqld died. If you see no messages after this, something went
Sep 17 15:54:57 h1 mysqld: terribly wrong...
Sep 17 15:54:57 h1 mysqld: stack_bottom = 0x0 thread_stack 0x48000
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0xbffe3e]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld(handle_fatal_signal+0x392)[0x733a32]
Sep 17 15:54:57 h1 mysqld: /lib/x86_64-linux-gnu/libpthread.so.0(+0xf8d0)[0x7f0a7b18b8d0]
Sep 17 15:54:57 h1 mysqld: /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x37)[0x7f0a79d34067]
Sep 17 15:54:57 h1 mysqld: /lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7f0a79d35448]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0xae005e]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0xae010e]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0xae7fdb]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0xaaf6c1]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0xa92d81]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0xa764f1]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0xa1a4c0]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0xa1726f]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0xa18e87]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0x9e51d2]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0xa3fbb2]
Sep 17 15:54:57 h1 mysqld: /usr/sbin/mysqld[0xa31efb]
Sep 17 15:54:57 h1 mysqld: /lib/x86_64-linux-gnu/libpthread.so.0(+0x80a4)[0x7f0a7b1840a4]
Sep 17 15:54:57 h1 mysqld: /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f0a79de787d]
Sep 17 15:54:57 h1 mysqld: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
Sep 17 15:54:57 h1 mysqld: information that should help you find out what is causing the crash.