Category: Databases

Posted on in Databases, Mac, Software

MacPorts mysql5-server not working on boot

If you’ve installed MacPorts’ mysql5-server, you may find that it doesn’t work after a restart.

This can be resolved by running the following commands to make sure you’ve killed off everything MySQL related.

sudo port unload mysql5-server
ps -ax | grep mysql
sudo kill

Once you have done this, load it again.

sudo port load mysql5-server

Not it should be working. This is fine, but quickly gets very annoying every time you restart your computer! A common cause is that you have a MySQL sock file in your tmp directory. Removing this may allow it to start automatically.

cd /tmp
rm -f mysql.sock

Once removed, restart your computer and see if MySQL works first time – hopefully, it will.

Posted on in Databases, Software

Data missing from MySQL replication due to column mismatch

If you are running MySQL replication, you may find that the structure of a table is brought over, but not the data. You can troubleshoot this by logging into the slave MySQL and showing the status.

SHOW SLAVE STATUS \G;

You may find an error similar to the following.

Table definition on master and slave does not match: 
Column 1 size mismatch - master has size 150, example_db.test_table 
on slave has size 50. Master's column size should be <= the slave's column size.

This is usually caused because you are using different character sets. In the example above, the master is set to UTF8 (utf8_general_ci) while the slave database is set up in Latin1. You can fix this in two ways (and should probably do both).

First, to change the database, log into phpMyAdmin and go to the operations tab. Here you change it from the dropdown and then hit save.

Secondly, you should change the default charset in MySQL to be UTF8 (you could also change it to Latin1 if that is what your master is running but in this day and age, everything should really be running UTF8). You can do this by editing your my.cnf.

[mysqld]
default-character-set=utf8

After editing it, you will need to restart MySQL. Any databases created from then on will default to UTF8.

Posted on in Databases, Software

Updating phpMyAdmin on MAMP Pro

The phpMyAdmin that ships with MAMP Pro is now seriously out of date, so you’ll probably want to upgrade to the latest version. You can do this in a couple of easy steps. Firstly, download and uncompress the latest version of phpMyAdmin. I did it to my Desktop. Then, open up Terminal and enter the MAMP Pro folder and rename (best to rename rather than delete) the current phpMyAdmin.

cd /Library/Application\ Support/MAMP\ Pro/
mv phpMyAdmin phpMyAdminBackup

Next, copy in the new version of phpMyAdmin.

cp /Users/you/Desktop/phpMyAdmin-x.x-all-languages ./phpMyAdmin

Finally, you need to set up the configuration for the new phpMyAdmin.

cd phpMyAdmin
cp config.sample.inc.php config.inc.php
vim config.inc.php

Change the authentication type and add a username and password entry to the file.

$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'root';

If you’ve changed the MySQL root password on your MAMP Pro, you’ll need to enter the new password instead.

Once done, save the file and you’re done.

Posted on in Databases, Mac, Programming

Cassandra RubyGem on Mac OSX Lion

If you’re trying to run Apache Cassandra with the RubyGem, you may encounter an error similar to the following.

thrift_client(0.8.1 not ~> 0.7.0)

You can fix this by editing the RubyGem.

cd /Library/Ruby/Gems/1.8/gems/cassandra-0.12.1/lib/
vim cassandra.rb

Find the line which sets the dependency.

gem 'thrift_client', '~> 0.7.0'

Change it to the following.

gem 'thrift_client', '~> 0.8.0'

Your script should now run without an error.

Posted on in Databases, Software, Unix

FATAL: no pg_hba.conf entry for host ::1

If you’re getting an error trying to connect to Postgres on localhost, the problem is probably that your system is configured for ipv6 but your Postgres isn’t. You can change this however.

First off, you need to find the pg_hba.conf file.

locate pg_hba.conf

Edit it and find the line which defines the localhost.

hosts  all all   md5

Below it, add the following line.

host all all ::1/128 trust

Finally, restart Postgres.

Posted on in Databases, Software, Unix

Installing Postgres PDO driver on cPanel

cPanel offers two options for installing PDO – using EasyApache which can enable PDO and MySQL’s PDO driver or installing everything via PECL. Unfortunately, under PHP 5.3, the PECL installers don’t work, so if you need any other PDO drivers, you’re in a hole.

Luckily, you can install it manually.

Download the PDO driver from the PECL website. Extract the archive and CD into the directory.

wget http://pecl.php.net/get/PDO_PGSQL
tar -xzf PDO-PGSQL-1.0.2.tgz
cd PDO-PSQL-1.0.2/

Once this is done, run the standard commands for building a PHP extension.

phpize
./configure
make
make install

Once this is done, you can add the extension to php.ini.

cd /usr/local/lib/
vim php.ini
extension=pdo_pgsql.so

Finally, restart Apache and the Postgres driver should show up in your phpinfo() output.

Posted on in Databases, Software, Unix

Installing Mongo PHP driver on CentOS 6 cPanel

Once again, the PECL installer. In order to get the Mongo driver for PHP working, you need to install it manually.

mkdir mongo
cd mongo
wget https://github.com/mongodb/mongo-php-driver/zipball/master
unzip master
cd mongodb-mongo-php-driver-df8b217
phpize
./configure
make install

Add the extension to your php.ini file.

extension=mongo.so

Restart Apache, and Mongo should appear inf your phpinfo() output.

Posted on in Databases, Programming

Primary key ranges in Propel ORM

If you’re using Propel ORM, you may want to select a range of primary keys. According to the documentation, you should be able to do this using code similar to the following.

BookQuery::create()->filterById(array('min' => 1, 'max' => 100));

However, what you find you get is that will return books with the ID of 1 and 100, but nothing else.

That is because Propel does not support ranges on IDs. This has been noted on the Propel GitHub issue tracker and will be resolved at some point in the future, but until then you have two possibilities.

Firstly, if you’re only looking to specify one value in the range, you can pass a criteria constant to filter by that.

BookQuery::create()->filterById(30, \Criteria::GREATER_THAN)->find();

You can also use LESS_THAN in the same way. Or, if you need a range with both ends specified, you can resort to the where() method.

BookQuery::create()->where('id BETWEEN 1 AND 100')->find();

Though that method requires you to use the database column names, rather than the PHP names used in Propel (yours may be the same, but I often rename mine for legacy reasons).

Posted on in Databases

Table is full MySQL error

If you’re using MySQL’s MEMORY table storage engine for anything intensive, you may run into the following error.

Table is full

This means what it says – memory tables have a fixed size they are allowed to me, 16mb by default, and once they reach this size, MySQL will prevent you from inserting any more data, to prevent the table from using too much memory.

If you run into this error, you can either increase the size in MySQL’s configuration, or you can switch to a disk based table engine such as Archive or InnoDB.

Posted on in Databases

How to truncate an ARCHIVE table

Archive is a cool MySQL storage engine designed for fast inserts. In fact, it’s so optimised for this that it actually only supports INSERT and SELECT – you can’t run UPDATE or DELETE commands against it at all.

This is a problem when developing though, as you’ll often want to empty out the table and start again. But because it doesn’t support delete operations, it won’t actually let you truncate the table! So to answer the question posed in the article title – you can’t.

Therefore, you have two options.

Firstly, you can drop the table and re-create it. This is the recommended way from MySQL, so make sure you have a copy of the table creation command handy.

The second option, the lazy hacky way if you will, is to change the table storage engine over to InnoDB or MyISAM, truncate the table, and then turn it back to ARCHIVE ;).