Can't log in to SQL server

Post your questions about Web Server usage and Apache + PHP + MySQL/SQLite web applications.
Post Reply
mlevin77
Know my way around
Posts: 201
Joined: Wed Jan 08, 2014 3:53 am
Contact:

Can't log in to SQL server

Post by mlevin77 » Thu Dec 31, 2015 8:35 pm

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?
-----
TS-879Pro (firmware 4.2.0, 8 hard drives in a RAID6, accessed from Apple machines)

User avatar
schumaku
Guru
Posts: 43672
Joined: Mon Jan 21, 2008 4:41 pm
Location: Kloten (Zurich), Switzerland -- Skype: schumaku
Contact:

Re: Can't log in to SQL server

Post by schumaku » Tue Jan 05, 2016 9:22 pm

... 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
Know my way around
Posts: 201
Joined: Wed Jan 08, 2014 3:53 am
Contact:

Re: Can't log in to SQL server

Post by mlevin77 » Tue Jan 05, 2016 10:32 pm

> ... 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).
-----
TS-879Pro (firmware 4.2.0, 8 hard drives in a RAID6, accessed from Apple machines)

User avatar
schumaku
Guru
Posts: 43672
Joined: Mon Jan 21, 2008 4:41 pm
Location: Kloten (Zurich), Switzerland -- Skype: schumaku
Contact:

Re: Can't log in to SQL server

Post by schumaku » Wed Jan 06, 2016 3:59 am

mlevin77 wrote:... 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.
You do not have the required permissions to view the files attached to this post.

mlevin77
Know my way around
Posts: 201
Joined: Wed Jan 08, 2014 3:53 am
Contact:

Re: Can't log in to SQL server

Post by mlevin77 » Wed Jan 06, 2016 6:16 pm

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!
-----
TS-879Pro (firmware 4.2.0, 8 hard drives in a RAID6, accessed from Apple machines)

User avatar
schumaku
Guru
Posts: 43672
Joined: Mon Jan 21, 2008 4:41 pm
Location: Kloten (Zurich), Switzerland -- Skype: schumaku
Contact:

Re: Can't log in to SQL server

Post by schumaku » Thu Jan 07, 2016 9:58 am

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
Know my way around
Posts: 201
Joined: Wed Jan 08, 2014 3:53 am
Contact:

Re: Can't log in to SQL server

Post by mlevin77 » Thu Jan 07, 2016 6:01 pm

# 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!
-----
TS-879Pro (firmware 4.2.0, 8 hard drives in a RAID6, accessed from Apple machines)

User avatar
schumaku
Guru
Posts: 43672
Joined: Mon Jan 21, 2008 4:41 pm
Location: Kloten (Zurich), Switzerland -- Skype: schumaku
Contact:

Re: Can't log in to SQL server

Post by schumaku » Thu Jan 07, 2016 7:58 pm

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

mlevin77
Know my way around
Posts: 201
Joined: Wed Jan 08, 2014 3:53 am
Contact:

Re: Can't log in to SQL server

Post by mlevin77 » Fri Jan 08, 2016 8:12 am

schumaku wrote:...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?
-----
TS-879Pro (firmware 4.2.0, 8 hard drives in a RAID6, accessed from Apple machines)

User avatar
schumaku
Guru
Posts: 43672
Joined: Mon Jan 21, 2008 4:41 pm
Location: Kloten (Zurich), Switzerland -- Skype: schumaku
Contact:

Re: Can't log in to SQL server

Post by schumaku » Mon Jan 11, 2016 2:00 am

mlevin77 wrote: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.

mlevin77 wrote: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
Know my way around
Posts: 201
Joined: Wed Jan 08, 2014 3:53 am
Contact:

Re: Can't log in to SQL server

Post by mlevin77 » Mon Jan 11, 2016 5:16 am

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?
-----
TS-879Pro (firmware 4.2.0, 8 hard drives in a RAID6, accessed from Apple machines)

User avatar
schumaku
Guru
Posts: 43672
Joined: Mon Jan 21, 2008 4:41 pm
Location: Kloten (Zurich), Switzerland -- Skype: schumaku
Contact:

Re: Can't log in to SQL server

Post by schumaku » Tue Jan 12, 2016 12:44 am

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
New here
Posts: 3
Joined: Wed Sep 14, 2016 7:18 pm

Re: Can't log in to SQL server

Post by thanosilva » Mon Oct 31, 2016 8:01 pm

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

User avatar
aesculus
Easy as a breeze
Posts: 315
Joined: Fri Dec 14, 2007 11:17 am

Re: Can't log in to SQL server

Post by aesculus » Wed Aug 21, 2019 6:57 am

thanosilva wrote: ↑
Mon Oct 31, 2016 8:01 pm
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.
Chris

Post Reply

Return to “Web Server & Applications (Apache + PHP + MySQL / SQLite)”