[Resolved] database update error

[Author]

Posts

[Nigel]

Sorry, we just cross posted.

I was writing to tell you that the upgrade worked on my copy of your site without a problem.

The only change I made was to disable the security plugins so that I could log into the site.

Can I get access to your site so that I can take a look at the database?

I will mark your next reply as private so that I can get log-in credentials from you—you may want to create a temporary admin user for me to use that you can later delete. And be sure to have a current backup of your site.

[Nigel]

I've done some checking on your site, I installed a couple of plugins to help, and the problem seems to be related to it not being possible to create the table wp_toolset_associations. It's missing, and Types should detect that and try to create it. It does create other custom tables successfully, but there appears to be something blocking it from creating that table.

I'm speaking to the devs to see how to proceed, and I'll update you again. I've left the plugins in place for the moment, but I'll delete them when I'm done.

[thomasarc]

Ok really great support, many thanks for trying to solve this 😉
I'll let all the plugins you need until you're finished !

[thomasarc]

Hi
any new with this issue ?
just to know 😉
thanks
cheers

[Nigel]

Sorry, nothing to report yet. This is assigned to the developer, but I think they are prioritising something related (the performance of relationship queries after migrating) that is affecting some clients, given that this issue is distracting you but not materially affecting your site.

I will let you know when I see some progress in the internal ticket.

[thomasarc]

No problem, thanks Nigel, it's perfectly understandable.
Have a nice day

[Nigel]

Hi Thomas

Hapy New Year.

The developer has been focusing on this, but is struggling to identify the problem.

Something specific to your server is preventing the creation of a required database table.

They have asked if you can try running the SQL command directly (within phpMyAdmin) to see if the table can be created that way, and to report back any messages generated with the attempt. (This way may give more details about why creating the table fails if it cannot be created.)

This is the specific query that needs to run:

CREATE TABLE 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 (id), KEY relationship_id (relationship_id), KEY parent_id (parent_id, relationship_id), KEY child_id (child_id, relationship_id), KEY intermediary_id (intermediary_id, relationship_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Once you've selected the correct database you should be able to enter that in the SQL tab and run it (see screenshot).

If it fails, please share details. You might then try disabling your security plugins (I think you have two) and try again, to see if they interfere in the process.

Let me know what you find.

[thomasarc]

Hi Nigel
Thanks for your update

Ok I tried the code and that is the answer :

#1813 - Tablespace for table '`psycle_wpcle`.`wp_toolset_associations`' exists. Please DISCARD the tablespace before IMPORT

this is what I had before but even with some googling I haven't been able to discard that f**** tablespace. And since the tablespace exists, I cannot delete the table to recreate it (what I wanted to do first)

for the 2 security plugins, that a terrible oversight from me, it should not have happened, stupid me. But even with the two deactivated, the results remains the same.

if you want access to my cpanel, I can grant access to your dev, I'm pretty sure you won't mess things up 😉 (anyway not worse than me)

[Nigel]

Thanks for that, I shared it with the dev, and they say, yes please, can they have access to cPanel.

I'll set up a private reply, the template will ask for site credentials, but you can put in your cPanel credentials instead.

[thomasarc]

[redacted]

[Nigel]

Thomas, the developer thinks they may have identified the problem, where the solution would be as described in either of these threads:

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

He tried to look into it via the cPanel terminal but it doesn't have the permissions to go to /var/lib/mysql.

So, this means you will probably have to talk to your hosting provider and try to solve the issue with them, given the above details.

We don't know how the issue on your server arose, but hopefully if they are able to resolve the above then the migration can complete and it won't happen again.

Please let me know how you get on.

[thomasarc]

Thank you very much for all your help, I will try with my provider, they are usually very fast and pretty good. I had seen such error but because I goes beyond my knowledge, I had no idea where it could come from.
So thanks again to have dig into this issue, you and the dev of course
I will let you know if things are back in order 😉

[thomasarc]

Hi
just to let you know that the only way we found with my hosting provider, the simplest in fact, was to copy all the table in a new db and change the db in wp. The tablespace has been remove with this operation and Toolset has been able to correctly update what it wanted.
So all is in order now..
You have a solution in case it happened for someone else, and it's done in 10mn...
thanks

[Nigel]

OK, that's great. We haven't seen it before, hopefully we won't see it again, but if we do we have this for reference.

Thanks for your patience.

[thomasarc]

My issue is resolved now. Thank you Nigel ! Great support !