Update Issue with Database fields

[Luthersites]

When I try to do the update I get the following error:

Unable to complete incremental updates: Unable to import the 7.8.0 Alpha1 database file. Unable to import /home/lutherh/public_html/resources/sql/upgrade780alpha1.sql: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

I can't find anything on your site that talks about this error, and I find it odd that the database has incorrectly set column types.  Please advise as to what I need to do.

[WHMCS John]

Hi @Luthersites,

There are two fields we create as BLOB type in /resources/sql/upgrade780alpha1.sql:

• tblbankaccts.bank_data
• tblcreditcards.card_data

If you run the SQL command from within this file to create the tables directly via MySQL or phpmyadmin, do you encounter the same error?

With both tables created manually, are you able to complete the update process?

 

[Luthersites]

When I attempt to run the entire sql script from upgrade780alpha1.sql in phpmyadmin, I get this error:

#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

BOTH of the fields (tblbankaccts.bank_data and tblcreditcards.card_data) are set to blob.

This is a MariaDB, if that helps you, version 10.3.17

[WHMCS Edward]

Can you try running each of the SQL update statements individually in order to see which one specifically is triggering this error?

-Ed

[Luthersites]

that's exactly what I did, and ultimately i was able to get it to work.  The following query was the one causing the issue:

-- Add the attachments_removed column to tbltickets
set @query = if ((select count(*) from information_schema.columns where table_schema=database() and table_name='tbltickets' and column_name='attachments_removed') = 0, 'alter table `tbltickets` add `attachments_removed` tinyint(1) NOT NULL DEFAULT \'0\' AFTER `attachment`', 'DO 0');
prepare statement from @query;
execute statement;
deallocate prepare statement;

The only way to get it to succeed was if I did NOT move the 'attachments_removed' field to before the 'attachment' field but just left it at the end of the table.  Doing this, everything updated just fine.

[WHMCS Edward]

Glad you were able to find a workaround. Please let us know if you run into any further issues.

-Ed