Error while setting value 'rowid_filter=off' to 'optimizer_switch'.

[Nolwennig]

# Create your project directory then go into it:
mkdir -p ~/Sites/magento
cd $_

# Download the Docker Compose template:
curl -s https://raw.githubusercontent.com/markshust/docker-magento/master/lib/template | bash

# Take a backup of your existing database:
bin/mysqldump > ~/Sites/existing/magento.sql

# Replace with existing source code of your existing Magento instance:
cp -R ~/Sites/existing src
# or: git clone git@github.com:myrepo.git src

# Start some containers, copy files to them and then restart the containers:
bin/start --no-dev

and bin/start --no-dev return

[+] Running 7/7
 ✔ Container magento246-redis-1        Healthy        0.9s 
 ✔ Container magento246-mailcatcher-1  Running        0.0s 
 ✔ Container magento246-opensearch-1   Healthy        0.9s 
 ✔ Container magento246-rabbitmq-1     Healthy        0.9s 
 ✘ Container magento246-db-1           Error          1.9s 
 ✔ Container magento246-phpfpm-1       Recreated      0.0s 
 ✔ Container magento246-app-1          Recreated      0.1s 
dependency failed to start: container magento246-db-1 exited (1)

➜ magento246 git:(master) ✗ docker logs magento246-db-1 return

➜  magento246 git:(master) ✗ docker logs magento246-db-1
2024-07-24 12:18:22+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.39-1.el9 started.
2024-07-24 12:18:22+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
    command was: mysqld --max_allowed_packet=64M --optimizer_use_condition_selectivity=1 --optimizer_switch=rowid_filter=off --verbose --help --log-bin-index=/tmp/tmp.5svLfXEFj8
2024-07-24T12:18:22.062514Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2024-07-24T12:18:22.062591Z 0 [ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value 'rowid_filter=off' to 'optimizer_switch'.
2024-07-24T12:18:22.064065Z 0 [ERROR] [MY-010119] [Server] Aborting

I find rowid_filter option for optimizer_switch in MariaDB (since 10.4.3) -- see: https://mariadb.com/kb/en/rowid-filtering-optimization/ + https://mariadb.com/kb/en/rowid-filtering-optimization/ -- but I dont find the options rowid_filter for optimizer_switch -- see: https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html + https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_optimizer_switch , so I decide to quickly comment the line 59 --optimizer_switch="rowid_filter=off" in compose.yaml

db:
    image: mysql:8.0
    command:
      --max_allowed_packet=64M
      --optimizer_use_condition_selectivity=1
    #  --optimizer_switch="rowid_filter=off"
    ports:
      - "3306:3306"
    env_file: env/db.env
    volumes:
      - dbdata:/var/lib/mysql

run again bin/start --no-dev failed to start

At this point, ➜ magento246 git:(master) ✗ docker logs magento246-db-1 return

2024-07-24 12:07:31+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.39-1.el9 started.
2024-07-24 12:07:31+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2024-07-24 12:07:31+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.39-1.el9 started.
'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2024-07-24T12:07:31.868796Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2024-07-24T12:07:31.870695Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.39) starting as process 1
2024-07-24T12:07:31.879748Z 0 [Warning] [MY-010075] [Server] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4e077def-49b5-11ef-b5e4-0242ac130006.
2024-07-24T12:07:31.885173Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-07-24T12:07:31.885194Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
-> 2024-07-24T12:07:31.909939Z 1 [ERROR] [MY-012224] [InnoDB] Tablespace flags are invalid in datafile: ./ibdata1, Space ID:0, Flags: 21. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2024-07-24T12:07:31.909971Z 1 [ERROR] [MY-012237] [InnoDB] Corrupted page [page id: space=0, page number=0] of datafile './ibdata1' could not be found in the doublewrite buffer.
2024-07-24T12:07:31.909982Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Data structure corruption.
2024-07-24T12:07:32.409039Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2024-07-24T12:07:32.409404Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-07-24T12:07:32.409435Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-07-24T12:07:32.410285Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.39)  MySQL Community Server - GPL.

Here is the best way is under the message : Tablespace flags are invalid in datafile: ./ibdata1, Space ID:0, Flags: 21. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting-datadict.html for how to resolve the issue. ?

Originally posted by @Nolwennig in https://github.com/markshust/docker-magento/issues/1188#issuecomment-2247844812

[maxorehov]

did you fix this error?

[Nolwennig]

@maxorehov No :disappointed: , to temporary fix this error, I used mariadb:10.6 in compose.yaml (line:41)

  # If you wish to use MariaDB, comment out the mysql db image below and
  # uncomment this block.
  db:
   image: mariadb:10.6
   command:
     --max_allowed_packet=64M
     --optimizer_use_condition_selectivity=1
     --optimizer_switch="rowid_filter=off"
   ports:
     - "3306:3306"
   env_file: env/db.env
   volumes:
     - dbdata:/var/lib/mysql

  # # If you wish to use MySQL, comment out the mariadb db image above and
  # # uncomment this block.
  # db:
  #   image: mysql:8.0
  #   command:
  #     --max_allowed_packet=64M
  #     --optimizer_use_condition_selectivity=1
  #     # --optimizer_switch="rowid_filter=off"
  #   ports:
  #     - "3306:3306"
  #   env_file: env/db.env
  #   volumes:
  #     - dbdata:/var/lib/mysql

[aveshnaik007]

Did anyone fixed it?