[Closed] Database upgrade error 'Precondition "toolset_associations table exists"'

[Author]

Posts

[Altti Papinsaari]

Hello,

on my site's dashboard Toolset is asking me to update database. I clicked Update Now -button and after that the system stops at first part "Pre-migration site health check" with technical information:

> The upgrade process is starting...
> Precondition "correct database layer mode"... OK
> Precondition "toolset_associations table exists"... ERR

How can I fix this?

[Jamal]

Hello and thank you for contacting Toolset support.

It seems that Toolset was not able to create the "toolset_associations" table during the migration of the relationships. Please check the database using an phpMyAdmin or something similar(Adminer) if the table exists. If it does not, try to create it with the following query:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
 --
-- Table structure for table `wp_toolset_associations`
--
 
DROP TABLE IF EXISTS `wp_toolset_associations`;
CREATE TABLE IF NOT EXISTS `wp_toolset_associations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `relationship_id` bigint(20) UNSIGNED NOT NULL,
  `parent_id` bigint(20) UNSIGNED NOT NULL,
  `child_id` bigint(20) UNSIGNED NOT NULL,
  `intermediary_id` bigint(20) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `relationship_id` (`relationship_id`),
  KEY `parent_id` (`parent_id`,`relationship_id`),
  KEY `child_id` (`child_id`,`relationship_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Then, try the migration again. If the migration fails for another table such as "toolset_relationships" or "toolset_type_sets", check the SQL queries here https://toolset.com/forums/topic/toolset_relationships-doesnt-exist/

If you encounter any errors about table spaces, check the solutions here:
- https://stackoverflow.com/questions/17914446/mysqldump-problems-with-restore-error-please-discard-the-tablespace-before-imp/17915212#17915212
- https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp/21692066#21692066

If nothing of the above helps, I'll need a copy of your website to debug it locally. Please create an administrator account for me and share it in your next reply, which will be private.

[Jamal]

Please try the following query in your MySQL user interface:

RENAME TABLE  `wp_toolset_associations_new` TO  `wp_toolset_associations`

Let me know if that helps.

[Altti Papinsaari]

Hello Jamal,
I tried using the query but I got an error message: "#1050 - Table 'wp_toolset_associations' already exists"

[Jamal]

This means that the table was created and that the migration process gave a false positive.

Can you check the related posts in a couple of posts just to make sure that the relations have been migrated?

Can you also try to create a couple of new posts and connect them using a Toolset relationship to make sure the linking is working as expected?

[Altti Papinsaari]

Hello,

my site doesn't use relationships.

[Jamal]

If you don't use relationships, you can ignore that message. The migration is only about relationships and optimizing them. If you don't use relationships, you can just ignore the outcome of the migration.

I'll remain at your disposal.

[Altti Papinsaari]

Hello Jamal,

just make sure, as my site does not use relationships I can ignore this message on the site's dashboard?
"Toolset needs to update the database. This will take a few minutes to complete."

[Jamal]

Well, yes. But we won't recommend that. Especially, if you intend to use the relationships or WPML in the future. In that case, it would be better if you run the Toolset migration.

[Altti Papinsaari]

Thanks for quick reply!
OK, what should I do if I want run the update? It still fails at part 6 and running that query gives an error?

RENAME TABLE  `wp_toolset_associations_new` TO  `wp_toolset_associations`

[Jamal]

Would you allow me temporary access to your website to check this further?
I would also want the FTP access to activate PHP debugging, and to install adminer in order to check the database.

Your next reply will be private to let you share credentials safely. ** Make a database backup before sharing credentials. **

[Jamal]

Hello and my apologies for the late reply, but I do not work on Wednesdays and Thursdays as you may check on my profile page https://toolset.com/forums/users/jamal-b/

Thank you for the credentials. I can see why the migration script could not rename the table "wp_toolset_associations_new" to "wp_toolset_associations". Because an "wp_toolset_associations" table already exist. I think that a previous step should rename the existing "wp_toolset_associations" table to "wp_toolset_associations_old" table. But no such table exists. I assume, that happens because we have already edited the database manually.

Instead of debugging and touching the database on your live site, I would like to take a copy of the site, and work on it locally. Would you allow me to take such a copy?

[Altti Papinsaari]

Hello Jamal,

no problem! Take a copy for debugging and thank you for your help so far!

[Jamal]

The migration has finished without issues in my local environment. The tables were created and renamed as it is expected.

Currently, in your server, two tables are missing:
- wp_toolset_relationshipsHide
- wp_toolset_type_sets

So, I renamed the existing Toolset tables, and run the migration again. It failed again. Then, I created the necessary tables manually. But, some of the queries have failed, and I had to change the engine from InnoDB to MyISAM in my queries in order to create the tables.
I got errors similar to this one:

Error in query (1813): Tablespace for table '`sg2607_wp`.`wp_toolset_relationships`' exists. Please DISCARD the tablespace before IMPORT

Then, I tried the migration again, and it failed. Return to the starting point.

From what I gathered so far, the issue happens because of tablespaces errors. These are the errors discussed here:
- https://stackoverflow.com/questions/17914446/mysqldump-problems-with-restore-error-please-discard-the-tablespace-before-imp/17915212#17915212
- https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp/21692066#21692066

Unfortunately, that is not something I can fix, because I don't have access to the database server files. I can query the database using Adminer, which I uploaded to your server. But, I don't have access to the database server filesystem, so I can remove the existing tablespaces.

Please reach out to your hosting provider and ask them for help regarding these issues. Show them these articles:
- https://stackoverflow.com/questions/17914446/mysqldump-problems-with-restore-error-please-discard-the-tablespace-before-imp/17915212#17915212
- https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp/21692066#21692066

And tell them, that these errors happens for the tables(wp_toolset_associations, wp_toolset_relationships, wp_toolset_type_sets) and their respective tablespaces. Once they help you fix the tablespaces errors, run the migration again, and I am sure it will complete without issues.

I'll remain at your disposal. Let us know what you will get.