[TYPO3-core] database indexes

[Steffen Kamper]

Hi,

today i had to dump and reimport all my databases for converting to "innodb_file_per_table", before one site was running out of tablespace.

As i did so, i was not able to reimport my TYPO3 DB without errors, i got these errors:
ERROR 1005 (HY000) at line 672: Can't create table 'cf_extbase_reflection_tags' (errno: -1)
ERROR 1005 (HY000) at line 698: Can't create table 'cf_extbase_typo3dbbackend_tablecolumns' (errno: -1)
ERROR 1005 (HY000) at line 724: Can't create table 'cf_extbase_typo3dbbackend_tablecolumns_tags' (errno: -1)
ERROR 1005 (HY000) at line 750: Can't create table 'cf_tt_news_cache' (errno: -1)
ERROR 1005 (HY000) at line 776: Can't create table 'cf_tt_news_cache_tags' (errno: -1)
ERROR 1005 (HY000) at line 802: Can't create table 'cf_workspaces_cache' (errno: -1)
ERROR 1005 (HY000) at line 828: Can't create table 'cf_workspaces_cache_tags' (errno: -1)
ERROR 1005 (HY000) at line 891: Can't create table 'fe_session_data' (errno: -1)
ERROR 1005 (HY000) at line 916: Can't create table 'fe_sessions' (errno: -1)
ERROR 1005 (HY000) at line 1080: Can't create table 'index_fulltext' (errno: -1)
ERROR 1005 (HY000) at line 1105: Can't create table 'index_grlist' (errno: -1)
ERROR 1005 (HY000) at line 1134: Can't create table 'index_phash' (errno: -1)
ERROR 1005 (HY000) at line 1182: Can't create table 'index_rel' (errno: -1)
ERROR 1005 (HY000) at line 1211: Can't create table 'index_section' (errno: -1)
ERROR 1005 (HY000) at line 1244: Can't create table 'index_stat_search' (errno: -1)
ERROR 1005 (HY000) at line 1273: Can't create table 'index_stat_word' (errno: -1)
ERROR 1005 (HY000) at line 1300: Can't create table 'index_words' (errno: -1)
ERROR 1005 (HY000) at line 2002: Can't create table 'sys_history' (errno: -1)
ERROR 1005 (HY000) at line 2097: Can't create table 'sys_log' (errno: -1)
ERROR 1005 (HY000) at line 2619: Can't create table 'tt_news_cache' (errno: -1)
ERROR 1005 (HY000) at line 2649: Can't create table 'tt_news_cache_tags' (errno: -1)
ERROR 1005 (HY000) at line 2958: Can't create table 'tx_realurl_chashcache' (errno: -1)
ERROR 1005 (HY000) at line 3014: Can't create table 'tx_realurl_pathcache' (errno: -1)
ERROR 1005 (HY000) at line 3111: Can't create table 'tx_realurl_urldecodecache' (errno: -1)
ERROR 1005 (HY000) at line 3140: Can't create table 'tx_realurl_urlencodecache' (errno: -1)

looking for the reason of these errors (this worked correct without the innodb_file_per_table setting) i only can guess because of errno -1.

What makes me wonder is that indexes are used with same name, so there are a lot of duplicate index names. I will retry with renaming all these indexes to see if this is the problem.
Anyways, i thing, it's a bad habit to use such index names. I came to the habit to set index names like "tablname_indexname_idx" and constraints like "tablename_indexname_fk" to prevent any duplicate name.

Maybe you have any suggestion?

vg Steffen