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

query cache bug (resturning inconsistent/old result set) with aria table parallel inserts, row format = page

    XMLWordPrintable

    Details

    • Sprint:
      10.0.20

      Description

      hi guys, i'm running a stress test with query cache and aria...
      the table:

      CREATE TABLE `errors` (
      	`id1` INT(11) NOT NULL DEFAULT '0',
      	`id2` INT(11) NOT NULL DEFAULT '0',
      	`err_id` INT(11) NOT NULL DEFAULT '0',
      	`datahora` DECIMAL(22,7) NOT NULL DEFAULT '0.0000000',
      	`erro` LONGBLOB NOT NULL,
      	PRIMARY KEY (`id1`, `id2`, `err_id`)
      )
      COLLATE='latin1_swedish_ci'
      ENGINE=Aria;
      (using page table type!)

      i startup a php script:

      <?php
      $pid=pcntl_fork();
      if($pid==0) $pid=pcntl_fork();
      if($pid==0) $pid=pcntl_fork();
      // three different process
       
       
      // connect to database
      $conn=mysql_connect(/* connection parameters */);
      mysql_select_db(/*select database*/);
       
      // and start a infinite loop
      $contador=0;
      while(1){
      		$SQL=	"SELECT MAX(err_id) FROM errors ".
      			"WHERE id1=1 AND id2=2";
      IF($contador%1000==0 && $contador>1000) echo "\n$SQL\n";
      		$tmp_tbl=mysql_query($SQL);
      		$err_id=mysql_result($tmp_tbl,0,0);
      IF($contador%1000==0 && $contador>1000) echo "\n$err_id\n";
      		mysql_free($tmp_tbl);
      		$err_id++;if($err_id<=0) $err_id=1;
      		$SQL=	"INSERT INTO errors (".
      				"id1,id2,err_id,datahora,erro".
      			") VALUES (".
      				"1,2,$err_id,'".microtime(1)."','test '".
      			")";
      		$ok=mysql_query($SQL); /* autocommit = 1 */
      		
      		if($ok){
      			$contador=0;
      			continue;
      		}
      		$contador++;
      		usleep(500); // wait a bit...
      }

      aria variables:

      Variable_name Value
      aria_block_size 8192
      aria_checkpoint_interval 30
      aria_checkpoint_log_activity 1048576
      aria_force_start_after_recovery_failures 0
      aria_group_commit none
      aria_group_commit_interval 0
      aria_log_file_size 1073741824
      aria_log_purge_type immediate
      aria_max_sort_file_size 9223372036853727232
      aria_page_checksum ON
      aria_pagecache_age_threshold 300
      aria_pagecache_buffer_size 134217728
      aria_pagecache_division_limit 100
      aria_recover NORMAL
      aria_repair_threads 1
      aria_sort_buffer_size 268434432
      aria_stats_method nulls_unequal
      aria_sync_log_dir NEWFILE
      aria_used_for_temp_tables ON

      autocommit=1
      concurrent_insert isn't a problem, i tested with 0,1,2 this give the same error


      what i think is happen?
      at query cache code there's a consideration about when or when not include the query at query cache, while a insert and another aria table is invalidated and a "select" query execute:

      time ->
      -----INSERT-------
      ---SELECCCCCCT--- (cached after invalidation - shoudn't be cached)
      ---------------------SELECT ---- (got from query cache, wrong query cache value)

      the value of the new insert field err_id isn't updated at the right time, in other words when i connect with a externall tool and set autocommit=0 (to get a different query cache flag, or use SQL_NO_CACHE) i see that the err_id is different from err_id of php script

      in other words
      1)the table wasn't invalidated correctly
      2)the query was cached in a state that aria engine should report to don't cache (concurrent insert? page flush? anything like a buffer or cache being used while updating table?)

      could we recheck if the aria engine invalidation is ok or not?
      i will test again with myisam, and after innodb

      no problem with innodb
      no problem with myisam
      no problem with aria using row_format fixed/dynamic, only with aria-page

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              monty Michael Widenius
              Reporter:
              rspadim roberto spadim
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: