Example Codes for Changing SQL Mode with docker-compose

• Docker: A platform for developing, deploying, and running applications in containers. Containers are self-contained units of software that package code and its dependencies together.
• docker-compose: A tool for defining and running multi-container Docker applications with a single command. It helps you manage the configuration for all your containers and their interactions.
• MariaDB: A popular open-source relational database management system (RDBMS) that's a community-developed fork of MySQL. It's known for its high performance, stability, and compatibility with MySQL.

SQL Mode:

• A setting in MariaDB that controls how strictly the server enforces certain aspects of the SQL language. Stricter modes can prevent errors but may also cause compatibility issues with some applications.

Changing SQL Mode:

3. --sql-mode Flag: To change the SQL mode, you can use the --sql-mode flag followed by a comma-separated list of desired modes or an empty string ("") to disable all modes. Here's an example:

   version: '3'
   services:
     my-mariadb:
       image: mariadb
       command: --sql-mode=""  # Disable all SQL modes
   

   In this example, the --sql-mode="" flag disables all strict SQL modes for the MariaDB container named my-mariadb.

Important Considerations:

• Specific Mode Disabling: If you encounter compatibility issues with a particular mode, consider disabling that specific mode instead of all modes. This approach maintains a balance between flexibility and data integrity.

Example Codes for Changing SQL Mode with docker-compose

Disable All Strict Modes:

This example disables all strict SQL modes:

version: '3'
services:
  my-mariadb:
    image: mariadb
    command: --sql-mode=""  # Disable all SQL modes

Disable Specific Modes:

This example disables the ONLY_FULL_GROUP_BY mode while keeping other modes enabled:

version: '3'
services:
  my-mariadb:
    image: mariadb
    command: --sql-mode=NO_ENGINE_AUTO_INCREMENT,STRICT_TRANS_TABLES  # Disable ONLY_FULL_GROUP_BY

Set Custom SQL Modes:

This example enables specific modes like STRICT_TRANS_TABLES and NO_ZERO_IN_DATE:

version: '3'
services:
  my-mariadb:
    image: mariadb
    command: --sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE  # Custom SQL modes

Explanation:

• The version key specifies the docker-compose file format version (3 in this case).
• The services section defines the services (containers) to run.
• Each service entry has an image key specifying the MariaDB image to use.
• The command key is where you set the --sql-mode flag and the desired modes (or an empty string to disable all).

• Define an environment variable named MYSQL_ALLOW_EMPTY_PASSWORD=yes (or 1) in your docker-compose.yml file. This variable, when set, triggers the MariaDB container to start with an empty root password, allowing you to connect and modify the SQL mode using tools like mysql inside the container.

version: '3'
services:
  my-mariadb:
    image: mariadb
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: yes  # Enables empty root password (for temporary access)
    command: --sql-mode=""  # (Optional) Set SQL mode here if preferred

• This method allows temporary modification but requires connecting to the container and running SQL commands.
• Security Risk: Leaving the root password empty poses a significant security risk. It's highly recommended to set a strong password after modifying the SQL mode and before exposing the container publicly.

Custom Dockerfile:

• Create a custom Dockerfile that inherits from the official MariaDB image and sets the desired SQL mode using the CMD instruction:

FROM mariadb

CMD ["mysqld", "--sql-mode=NO_ENGINE_AUTO_INCREMENT,STRICT_TRANS_TABLES"]

• Build this Dockerfile to create a custom image with the pre-configured SQL mode. Then, reference this custom image in your docker-compose.yml file.

• This method creates a new image, which can increase image size compared to the original MariaDB image.
• It's less flexible compared to the docker-compose.yml method if you need to frequently change the SQL mode.