Am 24.08.20 um 12:23 schrieb Reindl Harald:
Am 24.08.20 um 09:46 schrieb Sergei Golubchik:
Hi, Reindl!
1. Is it 10.3 to 10.4 difference? There were some optimizations that could have such an effect. https://mariadb.com/kb/en/changes-improvements-in-mariadb-104/#optimizer Can you share the table structure?
it is a 10.3 to 10.4 interface, hence the 10.4 in the subject
2. Technically, MyISAM only returns rows in the order of insertion if you insert into an empty table. This was always the case, see this simple test:
create table t1 (a int) engine=myisam; insert t1 values (1),(2),(3),(4),(5); select * from t1; delete from t1 where a<10; select * from t1; insert t1 values (1),(2),(3),(4),(5); select * from t1; drop table t1;
I don't think this is your case though.
given that tjis is an autotest that table is *always* empty at the begin of the autotest suite
forgot the table structure cms1_comments | CREATE TABLE `cms1_comments` ( `kid` mediumint(7) unsigned NOT NULL AUTO_INCREMENT, `ksid` mediumint(7) unsigned NOT NULL DEFAULT 0, `ks2id` mediumint(7) unsigned NOT NULL DEFAULT 0, `ktimestamp` int(10) unsigned NOT NULL DEFAULT 0, `kip` varchar(50) COLLATE latin1_german1_ci NOT NULL, `kname` varchar(100) COLLATE latin1_german1_ci NOT NULL, `kherkunft` varchar(100) COLLATE latin1_german1_ci NOT NULL, `kemail` varchar(80) COLLATE latin1_german1_ci NOT NULL, `khomepage` varchar(255) COLLATE latin1_german1_ci NOT NULL, `kkommentar` text COLLATE latin1_german1_ci NOT NULL, `kaktiv` tinyint(1) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`kid`), KEY `comment_key_1` (`ksid`,`kaktiv`,`ktimestamp`), KEY `comment_key_2` (`ks2id`,`kaktiv`,`ktimestamp`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci PACK_KEYS=0 DELAY_KEY_WRITE=1 and thats how the test records are generated $data_template = [ 'ksid' => 0, 'ks2id' => 0, 'kname' => 'CMS-Autotest [count]', 'kemail' => 'server-admins@thelounge.net', 'kkommentar' => trim(str_repeat('Test ', 10)), 'kherkunft' => 'Wien', 'khomepage' => 'www.thelounge.net', 'kativ' => 1, ]; for($count=1; $count<=3; $count++) { $data = str_replace('[count]', $count, $data_template); $data['ksid'] = 1; $insert_id = $this->add($data); if(empty($insert_id)) { $cl_autotests->trigger_error('check failed'); } }
On Aug 21, Reindl Harald wrote:
MyISAM:
in all previous versions this test was stable, so as the timestamps are identical and ordering is 'desc' the result was ordered by the creation time of the the records
now it's reverse which can break all sort of expectations in subtle ways
while we could discuss what is the expected output when order by 'ktimestamp' and all are identical it still worries me that something which didn't change over years and major versions now comes with the reverse ordering
unless someone tells me that's the result of some real peformance optimization i would perfer the known result given that it's hard to know how much other code depends implicit on the previous behavior
---------------------------------------------
select SQL_NO_CACHE SQL_CALC_FOUND_ROWS * from `cl_autotest_comments` where ksid=1 and kaktiv=1 order by ktimestamp desc limit 0,10
---------------------------------------------
Array ( [0] => Array ( [kid] => 1 [kaktiv] => 1 [ksid] => 1 [ks2id] => 0 [ktimestamp] => 1598022502 [kip] => 127.0.0.1 [kname] => CMS-Autotest 1 [kherkunft] => Wien [kkommentar] => Test Test Test Test Test Test Test Test Test Test )
[1] => Array ( [kid] => 2 [kaktiv] => 1 [ksid] => 1 [ks2id] => 0 [ktimestamp] => 1598022502 [kip] => 127.0.0.1 [kname] => CMS-Autotest 2 [kherkunft] => Wien [kkommentar] => Test Test Test Test Test Test Test Test Test Test )
[2] => Array ( [kid] => 3 [kaktiv] => 1 [ksid] => 1 [ks2id] => 0 [ktimestamp] => 1598022502 [kip] => 127.0.0.1 [kname] => CMS-Autotest 3 [kherkunft] => Wien [kkommentar] => Test Test Test Test Test Test Test Test Test Test )
)
---------------------------------------------
diff of the expected application output:
-<html><head><title>Autotest</title></head><body><a name="commentpart"></a><div><a href="comments_add.php?s2id=1">Verfassen</a><table><tr><td>CMS-Autotest 1</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test Test Test Test Test</td></tr></table><table><tr><td>CMS-Autotest 2</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test Test Test Test Test</td></tr></table><table><tr><td>CMS-Autotest 3</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test Test Test Test Test</td></tr></table><table><tr><td></td><td></td></tr></table></div></body>
+<html><head><title>Autotest</title></head><body><a name="commentpart"></a><div><a href="comments_add.php?s2id=1">Verfassen</a><table><tr><td>CMS-Autotest 3</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test Test Test Test Test</td></tr></table><table><tr><td>CMS-Autotest 2</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test Test Test Test Test</td></tr></table><table><tr><td>CMS-Autotest 1</td><td></td></tr><tr><td colspan="2">Test Test Test Test Test Test Test Test Test Test</td></tr></table><table><tr><td></td><td></td></tr></table></div></body>