Can't log in to SQL server

[mlevin77]

So I've activated the SQL server under applications (MariaDB), and reset its password (in case that was the problem), but neither SQL Buddy app nor the phoMyAdmin app can connect - they just say cannot connect or cannot log me in. What am I missing here?

[schumaku]

... send crystal ball on how your applications are configured to access the database.

Wild guess: Either change the applications to connect instead to 127.0.0.1 new to to localhost ... or enable the TCP/IP connection feature in the NAS SQL Server control panel.

[mlevin77]

> ... send crystal ball on how your applications are configured to access the database.

ok, let's forget my target applications; as I mentioned above let's just deal with phpMyAdmin app, which I understand is the standard way to access the database. I have not changed the config on that - am running it as installed, and there doesn't seem to be anything to change anyway on its UI. So presumably no crystal ball needed here as I'm using the very standard components, and just wondering what this error from phpMyAdmin means, and how to debug where it's failing (is the server even up? is it a login permissions issue or a communications issue? etc.).

> enable the TCP/IP connection feature in the NAS SQL Server control panel.

yep, have it enabled.

> change the applications to connect instead to 127.0.0.1 new to to localhost

so phpMyAdmin doesn't even ask for the IP of the server - the only thing that app page has is username and password. I'm using what I think is the default credentials for root (since I've reset). And it's saying

#2002 - No such file or directory<br />The server is not responding (or the local server's socket is not correctly configured).

[schumaku]

... the standard way to access the database. ... is the server even up?

Start here:

[~] # /mnt/ext/opt/mariadb/bin/mysql -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.44-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| qnote |
| test |
+--------------------+
5 rows in set (0.03 sec)

MariaDB [(none)]>

That's the (one and only) default.

Quickly downloaded the current phpMyAdmin code from https://www.phpmyadmin.net/ and extracted to the Web shared folder - access it straight away, ... and entered root / admin (on a default database config):

4.2.0 MariaDB phpMyAdmin 4.5.3.1.PNG

Have seen some alternate ideas and suggestions (just like installing a 3rd party phpmyadmin instead of the QNAP supplied one) - can't speak for these ... search for mySQL #2002.

[mlevin77]

Ok, so it fails at step 1:

[~] # /mnt/ext/opt/mariadb/bin/mysql -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[~] #

attached is a screengrab to show I've got the service running. I did notice that the file

/tmp/mysql.sock

doesn't exist, but

/tmp/mysql_mediadb.sock

does, so how do I tell it the .sock file is different (and why isn't it what it expects? I didn't change anything manually)

What do I do? thank you!

[schumaku]

There can be two MariaDB instances active - one for the Media Library whit the /tmp/mysql_mediadb.sock, plus another one when the NAS SQL service is enabled. Both relevant processes and sockets are around here:

[~] # ps -ef | grep sql
8821 admin 1752 S /bin/sh /usr/local/mariadb/bin/mysqld_safe --defaults-file=/usr/local/mariadb/my-mariadb.cnf --basedir=/usr/local/mariadb --datadir=/share/CACHEDEV1_DATA/.system/data --user=root --default-storage-engine=MyISAM...
9165 admin 12420 S /usr/local/mariadb/bin/mysqld --defaults-file=/usr/local/mariadb/my-mariadb.cnf --basedir=/usr/local/mariadb --datadir=/share/CACHEDEV1_DATA/.system/data --plugin-dir=/usr/local/mariadb/lib/plugin --user=root -
14630 admin 1748 S /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/config/my.cnf --datadir=/usr/local/mysql/var --tmpdir=/usr/local/mysql/tmp --pid-file=/var/lock/qmysql.pid --user=admin
14964 admin 4012 S /usr/local/mysql/bin/mysqld --defaults-file=/etc/config/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --plugin-dir=/usr/local/mysql/lib/plugin --user=admin --tmpdir=/usr/local/mysql/tmp --log...

[~] # ls /tmp/mysql*.sock
/tmp/mysql.sock= /tmp/mysql_mediadb.sock=


In /usr/local/mysql/var/ you find the MariaDB log file [NAShostname].err file (look at the process cmdline --log-error=....) ... this might unveil any potential issues.

[~] # cat /proc/14964/cmdline
/usr/local/mysql/bin/mysqld--defaults-file=/etc/config/my.cnf--basedir=/usr/local/mysql--datadir=/usr/local/mysql/var--plugin-dir=/usr/local/mysql/lib/plugin--user=admin--tmpdir=/usr/local/mysql/tmp--log-error=/usr/local/mysql/var/TS-853A.err--pid-file=/var/lock/qmysql.pid--socket=/tmp/mysql.sock--port=3306

[mlevin77]

# ps -ef | grep sql
4995 admin 960 S /bin/sh /usr/local/mariadb/bin/mysqld_safe --defaults-file=/usr/local/mariadb/my-mariadb.cnf --
5329 admin 3276 S /usr/local/mariadb/bin/mysqld --defaults-file=/usr/local/mariadb/my-mariadb.cnf --basedir=/usr/
26389 admin 536 S grep sql

so does this mean it's actually running? And if there's already an actual mysqld already there, can I connect to it and forget the Maria one? What port is the mysqld on?

# ls /tmp/mysql*.sock
/tmp/mysql.sock /tmp/mysql_mediadb.sock=

so only one .sock file and not the one that the myPhpAdmin is looking for.

# cat /proc/4995/cmdline
/bin/sh/usr/local/mariadb/bin/mysqld_safe--defaults-file=/usr/local/mariadb/my-mariadb.cnf--basedir=/usr/local/mariadb--datadir=/share/CACHEDEV1_DATA/.system/data--user=root--default-storage-engine=MyISAM--wait_timeout=2628000--pid-file=/var/lock/mariadb.pid--tmpdir=/share/CACHEDEV1_DATA/.system/tmp

# cat /proc/5329/cmdline
/usr/local/mariadb/bin/mysqld--defaults-file=/usr/local/mariadb/my-mariadb.cnf--basedir=/usr/local/mariadb--datadir=/share/CACHEDEV1_DATA/.system/data--plugin-dir=/usr/local/mariadb/lib/plugin--user=root--default-storage-engine=MyISAM--wait-timeout=2628000--tmpdir=/share/CACHEDEV1_DATA/.system/tmp--log-error=/share/CACHEDEV1_DATA/.system/data/GNAP.err--pid-file=/var/lock/mariadb.pid--socket=/tmp/mysql_mediadb.sock--port=3310

so apparently both of them run mariadb, and what's up with the .sock files?

thx!

[schumaku]

...and what's in /share/CACHEDEV1_DATA/.system/data/GNAP.err ?

[mlevin77]

...and what's in /share/CACHEDEV1_DATA/.system/data/GNAP.err ?

cat /share/CACHEDEV1_DATA/.system/data/GNAP.err
151115 21:52:50 mysqld_safe Starting mysqld daemon with databases from /share/CACHEDEV1_DATA/.system/data
151115 21:52:50 [Note] /usr/local/mariadb/bin/mysqld (mysqld 5.5.44-MariaDB) starting as process 5329 ...
151115 21:52:50 InnoDB: The InnoDB memory heap is disabled
151115 21:52:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins
151115 21:52:50 InnoDB: Compressed tables use zlib 1.2.3
151115 21:52:50 InnoDB: Initializing buffer pool, size = 128.0M
151115 21:52:50 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
151115 21:52:50 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
151115 21:52:50 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
151115 21:52:50 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
151115 21:52:52 InnoDB: Waiting for the background threads to start
151115 21:52:54 Percona XtraDB (http://www.percona.com) 5.5.43-MariaDB-37.2 started; log sequence number 0
151115 21:52:54 [Note] Plugin 'FEEDBACK' is disabled.
151115 21:52:54 [Note] Server socket created on IP: '127.0.0.1'.
151115 21:52:54 [Note] Event Scheduler: Loaded 0 events
151115 21:52:54 [Note] /usr/local/mariadb/bin/mysqld: ready for connections.
Version: '5.5.44-MariaDB' socket: '/tmp/mysql_mediadb.sock' port: 3310 MariaDB Server

so it looks like the .sock file is different than what myPhpAdmin expects (out of the box)? I thought this was the default/standard tool, how do other people use it (how can I be the only one with this problem)?
Worse yet, I did a port scan and it doesn't report anything listening on port 3310. So is my SQL server alive or not?

[schumaku]

so it looks like the .sock file is different than what myPhpAdmin expects (out of the box)?

No. Again: There is no process for the NAS user facing MariaDB server instance active, so there can't be a socket, too.

Worse yet, I did a port scan and it doesn't report anything listening on port 3310.

As the error log shows, the media library MariaDB server instance does bind on 127.0.0.1 - and interface you will never see or reach over the network.

-> https://helpdesk.qnap.com/

[mlevin77]

Sorry, I'm not understanding:

> No. Again: There is no process for the NAS user facing MariaDB server instance active, so there can't be a socket, too.

are you saying the right socket isn't present because the correct server process isn't running? Something was running, because the log says
Version: '5.5.44-MariaDB' socket: '/tmp/mysql_mediadb.sock' port: 3310 MariaDB Server
so it looks like it was launched at least, but then exited? (since I see nothing listening on 3310)?

> As the error log shows, the media library MariaDB server instance does bind on 127.0.0.1 - and interface you will never see or reach over the network.

so,
1) I should be able to reach it 127.0.0.1 from the NAS itself, right? I run myphpAdmin on the NAS itself (same localhost ip) but see nothing.
2) what do I change so that once the local problem is fixed, I can see it from other devices on the LAN?

[schumaku]

None of these...

Afraid, I have no idea why the user-facing MariaDB server does not start on your NAS at all. Anything we can see is the other MariaDB instance for the Media Library.

-> https://helpdesk.qnap.com/ and file a ticket if mot done already.

[thanosilva]

Have the same error.
After i re-initialize db in control panel >> sql server , everything works.

[aesculus]

Have the same error.
After i re-initialize db in control panel >> sql server , everything works.

Just wanted to bump this thread and state I had the same problem and this was the simple solution.

Not sure what caused the database to go south but this fix was easy to perform.