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

Index is not used when "#1292 - Truncated incorrect date value: ~"

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.6.4, 10.4.24, 10.6.7, 10.7.3, 10.8.2
    • Fix Version/s: 10.4, 10.5, 10.6, 10.7, 10.8
    • Component/s: Data types, Optimizer
    • Environment:
      Docker: version 20.10.8, build 3967b7d
      Image: Official image (mariadb:10.6.4)
      OS: Ubuntu 20.04 LTS

      Description

      I am not sure if this is the intended behavior or a bug. This is more of a question than a bug report (please let me know if there are other suitable places).
      If this is intended and the truncate is a necessary and sufficient condition for not using index, would it be nice to have a warning instead of a note? If you don't mind, I would be very happy to know why it is that way and whether it should be truncated on the user side.

      What I did.

      There is a messages table.

      -- auto-generated definition
      create table messages
      (
          id         char(36)                 not null
              primary key,
          user_id    char(36)                 not null,
          channel_id char(36)                 not null,
          text       text collate utf8mb4_bin not null,
          created_at timestamp(6)             null,
          updated_at timestamp(6)             null,
          deleted_at timestamp(6)             null,
          constraint messages_channel_id_channels_id_foreign
              foreign key (channel_id) references channels (id)
                  on update cascade on delete cascade,
          constraint messages_user_id_users_id_foreign
              foreign key (user_id) references users (id)
                  on update cascade on delete cascade
      )
          engine = InnoDB;
       
      create index idx_messages_channel_id_deleted_at_created_at
          on messages (channel_id, deleted_at, created_at);
       
      create index idx_messages_created_at
          on messages (created_at);
       
      create index idx_messages_deleted_at_created_at
          on messages (deleted_at, created_at);
       
      create index idx_messages_deleted_at_updated_at
          on messages (deleted_at, updated_at);
      

      Execute a query.

      EXPLAIN SELECT * FROM `messages` WHERE `created_at` > '2022-04-17 00:00:00.123456789' ORDER BY `created_at` DESC LIMIT 1;
      

      What I expected.

      Using index idx_messages_created_at.
      Like this.

      ANALYZE FORMAT=JSON SELECT * FROM `messages` WHERE `created_at` > '2022-04-17 00:00:00.123456' LIMIT 1;
      

      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.931062234,
          "table": {
            "table_name": "messages",
            "access_type": "range",
            "possible_keys": [
              "idx_messages_created_at"
            ],
            "key": "idx_messages_created_at",
            "key_length": "8",
            "used_key_parts": [
              "created_at"
            ],
            "r_loops": 1,
            "rows": 50408,
            "r_rows": 1,
            "r_table_time_ms": 0.899001226,
            "r_other_time_ms": 0.02017687,
            "filtered": 100,
            "r_filtered": 100,
            "index_condition": "messages.created_at > '2022-04-17 00:00:00.123456'"
          }
        }
      }
      

      What I see instead.

      Index is not used.
      Note that I am comparing created_at column which is microsecond-precision TIMESTAMP(6) with a nanosecond-precision date.

      ANALYZE FORMAT=JSON SELECT * FROM `messages` WHERE `created_at` > '2022-04-17 00:00:00.123456789' LIMIT 1;
      

      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.145948996,
          "table": {
            "table_name": "messages",
            "access_type": "ALL",
            "possible_keys": [
              "idx_messages_created_at"
            ],
            "r_loops": 1,
            "rows": 1770761,
            "r_rows": 40,
            "r_table_time_ms": 0.090800227,
            "r_other_time_ms": 0.040949911,
            "filtered": 100,
            "r_filtered": 2.5,
            "attached_condition": "messages.created_at > '2022-04-17 00:00:00.123456789'"
          }
        }
      }
      

      with a note from the server.

      Note #1292 - Truncated incorrect date value: '2022-04-17 00:00:00.123456789'

      Additional context

      mariadb.cnf

      # The MariaDB configuration file
      #
      # The MariaDB/MySQL tools read configuration files in the following order:
      # 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
      # 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
      # 2. "/etc/mysql/conf.d/*.cnf" to set global options.
      # 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
      # 4. "~/.my.cnf" to set user-specific options.
      #
      # If the same option is defined multiple times, the last one will apply.
      #
      # One can use all long options that the program supports.
      # Run program with --help to get a list of available options and with
      # --print-defaults to see which it would actually understand and use.
      #
      # If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/
       
      #
      # This group is read both by the client and the server
      # use it for options that affect everything
      #
      [client-server]
      # Port or socket location where to connect
      # port = 3306
      socket = /run/mysqld/mysqld.sock
       
      # Import all .cnf files from configuration directory
      [mariadbd]
      skip-host-cache
      skip-name-resolve
       
      !includedir /etc/mysql/mariadb.conf.d/
      !includedir /etc/mysql/conf.d/
      

      50-server.cnf

      #
      # These groups are read by MariaDB server.
      # Use it for options that only the server (but not clients) should see
       
      # this is read by the standalone daemon and embedded servers
      [server]
       
      # this is only for the mysqld standalone daemon
      [mysqld]
       
      #
      # * Basic Settings
      #
       
      #user                    = mysql
      pid-file                = /run/mysqld/mysqld.pid
      basedir                 = /usr
      datadir                 = /var/lib/mysql
      tmpdir                  = /tmp
      lc-messages-dir         = /usr/share/mysql
      lc-messages             = en_US
      skip-external-locking
       
      # Broken reverse DNS slows down connections considerably and name resolve is
      # safe to skip if there are no "host by domain name" access grants
      #skip-name-resolve
       
      # Instead of skip-networking the default is now to listen only on
      # localhost which is more compatible and is not less secure.
      # bind-address            = 127.0.0.1
      # ufwでプライベートネットワーク内からのみ許可
       
      #
      # * Fine Tuning
      #
       
      #key_buffer_size        = 128M
      #max_allowed_packet     = 1G
      #thread_stack           = 192K
      #thread_cache_size      = 8
      # This replaces the startup script and checks MyISAM tables if needed
      # the first time they are touched
      #myisam_recover_options = BACKUP
      #max_connections        = 100
      #table_cache            = 64
       
      #
      # * Logging and Replication
      #
       
      # Both location gets rotated by the cronjob.
      # Be aware that this log type is a performance killer.
      # Recommend only changing this at runtime for short testing periods if needed!
      #general_log_file       = /var/log/mysql/mysql.log
      #general_log            = 1
       
      # When running under systemd, error logging goes via stdout/stderr to journald
      # and when running legacy init error logging goes to syslog due to
      # /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
      # Enable this if you want to have error logging into a separate file
      #log_error = /var/log/mysql/error.log
      # Enable the slow query log to see queries with especially long duration
      #slow_query_log_file    = /var/log/mysql/mariadb-slow.log
      #long_query_time        = 10
      #log_slow_verbosity     = query_plan,explain
      #log-queries-not-using-indexes
      #min_examined_row_limit = 1000
       
      # The following can be used as easy to replay backup logs or for replication.
      # note: if you are setting up a replication slave, see README.Debian about
      #       other settings you may need to change.
      #server-id              = 1
      #log_bin                = /var/log/mysql/mysql-bin.log
      expire_logs_days        = 10
      #max_binlog_size        = 100M
       
      #
      # * SSL/TLS
      #
       
      # For documentation, please read
      # https://mariadb.com/kb/en/securing-connections-for-client-and-server/
      #ssl-ca = /etc/mysql/cacert.pem
      #ssl-cert = /etc/mysql/server-cert.pem
      #ssl-key = /etc/mysql/server-key.pem
      #require-secure-transport = on
       
      #
      # * Character sets
      #
       
      # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
      # utf8 4-byte character set. See also client.cnf
      character-set-server  = utf8mb4
      collation-server      = utf8mb4_general_ci
       
      #
      # * InnoDB
      #
       
      # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
      # Read the manual for more InnoDB related options. There are many!
      # Most important is to give InnoDB 80 % of the system RAM for buffer use:
      # https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size
      innodb_buffer_pool_size = 800MB
       
      # this is only for embedded server
      [embedded]
       
      # This group is only read by MariaDB servers, not by MySQL.
      # If you use the same .cnf file for MySQL and MariaDB,
      # you can put MariaDB-only options here
      [mariadb]
      innodb_read_only_compressed=OFF # for nextcloud
       
      # This group is only read by MariaDB-10.6 servers.
      # If you use the same .cnf file for MariaDB of different versions,
      # use this group for options that older servers don't understand
      [mariadb-10.6]
      

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            temma FUJISHIGE TEMMA
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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