elgg_logo1 Here’s the scenario; you’re a developer and you’ve been asked to do some work on an existing Elgg site, or you’ve built an Elgg site with some complex plugin interdependencies that you need to copy on to a live site.

In both cases, this primarily involves copying the source code and Elgg database from one site to another, here’s how…

Source code and database

  1. Install the source code for your project; scp it from the other site, git clone –recursive, whatever…
  2. On the site you’re copying, take a dump of the database. You can look in your engine/settings.php for the database username and password:

    mysqldump -u your-db-user -p elgg_database > database-dump.sql

  3. Copy this file onto your new host.
  4. Create a new database and install the Elgg database into it, in the mysql client do the following:

    create database new_elgg_database;
    grant all on new_elgg_database.* to db_username@localhost identified by 'db-password';
    use new_elgg_database;
    source /path/to/database-dump.sql

  5. You should now have a local copy of the elgg database installed, but in order for it to work you need to change a few paths. Firstly, alter your dataroot and site location details in your prefix_datalists table:

    update elggdatalists set value="/path/to/elgg/" where name="path";
    update elggdatalists set value="/path/to/dataroot/" where name="dataroot";

    Don’t forget the trailing slash on the paths!

  6. Next, you need to update the site url in the site object stored in the prefix_sites_entity table. For the vast majority of people (who only have one site object) this will be straightforward, for others, you might have to use a slightly different query in order to get all sites working as expected.

    update elggsites_entity set url="http://localhost/path/to/site/";

    Again, don’t forget the trailing slash on the URL!

  7. Finally, alter your copy of engine/settings.php to reflect your new database details.

When I view my site, all the CSS is broken!

This is almost certainly a mod-rewrite problem.

  • Firstly, check that it’s installed and enabled, and that overrides are enabled for your site URL (common problem if installing into ~/public_html).
  • Next, make sure that your RewriteBase is configured. If you’re installing into a subdirectory on a domain (e.g. http://localhost/~marcus/elgg/) you’ll need to set the RewriteBase in your .htaccess file accordingly, in the case of my example, RewriteBase /~marcus/elgg/

Files

The above should get you up and running with a usable site for testing, however if you want to fully migrate a site, you’ll also need to copy the data directory across.

  1. Using rsync or similar copy the complete data directory from your old site’s data directory to the new.
  2. Ensure that the directory, subdirectories and files are read and writeable by your web server’s user.
  3. Flush the caches. This is important since Elgg caches the locations of template files and other data in the data directory, which obviously will cause issues if you copy a cache file from another location! If the admin panel has become unavailable at this point, deleting the system_cache directory from dataroot by hand will often restore it.

Happy hacking!

mysql-logo3 So, the other week, I migrated all my sites over to a new server. This was accomplished with minimum fuss, using lots of rsync magic and juggling of DNS ttls.

The part of the migration I imagined would be the most complicated, moving several tens of MySQL databases running on the old server to the new, turned out to be pretty straightforward, and essentially completed with one command. I thought others might find it handy to know how…

First, I brought down Apache on both servers, so I could be sure that nobody was going to try and write to the database. This may not really be required, since mysqldump can handle dumping live databases consistently, for MyISAM at least, but in never hurts to be paranoid.

Next, I needed to move all the databases, together with their access permissions and users (so the full MySQL schema) to the other server. One way to do that is to copy the whole /var/lib/mysql directory over. However, I had a lot of cruft in there (old bin-logs etc), plus there were a number of articles suggesting that the straight binary copy had a number of issues, especially for mixed storage engine environments. So, I opted for the mysqldump method.

Traditionally, this takes a lot of SCPing. Here’s how to do it with one command, using the magic of Unix pipes:

mysqldump -u root -pPASSWORD --all-databases | ssh USER@NEW.HOST.COM 'cat - | mysql -u root -pPASSWORD'

Boom. This ran surprisingly quickly for me, and you can of course just as easily use this method to transfer a single database.

Three gotchas:

  • If the link dies, you need to start again, so don’t do this over a flakey connection, and I suggest you run the command in a screen if the first server isn’t localhost.
  • You need to restart the database server on the target machine for the new users and privileges to come into effect.
  • On Debian, you may see an error along the lines of:

    Got error: 1045: Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES) when trying to connect

    Fix this by executing the command:

    GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'THEPASSWORD' WITH GRANT OPTION;

    Where THEPASSWORD is the password found in /etc/mysql/debian.cnf

One final note: this will only work if both servers are the same major version number. I was moving between two Debian 6.0 installs, so YMMV.

Happy Easter!

If you’re anything like me, you often find yourself needing to add create and modified timestamps to data stored in a MySQL database. Typically, I’d do this programmatically from within the application without giving it a second thought, wrapping up time(); in an SQL statement and firing it over.

Recently, while doing some development work for one of my clients, I was given the requirement to add Created and Modified timestamps to a whole bunch of existing data tables. These tables were referenced by hundreds of different MySQL queries, and to complicate matters further, we were in the middle of migrating the code over to a new database library.

I didn’t much fancy rewriting all of the SQL queries, potentially twice, just to add a couple of timestamps, so modifying code wasn’t much of an option. Thankfully, a couple of MySQL’s internal features came to the rescue.

Adding a ModifiedTime

Adding a modified timestamp to a table is the most straight forward. All your have to do is create the field of type TIMESTAMP, and by default, MySQL will automatically update the field when the row is modified.

There are a couple of things to be aware of:

  • While you can have multiple TIMESTAMP fields in a row, only one of these can be automatically updated with the current time on update.
  • If your UPDATE query contains a value for your ModifiedTime field, this value will be used.

So, to add your modified timestamp field to an existing table, all you need is:

ALTER TABLE my_table ADD ModifiedTime TIMESTAMP;

Adding a CreatedTime

Adding a CreateTime value is a little more involved.

On the latest versions of MySQL it is apparently possible to create a DateTime field with a default value of CURRENT_TIMESTAMP. This wasn’t an option for me as I was having to support a somewhat older version, besides, even on the newer versions of MySQL it is not possible to have more than one field using CURRENT_TIMESTAMP, which of course we are in order to get ModifiedTime working.

So, in order to get a created timestamp, firstly we must add a DATETIME field to the table.

ALTER TABLE my_table ADD CreatedTime datetime NOT NULL;

Note, that this must be created as NOT NULL in order for the next part to work (this is because setting NOT NULL forces an automatic all zeros default).

Next, we must create a trigger, which will automatically be fired when we insert a value into our table and set the created timestamp.

DELIMITER //
DROP TRIGGER IF EXISTS my_table_insert_trigger//
CREATE TRIGGER my_table_insert_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
IF NEW.CreatedTime = '0000-00-00 00:00:00' THEN
SET NEW.CreatedTime = NOW();
END IF;
END;//
DELIMITER ;

Now, when you insert a value into the table, this trigger will fire and, if you’ve not provided a CreatedTime field in your insert query, it will be set to the current time stamp.

Conclusion

Since all the queries in the application code specified the columns they were updating on insert, I was able to use this method to add created and modified time stamp fields to all the existing object tables in the database, without needing to modify any of the existing application code.

This simplified my life greatly, but also suggests to me that this method might be somewhat more efficient than the methods I’d previously used.