Monthly Archives: March 2017

Updated the default character set on mysql server to utf8mb4 💩

Blasdale Home Posted on by

I had to update the default character set  to utf8mb4 on my mysql server.  I was getting quite a few submissions on my tomcat hosted systems with emojies like the ? symbol being inserted.  These always failed with a database error.  Reading up about this it appears the utf8 support of mysql was for up to a 3 byte utf character.  Full support requires 4 bytes, and this was a late addition to mysql.

I dutifully converted the database, and tables to utf8mb4, running into issues with the referential integrity I use.  Database, tables and columns all converted, set the default client and mysql to utf8mb4 and problem solved.  I could now add the ? into the application.

Along came the boss, who now complained about all the capital As, with a hat on top, in this the blasdale.com blog.  Arrgh.  Yes in the blasdale database there were some latin1 tables from a really old install of wordpress.  So a conversion of this database and tables to utf8mb4 but still the capital A with a hat appeared.  More googling, yes I had UTF data stored into a latin1 column, so had to run some sql to convert the data. 

update wp_posts SET post_content=convert(cast(convert(post_content using  latin1) as binary) using utf8mb4);

In total I ran the following SQL statements on the server:

ALTER DATABASE blasdale_blog CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE wp_blc_filters CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_blc_links CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_blc_synch CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_commentmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE wp_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_email_list CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_email_list_config CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_email_list_future CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_hl_twitter_replies CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_hl_twitter_tweets CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_hl_twitter_users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_links CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_ngg_album CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE wp_ngg_gallery CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_ngg_pictures CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE wp_options CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_postmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE wp_subscribe2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_term_relationships CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_term_taxonomy CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_termmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_terms CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_usermeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfBadLeechers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfBlockedIPLog CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfBlocks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfBlocksAdv CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfConfig CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfCrawlers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfFileMods CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfHits CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfHoover CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfIssues CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfKnownFileList CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfLeechers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfLockedOut CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfLocs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfLogins CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfNet404s CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfNotifications CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfReverseCache CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfSNIPCache CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfScanners CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfStatus CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfThrottleLog CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE wp_wfVulnScanners CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SELECT column_name,character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "blasdale_blog"
 AND table_name = "wp_hl_twitter_tweets";
 
create table wp_posts_bkp LIKE wp_posts; 
insert wp_posts_bkp select * from wp_posts;
 
update wp_posts SET post_content=convert(cast(convert(post_content using latin1) as binary) using utf8mb4);
update wp_posts SET post_title=convert(cast(convert(post_title using latin1) as binary) using utf8mb4);
update wp_posts SET post_content_filtered=convert(cast(convert(post_content_filtered using latin1) as binary) using utf8mb4);

I also updated the WordPress configuration to include utf8mb4 instead of utf8:

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8mb4');

 

The problem was not caused by the initial conversion, but by the statements I had placed in the my,cnf configuration file which caused WordPress to assume all connections are utf8mb4.

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci


[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

I think it is all working now, tomcat is happy and so is my WordPress install.   I do though have many tables in other databases which are only utf8.  I expect they will function unless someone tries to insert the 💩 into a page or post.  I expect I will get around to converting those databases,  should be straightforward…..

 

Fforest Fields Campsite, Hundred House

Blasdale Home Posted on by

Our second campervan trip was to Fforest Fields in Hundred House.  This was the campsite where I had stayed when I learnt to paraglide at least seventeen years ago.  The same family own the site, though they no longer teach paragliding.  The site has improved immensely since I was last there.  Large ponds have been dug which you can swim in.  The shower block is magnificent with under floor heating all run from a log burner and a huge store of hot water.   We arrived and were met by owner George.  We set up camp for the night.

The following day we went for a brief walk on the hills above the campsite.  The Welsh weather brought a drizzle, but not enough to destroy the walk.  In the afternoon we drove over to Llandrindod Wells to visit Tesco to buy some food.  The route we took was over the hills along some very narrow roads and cattle grates.  That night it rained heavily.

Sunday the weather had eased off, so we walked over to the pub at Hundred House.  This was a lovely walk along a stream, through woods.  The walk back was by a different route which took us over higher ground.  The pub was good, lovely big roast lunch with proper meat that had been carved off a joint, and good value.

Monday came, pouring with rain, forecast more rain, so we called it a day and drove home. 

VW T5 Campervan Conversion by CJ Van Designs

Blasdale Home Posted on by

Our VW T5 campervan conversion  was completed earlier this year.  Rosemary and I decided we wanted to travel more, and not be tied to hotels or tents.  We decided on a small campervan which is as easy to drive as a car, and can be parked in most normal parking spaces.  We went with the VW  campervan dream, well OK a modern VW T5 Campervan  dream.  We bought a two year old VW T5 kombi van coloured blackberry.  It is the normal wheel base, highline model with tailgate, 2.0 litre 140PS engine.  Blackberry is a metallic paint which the DVLA calls purple.  In some lights it looks almost black, whereas in the sunshine it looks a sparkling dark purple colour, to sometimes a brown.  It’s a fabulous chameleon taking on the colours of its surroundings. 

Once we had the van, we needed it converted. Problem here was there was an unbelievable waiting list amongst all the workshops we contacted.  We chose CJ Van Designs in Wiltshire.  Colin the owner was thorough and took us through the many options we could have.  His designs for cupboards seemed to be what we wanted.  We booked the van in for conversion.  In between booking the van in for conversion, and conversion day, we took the van (& tent) for a long drive through France in September. Eventually the day for conversion arrived in January, we said goodbye to the T5, and a few weeks later we had our campervan.  It was glisteningly clean, complete with a bottle of Champagne in the fridge.

The work we had done on the van included:

  • Reimo popup roof, colour coded to the van, which contains a bed
  • Riba rear seat which converts to a bed
  • Cupboards, under sink, under bed, over sink, over the bed & inside the tailgate
  • 12 volt power and lighting
  • 240 volt hookup
  • An extra power socket near the side door, for laptop & outdoor cooking with an electric frying pan
  • Refrigerator (240/12 volt)
  • Twin ring gas hob
  • Sink
  • Diesel space heater
  • Lighting, including lights for the popup roof
  • Insulation in van walls
  • Flooring & carpeting on the sides
  • Rail for connecting an awning
  • Rotating passenger seat
  • Safe 

We did not have to install extra windows as the Kombi van has windows on both sides to part way down the van.  The extra rear seats and original flooring in the van were sold off during the conversion.  New bench-seat/bed from Riba was then installed.

Much of this work is mandatory for the vehicle to be licensed by DVLA as a campervan aka “Motor Caravan”.  Without this complete work the van would not be able to be taken to music festivals like Reading festival where their requirements for a campervan mirror the DVLA requirements.

We took pictures  for the re-registration of the Volkswagen T5 Combi van to a Motor Caravan.  The photographs had to show the registration plate of the van, the fixtures, gas, water, sink, cupboards, table and bed.  These were sent to DVLA and a couple of weeks later we had the new registration certificate.

There are some of the extra items we want for the campervan: a bike rack and an awning.  Choosing the awning is difficult. Do we want a side-less awning to keep the sun off, where we can eat and cook in warm countries, or do we want a drive-away awning with sides where we can leave the tables and chairs under cover, and change our shoes and coats in cold and wet Britain?