Month: July 2014

phpBB3 – Can’t find FULLTEXT index matching the column list [1191] MySQL 5.6

james Leave a comment

I just upgraded MySQL from 5.5 to 5.6 and changed all the tables in the PHPBB3 database into InnoDB tables. I did this because InnoDB is faster if you have lots of concurrent read / writes and MySQL 5.6 finally support FULLTEXT searching using InnoDB. However, I made a mistake and did the conversion to InnoDB before upgrading to MySQL 5.6 and lost the fulltext indexes from the tables.

So, I got this error:

Can't find FULLTEXT index matching the column list [1191]

I found the query that was throwing the error:

mysql> SELECT SQL_CALC_FOUND_ROWS p.post_id FROM phpbb3_posts p WHERE MATCH (p.post_subject, p.post_text) AGAINST ('+search ' IN BOOLEAN MODE) ORDER BY p.post_time DESC LIMIT 250;

I ran it and got this error:

ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

To solve the probem is to add the index back in. Note is requires an index across two columns. Run this MySQL command on your database.

create fulltext index post_subject_text on phpbb3_posts (post_subject, post_text);

Hope this helps someone.

How to install MySQL 5.6 on Ubuntu 14.04

james 6 Comments

Ubuntu 14.04 comes with both MySQL 5.5 and MySQL 5.6 and by default uses MySQL 5.5. However, you can upgrade to MySQL 5.6 by doing the following steps:

Step 1 – Take a backup!

mysqldump -u root --all-databases > /home/me/add_databases.sql

Step 2 – Remove the old MySQL
I recommend using ‘apt-get purge’ rather than ‘apt-get remove’ to uninstall the old MySQL server. Don’t worry, this doesn’t mean all your data in /var/lib/mysql will be deleted. The purge command just removes all the configuration files in /etc/mysql/ . This is important because some old MySQL configurations aren’t supported in 5.6.

If you have an unsupport configuration option (for example: table_cache was renamed table_open_cache) MySQl doesn’t silently ignore these settings… it simply doesn’t start. Oh and it doesn’t show any warnings. So you really should remove any list in /etc/mysqal/conf.d . You can read the list of settings that might have changed.

So…

apt-get purge mysql-server-5.5 mysql-client-5.5
apt-get autoremove

Step 3 – Install the new MySQL 5.6 

sudo apt-get install mysql-server-5.6 mysql-client-5.6

And you are done. Hurray.

p.s. For interest, you can see which repository these packages are in from using the ‘apt-cache policy’ command:

sudo apt-cache policy mysql-server

mysql-server:
  Installed: 5.5.38-0ubuntu0.14.04.1
  Candidate: 5.5.38-0ubuntu0.14.04.1
  Version table:
 *** 5.5.38-0ubuntu0.14.04.1 0
        500 http://archive.ubuntu.com/ubuntu/ trusty-updates/main amd64 Packages
        500 http://security.ubuntu.com/ubuntu/ trusty-security/main amd64 Packages
        100 /var/lib/dpkg/status
     5.5.35+dfsg-1ubuntu1 0
        500 http://archive.ubuntu.com/ubuntu/ trusty/main amd64 Packages

sudo apt-cache policy mysql-server-5.6

mysql-server-5.6:
  Installed: (none)
  Candidate: 5.6.17-0ubuntu0.14.04.1
  Version table:
     5.6.17-0ubuntu0.14.04.1 0
        500 http://archive.ubuntu.com/ubuntu/ trusty-updates/universe amd64 Packages
        500 http://security.ubuntu.com/ubuntu/ trusty-security/universe amd64 Packages
     5.6.16-1~exp1 0
        500 http://archive.ubuntu.com/ubuntu/ trusty/universe amd64 Packages

Seat Ibiza GN10KJZ VSSZZZ6JZAR139908 – Crash or Salvage?

james Leave a comment

I looked at buying a black Seat Ibiza with number plate GN10KJZ. I did a HPI check and it came up clear. However, while looking through the car manual, I found a memo from ASM Autos which are salvage and breakers yard.

I then looked under at the engine compartment and found that substantial repair work had been carried out – including the removal of all the engine bolts, a new enginer, side wings and a respray. This information was not explained to me by the used car sales man and the cars history has been ‘forgotten’.

If you reading this page because you are checking up on this car – can I recommend that examine it very carefully (preferably with a mechanic present) – before committing to purchase this vehicle.

A window stuck in the top menu Ubuntu Unity 14.04

james Leave a comment

Some of my windows (for example libre office) have been getting stuck on the top left of the screen because Ubuntu Unity was appearing to eat the menu controls. I could not minimise, maximise or close the windows simply because the menu button weren’t visible.

The solution is to hold down the ‘alt’ key and drag the windows away from the top.

I didn’t know about alt-drag but is a really useful little tip.

Let me know if you find another solution.

How to install ssh-copy-id on OSX (the easy way!)

james Leave a comment

If you have migrated from Linux to OSX… you might well be missing ssh-copy-id. Frankly, the easier way to do this is to install the Brew repository / package manager for Mac and then install ssh-copy-id on your Mac in one command.

Install Brew (if you don’t already have this awesome tool!).

ruby -e "$(curl -fsSL https://raw.github.com/Homebrew/homebrew/go/install)"

Then simply..

brew install ssh-copy-id

Job done!