Using SQLite for Unit Testing in PHP

Unit testing is infamous when one attempts to test database-interacting classes. I noted that some had suggested using SQLite when running phpunit to test these sorts of classes. Been working for me, so I thought I'd jot it down here.

First, make sure sqlite3 is installed. On Ubuntu you'd run the following at the command prompt (assuming this step is necessary, but now I'm not quite sure):

sudo apt-get install sqlite3 libsqlite3-dev

Next, make sure PHP support for sqlite3 is installed enter this at the command prompt:

sudo apt-get install php5-sqlite

And then, in the part of your PHP script where you set up PDO (if that's the class you use, as I do), you can specify sqlite's in-memory DB:

$conn = new PDO('sqlite::memory');

SE Linux and "Can't connect to MySQL server"

I've been setting up a server for the last few days for production. It is to be highly secure, so it is a real pain. Anyway, I ran into this problem where I could not connect to the remote database. It was driving me nuts. (I'm using CentOS by the way, and I was setting up a web server to connect to a remote MySQL database server.)

First, I checked that I was getting network connectivity by doing

telnet 10.0.0.1 3306

from the command line (where 10.0.0.1 is the IP of the database server). And, I got some gobbledygook that had the words "MySQL" in it, so I know that worked and the network was there.

Then, I checked the database's user list, to make sure connections were allowed from the user at my host. I did this by going into MySQL's CLI and entering

SELECT `Host`,`User` FROM mysql.user WHERE 1;

This printed out a list of all the users so I just made sure it and the host were correct. (There are more MySQL troubleshooting tips here.)

Then it finally hit me. What always causes problems for hours on end? SE Linux!! I know it is good for my server, but it sure does drive me nuts sometimes. If you want your web server to be able to connect to a remote DB, you have to give it rights with SE Linux. Run this at the command line:

setsebool -P httpd_can_network_connect_db on

and for good measure:

setsebool -P httpd_can_network_connect on

There, all fixed!

Update: HA HA! I've had this problem before and completely forgot about it.

Database connection (mysql_connect) taking a long time

After setting up a new database, the connection from the web servers was really slow. We had enough combined traffic to slow down the response of the PHP function mysql_connect() to between 5 and 20 seconds. But the current load on the MySQL server wasn't that high... something fishy was going on.

What I found was that the database server was trying to do a reverse look-up on every connection using the IP. It was slowing down every connection for this. You can disable this by using the following option, as explained here.

--skip-name-resolve

"Do not resolve host names when checking client connections. Use only IP addresses. If you use this option, all Host column values in the grant tables must be IP addresses or localhost. See Section 7.9.8, "How MySQL Uses DNS"." --> http://dev.mysql.com

Anyway, you can make this change in the configuration file to be loaded at start-up (it doesn't have to be a command-line option). Simply add "skip-name-resolve" on a new line in the /etc/my.cnf file and restart your DB server. Voilà!

MySQL and mysql_select_db

I was convinced that mysql_select_db() was not working today. I wanted to simultaneously connect to two different databases. However, when I called the mysql_select_db function, it seemed that both connections were changing to the database I specified. I couldn't get one link to stay on the original database while I changed the second link to a new database.

After a little digging around I found a helpful user comment about using mysql_connect. The fourth parameter of this function is "new_link" and if you want to simultaneously have two connections open and both connections use the same credentials, you must set this parameter to TRUE. If you don't, it sees that the credentials are the same and will reuse the same resource link, even if you set a different variable.

So, this:

<?PHP
$conn1 = mysql_connect(HOST, USER, PASS);
mysql_select_db(TABLE_1, $conn1);  
$conn2 = mysql_connect(HOST, USER, PASS);
mysql_select_db(TABLE_2, $conn2); 

turned into this:

<?PHP
$conn1 = mysql_connect(HOST, USER, PASS, true);
mysql_select_db(TABLE_1, $conn1);  
$conn2 = mysql_connect(HOST, USER, PASS, true));
mysql_select_db(TABLE_2, $conn2);

And tada, I could simultaneously connect to two (or more) databases.

IP Geo-Location Database

In a previous post I talked about a nice website that will help you block countries from your site using the IP address.

In a related stream of thought, I found an entire database of IP addresses that is available for free. The nice people at IpInfoDb have an entire database of IP-to-location data available for download (in SQL, too). And, they give you some tips and PHP code on how to most effectively use it. Thanks IpInfoDb!