Solved Problems with MySQL

[tony33]

Hi, I need some help. I ran:

mysql -u root -p'password' password 'newpass'

in terminal. I ran it in terminal. I can see in the database of MySQL the user root password has changes.

The problem lies when I change the password. I then go into my websites and the mysql_connection() functions I insert the new password. However, if I use the new password it gives me permission denied warnings. If I put the password back to the old one it won't work either. If I go back the terminal and change the password back to the old password. It then works again. I am talking about my website being able to access MySQL database well connect. It's not a firewall issue. Since, the original password works while the firewall is on.

My personal feeling is that something is holding on to the old password and not changing it to the new one.

I need to change passwords. I feel the password in the database gets changed but seems the password is stored elsewhere and wherever that is that file might not permit the changes to it. So, the job is half done.

So, what I am saying is when I run that command I can see the password hash change in the database... in the user table. In terminal I can test it and I can execute show databases with the new password. The old password would be permission denied warning.

The problem is my Apache 2.4 server. My websites will still give me that permission denied warning. It will only work if I change the password back to the old password. If I go to my website I won't see any warnings and the website works perfectly.

Once, I change that password to the new one and make changes to my website files to use the new password. It will give me those warnings. I do change the website files back to the original password when trying the original password and changing back to it. I do the same when using the new password.

So, the problem is that I can change my users passwords but Apache 2.4 and Postfix both don't recognize them. I get the permission denied even thought I tested the new passwords in terminal and I was able to gain access.

I use MySQL 5.6.24.

Is there something I am missing? Do, I have to run another command?

 

[junovitch@]

Does mysql -u root -p'password' -e 'flush privileges' help?

 

[junovitch@]

Secondly, you should keep the root database password for administrative use. You may want to try something more along the lines of this for creating database tables and users for them. Ideally, restrict what privileges you grant even more then this.

mysql -u root -p
CREATE DATABASE foreman CHARACTER SET utf8;
CREATE USER 'foreman'@'10.100.102.11' IDENTIFIED BY 'foreman_password';
GRANT ALL PRIVILEGES ON foreman.* TO 'foreman'@'10.100.102.11';
flush privileges;
quit;

 

[tony33]

Does mysql -u root -p'password' -e 'flush privileges' help?

What does flush privileges do? During after the change I stop the MySQL server and restarted it. Would it have the same effect? If so then nothing happened. If it shouldn't have the same effect. Then I will try it out when I get a response to this post.

UPDATE:

I just tried that flush and it didn't do anything. I still get the error on my website.
Yet, in terminal the new password works and the old one doesn't. So, in terminal it works fine. However, my apache24 server for some reason gives a warning saying permission denied root @ localhost

Is there any command I can run to see if 2 versions of MySQL is installed?

I feel like the passwords don't actually get changed in MySQL but does change some areas but not everything needed. I feel that it's stuck with the old password.

That even if I change the database with new passwords. I need to change it back to the original passwords because something is stuck using it and it won't change.

I don't know if this makes sense but that's my gut feeling. Since, I changed the passwords and change the website to use the new password. I would get the warning permission denied. If I change the passwords back to the original password for only the websites. I see the warning message permission denied. However, if I change the mysql password back to the original password. The website works perfectly.

So, I feel the passwords are changed but there's something somewhere that doesn't get updated where it still looks for the original password.

 

[junovitch@]

What does flush privileges do? During after the change I stop the MySQL server and restarted it. Would it have the same effect? If so then nothing happened. If it shouldn't have the same effect. Then I will try it out when I get a response to this post.

Never mind on that one. It must have been a bad habit I picked up over the years and never did the research on. The steps I listed should make things take immediate effect per the MySQL Documentation:
https://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

UPDATE:

I just tried that flush and it didn't do anything. I still get the error on my website.
Yet, in terminal the new password works and the old one doesn't. So, in terminal it works fine. However, my apache24 server for some reason gives a warning saying permission denied root @ localhost

Is there any command I can run to see if 2 versions of MySQL is installed?

pkg info -x mysql\*. However even if they were installed in different places in the file system so there is no conflict they would conflict on the same port.

I feel like the passwords don't actually get changed in MySQL but does change some areas but not everything needed. I feel that it's stuck with the old password.

That even if I change the database with new passwords. I need to change it back to the original passwords because something is stuck using it and it won't change.

I don't know if this makes sense but that's my gut feeling. Since, I changed the passwords and change the website to use the new password. I would get the warning permission denied. If I change the passwords back to the original password for only the websites. I see the warning message permission denied. However, if I change the mysql password back to the original password. The website works perfectly.

So, I feel the passwords are changed but there's something somewhere that doesn't get updated where it still looks for the original password.

Is the password set for all possible hostnames the user can connect to? Does this show the password as being the same across each hostname?
mysql mysql -e 'select Host, User, Password from user;'

 

[vejnovic]

The problem lies when I change the password. I then go into my websites and the mysql_connection() functions I insert the new password. However, if I use the new password it gives me permission denied warnings. If I put the password back to the old one it won't work either. If I go back the terminal and change the password back to the old password. It then works again.

It likes that you have problem with cached data somewhere. Sometimes it helps if you delete all cookies from your browser. Sometimes it helps if you restart web server or restart all services which are dependent on MySQL server.

 

[tony33]

Never mind on that one. It must have been a bad habit I picked up over the years and never did the research on. The steps I listed should make things take immediate effect per the MySQL Documentation:
https://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html





pkg info -x mysql\*. However even if they were installed in different places in the file system so there is no conflict they would conflict on the same port.



Is the password set for all possible hostnames the user can connect to? Does this show the password as being the same across each hostname?
mysql mysql -e 'select Host, User, Password from user;'

Yes, I checked it and it's all the same. It shows the passwords getting updated when I make changes to them.

 

[tony33]

It likes that you have problem with cached data somewhere. Sometimes it helps if you delete all cookies from your browser. Sometimes it helps if you restart web server or restart all services which are dependent on mysql server.

What are those services? I turned off my firewall and still it won't work. I would then switch back to the older password and it will work weather the firewall is on or off. however, if using the new password it won't matter if the firewall is on or off. It will still not work.

What services do I need to restart? I restarted the mysql server and that didn't work. I deleted cookies for my website. That still didn't work. I don't think anything is cached. I think the changes are being made in one area and another area isn't changing it fully.

 

[tony33]

Is there any log or command that I can run to test to see why PHP cannot connect to my database? I mean getting permission errors.

 

[tony33]

Question: does FreeBSD MySQL use the same users as the users created by FreeBSD?

 

[ShelLuser]

Is there any log or command that I can run to test to see why PHP cannot connect to my database? I mean getting permission errors.

Normally you'd check your webserver logfiles for that. That should give some more hints as to what is going on. Another option is to enable and/or raise logging verbosity in MySQL itself.

Question: does FreeBSD MySQL use the same users as the users created by FreeBSD?

No, by default the MySQL userbase is completely separate from your system userbase. After installing you'll only have the root user on MySQL whereas your FreeBSD system will have more.

Edit:

I also wonder what exact command you used to try and change the password, because if I try to reproduce your problem (I'm also using MySQL 5.6.24) then all I get is an error when using the password command you mentioned in the OP.

Or did you mean to say mysqladmin instead of mysql? Because that makes quite a difference...

breve:/home/peter $ mysqladmin -u root -p password 'test'
Enter password:
Warning: Using a password on the command line interface can be insecure.
breve:/home/peter $ mysql -u root -ptest
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.24 Source distribution

Copyright (c) 2000, 2015, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

[junovitch@]

What are those services? I turned off my firewall and still it won't work. I would then switch back to the older password and it will work weather the firewall is on or off. however, if using the new password it won't matter if the firewall is on or off. It will still not work.

What services do I need to restart? I restarted the mysql server and that didn't work. I deleted cookies for my website. That still didn't work. I don't think anything is cached. I think the changes are being made in one area and another area isn't changing it fully.

You had mentioned Apache 2.4 and Postfix before. Given that the user's password is showing in MySQL as being updated and is valid at the command line it suggests MySQL should not be the cause of your issues. Can you provide more information on how those services are configured to use MySQL?

 

[tony33]

Normally you'd check your webserver logfiles for that. That should give some more hints as to what is going on. Another option is to enable and/or raise logging verbosity in MySQL itself.


No, by default the MySQL userbase is completely separate from your system userbase. After installing you'll only have the root user on MySQL whereas your FreeBSD system will have more.

Edit:

I also wonder what exact command you used to try and change the password, because if I try to reproduce your problem (I'm also using MySQL 5.6.24) then all I get is an error when using the password command you mentioned in the OP.

Or did you mean to say mysqladmin instead of mysql? Because that makes quite a difference...

breve:/home/peter $ mysqladmin -u root -p password 'test'
Enter password:
Warning: Using a password on the command line interface can be insecure.
breve:/home/peter $ mysql -u root -ptest
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.24 Source distribution

Copyright (c) 2000, 2015, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

No, in the op I used that exact line. Just replace -p'password' the password part with the actual password and so forth. I get no error messages. I don't use mysqladmin. I have webmin as an interface. I can change passwords via webmin or by typing that command into the terminal. For me it works. I can then use the new password to login and display all databases /tables.

The problem is that I can visually see the mysql database under users get their passwords changed.

I have root @ localhost as the user. The password has changes. The problem is when I change it. I can in terminal login and use the new password. I tried even the old password and it would no longer work. So, I know it successfully changed the password.

The problem is that my website prints out a warning message saying: [B]Warning[/B]: mysql_connect(): Access denied for user 'root'@'localhost' (using password: YES) in /location of php file/ line 8 aka the location where the function starts.

What I notice is that if I change it to any other password than the original password. It works in terminal perfectly. My old password won't work after the change which is to be expected.

Yet, when going on my website I get that message. Yes, I do go in to the code and update the password to use the new password. I copied it word for word. I even changed the password to something very easy like 'kid45' or something to make sure the password input isn't wrong.
I still get the same results.

No matter what password I change it to. I get this message. The only thing that works is when I change it back to the original password. After doing that it works in both the terminal and on the my website perfectly.

The problem is that the original password isn't secure. I recently got a few hackers going after it. I won't be surprised if they got the password cracked.

So, I really don't feel safe using the original password.

So, to recap I can change the passwords to a new one successfully. However, the new passwords only work in terminal but my website gives warnings and even if you login to my website it will get you stuck. It will log you in but give you multiple errors of the same message above. You aren't actually logged in.. it's actually a fail to login page you get redirected too. However, no images load and you get a bunch of errors. The one above you see that there and then you see like rows saying returns 0 or null expected 1 . Something along those lines.

So, what I need to do is figure out why when changing the password it doesn't work with my website. I get that message above. I had the firewall turn off and I get the same results. So, don't say it's the firewall. Even if that was the case I wouldn't be able to use my original password if it was a firewall issue.

What it looks like is that I can change the passwords and use it in terminal but the website has permission issues with any new password. If I set it back to the original password it works perfectly.

I would like any suggestions on what to do. Or what log to look at to see more detailed errors.

After typing this message I am going to look at httpd-errors.log and my actual website logs. I seen them before and it only would show failed logins attempts, and ssl certificate issues. I have a url for development only and the ssl certs don't match the url. I mean when they do a reverse lookup on the ip. That's the only thing I have seen and I looked at those logs when I first came across the issue.

 

[tony33]

You had mentioned Apache 2.4 and Postfix before. Given that the user's password is showing in MySQL as being updated and is valid at the command line it suggests MySQL should not be the cause of your issues. Can you provide more information on how those services are configured to use MySQL?

I didn't setup apache24 to use mysql...well didn't do any config setup. I just downloadedthe php module and the php module uses mysql. I have the orignal mysql and then mysqli and then pod_mysql modules installed.

I didn't touch anything with any configs. Just installed those modules. The setup works I never touched the passwords since the first day starting FreeBSD.

Postfix and dovecot I had to setup it up to use mysql. I use vhosts on postfix. I have the lookup tables in mysql.

Right now Postfix or dovecot isn't working properly. I cannot login to my e-mail accounts. Yet, ,I can send e-mail. I tested that I can send email to my mail accounts. I am sure I can send e-mail but can't test that. I just think the passwords are an issue.

It worked find before. However, recently had attacks from hackers. I didn't have a firewall setup nor any protection. I then set those up and everything worked with the firewall and fail2ban.

I then made changes to the postfix and dovecot to require ssl. before it was that it was optional. I then changed the encryption to the latest tls version. After making the changes it still was working. I then went into the database and changed the password hashes for my mail account. I generated the new password hashes. I restarted the servers and it no longer works.

I was in the process to change all my passwords. I even changed the freebsd user passwords.
I then moved on to change the mysql passords and then notice the issue I am having.

I use webmin to changed them but once having the problem. I tried the terminal and got the same results. To me this problem seems very strange. I used the grep to see all mysql stuff installed on my system. I got the mysql server, client and then a bunch of php installation that uses mysql.

I am saying I can change passwords. In terminal it works perfectly. It's just my website gives me the errors. I am sure it's the same issue with my postfix. Since, I cannot login to read my mail...etc
The mail log and even thunderbird mail would give me messages that says failed to login or would say a timeout. I then deleted thunderbird and reinstalled it. I cannot setup my accounts again. It just fails at the point of authentication.

I am pretty sure that it's not postfix nor dovecot. I think it's an issue with mysql. The reason I say this is because the website is having issues with login details. I do have a freebsd user postfix and did change that password too. Postfix uses that user to login to mysql to do lookups. On mysql I have a username postfix and I did change the password. I later change it back to the original password and yes I did change the config files where it does the sql queries and just changed the password.
I did change the queries to use pdo_mysql to avoid sql injections. It before used the regular mysql query syntax. I did get sql syntax errors. I played with it until those errors vanished.

So, I am sure that the syntax is correct. I don't want to get into the postfix issue. I want to stay on mysql and my website using apache24. I feel that the issue lies with mysql. If it's not mysql then it must be something with the unix permissions / users. My apache24 server uses user www and group www. I still feel it's an mysql issue. It's odd for postfix and apache24 to be having the same issues. even though with apache I can still login with my old password and still have it to work.

So, if my apache server can work flawlessly by using the original old password. Then the apache server setup has to be config correctly. The postfix works...when receiving mail. Also my FreeBSD users have their own mail account. I didn't change their passwords. I think it automatically grabs their user passwords from the OS. Those local accounts I have no issues with sending and receiving mail. I use webmin to send out e-mails using those accounts. I don't have to login. Since, webmin logins for me. So, that works flawlessly but it doesn't use mysql. It uses the local files to login. The other accounts that I normally use are vhosts and it uses mysql.

I doubt it's a postfix or a apache / php issue. The reason is that my local accounts work flawlessly but they don't use mysql for password purposes. I can send e-mail and receive it without an issue with the local accounts only. I know this 100% because I tested it. The vhosts errors are login errors.

Right now just checked in webmin. I notice in mysql config tab it says no config file found.
It asks me to manually specify the path. Is this file important and if so what command can I type in to find the path? I install stuff like this using the default settings.

webmin is looking for the path to be at '/etc/my.cnf' that file doesn't exist.

 

[tony33]

Update: just used thunderbird. I can send e-mails with the vhost accounts and receive it. I cannot login and see the emails in my account. Thunderbird says that my server isn't an imap4 capable server. This is when it tries to connect to my server to retrieve the e-mails in my inbox etc.

 

[junovitch@]

Perhaps the PHP code doesn't work as you think. What about grep -R <oldpassword> * in the root of your web page directory? Does the password exist anywhere else that could explain why the PHP MySQL call is only using the old password?

 

[tony33]

Perhaps the PHP code doesn't work as you think. What about grep -R <oldpassword> * in the root of your web page directory? Does the password exist anywhere else that could explain why the PHP MySQL call is only using the old password?

I will check that out today. I doubt that is the case because I have same issues with Postfix / Dovecot. I cannot login to my e-mail. That too uses MySQL to lookup the password hashes. Yet, Postfix uses MySQL to do mail paths lookups and that works perfectly. I know I can send mail and receive mail. I just cannot look at my inbox to read mail due to login errors / issues. However, even if I revert back to the original password with postfix / dovecot I still get errors.

I will try what you suggested. The password shouldn't be anywhere else. I used the same password with the FreeBSD account user root. However, I changed that password and it works perfectly. I think MySQL is separate then the FreeBSD users even if the folders had a password on them. I doubt the errors I see would be about MySQL. It would give me permission errors about the actual file not being accessed. My PHP files do get accessed. It just fails when connection to MySQL server to do queries in the database. That is the only errors I see at this point. Any software I install I follow the defaults. By default it created the root at localhost. I then just used the same password as my FreeBSD user root.

However, like I said the issue seems to be the same with Apache and Postfix / Dovecot. Is there any way to search the OS to see where anywhere on the OS my old password exists?

 

[junovitch@]

...
However, like I said the issue seems to be the same with Apache and Postfix / Dovecot. Is there any way to search the OS to see where anywhere on the OS my old password exists?

I'm just grasping for ideas here. It shouldn't be this hard to change a password and I'm not quite understanding why just yet.

To explain the above, the OS keeps OS usernames/password in /etc/passwd, /etc/master.passwd, /etc/group, and there are the associated database files /etc/pwd.db and /etc/spwd.db that go along with these. The first three have file man pages to describe them, passwd(5)/master.passwd(5) and group(5), while the latter two are mentioned as the result of pwd_mkdb(8). These files have no relation to what MySQL is doing besides MySQL having normal user/group ownership over all its database files.

MySQL keeps its users internal to the database in the mysql.users table. I've learned there is a PAM Authentication Plugin for the MySQL Enterprise Edition however that isn't relevant here so the steps you took should be the only steps needed.

The only other thing I can think of involves jails. I am assuming you have all of the applications installed on one system and only one running instance of MySQL. With jails, there could be some confusion because a jail connecting to localhost actually gets redirected to connect to the jail. However you haven't mentioned jails before so I don't think this is applicable.

 

[tony33]

I'm just grasping for ideas here. It shouldn't be this hard to change a password and I'm not quite understanding why just yet.

To explain the above, the OS keeps OS usernames/password in /etc/passwd, /etc/master.passwd, /etc/group, and there are the associated database files /etc/pwd.db and /etc/spwd.db that go along with these. The first three have file man pages to describe them, passwd(5)/master.passwd(5) and group(5), while the latter two are mentioned as the result of pwd_mkdb(8). These files have no relation to what MySQL is doing besides MySQL having normal user/group ownership over all its database files.

MySQL keeps its users internal to the database in the mysql.users table. I've learned there is a PAM Authentication Plugin for the MySQL Enterprise Edition however that isn't relevant here so the steps you took should be the only steps needed.

The only other thing I can think of involves jails. I am assuming you have all of the applications installed on one system and only one running instance of MySQL. With jails, there could be some confusion because a jail connecting to localhost actually gets redirected to connect to the jail. However you haven't mentioned jails before so I don't think this is applicable.

I don't use jails. I just followed the basic setup. Apache and postfix I just setup the basics and the vhosts to use MySQL.

I ran the command you suggested. It showed me all the files in my base folder for my website. It's because I never changed the passwords in those files. I only changed the php file that handles the login page. I want to get the login page working first before I change other pages. The login page wasn't in the list.

There was a gstreamoutput file that used the old password.

 

[tony33]

I just looked a the logs. There's nothing in MySQL logs but there's a customized log for my hostname. It's the error logs and I see many lines of errors saying:

2015-07-09 20:21:05 16791 [Note] IPv6 is available.
2015-07-09 20:21:05 16791 [Note] - '::' resolves to '::';
2015-07-09 20:21:05 16791 [Note] Server socket created on IP: '::'.

Do you think that could be the problem? I don't think I have a IPv6 ip address.
I don't know why it's binding to a IPv6.

 

[junovitch@]

The kernel supports IPv6 by default so it will have a local loopback address to bind on. That is the message you see.

I get that the password is all over rather than sourced out of a config file. I'm not sure what to suggest as a next step right now.

 

[tony33]

The kernel supports IPv6 by default so it will have a local loopback address to bind on. That is the message you see.

I get that the password is all over rather than sourced out of a config file. I'm not sure what to suggest as a next step right now.

Ok, then if anyone has any ideas what to try or test let me know. I also ran mysql_update. The MySQL longs no longer gives me the errors.

I found not in the MySQL logs but the MySQL logs for my hostname it showed a lot of errors. Like datatables using the wrong structure and missing a privilege table. It told me to run mysql_update to correct the error.

I ran it and it corrected the error and generated a new privilege table. I guess converted my databases to the new current format.

I restarted the server and I still have the same issue. However, I no longer see the errors in the log files.

I checked my config for MySQL and I don't have any password for default for MySQL nor a default user.

php.ini does show MySQL using a cache of 2000

I don't know if this would be helpful. I decided to add a new user and I made root @ hostname1.com

I still have root @ localhost. However, I checked my PHP and by default it uses localhost when connecting to MySQL. I changed it to use hostname1.com

I started Apache again and went to my web page. I still get the same error and it still says access denied root @ localhost (using password)...etc.

Shouldn't it be saying access denied root @ hostname1.com ??

 

[tony33]

Thanks for responding. I just figured the problem. It's the PHP file. I updated the file itself with the right password. I recently done some debugging on the PHP side and found out that even though I changed the password. I echoed the pass variable and it's still using the original password. I am sure I can fix the issue.

 

[junovitch@]

Thanks for the update. Hopefully you can track the issue down. Is there a support forum for the PHP application you are using where you can find out more information or is this a home grown application?

 

[tony33]

Thanks for the update. Hopefully you can track the issue down. Is there a support forum for the PHP application you are using where you can find out more information or is this a home grown application?

It's home grown application. It's solved now. I deleted the PHP file and just dumped a copy of it from my Windows machine to the server. Now it all works.

I notice there's 2 PHP files on my windows machine. One is the code the other is some file for the OS. That file I assume didn't get updated. On my server with FreeBSD it shows only one file. So, I deleted that file and dumped both files I see on my machine. Now, it works perfectly with the new password.

So, if anyone reads this having similar issue where your web pages won't login successfully but can connect. It could be the same issue. Just delete your PHP or ASP files and copy and paste them back in. Make copies of the file and just modify.

It looks like there's actually 2 files that gets created when making a image One of them is hidden and it's meant for the OS to give some information. It seems this file if broken doesn't get updated. Try echoing out your variable that possess the password. If it's the original password that you keep seeing. Then it's the same issue I had.