Sunday, April 15, 2018

TYPO3 MySQL database import fails with "Index column size too large. The maximum column size is 767 bytes"

I recently migrated a TYPO3 7.6 Website to TYPO3 8.7 and while importing the migrated TYPO3 database on the production server, the import failed with the following MySQL error:

ERROR 1709 (HY000) at line 2060: Index column size too large. The maximum column size is 767 bytes.

The error occurred for the import of the TYPO3 table sys_refindex. After some research and local debugging I found out, that I locally was using MySQL 5.7 and the production server was using MySQL 5.6, but settings in regard to innodb_large_prefix and innodb_file_format were equal.

In order to import the dump from my MySQL 5.7 server to the production MySQL 5.6 Server, I executed the following SQL query before creating the MySQL dump:  


After setting the ROW_FORMAT to DYNAMIC, the database dump from MySQL 5.7 could finally be imported without errors on the MySQL 5.6 production server.

Wednesday, April 11, 2018

TYPO3 extension sf_event_mgt version 3.0 released

Today I finally released the new version 3.0 of my TYPO3 extension sf_event_mgt - Event management and registration. The new version comes with tons of new features, bugfixes and improvements and also contains 2 breaking changes, so make sure to read the release notes.

Thanks to everyone, who contributed to the extension over the last few months. Also a special thanks to Alex Kellner for his extension powermail, from which I adapted ideas and some code for the registration fields feature.

New features 

Below follows some of the new features of sf_event_mgt 3.0.

Registration fields

Im order to make the extension more easy to use for non-programmers, I added the possibility for editors add additional registration fields to the default registration form on event basis.

Registration fields
Creating of registration fields works basically as in powermail. The user can add registrations fields in a new tab as shown in the screenshot above and choose one of the following field types: input, textarea, radio and checkbox.  When a participant registers to an event, all filled out registration fields are saved to the registration record in the TYPO3 backend.

allowLanguageSynchronization for TYPO3 8.7

The TCA settings allowLanguageSynchronization has been introduced in TYPO3 8.6 and is the successor for "l10n_mode=mergeIfNotBlank". Note, that sf_event_mgt did not use "l10n_mode=mergeIfNotBlank", so the language synchronization feature is only available in TYPO3 8.7


Finally, I also added signals in various actions, so it is now easily possible to modify/extend variables given to the desired views.

Monday, February 5, 2018

TYPO3 - How to render a Fluid standalone view multiple times in different languages

Back in 2015, I wrote 2 blogposts (first and second) about rendering a localized Fluid standalone view in a scheduler task (commandController). The main problem was to render a Fluid standalone view multiple times within the same request but with different languages. Back then, my solution was to create an own ViewHelper and a modified version of the TYPO3 LocalizationUtility which were responsible for handling the localization changes during the rendering request.

Meanwhile, I got feedback from readers of my blog pointing me to a more simple solution for the problem.

The main problem with changing the TYPO3 backend language during one request is the language cache, which is only initialized once for the current language. So when you switch the backend language multiple times, the cached language files for the previous language will still be used.

The solution is to unset the language cache for the extension you are rendering your Fluid standalone view from. In order to do so, you have to extend the TYPO3 LocalizationUtility with a new function as shown below.

class LocalizationUtility extends \TYPO3\CMS\Extbase\Utility\LocalizationUtility
     * Resets the language cache for the given extension key
     * @param string $extensionName
    public static function resetLocalizationCache($extensionName)

The example code below shows, how to use the resetLocalizationCache method before rendering a Fluid standalone view in a given language.

 * Renders a Fluid StandaloneView respecting the given language
 * @param string $language The language (e.g. de, dk or se)
 * @return string
public function renderStandaloneView($language = '')
    // Set the extensionKey
    $extensionKey = GeneralUtility::underscoredToUpperCamelCase('standaloneview');

    if ($language !== '') {
        // Temporary set Language of current BE user to given language
        $GLOBALS['BE_USER']->uc['lang'] = $language;

    /** @var \TYPO3\CMS\Fluid\View\StandaloneView $view */
    $view = $this->objectManager->get(StandaloneView::class);
    $template = GeneralUtility::getFileAbsFileName(

    // Set Extension name, so localizations for extension get respected

    return $view->render();

So with a small extension of the LocalizationUtility it is now easily possible to render a Fluid standalone view in a language of choice and it is also possible to switch the language within the same request (e.g. sending out localized e-mails to multiple recipients).

I updated the Demo-Extension, which contains a backend module and a command controller for demonstration purposes.

I would like to thank Johannes Rebhan for giving me the hint about the localization cache and also Ulrich Fischer for showing me a different approach, which requires more code, but lead to the same result.

Thursday, January 25, 2018

SSH reports "Too many Authentication Failures" on first connect

Today I wanted to connect to a new clients SSH server and received a "Too many Authentication Failures" message just on the first connect to the host. After a short break and some Google research, I found the very simple reason for the message.

Since I have several SSH keys in my .ssh/ directory, SSH tries to use each of it to connect to the SSH server. So when the SSH server has a very low "MaxAuthTries" setting configured, then the SSH connection may fail before password authentication is offered.

In order to connect a SSH servers with a low "MaxAuthTries" setting, you can use the following command:

ssh -o PubkeyAuthentication=no [email protected]

After using the "PubkeyAuthentication=no" option, I could login to the host and add a SSH public key to the .ssh/authorized_keys file.

Wednesday, June 28, 2017

TYPO3 8.7 LTS - How to enable the image cropping tool in your own extension

People using TYPO3 7.6 or 8.7 for sure know the cool Image Cropping tool that was introduced with TYPO3 7. As an extension developer, you can easily switch on the Image Cropping tool for your own extension by enabling adding the imageoverlayPalette to the foreign_types config array for the FILETYPE_IMAGE in the TCA like shown in this blogpost by Marcus Schwemer. In TYPO3 8.7 the Image Cropping tool does not show as expected, due to structural TCA changes in the TYPO3 8.

For TYPO3 8.7, the following (minimal) TCA configuration will enable the Image Cropping tool for the field "image" in an own extension:

'image' => [
    'exclude' => 1,
    'label' => 'LLL:EXT:custom_extension/Resources/Private/Language/locallang_db.xlf:tx_customextension_domain_model_tablename.custom_field',
    'config' => \TYPO3\CMS\Core\Utility\ExtensionManagementUtility::getFileFieldTCAConfig('image', [
        'foreign_match_fields' => [
            'fieldname' => 'image',
            'tablenames' => 'tx_customextension_domain_model_tablename',
            'table_local' => 'sys_file',
        'overrideChildTca' => [
            'types' => [
                \TYPO3\CMS\Core\Resource\File::FILETYPE_IMAGE => [
                    'showitem' => '
        'minitems' => 0,
        'maxitems' => 999,
    ], $GLOBALS['TYPO3_CONF_VARS']['GFX']['imagefile_ext']),

Note, that the configuration array only shows the configuration for a single field and that the array must be located in the "columns"-array of your domain model TCA. For TYPO3 8.7, the overrideChildTca config is the important part, which enables the Image Cropping tool.

If your extension needs to support TYPO3 7.6 and 8.7, your TCA should include both the overrideChildTca config as described in this blogpost and the foreign_types config as described in Marcus's blogpost.

Monday, June 26, 2017

TYPO3 Extbase - Manual validation of a domain model

When you create an Extbase extension and allow a website user to submit form data that will be saved to the TYPO3 database, you usually work with validators in your domain model to ensure, that the given data will match certain criteria (e.g. properties have the right data type or expected data format). Extbase offers an easy way to add validation rules to domain model properties, by just adding the @validate annotation followed by one or multiple validators as shown in the following example:

 * Description
 * @var string
 * @validate NotEmpty, StringLength(minimum=10, maximum=50)
protected $description = '';

Extbase will take care of the domain model validation, when the given form data is converted to an object of the type TYPO3\CMS\Extbase\Mvc\Controller\Argument.

Manual validation

If you manually create a domain model object and want to make sure, that your Extbase validation rules are meet, you can trigger the domain model validation manually as shown below:

/** @var Data $dataModel */
$dataModel = $this->objectManager->get(Data::class);
$dataModel->setDescription('too short');

/* @var $validator \TYPO3\CMS\Extbase\Validation\Validator\ConjunctionValidator */
$validator = $this->objectManager->get(ValidatorResolver::class)->getBaseValidatorConjunction(Data::class);
$validationResults = $validator->validate($dataModel);

if ($validationResults->hasErrors()) {
    // @todo cycle through errors in $validationResults->getFlattenedErrors()

By creating the domain model object manually, you must take into account, that this will create a new object, where all properties are initialized with the default values defined in the domain model.

Practical use case

As an example for a practical use case for manual domain model validation, lets assume you have a REST webservice and need to import some data to TYPO3. You typically fetch the data from the webservice and add the data to the database. Instead of checking the content of the incoming record/field manually using if-statements, you can use the Extbase property mapper in combination with manual domain model validation.

Below follows some example code for the described use case:

/** @var PropertyMapper $propertyMapper */
$propertyMapper = $this->objectManager->get(PropertyMapper::class);

// Get some data - could for example be some data from a REST webservice
$data = $this->getApiData();

foreach ($data as $importRecord) {
    $dataModel = $propertyMapper->convert($importRecord, Data::class);
    // Note: The propertyMapper will set domain model default values for all all non-mappable values

    /* @var $validator \TYPO3\CMS\Extbase\Validation\Validator\ConjunctionValidator */
    $validator = $this->objectManager->get(ValidatorResolver::class)->getBaseValidatorConjunction(Data::class);
    $validationResults = $validator->validate($dataModel);

    if ($validationResults->hasErrors()) {
        // Record could not be imported, collect error messages for each field in $errorMessages array

        /** @var Error $error */
        foreach ($validationResults->getFlattenedErrors() as $field => $errors) {
            $errorMessages = [];
            foreach ($errors as $error) {
                $errorMessages[] = $error->getMessage();
    } else {
        // Import record to repository...

By using the Extbase property mapper to create domain model objects, you do not need to check and assign each field individually. You just have to make sure, that the array given passed to the "convert" function use the same field naming as the domain model do like shown below.

'title' => 'a title',
'email' => '[email protected]',
'description' => 'a description',
'year' => 2017,
'amount' => 19.99,
'paid' => true

Note, that the resulting object from the property mapper will contain the default values for each property, that can't be mapped properly.

In the code example above, the resulting domain model object will manually be validated and if no validation errors occur, the object can be added to the repository.

I created a small demo extension with a command controller, which contains 2 example commands that show validation results for some dummy data.

Monday, May 15, 2017

How to use MySQL FIELD-function in TYPO3 8.7 with Doctrine DBAL

When you want to query an Extbase repository for a list of UIDs or PIDs, you usually add a query constraint like query->in('pid', $pidList) to the query, where $pidList is an array of integers. But what, if you want to control the sorting of the returned records? Lets assume, you want to select the following list of UIDs [5, 3, 4, 1] from your repository and the sorting or the UIDs must remain.
Extbase only has the possibility to sort the query result by a given column either ascending or descending, so there is no possibility to control so returned sorting as intended.

In order to resolve the problem, I found this very helpful article from Manfred Rutschmann, where he describes exactly the same situation and offers a solution for the problem, which works great in TYPO3 7.6. Sadly the solution does not work in TYPO3 8.7 LTS and fails with an exception, that e.g. column tx_myext_domain_model_mymodel.uid=5 does not exist.

Since I'm using MySQL as a database engine, I tried to find a solution, where I could use the MySQL FIELD-function to apply the special sorting to the ORDER BY clause.

Fortunately Doctrine DBAL was integrated in TYPO3 8.7 LTS, which offers an easy and readable way to construct my query. I added the following function to my Extbase repository.

In line 31 I add my own ORDER BY FIELD clause, where the special sorting is applied

Since the queryBuilder just returns an array of database records (where each records is just an array of returned fields/values), I use the TYPO3 DataMapper in line 35 to map the returned rows as objects, so the returned QueryResult object will contain objects of the type Mymodel

The example shown has one little downside, since it only works with a MySQL Database.

If you want to get more details about the Doctrine DBAL integration in TYPO3, make sure to read the documentation.