Skip to content
Snippets Groups Projects
README.md 2.85 KiB
Newer Older
Ludovic Cottret's avatar
Ludovic Cottret committed
# Metexplore Test Database
Ludovic Cottret's avatar
Ludovic Cottret committed

LIOTIER MARION's avatar
LIOTIER MARION committed
Mysql dump of the MetExplore test database. The name of the database is `metexplore_test`.
Ludovic Cottret's avatar
Ludovic Cottret committed

Ludovic Cottret's avatar
Ludovic Cottret committed
## How to create it ?
Ludovic Cottret's avatar
Ludovic Cottret committed

Ludovic Cottret's avatar
Ludovic Cottret committed
By command line, not with phpMyAdmin !

The structure of the database :
```sh
mysqldump -u root -p --routines --triggers --no-data --databases metexplore_test --default-character-set utf8mb4  > 1-metexplore_test-defs.sql
lcottret's avatar
lcottret committed

The data (don't forget to remove all the data that are not listed in the "What does it content?" section below) :
```sh
mysqldump -u root -p --no-create-info --skip-triggers --databases metexplore_test --default-character-set utf8mb4  > 2-metexplore_test-data.sql
Ludovic Cottret's avatar
Ludovic Cottret committed
```

**MySQL docker container executes script files from /docker-entrypoint-initdb.d/ folder in the ascending order of file names so the file containing the structure of the database need to start with 1.**

lcottret's avatar
lcottret committed
### Remove sql_mode NO_AUTO_CREATE_USER
Ludovic Cottret's avatar
Ludovic Cottret committed

lcottret's avatar
lcottret committed
The dump can integrate NO_AUTO_CREATE_USER in the sql_mode variable. It's not compatible with the docker mysql version.
LIOTIER MARION's avatar
LIOTIER MARION committed

lcottret's avatar
lcottret committed
Solution: edit the sql file and remove them.
LIOTIER MARION's avatar
LIOTIER MARION committed

lcottret's avatar
lcottret committed
### Remove the first line /*!999999\- enable the sandbox mode */ 
LIOTIER MARION's avatar
LIOTIER MARION committed

lcottret's avatar
lcottret committed
### Test the docker image to be sure that everything is ok
LIOTIER MARION's avatar
LIOTIER MARION committed

You can build the image with this command :
LIOTIER MARION's avatar
LIOTIER MARION committed
```sh
docker build -t test_db .
```

And test it using the image `test_db`.
lcottret's avatar
lcottret committed
If a mysql instance is already runnning on your computer, change the first number
docker run -d -p3306:3306 --name db test_db
docker exec -it db bash
```
Look inside the database with phpMyAdmin :
```sh
docker run --name phpmyadmin -d --link db:db -e PMA_USER=root -e PMA_PASSWORD=pass -p 8081:80 phpmyadmin
```

lcottret's avatar
lcottret committed
If everything is ok, the bash of the container is launched.

If it fails, check the logs:

```sh
docker logs db
```
lcottret's avatar
lcottret committed
**Don't forget to update `VERSION` in `.gitlab-ci.yml`.**
lcottret's avatar
lcottret committed


## Push the docker image in the registry

The docker image is pushed with gitlab CICD pipeline.

lcottret's avatar
lcottret committed
## How to import it ?

You can use the image `registry.forgemia.inra.fr/metexplore/metexplore-database:latest`
or build the database from the sql file:
lcottret's avatar
lcottret committed
```bash
mysql -u root -p < metexplore_test.sql
```
lcottret's avatar
lcottret committed

lcottret's avatar
lcottret committed
## What does it content?

- Three databaseRefs with 3 BioSources :
  - the iaf1260 coli model
  - the BioCyc 18.0 coli model
  - a small toy model 
- Three users:
  - admin
  - usertest1
  - usertest2

LIOTIER MARION's avatar
LIOTIER MARION committed
# Usual bugs

## default values for dates

To have `CURRENT_TIMESTAMP` as the default value for a date, the type of the column must be `datetime` and not `date`.

```sql
'date' datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
```
lcottret's avatar
lcottret committed

## No default value for TEXT, BLOB and JSON mode

These types are not allowed to have default versions in this mysql version.

Solution: remove the default values in the table and redo a mysql dump.
lcottret's avatar
lcottret committed

## collation not recognized when importing the database

Exemple : ERROR 1273 (HY000) at line 2900: Unknown collation: 'utf8mb4_0900_ai_ci'

Solution : replace utf8mb4_0900_ai_ci by utf8mb4_bin