Problems with MySQL after updating PHP with custombuild 2.0

medieja · Nov 13, 2016

Hi there,

I'm having troubles with my MySQL installation on my Debian Jessie VPS after upgrading PHP with custombuild. I 'm unable to run MySQL and my database-driven websites (Wordpress) return a connection failed error. Restarting MySQL from the CLI results in the following error:

Code:

Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect

I tried to restart the MySQL server but unfortunately that doesn't work. Rebuilding MySQL with the information listed here:

https://help.directadmin.com/item.php?id=240

results in:

Code:

Synchronizing state for mysqld.service with sysvinit using update-rc.d...
Executing /usr/sbin/update-rc.d mysqld defaults
Executing /usr/sbin/update-rc.d mysqld enable
Ensuring local-infile is disabled for security reasons in MySQL configuration file...
Giving mysqld a few seconds to start up...
Looking for 'mysql' as: /usr/local/mysql/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql/bin/mysqlcheck
Running 'mysqlcheck with default connection arguments
/usr/local/mysql/bin/mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
FATAL ERROR: Error during call to mysql_check for fixing the db/tables names.
Restarting MySQL.

Does anybody know how to fix this? I've Googled a lot but unfortunately I haven't figured out yet how to fix this. Any help would be much appreciated!

SeLLeRoNe · Nov 14, 2016

What error you got when restarting mysql?

What's the content (last 50 lines would be enough) of /var/lib/mysql/$HOSTNAME.err file after a restart attempt?

Best regards

medieja · Nov 14, 2016

Thank you for your reply SeLLeRoNe

My .err file is in the home directory but I suppose that doesn't make a difference. I can tell by the content of that file something goes wrong but I don't know what exactly and how to fix it.

Code:

161114 14:54:12 InnoDB: Initializing buffer pool, size = 128.0M
161114 14:54:12 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
161114 14:54:12 [ERROR] Plugin 'InnoDB' init function returned error.
161114 14:54:12 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
161114 14:54:12 [ERROR] Unknown/unsupported storage engine: InnoDB
161114 14:54:12 [ERROR] Aborting

161114 14:54:12 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

161114 14:54:12 mysqld_safe mysqld from pid file /usr/local/mysql/data/zeus.pid ended
161114 14:55:02 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
161114 14:55:02 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.52) starting as process 16824 ...
161114 14:55:02 [Note] Plugin 'FEDERATED' is disabled.
161114 14:55:02 InnoDB: The InnoDB memory heap is disabled
161114 14:55:02 InnoDB: Mutexes and rw_locks use GCC atomic builtins
161114 14:55:02 InnoDB: Compressed tables use zlib 1.2.3
161114 14:55:02 InnoDB: Using Linux native AIO
161114 14:55:02 InnoDB: Initializing buffer pool, size = 128.0M
161114 14:55:02 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
161114 14:55:02 [ERROR] Plugin 'InnoDB' init function returned error.
161114 14:55:02 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
161114 14:55:02 [ERROR] Unknown/unsupported storage engine: InnoDB
161114 14:55:02 [ERROR] Aborting

161114 14:55:02 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

161114 14:55:02 mysqld_safe mysqld from pid file /usr/local/mysql/data/zeus.pid ended
161114 14:55:12 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
161114 14:55:12 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.52) starting as process 16972 ...
161114 14:55:12 [Note] Plugin 'FEDERATED' is disabled.
161114 14:55:12 InnoDB: The InnoDB memory heap is disabled
161114 14:55:12 InnoDB: Mutexes and rw_locks use GCC atomic builtins
161114 14:55:12 InnoDB: Compressed tables use zlib 1.2.3
161114 14:55:12 InnoDB: Using Linux native AIO
161114 14:55:12 InnoDB: Initializing buffer pool, size = 128.0M
161114 14:55:12 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
161114 14:55:12 [ERROR] Plugin 'InnoDB' init function returned error.
161114 14:55:12 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
161114 14:55:12 [ERROR] Unknown/unsupported storage engine: InnoDB
161114 14:55:12 [ERROR] Aborting

161114 14:55:12 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

161114 14:55:12 mysqld_safe mysqld from pid file /usr/local/mysql/data/zeus.pid ended

Hopefully this makes sense to you

SeLLeRoNe · Nov 14, 2016

Have you made any change to the /etc/my.cnf file?

It looks like it fail to start because of InnoDB.

If you are not using master/slave replication in MySQL you may want to try remove those files:
/var/lib/mysql/ibdata1
/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

You may consider to move those files elsewhere than restart mysql, if it restart and everything work you can safely remove those files, if it still doesn't start, please keep those files away and send me latest restart attempt error log.

Best regards

medieja · Nov 14, 2016

I didn't make any changes to /etc/my.cnf. My /etc/my.cnf looks like this:

Code:

[mysqld]
local-infile=0
innodb_file_per_table

Below you find the latest restart error log:

Code:

InnoDB: Apply batch completed
161114 15:35:02  InnoDB: Waiting for the background threads to start
161114 15:35:03 InnoDB: 5.5.52 started; log sequence number 1595675
161114 15:35:03 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
161114 15:35:03 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
161114 15:35:03 [Note] Server socket created on IP: '0.0.0.0'.
161114 15:35:03 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
161114 15:35:03 mysqld_safe mysqld from pid file /usr/local/mysql/data/zeus.pid ended
161114 15:35:11 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
161114 15:35:11 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.52) starting as process 30555 ...
161114 15:35:11 [Note] Plugin 'FEDERATED' is disabled.
161114 15:35:11 InnoDB: The InnoDB memory heap is disabled
161114 15:35:11 InnoDB: Mutexes and rw_locks use GCC atomic builtins
161114 15:35:11 InnoDB: Compressed tables use zlib 1.2.3
161114 15:35:11 InnoDB: Using Linux native AIO
161114 15:35:11 InnoDB: Initializing buffer pool, size = 128.0M
161114 15:35:11 InnoDB: Completed initialization of buffer pool
161114 15:35:11 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 49439
161114 15:35:11  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 1595675
161114 15:35:12  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 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

SeLLeRoNe · Nov 14, 2016

Well it looks like it is starting now, did you have errors? Is it working now?

The log you posted was at 99% of restore, please post it again if mysql is still not working.

medieja · Nov 14, 2016

Unfortunately it still isn't working.

Code:

InnoDB: Apply batch completed
161114 17:36:02  InnoDB: Waiting for the background threads to start
161114 17:36:03 InnoDB: 5.5.52 started; log sequence number 1595675
161114 17:36:03 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
161114 17:36:03 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
161114 17:36:03 [Note] Server socket created on IP: '0.0.0.0'.
161114 17:36:03 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
161114 17:36:03 mysqld_safe mysqld from pid file /usr/local/mysql/data/zeus.pid ended
161114 17:36:11 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
161114 17:36:11 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.52) starting as process 6857 ...
161114 17:36:11 [Note] Plugin 'FEDERATED' is disabled.
161114 17:36:11 InnoDB: The InnoDB memory heap is disabled
161114 17:36:11 InnoDB: Mutexes and rw_locks use GCC atomic builtins
161114 17:36:11 InnoDB: Compressed tables use zlib 1.2.3
161114 17:36:11 InnoDB: Using Linux native AIO
161114 17:36:11 InnoDB: Initializing buffer pool, size = 128.0M
161114 17:36:11 InnoDB: Completed initialization of buffer pool
161114 17:36:11 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 49439
161114 17:36:11  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 1595675
161114 17:36:12  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 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
161114 17:36:12  InnoDB: Waiting for the background threads to start
161114 17:36:13 InnoDB: 5.5.52 started; log sequence number 1595675
161114 17:36:13 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
161114 17:36:13 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
161114 17:36:13 [Note] Server socket created on IP: '0.0.0.0'.
161114 17:36:13 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
161114 17:36:13 mysqld_safe mysqld from pid file /usr/local/mysql/data/zeus.pid ended

Seems like a table mysql.host is missing?

SeLLeRoNe · Nov 14, 2016

Do you have backups?

medieja · Nov 15, 2016

No database back-ups I'm afraid. Are there any options left?

SeLLeRoNe · Nov 15, 2016

You can try run this:

Code:

/usr/bin/mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --auto-repair --analyze --all-databases

But i'm afraid that will not work if MySQL hasn't started.

The point is, i can make you delete every data and reinstall MySQL, but than a lot of work to re-configure all DB/User/Pass have to be done and all existing Databases will be lost.

Maybe would be worth more for you if you hire someone that can try to fix it for you.

Best regards

ikkeben · Nov 19, 2016

@medieja solved?
and how?

medieja · Nov 19, 2016

Luckily I still had a snapshot where MySQL was partially working. It was a bit of a hassle rolling back to this point regarding all the other user data but fortunately everything worked out well. It actually brings me back to my initial problem where MySQL is running but I can't connect via localhost, this is what I mean by partially working.

E.g. my Wordpress based websites which used to connect via localhost return a database error. However, connecting to the database via 127.0.0.1 works. Any ideas or suggestions of what this might be?

Wanabo · Nov 19, 2016

Perhaps this is related: SECURITY(low): removed localhost A and AAAA records

SeLLeRoNe · Nov 20, 2016

You should check if the line 127.0.0.1 localhost is present in /etc/hosts

best regards

medieja · Nov 22, 2016

I checked it, and it's present in my /etc/hosts file. As I recall this issue occurred when I upgraded / re-compiled PHP from running 1 version (5.5) to running two versions (7.0 and 5.6). Do you have any other suggestions where I might have to look?

Kind regards

SeLLeRoNe · Nov 22, 2016

Mmmh, that's quite strange, what happend if you ping localhost from your server? Does it reply from 127.0.0.1? Can you double-check that you have just one entry for localhost?
cat /etc/hosts | grep "localhost"

Best regards

medieja · Nov 22, 2016

cat /etc/hosts | grep "localhost"

Code:

127.0.0.1       localhost
::1     localhost ip6-localhost ip6-loopback

ping localhost

Code:

64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.041 ms

So that seems to be okay I guess?

SeLLeRoNe · Nov 22, 2016

It shouldn't make any difference (unless PHP is trying to connect over IPv6) but you may want to try the IPv6 localhost to localhost6

Best regards

medieja · Nov 22, 2016

I tried that, but it didn't make a difference unfortunately. However I think I found the problem, but I'm not quite sure how to solve it.

Running

PHP:

<?php phpinfo(); ?>

tells me that the mysqli.default_socket is located here:

Code:

/usr/local/mysql/data/mysql.sock

But since I'm running Debian 8 the default location of my .sock file is:

Code:

/tmp/mysql.sock

I think that's where it goes wrong. Should I change my php.ini files or is there another way which I haven't thought of?

SeLLeRoNe · Nov 23, 2016

Are you using the php.ini from Custombuild or a custom version?

If is not custom, try. /usr/local/directadmin/custombuild/build php_ini

Best regards

Problems with MySQL after updating PHP with custombuild 2.0

Verified User

Super Moderator

Verified User

Super Moderator

Verified User

Super Moderator

Verified User

Super Moderator

Verified User

Super Moderator

Verified User

Verified User

Verified User

Super Moderator

Verified User

Super Moderator

Verified User

Super Moderator

Verified User

Super Moderator