Fixing double-encoded UTF-8 data in MySQL

I recently came across an old website of ours that I wanted to migrate to a new server. When I finished migration, I found the special characters typical to european languages broken. It took me a while to find the problem. The data was wrong in the Database but on the old system it was displayed correctly, the new didn’t. I finally figured out that my database suffered from double encoded UTF-8 characters.

It seams, that double-encoded UTF-8 texts are a fairly common problem when dealing with MySQL. This may be due to the fact that the default character set of the connection to the server is Latin-1, but that is not relevant once the data is already corrupt.

Here is how to fix it – export the database using latin1 charset an re-import with utf-8:

mysqldump -h DB_HOST -u DB_USER -pDB_PASSWORD –opt –quote-names –skip-set-charset –default-character-set=latin1 DB_NAME > dump.sql

mysql -h DB_HOST -u DB_USER -p DB_PASSWORD –default-character-set=utf8 DB_NAME < dump.sql

Of course, you should first replace DB_HOST, DB_USER, DB_PASSWORD and DB_NAME with values, corresponding to your personal setup.

source: Fixing double-encoded UTF-8 data in MySQL

How to create m:n relations with Extbase and Fluid

Update: don’t bother – I’ve found a much simpler solution and will update this tutorial soon! In the meantime, check out the extension on github where I’ve implemented the easy solution.

I was looking for a tutorial that explains how to create true m:n relational objects with Typo3 and Extbase and Fluid. Unfortunately I could not find something that explained how I could get this to work with the Extension Builder. I finally figured out a working solution. This post will be a short tutorial on how to accomplish „has many and is child of many“ relations with the Extbase Extension Builder.

  • First you need Extbase and Fluid installed – it comes bundled with Typo3 – I used Typo3 4.5.3 with Extbase and Fluid 1.3
  • You also need to download and install the Extension Builder –  unfortunately it’s not in the TER at the moment

This tutorial will show you, how to create an extension with an object „category“ which can have subcategories of the same type. You can create Categories and Subcategories. You can also assign multiple parent categories to subcategories and so forth. The relation will be visible on both ends.

First, you need to create a new extension. Switch to the Extension Builder and enter some details for your extension. In my case I create a new extension nbomn :

Hit „save“ after you have created the basics.

Now we are going to create a new Model „Category“. It is of the type „Entity“ which is an object that has a unique identity. Much like a car – compared to a value object which would be more like a color that only has a meaning when applied to a subject (car) but can’t stand for it’s own. It’s also an aggregate root – this means the extension builder will generate a repository and default controller actions for this model so we can easily fetch and save objects – this has nothing to do with the m:n relation.

Now that we have created the Model, let us add some properties. First and most obvious, we need a name for the category – this is of the type „String“, and it’s also required. Second, we need a new field that acts as the counterpart for the m:n relations. The Extension Builder will not create this automatically. It is of the type „Integer“ and not required (since not all categories have parents).

Last but very much not least, we create a relation of the object to itself. The name of the relations is „subcategory“ and it is of the type „m:n“ – since this is what it’s all about. Also, we don’t want it to be a „inline“ relation – we could not select existing categories that way. Then, we connect the „dot“ from the relation to the dot of the domain by dragging a line from the dot at the bottom to the dot at the top left.

You will end up with this (Properties folded):

Now you can save your extension. You have now a new folder named after your extension key in your extension folder typo3conf/ext/yourextkey. We now have to update two things:

  1. The MySQL database scheme for our mm table
  2. The TCA configuration for the Typo3 backend forms

Open the file „ext_tables.sql“ and scroll down to your mm table. We will need to add three fields and change the KEY fields:

  1. New field uid
  2. New field pid
  3. New field tablenames

We will also change the KEY definition and define the UID as the PRIMARY KEY and the pid as the parent KEY.

## Original fields:
# KEY uid_local (uid_local),
# KEY uid_foreign (uid_foreign)

## Changed fields for m:n
KEY parent (pid),

## New Fields for m:n
uid int(10) NOT NULL auto_increment,
pid int(11) DEFAULT '0' NOT NULL,
tablenames varchar(255) DEFAULT '' NOT NULL

Now we change the subcategory TCA definition to allow for true m:n relations. Open the file „Category.php“ at „Configuration/TCA“ and find „MM“ inside the „subcategory“ configuration. We add two new definitions:

  1. „MM_match_fields“ that matches the „tablenames“ field from the mm table. Don’t forget to adjust it to your correct field-names!
  2. „MM_opposite_field“ that links to our „category_mm“
'MM_match_fields' => array(
      'tablenames' => 'tx_nbomn_domain_model_category'   
'MM_opposite_field' => 'category_mm',

You should also add a line inside the „add wizard“ configuration to prevent adding new subcategories before the parent has been saved:

'notNewRecords' => 1,

That’s it for the subcategory configuration. It can now store relations to other categories. Now we need to setup the parent categories. We replace the whole „config“ array of the „category_mm“ field with the one of „subcategory“. Then we remove the „MM_opposite_field“ entry:

That’s all. Now we have working Sub- and Parentcategories. You can now go to the Extension Manager and install your Extension. Then create a Sysfolder somewhere in your Pagetree and insert a new category.

After you save the category, you can create subcategories and also add new parent categories:

I hope, this helped you to create your own m:n relations with the Extbase Extension Builder. If you have any questions or suggestions, feel free to leave a comment. In case you like to take a look at my test extension – download nbomn – it’s also on GitHub.

Ps. On my way to this solution this thread was useful.

New Typo3 Podcast Extension Release

The last day, I checked my veeeery old e-mail address after years and found some feature-requests and bug-reports for my podcast extension that provides typo3 the ability to create audio-podcasts. I haven’t touched it since 2008 so I was pleased to find out, that it still worked with the current version of typo3. I was also amazed, that after all this years, nbo_podcast seems to bee the only (widely used) extension that provides this functionality.

A common request was to make it possible for podcast producers to link files hosted on a different server than typo3. With the newest release version 2.0.3 uploaded to the Typo3 TER today, I added this possibility together with some bug-fixes and small changes. The Manual has undergone a slight update with new Screenshots. Go ahead and download the extension and RTFM (Read The Famous Manual).