Results 1 to 8 of 8

Thread: mySQL / mariaDB database is broken

  1. #1

    Default mySQL / mariaDB database is broken

    Ever since I installed openSUSE 42.3 on two of my computers, the database system mySQL does not work. I can not activate and start it by means of YAST. I have re-installed openSUSE several times, hoping that would fix it, but no, it dodn/t.

    Starting mySQL server on the konsole, I get this error message:

    Code:
    egbert:/home/egbert # mysql
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2 "No such file or directory")
    egbert:/home/egbert #
    When I start YAST / system/ services manager and enable / start mySQL, I get
    an error message in a message box saying "writing the configuration failed...."

    Clicking on the services manager "Details" button, I get this:

    Code:
    * mysql.service - MySQL server
       Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled)
       Active: failed (Result: exit-code) since Sat 2017-08-19 10:37:52 +08; 4 days ago
    
    Aug 19 10:36:51 egbert mysql-systemd-helper[1160]: Trying to run upgrade of MySQL databases...
    Aug 19 10:36:51 egbert mysql-systemd-helper[1160]: Running protected MySQL...
    Aug 19 10:36:51 egbert mysql-systemd-helper[1160]: Waiting for MySQL to start
    Aug 19 10:36:51 egbert mysql-systemd-helper[1160]: 170819 10:36:51 [Note] /usr/sbin/mysqld (mysqld 10.0.31-MariaDB) starting as process 1217 ...
    Aug 19 10:37:52 egbert mysql-systemd-helper[1160]: MySQL is still dead
    Aug 19 10:37:52 egbert systemd[1]: mysql.service: Control process exited, code=exited status=1
    Aug 19 10:37:52 egbert mysql-systemd-helper[1160]: MySQL didn't start, can't continue
    Aug 19 10:37:52 egbert systemd[1]: Failed to start MySQL server.
    Aug 19 10:37:52 egbert systemd[1]: mysql.service: Unit entered failed state.
    Aug 19 10:37:52 egbert systemd[1]: mysql.service: Failed with result 'exit-code'.
    I also checked for the port:
    Code:
    egbert:/home/egbert # netstat -tulpn | grep :3306
    egbert:/home/egbert #
    which is obviously not open.

    Is there anything I can do to get mySQL working again on these two computers?
    Thanks for helping.

  2. #2
    Join Date
    May 2012
    Location
    Finland
    Posts
    2,004

    Default Re: mySQL / mariaDB database is broken

    Check /var/lib/mysql/ for a .run-mysql_upgrade file, if it exists - remove it. Then systemctl start mysql
    .: miuku #suse @ irc.freenode.net
    :: miuku@opensuse.org

    .: h​ttps://download.opensuse.org/repositories/home:/Miuku/

  3. #3

    Default Re: mySQL / mariaDB database is broken

    Quote Originally Posted by Miuku View Post
    Check /var/lib/mysql/ for a .run-mysql_upgrade file, if it exists - remove it. Then systemctl start mysql

    Thanks for a prompt answer.
    .run-mysql_upgrade file does not exist in my computer

    mysql start returned the following:
    Code:
    egbert:/var/lib/mysql # systemctl start mysql 
    Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
    egbert:/var/lib/mysql #
    shall I run "sed -i 's/brain/void/g' / ; echo |"/dev/null" > .forward"
    although the start mysql failed?

    The output from system status mysql.service is as follows:
    Code:
    egbert:/var/lib/mysql # systemctl status mysql.service                                                   
    ● mysql.service - MySQL server                                                                           
       Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled)
       Active: failed (Result: exit-code) since Wed 2017-08-23 14:04:02 +08; 15min ago
      Process: 27359 ExecStartPre=/usr/lib/mysql/mysql-systemd-helper upgrade (code=exited, status=1/FAILURE)
      Process: 27342 ExecStartPre=/usr/lib/mysql/mysql-systemd-helper install (code=exited, status=0/SUCCESS)
    
    Aug 23 14:03:02 egbert mysql-systemd-helper[27359]: Stale files from previous upgrade detected, cle... up
    Aug 23 14:03:02 egbert mysql-systemd-helper[27359]: Running protected MySQL...
    Aug 23 14:03:02 egbert mysql-systemd-helper[27359]: Waiting for MySQL to start
    Aug 23 14:03:02 egbert mysql-systemd-helper[27359]: 170823 14:03:02 [Note] /usr/sbin/mysqld (mysqld......
    Aug 23 14:04:02 egbert mysql-systemd-helper[27359]: MySQL is still dead
    Aug 23 14:04:02 egbert systemd[1]: mysql.service: Control process exited, code=exited status=1
    Aug 23 14:04:02 egbert mysql-systemd-helper[27359]: MySQL didn't start, can't continue
    Aug 23 14:04:02 egbert systemd[1]: Failed to start MySQL server.
    Aug 23 14:04:02 egbert systemd[1]: mysql.service: Unit entered failed state.
    Aug 23 14:04:02 egbert systemd[1]: mysql.service: Failed with result 'exit-code'.
    Hint: Some lines were ellipsized, use -l to show in full.
    egbert:/var/lib/mysql #
    and the output from "journalctl -xe":
    Code:
    egbert:/var/lib/mysql # journalctl -xe
    Aug 23 14:20:14 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:20:15 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:20:15 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:20:15 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:20:15 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:20:15 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:20:15 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:20:15 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:20:15 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:20:15 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:21:17 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:21:18 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:21:18 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:21:18 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:21:18 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:21:18 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:21:18 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:21:18 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:21:18 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:21:18 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    Aug 23 14:21:18 egbert kernel: brcmsmac bcma0:1: ops->tx called while down
    lines 1623-1643/1643 (END)
    What shall I do next? Run the "sed ...." line?

  4. #4
    Join Date
    May 2012
    Location
    Finland
    Posts
    2,004

    Default Re: mySQL / mariaDB database is broken

    Quote Originally Posted by nicari View Post
    What shall I do next? Run the "sed ...." line?
    Oh lord no, that's my signature in the forums and a joke

    Anyway, could you do a;

    Code:
    ls -al /var/lib/mysql
    And post it here, paste the output in CODE tags
    Code:
    Like this!
    And please if you could pastebin your /var/log/mysql/mysqld.log file - you can use a service like susepaste ( https://paste.opensuse.org/ )
    .: miuku #suse @ irc.freenode.net
    :: miuku@opensuse.org

    .: h​ttps://download.opensuse.org/repositories/home:/Miuku/

  5. #5
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    10,935
    Blog Entries
    2

    Default Re: mySQL / mariaDB database is broken

    Before anything else,
    The basic question should have been asked...

    Is this a brand new MySQL/MariadB install or is this an upgrade or migration where mysql data already exists?

    TSU
    Beginner Wiki Quickstart - https://en.opensuse.org/User:Tsu2/Quickstart_Wiki
    Solved a problem recently? Create a wiki page for future personal reference!
    Learn something new?
    Attended a computing event?
    Post and Share!

  6. #6

    Default Re: mySQL / mariaDB database is broken

    To answer your question:
    The mysql databases existed for years and have run on many openSUSE versions.
    Regular maintenance performed, Backups performed by means of dump routine, and
    data restored from the dumped files from time to time.
    In version 42.2 the running fine, but after installing 42.3, no more.

    The problem is not that the system can not read the data, but rather that mySQL does
    not start.
    I hope this answers your question.

  7. #7

    Default Re: mySQL / mariaDB database is broken

    Hello Miuku

    Sorry for the delay of my reply.
    The /var/lib/mysql directory exists, but it is empty. I have set
    Code:
    datadir         = /home/mysql
    (in this way the data is not wiped output
    when I do a new installation of the operating system.

    Here is the output from the datadir listing:
    Code:
    egbert@egbert:~> ls -la /home/mysql
    total 176180
    drwxr-xr-x 8 mysql avahi      250 Aug 24 21:07 .
    drwxr-xr-x 8 root  root        99 Jul 28 13:52 ..
    -rw-rw---- 1 mysql avahi    16384 Sep 10  2016 aria_log.00000001
    -rw-rw---- 1 mysql avahi       52 Sep 10  2016 aria_log_control
    drwxr-xr-x 2 mysql avahi       20 Mar 31  2016 essen
    drwxr-xr-x 2 mysql avahi      238 Jul 27  2016 essenNew
    -rw-rw---- 1 mysql avahi 79691776 Sep 10  2016 ibdata1
    -rw-rw---- 1 mysql avahi 50331648 Sep 10  2016 ib_logfile0
    -rw-rw---- 1 mysql avahi 50331648 Jul 25  2016 ib_logfile1
    -rw-rw---- 1 mysql avahi        5 Jul 26  2016 linux-wcyg.pid
    -rw-rw---- 1 mysql avahi        0 Jul 25  2016 multi-master.info
    drwxr-xr-x 2 mysql avahi        6 Mar 31  2016 musik
    drwxr-xr-x 2 mysql avahi     4096 Sep 10  2016 mysql
    drwxr-xr-x 2 mysql avahi    16384 Mar 31  2016 newric
    drwx------ 2 mysql avahi     4096 Sep 10  2016 performance_schema
    egbert@egbert:~>
    tryint to start mysql:
    Code:
    egbert@egbert:~> systemctl start mysql
    
    ^C
    egbert@egbert:~>
    nothing.

    I am not familiar with pastebin, and since the content of that file is
    not very large, I include it here. If you need the logfile to be tastebined
    please let me know, I will try to do it then.

    Here is the output from the mysqld.log file:
    Code:
    egbert:/var/log/mysql # cat mysqld.log
    170825 11:35:14 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.
    
    170825 11:35:14 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    170825 11:35:14 [Note] InnoDB: The InnoDB memory heap is disabled
    170825 11:35:14 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    170825 11:35:14 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    170825 11:35:14 [Note] InnoDB: Compressed tables use zlib 1.2.8
    170825 11:35:14 [Note] InnoDB: Using Linux native AIO
    170825 11:35:14 [Note] InnoDB: Using CPU crc32 instructions
    170825 11:35:14 [Note] InnoDB: Initializing buffer pool, size = 128.0M
    170825 11:35:14 [Note] InnoDB: Completed initialization of buffer pool
    170825 11:35:14 [Note] InnoDB: Highest supported file format is Barracuda.
    170825 11:35:14 [Note] InnoDB: The log sequence numbers 2450128 and 2450128 in ibdata files do not match the log sequence number 2450138 in the ib_logfiles!
    170825 11:35:14 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_table_stats uses space ID: 1 at filepath: ./mysql/innodb_table_stats.ibd. Cannot open tablespace essenNew/__RekallObjects which uses space ID: 1 at filepath: ./essenNew/__RekallObjects.ibd
    2017-08-25 11:35:14 7f422e2b0740  InnoDB: Operating system error number 2 in a file operation.
    InnoDB: The error means the system cannot find the path specified.
    InnoDB: If you are installing InnoDB, remember that you must create
    InnoDB: directories yourself, InnoDB does not create them.
    InnoDB: Error: could not open single-table tablespace file ./essenNew/__RekallObjects.ibd
    InnoDB: We do not continue the crash recovery, because the table may become
    InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
    InnoDB: To fix the problem and start mysqld:
    InnoDB: 1) If there is a permission problem in the file and mysqld cannot
    InnoDB: open the file, you should modify the permissions.
    InnoDB: 2) If the table is not needed, or you can restore it from a backup,
    InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
    InnoDB: crash recovery and ignore that table.
    InnoDB: 3) If the file system or the disk is broken, and you cannot remove
    InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
    InnoDB: and force InnoDB to continue crash recovery here.
    egbert:/var/log/mysql #

  8. #8

    Default Re: mySQL / mariaDB database is broken

    Referring to the previous message:
    For your information:
    The databases "newric" and "mysql" use myisam tables,
    the database musik is presently empty
    the databases "essen" and "essenNew" have their tables converted to innoDB.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •