Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
-
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
- is blocked by
-
-
- Closed
-
Activity
Updated to php7:
<?php
$pid=pcntl_fork();
// connect to database
$conn=mysqli_connect('127.0.0.1:3307','root');
// create an Aria table
mysqli_query($conn,"DROP DATABASE IF EXISTS mdev6817");
mysqli_query($conn,"CREATE DATABASE mdev6817");
mysqli_select_db($conn,'mdev6817');
mysqli_query($conn,"CREATE TABLE IF NOT EXISTS t1 (id INT NOT NULL DEFAULT '0', PRIMARY KEY (id)) ENGINE=Aria transactional=1");
// enable query cache
mysqli_query($conn,"SET GLOBAL query_cache_type = 1");
mysqli_query($conn,"SET SESSION query_cache_type = 1");
mysqli_query($conn,"SET GLOBAL query_cache_size = 1024*1024*256");
// this is just for further logging
$con_id=mysqli_result(mysqli_query($conn,"SELECT CONNECTION_ID()"),0,0);
// $counter will count sequential SELECT MAX() queries executed after the last successful INSERT
$counter=0;
function mysqli_result($search, $row, $field) {
$result="";
$i=0; while($results=mysqli_fetch_array($search)){
if ($i==$row){$result=$results[$field];}
$i++;}
return $result;}
// and start a infinite loop
while(1){
$SQL= "SELECT MAX(id) FROM t1";
$tmp_tbl=mysqli_query($conn,$SQL);
$id=mysqli_result($tmp_tbl,0,0);
mysqli_free_result($tmp_tbl);
// 1000 is for throttling
if($counter%1000==0 && $counter>1000)
$id++;
// probably to avoid overflow? It was like that in the initial test, keeping as is
if($id<=0) $id=1;
$SQL= "INSERT INTO t1 VALUES ($id)";
$ok=mysqli_query($conn,$SQL); /* autocommit = 1 */
if($ok)
{ echo "Connection $con_id: Successfully inserted id=$id\n"; $counter=0; continue; } $counter++;
usleep(500); // wait a bit...
}
The problem is that for transactional aria tables (row_type=PAGE and transactional=1), maria_lock_database() didn't flush the state or the query cache.
Not flushing the state is correct for transactional tables as this is done by checkpoint, but not flushing the query cache was wrong and could cause
SELECT queries to not be deleted from the cache.
Fixed by introducing a flush of the query cache as part of commit, if the table has changed.
The test cases above and roberto's analyze of the problem helped a lot in finding and fixing this case.
To reproduce on higher versions, SESSION query_cache_type also needs to be set (upon the first execution of the test, subsequent executions will pick up the global value which it sets).
<?php// connect to database// create an Aria table// enable query cache// this is just for further logging// $counter will count sequential SELECT MAX() queries executed after the last successful INSERT// and start a infinite loop}}}