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!

How to move From Ubuntu to OSX

james 2 Comments

I’ve decided to stop using Ubuntu as my primary home computer and make the switch to OSX. I’ve purchased a new Macbook Pro and for the first time ever not immediately installed Ubuntu. I love Ubuntu (and this blog is 99% all about Ubuntu); so why have I decided to make this change?

Why go to OSX?

1 – My company has started developing software for Windows and Mac. I need to test the software. I can run Windows and Linux in a VM but can’t run OSX without a Mac.

2 – I don’t have some much time to geek and make Ubuntu work with my Android phone, my wifes Mac, a Nas, 4oD, Amazon streaming videos or any other random bits of hardware and software I need. Fundementally, OSX is better supported and just works most of the time.

That doesn’t diminish how brilliant Ubuntu is. It’s awesome and does 99% of what I want. I have written this post because perhaps there are other people changing from Ubuntu to OSX. So, I thought that I would note down the changes that I made to the default OSX – after all it isn’t perfect!

HyperDock – I miss the awesome windows snapping of Ubuntu Unity. Hyperdock brings this to Mac.

Pathfinder – The default file manager on Apple really sucks. No folders? WTF? Install Path Finder.

Firefox / Chrome – Let’s be honest – Safari sucks.

Libre Office – Apple provide Numbers and Words which are simply too basic for my taste.

Install a repository
Would you believe it but Apple don’t have a system repository like Linux. By default you simply can’t just ‘apt-get install randompackage’. This feels like lossing my right arm after living with the awesome repositories the Ubuntu / Debian provide. Luckily, the open source communitry have made a solution. It is called Brew.

You install Brew by running:

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

Then you can now run this one line…

brew install package

You can check for updates:

brew update

and upgrade like so:

brew upgrade

Install zsh
After using the awesome Ubuntu terminal, the default Apple terminal feels pants. You can fix this is just a couple of lines once you have Brew installed.

http://jakoblaegdsmand.com/blog/2013/04/how-to-get-an-awesome-looking-terminal-on-mac-os-x/

Remove the stupid [Process Completed] messages – When you exit the terminal you get the most annoying [Process completed]. This is super annoying.

Go to Terminal -> Preferences -> Settings -> Shell . Change “When the shell exits” to “Close if the shell exited cleanly”. This will close the window immediately after [Process completed] has shown.

Remove the stupid donk sounds –

 

I’m still learning about Mac’s and am updating this posts with all the changes that I made.  I will keep editing this post as I change other things.

Fogbugz stopped working after upgrade to Ubuntu 14.04

james Leave a comment

We have had Fogbugz installed on Ubuntu since Ubuntu 12.04. However, after upgrading to Ubuntu 14.04, we found that the software would not run. I could not find anything only about how to make Fogbugz work on Ubuntu 14.04. So…

We have moved to Jira and it sooooo much better than Fogbugz.

1) It is cheaper – just $10 for 10 users.
2) It is easier to install – it runs on Java and not on Mono
3) It is nicer to use – frankly Fogbugz is looking a little old fashioned!

So… Fogbugz is dead… long live Jira!