Heroku Deploy: SQL Error in 180704-addNotNullForConfig.sql

Support
#1

I just upgraded to platform 6.10 and tried to deploy to heroku. The application logs show a SQL error in file 180704-addNotNullForConfig.sql. I found this file on my local system and was able to paste the contents into MySQL Workbench and successfully execute them. So I am not sure why there is a syntax error.

Where does this file come from? It is not part of my github repo. This is the contents of the file on my local machine, but I cannot confirm the deployed contents on heroku are the same:

-- add not null constraints for SYS_CONFIG

alter table SYS_CONFIG MODIFY NAME varchar(190) not null;
alter table SYS_CONFIG MODIFY VALUE_ text not null;

Is it possible to exclude this from running? Since I executed the 2 commands manually against the database, they should no longer be needed.

Here is a portion of the log where the error occurs.

2018-10-16T15:55:35.753415+00:00 app[web.1]: 15:55:35.753 INFO  c.h.cuba.core.sys.MetadataImpl          - Metadata initialized in 722 ms
2018-10-16T15:55:36.597927+00:00 app[web.1]: 15:55:36.597 INFO  c.h.c.c.sys.dbupdate.DbUpdaterEngine    - Updating database...
2018-10-16T15:55:37.022843+00:00 app[web.1]: 15:55:37.022 INFO  c.h.c.c.sys.dbupdate.DbUpdaterEngine    - Executing script 10-cuba/update/mysql/18/180704-addNotNullForConfig.sql
2018-10-16T15:55:37.115601+00:00 app[web.1]: 15:55:37.114 ERROR c.h.c.c.s.AbstractWebAppContextLoader   - Error initializing application
2018-10-16T15:55:37.115605+00:00 app[web.1]: java.lang.RuntimeException: 
2018-10-16T15:55:37.115608+00:00 app[web.1]: =================================================
2018-10-16T15:55:37.115609+00:00 app[web.1]: ERROR: Database update failed. See details below.
2018-10-16T15:55:37.115611+00:00 app[web.1]: =================================================
2018-10-16T15:55:37.115612+00:00 app[web.1]: Error executing SQL script 180704-addNotNullForConfig.sql
2018-10-16T15:55:37.115625+00:00 app[web.1]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alter table SYS_CONFIG MODIFY VALUE_ text not null' at line 4
2018-10-16T15:55:37.115628+00:00 app[web.1]: 	at com.haulmont.cuba.core.sys.dbupdate.DbUpdaterEngine.executeSqlScript(DbUpdaterEngine.java:364) ~[cuba-core-6.10.1.jar:6.10.1]
#2

Hi,

In order to exclude a script from the execution, you have to register it in the SYS_DB_CHANGELOG table. For MySQL it should be like this:

INSERT INTO sys_db_changelog (script_name, create_ts, is_init)
VALUES('10-cuba/update/mysql/18/180704-addNotNullForConfig.sql', current_timestamp, 0)
#3

Yes, that worked. Thank you!

#4

It happened again. This time the DB update was from the addition of a new column. This is the script that failed with syntax error:

alter table AJJFTEST_TEMPLATE add column DATA_TYPE varchar(50) ^
update AJJFTEST_TEMPLATE set DATA_TYPE = 'M' where DATA_TYPE is null ;
alter table AJJFTEST_TEMPLATE modify column DATA_TYPE varchar(50) not null ;

I did not capture the logs, but the syntax error was complaining about the 3rd statement trying to set the column to not null. Coincidentally, this was the same action that failed the first time:

Error executing SQL script 180704-addNotNullForConfig.sql
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alter table SYS_CONFIG MODIFY VALUE_ text not null' at line 4

Both times I was able to work around the problem by executing the statements using MySQL Workbench connected to the remote DB, then inserting the SYS_DB_CHANGELOG row to skip processing of that file.

The remote DB is MySQL version 5.6.34.
My local DB engine (which does not have this problem) is MySQL 5.7.18. The other difference is my local updates are performed via CUBA Studio.

I wonder if CUBA is changing a session mode such that alter table...not null is failing when executed by CUBA during the DBUpdater process, but succeeds when I issue the same statements manually?

Any other ideas on why this would continue to happen?