Error in user dashboard

[sarfarosh]

Hi Team,

I am monitoring lots of servers and network devices in Nagios xi.

I have created one user "nmsalerts" for network devices monitoring, and assigned all the network devices to this user.

When I login to this user "nmsalerts", I am not able to see any hosts/services and getting the below error :
--------------------------
SQL: SQL Error [ndoutils] : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9 SQL: SQL Error [ndoutils] : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9 SQL: SQL Error [ndoutils] : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9 SQL: SQL Error [ndoutils] : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9 SQL: SQL Error [ndoutils] : You have an error in your SQL syntax; check the manual that corresponds to your ...............................
--------------------------

Refer the attahced screenshot as well.

Please help me to get rid of this issue and make me able to see all the hosts, which I assigned to this user.

[lmiltchev]

I have created one user "nmsalerts" for network devices monitoring, and assigned all the network devices to this user.

You created this user by going to Admin->Manage Users->Add New User, not directly, by running mysql commands, correct? Are you having similar issues with other users?

Run the following commands, and show the output:

Code: Select all

uname -a
cat /etc/*release
mysql --version
tail -50 /var/log/mysqld.log

[sarfarosh]

Hi,

Thanks for your help.

Yes, I have followed these steps "Admin->Manage Users->Add New User" only, not directly. I have this same issue with all the network device users, server-users are working fine.

Please refer below information :
======================
# uname -a
Linux nod-nag-srv1.spectranet.in 2.6.32-573.12.1.el6.x86_64 #1 SMP Tue Dec 15 21:19:08 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

# cat /etc/redhat-release
CentOS release 6.7 (Final)

# mysql --version
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

# tail -50 /var/log/mysqld.log
160210 13:09:01 [Warning] /usr/libexec/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
160210 13:09:03 InnoDB: Initializing buffer pool, size = 8.0M
160210 13:09:03 InnoDB: Completed initialization of buffer pool
160210 13:09:03 InnoDB: Started; log sequence number 0 44233
160210 13:09:03 [Note] Event Scheduler: Loaded 0 events
160210 13:09:03 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
160211 15:49:12 [Note] /usr/libexec/mysqld: Normal shutdown

160211 15:49:12 [Note] Event Scheduler: Purging the queue. 0 events
160211 15:49:14 InnoDB: Starting shutdown...
160211 15:49:20 InnoDB: Shutdown completed; log sequence number 0 44233
160211 15:49:20 [Note] /usr/libexec/mysqld: Shutdown complete

160211 15:49:20 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160211 16:27:11 mysqld_safe Starting mysqld daemon with databases from /mysql/mysql
160211 16:27:11 [Warning] /usr/libexec/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
160211 16:27:11 InnoDB: Initializing buffer pool, size = 8.0M
160211 16:27:11 InnoDB: Completed initialization of buffer pool
160211 16:27:11 InnoDB: Started; log sequence number 0 44233
160211 16:27:11 [Note] Event Scheduler: Loaded 0 events
160211 16:27:11 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
160213 15:01:13 [Note] /usr/libexec/mysqld: Normal shutdown

160213 15:01:13 [Note] Event Scheduler: Purging the queue. 0 events
160213 15:01:15 InnoDB: Starting shutdown...
160213 15:02:46 InnoDB: Shutdown completed; log sequence number 0 44233
160213 15:02:46 [Note] /usr/libexec/mysqld: Shutdown complete

160213 15:02:46 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160213 15:02:47 mysqld_safe Starting mysqld daemon with databases from /mysql/mysql
160213 15:02:53 InnoDB: Initializing buffer pool, size = 8.0M
160213 15:02:53 InnoDB: Completed initialization of buffer pool
160213 15:03:23 InnoDB: Started; log sequence number 0 44233
160213 15:03:23 [ERROR] /usr/libexec/mysqld: unknown variable '--variable-max_connections=500'
160213 15:03:23 [ERROR] Aborting

160213 15:03:23 InnoDB: Starting shutdown...
160213 15:03:29 InnoDB: Shutdown completed; log sequence number 0 44233
160213 15:03:29 [Note] /usr/libexec/mysqld: Shutdown complete

160213 15:03:29 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160213 15:06:15 mysqld_safe Starting mysqld daemon with databases from /mysql/mysql
160213 15:06:15 InnoDB: Initializing buffer pool, size = 8.0M
160213 15:06:15 InnoDB: Completed initialization of buffer pool
160213 15:06:15 InnoDB: Started; log sequence number 0 44233
160213 15:06:15 [Note] Event Scheduler: Loaded 0 events
160213 15:06:15 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
======================

Awaiting your reply.

[lmiltchev]

160213 15:03:23 [ERROR] /usr/libexec/mysqld: unknown variable '--variable-max_connections=500'
160213 15:03:23 [ERROR] Aborting

It is possible that you have an entry in the my.cnf that is causing the issue. Can you show us the file?

Code: Select all

cat /etc/my.cnf

[sarfarosh]

Hi,

Please refer below the file "/etc/my.cnf" :
--------------------------------
# cat /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
datadir=/mysql/mysql
#socket=/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=1
max_allowed_packet=500M
max_connections=500
interactive_timeout=60
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#bind-address=203.122.61.122
bind-address=172.16.50.137
port=3306
#skip-networking

[client]
socket=/var/lib/mysql/mysql.sock
--------------------------------

I have tried to comment "max_connections=500" and then restarted mysqld, nagios and httpd services. But still we are getting the same error.
---------
symbolic-links=1
max_allowed_packet=500M
#max_connections=500
interactive_timeout=60
---------

Please help to resolve it asap.

[Box293]

What is the output of these commands:

Code: Select all

find / -name my.cnf
mysql --help --verbose

[sarfarosh]

Hi,

Please refer the below details :

Code: Select all

]# find / -name my.cnf
/etc/my.cnf

Code: Select all

# mysql --help --verbose
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: mysql [OPTIONS] [database]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table and field completion, but startup
                      and reconnecting may take a longer time. Disable with
                      --disable-auto-rehash.
  -A, --no-auto-rehash 
                      No automatic rehashing. One has to use 'rehash' to get
                      table and field completion. This gives a quicker start of
                      mysql and disables rehashing on reconnect.
  -B, --batch         Don't use history file. Disable interactive behavior.
                      (Enables --silent.)
  --character-sets-dir=name 
                      Directory for character set files.
  --column-type-info  Display column type information.
  -c, --comments      Preserve comments. Send comments to the server. The
                      default is --skip-comments (discard comments), enable
                      with --comments.
  -C, --compress      Use compression in server/client protocol.
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  --debug-check       Check memory and open file usage at exit.
  -T, --debug-info    Print some debug info at exit.
  -D, --database=name Database to use.
  --default-character-set=name 
                      Set the default character set.
  --delimiter=name    Delimiter to be used.
  -e, --execute=name  Execute command and quit. (Disables --force and history
                      file.)
  -E, --vertical      Print the output of a query (rows) vertically.
  -f, --force         Continue even if we get an SQL error.
  -G, --named-commands 
                      Enable named commands. Named commands mean this program's
                      internal commands; see mysql> help . When enabled, the
                      named commands can be used from any line of the query,
                      otherwise only from the first line, before an enter.
                      Disable with --disable-named-commands. This option is
                      disabled by default.
  -g, --no-named-commands 
                      Named commands are disabled. Use \* form only, or use
                      named commands only in the beginning of a line ending
                      with a semicolon (;). Since version 10.9, the client now
                      starts with this option ENABLED by default. Disable with
                      '-G'. Long format commands still work from the first
                      line. WARNING: option deprecated; use
                      --disable-named-commands instead.
  -i, --ignore-spaces Ignore space after function names.
  --local-infile      Enable/disable LOAD DATA LOCAL INFILE.
  -b, --no-beep       Turn off beep on error.
  -h, --host=name     Connect to host.
  -H, --html          Produce HTML output.
  -X, --xml           Produce XML output.
  --line-numbers      Write line numbers for errors.
  -L, --skip-line-numbers 
                      Don't write line number for errors.
  -n, --unbuffered    Flush buffer after each query.
  --column-names      Write column names in results.
  -N, --skip-column-names 
                      Don't write column names in results.
  -O, --set-variable=name 
                      Change the value of a variable. Please note that this
                      option is deprecated; you can set variables directly with
                      --variable-name=value.
  --sigint-ignore     Ignore SIGINT (CTRL-C).
  -o, --one-database  Ignore statements except those that occur while the
                      default database is the one named at the command line.
  --pager[=name]      Pager to use to display results. If you don't supply an
                      option, the default pager is taken from your ENV variable
                      PAGER. Valid pagers are less, more, cat [> filename],
                      etc. See interactive help (\h) also. This option does not
                      work in batch mode. Disable with --disable-pager. This
                      option is disabled by default.
  --no-pager          Disable pager and print to stdout. See interactive help
                      (\h) also. WARNING: option deprecated; use
                      --disable-pager instead.
  -p, --password[=name] 
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --prompt=name       Set the mysql prompt to this value.
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -q, --quick         Don't cache result, print it row by row. This may slow
                      down the server if the output is suspended. Doesn't use
                      history file.
  -r, --raw           Write fields without conversion. Used with --batch.
  --reconnect         Reconnect if the connection is lost. Disable with
                      --disable-reconnect. This option is enabled by default.
  -s, --silent        Be more silent. Print results with a tab as separator,
                      each row on new line.
  -S, --socket=name   The socket file to use for connection.
  --ssl               Enable SSL for connection (automatically enabled with
                      other flags).Disable with --skip-ssl.
  --ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
                      --ssl).
  --ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
  --ssl-cert=name     X509 cert in PEM format (implies --ssl).
  --ssl-cipher=name   SSL cipher to use (implies --ssl).
  --ssl-key=name      X509 key in PEM format (implies --ssl).
  --ssl-verify-server-cert 
                      Verify server's "Common Name" in its cert against
                      hostname used when connecting. This option is disabled by
                      default.
  -t, --table         Output in table format.
  --tee=name          Append everything into outfile. See interactive help (\h)
                      also. Does not work in batch mode. Disable with
                      --disable-tee. This option is disabled by default.
  --no-tee            Disable outfile. See interactive help (\h) also. WARNING:
                      Option deprecated; use --disable-tee instead.
  -u, --user=name     User for login if not current user.
  -U, --safe-updates  Only allow UPDATE and DELETE that uses keys.
  -U, --i-am-a-dummy  Synonym for option --safe-updates, -U.
  -v, --verbose       Write more. (-v -v -v gives the table output format).
  -V, --version       Output version information and exit.
  -w, --wait          Wait and retry if connection is down.
  --connect_timeout=# Number of seconds before connection timeout.
  --max_allowed_packet=# 
                      The maximum packet length to send to or receive from
                      server.
  --net_buffer_length=# 
                      The buffer size for TCP/IP and socket communication.
  --select_limit=#    Automatic limit for SELECT when using --safe-updates.
  --max_join_size=#   Automatic limit for rows in a join when using
                      --safe-updates.
  --secure-auth       Refuse client connecting to server if it uses old
                      (pre-4.1.1) protocol.
  --server-arg=name   Send embedded server this as a parameter.
  --show-warnings     Show warnings after every statement.

Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- -----------------------------
auto-rehash                       TRUE
character-sets-dir                (No default value)
column-type-info                  FALSE
comments                          FALSE
compress                          FALSE
debug-check                       FALSE
debug-info                        FALSE
database                          (No default value)
default-character-set             latin1
delimiter                         ;
vertical                          FALSE
force                             FALSE
named-commands                    FALSE
ignore-spaces                     FALSE
local-infile                      FALSE
no-beep                           FALSE
host                              (No default value)
html                              FALSE
xml                               FALSE
line-numbers                      TRUE
unbuffered                        FALSE
column-names                      TRUE
sigint-ignore                     FALSE
port                              0
prompt                            mysql> 
quick                             FALSE
raw                               FALSE
reconnect                         TRUE
socket                            /var/lib/mysql/mysql.sock
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
table                             FALSE
user                              (No default value)
safe-updates                      FALSE
i-am-a-dummy                      FALSE
connect_timeout                   0
max_allowed_packet                16777216
net_buffer_length                 16384
select_limit                      1000
max_join_size                     1000000
secure-auth                       FALSE
show-warnings                     FALSE
[root@nagios ~]# 

[sarfarosh]

Hi Team,

Please refer the attached screenshot of user-settings. Let me know if there is something wrong. I am using this same setting for other working server-users.

[rkennedy]

What version of PHP and Apache are you running? I tried to replicate this, and was unable to.

[sarfarosh]

Hi,

Please refer the below details :

Code: Select all

# php --version
PHP 5.3.3 (cli) (built: Jul  9 2015 17:39:00) 
Copyright (c) 1997-2010 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies

[root@nod-nag-srv1 ~]# httpd -V
Server version: Apache/2.2.15 (Unix)
Server built:   Dec 15 2015 15:50:14
Server's Module Magic Number: 20051115:25
Server loaded:  APR 1.3.9, APR-Util 1.3.9
Compiled using: APR 1.3.9, APR-Util 1.3.9
Architecture:   64-bit
Server MPM:     Prefork
  threaded:     no
    forked:     yes (variable process count)

As I told you, there are some users (server users) which are working fine. And I have noticed that the users which is having issue some time comes up and showing correct hosts......but it comes up just only for 2 minutes then again go down and showing SQL error (already shared).

Please give me some suggestion, which I can try here.

Thank you