Cannot seem to get access to mysql database with user I created

[Another_Omeka_User]

Hi, I have checked the forums and posted to Twitter, but no luck yet.

I am the main admin for a VPS with CentOS 7 + Plesk 12.5. So far, it's still very much out of the box, I haven't made too many modifications. We have one domain on there right now that I'm trying to set up. I exported the old server's database contents via phpMyAdmin and reimported with no errors into the current one. I uploaded the old files into the new directory.

It's a very standard Omeka installation (https://omeka.org/codex/Installation) and something I've installed numerous times on servers before. The php files connect to the database in the main directory via a very, very simple db.ini file that requires all I do is replace the database username and password. (I can provide a sample file if you need to see what I mean). So I created a new mysql user in phpMyAdmin, gave it a new password and global privileges (which is fine because it's the only site and I'm the only admin), and tried it out but no matter what I got the Access denied @ localhost error. So I logged in via ssh at command line, plesk db, and set the password directly via command line. (I also noticed that the phpMyAdmin encrypts the passwords funny so that it does not look the same as what I entered in phpMyAdmin.) Anyways, suddenly everything worked, no more access denied, and all the content I imported looked the same as the old server.

Everything fine, right? So it seemed, yes... for about a week or two. But now something happened -- maybe an update to the server? -- because it was working until this week and now it's not working again. I am getting the same 'access denied 'new_user'@'localhost' ' error as before. I logged in via command line and, sure enough, my password for that new user has been reset back to something different (not what I manually set it to before). And now nothing I do to reset the password seems to be working, even using that same script.

I'm very frustrated because I have never experienced this error on any of the other three servers I've run Omeka on before -- then again, I'm usually in charge of setting everything up myself through phpMyAdmin and mysql at command line and not going through a web panel like Plesk... there's something odd happening when I try to add a user. It simply won't connect that user to the database I need it to connect to.

Please help. Am I missing some crucial step or is there something wrong with the Plesk mysql password generator?

So what am I doing wrong? What do I need to do?

 

[Another_Omeka_User]

So, this is temporarily working again following a restart of the mysql server... can someone please explain if I am doing this right? I could not get the mysql user to work AT ALL when I set the password in phpMyAdmin, but when I reset it at command line it works, at least temporarily, that way. What do I need to do to get it to work with the encrypted password via phpMyAdmin???

 

[abdi]

So I created a new mysql user in phpMyAdmin

Why are you not doing this from Plesk instead?

 

[Another_Omeka_User]

So, I did try that as well (after doing it the other way) and I couldn't figure out how to - through Plesk - assign the user to an already-existing database with data in it. Is there a way to do that?

 

[abdi]

Ah, I see ...
In that case, you can create the user and assign the respective privileges through PhpMyAdmin or Shell

http://blog.dynamichosting.biz/2010/11/29/plesk-how-to-create-multiple-mysql-dbs-with-a-single-user/

 

[Another_Omeka_User]

I did create a user and assign the privileges via phpMyAdmin. Sorry if I wasn't clear in my initial message - that is indeed precisely what I did. Assigning privileges was not the problem. The problem was that the password I assigned in phpMyAdmin is encrypted somehow when it's stored. I need that password to put in the db.ini file to create the connection to the database for my php files. When I tried to use the *exact password I created in phpMyAdmin* in the *db.ini file* to make the connection, it didn't work. So, I also tried to go into the shell and then copy and paste *the encrypted password phpMyAdmin created and stored in the users table* and then put that in the db.ini file. That also didn't work. The only thing that did work was *manually creating the password in the shell, at command line, for the user I created via phpMyAdmin*.

In my understanding, creating the password that way shouldn't be necessary. It should be possible to create a user AND a password via phpMyAdmin.

Or am I missing a step or two somewhere here?

 

[Another_Omeka_User]

Additionally, I forgot to add: as I noted in my original post, it seems that everything worked fine like this until some update or something??? I set the password in the shell and then within a week or two (sorry, I don't work on the site everyday, so I don't know when exactly) some update or change on the server reset the password to something encrypted. The only thing that worked was resetting it *again* in the shell and then restarting the mysql server.