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

aria_chk does not repair tables

Details

    Description

      If we repair the table(s) and start the database, it works until someone tries to access a corrupt row. Then we can repair again but then again as someone tries to access a corrupt row it crashes again.

      We tried to repair it using aria_chk with every possible method/option and afterwards trying to do a mysqldump but it just crashes after reaching some row.

      The error we get from mysqldump:

      G:\MariaDB\bin>mysqldump greyhound items_protocols -u root > items_protocols.txt
      mysqldump: Error 1030: Got error 22 "Invalid argument" from storage engine Aria when dumping table `items_protocols` at row: 38910855

      We even tried to delete those rows or exclude them using a where clause with the mysqldump command.

      Here the create table statement:

      CREATE TABLE `items_protocols` (
      		 
        `i_id` bigint(20) NOT NULL AUTO_INCREMENT,
      		 
        `i_item_r` int(11) NOT NULL DEFAULT '0',
      		 
        `i_user_r` int(11) NOT NULL DEFAULT '0',
      		 
        `c_rulename` varchar(64) NOT NULL DEFAULT '',
      		 
        `c_text` varchar(32768) NOT NULL DEFAULT '',
      		 
        `d_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      		 
        PRIMARY KEY (`i_id`),
      		 
        KEY `i_item_r` (`i_item_r`)
      		 
      ) ENGINE=Aria AUTO_INCREMENT=1033130995 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=1

      And our my.ini:

      [mysqld]
      		 
      character-set-server=latin1
      		 
      datadir=G:/MariaDB/data/
      		 
      enable-named-pipe
      		 
      key_buffer_size=64K
      		 
      aria_pagecache_buffer_size=8192M
      		 
      max_allowed_packet=32M
      		 
      max_connections=512
      		 
      myisam_max_sort_file_size=100G
      		 
      myisam_sort_buffer_size=64M
      		 
      port=3306
      		 
      query_cache_limit=8M
      		 
      query_cache_size=32M
      		 
      query_cache_type=1
      		 
      read_buffer_size=512K
      		 
      read_rnd_buffer_size=1M
      		 
      sort_buffer_size=4M
      		 
      sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
      		 
      thread_cache_size=128
      		 
      tmp_table_size=8M
      		 
      thread_handling=pool-of-threads
      		 
      thread_pool_min_threads=64
      		 
      thread_pool_max_threads=1024

      Any suggestions what we could try other than we already did?

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26533 MariaDB 10.5 and 10.6 crashes with key_buffer_size greater than 4Gb on Windows 64bit

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Hi,

            Does the table contain confidential information? Would you be able to upload it (all three files .frm, MAI, MAD) to ftp.askmonty.org/private?

            elenst Elena Stepanova added a comment - Hi, Does the table contain confidential information? Would you be able to upload it (all three files .frm, MAI, MAD) to ftp.askmonty.org/private?
            RaphaelH Raphael H. added a comment -

            I just uploaded the files.

            RaphaelH Raphael H. added a comment - I just uploaded the files.
            RaphaelH Raphael H. added a comment -

            Any news regarding this ticket?

            RaphaelH Raphael H. added a comment - Any news regarding this ticket?
            elenst Elena Stepanova added a comment -

            Thanks for the data. I was able to reproduce the problem.
            Apparently, it only happens on Windows, and only on non-debug builds.

            The culprit is aria_pagecache_buffer_size value.

            RaphaelH, as a workaround, please try to reduce the value to something not bigger than 4G.

            When it exceeds 4260M, bad things start happening.

            • when it's greater than 4262M, the error happens upon select: 

              mysql -uroot test -e "select * from t1" > t1.data
              		 
              ERROR 1030 (HY000) at line 1: Got error 22 "Invalid argument" from storage engine Aria

            • when it's 4261M or 4262M, the server crashes upon the same select: 

              Version: '10.0.24-MariaDB'  socket: ''  port: 3306  Source distribution
              		 
              160303  2:32:51 [ERROR] mysqld got exception 0xc0000005 ;
              		 
              This could be because you hit a bug. It is also possible that this binary
              		 
              or one of the libraries it was linked against is corrupt, improperly built,
              		 
              or misconfigured. This error can also be caused by malfunctioning hardware.
              		 
               
              		 
              To report this bug, see http://kb.askmonty.org/en/reporting-bugs
              		 
               
              		 
              We will try our best to scrape up some info that will hopefully help
              		 
              diagnose the problem, but since we have already crashed,
              		 
              something is definitely wrong and this may fail.
              		 
               
              		 
              Server version: 10.0.24-MariaDB
              		 
              key_buffer_size=134217728
              		 
              read_buffer_size=131072
              		 
              max_used_connections=1
              		 
              max_threads=501
              		 
              thread_count=0
              		 
              It is possible that mysqld could use up to
              		 
              key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1223927 K  bytes of memory
              		 
              Hope that's ok; if not, decrease some variables in the equation.
              		 
               
              		 
              Thread pointer: 0x0x0
              		 
              Attempting backtrace. You can use the following information to find out
              		 
              where mysqld died. If you see no messages after this, something went
              		 
              terribly wrong...
              		 
              ntdll.dll!RtlDeNormalizeProcessParams()
              		 
              ntdll.dll!RtlDeNormalizeProcessParams()
              		 
              mysqld.exe!os_mutex_enter()[os0sync.cc:761]
              		 
              mysqld.exe!sync_array_wake_threads_if_sema_free_low()[sync0arr.cc:949]
              		 
              mysqld.exe!sync_arr_wake_threads_if_sema_free()[sync0arr.cc:989]
              		 
              mysqld.exe!srv_error_monitor_thread()[srv0srv.cc:2189]
              		 
              kernel32.dll!BaseThreadInitThunk()
              		 
              ntdll.dll!RtlUserThreadStart()
              		 
              The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
              		 
              information that should help you find out what is causing the crash.

            On some reason, I cannot reproduce it on MariaDB 10.1, only 5.5 and 10.0 are affected. Maybe it was fixed, maybe not.
            wlad, could you please take a look?

            If it's indeed fixed in 10.1, I'm not sure we must backport the fix to 10.0, as there is a viable workaround – use a smaller value of the option (if you think we should backport anyway, please do so). But if it's still in 10.1, just does not cause visible problems on whatever reason, it definitely needs fixing.

            To reproduce:

            • Start the server with a safe value of aria_pagecache_buffer_size
            • Execute 

              drop table if exists t1;
              		 
              create table t1 (pk int auto_increment primary key, i int, key(i)) engine=Aria;
              		 
              insert into t1 values (null,1),(null,2),(null,3),(null,4);
              		 
              insert into t1 select null, a.i from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f, t1 g, t1 h, t1 i;

            • restart server with a problematic value of aria_pagecache_buffer_size (see above, depending on what you are trying to reproduce, the error or the crash);
            • run 

              mysql -uroot test -e "select * from t1" > t1.data

            elenst Elena Stepanova added a comment - Thanks for the data. I was able to reproduce the problem. Apparently, it only happens on Windows, and only on non-debug builds. The culprit is aria_pagecache_buffer_size value. RaphaelH , as a workaround, please try to reduce the value to something not bigger than 4G. When it exceeds 4260M , bad things start happening. when it's greater than 4262M , the error happens upon select: mysql -uroot test -e "select * from t1" > t1.data ERROR 1030 (HY000) at line 1: Got error 22 "Invalid argument" from storage engine Aria when it's 4261M or 4262M , the server crashes upon the same select: Version: '10.0.24-MariaDB' socket: '' port: 3306 Source distribution 160303 2:32:51 [ERROR] mysqld got exception 0xc0000005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware.   To report this bug, see http://kb.askmonty.org/en/reporting-bugs   We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail.   Server version: 10.0.24-MariaDB key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=1 max_threads=501 thread_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1223927 K bytes of memory Hope that's ok; if not , decrease some variables in the equation.   Thread pointer: 0x0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... ntdll.dll!RtlDeNormalizeProcessParams() ntdll.dll!RtlDeNormalizeProcessParams() mysqld.exe!os_mutex_enter()[os0sync.cc:761] mysqld.exe!sync_array_wake_threads_if_sema_free_low()[sync0arr.cc:949] mysqld.exe!sync_arr_wake_threads_if_sema_free()[sync0arr.cc:989] mysqld.exe!srv_error_monitor_thread()[srv0srv.cc:2189] kernel32.dll!BaseThreadInitThunk() ntdll.dll!RtlUserThreadStart() The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. On some reason, I cannot reproduce it on MariaDB 10.1, only 5.5 and 10.0 are affected. Maybe it was fixed, maybe not. wlad , could you please take a look? If it's indeed fixed in 10.1, I'm not sure we must backport the fix to 10.0, as there is a viable workaround – use a smaller value of the option (if you think we should backport anyway, please do so). But if it's still in 10.1, just does not cause visible problems on whatever reason, it definitely needs fixing. To reproduce: Start the server with a safe value of aria_pagecache_buffer_size Execute drop table if exists t1; create table t1 (pk int auto_increment primary key , i int , key (i)) engine=Aria; insert into t1 values ( null ,1),( null ,2),( null ,3),( null ,4); insert into t1 select null , a.i from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f, t1 g, t1 h, t1 i; restart server with a problematic value of aria_pagecache_buffer_size (see above, depending on what you are trying to reproduce, the error or the crash); run mysql -uroot test -e "select * from t1" > t1.data
            RaphaelH Raphael H. added a comment -

            Setting aria_pagecache_buffer_size to 4G seems to work. Thanks for your help!

            RaphaelH Raphael H. added a comment - Setting aria_pagecache_buffer_size to 4G seems to work. Thanks for your help!
            wlad Vladislav Vaintroub added a comment -

            pushed to 5.5 , serious

            wlad Vladislav Vaintroub added a comment - pushed to 5.5 , serious

            People

              wlad Vladislav Vaintroub
              RaphaelH Raphael H.
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.