Yuchen Pei added a comment - 2024-09-30 07:44 Can be reproduced locally with mtr --no-reorder compat/oracle.sp-package perfschema.lowercase_fs_off Guess compat/oracle.sp-package did not clean up properly

Daniel Black added a comment - 2024-10-01 06:35 Note wasn't sufficient: --- a/mysql-test/suite/compat/oracle/t/sp-package.test +++ b/mysql-test/suite/compat/oracle/t/sp-package.test @@ -3108,4 +3108,5 @@ EXPLAIN EXTENDED SELECT 'Non-deterministic package function', COUNT(*) FROM t1 W DROP TABLE t1; DROP FUNCTION f1_deterministic; DROP FUNCTION f2_not_deterministic; +DROP PACKAGE BODY pkg1; DROP PACKAGE pkg1; Perfschema seems to require pre-truncation in tests. however wasn't sufficient with: diff --git a/mysql-test/suite/perfschema/t/lowercase_fs_off.test b/mysql-test/suite/perfschema/t/lowercase_fs_off.test index 1132ce274eb..bcf79e306f5 100644 --- a/mysql-test/suite/perfschema/t/lowercase_fs_off.test +++ b/mysql-test/suite/perfschema/t/lowercase_fs_off.test @@ -6,6 +6,17 @@ --source include/have_perfschema.inc --source include/not_embedded.inc +--disable_query_log +truncate table performance_schema.events_statements_summary_by_program; +truncate table performance_schema.events_statements_summary_by_digest; +truncate table performance_schema.events_statements_history; +truncate table performance_schema.events_statements_history_long; +--enable_query_log + +TRUNCATE PERFORMANCE_SCHEMA.events_statements_summary_by_program; + +SELECT object_type, object_schema, object_name, count_star, count_statements, sum_rows_sent +FROM performance_schema.events_statements_summary_by_program; --echo # --echo # MDEV-33020 The database part is not case sensitive in SP names in PERFORMANCE_SCHEMA --echo # @@ -15,8 +26,6 @@ CREATE OR REPLACE DATABASE db1; CREATE PROCEDURE DB1.sp() SELECT 'This is DB1.sp'; CREATE PROCEDURE db1.sp() SELECT 'This is db1.sp'; CALL DB1.sp(); -# This is needed to reset the SP cache (a MDEV-33019 workaround) -CREATE PROCEDURE DB1.sp2() SELECT 'This is DB1.sp2'; CALL db1.sp(); SELECT object_type, object_schema, object_name, count_star, count_statements, sum_rows_sent resulted in: worker[01] Using MTR_BUILD_THREAD 300, with reserved ports 19000..19029 compat/oracle.sp-package [ pass ] 72 perfschema.lowercase_fs_off [ fail ] Test ended at 2024-10-01 16:13:20   CURRENT_TEST: perfschema.lowercase_fs_off --- /home/dan/repos/mariadb-server-11.6/mysql-test/suite/perfschema/r/lowercase_fs_off.result 2024-10-01 15:58:24.586125504 +1000 +++ /home/dan/repos/mariadb-server-11.6/mysql-test/suite/perfschema/r/lowercase_fs_off.reject 2024-10-01 16:13:20.784342459 +1000 @@ -1,3 +1,79 @@ +TRUNCATE PERFORMANCE_SCHEMA.events_statements_summary_by_program; +SELECT object_type, object_schema, object_name, count_star, count_statements, sum_rows_sent +FROM performance_schema.events_statements_summary_by_program; +object_type object_schema object_name count_star count_statements sum_rows_sent +PROCEDURE mtr check_testcase 0 0 0 +TRIGGER mtr gs_insert 0 0 0 +TRIGGER mtr ts_insert 0 0 0 +FUNCTION sys extract_schema_from_file_name 0 0 0 +FUNCTION sys extract_table_from_file_name 0 0 0 +FUNCTION sys format_bytes 0 0 0 +FUNCTION sys format_path 0 0 0 +FUNCTION sys format_statement 0 0 0 +FUNCTION sys format_time 0 0 0 +FUNCTION sys list_add 0 0 0 +FUNCTION sys list_drop 0 0 0 +FUNCTION sys ps_is_account_enabled 0 0 0 +FUNCTION sys ps_is_consumer_enabled 0 0 0 +FUNCTION sys ps_is_instrument_default_enabled 0 0 0 +FUNCTION sys ps_is_instrument_default_timed 0 0 0 +FUNCTION sys ps_is_thread_instrumented 0 0 0 +FUNCTION sys ps_thread_account 0 0 0 +FUNCTION sys ps_thread_id 0 0 0 +FUNCTION sys ps_thread_stack 0 0 0 +FUNCTION sys ps_thread_trx_info 0 0 0 +FUNCTION sys quote_identifier 0 0 0 +FUNCTION sys sys_get_config 0 0 0 +FUNCTION sys version_major 0 0 0 +FUNCTION sys version_minor 0 0 0 +FUNCTION sys version_patch 0 0 0 +FUNCTION test test2.f1 0 0 0 +FUNCTION test test2.f2 0 0 0 +FUNCTION test test2.concat 0 0 0 +PROCEDURE test test2.p1 0 0 0 +PROCEDURE test test2.p2 0 0 0 +FUNCTION test2 test2.f1 0 0 0 +PROCEDURE test2 test2.p1 0 0 0 +FUNCTION test test2.f2private 0 0 0 +PROCEDURE test test2.p2private 0 0 0 +FUNCTION test pkg.f1 0 0 0 +PROCEDURE test pack.p1 0 0 0 +FUNCTION test pack.f1 0 0 0 +PROCEDURE test pack.p2 0 0 0 +FUNCTION test pack.f2 0 0 0 +PROCEDURE test pack.p3 0 0 0 +FUNCTION test pack.f3 0 0 0 +PROCEDURE test pkg1.p1 0 0 0 +PROCEDURE test p1.p1 0 0 0 +FUNCTION test p1.f1 0 0 0 +PROCEDURE test p1.init1 0 0 0 +PROCEDURE test p1.init2 0 0 0 +FUNCTION test p1.init3 0 0 0 +PROCEDURE test p1.p2 0 0 0 +PROCEDURE test p2.p1 0 0 0 +FUNCTION test p2.f1 0 0 0 +PROCEDURE test p1.p1.p1 0 0 0 +FUNCTION test p1.p1.f1 0 0 0 +PROCEDURE test pkg1.p01 0 0 0 +PROCEDURE test pkg1.p00 0 0 0 +PROCEDURE test xyz.xyz123 0 0 0 +FUNCTION db1 pkg1.f1 0 0 0 +FUNCTION db1 pkg1.f2_db1_pkg1_f1 0 0 0 +FUNCTION db1 pkg1.f2_pkg1_f1 0 0 0 +FUNCTION db1 pkg1.f2_f1 0 0 0 +FUNCTION db2 pkg1.f1 0 0 0 +FUNCTION db2 pkg1.var1 0 0 0 +FUNCTION db2 pkg1.var2 0 0 0 +FUNCTION test pkg1.f1 0 0 0 +FUNCTION test test1.f_test 0 0 0 +PROCEDURE 0 0 0 +PROCEDURE db2 pkg1.p1 0 0 0 +PROCEDURE db2 pkg1.p2_db1_pkg1_p1 0 0 0 +PROCEDURE db1 pkg1.p1 0 0 0 +PROCEDURE test pkg1.t1_populate 0 0 0 +FUNCTION test pkg1.f3_deterministic 0 0 0 +FUNCTION test pkg1.f4_not_deterministic 0 0 0 +PROCEDURE mtr check_warnings 0 0 0

Oleksandr Byelkin added a comment - 2024-10-08 12:20 At least for me it looks like connected with something left in the server somewhere. If put some result mismatch in the first test of two the second test pass (mtr make more cleanup in case of error probably) but otherwise something left which should not be left. ./mtr --no-reorder compat/oracle.sp-package perfschema.lowercase_fs_off --mem

Oleksandr Byelkin added a comment - 2024-10-08 12:23 - edited If remove this test it will pass: # # Testing packages in different databases calling each other # in routines and in the initialization section. #   #CREATE DATABASE db1; #DELIMITER $$; #CREATE PACKAGE db1.pkg1 AS # PROCEDURE p1(a OUT TEXT); #END; #$$ #CREATE PACKAGE BODY db1.pkg1 AS # PROCEDURE p1(a OUT TEXT) AS # BEGIN # a:= 'This is db1.pkg1.p1'; # END; #END; #$$ #DELIMITER ;$$ # #CREATE DATABASE db2; #DELIMITER $$; #CREATE PACKAGE db2.pkg1 AS # FUNCTION var1 RETURN TEXT; # PROCEDURE p1(a OUT TEXT); # PROCEDURE p2_db1_pkg1_p1; #END; #$$ #CREATE PACKAGE BODY db2.pkg1 AS # m_var1 TEXT; # FUNCTION var1 RETURN TEXT AS # BEGIN # RETURN m_var1; # END; # PROCEDURE p1(a OUT TEXT) AS # BEGIN # a:= 'This is db2.pkg1.p1'; # END; # PROCEDURE p2_db1_pkg1_p1 AS # a TEXT; # BEGIN # db1.pkg1.p1(a); # SELECT a; # END; #BEGIN # db1.pkg1.p1(m_var1); #END; #$$ #DELIMITER ;$$ # #SELECT db2.pkg1.var1(); #CALL db2.pkg1.p2_db1_pkg1_p1; #--enable_ps2_protocol # #DROP DATABASE db1; #DROP DATABASE db2; #

Oleksandr Byelkin added a comment - 2024-10-08 12:31 - edited The test case : --source include/default_charset.inc   SET sql_mode=ORACLE;   CREATE DATABASE db1; DELIMITER $$; CREATE PACKAGE db1.pkg1 AS PROCEDURE p1(a OUT TEXT); END; $$ CREATE PACKAGE BODY db1.pkg1 AS PROCEDURE p1(a OUT TEXT) AS BEGIN a:= 'This is db1.pkg1.p1'; END; END; $$ DELIMITER ;$$   CREATE DATABASE db2; DELIMITER $$; CREATE PACKAGE db2.pkg1 AS FUNCTION var1 RETURN TEXT; PROCEDURE p1(a OUT TEXT); PROCEDURE p2_db1_pkg1_p1; END; $$ CREATE PACKAGE BODY db2.pkg1 AS m_var1 TEXT; FUNCTION var1 RETURN TEXT AS BEGIN RETURN m_var1; END; PROCEDURE p1(a OUT TEXT) AS BEGIN a:= 'This is db2.pkg1.p1'; END; PROCEDURE p2_db1_pkg1_p1 AS a TEXT; BEGIN db1.pkg1.p1(a); SELECT a; END; BEGIN db1.pkg1.p1(m_var1); END; $$ DELIMITER ;$$   SELECT db2.pkg1.var1(); CALL db2.pkg1.p2_db1_pkg1_p1; --enable_ps2_protocol   DROP DATABASE db1; DROP DATABASE db2;   SELECT * FROM performance_schema.events_statements_summary_by_program WHERE object_type='procedure' AND LOWER(object_schema)='db1'; as we dropped database nothing should be left behind to be shown in teh last SELECT but it return: ... DROP DATABASE db1; DROP DATABASE db2; SELECT * FROM performance_schema.events_statements_summary_by_program WHERE object_type='procedure' AND LOWER(object_schema)='db1'; OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR SUM_TIMER_WAIT MIN_TIMER_WAIT AVG_TIMER_WAIT MAX_TIMER_WAIT COUNT_STATEMENTS SUM_STATEMENTS_WAIT MIN_STATEMENTS_WAIT AVG_STATEMENTS_WAIT MAX_STATEMENTS_WAIT SUM_LOCK_TIME SUM_ERRORS SUM_WARNINGS SUM_ROWS_AFFECTED SUM_ROWS_SENT SUM_ROWS_EXAMINED SUM_CREATED_TMP_DISK_TABLES SUM_CREATED_TMP_TABLES SUM_SELECT_FULL_JOIN SUM_SELECT_FULL_RANGE_JOIN SUM_SELECT_RANGE SUM_SELECT_RANGE_CHECK SUM_SELECT_SCAN SUM_SORT_MERGE_PASSES SUM_SORT_RANGE SUM_SORT_ROWS SUM_SORT_SCAN SUM_NO_INDEX_USED SUM_NO_GOOD_INDEX_USED PROCEDURE db1 pkg1.p1 2 41720000 0 20860000 20911000 2 14374000 6631000 7187000 7743000 0 0 0 00 0 0 0 0 0 0 0 0 0 00 0 0 0

Alexander Barkov added a comment - 2024-10-24 13:55 A smaller test case demonstrating the same problem: --source include/default_charset.inc   SET sql_mode=ORACLE; CREATE DATABASE db1; DELIMITER $$; CREATE PACKAGE db1.pkg1 AS PROCEDURE p1(); END ; $$   CREATE PACKAGE BODY db1.pkg1 AS PROCEDURE p1() AS BEGIN NULL ; END ; END ; $$   DELIMITER ;$$   CALL db1.pkg1.p1();   DROP DATABASE db1;     --vertical_results SELECT * FROM performance_schema.events_statements_summary_by_program WHERE object_type= 'procedure' AND LOWER (object_schema)= 'db1' ;

Alexander Barkov added a comment - 2024-10-28 10:40 - edited The problem happens because when a package procedure (or a package function) is executed, performance schema remembers it as: type='procedure' db='db' name='pkg1.p1' When a DROP DATABASE happens, performance schema removes from its cached only a record like this: type='package body' db='db' name='pkg1' To make it work properly, when removing a package body from the perfschema cache, it should parse the package body and remove its all routines. This looks quite complex. Another option is to remember package routines in perfschema as their owner package body. So when db.pkg1.p1 is executed, perfschema remembers it as: type='package body' db='db' name='pkg1' But this will worsen the statistics.

Daniel Black added a comment - 2024-12-19 02:50 https://buildbot.mariadb.org/#/builders/534/builds/23551 (main) CURRENT_TEST: perfschema.lowercase_fs_off --- /home/buildbot/amd64-ubuntu-2204-debug-ps/build/mysql-test/suite/perfschema/r/lowercase_fs_off.result 2024-12-14 12:15:59.000000000 +0000 +++ /home/buildbot/amd64-ubuntu-2204-debug-ps/build/mysql-test/suite/perfschema/r/lowercase_fs_off.reject 2024-12-14 12:27:47.236326902 +0000 @@ -16,7 +16,7 @@ FROM performance_schema.events_statements_summary_by_program WHERE object_type='procedure' AND LOWER(object_schema)='m33020_db1'; object_type object_schema object_name count_star count_statements sum_rows_sent -PROCEDURE m33020_DB1 sp 1 1 1 -PROCEDURE m33020_db1 sp 1 1 1 +PROCEDURE m33020_DB1 sp 2 2 1 +PROCEDURE m33020_db1 sp 2 2 1 DROP DATABASE m33020_db1; DROP DATABASE m33020_DB1;

Yuchen Pei added a comment - 2024-12-20 02:50 Not sure how this can be reproduced locally, not with the old trick of --no-reorder, i.e. the following passes at f5821aaf7731e1f74b970fdaf186b945ae012c9d despite failing in the previous buildbot link, with or without --ps-protocol: mtr --no-reorder perfschema.information_schema perfschema.lowercase_fs_off This was from August and looks like the same failure https://buildbot.mariadb.org/#/builders/572/builds/12277

Yuchen Pei added a comment - 2025-01-21 00:24 Just encountered the new failure in https://buildbot.mariadb.org/#/builders/369/builds/27641

Yuchen Pei added a comment - 2025-03-03 23:50 The latest failure on 11.8 on the new buildbot is from 2025-02-17 59ad3225ae16b5672f10f30841ef414a8cb0d42c: https://buildbot.mariadb.org/#/builders/572/builds/16691 cross ref could not find anything on the old buildbot: https://buildbot.mariadb.net/ci/reports/cross_reference#branch=11.8&revision=&platform=&fail_name=perfschema.lowercase_fs_off&fail_variant=&fail_info_full=&typ=&info=&dt=&limit=100&fail_info_short=