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
-