You are here
Dan - Thu, 2020/12/03 - 22:50
I have narrowed this down to being a MariaDB/MySQL issue... I think TKLBAM is doing its job but when the restore runs and it tries to use the MySQL data dump I am getting this error:
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
The question is, how should I go about resolving it ?
Forum:
That sucks... :(
TBH, following the number of successful migrations reported by other users, I would have expected coming from v15.x to v16.x to have "just worked". But it seems that's not the case with your server... :(
I've done a bit of research on the error you noted, and it appears that the particular error is not uncommon when moving from an older version of MySQL/MariaDB to a newer version. It appears that the DB design and layout of your web app may be sub-optimal. There seem to be a ton of potential workarounds but without knowing more about your data, it's hard to guess which might be the most appropriate. FWIW, here's a ton of links which appear to be relevant (in no particular order):
(I'm not necessarily recommending that you follow any/all of the advice of those, just posting them here for future reference).
So one path forward may be to tweak the data in your existing database and then create a new backup and retry the restore on the new server. Although the fact that it's not clearly stating which particular table or row is causing the issue makes it hard, plus it'll be a bit of a trial and error, back and forth process which might get frustrating (you won't know for sure whether the issue is fixed or not until you try the restore on the new server).
Another option might be to go back to what you were originally intending for now (I.e. leave your existing server be and update to 3rd party PHP if you really need a newer version). You will need to migrate eventually, and I'd argue that if you have the time and energy right now, now is as good a time as any. However, it's also worth noting, that v15.x is based on Debian Stretch/9 which is supported via LTS until mid 2022 (and will likely go into ELTS after that). Not all packages are supported, but as a headless server, I suspect everything you have installed should be (check more details).
Although, seeing as you've already got this far, I would at least recommend trying to just restore the DB with the default v16.x MySQL/MariaDB config.
As I mentioned to you via our support system, when you hit issues, the best plan is to use the commandline. So if you aren't already logged in via SSH, then please do so. On Linux or Mac OSX you can open your terminal app and just use the built-in native ssh command. I.e.:
(Where YOUR_SERVER_IP is your actual server IP).
If you are using Windows, then you can either install OpenSSH and use it from a powershell terminal (essentially as per above). Or use a third party tool such as PuTTY.
So once logged in via SSH, the first thing I would do would be to rollback the failed restore (that may not be required, but just in case). I.e. try this:
Then let's try again, but this time, we'll do a staged restore and limit what we restore to what should be required for your LAMP website/application. Also, if things go pear-shaped, you can always just trash this server and create a new one. Although to speed up the process, another option for if things go wrong would be to do a backup of the original clean server (it'll be tiny and should be pretty quick).
So IMO the best way to limit what we restore is to split the process up into steps and go through it bit by bit. So here's how to just download the backup, then restore what I think should be the minimum requirements.:
To explain those steps, the first line is to create the dump directory. Then the tklbam-restore lines; first download the backup. Then restore packages and just the files contained within the /var/www & /etc/apache2 directories (I'm assuming that you may have customised your Apache config and your web app is within /var/www - if it's somewhere else, swap /var/www for the location, e.g. /opt). Finally, restore the DB (skipping the files and packages).
Note that you can poke around in /tklbam-dump and you will find all the other backed up files (relative to '/').
Hopefully restoring in the process that I detailed above, might work around the issue you hit. Fingers crossed...
Also FWIW there's some notes on the MariaDB site
On MariaDB website:
Semper Fi: Always Faithful Mr. Davis
Thank you once again for your detailed and thorough response.
I too am hoping to enjoy the bliss of easy migration, backup and restore with TKLBAM... so its worth getting to the bottom of this issue to make that happen.
To start with, the MySQL restore portion failed very early on as I don't have ANY of my databases in the migrated version. I can see that, if the restore runs in the same order as the backup, the very first database it picked was full of deprecated tables that are not required data.... so perhaps my first order of business will be to clean up my database a bit.
I suppose the other option would be to list which DB's I want the backup to skip... from what I read it will still create the table structure, but not the data.... so Im not sure if it would actually help me as I think the restore error is related to the table structure itself as you pointed out and as many of those sources alluded to.
I am going to work on that and circle back later. Im more than happy to keep this info in the forums to possibly guide future users of TKL if they happen across similar issues.
Great thanks Dan
Cleaning up your databases (on the original host) may be a good idea. Although another option (rather than explicitly removing them) would be to exclude them from the backup. You can do that via the TKLBAM overrides. You can either do it permanently (via adding one per line to the /etc/tklbam/overrides file). Or temporarily when issuing the 'tklbam-backup command. The format is '-mysql:DB_NAME' i.e. a minus sign/dash ('-') followed by "mysql:", then the database name.
E.g. to permanently exclude a database named "bad_database" add this line to /etc/tklbam/overrides (you can do that within a SSH session using the pre-installed 'nano' commandline text editor):
(Additional overrides go on separate lines).
Or temporarily:
(Additional overides can be added after the abvove, separate by a space).
For more info, see the tklbam-backup man page.
Alternatively, you could explicitly just note the databases that you wish to restore (and don't note the ones you wish to ignore). Note that your DB users, their permissions and passwords are stored in the mysql.users table, so you'll likely want to restore at least that table too. The limitations of what is restored, is more-or-less like the overrides, but are referred to as "limits". You can see I used the '--limits' switch in my previous post to limit the files that were restored. That same command could also include databases (and the mysql.users table). So instead of the code that I noted in my previous post, you could instead use this (to restore the /var/www /etc/apache2 directories, plus "database1", "database2" and the "mysql.users" table):
Hopefully that all makes sense.
Add new comment