1812 Error during Upgrade (Tablespace for ...core_themes missing)

[sibomots]

The question is about tables losing their Tablespace DURING UPGRADE.

TL;DR:

The Question

In the recent past  (6-9 months) -- have any other users/admins come across this kind of error where during the Upgrade one of the tables (freshly cleaned/converted by IPB software prior to beginning the actual Upgrade) resulted in one of the tables losing its Tablespace during the Upgrade?   On the same table repeatedly?

Error 1812 emitted by the Upgrade SW during an Upgrade after successfully running the UTF-8 conversion tool?

1812
Tablespace is missing for table bolter4/x_utf_ibf_core_themes.
/DocumentRoot/applications//setup/upg_/queries.json - query #3

 

Details/Steps:

Version:   ips_469

Prerequisite check (php) of system passed before starting Upgrade.

# dpkg -l | grep php
ii  libapache2-mod-php7.4                 7.4.3-4ubuntu2.10                     amd64        server-side, HTML-embedded scripting language (Apache 2 module)
ii  php                                   2:7.4+75                              all          server-side, HTML-embedded scripting language (default)
ii  php-cli                               2:7.4+75                              all          command-line interpreter for the PHP scripting language (default)
ii  php-common                            2:75                                  all          Common files for PHP packages
ii  php-composer-ca-bundle                1.2.6-1                               all          utility library to find a path to the system CA bundle
ii  php-composer-semver                   1.5.1-1                               all          utilities, version constraint parsing and validation
ii  php-composer-spdx-licenses            1.5.3-1                               all          SPDX licenses list and validation library
ii  php-composer-xdebug-handler           1.4.0-1                               all          Restarts a process without Xdebug
ii  php-curl                              2:7.4+75                              all          CURL module for PHP [default]
ii  php-gd                                2:7.4+75                              all          GD module for PHP [default]
ii  php-gmp                               2:7.4+75                              all          GMP module for PHP [default]
ii  php-json-schema                       5.2.9-1                               all          implementation of JSON schema
ii  php-mbstring                          2:7.4+75                              all          MBSTRING module for PHP [default]
ii  php-psr-container                     1.0.0-2                               all          Common Container Interface (PHP FIG PSR-11)
ii  php-psr-log                           1.1.2-1                               all          common interface for logging libraries
ii  php-zip                               2:7.4+75                              all          Zip module for PHP [default]
ii  php7.4                                7.4.3-4ubuntu2.10                     all          server-side, HTML-embedded scripting language (metapackage)
ii  php7.4-cli                            7.4.3-4ubuntu2.10                     amd64        command-line interpreter for the PHP scripting language
ii  php7.4-common                         7.4.3-4ubuntu2.10                     amd64        documentation, examples and common module for PHP
ii  php7.4-curl                           7.4.3-4ubuntu2.10                     amd64        CURL module for PHP
ii  php7.4-gd                             7.4.3-4ubuntu2.10                     amd64        GD module for PHP
ii  php7.4-gmp                            7.4.3-4ubuntu2.10                     amd64        GMP module for PHP
ii  php7.4-json                           7.4.3-4ubuntu2.10                     amd64        JSON module for PHP
ii  php7.4-mbstring                       7.4.3-4ubuntu2.10                     amd64        MBSTRING module for PHP
ii  php7.4-mysql                          7.4.3-4ubuntu2.10                     amd64        MySQL module for PHP
ii  php7.4-opcache                        7.4.3-4ubuntu2.10                     amd64        Zend OpCache module for PHP
ii  php7.4-readline                       7.4.3-4ubuntu2.10                     amd64        readline module for PHP
ii  php7.4-xml                            7.4.3-4ubuntu2.10                     amd64        DOM, SimpleXML, XML, and XSL module for PHP
ii  php7.4-zip                            7.4.3-4ubuntu2.10                     amd64        Zip module for PHP
#
# dpkg -l | grep mysql
ii  mysql-client                          8.0.29-0ubuntu0.20.04.3               all          MySQL database client (metapackage depending on the latest version)
ii  mysql-client-8.0                      8.0.29-0ubuntu0.20.04.3               amd64        MySQL database client binaries
ii  mysql-client-core-8.0                 8.0.29-0ubuntu0.20.04.3               amd64        MySQL database core client binaries
ii  mysql-common                          5.8+1.0.5ubuntu2                      all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                          8.0.29-0ubuntu0.20.04.3               all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-8.0                      8.0.29-0ubuntu0.20.04.3               amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-8.0                 8.0.29-0ubuntu0.20.04.3               amd64        MySQL database server binaries
ii  php7.4-mysql                          7.4.3-4ubuntu2.10                     amd64        MySQL module for PHP
#
# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.4 LTS
Release:        20.04
Codename:       focal

 

Background:

In February 2022, we imported a legacy IPB database into mysql on new HW.   We applied the upgrade against that database with media files from the legacy site. 

We followed the upgrade path steps to the letter and were successful in standing up a new beta-site on version    ips_469    The beta-site test installation worked and the beta-site was successfully instantiated.

New UX, New Theme, Learned all the whistles and bells..

Now it's June 2022 and we've had quite enough time tinkering with the "beta site" and decided it was time to do a full and final update with the site.    We figured out how the new SW works, and we've made all the UX tailoring and setup choices..   

So, blank slate again (put beta-site on ice and start blank slate)

We re-extracted the database from the legacy site.   The mysqldump is about 10GB, we're a media file heavy site.

Re-created the database on the new HW.  

Ran the utf8 conversion on the fresh new DB on the HW.

$ cd DOC_ROOT/admin/convertutf8
$ /usr/bin/screen -dmS FIX php -f cli.php

That process takes about 2-3 hours given the size of our dataset.

 

 (I left the prefix alone after the UTF-8 conversion because the question the script asked was if I was satisfied with the results, to let the "cli.php" script rename the converted tables.  I left the prefix alone because I couldn't ascertain yet if I was satisfied. It makes no difference what the prefix is as long as the configuration is setup to align.)

So, after the UTF-8 conversion, these changes to conf:

<?php
$INFO['sql_charset'] = 'utf8mb4';
$INFO['sql_utf8mb4'] = true;
# ...
$INFO['sql_database']                   =       'bolter4';
$INFO['sql_driver']                     =       'mySQL';
$INFO['sql_host']                       =       'localhost';
$INFO['sql_pass']                       =       '#########';
$INFO['sql_port']                       =       '';
$INFO['sql_tbl_prefix']                 =       'x_utf_ibf_';
$INFO['sql_user']                       =       'dbusr';
# ...
?>

Then began the upgrade steps in earnest..

Visit http://domain/admin/upgrade

(fill in the essential starting conditions, KEY, and etc.. ..  and off it goes running...)

About 8 hours later it begins to work with a table called "...core_themes"  (the ellipses are just the table prefix)

The error reported by the Upgrade is this:

1812
Tablespace is missing for table bolter4/x_utf_ibf_core_themes.
/DocumentRoot/applications//setup/upg_/queries.json - query #3

 

Interestingly, that path doesn't exist on the file system.  There is no file in the file system called applications/setup/upg_/queries.json -- so I cannot investigate what query (#3?) was involved.

(That would be helpful to know -- where is the SW storing applications/setup/upg_/queries.json  ?  Anyway...)

# find applications -iname '_queries.json'
#

No result. Moving on, to the DB issue:

I checked the database directly.  The Tablespace for the table is missing yet the .ibd file exists.

mysql> check table x_utf_ibf_core_themes;
+-------------------------------+-------+----------+----------------------------------------------------------------+
| Table                         | Op    | Msg_type | Msg_text                                                       |
+-------------------------------+-------+----------+----------------------------------------------------------------+
| bolter4.x_utf_ibf_core_themes | check | Error    | Tablespace is missing for table bolter4/x_utf_ibf_core_themes. |
| bolter4.x_utf_ibf_core_themes | check | Error    | Table 'bolter4.x_utf_ibf_core_themes' doesn't exist            |
| bolter4.x_utf_ibf_core_themes | check | error    | Corrupt                                                        |
+-------------------------------+-------+----------+----------------------------------------------------------------+

 

All of the usual attempts to repair the table failed.   I cannot shake loose the error.  The table cannot be repaired, dropped, altered, etc..   I have not had the fortune to experience this kind of error before with MySQL and I certainly didn't experience it when I imported the same database schema from the legacy site 4 months earlier.

I had no expectation to backup the UTF8 conversion results PRIOR to starting the upgrade.  (We'll get to that in a moment).

This was all disappointing, but things happen.  Dust off and try again, right?

I did re-attempt on clean slate and reproduced the same error on the same table.

Score

InvisionCommunity SW 2,

Me 0

I'm on my third attempt but the difference I'll make is to make a snapshot of the UTF-8 converted database prior to starting the Upgrade.  I would have not expected that the SW during Upgrade would corrupt the freshly built tables after the UTF-8 conversion.

See TL;DR; for Question above.

Thanks.

 

 

 

[Jim M]

When missing a tablespace, usually it happens due to errors in server migrations, disk issues or even administration errors.

As you just performed the UTF8 conversion and you basically have duplicates of your tables in place, are you running out of disk space?

[sibomots]

space?  No we're fine. North of 500GB + unused.

server migration issue?  interesting. the database was exported (mysqldump) and then re-imported on new HW without incident.   mysqldump AFTER UTF-8 conversion, but before Upgrade also successful, without incident.

disk issues?  potentially, but the syslog is clean there.  no mysql errors reporting that nor HW errors logged in the RAID.  I cannot rule out HW errors, but I would be expecting HW errors on sporadic files not the same Logical DB file in two different Databases.

administration errors?  not likely since the tool provided by IP was used prior with success on the beta-site, and the same tool was re-used on the latest drop of the database.

Question is has anyone else seen the error mentioned repeatedly on the same table during upgrade?

The interesting part is it happened twice (reproduced) and occurred on the same table on different new databases.

 

Edited June 14, 2022 by sibomots

[Jim M]

3 minutes ago, sibomots said:

The interesting part is it happened twice (reproduced) and occurred on the same table.

Were the same steps performed with the database restore, use, etc...? If so, it may just be the database restore process. I would suggest starting over with a fresh dump from your production source and ensure the collation match source to new server.

[sibomots]

The table in question is not present in the legacy DB schema.  It's  created by the Upgrade SW.

After UTF-8 conversion from Legacy DB, the table in question does not exist.

The table is created by the Upgrade process, as far as I can tell.

From what I can gather this table "...core_themes" is synthesized by the new (upgrade target) SW.

 

re: Steps

Same steps performed:

bring export.sql from legacy site

create new db  on new site,

import the expot.sql to that DB.

adjust conf settings for new DB name, user, and prefix (if necessary)

run the UTF-8 conversion.

run the Upgrade

hit the error where Tablespace on the ...core_themes table (twice) was missing.

 

 

 

[Jim M]

3 minutes ago, sibomots said:

The table in question is not present in the legacy DB schema.  It's  created by the Upgrade SW.

After UTF-8 conversion from Legacy DB, the table in question does not exist.

The table is created by the Upgrade process, as far as I can tell.

From what I can gather this table "...core_themes" is synthesized by the new (upgrade target) SW.

 

re: Steps

Same steps performed:

bring export.sql from legacy site

create new db  on new site,

import the expot.sql to that DB.

adjust conf settings for new DB name, user, and prefix (if necessary)

run the UTF-8 conversion.

run the Upgrade

hit the error where Tablespace on the ...core_themes table (twice) was missing.

 

 

 

Are you importing to the same database? I would create a new one and try there. MySQL is funky about that sometimes with existing and deleting tables.

[sibomots]

host A   runs Legacy site SW

host B   runs new site SW

Different hosts.  Different systems.

 

1. On host B,   create database foo
2. on DB foo, import the legacy DB
3. New IPS software installed on host B (eg:  /whatever/foo is DocumentRoot)
4. UTF-8 conversion occurs on host B DB foo
5. Alter conf to point to DB foo.
6. Run upgrade (/admin/upgrade) on host B.
7. Hit error with Tablespace missing on that target table.

Repeat:

1. On host B, create database bar
2. on DB bar, import legacy DB
3. New IPS software installed on host (eg:  /whatever/bar is DocumentRoot)
4. UTF-8 conversion occurs on host B DB bar.
5. Alter conf to point to DB bar.
6. Run upgrade (/admin/upgrade) on host B.
7. Hit error with Tablespace missing on that target table.

I hit the same error on the same table at step 7 (...core_themes) -- a table created by the Upgrade process.

 

[sibomots]

What would help debug this is if there was a way to suspend the Upgrade at steps so evaluation of the DB can be made (and even backups of the DB during the upgrade) -- suspended so that careful export/evaluation can be made without simultaneous changes per Upgrade interfere with export/evaluation.  Then let the Upgrade proceed to next milestone, rinse repeat.

Is there a flag to set that causes the Upgrade to prompt before major steps are performed?

The Upgrade appears to be monolithic in terms of start-work-finish, w/o a chance to snapshot the state of the DB safely.

 

Edited June 14, 2022 by sibomots

[Jim M]

See in your output above the you're using MySQL 8. Have you tried with MySQL 5.6?

[sibomots]

The baseline MySQL for 20.04 Fossa is  MySQL 8 I believe.  I'd have to downshift quite a bit to get 5.6 being the baseline.

Perplexed that that beta-site instantiation with same SW version and like-wise export didn't experience this.

I cannot envision that in 4 months some user content was added that would stimulate the Upgrade such that this table would become corrupted during the Upgrade.

I just checked during run #3 that the table is bonafide and stable but I don't know at what point in the Upgrade it will attempt to modify/work on this target table.

 

 

[Jim M]

I have tagged this to a developer to see if they may have any helpful hints for you as you move through this. However, please keep in mind that server support is outside our scope of support, additionally, version 3 is no longer supported (including upgrades). There appears to be something here on the server-side which is causing this so beyond a little help or confirmation of what I said above, we are a little beyond our scope here, I'm afraid.

[sibomots]

Fair.  The request is not to diagnose MySQL/Server issue.

In the error message it refers to a PHP script that is apparently not in the filesystem but apparently part of the Upgrade kit/SW.

Where is it stored?  If I can see what it's trying to do with this table, I can try to resolve the defect.

I am claiming that there is a defect in the Upgrade process (your software) due to the fact that on two different runs, on two different databases, the same error occurs against the same table that is created by the Upgrade process (SW).

Edit -

Another question related to this is -- during an Upgrade, does your PHP/SW script attempt to fetch from the "Invision Community Mothership" updated/current SW and execute it during the Upgrade regardless of the actual static ips_xxx software that was used locally?   Is there a dynamic "fetch latest bits from Invision Community" during an Upgrade even though the Upgrade locally is based on a specific version ips_xxx ?

If no, then I can rule out some new behavior that wasn't seen on the beta-site successful instantiation compared to this attempt four months later.

If yes, then is there a flag to suppress that capability?  I don't know how deeply the Upgrade process attempts to obfuscate the Upgrade by fetching actual code during the process vs. using only PHP/SW that is resident on the host.

 

Edited June 14, 2022 by sibomots

[Stuart Silvester]

It's worth noting that `core_themes` is not a table that exists in older 3.x versions. If you have this table present, you should be safe to drop it prior to upgrading. It may also suggest that your database has other IPS4 specific tables already in it from a previous upgrade attempt.

30 minutes ago, sibomots said:

Another question related to this is -- during an Upgrade, does your PHP/SW script attempt to fetch from the "Invision Community Mothership" updated/current SW and execute it during the Upgrade regardless of the actual static ips_xxx software that was used locally?   Is there a dynamic "fetch latest bits from Invision Community" during an Upgrade even though the Upgrade locally is based on a specific version ips_xxx ?

No

 

2 hours ago, sibomots said:

/DocumentRoot/applications//setup/upg_/queries.json - query #3

I haven't seen that before, it should contain the application and the version number of where the error occurred. 

[sibomots]

I think there was a confusion, let me restate it:

Yes, ...core_themes is not in the legacy DB schema.   It cannot be dropped prior to Upgrade because it doesn't exist prior to Upgrade.

After the UTF-8 conversion takes place, it still doesn't exist.

It is created by the Upgrade process (ips_469)

The point is that the table is created by the Upgrade.  And after it creates it, something happens during the Upgrade that corrupts it.

About 

applications//setup/upg_/queries.json - query #3

Can you point to where it is in the SW kit?  I cannot locate it.  Its not in DocRoot/applications/

 

Edited June 14, 2022 by sibomots

[sibomots]

Well, that's three clean slate runs in a row with the same error on the same table.

 

[Matt]

We have not tested extensively with MySQL 8. Our recommended version is 5.6 (MySQL went from 5.7 to 8). I suspect MySQL 8 is the issue here.

[sibomots]

OK. Well that's good information.   It did not occur to me that MySQL 8 wouldn't have carried forward any bug fixes from 5.6.  I can probably shoe-horn the 5.6 into the system although it will cause some other dependencies on 20.04 Fossa (Ubuntu) to be re-organized (PHP packages, etc..) that are aligned with 5.6.

Digging into the error (1812 from mysql) it appears to be a bug that was patched in the 5.x line.

The Upgrade takes about 12 hours before I hit the error (on the first 3 runs).  On #4 now.  This time running a slightly newer version of the Invision SW.  I also re-exported the legacy DB.   If this run fails I'll revert the mysql to 5.6.  I'll end up probably spinning up a new box since I don't want to affect the other services on the mysql-8 box. 

Thanks for looking into it.

So the advice/nutshell is -

Run MySQL 5.6.

[Jim M]

21 hours ago, sibomots said:

applications//setup/upg_/queries.json - query #3

Can you point to where it is in the SW kit?  I cannot locate it.  Its not in DocRoot/applications/

I would like to take a closer look at why the application is missing here in the path. Could you please provide the following access to your installation:

We would need to look further into this for you, however the access details on file appear to be incorrect or missing. Could you please update these details by visiting your client area, selecting the relevant purchase, then clicking "Review/Update Access Information" under the "Stored Access Information" section. 

We look forward to further assisting you. 

 

[sibomots]

FYI, the latest IPS version checker pre-checklist says this. Emphasis mine.  Might want to express more strongly that "or above" does not mean up to v8.   $0.02

 

• MySQL connection could not be established to perform version check. Make sure your MySQL Server version is 5.5.3 or above (5.6.2 or above recommended).

[Jim M]

Thank you for your feedback.

[sibomots]

Final result.   Nuke from orbit.

Rented new silicon and placed the software an 18.04 Ubuntu baseline.

This recipe worked fine.  Although I will need to upgrade PHP from 7.2 to 7.4 sooner than later.. Otherwise back on track.

# baseline the host reference to packages
apt update

# get apache
apt install apache

# in order to get the recommended version of 5.6 MySQL
add-apt-repository 'deb http://kr.archive.ubuntu.com/ubuntu xenial main' -y
add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty universe' -y
apt-get update
apt-get install mysql-server-5.6
apt-get install mysql-client-5.6

# Useful tools for the system
apt install net-tools

# these are all required by Invision
apt install php

apt install php7.2-xml
apt install php-dom php-gd php-mysqli php-mbstring
apt install php-gd
apt install php-mbstring
apt install php7.2-mysql
apt install php-curl php-zip

# This package lets you run the apache2 process for the server
# under a specific username.  Makes it easier to assign a new user
# to run the server as (not root!) and then allow maintainers to
# access files on the site.  The default user is `www-data` for Apache2
# and we don't want to make that a login user. Nor do we want to
# open the file permissions up for `o+w`.  There's no need for it.
# Just make a user, run the Apache2 instance (virtual host) under that
# user.
apt install libapache2-mpm-itk

 

[teraßyte]

I hit this very same issue yesterday upgrading an old 3.4.9 site. Downgrading MySQL 8 to 5.7 did indeed fix the issue.

Please update the check file to also check the maximum version, not only the minimum. I wasted hours to restore a backup, downgrade mysql, and re-run the upgrader because of it. 😕

[Marc Stridgen]

Does anyone have an example which is actively at this point? I would like to get a ticket created so we can take a look at this

[teraßyte]

4 hours ago, Marc Stridgen said:

Does anyone have an example which is actively at this point? I would like to get a ticket created so we can take a look at this

My client still has a full server snapshot of the pre-upgrade data (on Ubuntu 14.0.4), but it would require to restore it over the current upgraded site and spend time to upgrade again to Ubuntu 20.04 in order to test it. So yeah, it won't happen...

 

In the end, just like @sibomots did, I ended up going with Ubuntu 18.04 (with PHP 7.2 and MySQL 5.7) to get the upgrade done. So, based on the above replies, it looks like you should be able to replicate the issue upgrading from a 3.4.9 database on Ubuntu 20.08 (with PHP 7.4 and MySQL 8).