Upgrade 2021.02.2 failed at setup.php

[cvantum]

Hello,

we are running Tine20 on a Debian 10 Server. Today morning we updated to the new version '2021.02.2' via apt. The apt-
process ran without any issues. The upgrade via setup.php afterwards was leading to an error:

php /usr/share/tine20/setup.php --config /etc/tine20/config.inc.php --update

PDOException: 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 in /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
#2 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ti...', Array)
#4 /usr/share/tine20/Setup/Backend/Abstract.php(207): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ti...', Array)
#5 /usr/share/tine20/Setup/Backend/Mysql.php(234): Setup_Backend_Abstract->execQueryVoid('ALTER TABLE `ti...')
#6 /usr/share/tine20/Tinebase/Setup/Update/14.php(72): Setup_Backend_Mysql->addCol('accounts', Object(Setup_Backend_Schema_Field_Xml))
#7 /usr/share/tine20/Setup/Controller.php(556): Tinebase_Setup_Update_14->update003()
#8 /usr/share/tine20/Setup/Frontend/Cli.php(479): Setup_Controller->updateApplications()
#9 /usr/share/tine20/Setup/Frontend/Cli.php(96): Setup_Frontend_Cli->_update(Object(Zend_Console_Getopt))
#10 /usr/share/tine20/Setup/Server/Cli.php(149): Setup_Frontend_Cli->handle(Object(Zend_Console_Getopt))
#11 /usr/share/tine20/Setup/Core.php(117): Setup_Server_Cli->handle()
#12 /usr/share/tine20/setup.php(14): Setup_Core::dispatchRequest()
#13 {main}

Next Zend_Db_Statement_Exception: 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, query was: ALTER TABLE `tine20_accounts` ADD COLUMN   `mfa_configs` text(65535) in /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ti...', Array)
#3 /usr/share/tine20/Setup/Backend/Abstract.php(207): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ti...', Array)
#4 /usr/share/tine20/Setup/Backend/Mysql.php(234): Setup_Backend_Abstract->execQueryVoid('ALTER TABLE `ti...')
#5 /usr/share/tine20/Tinebase/Setup/Update/14.php(72): Setup_Backend_Mysql->addCol('accounts', Object(Setup_Backend_Schema_Field_Xml))
#6 /usr/share/tine20/Setup/Controller.php(556): Tinebase_Setup_Update_14->update003()
#7 /usr/share/tine20/Setup/Frontend/Cli.php(479): Setup_Controller->updateApplications()
#8 /usr/share/tine20/Setup/Frontend/Cli.php(96): Setup_Frontend_Cli->_update(Object(Zend_Console_Getopt))
#9 /usr/share/tine20/Setup/Server/Cli.php(149): Setup_Frontend_Cli->handle(Object(Zend_Console_Getopt))
#10 /usr/share/tine20/Setup/Core.php(117): Setup_Server_Cli->handle()
#11 /usr/share/tine20/setup.php(14): Setup_Core::dispatchRequest()
#12 {main}

The UI is still accessable and also the setup-UI via browser. While trying to update the applications on the browser the same error occur.

[lab-at-nohl]

Hi cvantum,

having the same issue, here. Probably you are updating an old database (not very old, though). Can you tell the output of the following SQL query:

SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE ROW_FORMAT IN('Redundant', 'Compact')
AND NAME NOT IN('SYS_DATAFILES', 'SYS_FOREIGN', 'SYS_FOREIGN_COLS', 'SYS_TABLESPACES', 'SYS_VIRTUAL', 'SYS_ZIP_DICT', 'SYS_ZIP_DICT_COLS')
HAVING NAME LIKE 'TINE_DB_NAME%'

Please replace TINE_DB_NAME by your real Database name for Tine 2.0...

Old tables often have 'Compact', which would be 'DYNAMIC' in newer versions of MariaDB.
I'll go for that and see what happens.

[lab-at-nohl]

For me changing ROW_FORMAT to DYNAMIC solved the issue of
PDOException: 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 in tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228

You can try this but have a backup in place, in case something goes wrong.

1. Follow my first answer an check the ROW_FORMAT in question here...

2. Generate query text that changes your table's ROW_FORMAT in bulk (replace TINE_DB_NAME by your Database name)

SELECT CONCAT('ALTER TABLE `', table_name, '` ROW_FORMAT=DYNAMIC;') AS sqlText FROM information_schema.tables WHERE table_schema = 'TINE_DB_NAME'

The result is text with prepared commands for your database.

3. Actually change your Table's ROW_Format by copy & paste the generated

ALTER TABLE `tine20_accounts` ROW_FORMAT=DYNAMIC;

and so forth.

4. Re-run Update

Unfortunately, I do not know a way to permanently ensure that new tables are of the new ROW_FORMAT. However, it may be default for MariaDB versions after 10.2.

Best
Johannes

[cvantum]

Hello,

thanks for your response. I can confim, that the value of ROW_FORMAT is Compact. Also thanks for the fix :) i will try it as soon as i have a time windows for some outage. Luckily this is our small server.

The really bad side of this issue is, that we also have a BE-Version which could also run into this issue in the future. With way more affected tables:

143 rows in set (0.147 sec)

Maybe devs can find a solution for this problem. At least for the BE-Version there is some time to find a sql-fix for upcoming releases.

[lab-at-nohl]

Maybe devs can find a solution for this problem. At least for the BE-Version there is some time to find a sql-fix for upcoming releases.

Right that's not my expertise, I do not have any experience on BE versions.

[pschuele]

hi, sorry for the late response. here is the upgrade howto for mariaDB (when you run into "Row size too large" errors):

1. set those values in the mariaDB config

innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_large_prefix=1

2. migrate to UTF8MB4 (also sets ROW_FORMAT=DYNAMIC)

$ sudo -u www-data php /usr/share/tine2p/setup.php --config=/etc/tine20 --migrateUtf8mb4

[cvantum]

Hello,

thanks for your help. And i am sorry to open up this issue again. We ran those two tasks on our server. But firing up the migration-script we encountered a new issue:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP FOREIGN KEY `tree_nodes??parent_id??tree_nodes??id`; check that it exists in /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
#2 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ti...', Array)
#4 /usr/share/tine20/Setup/Backend/Abstract.php(207): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ti...', Array)
#5 /usr/share/tine20/Setup/Backend/Abstract.php(460): Setup_Backend_Abstract->execQueryVoid('ALTER TABLE `ti...')
#6 /usr/share/tine20/Setup/Backend/Abstract.php(440): Setup_Backend_Abstract->_dropForeignKey('tree_nodes', 'tree_nodes??par...')
#7 /usr/share/tine20/Setup/Frontend/Cli.php(1279): Setup_Backend_Abstract->dropForeignKey('tree_nodes', 'tree_nodes::par...')
#8 /usr/share/tine20/Setup/Frontend/Cli.php(142): Setup_Frontend_Cli->_migrateUtf8mb4()
#9 /usr/share/tine20/Setup/Server/Cli.php(149): Setup_Frontend_Cli->handle(Object(Zend_Console_Getopt))
#10 /usr/share/tine20/Setup/Core.php(117): Setup_Server_Cli->handle()
#11 /usr/share/tine20/setup.php(14): Setup_Core::dispatchRequest()
#12 {main}

Next Zend_Db_Statement_Exception: SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP FOREIGN KEY `tree_nodes??parent_id??tree_nodes??id`; check that it exists, query was: ALTER TABLE `tine20_tree_nodes` DROP FOREIGN KEY `tree_nodes??parent_id??tree_nodes??id` in /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 /usr/share/tine20/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('ALTER TABLE `ti...', Array)
#3 /usr/share/tine20/Setup/Backend/Abstract.php(207): Zend_Db_Adapter_Pdo_Abstract->query('ALTER TABLE `ti...', Array)
#4 /usr/share/tine20/Setup/Backend/Abstract.php(460): Setup_Backend_Abstract->execQueryVoid('ALTER TABLE `ti...')
#5 /usr/share/tine20/Setup/Backend/Abstract.php(440): Setup_Backend_Abstract->_dropForeignKey('tree_nodes', 'tree_nodes??par...')
#6 /usr/share/tine20/Setup/Frontend/Cli.php(1279): Setup_Backend_Abstract->dropForeignKey('tree_nodes', 'tree_nodes::par...')
#7 /usr/share/tine20/Setup/Frontend/Cli.php(142): Setup_Frontend_Cli->_migrateUtf8mb4()
#8 /usr/share/tine20/Setup/Server/Cli.php(149): Setup_Frontend_Cli->handle(Object(Zend_Console_Getopt))
#9 /usr/share/tine20/Setup/Core.php(117): Setup_Server_Cli->handle()
#10 /usr/share/tine20/setup.php(14): Setup_Core::dispatchRequest()
#11 {main}

If you need any further informations we can post them here too.

Thank you in advance.

[lab-at-nohl]

Have you tried to run the update twice in a row (CLI: php setup.php --update twice)? Does this occur each time or only on first update attempt (which would be a known issue)?

[cvantum]

Hello,

yes. First i updated the config at MariaDB. Restarted the service and looked at systemctl for a successful running-service. I started the upgrade-process with --migrateUtf8mb4 which exited with the given error.

I tried to do an Upgrade with --update but it also exited.

Unfortunately i had to rollback the server becuase with this state i wasn't table to work with it :) If you need any further informations, i will create a duplicate and can redo the steps on a non-productive system.