[BUG] upgrade 1.5.5 -> 1.5.6 SQL Errors

[celevra]

What happened?

upgrade docker compose from 1.5.5 to 1.5.6

tried to go back to 1.5.5 but now i get there also DB errors

How to reproduce?

update ;-)

Configuration file(s) (yaml or .env)

No response

Relevant log output

bunker-bw-scheduler-1  | sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`db`.`bw_custom_configs`, CONSTRAINT `bw_custom_configs_ibfk_1` FOREIGN KEY (`service_id`) REFERENCES `bw_services` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)')
bunker-bw-scheduler-1  | [SQL: INSERT INTO bw_custom_configs (service_id, type, name, data, checksum, method) VALUES (%(service_id__0)s, %(type__0)s, %(name__0)s, %(data__0)s, %(checksum__0)s, %(method__0)s), (%(service_id__1)s, %(type__1)s, %(name__1)s, %(data__1)s, %(checksum__1)s, %(method__1)s), (%(service_id__2)s, %(type__2)s, %(name__2)s, %(data__2)s, %(checksum__2)s, %(method__2)s) RETURNING bw_custom_configs.id, bw_custom_configs.id AS id__1]
bunker-bw-scheduler-1  | [parameters: {'name__0': 'disable_rules', 'method__0': 'manual', 'type__0': 'modsec', 'checksum__0': '9922ea4a62c07347aa2fabc21830c2958e5cc5585c3085b65614dfe91d190c47', 'service_id__0': 'pass.zeitschel.net', 'data__0': b'SecRuleRemoveById 920120\nSecRuleRemoveById 200002\nSecRuleRemoveById 920450\n', 'name__1': 'disable_rules', 'method__1': 'manual', 'type__1': 'modsec', 'checksum__1': 'ce517e94c91808c4f807c0f5abc91fecb690ee43f27a5c6936a7bbda57c261d0', 'service_id__1': 'wlb01.srvz.it', 'data__1': b'SecRuleRemoveById 932235\nSecRuleRemoveById 932260\nSecRuleRemoveById 941130', 'name__2': 'disable_rules', 'method__2': 'manual', 'type__2': 'modsec', 'checksum__2': 'b24c09d683c3739d6963bb199f2300494f210ddcec068ca23a7b12d59bec59f8', 'service_id__2': 'www.zeitschel.net', 'data__2': b'#SecRuleRemoveById 900240\n\nSecAction \\\n    "id:900240,\\\n    phase:1,\\\n    pass,\\\n    t:none,\\\n    nolog,\\\n    setvar:\'tx.restricted_e ... (198 characters truncated) ... g/ .mdb/ .old/ .pass/ .pdb/ .pol/ .printer/ .pwd/ .rdb/ .resources/ .resx/ .sql/ .swp/ .sys/ .vb/ .vbs/ .vbproj/ .vsdisco/ .webinfo/ .xsd/ .xsx/\'"\n'}]

bunker-bw-scheduler-1  | sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1364, "Field 'pro_services' doesn't have a default value")
bunker-bw-scheduler-1  | [SQL: INSERT INTO bw_metadata (id, is_initialized, first_config_saved, autoconf_loaded, scheduler_first_start, custom_configs_changed, external_plugins_changed, config_changed, instances_changed, integration, version) VALUES (%(id)s, %(is_initialized)s, %(first_config_saved)s, %(autoconf_loaded)s, %(scheduler_first_start)s, %(custom_configs_changed)s, %(external_plugins_changed)s, %(config_changed)s, %(instances_changed)s, %(integration)s, %(version)s)]

bunker-bw-scheduler-1  | sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1364, "Field 'is_pro' doesn't have a default value")
bunker-bw-scheduler-1  | [SQL: INSERT INTO bw_metadata (id, is_initialized, first_config_saved, autoconf_loaded, scheduler_first_start, custom_configs_changed, external_plugins_changed, config_changed, instances_changed, integration, version) VALUES (%(id)s, %(is_initialized)s, %(first_config_saved)s, %(autoconf_loaded)s, %(scheduler_first_start)s, %(custom_configs_changed)s, %(external_plugins_changed)s, %(config_changed)s, %(instances_changed)s, %(integration)s, %(version)s)]
bunker-bw-scheduler-1  | [parameters: {'id': 1, 'is_initialized': 1, 'first_config_saved': 0, 'autoconf_loaded': 0, 'scheduler_first_start': 1, 'custom_configs_changed': 0, 'external_plugins_changed': 0, 'config_changed': 0, 'instances_changed': 0, 'integration': 'Docker', 'version': '1.5.5'}]

### BunkerWeb version

1.5.6

### What integration are you using?

Docker

### Linux distribution (if applicable)

_No response_

### Removed private data

- [X] I have removed all private data from the configuration file and the logs

### Code of Conduct

- [X] I agree to follow this project's Code of Conduct

[fl0ppy-d1sk]

Thanks @celevra,

We will try to reproduce and we will push a hotfix soon.

[celevra]

all my data from mounted bw-data is also gone, all custom modsec rules and so on...

here is my compose.yaml

version: "3.5"

services:
  bunkerweb:
    image: bunkerity/bunkerweb:1.5.5
    ports:
      - 80:8080
      - 443:8443
    volumes:
      - ./bw-data/plugins:/etc/bunkerweb/plugins
      - ./errors:/errors
    labels:
      - "bunkerweb.INSTANCE=yes"
    environment:
      - SERVER_NAME=
      - MULTISITE=yes
      - API_WHITELIST_IP=127.0.0.0/8 10.20.30.0/24
      - UI_HOST=http://bw-ui:7000 # Change it if needed
      - USE_WEBHOOK=yes
      - WEBHOOK_URL=https://xxx/bw
      - USE_CROWDSEC=yes
      - CROWDSEC_API=http://xxx:8181
      - CROWDSEC_API_KEY=xxx
      - CROWDSEC_REQUEST_TIMEOUT=3000
      - CROWDSEC_CACHE_EXPIRATION=30000
      - USE_CLAMAV=yes
      - CLAMAV_HOST=clamav
      - USE_MODSECURITY=yes # We don't need modsecurity anymore
      - USE_CORAZA=no
      - CORAZA_API=http://bw-coraza:8080
    networks:
      - bw-universe
      - bw-services
    logging:
      driver: syslog
      options:
        syslog-address: "udp://10.20.30.254:514"

  bw-scheduler:
    image: bunkerity/bunkerweb-scheduler:1.5.5
    volumes:
      - ./bw-data:/data
    depends_on:
      - bunkerweb
      - bw-docker
    environment:
      - DATABASE_URI=mariadb+pymysql://bunkerweb:xxx@bw-db:3306/db # Remember to set a stronger password for the database
      - DOCKER_HOST=tcp://bw-docker:2375
      #- EXTERNAL_PLUGIN_URLS=https://github.com/bunkerity/bunkerweb-plugins/archive/refs/tags/v1.2.zip
    networks:
      - bw-universe
      - bw-docker

  bw-docker:
    image: tecnativa/docker-socket-proxy:nightly
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock:ro
    environment:
      - CONTAINERS=1
      - LOG_LEVEL=warning
    networks:
      - bw-docker

  bw-coraza:
    image: bunkerity/bunkerweb-coraza:latest
    networks:
      - bw-universe

  clamav:
    image: clamav/clamav:1.2
    volumes:
      - ./clamav-data:/var/lib/clamav
    networks:
      - bw-universe

  syslog:
    image: balabit/syslog-ng:4.4.0
    volumes:
      - ./syslog-ng.conf:/etc/syslog-ng/syslog-ng.conf
      - ./logs:/var/log
    networks:
      bw-universe:
        ipv4_address: 10.20.30.254

  bw-ui:
    image: bunkerity/bunkerweb-ui:1.5.5
    depends_on:
      - bw-docker
    environment:
      - DATABASE_URI=mariadb+pymysql://bunkerweb:xxx@bw-db:3306/db # Remember to set a stronger password for the database
      - DOCKER_HOST=tcp://bw-docker:2375
    networks:
      - bw-universe
      - bw-docker

  bw-db:
    image: mariadb:10.10
    environment:
      - MYSQL_RANDOM_ROOT_PASSWORD=yes
      - MYSQL_DATABASE=db
      - MYSQL_USER=bunkerweb
      - MYSQL_PASSWORD=xxx # Remember to set a stronger password for the database
    volumes:
      - ./mysql:/var/lib/mysql
    networks:
      - bw-docker

networks:
  bw-universe:
    name: bw-universe
    ipam:
      driver: default
      config:
        - subnet: 10.20.30.0/24
  bw-services:
    name: bw-services
  bw-docker:
    name: bw-docker

not your fault, but the system was relative new so we have no backup, couldn't think of that there is an error that deletes the bw-data Folder....

[fl0ppy-d1sk]

Hello @celevra,

Maybe config is not gone and still in your database.

Can you try to run your mysql container only, and connect using mysql -u bunkerweb -pxxx db then show tables; and select * from bw_custom_configs;

We are trying to reproduce your problem and will come back to you.

[celevra]

i appreciate your work, thank you.
But i allready started over (now with backups ;-))

[TheophileDiot]

Hi @celevra, I couldn't reproduce the problem could you give us more information? :

1. The logs you provided are after the whole procedure or during it ?
2. Do you have more relevant logs ? The ones you provided doesn't help really much

[TheophileDiot]

Unfortunately, we do not support database downgrades. Therefore, you would need to recreate a new one since the models are different.

[celevra]

i've just copied these parts, because i thought that it seems like a failure in shema update.
After i updated the fields my self (DEFAULT 0) it seemed it worked, but all my configuration was gone.
so i started from the beginning.

but i have other problems with not updateting services (enable reverse proxy after a service was created) but for that i will create a new issue

[TheophileDiot]

Okay, thank you for that information.
We modified the migration part to create a backup inside the database in case something goes wrong.
We also wrote a migration procedure that will be available in the documentation shortly ! 😄

[ANBAL534]

I have also upgraded from 1.5.5 to 1.5.6 in a kubernetes deployment of bunkerweb and my db also broke down

[fl0ppy-d1sk]

1.5.6 to 1.5.7 should now be smooth.

We also added a backup plugin to rollback if it's needed. More info here : https://docs.bunkerweb.io/1.5.7/security-tuning/#backup-and-restore