Bug #89306
closed
postgres Analyze Database Structure and Upgrade Wizzard fails to read indicies and primary keys
0%
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")
Updated by Manuel Selbach over 4 years ago
- Related to Bug #88176: DBAL Unique Key containing more than 3 columns not working added
Updated by Manuel Selbach over 4 years 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?
Updated by Gerhard Heide over 4 years 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.
Updated by Manuel Selbach over 4 years 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/
Updated by Riccardo De Contardi over 4 years ago
- Status changed from Needs Feedback to Closed
- Assignee deleted (
Manuel Selbach)
There has no feedback since 90 days > Closing this issue.
If you think that this is the wrong decision and have more information about how to reproduce it, please reopen it or open a new issue with a reference to this one.
Thank you.