user manager eats up my disk

halacs · Wed Sep 23, 2020 2:21 pm

Hi,

I have a Mikrotik CCR1009-7G-1C-1S+PC router acting as a RADIUS server as well with help of user manager package.
Today when I wanted to upgrade to the latest RouterOS release I noticed there is no enough free space to do so. I can I free up some space?

I noticed user-manager eats up almost all the disk space while there is no too many users in my network: 81 registered only as it is my home network.
I noticed that there are so many sessions: 679289
I read that in some cases sessions can get stuck and needs to be cleaned out manually. While I don't understand why manual cleanup would be needed in an industrial grade device, I tried to do so as well as attached a UPS to my router.
If I try to remove the sessions, router thinking a lot then after a while my command gets interrupted:

[halacs@router] > /tool user-manager session remove
numbers: 0-10000
Script Error: action cancelled

Here are the file sizes what I think is unjustified in this network size:

# NAME                                                      TYPE                                                           SIZE CREATION-TIME       
 7 user-manager                                              directory                                                           sep/23/2020 13:08:23
 8 user-manager/sqldb                                        file                                                       130.1MiB sep/23/2020 13:08:21
10 user-manager/logsqldb                                     file                                                         6.0KiB sep/05/2020 07:57:48
11 user-manager/sqldb-journal                                file                                                        14.6KiB sep/23/2020 13:08:23

And just because of the the package versions:

[halacs@router] > /system package print 
Flags: X - disabled 
 #   NAME                                                       VERSION                                                       SCHEDULED              
 0   ntp                                                        6.47.3                                                                               
 1   ups                                                        6.47.3                                                                               
 2   routeros-tile                                              6.47.3                                                                               
 3   system                                                     6.47.3                                                                               
 4   ipv6                                                       6.47.3                                                                               
 5   wireless                                                   6.47.3                                                                               
 6   hotspot                                                    6.47.3                                                                               
 7   mpls                                                       6.47.3                                                                               
 8   routing                                                    6.47.3                                                                               
 9   ppp                                                        6.47.3                                                                               
10   dhcp                                                       6.47.3                                                                               
11   security                                                   6.47.3                                                                               
12   advanced-tools                                             6.47.3                                                                               
13   user-manager                                               6.47.3

Does anyone have an idea how to compress sqldb to free up some space to upgrade RouterOS?

Thanks & br,
Halacs

CZFan · Wed Sep 23, 2020 3:20 pm

Use a MicroSD or USB storage for the user manager data

halacs · Wed Sep 23, 2020 3:35 pm

It seems my router really have a MicroSD slot, I never used so far.
How can I configure to use microSD for user manager?

Edit:
Do you know the max size of the microSD card I can use? I cannot find it in the specification on mikrotik.com. Thanks!

halacs · Wed Sep 23, 2020 10:05 pm

Regarding the microSD card: I found the answer for my question here: https://wiki.mikrotik.com/wiki/Manual:S ... se_example

The max size of the microSD card is still a question but I plan to order a 'SANDISK Ultra 64GB MicroSDXC 100 MB/s' one as soon as possible. I hope it will work.

Thanks!

halacs · Fri Sep 25, 2020 9:06 pm

Now I have a microSD but can't work. I have tried two microSD: a brand new SanDisk 64GB and an older one which is 2GB big. Both could be formatted to ext3 but then I got the following error when I try to move the database which looks really bad.
My best guess is 14MB free space on the internal disk is not enough to do so.

[admin@router] > /tool user-manager database save name=/disk1/UMBackup 
Saving user-manager database
Failed to save user-manager database backup
action timed out - try again, if error continues contact MikroTik support and send a supout file (13)
[admin@router] >

CZFan · Fri Sep 25, 2020 9:52 pm

I have not worked much with usermanager, but maybe rather do a backup / export of usermanager data, clear database, move it and then restore / import from backup

halacs · Fri Sep 25, 2020 10:18 pm

Yeah, I had a similar idea: disabled user manager package, reboot, remove user-manager directory, re-enable um package, reboot then set the database directory but I got the same error: time out, send logs to the support.

Before I did the above I downloaded the user-manger directory and copy-pasted my user data (MAC addresses, IPs and comments) from the web UI so I can load them back in worst case manually.

Just to make sure, this is not a known bug: before I re-enabled the um package I applied the latest updates.

halacs · Fri Sep 25, 2020 11:31 pm

Simply copying user-manager's directory to a USB stick then set db path to the new directory is enough to migrate without data loss.
No need to do backups or whatever just disable the package, copy the files via SCP, re-enable the package and set db path in terminal.
So issue is solved now, thanks a lot! :)

However, I opened another topic because of the microSD card instability. Plus I suspect db path change is not permanent as I had to set it again after a reboot even if I used USB storage instead of the microSD slot.

halacs · Sun Sep 27, 2020 3:49 pm

I want to share my experiences, might be usefull for others struggling with the same problems:

user manager's database path setting is NOT permanent: it doesn't survive a reboot
no external storage was working with user manager: neither microSD slot nor USB storage
user manager's database is an sqlite3 database, can be moved to another device by "copy-paste". I used SCP to do so and a SD card reader attached to my PC. (Mikrotik seems not support direct file copy so need some magic in a way or in another)
user manager's database is not encrypted
user manager's database can be opened in bash with help of sqlite3 command
too many sessions exist somehow at database level

So what helped me at the end was compacting the database. 133 MB sqlite3 database because a 111 KB big file what is fine even for the internal storage.

So the final solution was in my Ubuntu 18.04 LTS machine:

First you have to download user-manager directory from the router
install sqlite3 utility:
Code: Select all
```
apt install sqlite3
```
open user managers database:
Code: Select all
```
sqlite3 sqldb
```
truncate sessions table:
Code: Select all
```
delete from session;
```
compact the database:
Code: Select all
```
VACUUM;
```
press CTRL+D to exit from the sqlite3 utility
Override original files on the router with the compacted ones

Comment:
I'm far dissatisfied with the external storage capability of my Mikrotik router. I would be happy if support would step in and help to debug as I read similar experiences on the internet from others too.

Useful links:
* https://wiki.mikrotik.com/wiki/Manual:S ... se_example
* https://stackoverflow.com/questions/216 ... sqlite3-db

user manager eats up my disk [SOLVED]

user manager eats up my disk

Re: user manager eats up my disk

Re: user manager eats up my disk

Re: user manager eats up my disk

Re: user manager eats up my disk

Re: user manager eats up my disk

Re: user manager eats up my disk

Re: user manager eats up my disk

Re: user manager eats up my disk [SOLVED]

Who is online