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

MyISAM creates defect indexes on varchar, if rowcount is above some threshold between 480,000,000 and 490,000,000

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2.15, 10.2, 10.3
    • 10.4
    • None
    • Ubuntu Xenial Server
      CPU Intel 6 core + HT, 64GB RAM

    Description

      DROP TABLE IF EXISTS test;
      CREATE TABLE test (
      rs	VARCHAR(20) PRIMARY KEY
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
      

      rs is in the form "rs12364789", max. length of the string is 12; always "rs" before a number

      # myisamchk --keys-used=0 -rq test.MYI
      ... FLUSH
      # mysqlimport --local --fields_escaped_by=\\ -u root -p test_db test.tsv
      ... FLUSH
      # myisamchk --analyze --update-state --sort_buffer_size=56G  --recover --quick --quick --tmpdir=/mnt/WORK/tmp test.MYI
      ... FLUSH
      

      RESULTS:

      • with 480,000,000 rows everything is OK
      • with 490,000,000 rows nothing will be found.
        EXPLAIN says: "Impossible WHERE noticed after reading const tables"

      The row can still be found with IGNORE INDEX (PRIMARY)

      If I build another table with only a non unique index on `rs` , EXPLAIN says it's using the index but the query won't find anything..

      A server version from 3 or 4 Months ago worked OK.

      Hope it helps

      Thanks

      Attachments

        Issue Links

          Activity

            People

              rucha174 Rucha Deodhar
              rawi rawi
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.