Posts Tagged ‘mysql’

MacPorts mysql5-server not working on boot

Friday, February 22nd, 2013 | Life, Tech

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.

Data missing from MySQL replication due to column mismatch

Saturday, February 16th, 2013 | Life, Tech

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.


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.


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

Updating phpMyAdmin on MAMP Pro

Sunday, December 30th, 2012 | Life, Tech

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

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.

ActiveRecord::ConnectionNotEstablished in Rails

Friday, August 24th, 2012 | Programming, Tech

I ran into this error while trying to get Ruby on Rails 3.1.1 which I installed from the Rails Installer to talk to MySQL. My stack is built from WAMP, so it might not have been as easily as it would be on a standlone MySQL installation.

Here is are the steps I took to fix it.

Edit Gemfile in project and add the following line.

gem 'mysql2', '< 0.3'

Rebuild the MySQL adapter with your version of MySQL.

gem uninstall mysql2
gem install mysql2 -- --with-mysql-config="C:/wamp/bin/mysql/mysql5.5.16/bin/"

Download the MySQL connector from the MySQL website.

Once you have the zip file, uncompress it and copy lib/libmysql.dll to the Ruby bin directory. Finally, go back to your Rails project and run the following command.

bundle install

I was finally then able to run a command such as the following.

rails generate model Something name:string

Without it throwing any errors up.

Table is full MySQL error

Saturday, April 21st, 2012 | Life, Tech

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.

How to truncate an ARCHIVE table

Friday, March 9th, 2012 | Life, Tech

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 ;).

Profiling SQL queries with EXPLAIN

Wednesday, January 18th, 2012 | Life, Tech

If you have a complex SQL query, you might find that performance isn’t exactly ideal. Worse still, you don’t actually know which part of the query it is that is actually taking so long.

Luckily, MySQL comes to the rescue with the ability to explain.

All you have to do is start your query with the keyword EXPLAIN and MySQL will, rather than returning you a recordset of results, will instead provide a break down of everything it has done, including how it made the table joins and what order it did everything in.

EXPLAIN SELECT a INNER JOIN b ON a.col1 = b.col2 WHERE a.col1 > 1 AND b.col2 > 2;

Speeding up inserts with INSERT DELAYED

Thursday, January 12th, 2012 | Life, Tech

If your insert statements are not time critical, you can use insert delayed in your SQL to speed things up. The syntax is as follows.

INSERT DELAYED table (col1, col2) VALUES ('a', 'b');

Insert delayed can be used with MyISAM and Memory, but cannot be used with InnoDB.

The advantage of using insert delayed is that the MySQL server returns a success message straight away so the script can keep going, without it actually having to do the insert. This allows the MySQL server to carry it out when it isn’t busy, and do several at the same time.

It’s appropriate for tables such as logs tables where it doesn’t matter too much if they don’t go in straight away.

Updating views in MySQL

Thursday, January 5th, 2012 | Life, Tech

Yes, you can update views in MySQL! A lot of people seem to be under the impression that you are unable to, but this simply isn’t the case. There are a lot of restrictive rules, which can be found in the MySQL manual, but as a rule of thumb, you can update them, but only one table.

For example, lets say you have a view which links table A and table B.

You can run an update on this view, but only if you are only updating the columns from one table. So you could run a query that updates a number of columns from table A, or you could run a query which updates columns from table B – but you can’t run a query which updated columns in both table A and table B.

If you need to accomplish this, you need to use a join instead.

Connecting to MySQL in PHP

Sunday, September 16th, 2007 | Programming, Tech

So you have your shiny new MySQL database your web host has given you and you are already to begin your PHP scripting. If not then you can get an account with all this from Or you could when I wrote this anyway.

For those of you used to connecting to Microsoft Access Databases, like I am, the difference here is that rather than the database being a normal file like a word document or a music file which can be easily opened, etc, MySQL databases are stored on the server. So rather than connecting to a file you connect to the server.

First I am going to jump straight in to the code and then explain it after.

$db = mysql_connect("localhost", "username", "password");

That is a basic connection. Ignoring the php open and close tags, the second link in the code makes the connection to the database of your choose. In this case it connects to a database simply called “database” so change this to your database name.

The top line tells the script where the database is. In most cases you can leave this as “localhost” as most hosts keep this as standard. If you get told your MySQL host is different though replace localhost with the new address your web host gives you.

Once you have established a connection you can then enter SQL underneath:

$db = mysql_connect("localhost", "username", "password");

$sql="SELECT * FROM members WHERE posts > 10";

In five lines you have connected to a database and even prepaed some SQL to select a record set from it. That is pretty simple I recon.