Issue Error after moving ibdata1 file temporarily from /var/lib/mysql

[erreip88]

Hello Guys,
i have a Prestashop store and Plesk panel on a Centos dedicated server since years.

I was finishing the space on SSD so I moved the ibdata1 file from /var/lib/mysql folder on another partition. (I know it has been a stupid thing)

I freed up some GBs and then i re-moved the ibdata1 file to the standard directory, now I have many problems with the store and the Plesk panel, sometimes it works correctly (example: add to cart, make orders, i enter into webmail, etc etc) and sometimes not and I get this error:

Connection failed: SQLSTATE[HY000] [2002] No such file or directory

If i refresh everything goes well, then sometimes same error come again.

Looking the mysql log I get this:

200202 13:35:28 mysqld_safe Number of processes running now: 0
200202 13:35:28 mysqld_safe mysqld restarted
200202 13:35:28  InnoDB: Initializing buffer pool, size = 8.0M
200202 13:35:28  InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 45 1568465318
200202 13:35:28  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 45 1568493321
200202 13:35:28  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 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
InnoDB: Apply batch completed
200202 13:35:29  InnoDB: Started; log sequence number 45 1568493321
200202 13:35:29 [Note] Event Scheduler: Loaded 0 events
200202 13:35:29 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

How to solve now?

 

[Bitpalast]

Have you tried to use "mysqlcheck" to check and repair your database?

 

[erreip88]

I get this... it's like if mysql lost connection, also when browsing, situation is similar

ps_carrier_lang OK


ps_carrier_shop OK


ps_carrier_tax_rules_group_shop OK


ps_carrier_zone OK


mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '

 

[Bitpalast]

This should not happen. Is there any log entry in /var/log/messages or a specific database log that points to the reason? Is the hard disk working reliably?

 

[erreip88]

A lot of error into mysql log

InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
18:15:00 UTC - mysqld got signal 6 ;
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.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

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

Thread pointer: 0x0
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 = 0 thread_stack 0x40000
/usr/libexec/mysqld(my_print_stacktrace+0x29) [0x850ca9]
/usr/libexec/mysqld(handle_fatal_signal+0x483) [0x6a4143]
/lib64/libpthread.so.0(+0xf7e0) [0x7f5ce918f7e0]
/lib64/libc.so.6(gsignal+0x35) [0x7f5ce77c34f5]
/lib64/libc.so.6(abort+0x175) [0x7f5ce77c4cd5]
/usr/libexec/mysqld(fil_io+0x36e) [0x76807e]
/usr/libexec/mysqld() [0x74fc53]
/usr/libexec/mysqld(buf_read_page+0x225) [0x750695]
/usr/libexec/mysqld(buf_page_get_gen+0x393) [0x749b73]
/usr/libexec/mysqld() [0x7cd229]
/usr/libexec/mysqld() [0x7cd895]
/usr/libexec/mysqld(trx_purge_fetch_next_rec+0x218) [0x7ce8f8]
/usr/libexec/mysqld(row_purge_step+0x40) [0x7b34e0]
/usr/libexec/mysqld(que_run_threads+0x55b) [0x7a278b]
/usr/libexec/mysqld(trx_purge+0x332) [0x7ccb32]
/usr/libexec/mysqld(srv_master_thread+0x708) [0x7c54c8]
/lib64/libpthread.so.0(+0x7aa1) [0x7f5ce9187aa1]
/lib64/libc.so.6(clone+0x6d) [0x7f5ce7879c4d]
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.
200202 19:15:00 mysqld_safe Number of processes running now: 2
200202 19:15:00 mysqld_safe mysqld process hanging, pid 23090 - killed
200202 19:15:00 mysqld_safe Number of processes running now: 1
200202 19:15:00 mysqld_safe mysqld process hanging, pid 23085 - killed
200202 19:15:00 mysqld_safe mysqld restarted
200202 19:15:00 mysqld_safe Number of processes running now: 0
200202 19:15:00 mysqld_safe mysqld restarted
200202 19:15:00 mysqld_safe mysqld restarted
200202 19:15:00  InnoDB: Initializing buffer pool, size = 8.0M
200202 19:15:00  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
200202 19:15:00  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
200202 19:15:00  InnoDB: Initializing buffer pool, size = 8.0M
200202 19:15:00  InnoDB: Completed initialization of buffer pool
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
200202 19:15:00  InnoDB: Retrying to lock the first data file
200202 19:15:00  InnoDB: Initializing buffer pool, size = 8.0M
200202 19:15:00  InnoDB: Completed initialization of buffer pool
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
200202 19:15:00  InnoDB: Retrying to lock the first data file
200202 19:15:00  InnoDB: Started; log sequence number 45 1572101406
200202 19:15:00 [Note] Event Scheduler: Loaded 0 events
200202 19:15:00 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.

 

[Bitpalast]

You can find very many articles in other forums on the "Unable to lock ./ibdata1, error: 11" issue. For example:

MySQL keeps crashing: InnoDB: Unable to lock ./ibdata1, error: 11

I have a simple webserver (Debian 6.0 x86, DirectAdmin with 1 GB of memory and still 10 GB free space, mySQl version 5.5.9), however the mySQL server keeps crashing and I need to kill all mySQL pro...

serverfault.com

There is no clear reason. It could be a software like App Armor that is blocking access. It could be that there is not enough room on the disc so that a socket cannot be created. It could be a hanging process that you could simply kill and then restart as described in

Plesk Help Center

support.plesk.com

 

[mow]

It could also be that moving the file elsewhere and back messed up the file owner and/or permissions. Especially if the file was copied back and neither -a nor --preserve options were given to cp, or if "elsewhere" has a filesystem (e.g. FAT32) that does not store such information.