A fix has just been committed to the emoncms master git branch to allow the use of utf8 unicode characters in the multigraphs, such as;
However, until now, the emoncms MYSQL databases have been configured to
latin1 and not
utf8, which means that although emoncms can process the characters OK, MYSQL will not be able to store them.
The installation guide has now been updated to ensure that all NEW emncms databases are installed configured as
utf8 and in due course the emonpi imagefiles will also be updated.
If you need to use
utf8 characters in an existing installation, you have two options;
- Create a new MYSQL database which is configured
utf8 as per the installation guide - but you will lose all of your settings and possibly data, or
- Convert your existing database table to
utf8 (seems like a better plan!)
To convert to
utf8, log into MYSQL;
mysql -u USERNAME -p
First check how it is currently configured;
SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "emoncms";
It will probably say
latin1 so to change the default character decoding (for all new tables);
ALTER DATABASE emoncms CHARACTER SET utf8;
Then you need to convert the existing tables to
utf8 so load up the emoncms database;
List the emoncms tables;
Then convert each table in turn to
ALTER TABLE nameoftable CONVERT TO CHARACTER SET utf8; (where nameoftable is the name of the actual table, eg multigraph)
Once all of the tables have been converted, exit MYSQL by
couldn’t this be included in a future major upgrade as standard routine while upgrading ?
Not many people will venture the mysql path I’m afraid.
It will be shortly included for all new installation, but it would be difficult to write and run a script to update existing users MYSQL databases, and certainly not one I’d be confident to push out…
This only affects users who need Unicode characters in their multigraph, and all other users can carry on without doing any changes at all. This is optional, and in response to issues raised in git.
I’ve just checked the charset of the
feeds mysql table on the emonPi (Nov 2016) and
Do you think it would be worth writing a bash script which we could execute during emonPi update to change all emonpi mysql table charsets? Do you think there would be any downsides to this?
Maybe not worth the effort (and risk) to update all emonPi’s, since it’s a bit of a niche use case? Could cause more trouble…
Thanks for documenting on the forum. I wonder if we should add this info to the Emoncms docs?
My thoughts too.
I did consider a bash script too, but didn’t want the responsibility for screwing-up users databases! it’s a risk factor too far for me, especially as utf8 may not even be a consideration for most users.
I think that it should be easy to find in the forum, but whatever you think.
maybe write a bash but not with the basic install.
Like a little script you can download and run if you really need it, with the usual recommendations like make a full back up before etc …
And next to that the full explanation from first post here so he who want’s to do it table by table could.
This way you leave choice to each but at least a simple solution for the not so expert man (or women btw)
I have renamed the title to include ‘feed names’ and ‘UTF8’ to help with this