README.md

# Metexplore Test Database

Mysql dump of the MetExplore test database. The name of the database is `metexplore_test`.

## How to create it ?

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
```

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
```

**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.**

### Remove sql_mode NO_AUTO_CREATE_USER

The dump can integrate NO_AUTO_CREATE_USER in the sql_mode variable. It's not compatible with the docker mysql version.

Solution: edit the sql file and remove them.

### Remove the first line /*!999999\- enable the sandbox mode */ 

### Test the docker image to be sure that everything is ok

You can build the image with this command :

```sh
docker build -t test_db .
```

And test it using the image `test_db`.
If a mysql instance is already runnning on your computer, change the first number
```sh
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
```

If everything is ok, the bash of the container is launched.

If it fails, check the logs:

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


## Push the docker image in the registry

The docker image is pushed with gitlab CICD pipeline.

## 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:


```bash
mysql -u root -p < metexplore_test.sql
```

## 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

# 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
```

## 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.

## 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