Bug #89306

postgres Analyze Database Structure and Upgrade Wizzard fails to read indicies and primary keys

Added by Gerhard Heide 4 months ago. Updated 3 months ago.

Status:
Needs Feedback
Priority:
Should have
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2019-09-30
Due date:
% Done:

0%

TYPO3 Version:
9
PHP Version:
7.2
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

Typo3 9.5.9 LTS
Postgres 10.7
OS Windows
php 7.22
php driver pdo_pgsql

After fresh install of v9 LTS 'Analyze Database Structure' reports
a) a lot of missing indexes even though the indexes exist
b) missing primary keys even though the columns are primary keys

Upgrade Wizard reports the same, does this mean upgrade wizzard is not supported on postgres?

output from Analyze Database Structure:

Add fields to tables
select/deselect all
ALTER TABLE "be_groups" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_b16c85e9" ON "be_groups" ("pid", "deleted", "hidden")
ALTER TABLE "be_sessions" ADD PRIMARY KEY ("ses_id")
CREATE INDEX "ses_tstamp_c7bf568a" ON "be_sessions" ("ses_tstamp")
CREATE INDEX "username_a59b1520" ON "be_users" ("username")
ALTER TABLE "be_users" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_5253dc25" ON "be_users" ("pid", "deleted", "disable")
CREATE INDEX "alias_0c9f22fe" ON "pages" ("alias")
CREATE INDEX "determineSiteRoot_5c54427f" ON "pages" ("is_siteroot")
CREATE INDEX "language_identifier_8c77f4c2" ON "pages" ("l10n_parent", "sys_language_uid")
ALTER TABLE "pages" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_90041b40" ON "pages" ("pid", "deleted", "hidden")
CREATE INDEX "t3ver_oid_f068716b" ON "pages" ("t3ver_oid", "t3ver_wsid")
ALTER TABLE "sys_registry" ADD PRIMARY KEY ("uid")
CREATE UNIQUE INDEX "entry_identifier_dab848e9" ON "sys_registry" ("entry_namespace", "entry_key")
ALTER TABLE "sys_be_shortcuts" ADD PRIMARY KEY ("uid")
CREATE INDEX "event_c44dcccf" ON "sys_be_shortcuts" ("userid")
ALTER TABLE "sys_news" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_17ba38d7" ON "sys_news" ("pid", "deleted", "hidden")
ALTER TABLE "sys_filemounts" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_263d95c1" ON "sys_filemounts" ("pid", "deleted", "hidden")
ALTER TABLE "sys_file_storage" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_fc7d5056" ON "sys_file_storage" ("pid", "deleted")
CREATE INDEX "sel01_30b2552c" ON "sys_file" ("storage", "identifier_hash")
CREATE INDEX "folder_f228b49e" ON "sys_file" ("storage", "folder_hash")
CREATE INDEX "tstamp_ac973b32" ON "sys_file" ("tstamp")
CREATE INDEX "lastindex_7981f97a" ON "sys_file" ("last_indexed")
CREATE INDEX "sha1_c2b16870" ON "sys_file" ("sha1")
ALTER TABLE "sys_file" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_2304dd1c" ON "sys_file" ("pid")
CREATE INDEX "file_a8a43bc7" ON "sys_file_metadata" ("file")
CREATE INDEX "fal_filelist_3fe1fc5b" ON "sys_file_metadata" ("l10n_parent", "sys_language_uid")
ALTER TABLE "sys_file_metadata" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_8d466b11" ON "sys_file_metadata" ("pid")
CREATE INDEX "t3ver_oid_2afb6cbb" ON "sys_file_metadata" ("t3ver_oid", "t3ver_wsid")
ALTER TABLE "sys_file_processedfile" ADD PRIMARY KEY ("uid")
CREATE INDEX "combined_1_54bd7fb4" ON "sys_file_processedfile" ("original", "task_type", "configurationsha1")
CREATE INDEX "identifier_08f63fda" ON "sys_file_processedfile" ("storage", "identifier")
CREATE INDEX "tablenames_fieldname_2fe50272" ON "sys_file_reference" ("tablenames", "fieldname")
CREATE INDEX "deleted_ec55f9ff" ON "sys_file_reference" ("deleted")
CREATE INDEX "uid_local_6e063d86" ON "sys_file_reference" ("uid_local")
CREATE INDEX "uid_foreign_84870ada" ON "sys_file_reference" ("uid_foreign")
CREATE INDEX "combined_1_b25ed807" ON "sys_file_reference" ("l10n_parent", "t3ver_oid", "t3ver_wsid", "t3ver_state", "deleted")
ALTER TABLE "sys_file_reference" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_3efdb54b" ON "sys_file_reference" ("pid", "deleted", "hidden")
CREATE INDEX "t3ver_oid_08d845fb" ON "sys_file_reference" ("t3ver_oid", "t3ver_wsid")
ALTER TABLE "sys_file_collection" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_cfb58add" ON "sys_file_collection" ("pid", "deleted", "hidden")
CREATE INDEX "t3ver_oid_d19f62d6" ON "sys_file_collection" ("t3ver_oid", "t3ver_wsid")
ALTER TABLE "sys_collection" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_9a244dea" ON "sys_collection" ("pid", "deleted", "hidden")
CREATE INDEX "t3ver_oid_f30873cd" ON "sys_collection" ("t3ver_oid", "t3ver_wsid")
CREATE INDEX "uid_local_97c0330b" ON "sys_collection_entries" ("uid_local")
CREATE INDEX "uid_foreign_ed12381d" ON "sys_collection_entries" ("uid_foreign")
ALTER TABLE "sys_collection_entries" ADD PRIMARY KEY ("uid")
CREATE INDEX "recordident_1_e1dd6ed6" ON "sys_history" ("tablename", "recuid")
CREATE INDEX "recordident_2_78d43f6c" ON "sys_history" ("tablename", "tstamp")
ALTER TABLE "sys_history" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_52f2b9f6" ON "sys_history" ("pid")
ALTER TABLE "sys_lockedrecords" ADD PRIMARY KEY ("uid")
CREATE INDEX "event_5458b68a" ON "sys_lockedrecords" ("userid", "tstamp")
ALTER TABLE "sys_refindex" ADD PRIMARY KEY ("hash")
CREATE INDEX "lookup_rec_000b43f6" ON "sys_refindex" ("tablename", "recuid")
CREATE INDEX "lookup_uid_37958fdc" ON "sys_refindex" ("ref_table", "ref_uid")
CREATE INDEX "lookup_string_13590c2e" ON "sys_refindex" ("ref_string")
CREATE INDEX "event_63aff1ce" ON "sys_log" ("userid", "event_pid")
CREATE INDEX "recuidIdx_c290c39d" ON "sys_log" ("recuid")
CREATE INDEX "user_auth_014b30af" ON "sys_log" ("type", "action", "tstamp")
CREATE INDEX "request_a5c72ce4" ON "sys_log" ("request_id")
CREATE INDEX "combined_1_9d80faf5" ON "sys_log" ("tstamp", "type", "userid")
ALTER TABLE "sys_log" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_09b8b848" ON "sys_log" ("pid")
ALTER TABLE "sys_language" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_b7280232" ON "sys_language" ("pid", "hidden")
CREATE INDEX "category_parent_f5445003" ON "sys_category" ("parent")
CREATE INDEX "category_list_9214ae08" ON "sys_category" ("pid", "deleted", "sys_language_uid")
ALTER TABLE "sys_category" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_a4d3f12f" ON "sys_category" ("pid", "deleted", "hidden")
CREATE INDEX "t3ver_oid_0fc5a153" ON "sys_category" ("t3ver_oid", "t3ver_wsid")
CREATE INDEX "uid_local_foreign_a25e7184" ON "sys_category_record_mm" ("uid_local", "uid_foreign")
CREATE INDEX "uid_foreign_tablefield_7452b228" ON "sys_category_record_mm" ("uid_foreign", "tablenames", "fieldname", "sorting_foreign")
ALTER TABLE "fe_users" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_d82fb946" ON "fe_users" ("pid", "username")
CREATE INDEX "username_d69332ef" ON "fe_users" ("username")
CREATE INDEX "is_online_3081e577" ON "fe_users" ("is_online")
ALTER TABLE "fe_groups" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_655dc96e" ON "fe_groups" ("pid", "deleted", "hidden")
ALTER TABLE "cache_treelist" ADD PRIMARY KEY ("md5hash")
ALTER TABLE "fe_sessions" ADD PRIMARY KEY ("ses_id")
CREATE INDEX "ses_tstamp_9b1ec58a" ON "fe_sessions" ("ses_tstamp")
CREATE INDEX "getSysDomain_3399d09e" ON "sys_domain" ("hidden")
CREATE INDEX "getDomainStartPage_bb295ce3" ON "sys_domain" ("pid", "hidden", "domainName")
ALTER TABLE "sys_domain" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_c7fb3c37" ON "sys_domain" ("pid", "hidden")
CREATE INDEX "roottemplate_b05d9c09" ON "sys_template" ("deleted", "hidden", "root")
ALTER TABLE "sys_template" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_a0d332a6" ON "sys_template" ("pid", "deleted", "hidden")
CREATE INDEX "t3ver_oid_01f605d5" ON "sys_template" ("t3ver_oid", "t3ver_wsid")
CREATE INDEX "parent_8b16a914" ON "tt_content" ("pid", "sorting")
CREATE INDEX "t3ver_oid_be974a6e" ON "tt_content" ("t3ver_oid", "t3ver_wsid")
CREATE INDEX "language_6f2c39a6" ON "tt_content" ("l18n_parent", "sys_language_uid")
ALTER TABLE "tt_content" ADD PRIMARY KEY ("uid")
ALTER TABLE "backend_layout" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_30db6e61" ON "backend_layout" ("pid", "deleted", "hidden")
CREATE INDEX "t3ver_oid_8dc31fea" ON "backend_layout" ("t3ver_oid", "t3ver_wsid")
ALTER TABLE "tx_impexp_presets" ADD PRIMARY KEY ("uid")
CREATE INDEX "lookup_a3f29024" ON "tx_impexp_presets" ("item_uid")
ALTER TABLE "tx_extensionmanager_domain_model_repository" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_9e955541" ON "tx_extensionmanager_domain_model_repository" ("pid")
CREATE INDEX "index_extrepo_0d47666f" ON "tx_extensionmanager_domain_model_extension" ("extension_key", "repository")
CREATE INDEX "index_versionrepo_e07e6c4b" ON "tx_extensionmanager_domain_model_extension" ("integer_version", "repository", "extension_key")
CREATE INDEX "index_currentversions_30ab63fc" ON "tx_extensionmanager_domain_model_extension" ("current_version", "review_state")
CREATE UNIQUE INDEX "versionextrepo_ff4327e2" ON "tx_extensionmanager_domain_model_extension" ("extension_key", "version", "repository")
ALTER TABLE "tx_extensionmanager_domain_model_extension" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_1b0566e3" ON "tx_extensionmanager_domain_model_extension" ("pid")
CREATE INDEX "index_source_a1641672" ON "sys_redirect" ("source_host", "source_path")
ALTER TABLE "sys_redirect" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_19d66a6b" ON "sys_redirect" ("pid", "deleted", "disabled")
ALTER TABLE "sys_note" ADD PRIMARY KEY ("uid")
CREATE INDEX "parent_83c1ac77" ON "sys_note" ("pid", "deleted")
ALTER TABLE "cf_cache_hash" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_6b1e2edb" ON "cf_cache_hash" ("identifier", "expires")
ALTER TABLE "cf_cache_hash_tags" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_3595a16f" ON "cf_cache_hash_tags" ("identifier")
CREATE INDEX "cache_tag_b5653678" ON "cf_cache_hash_tags" ("tag")
ALTER TABLE "cf_cache_pages" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_c881eb74" ON "cf_cache_pages" ("identifier", "expires")
ALTER TABLE "cf_cache_pages_tags" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_f514a7fb" ON "cf_cache_pages_tags" ("identifier")
CREATE INDEX "cache_tag_42c7e023" ON "cf_cache_pages_tags" ("tag")
ALTER TABLE "cf_cache_pagesection" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_fc62a803" ON "cf_cache_pagesection" ("identifier", "expires")
ALTER TABLE "cf_cache_pagesection_tags" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_27c9f1f9" ON "cf_cache_pagesection_tags" ("identifier")
CREATE INDEX "cache_tag_ac1b5c59" ON "cf_cache_pagesection_tags" ("tag")
ALTER TABLE "cf_cache_rootline" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_ef5e64e2" ON "cf_cache_rootline" ("identifier", "expires")
ALTER TABLE "cf_cache_rootline_tags" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_4278134e" ON "cf_cache_rootline_tags" ("identifier")
CREATE INDEX "cache_tag_f97bcb94" ON "cf_cache_rootline_tags" ("tag")
ALTER TABLE "cf_cache_imagesizes" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_53bc7e92" ON "cf_cache_imagesizes" ("identifier", "expires")
ALTER TABLE "cf_cache_imagesizes_tags" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_6618a100" ON "cf_cache_imagesizes_tags" ("identifier")
CREATE INDEX "cache_tag_683bc7b1" ON "cf_cache_imagesizes_tags" ("tag")
ALTER TABLE "cf_extbase_datamapfactory_datamap" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_9bcd31f4" ON "cf_extbase_datamapfactory_datamap" ("identifier", "expires")
ALTER TABLE "cf_extbase_datamapfactory_datamap_tags" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_e50d34a7" ON "cf_extbase_datamapfactory_datamap_tags" ("identifier")
CREATE INDEX "cache_tag_200ae46f" ON "cf_extbase_datamapfactory_datamap_tags" ("tag")
ALTER TABLE "cf_adminpanel_requestcache" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_f68b7367" ON "cf_adminpanel_requestcache" ("identifier", "expires")
ALTER TABLE "cf_adminpanel_requestcache_tags" ADD PRIMARY KEY ("id")
CREATE INDEX "cache_id_e6f21d49" ON "cf_adminpanel_requestcache_tags" ("identifier")
CREATE INDEX "cache_tag_67bbd439" ON "cf_adminpanel_requestcache_tags" ("tag")


Related issues

Related to TYPO3 Core - Bug #88176: DBAL Unique Key containing more than 3 columns not working Closed 2019-04-18

History

#1 Updated by Manuel Selbach 3 months ago

  • Assignee set to Manuel Selbach

#2 Updated by Manuel Selbach 3 months ago

  • Related to Bug #88176: DBAL Unique Key containing more than 3 columns not working added

#3 Updated by Manuel Selbach 3 months ago

  • Status changed from New to Needs Feedback

With the fix provided for https://forge.typo3.org/issues/88176 "changed indexes" will be identified and provided with SQL to update it in the "Analyze Database Structure".

May I ask you to check this with the current branch of 9.5 and provide some feedback?

#4 Updated by Gerhard Heide 3 months ago

I applied the changes in ConnectionMigrator.php to 9.5.9 LTS and set up a new typo3 installation - nothing changed - nothing broke.

The Upgrade-Wizzard / database analyser still complain about missing indexes (for the upgrade wizard this means I'm stuck), and keeps unable to create them.

I also checked if the has been a problem with multicolumne unique idexes:

SELECT indexname  FROM    pg_indexes where indexdef like 'CREATE UNIQ%,%,%,%' ;

            indexname
---------------------------------
 pg_amop_fam_strat_index
 pg_amproc_fam_proc_index
 pg_conversion_default_index
 pg_operator_oprname_l_r_n_index
 pg_seclabel_object_index
(5 Zeilen)


But al these pg_... indexes with more than 3 columns do also not show up in the list of missing indexes.

With and without the patch the indexes are the same - maybe v9 installations where never affected by this bug? I don'T know

I also dropped a table (sys_file_reference) with all indexes and recreated it with database analyser - but it still complains about missing indexes.

#5 Updated by Manuel Selbach 3 months ago

Thanks for for feedback.

May I ask you not only to adapt the changes from the patch, but use the latest state of the branch 9.5? There are some more changes, that are needed and applied before that change.

How did you apply the change?
Please contact me for further support on slack.

If you're not already using slack: https://typo3.org/article/how-to-use-slack-in-the-typo3-community/

Also available in: Atom PDF