[
https://issues.apache.org/jira/browse/AIRAVATA-2938?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16761979#comment-16761979
]
Marcus Christie commented on AIRAVATA-2938:
-------------------------------------------
{noformat}
Ran into an issue where a column change corrupted the InnoDB database
```
MariaDB [profile_service]> ALTER TABLE `USER_PROFILE_PHONE` CHANGE
`AIRAVATA_INTERNAL_USER_ID` `AIRAVATA_INTERNAL_USER_ID` varchar(255) NOT NULL;
ERROR 1025 (HY000): Error on rename of './profile_service/#sql-606_29ed0' to
'./profile_service/USER_PROFILE_PHONE' (errno: 150 "Foreign key constraint is
incorrectly formed")
```
The table is no longer listed by `show tables` but I was no longer able to
recreate the table:
```
MariaDB [profile_service]> CREATE TABLE IF NOT EXISTS USER_PROFILE_PHONE (
-> AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL,
-> PHONE VARCHAR (255) NOT NULL,
-> PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, PHONE ),
-> FOREIGN KEY (AIRAVATA_INTERNAL_USER_ID) REFERENCES
USER_PROFILE(AIRAVATA_INTERNAL_USER_ID) ON DELETE CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ERROR 1005 (HY000): Can't create table `profile_service`.`USER_PROFILE_PHONE`
(errno: 121 "Duplicate key on write or update")
```
`SHOW ENGINE INNODB STATUS` gives me this:
```
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2019-02-06 17:37:21 7fc89f8bc700 Error in foreign key constraint creation for
table `profile_service`.`USER_PROFILE_PHONE`.
A foreign key constraint of name `profile_service`.`USER_PROFILE_PHONE_ibfk_1`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
------------
...
```
So the FK is still there. Finally [what worked was suggestion #1 of this
stackoverflow answer](https://dba.stackexchange.com/a/69664): create the table
without the FK, drop it, then create with the FK:
```
MariaDB [profile_service]> CREATE TABLE IF NOT EXISTS USER_PROFILE_PHONE (
AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL, PHONE VARCHAR (255) NOT
NULL, PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, PHONE ) ) ENGINE=InnoDB
DEFAULT CHARSET=latin1;
MariaDB [profile_service]> drop table USER_PROFILE_PHONE;
MariaDB [profile_service]> CREATE TABLE IF NOT EXISTS USER_PROFILE_PHONE (
AIRAVATA_INTERNAL_USER_ID VARCHAR (255) NOT NULL, PHONE VARCHAR (255) NOT
NULL, PRIMARY KEY (AIRAVATA_INTERNAL_USER_ID, PHONE ), FOREIGN KEY
(AIRAVATA_INTERNAL_USER_ID) REFERENCES USER_PROFILE(AIRAVATA_INTERNAL_USER_ID)
ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
```
Fortunately there was no important data in that table.
{noformat}
> Change DB initialization in Registry server to use registry-refactoring code
> ----------------------------------------------------------------------------
>
> Key: AIRAVATA-2938
> URL: https://issues.apache.org/jira/browse/AIRAVATA-2938
> Project: Airavata
> Issue Type: Bug
> Reporter: Marcus Christie
> Assignee: Marcus Christie
> Priority: Major
> Attachments: app_catalog_diff.sql, credential_store_diff.sql,
> experiment_catalog_diff.sql, profile_service_diff.sql,
> replica_catalog_diff.sql, sharing_catalog_diff.sql
>
>
> TODO
> * [x] Move DB intialization code to airavata-commons. Code in registry-core
> is duplicated in airavata-sharing-registry-server and
> airavata-credential-store.
> ** Don't need a connection pool (JdbcStorage) for initializing a database
> ** Single DBInitializer class that takes a config object with JDBC properties
> etc.
> ** Each database has its own initialization class that can also have a
> postInit method to do any database setup
> * [x] Update distribution bin assembly to include db scripts from
> registry-refactoring
> * [x] Update RegistryAPIServer to call DBInitializers from
> registry-refactoring
> * [x] code cleanup: DatabaseCreator copies
> ** (/) CredentialStoreInitUtil
> ** (/) orchestrator-core Initialize
> ** (/) registry-refactoring: expcatalog Initialize.java
> ** (/) registry-refactoring: replicacatalog Initialize.java
> ** (/) registry-refactoring: workflowcatalog Initialize.java
> ** -registry-core: appcatalog Initailize.java- registry-core is deprecated so
> no need to convert these
> ** -registry-core: expcatalog Initialize.java-
> ** -registry-core: replicacatalog Initialize.java-
> ** (/) registry-server: appcatalog AppCatalogInitUtil
> ** (/) registry-server: DatabaseCreator
> ** (/) registry-server: ExpCatalogInitUtil
> ** (/) registry-server: ReplicaCatalogInitUtil
> ** (/) registry-server: WorkflowCatalogInitUtil
> ** (/) sharing-registry-server: JPAUtils
> ** (/) sharing-registry-server: Initialize
> ** -registry-tool: DBMigrator- not going to touch this now
> ** (/) gfac-service: Initialize
> * [x] code cleanup: DBInitializer copies
> ** (/) profile-service-commons: Utils.java
> ** (/) CredentialStoreInitiUtil
> ** -registry-core: Utils- registry-core is deprecated so no need to convert
> * [x] code cleanup: DerbyUtils.java copies
> ** AiravataDerbyServer
> ** other locations listed in above lists
> * [x] code cleanup: Merge DerbyUtils.java and DerbyDBManager.java
> * [x] run a DB schema diff between a clean database initialized by these
> refactored scripts and a live existing Airavata develop database
> (dbdev.scigap.org)
> * [x] move Derby start code out of DBInitializer?
> * [x] Factor out EntityManagerFactory creation code from AppCatalogJPAUtils
> and use for all databases
> * [x] turn off the feature in OpenJPA where it adds missing columns/tables
> automatically. Instead we'll require DB scripts, migration scripts.
> * [ ] rerun DB schema diff after running migration scripts on live develop
> database
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
