10

first I have to say I'm a mysql newbie. Basically mysql does not start and says:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysql.sock' (2)

Now Here are the steps with which I ruined everything:

Due to it was not possible to log into my system (otrs) I thought a restore of an older backup would help.

While the restore process the backup manager said I need to drop the old db. I tried it but the process did not finish and so I cancelled it.

After this I tried to reboot the system but had huge problems with this but when I finished the reboot I tried to run mysql but it said it could not find the mysql.socket.

At this point I thought it would be better to reinstall mysql and did so but this did not help. When trying to start mysqld as mysql user it said:

[ERROR] Found 1 prepared transactions! It means that mysqld was not shut down properly last time and critical recovery information (last binlog or tc.log file) was manually deleted after a crash. You have to start mysqld with --tc-heuristic-recover switch to commit or rollback pending transactions.

trying both did not help:

mysql:/root> /usr/sbin/mysqld  --tc-heuristic-recover commit
131213 16:46:00 InnoDB: The InnoDB memory heap is disabled
131213 16:46:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131213 16:46:00 InnoDB: Compressed tables use zlib 1.2.7
131213 16:46:00 InnoDB: Using Linux native AIO
131213 16:46:00 InnoDB: Initializing buffer pool, size = 128.0M
131213 16:46:00 InnoDB: Completed initialization of buffer pool
131213 16:46:00 InnoDB: highest supported file format is Barracuda.
131213 16:46:01  InnoDB: Waiting for the background threads to start
131213 16:46:02 Percona XtraDB (http://www.percona.com) 5.5.33-MariaDB-31.1 started; log sequence number 3710898915
131213 16:46:02 [Note] Server socket created on IP: '0.0.0.0'.
131213 16:46:02 [ERROR] Event Scheduler: Failed to open table mysql.event
131213 16:46:02 [ERROR] Event Scheduler: Error while loading from disk.
131213 16:46:02 [Note] Event Scheduler: Purging the queue. 0 events
131213 16:46:02 [ERROR] Aborting

131213 16:46:02  InnoDB: Starting shutdown...
131213 16:46:03  InnoDB: Shutdown completed; log sequence number 3710898915
131213 16:46:03 [Note] /usr/sbin/mysqld: Shutdown complete

Running systemctl start mysql.service fails anytime:

mysql.service - LSB: Start the MySQL database server
          Loaded: loaded (/etc/init.d/mysql)
          Active: failed (Result: timeout) since Fri, 13 Dec 2013 16:27:12 +0100; 23min ago
         Process: 8845 ExecStart=/etc/init.d/mysql start (code=killed, signal=TERM)
          CGroup: name=systemd:/system/mysql.service

Dec 13 16:31:21  mysql[8845]: otrs.user_preferences                              OK
Dec 13 16:31:21  mysql[8845]: otrs.users                                         OK
Dec 13 16:31:21  mysql[8845]: otrs.valid                                         OK
Dec 13 16:31:21  mysql[8845]: otrs.virtual_fs                                    OK
Dec 13 16:31:21  mysql[8845]: otrs.virtual_fs_db                                 OK
Dec 13 16:31:21  mysql[8845]: otrs.virtual_fs_preferences                        OK
Dec 13 16:31:21  mysql[8845]: otrs.web_upload_cache                              OK
Dec 13 16:31:21  mysql[8845]: otrs.xml_storage                                   OK
Dec 13 16:31:21  mysql[8845]: performance_schema
Dec 13 16:31:21  mysql[8845]: Phase 3/3: Running 'mysql_fix_privilege_tables'...

I have absolutely no clue what to do. Could anyone help me? How can the otrs tables been droped/deleted without using the mysql DROP command? Would this help anyway?

Thank you.

rosenrot
  • 101
  • 1
  • 1
  • 4
  • I'd just like to add that I had this issue after a server crash with Fedora 28 and MariaDB 10.2.21. I did sudo -u mysql mysqld_safe --tc-heuristic-recover=commit as root and then started the mariadb service as normal with systemctl and the issue was resolved. – Stefan Midjich Feb 01 '19 at 09:39

4 Answers4

19
mysqld --tc-heuristic-recover=ROLLBACK

Didn't quite do the magic for me. However the following worked

mysqld_safe --tc-heuristic-recover=COMMIT
chinmay
  • 570
  • 3
  • 13
7

I was able to overcome this issue on CentOS 6 with

service mysql start --tc-heuristic-recover=ROLLBACK

which ultimately discarded the commit in question. I'm not sure if systemd scripts support passing additional parameters. Maybe you could try and run it manually with the =

mysqld --tc-heuristic-recover=ROLLBACK
greezybacon
  • 466
  • 4
  • 14
3

On mysql 5.6.x, and because of this off-by-one https://bugs.mysql.com/bug.php?id=70860 bug, I was able to get past this by doing

sudo service mysql start --tc-heuristic-recover=0

which presumably commits the tx.

David Mann
  • 1,574
  • 2
  • 14
  • 18
1

Answer from ->> https://www.youtube.com/watch?v=qr-t8ksYO78

go to my.cnf file, note that you will find multiple my.cnf file, i had to look at all of them to find this->

 # The MySQL server
    [mysqld]
    user = mysql
    port=3306
    socket      = /opt/lampp/var/mysql/mysql.sock

Copy the socket path and writ it like ->

mysql -u root -p --socket=/opt/lampp/var/mysql/mysql.sock

Thanks

Syed Aqeel
  • 19
  • 4