Posts

Making an external connection to a Blaknight MySQL database

/in /by

You can connect to a Blacknight MySQL database from an external tool (e.g. mysql or mysqlimport commandline) if you change some of the database’s settings. This can be useful if you need to manage your database, for example if you need to perform a large database import (one that is so large it causes phpMyAdmin to time out).

First you need to configure the database to allow external connections, in the Blacknight control panel choose to edit the database’s settings and check the ‘Access from external hosts’ check-box. This change on its own isn’t sufficient, next you must add your ip address to the ‘Access Hosts’ list.

enable_external_access

When on the database properties page, click on the ‘Access Hosts’ tab, click on ‘Add new access host’ and add your ip address. To find your IP address, just google ‘my ip address’ and your address will be displayed near the top of the results page.

add_ip_address_blacknight

Once you have made these changes you should be able to make external connections to the database. When you are finished it is probably a good idea to remove external access again.

You can now do something like import a database, get the hostname from the ‘external hostname’ field on the blacknight control panel:

mysql --host="mysq12666.cp.blacknight.com"   --user='u1273449_the_user' --password="ThePassword" database_name < ./db4.sql

Swapping Database Column Values (mySQL)

/in /by

Software Developers know their away around SQL – it is a tool of the trade (as it were!), however today I had to revert to google to find the SQL to swap the values of 2 columns within a table. Luckily I found this interesting (and very handy) article that explains just how to do it:

 

http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/

 

I had to reverse the relationships between drupal taxonomy terms, this was to be done by reversing the values of the tid1 and tid2 columns of the term_relation table, this code did the trick for me:

 

[code]
UPDATE term_relation SET tid1=tid2, tid2=@temp WHERE (@temp:=tid1) IS NOT NULL;
[/code]

 

If I had to manually reverse all of the taxonomy relationships via the drupal admin interface it would have taken _forever_!

 

PS. If, for a given taxonomy term id, you need to get a list of the related taxonomy terms, just call:
[code]taxonomy_get_related()[/code] although be warned that the concept of related terms is to be removed in drupal 7!