Tag Archives: mysql

Posted on
by

Just a very quick one, really as a aide-mémoire for when this inevitably happens again and leaves me scratching my head.

So, if you’re creating a foreign key relationships between two tables in your mysql / mariadb database, and you get errors along the lines of:

errno: 150 "Foreign key constraint is incorrectly formed"

or

Failed to add the foreign key constaint. Missing index for constraint 'BLAH' in the referenced table 'whatever'

and you’ve checked that your statement is correctly constructed and otherwise correct, check that the tables in question are the same collation using show create table TABLENAME

It is easy to miss, but foreign keys can only exist between tables of the same table collation and type. So, if they’re different, you’ll need to do an alter table, e.g.

alter table TABLENAME convert to character set utf8 collate utf8_unicode_ci;

Hope this helps someone!

Posted on
by

So, I’ve been quite busy recently.

I’ve made some decisions in my personal life that have resulted in a bit of a change of direction and focus for me. 

It’s been exciting, and has necessarily meant some changes. This has given me the opportunity to “sharpen my tools”, and so I’ve been getting around to playing with a bunch of technologies that have always been on my “weekend project” list, but they never made it up the priority list.

This isn’t directly related to the title of this article, but provides context. Since, as part of one of these projects, I found it necessary to populate a database with the contents of a bunch of rather unwieldy CSV files (within a docker container, so that their contents could be exposed by a NodeJS/Express REST API, but I digress).

It was while reading the various man pages, before launching in to writing a script, that I found this little gem. This meant I could do the import and provisioning of my docker instance straight from the /docker-entrypoint-initdb.d SQL scripts.

First, create your tables in the normal way, defining the data types that are represented in your CSV file. For example:

CREATE TABLE locations (
   location varchar(128),
   latitude DECIMAL(11,8),
   longitude DECIMAL(11,8)
);

Into which, as you might expect, you’d want to import a long list of location coordinates from a CSV file structured as follows:

location, latitude, longitude
"Oxford", 51.7520, 1.2577
"Edinburgh", 55.9533, 3.1883

Now, in your SQL, execute the following query:

LOAD DATA LOCAL INFILE 'locations.csv' 
INTO TABLE locations
FIELDS
  TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
IGNORE 1 ROWS (location, latitude, longitude);

Which tells MariaDB to read each line from locations.csv into the locations table, skipping over the first line (which contains the header).

This little trick meant I was able to provision my api’s backend quickly and easily, without the need to hack together some arduous import script. 

Hope you find this useful too!

Posted on
by

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!