Use the hook_field_schema function from your custom module’s .install file

Many custom modules will not need to add information directly to your MySQL database. But if you do want to store new info from your module, you’ll need to include a .install file with your .module and .info files and use the hook function listed above to do this.

This references a great book that I picked up from Yeah, I know; another shameless plug. But it is worth it.

This is the code that you need to place in your install file. You’ll note that it looks similar to the SQL statements that you would use to add the info manually.

* Implements hook_field_schema()
function countryinfo_field_schema() {
$columns = array(
‘country’ => array(
‘description’ => ‘Two letter ISO country code of this
‘type’ => ‘varchar’,
‘length’ => 2,
‘not null’ => FALSE,
‘default’ => ”,
return array(
‘columns’ => $columns,

If this is the type of development you want to be able to do, take a look at the book at

Drupal Site indexing – MySQL Errors, CRON timeouts

Since I recently dumped almost 17,000 new nodes into my DB in a relatively short time, I have been keeping a close eye on how the back end is responding. The main concern that I have is the indexing process for the new data. I began receiving errors from MySQL as seen in the SS.

In the Drupal Admin UI, check the number of nodes that are indexed per


CRON run. In my case, I had it set to 500, the maximum. This was a bit of overkill and I ended up making the value lower. I also increased the PHP memory, which you can check under “status reports”.

…PHP 5.3.27 (more information)
PHP extensions Enabled
PHP memory limit 512M
PHP register globals Disabled…

I really had simply to tweak the settings. I ended up at 100 nodes per run, one run per hour and PHP mem allocation as seen above. From the Search Options UI, you can see the status of the indexing, how fast, how many nodes, etc.

Good book for PHP, MySQL and Apache

I have been working with this particular book now for a couple of months now and I have to say that it is one of my favorites.

Sam’s Teach Yourself PHP, MySQL and Apache. – this link goes to Amazon.Image

This book is perfect for where I want to go with Drupal. xAMP is the Drupal backbone and this book covers everything that I need from the last three letters of this acronym. There code sample are well documented and helpful. Check it out if you plan to be able to develop for Drupal and not just implement the modules and themes that are on

Aggregators, CRON Jobs and Drupal cleanup.

This was a really involved project. If you use the Aggregator Core module a lot, take a look. I depend on Aggregators more than anything right now and have really had to do some involved work with it. Read on:

I have aggregator needs that the core doesn’t really quite give me. but it does work pretty well. here is what I collect:

  • 50+ feeds from various newspapers culled hourly resulting in several hundred articles per day.
  • Each RSS source is categorized (automatically, by default in drupal) as z-Uncategorized which corresponds to a CID (in the drupal DB) of 22. 
  • As the articles come in, I review and categorize them. I have a shortcut to the z-uncategorized category of items. That gives me all the new items, regardless of the source in one place where I can categorize them quickly by clicking on the categorize tab provided by Core. I keep about 10% of the stories that come in.
  • Because the newspapers maintain articles in their RSS feeds for a period of time beyond my control, they are readded to drupal’s DB whenever the feed is pulled; but now listed with two categories. There are now two entries for each of these stories with the same IID but a different CID. It looks like this below. There is the default z-uncat… category and the Juvenile category that I chose before the feed was queried again.
  • Even thought this looks like one record, it is really two different records in the tables. So, if I look at the aggregator_category_item table, I can see two records for the one IID. One with CID of 22 (the default, z-uncategorized) and the other of whatever I assigned it to. So, I can run a query and delete all with category 22. But, until the newspaper removes it from THEIR feed, it continues to come through.
  • I perform a nightly clean up where I delete all the 22s. This occurs when the papers are slow and new items have all been categorized by me.
  • Eventually (after a few days for most news sources) the stories are removed from the papers’ RSS feeds and do not get repopulated in Drupal with the default of CID 22. So then I am left with a nice single record in the category that I have assigned it to. By cleaning up every night, I get rid of stale 22s as the newspaper removes them from their RSS feed and I don’t have to think about whether they still have it or not.


This is the cron job that I have to do the clean up.

0 22 * * * /usr/bin/mysql –defaults-file=”/home/xxxx/.my.cnf_cron” -e “DELETE FROM drupal.aggregator_category_item WHERE aggregator_category_item.cid = 22” >>/dev/null 2>&1

The .my.cnf.cron file contains authentication information


The user and password is a mysql specific user I created for this job.

The 0 22 * * * means that it will run at 10 PM EST every night. EST because that is the time zone for the server.

Here are the specific rights for the crondel account name for the drupal DB, named, drupal.

GRANT USAGE ON *.* TO ‘crondel’@’localhost’ IDENTIFIED BY PASSWORD ‘*6E52D2AA6010C379DE1AE3BC559E2416A9A5C513’
GRANT SELECT, DELETE ON `drupal`.`aggregator_category_item` TO ‘crondel’@’localhost’

The account needs SELECT rights to execute the WHERE condition of the SQL statement in addition to the DELETE FROM on the specific table in the DB.

You might ask, why not do all this with Feeds? Well, I did try to do it with Feeds. I spent quite a bit of time with it. But feeds grabs each RSS item as a node. And I could not figure out an easy way to categorize the hundreds of stories per day when they all come in as nodes. And since this DB will eventually be huge with 100k+ stories in a searchable archive, I think that it may be easier to keep it this way. I just had to figure out what to do with the extra 22s. And this solution seems to work.

Ug. This was a pain. And if you want to know more about the subject or I have been unclear, let me know and I’ll try to clarify.

PHPMyAdmin – MySQL administration page

The main admin page for mysql is phpmyadmin. it is secured through a local firewall of sorts in the form of the phpmyadmin.conf file. there are explicit “allow” and “deny” statements for IPs here. I was a firewall guy for many years and this is actually a great security method. Nice and simple. KISS!

Anyway, because of some limitations in Drupal regarding aggregated news items, I need to have access to the sql db itself. I could do this via sql statements from the command line, but for what I need to do I need to see the tables and prefer a nice GUI. And PHPMyAdmin is a pretty nice GUI. Since I have my Drupal servers, (S, Q and Prod) secured via SSL, there is no issue with changing the security from deny to allow for any host other than localhost.

<Directory /usr/share/phpMyAdmin/>
<IfModule mod_authz_core.c>
# Apache 2.4
Require ip
Require ip ::1
<IfModule !mod_authz_core.c>
# Apache 2.2
Order Deny,Allow
# Deny from All
Allow from All
Allow from
Allow from ::1

this reflects the change that I made. Look at the # in front of the deny statement and the addition of the allow all statement. that’s all you need. BUT, make sure to have ssl enabled to protect your mysql password.

WAMP and Aptana code editor

well, i have all my great drupal environments setup. but as i started working with php code in aptana, i realized pretty quickly that i was going to need a local web server to test the php pages. so, install apache for windows xp. let me digress. windows xp is one of the best OSs ever developed. this MCSE isn’t big on MS much these days but XP is still a masterpiece. it will run so much stuff and run it well. i have a nearly ten year old dell running XP and it works great. it will run all the modern open source crap that i want and it is as solid as a rock. there is a reason that the biz community ran XP for as long as it did. and DOES. there are still millions of PCs out there in offices all over the place that run xp. there just isn’t a good reason to get rid of it when it will do 96% of what you need on hardware that is now pretty old. the only thing it won’t run that i actually care about is IE9. and i would never use that anyway if i didn’t have to plan my development around its css quirks.

i love XP. it’s great and it is a product like americans used to make: no planned obsolescence.

so, i’m installing a full WAMP server. most of the config for WAMP was pretty easy. the hard part was getting aptana to work properly with apache and the installed browsers so that the preview features could be used. for clues on that i am attaching a SS of what my server config looked like in aptana. it was a pain and i don’t believe that it was well documented. so, i posted what i did to stackoverflow here:

Import/export Aggregator feeds part II

well, i now have it running in prod and there were a few minor differences that i wanted to document.

since i don’t have an X window type desktop, i had to access the phpmyadmin via a remote web browser. so, i had to change the file /etc/conf.d/phpmyadmin.conf in three places to allow connections from all instead of just localhost. i was then able to access the web gui, import the file, and test the aggegator feeds. i had to chanmge som permissions as well to be able to access the file so i could edit it remotely. i could have done it locally via VI Editor, but i hate that thing. it sucks. so after the changes were made i made sure to change the perms back as well as change the file itself so that attempting to access the phpmyadmin gui from the remote host yielded again a “forbidden” error.

import/export aggregator sources – drupal 7 core module

So i have my three environments setup and i am moving module settings from sandbox to quality. i have dozens of aggregator sources that i need to move. and i don’t want to reenter them manually. so i am using phpmyadmin to export the table to an sql file and then import it.

well, i made this work. it was a bit of a pain but not too bad. i had to install phpmyadmin on my sandbox server and export the proper table (aggregator_feed) to a sql file. i then sneakerneted the file to the new server. i had to install phpmyadmin on that server too. that was a bit more work. once i had it installed i was unable to access it without changing the root mysql user from ‘blank’ (no password) to ‘something’ (one of my password defaults). then i was able to import the sql file into the drupal db. since this is a core mod, i didn’t have to do anything to get the file to import properly. then, since i had had changed the password of the user that drupal runs under, i had to change that in the settings.php file used by drupal. done and done and it only took a couple hours.

Centos Dev Server up and running

Well, I have finally gotten my dev server up and running. now my dev matches prod. CentOS 6.3 is the choice by my provider, and now it is my choice as well.

there were some challenges getting centos to work with drupal but nothing too bad and it ended up taking about half a day.

LAMP and VMWare player

so i’m running drupal 7.16 on my windows 7 laptop. well, it’s on a vm on the laptop. i’m running vwware player. it’s free. and works well. running ubuntu 12.06. i wrote a good doc on how to do this. i’m an IT pro, 15 years but i have been almost completely focused on MS solutions. so this doc is good for someone who knows MS but wants to move towards open source. the doc is in ms word. here is the stuff from the docs. if you want the doc itself, send me a message and I can get it to you.

Procedure – Installing Drupal 7.16 on Ubuntu Desktop 12.04 LTS on VMWare Player hosted on Windows 7 Home 64 bit

Date: 10/22/2012

Author: PJ McGhee



Procedure:            New                                                              

                                Replace Existing                    


Intended Audience:

IT Pros who have been traditionally Microsoft based but are moving towards open source/linux based applications


This document explains the steps to install Drupal 7.16 on a VMWare Player hosted Ubuntu Desktop12.04 to create a development environment on a Windows 7 64 bit laptop. There are ways to do a “one click” install for this purpose. This procedure is intended to describe how to install all the separate components manually, giving the user a better understanding of the underlying technologies that support Drupal.

This may be of particular value for those (like me) who have traditionally been Microsoft oriented who now embrace open source. The document assumes then, a high level of familiarity with install procedures and other broad IT concepts like virtualization, web services, databases and systems administration regardless of the platform that it is being implemented on.

The overall objective for the first section is to successfully install what is known as a LAMP server. This is just an acronym for Linux, Apache, MySQL and PHP. Together, these elements form a platform, both scalable and powerful (free too), that can be used to implement many different open source applications. They form the backbone of the Drupal server, providing OS (Linux), Web Services (Apache), Database (MySQL) and Programming Language (PHP). There are many smaller packages that need to be installed as prerequisites and they are all detailed in the procedures.

Since familiarity with Linux and its components is assumed to be limited, this document describes in a fair amount of detail, how to achieve a dev/test environment on a Linux Ubuntu Desktop install running on a VMWare Player hosted on a Windows 7 laptop. This is what I am running. Ubuntu Desktop provides a familiar interface with the desktop feel. VMWare Player allows Linux to be run in a way that doesn’t impact your laptop much while still allowing internet access. is an awesome site. Do yourself a favor right away and register with the site. Take some time to check out the different areas that are available. Get to learn where the resources are located. This will help you as you start to develop sites with Drupal. There are several newsletters that you can join as well that will aid you in learning this great product.

Open source is awesome and the community behind it is superb. But open source requires more time and work to achieve a clean install. The readme files in the packages you will be installing are very important for the procedures; always make sure to reference them! If you encounter errors along the way, remember that Google is your friend. The first time that I went through this install I had to work my way through errors at almost every single turn, usually because a prerequisite was missing, which is my fault for not reading enough beforehand which is arguably difficult because there is simply so much information out there. But, I was able to work through every single issue by Googling the message and following the tips offered. It takes time but in the end it is worth it because you gain a better understanding of the underlying systems. That’s why I eschew the “one-click” packages available for Drupal. And why I am not running Drupal on Windows. There are no Install Shield wizards for these installs but there really isn’t anything too onerous either: it’s just a lot of steps. And this is a great way to start learning Linux.


Caveats and Disclaimers

I’m not a Linux guy. I have spent my 15 professional years in IT mostly designing/implementing/supporting systems based on Microsoft products. There are sure to be much better ways to do many (most) of the things in this procedure. I won’t be the one to argue that. Please email me any suggestions. I simply want to help people who have traditionally focused on Microsoft products move into open source. Especially Drupal, which is a great product. That being said, I make no guarantees about the procedures in this document or their impact on your systems.


Common Linux commands/tips –


  • Ctrl-Alt-T                                                  opens command prompt on Ubuntu
  • Dir                                                            shows list of directories
  • Ls                                                            shows list of files
  • ls –l                                                          show files with perms, owner, etc
  • cd ..                                                        moves up one dir level
  • cd /                                                          takes you up to the top level
  • chmod                                                     command to change permissions – see docs for syntax
  • apache2 –v                                             tells you the version of apache you have installed
  • mysqladmin -u root -p version                show version of MySQL installed






  • Admin access to a Windows 7 64bit Host OS
  • VMWare Player 4.0.4 Build 744019
  • Linux – Ubuntu Desktop ver. 12.04 LTS 64 bit
  • Autotools
  • Apache 2.2.22 Web Server
  • MYSQL 5.5.24
  • PHP 5.3.10
  • APR 1.4.6 APR-Util 1.5.1 – Apache components
  • PCRE 8.31
  • G++ via Build-essential


Estimated Time to Complete Procedure:

240 Minutes

Contact Information: – Feel free to email me if you have questions about this procedure.

Objective 1: Install LAMP server



Build the Ubuntu Server

  1. Install the free VMWare Player ver. 4.0.4.
  2. Start VMWare upon completion.
  3. Click File, New Virtual Machine.
  4. Browse to the Ubuntu .ISO file.
  5. Enter your name
  6. Give the new virtual server a name relevant to your purpose – Drupal-Dev in this case
  7. The install will prompt you for a user to be created. Make the user drupal-dev and the password password.
  8. VMWare tools for Ubuntu Linux will be installed automatically after the first reboot.
  9. Login to the new virtual Ubuntu Linux machine when prompted.
  10. Click the Cog icon in the top right corner of the desktop and choose “available updates”. There will be approximately 433 (274 MB at the time of writing) updates that will need to be downloaded and installed. Make sure to check for updates regularly as the components are installed. The VM must be able to browse the internet.

Enable the root account [i]  – – this is a good reference for this part

  1. 11.     Hit Ctrl-Alt-T to open a terminal window. Remember this; it will be used a lot
  2. Type sudo passwd root and hit enter – set a password for the root account
  3. Type su root and supply the password for root – you are now using root privileges
  4. Type sh –c ‘echo “greeter-show-manual-login=true” >>/etc/lightdm/lightdm.conf’
  5. Reboot the server. You will now be able to choose the user name with which to login.
  6. Login as root with the password of password




Download Components

Open the Firefox web browser from within Drupal-dev. Some of these packages will be downloaded and installed manually and some will be installed via apt-get ( but it is a good idea to look at the web site of each individual component.

  1. Autotools for Ubuntu –
  2. Apache 2.2.22 – – reference this link
  3. APR 1.4.6 – Apache component –
  4. APR-Util 1.5.1 – Apache component – “
  5. PCRE 8.31 –
  6. Build Essential –
  7. MySQL Server Installed via apt-get but reference
  8. PHP 5.4.8 – installed via apt-get reference
  9. Drupal Core 7.16 – recommended release, not development –
  10. Open – This is an excellent resource for the overall procedure to install Drupal.


Install Autotools  for Ubuntu –

  1. Go to and download the libtool (all you really need) package or, skip to step 5 to use apt-get to get the whole Autotools package.
  2. Double click the downloaded package.
  3. The UbuntuSoftwareCenter will open.
  4. Click install. This feels more like a Windows installer process which is why I included it.
  5. Or, you can use apt-get to download and install the package.
  6. Hit Ctrl-Alt-T to open a terminal session window
  7. From the terminal session type apt-get install libtool automake autoconf and hit enter.
  8. Click Y to confirm the needed space.

Install Yum

  1. Yum and apt-get do the same things but you may end up liking Yum more than apt-get and it is good to know about some of the install options available.
  3. Hit Ctrl-Alt-T to open a terminal session window.
  4. Type apt-get install yum.
  5. Confirm the space needed for the install.

Install Build Essential

  1. This will give you G++ which will be needed, plus others.
  2. From the command prompt type apt-get install build-essential and hit enter.
  3. Confirm disk space.


Install APR

  1. Click the Home Folder icon in the top left of the desktop and go to downloads.
  2. Double click (DC) the APR-1.4.6  folder. Right click the contents of Archive Manager and choose Extract.
  3. Extract files to the downloads folder. You’ll now have a folder called apr-1.4.6 along with the tar packages.
  4. Open a terminal window.
  5. Linux can be a little confusing in regards to directory structure. A simple way to ensure you are at the root level is to type simply type cd / and hit enter.
  6. Once you’re at the root level you need to get to the APR-1.4.6 directory.
  7. Type dir to ensure that you’re at the top. You’ll see directories like boot, bin, home, root, var.
  8. Type cd root and hit enter – your prompt should go from

root@ubuntu:/# to root@ubuntu:~#

  1. Type cd Downloads – this is case sensitive – hit enter.
  2. Type cd apr-1 and hit the tab key – this will complete the command – hit enter.
  3. Type ./configure and hit enter. Many scripted commands will now run. At the end you will see an error about libtoolsT cannot be removed. This can be ignored.
  4. Type make and hit enter – more scripted commands.
  5. Type make test and hit enter – this will take a couple of minutes.
  6. Type make install and hit enter – this will be fast.
  7. There will now be an APR-1.4.6 folder in the /usr/local/src directory.

Install APR-Util

  1. Extract the APR-Util folder to downloads.
  2. Go back to the terminal window and make your way to the APR-Util-1.5.1.
  3. Type ./configure –prefix=/usr/local/apr-util –with-apr=/usr/local/apr/ and hit enter.
  4. The prefix part specifies where the apr-util files will go and also specifies where the apr files are located.
    1. Type make and hit enter.
    2. Type make install and hit enter.


Install PCRE 8.31

  1. Extract the files to the download directory.
  2. From the terminal session go to the PCRE-8.31 directory.
  3. Type ./configure and hit enter.
  4. Type make and hit enter.
  5. Type make install and hit enter.


Install Apache

  1. At the command line type  apt-get install apache2 apache2-mpm-prefork and hit enter.
  2. Confirm that it is OK to use the additional space.


Install MySQL Server

  1. From the terminal session type apt-get install mysql-server and hit enter.
  2. Confirm Y to use the additional 97.1 MB of space.
  3. Enter a password for the root account (user name  =  root) when prompted. You’ll be prompted twice.

Install PHP

  1. Install mcrypt first.
  2. From a terminal session type apt-get install php5-mcrypt and hit enter.
  3. Choose Y to confirm the disk usage.
  4. Install Libxml2 dev files.
  5. Type apt-get install libxml2-dev and hit enter.
  6. Type apt-get install libapache2-mod-php5 and hit enter.
  7. Type Y to use the needed space.
  8. Type apt-get install php5-gd hit enter.
  9. Type Y to use the needed space.
  10. Type apt-get install php5-common php5-mysql and hit enter.


Test the installed components


Test Apache

  1. Open the Firefox browser and navigate to http://localhost.
  2. The default directory is in /var/www the page is index.html.
  3. If you see a plain page that says, “It works!…” the install is working.


Test MySQL

  1. From a terminal session type netstat -tap | grep mysql hit enter and you should see:
  2. tcp                   0              0 localhost:mysql    *:*            LISTEN931/mysqld
  3. This indicates the server is running.


Test PHP

  1. Open /var/www and create a new test php page called test.php using the test editor.
  2. Enter the following PHP code:

<?php phpinfo(); ?>

  1. Save the file.
  2. Browse to the url http://localhost/test.php.
  3. You should see a web page with quite a bit of PHP information.
  4. If you are asked to download the file PHP is not being parsed by Firefox.
  5. Reboot the server.

That concludes the installation of the LAMP server. Hopefully, it wasn’t too bad. Thanks for your patience and I hope that you might have learned a thing or two about the Linux world. I know that I did! Now it is time to move on to Object 2 – the install of Drupal Core itself


Objective 2: Install Drupal Core


Prepare the Drupal install files and create needed directories


  1. Extract the Drupal 7.16 to the download directory.
  2. Move the Drupal files via the next line’s command.
  3. Type mkdir /var/www/drupal and hit enter.
  4. Type mv drupal-7.16/* drupal-7.16/.htaccess /var/www/drupal and hit enter.
  5. Go to the extracted files and open the install.txt file for install procedures.

Create the MySQL Drupal DB

  1. Open the INSTALL.mysql.txt file in the ./Drupal-7.16 directory.
  2. Open a terminal session.
  3. Type mysqladmin –u root –p create drupal     where root is the user name and drupal is the name of the database that we are creating for the install.
  4. You will be prompted for the password that you supplied for the root account during the mysql server install.
  5. Login to mysql and set access rights by typing mysql –u root –p and hitting enter.
  6. Supply the password again.
  7. Now type this query to provide rights:
  8. GRANT ALL PRIVILEGES ON drupal.* TO ‘root’@’localhost’ IDENTIFIED BY ‘password’;
  9. Be very careful with the syntax here. Notepad may add characters that will interfere with a copy and paste method. The single quotes can also be different from Windows to Ubuntu. The syntax here is correct; you need to make sure that it is being transferred properly.
  10. Note that password is the same as supplied before.
  11. Successful assignment of perms will show as:
  12. Query OK, zero rows affected.
  13. Type exit and hit enter to leave mysqladmin.

Run the Drupal installation script and prepare files


  1. From a terminal session type mkdir /var/www/drupal/sites/default/files and hit enter.
  2. Type cp /var/www/drupal/sites/default/default.settings.php /var/www/drupal/sites/default/settings.php and hit enter.
  3. Type chmod 777 /var/www/drupal/sites/default/files and hit enter.
  4. Type root and hit enter.
  5. Open Firefox.
  6. Browse to http://localhost/drupal/install.php.
  7. Leave the radio button at “Standard” and Save and Continue.
  8. Leave at “English” and click Save and Continue.
  9. Verify Requirements will now appear.
  10. Correct any missing requirements and click Proceed
  11. You will now be at the Database configuration screen.

Supply the name of the database, user and password that you specified in step 3 of Create the MySQL Drupal DB. In this case, the db name is drupal, the user is root and the password is password.

  1. You should not need to change advanced options.
  2. Click Save and continue.
  3. The Install profile steps will be run.
  4. You will now be at Configure site and you’ll need to supply some SITE INFORMATION.
  5. localhost is fine for this dev environment.
  6. Any email address will be fine for now.
  7. Now for SITE MAINTENANCE ACCOUNT – This is important because this Drupal specific account will have rights to do everything in your Drupal install.
  8. User name is root, email address is and the pass word will be password.
  9. Under SERVER SETTINGS choose the country settings and time zone appropriate for you.
  10. Leave UPDATE NOTIFICATIONS the way they are.
  11. Click Save and Continue.
  12. You should now be at Finished.
  13. One last step – VERY IMPORTANT!
  14. From the terminal session type:
  15. chmod 640 settings.php and hit enter.
  16. chmod 755 ../default and hit enter.
  17. Click the link to visit your site.
  18. CONGRADULATIONS! You have now implemented an excellent Drupal dev environment.

[i] Linux gurus will probably tell me that there is a better way to do this and I am sure that there is but I’m just an MCSE trying to make it in an open source world! Please send me your recommendations.