Hi Marcus, Thanks for the detailed explanation. It’s very helpful.
Few follow up questions: 1. I’ve tried building the docker image with ‘CREATE TABLE’ changes in the appcatalog-mysql.sql and appcatalog-derby.sql in registry/src/main/resources or/and in database-scripts/ but still the schema never changed. Only changes in 01-databases.sql or the release-migration-scripts changed the schema in my case. Where are these files used? Do I need to remove the changes I’ve made in these files? 2.. I’m assuming the folders in the release-migration-scripts represent the mysql version. Is my understanding right? 3. Why do we have almost similar scripts at two places in the codebase? appcatalog_derby.sql and appcatalog_mysql.sql in the folders ‘ide-integration/src/main/resources/database-scripts’ and ‘registry/src/main/resources’? Thanks in advance. Regards, Vivek. From: "Christie, Marcus Aaron" <[email protected]> Reply-To: <[email protected]> Date: Wednesday, June 17, 2020 at 10:22 AM To: Airavata Dev <[email protected]> Subject: Re: GSoC Milestone 1 update Hi Vivek, This is good feedback. I'm working on simplifying the db migrations process, hopefully it will be clearer in the future. In general, the appcatalog-mysql.sql, etc. scripts are executed by Airavata API server when it starts up when the database has no tables. However, the ide-integration docker-compose starts the MySQL database with a full dump of an airavata database (01-databases.sql), so appcatalog-mysql.sql doesn't actually get executed in that case. When a MySQL container is created for the first time by docker-compose, it runs all of the scripts in database_scripts/init (because these are mapped to the volume /docker-entrypoint-initdb.d). As mentioned above, that's the 01-databases.sql script which is db dump and then migrations scripts that are intended to be applied on top of it. I hope that helps. Sincerely, Marcus On Jun 16, 2020, at 6:57 PM, Bandaru, Vivek Shresta <[email protected]<mailto:[email protected]>> wrote: That worked! Thanks Marcus. Earlier, I faced issues with my ‘alter table’ syntax during docker-compose, so I did try building mysql db without the alter table query, and just the updated ‘CREATE TABLE STORAGE_PREFERENCE’ query with the new ‘userStorageQuota’ entry. Since I deleted the mysql image and database-data folder, shouldn’t the new database image contain the ‘userStorageQuota’ entry? The exception I posted earlier was when the code had no alter table query and just the updated ‘CREATE TABLE STORAGE_PREFERENCE’ query. Is my understanding right? Any idea what mistake I might be doing? Most of my changes to the ‘.sql’ scripts were made through trial and error and workspace searches so I would like to get a better understanding on how the database is built. For instance, why do we have almost similar scripts at two places in the codebase? appcatalog_derby.sql and appcatalog_mysql.sql in the folders ‘ide-integration/src/main/resources/database-scripts’ and ‘registry/src/main/resources’? It would be really helpful if you can point me towards some documentation if any. Regards, Vivek. From: "Christie, Marcus Aaron" <[email protected]<mailto:[email protected]>> Reply-To: <[email protected]<mailto:[email protected]>> Date: Tuesday, June 16, 2020 at 5:45 PM To: Airavata Dev <[email protected]<mailto:[email protected]>> Subject: Re: GSoC Milestone 1 update Hi Vivek, https://github.com/apache/airavata/pull/254/files#diff-0d0ad3e35c91564651a24674c1a99411R20 Try removing "IF NOT EXISTS". That syntax works for MariaDB but not MySQL, which the ide-integration docker-compose uses. (An aside, I am updating ide-integration to start up a MariaDB container instead, but that code is still on a branch.) On Jun 14, 2020, at 11:26 PM, Bandaru, Vivek Shresta <[email protected]<mailto:[email protected]>> wrote: I’ve made sure to delete the existing mysql image and ide-integration/database-data folder before building a new mysql image with the new changes, but the schema still won’t change. I encountered the below mentioned exception stacktrace during server startup.
