[TYPO3-dev] 6.2: Issue with backend module templates after converting sys_template to InnoDB

[g4-lisz at tonarchiv.ch]

On 05/01/2015 02:01 PM, Jigal van Hemert wrote:
> Hi,
>
> On 01/05/2015 02:30, g4-lisz at tonarchiv.ch wrote:
>> After a lot of testing I found out that, by the update to InnoDB,  the
>> "default" ordering of the table has changed.
>
> There is no such thing as "default" ordering in MySQL.
I know, that's why I quoted it. But the order it is returned is always
the same, until you change the engine or rebuild the table.

>
> If you don't specify and ORDER BY clause MySQL will result the items
> in "database order" which is basically "the order in which the engine
> returns them". This may depend on many things and may even change over
> time when the engine and query optimizer decide that the data is
> processed differently.
Again, i know (in theory). But each enginge has a certain default output
order becasue of the way it stores data. MyISAM returns the records the
way they were stored. InnoDB uses the primary key.

>
>> Maybe there's a bug in T3 and the 'sorting' field isn't used sometimes?
>
> Changing the engine will rebuild the table completely as data is
> stored in a different way. During the rebuilding the new engine may
> have ordered the data on the primary key. In the case of InnoDB this
> is very likely as the primary key is stored in the record itself (not
> a separate index).

That's what I epxlained here whit my examples. But this mail is not
about MySQL. It's about T3 reacting differently depending of the sorting
order (call it like this or not) of this specific table.
Which shouldn't be, becasuse, as you correctly stated, you can not rely
on the order of data comming from the DB.
>
>>
>> SELECT sorting, count(*) FROM `sys_template` group by sorting; returned
>> this:
>>
>> 128                  1
>> 256                35
>> 1024                1
>> 1792                1
>>   ..
>>    ..
>>     ...     all        1
>> 1000000000 21
>>
>> So some templates which are in the order 256 or 1000000000 should be in
>> a specific sequence to make things with backend template path work...
>> Why this? Again, no idea.
>
> The field 'sorting' is used per folder (page/sysfolder). The sorting
> value 1000000000 is used for records which are marked as deleted.
This is real info that could help debug the issue.

> There is an algorithm in the core to set a new sorting value which
> tries to limit the number of records which need to be updated when the
> order of object in the TYPO3 BE is changed.
>
>> At the moment there seems to be no solution make this table work with
>> InnoDB.
>
> There shouldn't be a problem. TYPO3 uses sorting per page/folder and
> should return the templates in the right order anyway.
There shouldn't. But apparently there is an issue with my table, which
only appears when data is retourned ordered by Uid. This is not an
question of DB engine tpye, but an issue of changing the sequence of
data when returned from DB.
I can simply proof this when I modify the MyISAM table like this:
  ALTER TABLE `sys_template` ORDER BY `uid` ;

From this moment backend templates won't work any more.

There must be some issue in T3 core. I find no other way to explain this
issue.

Cheers,
Till