Upgrade from 3.0.5.4p3 to 3.1.15p2 leaves DB missing table columns

I just did an upgrade (ispconfig_update.sh accepted all defaults) from 3.0.5.4p3 to 3.1.15p2. The process produced no errors. Yet, I am seeing the following errors in the UI (just a couple of examples):

Unknown column 'client.contact_firstname' in 'field list'
Unknown column 'contact_firstname' in 'field list'​

I have tracked down that contact_firstname is a new column in 3.1 and is added in

./install/sql/incremental/upd_0081.sql​

and that my DB currently does not have this column:

# mysqldump -d -u root -p dbispconfig | grep contact_firstname
Enter password:
#​

My update output shows this SQL update was done without error:

Code:

Creating backup of "/usr/local/ispconfig" directory...
Creating backup of "/etc" directory...
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Checking ISPConfig database .. mysqlcheck: [Warning] Using a password on the command line interface can be insecure.
OK
mysql: [Warning] Using a password on the command line interface can be insecure.
Starting incremental database update.
Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_0079.sql
Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_0080.sql
Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_0081.sql
Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_0082.sql
Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_0083.sql
Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_0084.sql
Executing PHP patch file: /tmp/ispconfig3_install/install/patches/upd_0085.php
Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_0085.sql
Executing PHP patch file: /tmp/ispconfig3_install/install/patches/upd_0086.php
Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_0086.sql
Delete obsolete file interface/web/js/mail_domain_dkim.js? (y,n,a,all,none) [y]:

Delete obsolete file interface/web/mail/mail_domain_dkim_create.php? (y,n,a,all,none) [y]:

Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_0087.sql
Executing PHP patch file: /tmp/ispconfig3_install/install/patches/upd_0088.php
Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_0088.sql
Delete obsolete file interface/web/mail/templates/spamfilter_taglevel_edit.htm? (y,n,a,all,none) [y]:

Delete obsolete file interface/web/mail/templates/spamfilter_quarantine_edit.htm? (y,n,a,all,none) [y]:

Delete obsolete file interface/web/mail/templates/spamfilter_other_edit.htm? (y,n,a,all,none) [y]:

Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_dev_collection.sql
Reconfigure Permissions in master database? (yes,no) [no]:

Reconfigure Services? (yes,no,selected) [yes]:

Configuring Postfix
Configuring Dovecot
Configuring Mailman
Configuring Spamassassin
Configuring Amavisd
Configuring Getmail
Configuring Pureftpd
Configuring Apache
Configuring vlogger
Configuring Apps vhost
Configuring Jailkit
Configuring Ubuntu Firewall
Configuring Database
Updating ISPConfig

Reconfigure Crontab? (yes,no) [yes]:

Updating Crontab
Restarting services ...
Update finished.

My question is: should I just add the column manually and move on OR is there a better way to ensure my DB gets fully updated?

 

Root login is correct. When adding the column I get this:

mysql> ALTER TABLE `client` ADD `contact_firstname` VARCHAR( 64 ) NOT NULL DEFAULT '' AFTER `gender`;
ERROR 1067 (42000): Invalid default value for 'added_date'​

Googling this, it appears that mysql 5.7 now uses by default 'strict mode' and parameters such as NO_ZERO_DATE do not allow entering date values like ‘0000-00-00’ into the database. I assume added_date is an implicit field when adding a new column. Now I could turn this off or try to fix the ALTER, recommendations?
Also why wouldn't this have caused an error when running upd_0081.sql?

​

 

So this is wrong?
`added_date` date NOT NULL DEFAULT '0000-00-00',
but it should be this?
`added_date` date NULL DEFAULT NULL,

There is this exact same field in web_domain should I change it as well?

 

YEah I figured it was the big jump in versions.
No joy changing it. Looks like I need to modify some startup params, too. Specifically removing NO_ZERO_IN_DATE and NO_ZERO_DATE. Agree?

Code:

mysql>  ALTER TABLE  `client` MODIFY `added_date` date NULL DEFAULT NULL;
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'added_date' at row 1
mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

Looking better, but there may be one last issue lurking....
I set the sql_mode as you described and was able to MODIFY added_date and ADD client_firstname successfully. I then re-sourced the updates from 79 to 88. All the errors looked like dups except for 3. There were 2 in 81 and 1in 85. The one in 85 was easy, it was a MODIFY and my DB still didn't have that field so I ADD-ed by hand according to the info in 85.
The 2 in 81 were identical:

Code:

mysql> ALTER TABLE `openvz_traffic` CHANGE `traffic_date` `traffic_date` DATE NULL DEFAULT NULL;
ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
mysql> ALTER TABLE `web_traffic` CHANGE `traffic_date` `traffic_date` DATE NULL DEFAULT NULL;
ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
mysql>

Any ideas?

 

After a little research, I could drop the PRIMARY constraint on these two, but I don't know if the code depends on them being unique. Could drop the PRIMARY constraint and add the UNIQUE constraint which would allow a single NULL at any given time.
Thoughts?