airavata-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marcus Christie (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (AIRAVATA-2938) Change DB initialization in Registry server to use registry-refactoring code
Date Wed, 06 Feb 2019 17:55:00 GMT

    [ 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)

Mime
View raw message