[TYPO3-english] Clean up deleted pages and refresh uid count

[Urs Bräm]

Hi Stefan

thanks for the thorough explanation!

Urs

Am 11.10.11 02:15, schrieb Stephan Schuler:
> Hi Urs.
>
> Since the UID properties of all tables tend be of type integer, there should be enough numers available above and no need to recycle number ranges.
>
> There is no automated way to reuse fragmented free space. This is caused by the TYPO3 habit of just using the database feature "auto_increment", or something comparable if it comes to other database backends then MySQL. When creating a new record, TYPO3 doesn't know which UID will be taken advance, and it does (almost [*1]) never try to influence this.
>
> So, the only way of reusing formerly used but now freed number ranges would be to make the database do this for you automatically. Then TYPO3 will follow -- or it rater will still not care about and let the database do.
> You could adjust your MySQL table by "ALTER TABLE tbl_name AUTO_INCREMENT=100" to make MySQL start with the UID 100 [*2], but this is only going to work if your freed UIDs are the topmost ones. Fragmentation with free space inbetween of used space will result in some kind duplicate identifier creation exceptions followed by a totally broken setup.
>
> Example:
> You have used the UIDs from 1 to 15. Then you delete 6, 7, 13, 14, 15. If you reduce your AUTO_INCREMENT=n from 16 to 12 [*2], the next number MySQL uses is 13 which is perfectly fine. If you configure your AUTO_INCREMENT from 16 to 5 [*2] instead, the next two records will seem to be fine (6 and 7), but the third one will try to take the 8 which is already in use. This results in a duplicate key error on the MySQL layer. Your TYPO3 isn't able to create any page recors from now on until you fix it by hand by readjusting the AUTO_INCREMENT=n value to a higher value.
>
> So you'd better not touch the AUTO_INCREMENT settings.
>
> Trying to reuse deleted number ranges for AUTO_INCREMENT identifers would have a very negative impact on performance. I can hardly imagine that any dbms offers such a thing as database feature, especially because int32 is huge when talking about filling it buttom up.
>
>
> [*1]
> A working sollution but definitely beyound the scope: Export your whole setup to a t3x file and import it into a completely new installation. This will make TYPO3 recreate all records "from scratch with content" which results in unfragmented tables. But because this doesn't work well for multiple referenced files (think about templa voila HTML template source files, one file for several template objects), this would introduces new disadvantages.
>
> [*2]
> I don't know if the AUTO_INCREMENT=n value points to the last used value or the one which will be the next. If 6 is next then could be that AUTO_INCREMENT=5 or could be that AUTO_INCREMENT=6. This detail doesn't realy matter for my explanation, does it?
>
>
> Regards,
>
> ________________________________________
> Von: typo3-english-bounces at lists.typo3.org [typo3-english-bounces at lists.typo3.org]" im Auftrag von"Urs Bräm [info at ursbraem.ch]
> Gesendet: Dienstag, 11. Oktober 2011 00:27
> Bis: typo3-english at lists.typo3.org
> Betreff: Re: [TYPO3-english] Clean up deleted pages and refresh uid count
>
> Am 10.10.11 10:34, schrieb Georg Ringer:
>
>> It is possible that the output won't be the same anymore if there is
>> something hardcoded, e.g. in TS or in a relation..
>
> Just to be sure: what I wanted to do was not to reorder existing UIDs,
> only the following:
>
> 1. Delete Page 9
> 2. Remove it from the table with Recycler
> 3. Then create a new page: Normally, UID will be 10 (although 9 would be
> free now). But I would like it to be 9. All the lower UIDs stay the same.
>
> Hardcoded references to the old 9 page in TS would be broken anyway, so
> I don't know where the difference would be?
>
> Sorry for nagging, I'm really interested in this
> Regards
> Urs
>
>
> --
> Urs Bräm
> macht Websites
> ---
> Certified Typo3 Integrator
> CH-3011 Bern
> www.ursbraem.ch
> _______________________________________________
> TYPO3-english mailing list
> TYPO3-english at lists.typo3.org
> http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english


-- 
Urs Bräm
macht Websites
---
Certified Typo3 Integrator
CH-3011 Bern
www.ursbraem.ch