Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Previous development branch now released as 1.36
ginbosqueeze
Posts: 9
Joined: Thu Oct 22, 2020 3:32 pm

Re: Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Post by ginbosqueeze »

Debian 10 Buster with Zoneminder 1.36.x from ZM Repo

and i need to invert what i said previously in one of my posts...

in my /etc/mysql/mariadb.conf.d/50-server.cnf there is no [mysql], mine has [mysqld]

if they are the same thing, i've added the section

innodb_file_per_table = ON
innodb_buffer_pool_ size = 256M
innodb_log_file_size - 32M

to my [mysqld] section, and it did not resolve my issue...i even attempted to add those lines of code under the [mariadb] section, and that didn't work

so i did another clean install of ZM..and now the same MySQL issues happened again...i've already reinstalled i believe 4x, each time trying to minimize and narrow down the potential issue, but i've taken all the steps listed in all forums i've read and found, with no luck...

so essentially, my surveillance camera system is currently down until we can hopefully find out a fix for this
User avatar
bkjaya1952
Posts: 282
Joined: Sat Aug 25, 2018 3:24 pm
Location: Sri Lanka

Re: Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Post by bkjaya1952 »

ginbosqueeze wrote: Fri Nov 19, 2021 3:56 am
bkjaya1952 wrote: Tue Apr 27, 2021 6:38 am Please drop mysql zm data base and re create as follows

mysql -e "drop database zm;"

mysql -uroot --password=""< /usr/share/zoneminder/db/zm_create.sql 2>/dev/null

mysql -e "ALTER USER 'zmuser'@localhost IDENTIFIED BY 'zmpass';"

mysql -e "GRANT ALL PRIVILEGES ON zm.* TO 'zmuser'@'localhost' WITH GRANT OPTION;"

mysql -e "FLUSH PRIVILEGES ;"
by the time i tried this, i was locked out of the database due to ZM failing

got this error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Before installation zoneminder , you may have to purge existing zoneminder .

Please follow the following steps to purge zoneminder and it's components.

sudo apt purge zoneminder

sudo mysqladmin --defaults-file=/etc/mysql/debian.cnf -f drop zm

sudo rm -rf /var/cache/zoneminder /var/log/zm /var/cache/zoneminder/cache

sudo apt autoremove


https://wiki.zoneminder.com/How_to_Inst ... o%E2%80%99


In the above link replace the following command with the zoneminder type ( zoneminder master , stable or proposed )that you want to install

sudo add-apt-repository ppa:iconnor/zoneminder-1.36
ginbosqueeze
Posts: 9
Joined: Thu Oct 22, 2020 3:32 pm

Re: Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Post by ginbosqueeze »

Thanks much for your reply

So every time i have done a clean install of ZM, i actually do a full re-installation of Debian 10

Which is why it's such a daunting task, because reinstalling the OS and configuring it, and also reinstalling ZM and configuring it, every time, is a bit much.

But I do it this way so that I can make sure I have a clean installation every singe time.

I always did this when i was running ZM on a physical node, and I am currently doing the same having a VM with Debian 10 and ZM installed on it.

I did attempt your commands, like I previously told another commenter, but unfortunately the state the SQL server was in would even let me log into the SQL server to run your commands to drop the ZM db at all...i was completely locked out due to ZM being corrupted by the SQL database.

At this point I can go ahead and do a full re-installation of everything, but i know this issue is going to come again.

I was actually considering upgrading to Debian 11 and seeing if ZM is more table on that version of OS...any thoughts on that?
dougmccrary
Posts: 1151
Joined: Sat Aug 31, 2019 7:35 am
Location: San Diego

Re: Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Post by dougmccrary »

I did attempt your commands, like I previously told another commenter, but...
I think those are run from the system command line. Is that what you did?
dougmccrary
Posts: 1151
Joined: Sat Aug 31, 2019 7:35 am
Location: San Diego

Re: Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Post by dougmccrary »

OK, So when you did

mysql -e "drop database zm;"

what came back?
ginbosqueeze
Posts: 9
Joined: Thu Oct 22, 2020 3:32 pm

Re: Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Post by ginbosqueeze »

Sorry for the late reply, but I received this:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

along with others, but at this point I was thinking about installing ZM on Debian 9, and hopefully get it stable...or move to Debian 11

Not sure which route to go, maybe you can give me some direction please?

Thanks
User avatar
kitkat
Posts: 193
Joined: Sun Jan 27, 2019 5:17 pm

Re: Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Post by kitkat »

ginbosqueeze wrote: Fri Nov 19, 2021 6:02 pm in my /etc/mysql/mariadb.conf.d/50-server.cnf there is no [mysql], mine has [mysqld]

if they are the same thing, i've added the section

innodb_file_per_table = ON
innodb_buffer_pool_ size = 256M
innodb_log_file_size - 32M

to my [mysqld] section, and it did not resolve my issue
There's a typo in this.

innodb_buffer_pool_ size has a wayward space in it and it'll probably throw an error when the .cnf loads which may well prevent the server from starting.
ginbosqueeze
Posts: 9
Joined: Thu Oct 22, 2020 3:32 pm

Re: Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Post by ginbosqueeze »

Awesome catch, thanks much

I will go ahead and install ZM on Debian 10 using the "Debian 10 Buster with Zoneminder 1.36.x from ZM Repo" installation guide

And adjust the space in the "innodb_buffer_pool_ size = 256M" section

And I will post my results.

Thank you
User avatar
kitkat
Posts: 193
Joined: Sun Jan 27, 2019 5:17 pm

Re: Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Post by kitkat »

kitkat wrote: Sun Nov 28, 2021 1:53 pm
ginbosqueeze wrote: Fri Nov 19, 2021 6:02 pm in my /etc/mysql/mariadb.conf.d/50-server.cnf there is no [mysql], mine has [mysqld]

if they are the same thing, i've added the section

innodb_file_per_table = ON
innodb_buffer_pool_ size = 256M
innodb_log_file_size - 32M

to my [mysqld] section, and it did not resolve my issue
There's a typo in this.

innodb_buffer_pool_ size has a wayward space in it and it'll probably throw an error when the .cnf loads which may well prevent the server from starting.
To add to this (I was going to edit but there's been a reply since)...

You may need to delete the old InnoDB log files after changing their size. (I have to on CentOS).

Check the mariadb log for errors along the lines of

Code: Select all

InnoDB: Error: log file ./ib_logfile0 is of different size 0 123456789  bytes
InnoDB: than specified in the .cnf file 0 987654321 bytes!
I fix this by:

Stopping Mariadb/MySQL.
Deleting (or moving/renaming) /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1.
Starting Mariadb/MySQL.

You should than see in the logs a warning that the files are missing and information saying that they're being rebuilt.
User avatar
kitkat
Posts: 193
Joined: Sun Jan 27, 2019 5:17 pm

Re: Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Post by kitkat »

ginbosqueeze wrote: Sun Nov 28, 2021 1:58 pm Awesome catch, thanks much

I will go ahead and install ZM on Debian 10 using the "Debian 10 Buster with Zoneminder 1.36.x from ZM Repo" installation guide

And adjust the space in the "innodb_buffer_pool_ size = 256M" section

And I will post my results.

Thank you
There's another error in the guide...

innodb_log_file_size - 32M should be innodb_log_file_size = 32M (equals instead of minus)

See the post above about potential problems when changing the log file size, and how to fix them.
ginbosqueeze
Posts: 9
Joined: Thu Oct 22, 2020 3:32 pm

Re: Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

Post by ginbosqueeze »

Hello all, this is an update that as of 12/30/21 I have done a fresh install of ZM on Debian 10 using the "Debian 10 Buster with Zoneminder 1.36.x from ZM Repo" installation guide

Also, I fixed and implemented this block of code in my /etc/mysql/mariadb.conf.d/50-server.cnf file:
innodb_file_per_table = ON
innodb_buffer_pool_size = 256M
innodb_log_file_size = 32M

I probably did this almost a month ago now, and I currently have 5 cameras running and so far so good.

I have not had a database crash, and also my filter seems to be working.

Thanks all for you help
Locked