I have upgraded my multi-server installation from ubuntu jammy to noble and also upgraded ispconfig from 3.2.12 to 3.3.0p1. It worked all fine, but after a "resync" of some services, I get a lot of replication error ( Replication of datalog_id: 296077 failed. Error: (client) in MySQL server: (localhost) Unknown column 'limit_database_postgresql) After digging into that issue I see that on my mail server the "client" table doesn't have the above column. After trying to add it manually i get an "Row size too large" error. So I took a mysqldump of the schema from mail server and compare it with the schema of the webserver (all servers have always been upgraded at the same time to the next ispconfig version). I see that the mail server is using InnoDB, whereas the web server is using MyISAM as mysql engine. Also the "main" ispconfig server is using "MyISAM" and my dns server too. When was the db engine changed? why didn't the mail server also upgrade the engine? How shall i fix the engine manually and add the missing columns? Thanks! KoS
@till any suggestion how to proceed to resolve this DB schema problem? Due to the replication issue the mail server does not get any changes executed from the main DB. (and I get from the cron job every minute 3 emails ;-))
@till *ping* please help me solve this issue. I see that fresh installations of ISPconfig use InnoDB as engine. So I am a bit puzzled, should the other servers have at some point in time have their engine changed in all the tables? why do the columns do not fit into the InnoDB table whereas on a new installation it works fine?
I don't know why they don't fit into your InnoDB table. ISPConfig switched to InnoDB quite some time ago. Also, if all your servers were installed simultaneously, they should use the same engine. I don't remember that we converted old systems, but might be that there was such an update in the past. For ISPConfig, the database engine does not really matter as we neither use MyIsam nor InnoDB specific features. Maybe this helps you with converting the database tables:
i compared my client table schema on the mail server with a vanilla installation of ispconfig and they are exactly the same, only the single column for limit_database_postgresql is missing, see: but if I try to add the column on the mail server manually it throws an error: Code: MariaDB [dbispconfig]> ALTER TABLE client -> ADD COLUMN `limit_database_postgresql` int(11) NOT NULL DEFAULT -1 -> AFTER limit_database; ERROR 1118 (42000): 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 MariaDB [dbispconfig]> How is it possible that the Row size is too large on the mail server but not on a fresh installation?
Thanks @till i found a solution. run optimize table and afterwards i could alter the table and add the missing column and the replication worked fine, everything up and running fine. I'll run an optimize on all servers and tables I compared all the other tables and there are no missing columns, but some with different default values or lengths, e.g.: I assume there is no automated way to fix this differences? They should have been catched by the upgrade procedures/schema migrations?
No. The same schema updates are run on all nodes, they are in the install/sql/incremental/ folder of the ISPConfig tar.gz file, but there is no such thing as an overall comparison and update.
