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

[Jigal van Hemert]

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.

> This installation is quite old (6..8 years) and It looks like there were
> a lot of deleted rows over time. So MyISAM filled these gaps...
>
> On MyISAM, a select without order by clause returns the primary key UID
> in a more or less unordered sequence, like:
> UID
> 3
> 6
> 4
> 7
> 8
> 14
> 10
>
> After moving to InnoDB, the "clean" select returns the rows ordered by
> the primary key:
> UID
> 3
> 4
> 6
> 7
> 8
> 10
> 14
>
> Why the heck does this affect anything at all?

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.

> 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).

>
> 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.
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.

-- 
Jigal van Hemert
TYPO3 CMS Active Contributor

TYPO3 .... inspiring people to share!
Get involved: typo3.org