Forums

Resolved
0 votes
Anyone ever have the this pop up when first logging into their Webconfig? I noticed it the day after spending some time the previous evening looking through my website's database using phpMyAdmin. I had made one edit in the cell of a log table and verified it showed up in the backend of the website. It did and there were no issues. That's all I can think of that I did, except I did do a yum update this a.m. that included content filter definitions and a miniupnp update.

Ooooops: [error ] gsoap connect: ()

Man it gave me a scare! I would get a white page with this error text on it, but then if I went to login again the dashboard would show up saying I was already logged in. However, I discovered a lot of other stuff gone haywire.

<ul>
Kopano-server was down. Log showed database access error, can't connect to mysql server on localhost.
Couldn't access user accounts in the Webconfig. Got the same oops fault shown above.
System log showed a few exception errors related to /usr/clearos/apps/reports_database noting can't connect to mysql server at localhost
Mariadb log showed different website tables crashed and would have to be repaired.
system-mariadb service was down.
Netify-awa service was down.
Couldn't access website, but web server was running.
</ul>

The mariadb service itself was running through all of this; just not the system-mariadb service. There may have been more, but this was what I noticed prior to rebooting my ClearOS gateway. Everything is working now, but what in the world!? I've had this gateway running in our office for maybe 3 months now with only a configuration backup of it. Now I'd like to have a complete backup of it. Any suggestions on that? Can BackupPC backup the server it's running from, say on a big usb drive? Anyone got a good forum link for that? I want to be able to revert back to the latest working gateway that I had, since it's a small business solution for our office.
Wednesday, February 06 2019, 07:11 PM
Share this post:
Responses (32)
  • Accepted Answer

    Monday, February 11 2019, 04:19 PM - #Permalink
    Resolved
    0 votes
    --nodeps is only relevant for uninstalling and only works with the rpm command and not yum. As it happens, it would have been safe to remove the packages with yum as they would not have removed other dependencies.

    There shouldn't be a problem installing dependencies when you install a package.

    If you do reinstall the reports, you now know that you can easily remove them. If you want the reports, try reinstalling them.
    Like
    1
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, February 06 2019, 08:28 PM - #Permalink
    Resolved
    0 votes
    I've never seen this one before. The miniupnpd update was trivial (and was released weeks ago) unless you missed earlier update. All if did was make sure it didn't die if the server was configured with out a LAN NIC. Miniupnpd does not touch any database. I wouldn't know how to track down what happened.

    For backups you could try the Bare Metal Backup app. I have not tried it. Some use it OK, but for others it may have USB detection issues. All you can do is try it.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 12:16 AM - #Permalink
    Resolved
    0 votes
    Doesn’t Bare Metal do home directories and configuration files? I really would like a whole server backup, so about 25GB at this point. Wouldn’t mind it being dynamic in nature so it would backup once a week and only those folders and files that have changed, like Crash Plan Pro. We’re already using that and it’s got an app for Redhat, but it’s a desktop GUI and that won’t work on my COS gateway.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 10:00 AM - #Permalink
    Resolved
    0 votes
    I've just had a look at BMB and it backs up home directories, configuration files (like the Configuration Backup and Restore) and flexshares, which includes all web sites created through the Web Server app. So, you're right, it does not do everything.

    There is in interesting forum thread here with a link to https://gist.github.com/morhekil/8382294. The script is possibly a bit OTT and over parameterised, but using rsync with the --link-dest option is great as it really reduces the backup volume and traffic. I use it to back up e-mails and config data to a remote Pi and other files and folders to a more local device, but you will need to choose all the data you back up.

    Dave Loper indicated in a thread that you could run another instance of ClearOS running BackupPC and that could back up your primary ClearOS server.

    There seem to be all sorts of other backup solutions for Linux. I think there used to be a MondoRescue package in the past but Google will be your friend.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 01:54 PM - #Permalink
    Resolved
    0 votes
    Man, I woke up this morning and it came to me instantly. Ever have that happen? I mentioned in my original post that one of the yum updates I installed was a content filter update. It shows up in my Webconfig as
    clearsdn-content-filter-6.1-20190128.134 ... 	Updated 	Feb 06, 08:11:17

    I'm afraid this was a horrible mistake and has broken my database. When I first built my COS gateway, I included the content filter and its update service from ClearSDN. Later, I tried to install GM from the Marketplace and it said I can't have both, so I uninstalled the content filter (see where I'm going with this?). I at one time a while ago, also went in and disabled the update service in my ClearCenter account, I'm thinking not before this update had been sitting prepared to be installed. It just took my density to initiate the bomb. I'm sure the content filter update pushed data into the db where there were no tables for the content filter since I uninstalled it prior to installing the GM.

    The system-mariadb service was down again this morning, so I couldn't access user accounts. A restart allows it, but has not taken the sting away. Kopano webaccess is riddled with errors when logging into it and looking through different folders (i.e. inbox, junkmail, etc). The Webconfig still shows an error page when first logging into the dashboard. If I try again it will go to the dashboard and say already logged in. The dashboard shows mysqld is consuming cpu usage, jumping all over in its percent of use, but always the biggest user. I've tried restarting the db services, but no change. The system-mariadb log keeps growing to a huge size, 230MB prior to rotating yesterday. I don't even know where to begin.

    Logging in as root to phpMyAdmin, I see the Main Database that shows tables for information schema, mysql, performance schema, my website, and configuration_storage which I had created a while ago when phpMyAdmin recommended it. I see there's a System Database and System Database-SSO in the dropdown menu, but when I choose either one of them, I'm taken to the login page and my root credentials won't let me in. What a mess.

    I do have configuration backup running, but I'm guessing this won't restore anything to do with my database.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 02:54 PM - #Permalink
    Resolved
    0 votes
    Man, is there a way to stop the logging for system-mariadb!!? The log just keeps growing and consuming cpu load. It's crazy!
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 03:24 PM - #Permalink
    Resolved
    0 votes
    Hi Dirk,
    If system-mariadb is using rsyslogd for logging, you can create a file called /etc/rsyslog.d/{anything}.conf and put some filters in there. I have put mine below so you can see how to filter:
    # Reference = http://wiki.rsyslog.com/index.php/Filtering_by_program_name
    # Suppress apcupsd from /var/log/messages as they also go to /var/log/apcupsd.events
    if $programname == 'apcupsd' then stop

    # Suppress clamd database check messages as they got to /var/log/clamav/clamd.log
    # if $programname == 'clamd' and $msg contains 'SelfCheck: Database status OK.' then stop

    # Suppress Some Snort start up messages
    if $programname == 'snort' and $pri-text == 'daemon.notice' and not ($msg contains 'Commencing packet processing' or $msg contains_i 'error' or ( $msg contains_i 'warning' and not $msg contains_i 'flowbits' )) then stop
    # Snort exiting

    # Suppress Openvpn MAMAGEMENT messages
    if $programname == 'openvpn' and $msg contains 'MANAGEMENT' then stop


    # Drop firewall Bittorrent messages
    if $programname == 'kernel' and $msg contains 'DPT=51413' then stop

    # Split out Firewall messages
    #if $programname == 'kernel' and $msg regex "IN.*OUT" then -/var/log/firewall
    if $programname == 'kernel' and $msg contains 'IN=' and $msg contains 'OUT=' then -/var/log/firewall
    & stop

    # Split out OpenVPN messages
    #if ($programname == 'openvpn' and ($msg contains 'IV_' and not $msg contains 'IV_VER')) then stop
    #if $programname == 'openvpn' and ( $msg contains 'IV_PROTO' or $msg contains 'IV_LZ' or $msg contains 'IV_COMP_STUB' or $msg contains 'IV_TCPNL' or $msg contains 'inconsistently' or $msg contains 'IV_NCP' ) then stop
    #if $programname == 'openvpn' then -/var/log/openvpn
    #& stop

    # Crappy systemd messages - use "systemd-analyze set-log-level notice". Query, was it INFO before?
    # now set in /etc/systemd/system.conf
    #if ($programname == 'systemd' and $msg startswith 'Start' and $msg contains 'Session' and $msg contains 'of user root') then stop
    #if ($programname == 'systemd' and ($msg startswith 'Start' and $msg contains 'Session' and $msg contains 'of user root') or $msg contains 'user-0.slice.') then stop

    # polkitd messages
    if ($programname == 'polkitd' and $msg contains 'egistered Authentication Agent for unix-process:') then stop

    # arpwatch flip flop messages
    if ($programname == 'arpwatch' and $msg contains '0.0.0.0') then stop

    # Did try this for a while: https://lists.freedesktop.org/archives/systemd-devel/2014-June/019853.html
    # Possibly high swap usage

    # Suppress smartd device comingout of standby messages
    if $programname == 'smartd' and $msg contains 'is back in ACTIVE or IDLE mode, resuming checks' then stop

    # Stairs camera dhcp messages
    #if ($programname == 'dnsmasq-dhcp' and $msg contains '172.17.2.102') then stop

    # ProFTPD
    if ($programname == 'proftpd' and $msg contains 'ourfamily') then stop
    if ($programname == 'proftpd' and $msg contains 'Unable to open config file: /etc/security/pam_env.conf: Permission denied') then stop
    if ($programname == 'proftpd' and $msg contains 'Failed to connect to system bus: Permission denied') then stop
    if ($programname == 'proftpd' and $msg contains 'error opening connection to nslcd: Permission denied') then stop
    if ($programname == "systemd-logind") and (($msg contains "New session" and $msg contains "ourfamily") or $msg contains "Removed session") then stop

    # DNSMASQ DHCP LAN messages
    if $programname == 'dnsmasq-dhcp' then -/var/log/dnsmasq-dhcp
    & stop

    # Docker Messages
    if $programname == 'dockerd-current' then -/var/log/docker
    & stop
    if ($programname == 'docker-compose' and ($msg contains ' INFO ' or $msg contains '[INFO' or $msg contains '=info' or $msg contains 'INFO/')) then stop
    if $programname == 'docker-compose' then -/var/log/docker
    & stop

    # ClearGlass Messages
    if ($programname == 'journal' and ($msg contains ' INFO ' or $msg contains '[INFO' or $msg contains '=info' or $msg contains 'INFO/')) then stop
    if $programname == 'journal' and $msg contains 'slapd.service' then -/var/log/openldap
    & stop
    if $programname == 'journal' then -/var/log/clearglass
    & stop

    Most of the lines are commented out, but perhaps look at the ProFTPD ones. I don't have any system-mariadb logs, but looking at one of the other servers I can access, I don't think it uses rsyslogd as there is no programname name in them (normally the second field after the date/time. If it does work, the "stop" stops matching lines from being logged.

    To throw a sledgehammer at it, try editing /usr/clearos/sandbox/etc/my.cnf and redirecting the logs to /dev/null but then you would lose all the diagnostics.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 03:30 PM - #Permalink
    Resolved
    0 votes
    Going down to your next post, the password for system-mariadb is in /var/clearos/system_database/root. For the main password, it is however you initially set it up, but most ClearOS programs do not use it.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 04:01 PM - #Permalink
    Resolved
    0 votes
    Thanks Nick. I don't think it's using rsyslogd. I put
    # System-Mariadb messages
    if $programname == 'system-mariadb' then -/var/log/system-mariadb
    & stop

    in a conf file for it and restarted rsyslogd and it's still eating up cpu process, not to mention the log keeps growing. I force rotated the system-mariadb log to monitor its growth and it definitely keeps swelling.

    I've put
    log-error=/dev/null 2>&1
    in /usr/clearos/sandbox/etc/my.cnf, but I haven't saved it yet or restarted system-mariadb (assuming that's the service that reads my.cnf). I'm thinking that might just dump the logging, but it wouldn't stop system-mariadb from continuing its cpu hogging.

    If I had to repair tables, I wouldn't know where to begin because the damage has affected many apps that use the database, i.e. Kopano, openldap, my website, Webconfig, etc.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 04:56 PM - #Permalink
    Resolved
    0 votes
    From the logs I saw I didn't think system-mariadb logs were under rsyslogd control :(

    AFAIK, openldap, web sites and webconfig do not use system-mariadb. Kopano/Zarafa, Openfire, Roundcubemail and Nextcloud do (on my system) as do some of the reports.

    Are you able to see from the command "top" what command is consuming the CPU. Note the PID then do a "ps aux | grep your_process_id" to find the command doing the hogging.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 05:44 PM - #Permalink
    Resolved
    0 votes
    I can't see that the content-filter-updates touch system-mariadb. They rpm unpacks them into /var/clearos/content_filter_updates/blacklists/ then symlinks them back into /etc/dansguardian and /etc/dansguardian-av/lists. It then runs a script /usr/clearos/apps/content_filter/deploy/update-lists, but as I don't have the content filter installed I don't know what the script does. I suspect it does not touch system-mariadb.

    Have you run out of space anywhere:
    df -h
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 07:00 PM - #Permalink
    Resolved
    0 votes
    Are you able to see from the command "top" what command is consuming the CPU. Note the PID then do a "ps aux | grep your_process_id" to find the command doing the hogging.


    [root@gateway ~]# ps aux|grep 13035
    root 3105 0.0 0.0 112708 960 pts/0 S+ 13:52 0:00 grep --color=auto 13035
    system-+ 13035 2.5 0.1 116792 5152 ? Ss 09:16 7:05 /bin/sh /usr/clearos/sandbox/usr/bin/mysqld_safe --basedir=/usr


    There are two doing the hogging. The one shown above shows up as mysqld_safe in response to the "ps aux | grep" command. The one doing the most hogging is mysqld, but it keeps coming and going when monitoring cpu usage via "top". There's always a different PID for it. mysqld_safe keeps coming and going, but always with the same PID. The user for both is "system-+"

    Have you run out of space anywhere:

    No, plenty of space. I have a 2T hard drive.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 07:04 PM - #Permalink
    Resolved
    0 votes
    Dirk Albring wrote:

    No, plenty of space. I have a 2T hard drive.
    It depends how it is partitioned. Can you do the "df -h"?
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 07:07 PM - #Permalink
    Resolved
    0 votes
    [root@gateway ~]# df -h
    Filesystem Size Used Avail Use% Mounted on
    /dev/mapper/clearos-root 1.9T 24G 1.8T 2% /
    devtmpfs 2.0G 0 2.0G 0% /dev
    tmpfs 2.0G 84K 2.0G 1% /dev/shm
    tmpfs 2.0G 210M 1.8G 11% /run
    tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup
    /dev/sda1 1014M 163M 852M 17% /boot
    tmpfs 395M 0 395M 0% /run/user/981
    tmpfs 395M 0 395M 0% /run/user/993
    tmpfs 395M 0 395M 0% /run/user/0
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 07:12 PM - #Permalink
    Resolved
    0 votes
    Do you have any snippets of the mad logging?

    It is possible that the db is trying to do a recovery, hence the proc spike.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 07:22 PM - #Permalink
    Resolved
    0 votes
    If by mad logging you mean the system-mariadb log...
    Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

    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.
    190207 14:20:07 mysqld_safe Number of processes running now: 0
    190207 14:20:07 mysqld_safe mysqld restarted
    190207 14:20:07 [Note] /usr/clearos/sandbox/usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 20076 ...
    190207 14:20:08 InnoDB: The InnoDB memory heap is disabled
    190207 14:20:08 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    190207 14:20:08 InnoDB: Compressed tables use zlib 1.2.7
    190207 14:20:08 InnoDB: Using Linux native AIO
    190207 14:20:08 InnoDB: Initializing buffer pool, size = 500.0M
    190207 14:20:08 InnoDB: Completed initialization of buffer pool
    190207 14:20:08 InnoDB: highest supported file format is Barracuda.
    190207 14:20:08 InnoDB: Starting crash recovery from checkpoint LSN=86418190477
    InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
    190207 14:20:08 InnoDB: Starting final batch to recover 10 pages from redo log
    190207 14:20:09 InnoDB: Waiting for the background threads to start
    190207 14:20:10 Percona XtraDB (http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 86418192998
    190207 14:20:10 [Note] Plugin 'FEEDBACK' is disabled.
    190207 14:20:10 [Note] Server socket created on IP: '127.0.0.1'.
    190207 14:20:10 [Note] Event Scheduler: Loaded 0 events
    190207 14:20:10 [Note] /usr/clearos/sandbox/usr/libexec/mysqld: ready for connections.
    Version: '5.5.60-MariaDB' socket: '/var/lib/system-mysql/mysql.sock' port: 3308 MariaDB Server
    InnoDB: Error in pages 189451 and 221370 of index "PRIMARY" of table "reports"."proxy"
    InnoDB: broken FIL_PAGE_NEXT or FIL_PAGE_PREV links
    190207 14:20:16 InnoDB: Page dump in ascii and hex (16384 bytes):

    InnoDB: Page lsn 20 213735116, low 4 bytes of lsn at page end 213735116
    InnoDB: Page number (if stored to page already) 16496,
    InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
    InnoDB: Page may be an index page where index id is 1698
    InnoDB: (index "PRIMARY" of table "reports"."proxy")
    InnoDB: Corruption of an index tree: table "reports"."proxy", index "PRIMARY",
    InnoDB: father ptr page no 12782, child page no 221370
    PHYSICAL RECORD: n_fields 23; compact format; info bits 0

    InnoDB: You should dump + drop + reimport the table to fix the
    InnoDB: corruption. If the crash happens at the database startup, see
    InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html about
    InnoDB: forcing recovery. Then dump + drop + reimport.
    190207 14:20:16 InnoDB: Assertion failure in thread 139649187772160 in file btr0btr.c line 1330
    InnoDB: We intentionally generate a memory trap.
    InnoDB: Submit a detailed bug report to https://jira.mariadb.org/
    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.5/en/forcing-innodb-recovery.html
    InnoDB: about forcing recovery.
    190207 14:20:16 [ERROR] 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.

    To report this bug, see http://kb.askmonty.org/en/reporting-bugs

    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.

    Server version: 5.5.60-MariaDB
    key_buffer_size=134217728
    read_buffer_size=131072
    max_used_connections=3
    max_threads=153
    thread_count=3
    It is possible that mysqld could use up to
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 466718 K bytes of memory
    Hope that's ok; if not, decrease some variables in the equation.

    Thread pointer: 0x55fd614abd00
    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 = 0x7f029c3aed80 thread_stack 0x48000
    /usr/clearos/sandbox/usr/libexec/mysqld(my_print_stacktrace+0x3d)[0x55fd5eba7cbd]
    /usr/clearos/sandbox/usr/libexec/mysqld(handle_fatal_signal+0x515)[0x55fd5e7bc4a5]
    sigaction.c:0(__restore_rt)[0x7f02a38eb6d0]
    :0(__GI_raise)[0x7f02a2016277]
    :0(__GI_abort)[0x7f02a2017968]
    /usr/clearos/sandbox/usr/libexec/mysqld(+0x658378)[0x55fd5e970378]
    /usr/clearos/sandbox/usr/libexec/mysqld(+0x6609d2)[0x55fd5e9789d2]
    /usr/clearos/sandbox/usr/libexec/mysqld(+0x6622fe)[0x55fd5e97a2fe]
    /usr/clearos/sandbox/usr/libexec/mysqld(+0x5f4669)[0x55fd5e90c669]
    /usr/clearos/sandbox/usr/libexec/mysqld(_ZN7handler8ha_checkEP3THDP15st_ha_check_opt+0x7a)[0x55fd5e7c1b0a]
    /usr/clearos/sandbox/usr/libexec/mysqld(+0x4387e3)[0x55fd5e7507e3]
    /usr/clearos/sandbox/usr/libexec/mysqld(_ZN21Check_table_statement7executeEP3THD+0xbb)[0x55fd5e7511cb]
    /usr/clearos/sandbox/usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x18c9)[0x55fd5e68bed9]
    /usr/clearos/sandbox/usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x125)[0x55fd5e691445]
    /usr/clearos/sandbox/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1750)[0x55fd5e6934a0]
    /usr/clearos/sandbox/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x1c2)[0x55fd5e745a22]
    /usr/clearos/sandbox/usr/libexec/mysqld(handle_one_connection+0x4a)[0x55fd5e745aca]
    pthread_create.c:0(start_thread)[0x7f02a38e3e25]
    /usr/lib64/libc.so.6(clone+0x6d)[0x7f02a20debad]

    Trying to get some variables.
    Some pointers may be invalid and cause the dump to abort.
    Query (0x7f023c004bf8): CHECK TABLE `proxy`
    Connection ID (thread ID): 3
    Status: NOT_KILLED
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 07:28 PM - #Permalink
    Resolved
    0 votes
    Wow!! Sorry about that last contribution. If you witnessed that mess I copied and pasted from my system-mariadb log, there was a bunch of SQL hex data mixed in there that wreaked havoc on the post. I edited it and deleted the hex lines of text.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 07:56 PM - #Permalink
    Resolved
    0 votes
    Nick said:
    I can't see that the content-filter-updates touch system-mariadb. They rpm unpacks them into /var/clearos/content_filter_updates/blacklists/ then symlinks them back into /etc/dansguardian and /etc/dansguardian-av/lists. It then runs a script /usr/clearos/apps/content_filter/deploy/update-lists, but as I don't have the content filter installed I don't know what the script does. I suspect it does not touch system-mariadb.

    I don't see any tables for content filter in the main or the system database. On top of that, you would think when I installed the update via yum, it wouldn't have accepted it, what with me not having the content filter installed. I do have a blacklists folder under /var/clearos/content_filter_updates/blacklists full of blacklists having yesterdays date, so that makes me feel somewhat better, but there's still no explanation then as to why mariadb is having a conniption.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 08:36 PM - #Permalink
    Resolved
    0 votes
    Interesting. I wonder if it is pointing to a problem with the proxy table? That is one of the report tables. If it is only for reporting, I wonder if the table can be dropped, then, if needed, the proxy reports (if it is them) reloaded by yum to re-initialise them.

    If I were a gambler, I'd drop the proxy and proxy_domains tables then re-initialise them by running:
    /usr/sbin/initialize-report-tables proxy_report


    Dave will be better at this than me.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 09:08 PM - #Permalink
    Resolved
    0 votes
    Man, did I get hacked!? This is showing up spattered throughout the system-mariadb log in the midst of all that hex data I mentioned previously:

    asc http://c3.prod.playlists.ihrhls.com

    That might explain why my last post went haywire when I copied and pasted all that text from the log.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 09:37 PM - #Permalink
    Resolved
    0 votes
    Dirk - Is this the same system that has the JoomVideos problem?
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 09:57 PM - #Permalink
    Resolved
    0 votes
    Yes it is Tony
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, February 07 2019, 10:48 PM - #Permalink
    Resolved
    0 votes
    Dirk, from the JoomBoost web-site it appears that JoomVideos uses SQL and hence MariaDB - just another complication...
    The reply is currently minimized Show
  • Accepted Answer

    Friday, February 08 2019, 12:19 AM - #Permalink
    Resolved
    0 votes
    Yeah Tony, Joomla uses the database for many features on your website, so a lot of the Joomla apps will use it too. JoomVideos stores information about users and videos in the database. Your last comment got me hoping. I had experimented with uploading a bunch of different video types after you got me thinking on the other thread, so I deleted them and confirmed they're no longer in the db. Then I restarted a few services on my COS gateway, but it didn't change anything. system-mariadb is still hogging the cpu and filling up its log files at a pretty good click I might add.

    I did add a custom firewall rule to drop all packets from that malware site's url on the INPUT table, but that didn't do anything for me. I listed below what I'm seeing in the log, where it starts with "acs", but some instances in the log have the word "GET" in front of the malware site's url. GET what?
    The reply is currently minimized Show
  • Accepted Answer

    Friday, February 08 2019, 08:48 AM - #Permalink
    Resolved
    0 votes
    Nick Howitt wrote:

    Interesting. I wonder if it is pointing to a problem with the proxy table? That is one of the report tables. If it is only for reporting, I wonder if the table can be dropped, then, if needed, the proxy reports (if it is them) reloaded by yum to re-initialise them.

    If I were a gambler, I'd drop the proxy and proxy_domains tables then re-initialise them by running:
    /usr/sbin/initialize-report-tables proxy_report


    Dave will be better at this than me.
    Uninstalling the proxy report then dropping the tables may be a better way to go. I'll try and catch up with Dave this evening.
    The reply is currently minimized Show
  • Accepted Answer

    Friday, February 08 2019, 01:46 PM - #Permalink
    Resolved
    0 votes
    Thanks Nick. The system-mariadb service was down again this morning on my end of the world. This is getting frustrating because there's nothing clearly pointing to what happened to cause this.
    The reply is currently minimized Show
  • Accepted Answer

    Friday, February 08 2019, 02:04 PM - #Permalink
    Resolved
    0 votes
    If you don't use the proxy, you may as well remove the proxy report. Try:
    rpm -e app-proxy-report app-proxy-report-core --no-deps
    I've just removed it on mine and can confirm that it does not remove the tables from system-mariadb. That would be the next step.

    I should be speaking to Dave later today.
    The reply is currently minimized Show
  • Accepted Answer

    Friday, February 08 2019, 02:21 PM - #Permalink
    Resolved
    0 votes
    If you don't use the proxy, you may as well remove the proxy report.

    I do use the proxy in our office to help improve browsing, since a good dozen people could be browsing at once during work hours. The reports help in monitoring user usage too. I've always had good success with the web proxy in past Clark Connect and ClearOS systems, especially when I was using the content filter. I didn't realize up until now that it was knit so tight with the database.

    I am getting no User Management>Filter and Proxy Reports showing up in the Webconfig. All boxes are blank saying, "Loading" or "Nothing to report...", but there's definitely online traffic in the office.
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, February 09 2019, 09:25 AM - #Permalink
    Resolved
    0 votes
    Dave contacted me late yesterday. He thinks it is OK to uninstall the proxy reports then drop the reports database as I suggested, but back it up first. You can backup the whole system-mariadb database with:
    /usr/clearos/sandbox/usr/bin/mysqldump --port=3308 --single-transaction --routines --all-databases \
    --password=$(awk '{print $3}' /var/clearos/system_database/root) > /tmp/filename.sql
    (cribbed from the Kopano docs). It may be an idea to dump the proxy and proxy_domains table separately as well so you don't overwrite the Kopano database if you do a restore. Check the mysqldump documentation.

    Thinking about it, it may be better to stop Kopano as well while you try it so you don't lose any e-mails if you need to do a restore. The SMTP server should still queue the e-mails until Kopano comes back up.

    After that you can always reinstall the reports.
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, February 09 2019, 02:15 PM - #Permalink
    Resolved
    0 votes
    Thanks NIck and Dave. I’ll do that then by Monday morning. I’ll let you know if it works. Don’t suppose you have a command line format for removing the reports table? I suppose I could always do it in phpMyAdmin as well, but I think command line would be cleaner.

    It may be an idea to dump the proxy and proxy_domains table separately as well so you don't overwrite the Kopano database if you do a restore. Check the mysqldump documentation.


    I’m assuming you mean dump (i.e. backup) the whole database minus the proxy and proxy_domains tables?
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, February 09 2019, 02:37 PM - #Permalink
    Resolved
    0 votes
    References here and here. You can even build it into a single command including logging onto mariadb but that is not my strong point.

    Here is a one-liner I hacked from elsewhere a while ago:
    /usr/clearos/sandbox/usr/bin/mysql -D reports -u reports -p`cut -f3 -d" " /var/clearos/system_database/reports` -e 'delete from resource where TIMESTAMPDIFF(MONTH,timestamp,curdate()) > 11 ;'
    You are welcome to modify it. You may need to pull in a different password (the root one). You probably don't want the -D switch as you'll specify the database to delete with the DROP command.
    The reply is currently minimized Show
  • Accepted Answer

    Monday, February 11 2019, 03:39 PM - #Permalink
    Resolved
    0 votes
    Man oh man, I think this is finally wrapped up. Thank you so much Nick and Dave! I wound up following your advise and removed the proxy report app via:
    rpm -e app-proxy-report app-proxy-report-core --nodeps

    Just a typo correction on the code. It's --nodeps rather than --no-deps.
    When it instantly came back to the command line, just to verify the two apps were gone, I then tried:
    yum install app-proxy-report app-proxy-report-core --nodeps

    However, I get this in response:
    Dependencies Resolved

    ============================================================================================================================
    Package Arch Version Repository Size
    ============================================================================================================================
    Installing:
    app-proxy-report noarch 1:2.5.0-1.v7 clearos-verified 13 k
    app-proxy-report-core noarch 1:2.5.0-1.v7 clearos-verified 19 k

    Transaction Summary
    ============================================================================================================================
    Install 2 Packages

    Total size: 31 k
    Installed size: 95 k
    Exiting on user command
    Your transaction was saved, rerun it with:
    yum load-transaction /tmp/yum_save_tx.2019-02-11.10-15.XOvW5Z.yumtx

    It will give me a yes or no choice if I eliminate the --nodeps parameter, but I know I wouldn't want to do that.

    So, in proceeding, I stopped all of the Kopano related services.

    I backed up the database using:
    /usr/clearos/sandbox/usr/bin/mysqldump --port=3308 --single-transaction --routines --all-databases     --password=$(awk '{print $3}' /var/clearos/system_database/root) --ignore-table=reports.proxy --ignore-table=reports.proxy_domains > /tmp/Feb112019.sql


    Then I connected to mysql and did the following:
    MariaDB [mysql]> use reports;
    MariaDB [reports]> show tables;
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id: 1
    Current database: reports

    +-------------------+
    | Tables_in_reports |
    +-------------------+
    | network |
    | proxy |
    | proxy_domains |
    | proxy_prune |
    | resource |
    +-------------------+
    5 rows in set (0.22 sec)

    MariaDB [reports]> drop table proxy;
    MariaDB [reports]> drop table proxy_domains;
    MariaDB [reports]> show tables;

    +-------------------+
    | Tables_in_reports |
    +-------------------+
    | network |
    | proxy_prune |
    | resource |
    +-------------------+
    3 rows in set (0.00 sec)


    Afterwards, I restarted the system-mariadb, mariadb, webconfig, and all of the kopano services. I issue the "top" command and now mysqld is the only database related process showing up with 0.4% usage. Additionally, the system-mariadb log is sitting at 0kb. That's so nice after dealing with this for a week. My browser was still having hiccups when trying to access the dashboard of Webconfig, so I cleared the browsers cache and all seems fine now.

    I hesitate to try and install the proxy report app, especially considering yum spits back what I'm showing above when using the nodeps parameter.

    I'd like to mark this as resolved after I give it a day or two, but also if I have the option to reinstall the proxy report minus dependencies. Any thoughts on that?
    The reply is currently minimized Show
Your Reply