Hello, I've been using ispconfig for years, thank you for the service! So yesterday I've updated my ispconfig from 3.1 to 3.2. Before the update everything was working but after it some things broke (some columns were missing in database, I recreated them manually). But the only thing that I couldnt fix was the mail's database. I've tried creating it again but I was not successful. I can send mails but I can't receive because one column is missing. Server: Ubuntu 18.04 /var/log/mail.log Code: Oct 21 17:18:46 server1 postfix/smtpd[6421]: connect from mail-ed1-f49.google.com[209.85.208.49] Oct 21 17:18:47 server1 postfix/smtpd[6421]: NOQUEUE: filter: RCPT from mail-ed1-f49.google.com[209.85.208.49]: <[email protected]>: Sender address triggers FILTER lmtp:[127.0.0.1]:10026; from=<[email protected]> to=<[email protected]> proto=ESMTP helo=<mail-ed1-f49.google.com> Oct 21 17:18:47 server1 postfix/smtpd[6421]: NOQUEUE: filter: RCPT from mail-ed1-f49.google.com[209.85.208.49]: <[email protected]>: Sender address triggers FILTER lmtp:[127.0.0.1]:10024; from=<[email protected]> to=<[email protected]> proto=ESMTP helo=<mail-ed1-f49.google.com> Oct 21 17:18:47 server1 dovecot: auth-worker(6436): Warning: mysql: Query failed, retrying: Unknown column 'disablequota-status' in 'where clause' Oct 21 17:18:47 server1 postfix/smtpd[6421]: NOQUEUE: reject: RCPT from mail-ed1-f49.google.com[209.85.208.49]: 450 4.7.1 <[email protected]>: Recipient address rejected: Internal error occurred. Refer to server log for more information.; from=<[email protected]> to=<[email protected]> proto=ESMTP helo=<mail-ed1-f49.google.com> Oct 21 17:18:47 server1 dovecot: auth-worker(6436): Error: sql([email protected]): User query failed: Unknown column 'disablequota-status' in 'where clause' Oct 21 17:18:47 server1 dovecot: quota-status([email protected]): Error: user [email protected]: Auth USER lookup failed Oct 21 17:18:47 server1 postfix/smtpd[6421]: disconnect from mail-ed1-f49.google.com[209.85.208.49] ehlo=2 starttls=1 mail=1 rcpt=0/1 data=0/1 quit=1 commands=5/7 debug script Code: ##### SERVER ##### IP-address (as per hostname): ***.***.***.*** [WARN] could not determine server's ip address by ifconfig [INFO] OS version is Ubuntu 18.04.5 LTS [INFO] ISPConfig is installed. ##### ISPCONFIG ##### ISPConfig version is 3.2 ##### VERSION CHECK ##### [INFO] php (cli) version is 7.4.11 ##### PORT CHECK ##### ##### MAIL SERVER CHECK ##### ##### RUNNING SERVER PROCESSES ##### [INFO] I found the following web server(s): Apache 2 (PID 6294) [INFO] I found the following mail server(s): Postfix (PID 6082) [INFO] I found the following pop3 server(s): Dovecot (PID 6191) [INFO] I found the following imap server(s): Dovecot (PID 6191) [INFO] I found the following ftp server(s): PureFTP (PID 6357) ##### LISTENING PORTS ##### (only () Local (Address) [anywhere]:110 (6191/dovecot) [anywhere]:143 (6191/dovecot) [anywhere]:465 (6082/master) ***.***.***.***:53 (6365/named) [localhost]:53 (6365/named) [anywhere]:21 (6357/pure-ftpd) ***.***.***.***:53 (833/systemd-resolve) [localhost]:9014 (4073/php-fpm:) [anywhere]:22 (1667/sshd) [localhost]:953 (6365/named) [anywhere]:25 (6082/master) [localhost]:9017 (4073/php-fpm:) [localhost]:9018 (4073/php-fpm:) [anywhere]:993 (6191/dovecot) [anywhere]:995 (6191/dovecot) ***.***.***.***:27015 (29775/./srcds_linux) [localhost]:10023 (1866/postgrey) [localhost]:10024 (6155/amavisd-new) [localhost]:10025 (6082/master) [localhost]:10026 (6155/amavisd-new) [localhost]:10027 (6082/master) [anywhere]:587 (6082/master) [localhost]:11211 (1114/memcached) [localhost]10 (6191/dovecot) - If you need anything else please let me know. Thank you for your help!
You should not add columns manually, run the update again instead, so the installer creates them with the correct properties.
The sql updates won't be applied again when you run update.php unless you change the server.dbversion field to a lower number. Alternately, you can run through the 0089 and 0090 sql update files and paste those into a mysql session and it should fix it, I had to do that on a couple 3.1dev->3.2 boxes.
I've tried that but as Jesse said it didnt update. Thank your very much!! I have already tried running 0090 but it was not fixed so I though problem was smh different - tried fixing for hours! But running 0089 fixed the problem. Thank you both again, have a great day!
@irfantopal do you know what version of 3.1 you had previously? I assumed both my cases were from my local actions, where I had 3.1dev of various timeframes, as well as testing my own patches/features where I ended up missing some updates. I've not heard of anyone else with the issue on a 3.2 update until now, either, so hopefully it's similar on your end (ie. not a standard 3.1.15p3 release to 3.2 release update).
Curious. There have been several reports of this, but it doesn't seem to affect everyone. @Croydon any thoughts?
Could it be a deprecated php function which is used when running the sql files? It might be glitched in certain php versions that's why it would not affect everyone.
I logged https://git.ispconfig.org/ispconfig/ispconfig3/-/issues/5870 to try to collect details, anyone feel free to add OS/ISPConfig/php version info, and other clues.
@Jesse Norell I think I got the solution. I checked some logs and codes and I just saw on my first update from 3.1 to 3.2 there was sql error ( Row size too large ...). Then when I was manually trying to insert it I became the same error. I fixed it and after that I could insert the rows manually, so I executed 90 and I was succesful but my problem wasnt solved (didnt execute 89) so I wrote to here and you solved my problem by saying executing 89 (I changed the db version from db and executed the update script). Fixing that sql error is simple. Code: ALTER TABLE web_domain ROW_FORMAT=DYNAMIC; The problem is that not everyone has the same row format so that causes the problems. Maybe change row format when installing/updating ispconfig. Edit: Before executing this code the dbs weren't succesfully executing, but after executing this fixes the problem.
Thanks for the tip, @irfantopal, that could certainly be part of the issue, though in checking the two servers where I had incomplete sql updates, the row format is already dynamic, so I think this exact scenario didn't apply to me. But I'm willing to ignore my upgrade path as unusual, if it applies to everyone else. Or maybe there are multiple things going on, with the same outcome.
Oh okay, that was the issue for me. The sql files did get executed but the db wasnt dynamic so it failed.
