How to edit mysql database? time_zone_name [SOLVED]

[Skepticalme]

I am wondering how I can resolve the following issue.

What are the commands to edit MySQL?

Sorry for the noob questions.

Code: Select all

ERROR: Your Cacti database login account does not have access to the MySQL TimeZone database. Please provide the Cacti database account "select" access to the "time_zone_name" table in the "mysql" database, and populate MySQL's TimeZone information before proceeding.

[cigamit]

Here are the relevant portions in the install documentation.

https://docs.cacti.net/General-Installi ... sqlmariadb

https://docs.cacti.net/General-Installi ... gure-cacti

[Skepticalme]

Here are the relevant portions in the install documentation.

https://docs.cacti.net/General-Installi ... sqlmariadb

https://docs.cacti.net/General-Installi ... gure-cacti

I received an error when both trying to create a password and trying to change the timezone. Thanks for your help, btw.

Code: Select all

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Also,

Code: Select all

xxxx@nems:~ $ mysql --user=root mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[cigamit]

That typically means you already have a password set. We have no idea what OS you are running, so you might try googling your distro/OS and mysql root password and see if they are auto setting one and saving it somewhere.

[Skepticalme]

That typically means you already have a password set. We have no idea what OS you are running, so you might try googling your distro/OS and mysql root password and see if they are auto setting one and saving it somewhere.

I'm using Nems OS 1.5.2 which is a version of Raspbian 10
CACTI 1.2.23
MariaDB 10.3.36 database server

These are the things I've tried:

Code: Select all

nemsadmin@nems:~ $ mariadb
ERROR 1045 (28000): Access denied for user 'nemsadmin'@'localhost' (using password: NO)
nemsadmin@nems:~ $ sudo cat /root/.my.cnf
[sudo] password for nemsadmin: 
cat: /root/.my.cnf: No such file or directory
nemsadmin@nems:~ $ sudo -i mysql

          ███╗   ██╗███████╗███╗   ███╗███████╗
          ████╗  ██║██╔════╝████╗ ████║██╔════╝
          ██╔██╗ ██║█████╗  ██╔████╔██║███████╗
          ██║╚██╗██║██╔══╝  ██║╚██╔╝██║╚════██║
          ██║ ╚████║███████╗██║ ╚═╝ ██║███████║
          ╚═╝  ╚═══╝╚══════╝╚═╝     ╚═╝╚══════╝
                                         LINUX

                   BY: ROBBIE FERGUSON
                      NEMSLINUX.COM

  NEMS Platform....: Raspberry Pi 4
  NEMS Version.....: 1.5.2 (Current Version is 1.5.2)
  NEMS IP Address..: 192.168.50.168
  Uptime...........: 0 days 15 hours 46 minutes 38 seconds
  Load.............: 0.35 (1 minute) 0.28 (5 minutes) 0.30 (15 minutes)
                     0.50 (1 week)
  Memory...........: Total: 1939 MB / Cached: 98 MB
                     Used: 322 MB / Free: 354 MB
  Disk Usage.......: You're using 8% of your root filesystem

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
nemsadmin@nems:~ $ systemctl stop mariadb
Failed to stop mariadb.service: Access denied
See system logs and 'systemctl status mariadb.service' for details.
nemsadmin@nems:~ $ sudo mysqld_safe --skip-grant-tables --skip-networking &
[1] 9829
nemsadmin@nems:~ $ mariadb -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
nemsadmin@nems:~ $ 230114 13:13:15 mysqld_safe Logging to syslog.
230114 13:13:15 mysqld_safe A mysqld process already exists

[1]+  Exit 1                  sudo mysqld_safe --skip-grant-tables --skip-networking
nemsadmin@nems:~ $ mariadb -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[cigamit]

A quick google shows this, but its for an older version
https://baldnerd.com/nems-technical-information/

Code: Select all

MySQL: root/nagiosadmin

So I would try using that username and password

Code: Select all

mariadb -u root -p

I can't find a documented username / password for the newer version, but there is this

https://docs.nemslinux.com/en/latest/ba ... sword.html
so you might try using your nemsadmin password instead

Code: Select all

mariadb -u nemsadmin -p

if all else fails, swap to the root user's profile and just try to get into mariadb that way

Code: Select all

sudo su -
mariadb

Beyond that, I can't help you much. It seems that NEMS is really trying to make itself an appliance and not as a proper OS that they want you installing additional things on.

[Skepticalme]

You were right. I tried

Code: Select all

sudo mysql -p

entered my user password, then entered:

Code: Select all

nagiosadmin 

(root). This what I got as a result (mariadb):

Code: Select all

nemsadmin@nems:~ $ sudo mysql -p 
[sudo] password for nemsadmin: MyPassword
Enter password: nagiosadmin
Welcome to the MariaDB monitor.
Commands end with ; or \g.
Your MariaDB connection id is 975
Server version: 10.3.36-MariaDB-0+deb10u2 Raspbian 10
Copyright (c) 2000, 2018, Oracle, Maria Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'Ic' to clear the current input statement.

MariaDB [ (none) ]> 

So, I have this issue with Cacti, and I'm wondering how I can edit "time_zone_name", and if the command

Code: Select all

sudo mysql -p

takes me to mariadb does that mean I don't have mysql installed? (forgive me for the beginner questions and problems).

Code: Select all

ERROR: Your Cacti database login account does not have access to the MySQL TimeZone database. Please provide the Cacti database account "select" access to the "time_zone_name" table in the "mysql" database, and populate MySQL's TimeZone information before proceeding.

[cigamit]

Mariadb is a fork of mysql, so they are basically the same thing. You can read about it here if you are interested.
https://mariadb.com/database-topics/mariadb-vs-mysql/

Now that we know how to get into mysql, try this command

Code: Select all

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u mysql -p

[Skepticalme]

Mariadb is a fork of mysql, so they are basically the same thing. You can read about it here if you are interested.
https://mariadb.com/database-topics/mariadb-vs-mysql/

Now that we know how to get into mysql, try this command

Code: Select all

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u mysql -p

Thanks for your reply, and thanks for the assistance.

I followed your instructions and it returned this result:

[cigamit]

Oops, take out the -u from that command

[Skepticalme]

Code: Select all

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql -p

It returned the result:

Code: Select all

ERROR 1045 (28000): Access denied for user 'nemsadmin@localhost' (using password: YES) 

I also tried (just in case the previous one was wrong)

Code: Select all

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -p

Do you think it might be worth me reinstalling the OS and starting again? Because I'm pretty sure I made a mistake by running some type of 'secure installation' script at some stage. I remember alternating some type of security settings in nems thinking it was necessary. Someone else pointed out that some type of security setting could have been altered.

EDIT: Disregard this as I have decided to reinstall the OS and start again

[Skepticalme]

Oops, take out the -u from that command

I have reinstalled the OS and Cacti.

I really don't understand why I'm having so much trouble with this. I'm only learning but I've done everything according to instructions and advice.

Here are the result:

Code: Select all

admin@nems:~ $ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
admin@nems:~ $ sudo mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.

I also tried:

Code: Select all

admin@nems:~ $ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
Enter password: 
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.

[phalek]

The last command actually looks good.

Just make sure that your Cacti database user also has access to the timezone info.

Example:

Code: Select all

GRANT SELECT ON mysql.time_zone_name TO cacti@localhost IDENTIFIED BY 'mypass23';

[Skepticalme]

The last command actually looks good.

Just make sure that your Cacti database user also has access to the timezone info.

Example:

Code: Select all

GRANT SELECT ON mysql.time_zone_name TO cacti@localhost IDENTIFIED BY 'mypass23';

Solved! Thank you very very much!