Thread

We moved our EE install from one CentOS server to another. After the move, we started to have some seemingly unexplainable issues with our MySQL database. In our database, we have two users, root and .(JavaScript must be enabled to view this email address). This is the same set up we had on the original server. Once everything was set up on the new server, we open up the main page and we start clicking around. Once we get to a page that makes a database connection to a different database (but using the same user1@localhost) we just get a blank white screen. If I switch the connection to use the root user instead of user1, it works just fine.

So at that point I start checking permissions. Originally I had just issued

grant all on *.* to user1@localhost;

grant all on database1.* to user1@localhost;

That didn’t fix it either, so I start issuing the same command, but for database2, database3, etc.. At that point I noticed that the main page quit working! By granting the user1 user MORE permissions to more databases, the site quit working. As soon as I revoked all the permissions I had just added, it started working again.

So for a band-aid solution, we have the main database being accessed using user1@localhost, but any additional database connections are using the root user. Everything is working like expected. The problem is, we don’t want to use the root account. Plus, my developer only has access to the user1@localhost account which at the moment only has privileges to the main database. If she wants to make edits to any of the other databases, I’ll have to grant the user those privileges, and as soon as I do that, the web page quits working.

It’s a very odd situation. Hopefully someone out there will have some answers! Thanks in advance. Let me know if you need any more info about the database or set up.

csmkt,

Welcome to ExpressionEngine and the Forums!

When you reference other databases, what exactly does that mean? Are you using other EE databases? Is so, how so? Perhaps a higher level explanation about what you are trying to achieve will help us get you there.

Cheers,

By referencing the database, we mean:
We make a MySQL connection in php to the host and then call the database using php. This database is not the database we have designated for ExpressionEngine data.

For the host, we currently have two Users that we want to have different permissions:
Root (all-powerful), and User1 (limited)

We are able to connect to the ExpressionEngine database with no problem using User1, but connecting to any other databases on the same host with User1 breaks our ExpressionEngine site. However, we are able to connect to other databases on the same host using the Root user without breaking the ExpressionEngine site. Granting all permissions to User1 breaks it- see line 1 of code above. Granting explicit permissions (database specific) to User1 breaks it- see line 2 of code above.

We are using the ExpressionEngine databases that were installed by default and populated via ExpressionEngine’s backend.

Appreciate any help and information!

csmkt,

That certainly sounds like an issue at your host perhaps. I have several databases installed locally that I have assigned to one user, “engine”. That user has the same permissions on several databases with no ill effects. Have you spoken with your hosting provider about these issues?

Cheers,

We’re hosting it ourselves. Both the original server and the new one we migrated EE to used the yum package installation of mysql with no additional configurations. I’ve looked through your installation and set up guides and made sure we’re following every database specification that you’ve laid out.

We don’t actually get any mysql errors. I turned on general query logging in mysql so I could see every query that the website is doing. I then compared the queries while using the root user with the queries while using the user1 user. The queries are identical for the first 20 or so queries, but then the user1 queries just stop (at which point we would receive a blank white page in the browser) while the other does another 20 or so queries. It actually issues “exit” to mysql to kill the connection, so I don’t think it’s timing out or anything.

I’m not sure I fully understand the “connecting to multiple databases” part of things. Do you mean to say that you simply have other, unrelated-to-EE web software running on your server for which you would like to allow the same MySQL user to connect to that application’s respective database?

If that’s the case, that’s fine, I’m just trying to sort out whether or not you meant you had a single EE installation trying to use multiple databases.

Either way, I’m afraid this is a MySQL server related issue, and not something we’ll be able to help with. When it comes to troubleshooting database server configurations, I know just enough to be dangerous, so I tend to rely pretty heavily on Google in those situations. The only bit of help I can really offer here is to take EE completely out of the picture by using the database testing utility found at system/expressionengine/utilities/dbtest.php. Just move that file into your web root, fill in the proper DB details, and run that PHP file straight. That’ll help you test your connections without EE in the picture.

This was fixed by setting “Database Connection Type” to “Persistent” in Admin > System Preferences > Database Settings.

Hi csmkt,

Thank you for sharing your solution. In general ExpressionEngine doesn’t need that, but that is dependent on your database server’s expectations. If you need assistance in the future, let us know by starting a new thread.

Cheers,

resolved