sibomots Posted June 14, 2022 Posted June 14, 2022 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 Posted June 14, 2022 Posted June 14, 2022 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 Posted June 14, 2022 Author Posted June 14, 2022 (edited) 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 Posted June 14, 2022 Posted June 14, 2022 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 Posted June 14, 2022 Author Posted June 14, 2022 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 Posted June 14, 2022 Posted June 14, 2022 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 Posted June 14, 2022 Author Posted June 14, 2022 host A runs Legacy site SW host B runs new site SW Different hosts. Different systems. On host B, create database foo on DB foo, import the legacy DB New IPS software installed on host B (eg: /whatever/foo is DocumentRoot) UTF-8 conversion occurs on host B DB foo Alter conf to point to DB foo. Run upgrade (/admin/upgrade) on host B. Hit error with Tablespace missing on that target table. Repeat: On host B, create database bar on DB bar, import legacy DB New IPS software installed on host (eg: /whatever/bar is DocumentRoot) UTF-8 conversion occurs on host B DB bar. Alter conf to point to DB bar. Run upgrade (/admin/upgrade) on host B. 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 Posted June 14, 2022 Author Posted June 14, 2022 (edited) 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 Posted June 14, 2022 Posted June 14, 2022 See in your output above the you're using MySQL 8. Have you tried with MySQL 5.6?
sibomots Posted June 14, 2022 Author Posted June 14, 2022 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 Posted June 14, 2022 Posted June 14, 2022 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 Posted June 14, 2022 Author Posted June 14, 2022 (edited) 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 Posted June 14, 2022 Posted June 14, 2022 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 Posted June 14, 2022 Author Posted June 14, 2022 (edited) 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 Posted June 15, 2022 Author Posted June 15, 2022 Well, that's three clean slate runs in a row with the same error on the same table.
Management Matt Posted June 15, 2022 Management Posted June 15, 2022 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. Marc and Jim M 2
sibomots Posted June 15, 2022 Author Posted June 15, 2022 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 Posted June 15, 2022 Posted June 15, 2022 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 Posted June 15, 2022 Author Posted June 15, 2022 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).
sibomots Posted June 19, 2022 Author Posted June 19, 2022 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 Posted June 19, 2022 Posted June 19, 2022 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 Posted June 20, 2022 Posted June 20, 2022 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 Posted June 20, 2022 Posted June 20, 2022 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).
Recommended Posts