[Resolved] Database upgrade Toolset – Problem migrating associations

[Author]

Posts

[SJ5362]

I'm upgrading my WooComemrce site :
* Upgrade WooCommerce version
* Upgrade plugins
* Upgrade Toolset components : View, Types and Access
* Update Database requested by toolset - problems with CPT relationship of Books and Authors

The staging site : hidden link
I'll give you the access when it is set to private.

Thank you for your help.

The log :

> The upgrade process is starting...
> Precondition "correct database layer mode"... OK
> Precondition "toolset_associations table exists"... OK
> Precondition "toolset_associations_old table is not yet present (indicates manual database manipulation)"... OK
> Precondition "toolset_associations_new table is not yet present (indicates interrupted upgrade process)"... OK
> Precondition "toolset_connected_elements table is not yet present"... OK
> Precondition "able to create a new database table"... OK
> Precondition "able to delete a database table"... OK
> Precondition "WPML not active or in a good version (4.4.0 or above)"... OK
> Temporary migration tables have been removed if they existed.
> Clearing persisted migration state if it exists from previous attempts.
> Created table "wp_ibciva82ee_toolset_associations_new".
> Created table "wp_ibciva82ee_toolset_connected_elements".
> Error when migrating association #295: Post #2571 doesn't exist. An association involving it will be skipped.
> Error when migrating association #296: Post #2571 doesn't exist. An association involving it will be skipped.
> Error when migrating association #297: Post #2571 doesn't exist. An association involving it will be skipped.
> Completed batch of 250 associations between #4 and #335.
> Completed batch of 201 associations between #336 and #540.
> All associations have been migrated to the new temporary table.
> Le mode de maintenance est activé.
> Old and new association tables have been renamed successfully ("wp_ibciva82ee_toolset_associations" to "wp_ibciva82ee_toolset_associations_old" and "wp_ibciva82ee_toolset_associations_new" to "wp_ibciva82ee_toolset_associations").
> Database layer mode has been updated successfully.
> Le mode de maintenance est désactivé.

Toolset Debug Information and Troubleshooting:

{"core":{"Wordpress":{"Multisite":"No","SiteURL":"http:\/\/staging.123clicsite.flywheelsites.com","HomeURL":"http:\/\/staging.123clicsite.flywheelsites.com","Version":"5.5.3","PermalinkStructure":"\/%category%\/%postname%\/","PostTypes":"post, page, attachment, revision, nav_menu_item, custom_css, customize_changeset, oembed_cache, user_request, wp_block, project, scheduled-action, wp-types-group, wp-types-user-group, wp-types-term-group, product, product_variation, shop_order, shop_order_refund, wpephpcompat_jobs, view, view-template, book-author, auteur-product, book-artist, product-artist, editor, editor-product, auteur-editor, wpa-helper, et_theme_builder, et_template, et_header_layout, et_body_layout, et_footer_layout","PostSatus":"publish, future, draft, pending, private, trash, auto-draft, inherit, request-pending, request-confirmed, request-failed, request-completed, in-progress, failed, wc-pending, wc-processing, wc-on-hold, wc-completed, wc-cancelled, wc-refunded, wc-failed, hidden"},"Server":{"jQueryVersion":"1.12.4-wp","PHPVersion":"7.3.25-1+ubuntu16.04.1+deb.sury.org+1","MySQLVersion":"5.6.32","ServerSoftware":"Flywheel\/4.1.0"},"PHP":{"MemoryLimit":"128M","UploadMax":"300M","PostMax":"300M","TimeLimit":"180s","MaxInputVars":"5000"}},"plugins":{"active_plugins":{"advanced-woocommerce-product-gallery-slider\/woo-product-gallery-image-slider.php":{"WC requires at least":"","WC tested up to":"","Woo":"","Name":"Advanced Woocommerce Product Gallery Slider","PluginURI":"https:\/\/wordpress.org\/plugins\/advanced-woo-product-gallery-images-slider\/","Version":"1.0.0","Author":"UnikInfotech","AuthorURI":"http:\/\/www.unikinfotech.com","TextDomain":"advanced-woocommerce-product-gallery-slider","DomainPath":"","Network":false,"RequiresWP":"","RequiresPHP":"","Title":"Advanced Woocommerce Product Gallery Slider","AuthorName":"UnikInfotech"},"classic-editor\/classic-editor.php":{"WC requires at least":"","WC tested up to":"","Woo":"","Name":"Classic Editor","PluginURI":"https:\/\/wordpress.org\/plugins\/classic-editor\/","Version":"1.6","Author":"WordPress Contributors","AuthorURI":"https:\/\/github.com\/WordPress\/classic-editor\/","TextDomain":"classic-editor","DomainPath":"\/languages","Network":false,"RequiresWP":"4.9","RequiresPHP":"5.2.4","Title":"Classic Editor","AuthorName":"WordPress Contributors"},"express-checkout-paypal-payment-gateway-for-woocommerce\/express-checkout-paypal-payment-gateway-for-woocommerce.php":{"WC requires at least":"","WC tested up to":"4.8.0","Woo":"","Name":"PayPal Express Checkout Payment Gateway for WooCommerce ( Basic )","PluginURI":"https:\/\/wordpress.org\/plugins\/express-checkout-paypal-payment-gateway-for-woocommerce\/","Version":"1.5.3","Author":"WebToffee","AuthorURI":"https:\/\/www.webtoffee.com\/product\/paypal-express-checkout-gateway-for-woocommerce\/","TextDomain":"express-checkout-paypal-payment-gateway-for-woocommerce","DomainPath":"","Network":false,"RequiresWP":"","RequiresPHP":"","Title":"PayPal Express Checkout Payment Gateway for WooCommerce ( Basic )","AuthorName":"WebToffee"},"types-access\/types-access.php":{"WC requires at least":"","WC tested up to":"","Woo":"","Name":"Toolset Access","PluginURI":"http:\/\/toolset.com\/home\/types-access\/?utm_source=accessplugin\u0026utm_campaign=access\u0026utm_medium=release-notes-plugins-list\u0026utm_term=Visit plugin site","Version":"2.8.10","Author":"OnTheGoSystems","AuthorURI":"http:\/\/www.onthegosystems.com\/","TextDomain":"types-access","DomainPath":"","Network":false,"RequiresWP":"","RequiresPHP":"","Title":"Toolset Access","AuthorName":"OnTheGoSystems"},"types\/wpcf.php":{"WC requires at least":"","WC tested up to":"","Woo":"","Name":"Toolset Types","PluginURI":"https:\/\/toolset.com\/?utm_source=typesplugin\u0026utm_campaign=types\u0026utm_medium=plugins-list-full-version\u0026utm_term=Visit plugin site","Version":"3.4.5","Author":"OnTheGoSystems","AuthorURI":"http:\/\/www.onthegosystems.com","TextDomain":"types","DomainPath":"","Network":false,"RequiresWP":"","RequiresPHP":"","Title":"Toolset Types","AuthorName":"OnTheGoSystems"},"widget-shortcode\/init.php":{"WC requires at least":"","WC tested up to":"","Woo":"","Name":"Widget Shortcode","PluginURI":"","Version":"0.3.5","Author":"Hassan Derakhshandeh","AuthorURI":"","TextDomain":"widget-shortcode","DomainPath":"\/languages","Network":false,"RequiresWP":"","RequiresPHP":"","Title":"Widget Shortcode","AuthorName":"Hassan Derakhshandeh"},"woocommerce-admin\/woocommerce-admin.php":{"WC requires at least":"4.5.0","WC tested up to":"4.7.0","Woo":"","Name":"WooCommerce Admin","PluginURI":"https:\/\/github.com\/woocommerce\/woocommerce-admin","Version":"1.8.3","Author":"WooCommerce","AuthorURI":"https:\/\/woocommerce.com\/","TextDomain":"woocommerce-admin","DomainPath":"\/languages","Network":false,"RequiresWP":"5.3","RequiresPHP":"5.6.20","Title":"WooCommerce Admin","AuthorName":"WooCommerce"},"woocommerce-menu-bar-cart\/wp-menu-cart.php":{"WC requires at least":"2.0.0","WC tested up to":"4.6.0","Woo":"","Name":"WooCommerce Menu Cart","PluginURI":"www.wpovernight.com\/plugins","Version":"2.9.6","Author":"Jeremiah Prummer, Ewout Fernhout","AuthorURI":"www.wpovernight.com\/","TextDomain":"wp-menu-cart","DomainPath":"","Network":false,"RequiresWP":"","RequiresPHP":"","Title":"WooCommerce Menu Cart","AuthorName":"Jeremiah Prummer, Ewout Fernhout"},"woocommerce-views\/views-woocommerce.php":{"WC requires at least":"","WC tested up to":"4.7.1","Woo":"","Name":"Toolset WooCommerce Blocks","PluginURI":"https:\/\/toolset.com\/course\/custom-woocommerce-site\/?utm_source=plugin\u0026utm_medium=gui\u0026utm_campaign=woocommerceblocks","Version":"2.9.4","Author":"OnTheGoSystems","AuthorURI":"http:\/\/www.onthegosystems.com","TextDomain":"woocommerce-views","DomainPath":"","Network":false,"RequiresWP":"","RequiresPHP":"","Title":"Toolset WooCommerce Blocks","AuthorName":"OnTheGoSystems"},"woocommerce\/woocommerce.php":{"WC requires at least":"","WC tested up to":"","Woo":"","Name":"WooCommerce","PluginURI":"https:\/\/woocommerce.com\/","Version":"4.9.0","Author":"Automattic","AuthorURI":"https:\/\/woocommerce.com","TextDomain":"woocommerce","DomainPath":"\/i18n\/languages\/","Network":false,"RequiresWP":"5.3","RequiresPHP":"7.0","Title":"WooCommerce","AuthorName":"Automattic"},"wp-views\/wp-views.php":{"WC requires at least":"","WC tested up to":"","Woo":"","Name":"Toolset Views","PluginURI":"https:\/\/toolset.com\/?utm_source=viewsplugin\u0026utm_campaign=views\u0026utm_medium=plugins-list-full-version\u0026utm_term=Visit plugin site","Version":"3.3.5","Author":"OnTheGoSystems","AuthorURI":"https:\/\/toolset.com","TextDomain":"wp-views","DomainPath":"","Network":false,"RequiresWP":"","RequiresPHP":"","Title":"Toolset Views","AuthorName":"OnTheGoSystems"},"wpc-variations-radio-buttons\/wpc-variations-radio-buttons.php":{"WC requires at least":"3.0","WC tested up to":"4.8.0","Woo":"","Name":"WPC Variations Radio Buttons for WooCommerce","PluginURI":"https:\/\/wpclever.net\/","Version":"2.3.0","Author":"WPClever.net","AuthorURI":"https:\/\/wpclever.net","TextDomain":"wpc-variations-radio-buttons","DomainPath":"\/languages\/","Network":false,"RequiresWP":"4.0","RequiresPHP":"","Title":"WPC Variations Radio Buttons for WooCommerce","AuthorName":"WPClever.net"}},"mu_plugins":{"SupportCenterMUAutoloader.php":{"WC requires at least":"","WC tested up to":"","Woo":"","Name":"ET Support Center :: Must-Use Plugins Autoloader","PluginURI":"http:\/\/www.elegantthemes.com","Version":"","Description":"This plugin enables the Elegant Themes Support Center to provide more consistent functionality when Safe Mode is active.","Author":"Elegant Themes","AuthorURI":"http:\/\/www.elegantthemes.com","TextDomain":"","DomainPath":"","Network":false,"RequiresWP":"","RequiresPHP":"","Title":"ET Support Center :: Must-Use Plugins Autoloader","AuthorName":"Elegant Themes"}},"dropins":{"db-error.php":{"WC requires at least":"","WC tested up to":"","Woo":"","Name":"db-error.php","PluginURI":"","Version":"","Description":"","Author":"","AuthorURI":"","TextDomain":"","DomainPath":"","Network":false,"RequiresWP":"","RequiresPHP":"","Title":"","AuthorName":""}}},"theme":{"Name":"123clic","ThemeURI":null,"Author":"\u003Ca href=\u0022http:\/\/www.elegantthemes.com\u0022\u003EElegant Themes\u003C\/a\u003E","AuthorURI":null,"Template":"Divi","Version":"1.0.0","TextDomain":null,"DomainPath":null},"extra-debug":{"access":{"rules":[],"settings":{"types":{"product":{"mode":"permissions","permissions":{"publish":{"roles":["administrator"]},"delete_any":{"roles":["administrator"]},"edit_any":{"roles":["administrator"]},"delete_own":{"roles":["administrator"]},"edit_own":{"roles":["administrator"]},"read_private":{"roles":["administrator"]},"read":{"roles":["administrator"]}}},"shop_order":{"mode":"permissions","permissions":{"publish":{"roles":["administrator"]},"delete_any":{"roles":["administrator"]},"edit_any":{"roles":["administrator"]},"delete_own":{"roles":["administrator"]},"edit_own":{"roles":["administrator"]},"read_private":{"roles":["administrator"]},"read":{"roles":["administrator"]}}},"book-author":{"mode":"permissions","permissions":{"publish":{"roles":["administrator"]},"delete_any":{"roles":["administrator"]},"edit_any":{"roles":["administrator"]},"delete_own":{"roles":["administrator"]},"edit_own":{"roles":["administrator"]},"read_private":{"roles":["administrator"]},"read":{"roles":["administrator"]}}},"book-artist":{"mode":"permissions","permissions":{"publish":{"roles":["administrator"]},"delete_any":{"roles":["administrator"]},"edit_any":{"roles":["administrator"]},"delete_own":{"roles":["administrator"]},"edit_own":{"roles":["administrator"]},"read_private":{"roles":["administrator"]},"read":{"roles":["administrator"]}}},"editor":{"mode":"permissions","permissions":{"publish":{"roles":["administrator"]},"delete_any":{"roles":["administrator"]},"edit_any":{"roles":["administrator"]},"delete_own":{"roles":["administrator"]},"edit_own":{"roles":["administrator"]},"read_private":{"roles":["administrator"]},"read":{"roles":["administrator"]}}},"accordion_menu":{"mode":"not_managed"},"project":{"mode":"permissions","permissions":{"read":{"roles":[]}}},"page":{"mode":"permissions","permissions":{"publish":{"roles":["administrator"]},"delete_any":{"roles":["administrator"]},"edit_any":{"roles":["administrator"]},"delete_own":{"roles":["administrator"]},"edit_own":{"roles":["administrator"]},"read_private":{"roles":["administrator"]},"read":{"roles":["administrator"]}}},"post":{"mode":"permissions","permissions":{"publish":{"roles":["administrator"]},"delete_any":{"roles":["administrator"]},"edit_any":{"roles":["administrator"]},"delete_own":{"roles":["administrator"]},"edit_own":{"roles":["administrator"]},"read_private":{"roles":["administrator"]},"read":{"roles":["administrator"]}}},"attachment":{"mode":"permissions","permissions":{"publish":{"roles":["administrator"]},"delete_any":{"roles":["administrator"]},"edit_any":{"roles":["administrator"]},"delete_own":{"roles":["administrator"]},"edit_own":{"roles":["administrator"]},"read_private":{"roles":["administrator"]},"read":{"roles":["administrator"]}}},"oembed_cache":{"mode":"not_managed"},"user_request":{"mode":"not_managed"},"scheduled-action":{"mode":"not_managed"},"product_variation":{"mode":"not_managed"},"shop_order_refund":{"mode":"not_managed"},"wpephpcompat_jobs":{"mode":"not_managed"},"auteur-product":{"mode":"not_managed"},"product-artist":{"mode":"not_managed"},"editor-product":{"mode":"not_managed"},"auteur-editor":{"mode":"not_managed"},"et_theme_builder":{"mode":"not_managed"},"et_template":{"mode":"not_managed"},"et_header_layout":{"mode":"not_managed"},"et_body_layout":{"mode":"not_managed"},"et_footer_layout":{"mode":"not_managed"},"et_pb_layout":{"mode":"not_managed"}},"tax":[],"third_party":[]}},"types":{"add_resized_images_to_library":0,"images_remote":0,"images_remote_cache_time":"36","help_box":"by_types","hide_standard_custom_fields_metabox":"show","postmeta_unfiltered_html":"on","usermeta_unfiltered_html":"on"},"views":{"views_template_loop_blog":0,"wpv_bootstrap_version":1,"wpv_custom_conditional_functions":["woo_product_on_sale","woo_product_in_stock","wpv_woo_single_products_rating_func","wpv_woo_list_attributes_func","wpv_woo_show_upsell_func","wpv_woo_products_rating_on_listing_func","woo_has_product_subcategory","woo_shop_display_is_categories","wpv_woo_product_belongs_to_this_order"],"wpv_custom_inner_shortcodes":[],"wpv_debug_mode":"","wpv_debug_mode_type":"compact","wpv_map_plugin":0,"wpv_show_hidden_fields":"_price,_regular_price","wpv_saved_auto_detected_framework":"","wpv_codemirror_autoresize":"","wpv_enable_pagination_manage_history":0,"wpv_enable_parametric_search_manage_history":0,"support_spaces_in_meta_filters":0,"allow_views_wp_widgets_in_elementor":0,"default_user_editor":"basic","default_wpa_editor":"basic","manage_meta_transient_method":"cron","editing_experience":"classic","views_template_for_book-artist":332,"views_template_for_book-author":732,"view_cpt_product":"333","view_taxonomy_loop_pa_product":2062,"view_taxonomy_loop_product_tag":2076,"view_taxonomy_loop_product_cat":"1448"},"installer":{"version":"2.5.4","repositories":{"wpml":{"api-url":"https:\/\/api.wpml.org\/","bucket-url":"not assigned","subscription":"","last-successful-subscription-fetch":"none"},"toolset":{"api-url":"https:\/\/api.toolset.com\/","bucket-url":"https:\/\/wptypes.s3.amazonaws.com\/toolset-bucket-20.json","subscription":{"key":"DXEm1zn3ol","data":{"subscription_type":5740,"expires":null,"status":"4","notes":"upgrade for #51284"},"registered_by":1,"site_url":"http:\/\/staging.123clicsite.flywheelsites.com"},"last-successful-subscription-fetch":"2021-01-16 05:40:46"}},"instances":{"\/www\/wp-content\/plugins\/types\/vendor\/otgs\/installer\/installer.php":{"bootfile":"\/www\/wp-content\/plugins\/types\/vendor\/otgs\/installer\/installer.php","version":"2.5.4","args":{"plugins_install_tab":"1","repositories_include":["toolset","wpml"],"site_key_nags":[{"repository_id":"toolset","product_name":"Toolset"}]},"delegated":true}}}}}

[Jamal]

Hello and thank you for contacting the Toolset support.

I am setting your next reply to be private to let you share credentials safely. We may also need a copy of your website before the update, let us know if that's available on this staging site, or if you can prepare a staging site with an older copy.

[Jamal]

Thank you for the credentials. I run the Toolset migration and I have got the same results as you. There are 3 errors for migrating the associations:

> Error when migrating association #295: Post  doesn't exist. An association involving it will be skipped.
> Error when migrating association #296: Post #2571 doesn't exist. An association involving it will be skipped.
> Error when migrating association #297: Post #2571 doesn't exist. An association involving it will be skipped.

They are all about the same post( ID 2571 ). This post does not exist anymore in the database. It has been deleted before but its associations to other posts were not removed. I'd suspect that it was a Product, because products have 3 relationships to other post types. I uploaded the Adminer script to check the database in order to analyze it further, but the server does not allow me to use it. I guess, it is a hosting restriction.

I would say that this error can be ignored. Unless you have noticed an error on your product, a missing relationship, or something that is not working as expected.
On the other hand, if you would like us to investigate more, we would need an older update where the Toolset plugins are also not updated and no changes to the database were made by the Toolset Installer. We would like to check the database before updating Toolset plugins. Because Adminer is not working on the staging site, I would prefer to take a copy and analyzing it locally.

[SJ5362]

Hello Jamal,

Thank you for your explanation. The problem is that no relationship is working after having updated Toolset Database. The book is not associated correctly to the author. The same problem occurs to the other site as well. I thought that all updates went well as I saw no error but in fact the relationship between posts have been completely wrong. I'll open another ticket if needed.

I can send you a copy of the site before all updates, if you can set the next replay to private so that I'll send you the link to download the file. Thank you for your help.

Best regards,
Sawitri

[Jamal]

Sure. Your next message will be private, so you can share the copy safely.

However, I would like that you give some examples of associations of books and authors that are working on the previous versions and that present issues after the update. This way, we have a lead to follow during our debugging.

You may open a new ticket if you want us to check the second website too. You can assign it directly to me or leave a message, so my colleagues will assign it to me. That way you have only one supporter to talk to instead of multiple supporters.

[Jamal]

Thank you! However, as I requested before, can you give an example of associations between authors and books that were working before the update and that broke after the update?

[SJ5362]

Hello Jamal,

Sorry I forgot the details in my previous email.

Basically, all relationship between book and author will be broken after updating Toolset database.

Broken data examples :

* Page 'Nos auteurs' : listing all authors of Editions A Propos - This page will become empty : hidden link
* Book - The tab 'Auteur(e)(s) listing authors of the book will be empty too, all the books will no longer have related authors :

- hidden link
In this book, the filed author in orange, just under the book title, will disappear and the information about the book under the images in the tab 'Auteur(e)(s) will contain no information as well.

Please let me know if you need any other information. Thank you again for your help.

best regards,
Sawitri

[Jamal]

Hello Sawitri and thank you for your feedback.

I built a copy of your website locally and run the migration again, and I could not find any issues with the relationships. I compared some products and their relationships before and after the update and no associations were lost.

So, I debugged a bit the issues that you have mentioned:
- Nos-auteurs page.
- Auteur tab in product pages.
And it turned out that this is caused by the theme, which uses direct SQL queries to the database instead of using Toolset functions that are backward compatible.

Toolset Types 3.4 has changed the database schema, another table was introduced for the relationships and the theme's SQL queries are not correct anymore.

For example, the page Nos-Auteurs uses a shortcode "relatedauthor" hidden link that is defined int the theme's functions.php file between lines 273-314. This shortcode's function uses direct SQL query to the database at line 279:

$e_authors = $GLOBALS['wpdb']->get_results("SELECT * FROM {$wpdb->prefix}toolset_associations WHERE child_id = 586 AND relationship_id = 6", OBJECT);

This query is not correct anymore with the new database schema.

The product's page uses custom code to add the Auteurs tab. The code is defined in the theme's functions.php file between lines 69-103. This code uses a direct SQL query at line 91:

$authors = $GLOBALS['wpdb']->get_results("SELECT * FROM {$wpdb->prefix}toolset_associations WHERE child_id = " . $post->ID . " AND relationship_id = 1", OBJECT);

This query is not correct anymore with the new database schema.

Your theme is a custom-made theme and is therefore out of the scope of the support. You will need to contact your theme's developer to update the theme for the new Toolset relationships database schema. Or you may want to hire one of our partners to help you update the theme. https://toolset.com/contractors/