Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35663

Sporadic connection failures during FLUSH PRIVILEGES

Details

    Description

      It seems that FLUSH PRIVILEGES can cause simultaeous connection attempts to fail.

      We noticed this in a Kubernetes cluster with mariadb-operator, which frequently calls FLUSH PRIVILEGES after reconciling users/grants. This causes sporadic connection failures on all clients (mariadb-operator itself, the metrics pod spawned by the operator, and our PHP app).

      The problem is however not limited to mariadb-operator (see MRE below).

      The server side log message is:

      [Warning] Aborted connection <conn id> to db: 'unconnected' user: 'unauthenticated' host: '<ip address>' (This connection closed normally without authentication)

      The PHP client error message is:

      Fatal error: Uncaught mysqli_sql_exception: Host '<ip address>' is not allowed to connect to this MariaDB server

      MRE in a self-contained docker compose file:

      services:
        db:
          image: mariadb:11.4
          entrypoint: >
            bash -c '
            docker-entrypoint.sh --character-set-server=utf8mb4 & 
            sleep 10 ;
            for (( n=0; n<10000; n++ )) ; do mariadb -u root -e "FLUSH PRIVILEGES;" ; done
            '
          environment:
            - MARIADB_DATABASE=foo
            - MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=true
       
        php:
          image: php:8.4
          entrypoint: >
            bash -c "
            docker-php-ext-install mysqli ;
            sleep 5 ;
            for (( n=0; n<10000; n++ )) ; do php -r $'mysqli_connect(\\'db\\', \\'root\\', \\'\\', \\'foo\\');' ; done
            "
      

      How to run: save as compose.yaml, then run docker compose up in the same directory
      What it does: runs a mariadb container, which runs FLUSH PRIVILEGES 10,000 times, and a php container, which tries to connect to the mariadb 10,000 times.
      What I expect: no output ( = no errors)
      What I see instead: sporadic connection failures, for example:

      php-1  | Warning: mysqli_connect(): (HY000/1130): Host '172.22.0.2' is not allowed to connect to this MariaDB server in Command line code on line 1
      db-1   | 2024-12-16 21:49:01 1124 [Warning] Aborted connection 1124 to db: 'unconnected' user: 'unauthenticated' host: '172.22.0.2' (This connection closed normally without authentication)
      

      I have tested this with previous MariaDB image versions back to 10.2 (and PHP versions back to 7.0), all of which exhibit the same problem.
      As the mariadb-operator is written in go and runs into the same problem, I'm confident that this is not a PHP issue.

      I think these mariadb-operator issues are caused by this bug:

      https://github.com/mariadb-operator/mariadb-operator/issues/686
      https://github.com/mariadb-operator/mariadb-operator/issues/1005

      Attachments

        Activity

          danblack Daniel Black added a comment -

          Thanks for the bug report and reproducer. Not discounting a bug somewhere, but the operator need not do any flush privileges - https://github.com/mariadb-operator/mariadb-operator/pull/1083

          danblack Daniel Black added a comment - Thanks for the bug report and reproducer. Not discounting a bug somewhere, but the operator need not do any flush privileges - https://github.com/mariadb-operator/mariadb-operator/pull/1083
          danblack Daniel Black added a comment -

          fwiw, couldn't reproduce on 11.4.5-MariaDB source revision f8eab69c3e2a9d5f95adc084c8651f03f3c58e17 or 10.5.28-MariaDB source revision bc6121819cf648237d0c2c63e48d53acf816db76 over a unix socket.

          danblack Daniel Black added a comment - fwiw, couldn't reproduce on 11.4.5-MariaDB source revision f8eab69c3e2a9d5f95adc084c8651f03f3c58e17 or 10.5.28-MariaDB source revision bc6121819cf648237d0c2c63e48d53acf816db76 over a unix socket.
          sfrye Stefan Frye added a comment -

          @Sergei: Daniel said he couldn't reproduce it over a unix socket. The reproducer is right there in the ticket description.

          sfrye Stefan Frye added a comment - @Sergei: Daniel said he couldn't reproduce it over a unix socket . The reproducer is right there in the ticket description.

          Sorry! reopened

          serg Sergei Golubchik added a comment - Sorry! reopened

          confirmed on 11.8

          1. start the server (no docker required)
          2. run

            create user foo@'%';
            delimiter $
            while 1 do flush privileges; end while $
            

          3. in another terminal run

            while mariadb --protocol tcp --host 127.0.0.1 -u foo '' -e 'select user(), current_user()'; do echo ; done
            

            it'll fail with

            ERROR 2002 (HY000): Received error packet before completion of TLS handshake. The authenticity of the following error cannot be verified: 1130 - Host '127.0.0.1' is not allowed to connect to this MariaDB server
            

            run the command

            while mariadb -u foo '' -e 'select user(), current_user()'; do echo ; done
            

            see how it keeps running

          serg Sergei Golubchik added a comment - confirmed on 11.8 start the server (no docker required) run create user foo@ '%' ; delimiter $ while 1 do flush privileges ; end while $ in another terminal run while mariadb --protocol tcp --host 127.0.0.1 -u foo '' -e 'select user(), current_user()' ; do echo ; done it'll fail with ERROR 2002 (HY000): Received error packet before completion of TLS handshake. The authenticity of the following error cannot be verified: 1130 - Host '127.0.0.1' is not allowed to connect to this MariaDB server run the command while mariadb -u foo '' -e 'select user(), current_user()' ; do echo ; done see how it keeps running

          Looks good to me.

          wlad Vladislav Vaintroub added a comment - Looks good to me.

          People

            serg Sergei Golubchik
            sfrye Stefan Frye
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.