- commits - lists.mariadb.org

[Commits] 5cac06a04a4: Update test results
by psergey 22 Jan '21

22 Jan '21

revision-id: 5cac06a04a468b421e39fff3daab7f93c28063f5 (mariadb-10.4.11-421-g5cac06a04a4) parent(s): ef5fe786a2f6608a67a34b1bfe91603d9a9f04f7 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-22 18:31:23 +0300 message: Update test results --- .../sys_vars/r/sysvars_server_embedded,32bit.rdiff | 137 +++++++++++---------- 1 file changed, 75 insertions(+), 62 deletions(-) diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff index 9ec15d3079a..d383deb9116 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff @@ -734,7 +734,20 @@ VARIABLE_COMMENT If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 or autoset then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of file descriptors NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2244,7 +2244,7 @@ READ_ONLY YES +@@ -2244,10 +2244,10 @@ + COMMAND_LINE_ARGUMENT REQUIRED + VARIABLE_NAME OPTIMIZER_MAX_SEL_ARG_WEIGHT + VARIABLE_SCOPE SESSION +-VARIABLE_TYPE BIGINT UNSIGNED ++VARIABLE_TYPE INT UNSIGNED + VARIABLE_COMMENT The maximum weight of the SEL_ARG graph. Set to 0 for no limit + NUMERIC_MIN_VALUE 0 +-NUMERIC_MAX_VALUE 18446744073709551615 ++NUMERIC_MAX_VALUE 4294967295 + NUMERIC_BLOCK_SIZE 1 + ENUM_VALUE_LIST NULL + READ_ONLY NO +@@ -2254,7 +2254,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_PRUNE_LEVEL VARIABLE_SCOPE SESSION @@ -743,7 +756,7 @@ VARIABLE_COMMENT Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from the optimizer search space. Meaning: 0 - do not apply any heuristic, thus perform exhaustive search; 1 - prune plans based on number of retrieved rows NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1 -@@ -2254,7 +2254,7 @@ READ_ONLY NO +@@ -2264,7 +2264,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SEARCH_DEPTH VARIABLE_SCOPE SESSION @@ -752,7 +765,7 @@ VARIABLE_COMMENT Maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to compile a query. Values smaller than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will automatically pick a reasonable value. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 62 -@@ -2264,7 +2264,7 @@ READ_ONLY NO +@@ -2274,7 +2274,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SELECTIVITY_SAMPLING_LIMIT VARIABLE_SCOPE SESSION @@ -761,7 +774,7 @@ VARIABLE_COMMENT Controls number of record samples to check condition selectivity NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 4294967295 -@@ -2294,17 +2294,17 @@ READ_ONLY NO +@@ -2304,17 +2304,17 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_TRACE_MAX_MEM_SIZE VARIABLE_SCOPE SESSION @@ -782,7 +795,7 @@ VARIABLE_COMMENT Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial join when it searches for the best execution plan Meaning: 1 - use selectivity of index backed range conditions to calculate the cardinality of a partial join if the last joined table is accessed by full table scan or an index scan, 2 - use selectivity of index backed range conditions to calculate the cardinality of a partial join in any case, 3 - additionally always use selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join, 4 - use histograms to calculate selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join.5 - additionally use selectivity of certain non-range predicates calculated on record samples NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 5 -@@ -2324,7 +2324,7 @@ READ_ONLY YES +@@ -2334,7 +2334,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME PERFORMANCE_SCHEMA_ACCOUNTS_SIZE VARIABLE_SCOPE GLOBAL @@ -791,7 +804,7 @@ VARIABLE_COMMENT Maximum number of instrumented user@host accounts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2334,7 +2334,7 @@ READ_ONLY YES +@@ -2344,7 +2344,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_DIGESTS_SIZE VARIABLE_SCOPE GLOBAL @@ -800,7 +813,7 @@ VARIABLE_COMMENT Size of the statement digest. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 200 -@@ -2344,7 +2344,7 @@ READ_ONLY YES +@@ -2354,7 +2354,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -809,7 +822,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_STAGES_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2354,7 +2354,7 @@ READ_ONLY YES +@@ -2364,7 +2364,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -818,7 +831,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STAGES_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2364,7 +2364,7 @@ READ_ONLY YES +@@ -2374,7 +2374,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STATEMENTS_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -827,7 +840,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_STATEMENTS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2374,7 +2374,7 @@ READ_ONLY YES +@@ -2384,7 +2384,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STATEMENTS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -836,7 +849,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STATEMENTS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2384,7 +2384,7 @@ READ_ONLY YES +@@ -2394,7 +2394,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -845,7 +858,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_WAITS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2394,7 +2394,7 @@ READ_ONLY YES +@@ -2404,7 +2404,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -854,7 +867,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_WAITS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2404,7 +2404,7 @@ READ_ONLY YES +@@ -2414,7 +2414,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_HOSTS_SIZE VARIABLE_SCOPE GLOBAL @@ -863,7 +876,7 @@ VARIABLE_COMMENT Maximum number of instrumented hosts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2414,7 +2414,7 @@ READ_ONLY YES +@@ -2424,7 +2424,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_COND_CLASSES VARIABLE_SCOPE GLOBAL @@ -872,7 +885,7 @@ VARIABLE_COMMENT Maximum number of condition instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2424,7 +2424,7 @@ READ_ONLY YES +@@ -2434,7 +2434,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_COND_INSTANCES VARIABLE_SCOPE GLOBAL @@ -881,7 +894,7 @@ VARIABLE_COMMENT Maximum number of instrumented condition objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2434,7 +2434,7 @@ READ_ONLY YES +@@ -2444,7 +2444,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_DIGEST_LENGTH VARIABLE_SCOPE GLOBAL @@ -890,7 +903,7 @@ VARIABLE_COMMENT Maximum length considered for digest text, when stored in performance_schema tables. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2444,7 +2444,7 @@ READ_ONLY YES +@@ -2454,7 +2454,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_CLASSES VARIABLE_SCOPE GLOBAL @@ -899,7 +912,7 @@ VARIABLE_COMMENT Maximum number of file instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2454,7 +2454,7 @@ READ_ONLY YES +@@ -2464,7 +2464,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_HANDLES VARIABLE_SCOPE GLOBAL @@ -908,7 +921,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented files. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2464,7 +2464,7 @@ READ_ONLY YES +@@ -2474,7 +2474,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_INSTANCES VARIABLE_SCOPE GLOBAL @@ -917,7 +930,7 @@ VARIABLE_COMMENT Maximum number of instrumented files. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2474,7 +2474,7 @@ READ_ONLY YES +@@ -2484,7 +2484,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MUTEX_CLASSES VARIABLE_SCOPE GLOBAL @@ -926,7 +939,7 @@ VARIABLE_COMMENT Maximum number of mutex instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2484,7 +2484,7 @@ READ_ONLY YES +@@ -2494,7 +2494,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES VARIABLE_SCOPE GLOBAL @@ -935,7 +948,7 @@ VARIABLE_COMMENT Maximum number of instrumented MUTEX objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2494,7 +2494,7 @@ READ_ONLY YES +@@ -2504,7 +2504,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_RWLOCK_CLASSES VARIABLE_SCOPE GLOBAL @@ -944,7 +957,7 @@ VARIABLE_COMMENT Maximum number of rwlock instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2504,7 +2504,7 @@ READ_ONLY YES +@@ -2514,7 +2514,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES VARIABLE_SCOPE GLOBAL @@ -953,7 +966,7 @@ VARIABLE_COMMENT Maximum number of instrumented RWLOCK objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2514,7 +2514,7 @@ READ_ONLY YES +@@ -2524,7 +2524,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SOCKET_CLASSES VARIABLE_SCOPE GLOBAL @@ -962,7 +975,7 @@ VARIABLE_COMMENT Maximum number of socket instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2524,7 +2524,7 @@ READ_ONLY YES +@@ -2534,7 +2534,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SOCKET_INSTANCES VARIABLE_SCOPE GLOBAL @@ -971,7 +984,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented sockets. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2534,7 +2534,7 @@ READ_ONLY YES +@@ -2544,7 +2544,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STAGE_CLASSES VARIABLE_SCOPE GLOBAL @@ -980,7 +993,7 @@ VARIABLE_COMMENT Maximum number of stage instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2544,7 +2544,7 @@ READ_ONLY YES +@@ -2554,7 +2554,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES VARIABLE_SCOPE GLOBAL @@ -989,7 +1002,7 @@ VARIABLE_COMMENT Maximum number of statement instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2554,7 +2554,7 @@ READ_ONLY YES +@@ -2564,7 +2564,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES VARIABLE_SCOPE GLOBAL @@ -998,7 +1011,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2564,7 +2564,7 @@ READ_ONLY YES +@@ -2574,7 +2574,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1007,7 +1020,7 @@ VARIABLE_COMMENT Maximum number of instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2574,7 +2574,7 @@ READ_ONLY YES +@@ -2584,7 +2584,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_THREAD_CLASSES VARIABLE_SCOPE GLOBAL @@ -1016,7 +1029,7 @@ VARIABLE_COMMENT Maximum number of thread instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2584,7 +2584,7 @@ READ_ONLY YES +@@ -2594,7 +2594,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1034,7 +1047,7 @@ VARIABLE_COMMENT Size of session attribute string buffer per thread. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2604,7 +2604,7 @@ READ_ONLY YES +@@ -2614,7 +2614,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SETUP_ACTORS_SIZE VARIABLE_SCOPE GLOBAL @@ -1043,7 +1056,7 @@ VARIABLE_COMMENT Maximum number of rows in SETUP_ACTORS. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1024 -@@ -2614,7 +2614,7 @@ READ_ONLY YES +@@ -2624,7 +2624,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SETUP_OBJECTS_SIZE VARIABLE_SCOPE GLOBAL @@ -1052,7 +1065,7 @@ VARIABLE_COMMENT Maximum number of rows in SETUP_OBJECTS. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2624,7 +2624,7 @@ READ_ONLY YES +@@ -2634,7 +2634,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_USERS_SIZE VARIABLE_SCOPE GLOBAL @@ -1061,7 +1074,7 @@ VARIABLE_COMMENT Maximum number of instrumented users. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2674,7 +2674,7 @@ READ_ONLY YES +@@ -2684,7 +2684,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PRELOAD_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1070,7 +1083,7 @@ VARIABLE_COMMENT The size of the buffer that is allocated when preloading indexes NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 -@@ -2694,7 +2694,7 @@ READ_ONLY NO +@@ -2704,7 +2704,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME PROFILING_HISTORY_SIZE VARIABLE_SCOPE SESSION @@ -1079,7 +1092,7 @@ VARIABLE_COMMENT Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See SHOW PROFILES. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 100 -@@ -2704,7 +2704,7 @@ READ_ONLY NO +@@ -2714,7 +2714,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PROGRESS_REPORT_TIME VARIABLE_SCOPE SESSION @@ -1088,7 +1101,7 @@ VARIABLE_COMMENT Seconds between sending progress reports to the client for time-consuming statements. Set to 0 to disable progress reporting. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2764,7 +2764,7 @@ READ_ONLY NO +@@ -2774,7 +2774,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME QUERY_ALLOC_BLOCK_SIZE VARIABLE_SCOPE SESSION @@ -1097,7 +1110,7 @@ VARIABLE_COMMENT Allocation block size for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 -@@ -2774,7 +2774,7 @@ READ_ONLY NO +@@ -2784,7 +2784,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_LIMIT VARIABLE_SCOPE GLOBAL @@ -1106,7 +1119,7 @@ VARIABLE_COMMENT Don't cache results that are bigger than this NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2784,7 +2784,7 @@ READ_ONLY NO +@@ -2794,7 +2794,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_MIN_RES_UNIT VARIABLE_SCOPE GLOBAL @@ -1115,7 +1128,7 @@ VARIABLE_COMMENT The minimum size for blocks allocated by the query cache NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2797,7 +2797,7 @@ VARIABLE_SCOPE GLOBAL +@@ -2807,7 +2807,7 @@ VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The memory allocated to store results from old queries NUMERIC_MIN_VALUE 0 @@ -1124,7 +1137,7 @@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -2834,7 +2834,7 @@ READ_ONLY NO +@@ -2844,7 +2844,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME QUERY_PREALLOC_SIZE VARIABLE_SCOPE SESSION @@ -1133,7 +1146,7 @@ VARIABLE_COMMENT Persistent buffer for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 -@@ -2847,7 +2847,7 @@ VARIABLE_SCOPE SESSION ONLY +@@ -2857,7 +2857,7 @@ VARIABLE_SCOPE SESSION ONLY VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@ -1142,7 +1155,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -2857,14 +2857,14 @@ VARIABLE_SCOPE SESSION ONLY +@@ -2867,14 +2867,14 @@ VARIABLE_SCOPE SESSION ONLY VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@ -1159,7 +1172,7 @@ VARIABLE_COMMENT Allocation block size for storing ranges during optimization NUMERIC_MIN_VALUE 4096 NUMERIC_MAX_VALUE 4294967295 -@@ -2874,7 +2874,7 @@ READ_ONLY NO +@@ -2884,7 +2884,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME READ_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1168,7 +1181,7 @@ VARIABLE_COMMENT Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value NUMERIC_MIN_VALUE 8192 NUMERIC_MAX_VALUE 2147483647 -@@ -2894,7 +2894,7 @@ READ_ONLY NO +@@ -2904,7 +2904,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME READ_RND_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1177,7 +1190,7 @@ VARIABLE_COMMENT When reading rows in sorted order after a sort, the rows are read through this buffer to avoid a disk seeks NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 2147483647 -@@ -2904,10 +2904,10 @@ READ_ONLY NO +@@ -2914,10 +2914,10 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME ROWID_MERGE_BUFF_SIZE VARIABLE_SCOPE SESSION @@ -1190,7 +1203,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -2944,7 +2944,7 @@ READ_ONLY YES +@@ -2954,7 +2954,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SERVER_ID VARIABLE_SCOPE SESSION @@ -1199,7 +1212,7 @@ VARIABLE_COMMENT Uniquely identifies the server instance in the community of replication partners NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 4294967295 -@@ -3014,7 +3014,7 @@ READ_ONLY NO +@@ -3024,7 +3024,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME SLAVE_MAX_ALLOWED_PACKET VARIABLE_SCOPE GLOBAL @@ -1208,7 +1221,7 @@ VARIABLE_COMMENT The maximum packet length to sent successfully from the master to slave. NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 -@@ -3024,7 +3024,7 @@ READ_ONLY NO +@@ -3034,7 +3034,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLOW_LAUNCH_TIME VARIABLE_SCOPE GLOBAL @@ -1217,7 +1230,7 @@ VARIABLE_COMMENT If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 31536000 -@@ -3067,7 +3067,7 @@ VARIABLE_SCOPE SESSION +@@ -3077,7 +3077,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Each thread that needs to do a sort allocates a buffer of this size NUMERIC_MIN_VALUE 1024 @@ -1226,7 +1239,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3274,7 +3274,7 @@ READ_ONLY NO +@@ -3284,7 +3284,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME STORED_PROGRAM_CACHE VARIABLE_SCOPE GLOBAL @@ -1235,7 +1248,7 @@ VARIABLE_COMMENT The soft upper limit for number of cached stored routines for one connection. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 524288 -@@ -3354,7 +3354,7 @@ READ_ONLY NO +@@ -3364,7 +3364,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME TABLE_DEFINITION_CACHE VARIABLE_SCOPE GLOBAL @@ -1244,7 +1257,7 @@ VARIABLE_COMMENT The number of cached table definitions NUMERIC_MIN_VALUE 400 NUMERIC_MAX_VALUE 2097152 -@@ -3364,7 +3364,7 @@ READ_ONLY NO +@@ -3374,7 +3374,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TABLE_OPEN_CACHE VARIABLE_SCOPE GLOBAL @@ -1253,7 +1266,7 @@ VARIABLE_COMMENT The number of cached open tables NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 1048576 -@@ -3424,7 +3424,7 @@ READ_ONLY NO +@@ -3434,7 +3434,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME THREAD_CACHE_SIZE VARIABLE_SCOPE GLOBAL @@ -1262,7 +1275,7 @@ VARIABLE_COMMENT How many threads we should keep in a cache for reuse. These are freed after 5 minutes of idle time NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16384 -@@ -3434,7 +3434,7 @@ READ_ONLY NO +@@ -3444,7 +3444,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME THREAD_CONCURRENCY VARIABLE_SCOPE GLOBAL @@ -1271,7 +1284,7 @@ VARIABLE_COMMENT Permits the application to give the threads system a hint for the desired number of threads that should be run at the same time.This variable has no effect, and is deprecated. It will be removed in a future release. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 512 -@@ -3527,7 +3527,7 @@ VARIABLE_SCOPE SESSION +@@ -3537,7 +3537,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Max size for data for an internal temporary on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 1024 @@ -1280,7 +1293,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3537,7 +3537,7 @@ VARIABLE_SCOPE SESSION +@@ -3547,7 +3547,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. Same as tmp_table_size. NUMERIC_MIN_VALUE 1024 @@ -1289,7 +1302,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3547,14 +3547,14 @@ VARIABLE_SCOPE SESSION +@@ -3557,14 +3557,14 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Alias for tmp_memory_table_size. If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 1024 @@ -1306,7 +1319,7 @@ VARIABLE_COMMENT Allocation block size for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 -@@ -3564,7 +3564,7 @@ READ_ONLY NO +@@ -3574,7 +3574,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TRANSACTION_PREALLOC_SIZE VARIABLE_SCOPE SESSION @@ -1315,7 +1328,7 @@ VARIABLE_COMMENT Persistent buffer for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 -@@ -3704,7 +3704,7 @@ READ_ONLY YES +@@ -3714,7 +3714,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME WAIT_TIMEOUT VARIABLE_SCOPE SESSION @@ -1324,7 +1337,7 @@ VARIABLE_COMMENT The number of seconds the server waits for activity on a connection before closing it NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 31536000 -@@ -3731,7 +3731,7 @@ order by variable_name; +@@ -3741,7 +3741,7 @@ order by variable_name; VARIABLE_NAME LOG_TC_SIZE GLOBAL_VALUE_ORIGIN AUTO VARIABLE_SCOPE GLOBAL

1 0

[Commits] eb75e8705d9: MDEV-8134: The relay-log is not flushed after the slave-relay-log.999999 showed
by sujatha 21 Jan '21

21 Jan '21

revision-id: eb75e8705d9a444e10057967eaebf947b1115ff8 (mariadb-10.2.31-695-geb75e8705d9) parent(s): 53acd1c1d88be82190c56af3e4cc11fb2770a169 author: Sujatha committer: Sujatha timestamp: 2021-01-21 13:00:02 +0530 message: MDEV-8134: The relay-log is not flushed after the slave-relay-log.999999 showed Problem: ======== Auto purge of relaylogs stops when relay-log-file is 'slave-relay-log.999999' and slave_parallel_threads is enabled. Analysis: ========= The problem is that in Relay_log_info::inc_group_relay_log_pos() function, when two log names are compared via strcmp() function, it gives correct result, when log name sequence numbers are of same digits(6 digits), But when the number goes to 7 digits, a 999999 compares greater than 1000000, which is wrong, hence the bug. Fix: ==== Extract the numeric extension part of the file name, convert it into unsigned long and compare. Thanks to David Zhao for the contribution. --- .../suite/rpl/r/rpl_relay_max_extension.result | 37 +++++++ .../suite/rpl/t/rpl_relay_max_extension.test | 109 +++++++++++++++++++++ sql/rpl_parallel.cc | 5 +- sql/rpl_rli.cc | 4 +- sql/sql_repl.cc | 17 ++++ sql/sql_repl.h | 1 + 6 files changed, 169 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_relay_max_extension.result b/mysql-test/suite/rpl/r/rpl_relay_max_extension.result new file mode 100644 index 00000000000..4444398203e --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_relay_max_extension.result @@ -0,0 +1,37 @@ +include/rpl_init.inc [topology=1->2] +connection server_2; +include/stop_slave.inc +RESET SLAVE; +include/start_slave.inc +include/stop_slave.inc +# +# Stop slave server +# +# +# Simulate file number get close to 999997 +# by renaming relay logs and modifying index/info files +# +# Restart slave server +# +SET @save_slave_parallel_threads= @@GLOBAL.slave_parallel_threads; +SET @save_max_relay_log_size= @@GLOBAL.max_relay_log_size; +SET GLOBAL slave_parallel_threads=1; +SET GLOBAL max_relay_log_size=100 * 1024; +include/start_slave.inc +connection server_1; +create table t1 (i int, c varchar(1024)); +# +# Insert some data to generate enough amount of binary logs +# +connection server_2; +# +# Assert that 'slave-relay-bin.999999' is purged. +# +NOT FOUND /slave-relay-bin.999999/ in slave-relay-bin.index +include/stop_slave.inc +SET GLOBAL slave_parallel_threads= @save_slave_parallel_threads; +SET GLOBAL max_relay_log_size= @save_max_relay_log_size; +include/start_slave.inc +connection server_1; +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_relay_max_extension.test b/mysql-test/suite/rpl/t/rpl_relay_max_extension.test new file mode 100644 index 00000000000..e1e087f2e0e --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_relay_max_extension.test @@ -0,0 +1,109 @@ +# ==== Purpose ==== +# +# Test verifies that auto purging mechanism of relay logs works fine when the +# file extension grows beyond 999999. +# +# ==== Implementation ==== +# +# Steps: +# 0 - In master-slave setup clear all the relay logs on the slave server. +# 1 - Start the slave so that new relay logs starting from +# 'slave-relay-bin.000001' are created. +# 2 - Get the active relay-log file name by using SHOW SLAVE STATUS. +# Shutdown the slave server. +# 3 - Rename active relay log to '999997' in both 'relay-log.info' and +# 'slave-relay-bin.index' files. +# 4 - Restart the slave server by configuring 'slave_parallel_threads=1' +# and 'max_relay_log_size=100K'. +# 5 - Generate load on master such that few relay logs are generated on +# slave. The relay log sequence number will change to 7 digits. +# 6 - Sync slave with master to ensure that relay logs are applied on +# slave. They should have been automatically purged. +# 7 - Assert that there is no 'slave-relay-bin.999999' file in +# 'relay-log.info'. +# +# ==== References ==== +# +# MDEV-8134: The relay-log is not flushed after the slave-relay-log.999999 +# showed +# + +--source include/have_innodb.inc +--source include/have_binlog_format_row.inc +--let $rpl_topology=1->2 +--source include/rpl_init.inc + +--connection server_2 +--source include/stop_slave.inc +RESET SLAVE; +--source include/start_slave.inc +--source include/stop_slave.inc +--let $relay_log=query_get_value(SHOW SLAVE STATUS, Relay_Log_File, 1) + +--echo # +--echo # Stop slave server +--echo # + +--let $datadir = `select @@datadir` +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.2.expect +--shutdown_server 10 +--source include/wait_until_disconnected.inc + +--exec sed -i "s/$relay_log/slave-relay-bin.999997/g" $datadir/relay-log.info +--exec sed -i "s/$relay_log/slave-relay-bin.999997/g" $datadir/slave-relay-bin.index + +--echo # +--echo # Simulate file number get close to 999997 +--echo # by renaming relay logs and modifying index/info files + +--move_file $datadir/$relay_log $datadir/slave-relay-bin.999997 + +--echo # +--echo # Restart slave server +--echo # + +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.2.expect +--enable_reconnect +--source include/wait_until_connected_again.inc +SET @save_slave_parallel_threads= @@GLOBAL.slave_parallel_threads; +SET @save_max_relay_log_size= @@GLOBAL.max_relay_log_size; + +SET GLOBAL slave_parallel_threads=1; +SET GLOBAL max_relay_log_size=100 * 1024; +--source include/start_slave.inc + +--connection server_1 +create table t1 (i int, c varchar(1024)); +--echo # +--echo # Insert some data to generate enough amount of binary logs +--echo # +--let $count = 1000 +--disable_query_log +while ($count) +{ + eval insert into t1 values (1001 - $count, repeat('a',1000)); + dec $count; +} +--enable_query_log +--save_master_pos + +--connection server_2 +--sync_with_master + +--let $relay_log=query_get_value(SHOW SLAVE STATUS, Relay_Log_File, 1) + +--echo # +--echo # Assert that 'slave-relay-bin.999999' is purged. +--echo # +let SEARCH_FILE=$datadir/slave-relay-bin.index; +let SEARCH_PATTERN=slave-relay-bin.999999; +source include/search_pattern_in_file.inc; + +--source include/stop_slave.inc +SET GLOBAL slave_parallel_threads= @save_slave_parallel_threads; +SET GLOBAL max_relay_log_size= @save_max_relay_log_size; +--source include/start_slave.inc + +--connection server_1 +DROP TABLE t1; +--source include/rpl_end.inc diff --git a/sql/rpl_parallel.cc b/sql/rpl_parallel.cc index 4cf87ba73b7..869640fd46f 100644 --- a/sql/rpl_parallel.cc +++ b/sql/rpl_parallel.cc @@ -4,6 +4,7 @@ #include "rpl_mi.h" #include "sql_parse.h" #include "debug_sync.h" +#include "sql_repl.h" /* Code for optional parallel execution of replicated events on the slave. @@ -82,7 +83,7 @@ handle_queued_pos_update(THD *thd, rpl_parallel_thread::queued_event *qev) return; mysql_mutex_lock(&rli->data_lock); - cmp= strcmp(rli->group_relay_log_name, qev->event_relay_log_name); + cmp= compare_log_name(rli->group_relay_log_name, qev->event_relay_log_name); if (cmp < 0) { rli->group_relay_log_pos= qev->future_event_relay_log_pos; @@ -91,7 +92,7 @@ handle_queued_pos_update(THD *thd, rpl_parallel_thread::queued_event *qev) rli->group_relay_log_pos < qev->future_event_relay_log_pos) rli->group_relay_log_pos= qev->future_event_relay_log_pos; - cmp= strcmp(rli->group_master_log_name, qev->future_event_master_log_name); + cmp= compare_log_name(rli->group_master_log_name, qev->future_event_master_log_name); if (cmp < 0) { strcpy(rli->group_master_log_name, qev->future_event_master_log_name); diff --git a/sql/rpl_rli.cc b/sql/rpl_rli.cc index 40ab375571a..5273b33c728 100644 --- a/sql/rpl_rli.cc +++ b/sql/rpl_rli.cc @@ -989,7 +989,7 @@ void Relay_log_info::inc_group_relay_log_pos(ulonglong log_pos, if (rgi->is_parallel_exec) { /* In case of parallel replication, do not update the position backwards. */ - int cmp= strcmp(group_relay_log_name, rgi->event_relay_log_name); + int cmp= compare_log_name(group_relay_log_name, rgi->event_relay_log_name); if (cmp < 0) { group_relay_log_pos= rgi->future_event_relay_log_pos; @@ -1001,7 +1001,7 @@ void Relay_log_info::inc_group_relay_log_pos(ulonglong log_pos, In the parallel case we need to update the master_log_name here, rather than in Rotate_log_event::do_update_pos(). */ - cmp= strcmp(group_master_log_name, rgi->future_event_master_log_name); + cmp= compare_log_name(group_master_log_name, rgi->future_event_master_log_name); if (cmp <= 0) { if (cmp < 0) diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc index 4af8ebc2dd8..59a3f686e45 100644 --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -4541,5 +4541,22 @@ rpl_gtid_pos_update(THD *thd, char *str, size_t len) return false; } +int compare_log_name(const char *log_1, const char *log_2) { + int res= 1; + const char *ext1_str= strrchr(log_1, '.'); + const char *ext2_str= strrchr(log_2, '.'); + char file_name_1[255], file_name_2[255]; + strmake(file_name_1, log_1, (ext1_str - log_1)); + strmake(file_name_2, log_2, (ext2_str - log_2)); + char *endptr = NULL; + res= strcmp(file_name_1, file_name_2); + if (!res) + { + ulong ext1= strtoul(++ext1_str, &endptr, 10); + ulong ext2= strtoul(++ext2_str, &endptr, 10); + res= (ext1 > ext2 ? 1 : ((ext1 == ext2) ? 0 : -1)); + } + return res; +} #endif /* HAVE_REPLICATION */ diff --git a/sql/sql_repl.h b/sql/sql_repl.h index 8ddfa9239f6..9129aaeed5e 100644 --- a/sql/sql_repl.h +++ b/sql/sql_repl.h @@ -56,6 +56,7 @@ bool show_binlogs(THD* thd); extern int init_master_info(Master_info* mi); void kill_zombie_dump_threads(uint32 slave_server_id); int check_binlog_magic(IO_CACHE* log, const char** errmsg); +int compare_log_name(const char *log_1, const char *log_2); struct LOAD_FILE_IO_CACHE : public IO_CACHE {

1 0

[Commits] 962624a3f82: MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index
by varun 20 Jan '21

20 Jan '21

revision-id: 962624a3f82f36f13551afc049eab903f82de6e0 (mariadb-10.2.31-679-g962624a3f82) parent(s): a87320e335b6345f1ce04eea8b790a741c1ec23e author: Varun Gupta committer: Varun Gupta timestamp: 2021-01-20 17:20:53 +0530 message: MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index The issue happens when the secondary keys are extended with primary key parts. Inside the function TABLE_SHARE::init_from_binary_frm_image() adds the length bytes for the primary key key parts to the length of the secondary key. This is not needed because when the extended keys are used we recalculate the length for the used key parts. --- mysql-test/r/innodb_ext_key.result | 16 ++++++++++++++++ mysql-test/t/innodb_ext_key.test | 19 +++++++++++++++++++ sql/table.cc | 3 ++- 3 files changed, 37 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result index c7a5b1a80ca..880d7a8bceb 100644 --- a/mysql-test/r/innodb_ext_key.result +++ b/mysql-test/r/innodb_ext_key.result @@ -790,3 +790,19 @@ EXPLAIN } drop table t1; SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 ( +pk VARCHAR(50), +a VARCHAR(20), +KEY k1(a), +PRIMARY KEY(pk) +)ENGINE=INNODB; +INSERT INTO t2 SELECT a,a FROM t1; +EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL k1 23 NULL 10 Using index +DROP TABLE t1,t2; diff --git a/mysql-test/t/innodb_ext_key.test b/mysql-test/t/innodb_ext_key.test index f5edd736490..de9ca10ff1b 100644 --- a/mysql-test/t/innodb_ext_key.test +++ b/mysql-test/t/innodb_ext_key.test @@ -606,3 +606,22 @@ explain format= json select * from t1 force index(k1) where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and f1 <= '3'; drop table t1; SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index +--echo # + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +CREATE TABLE t2 ( + pk VARCHAR(50), + a VARCHAR(20), + KEY k1(a), + PRIMARY KEY(pk) +)ENGINE=INNODB; + +INSERT INTO t2 SELECT a,a FROM t1; +EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); + +DROP TABLE t1,t2; diff --git a/sql/table.cc b/sql/table.cc index e4492f21a30..04bede3f8a3 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -2285,7 +2285,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, else key_part->key_part_flag|= HA_VAR_LENGTH_PART; key_part->store_length+=HA_KEY_BLOB_LENGTH; - keyinfo->key_length+= HA_KEY_BLOB_LENGTH; + if (i < keyinfo->user_defined_key_parts) + keyinfo->key_length+= HA_KEY_BLOB_LENGTH; } if (field->type() == MYSQL_TYPE_BIT) key_part->key_part_flag|= HA_BIT_PART;

1 0

[Commits] 064f7c2c28c: Update test results
by psergey 20 Jan '21

20 Jan '21

revision-id: 064f7c2c28cdaf8198e436d76e05c8bd9abed802 (mariadb-10.4.11-426-g064f7c2c28c) parent(s): 3bbb233bf5c3de11a623905bd1678ffe5553b6e9 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-20 14:18:04 +0300 message: Update test results --- .../sys_vars/r/sysvars_server_embedded.result | 10 ++ .../r/sysvars_server_notembedded,32bit.rdiff | 153 +++++++++++---------- 2 files changed, 93 insertions(+), 70 deletions(-) diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index d2e1710a298..870da865c2a 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -2242,6 +2242,16 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME OPTIMIZER_MAX_SEL_ARG_WEIGHT +VARIABLE_SCOPE SESSION +VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_COMMENT The maximum weight of the SEL_ARG graph. Set to 0 for no limit +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_PRUNE_LEVEL VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff index bfe56dbc1a9..17f6b35ef10 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded,32bit.rdiff @@ -734,7 +734,20 @@ VARIABLE_COMMENT If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 or autoset then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of file descriptors NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2404,7 +2404,7 @@ READ_ONLY YES +@@ -2404,10 +2404,10 @@ + COMMAND_LINE_ARGUMENT REQUIRED + VARIABLE_NAME OPTIMIZER_MAX_SEL_ARG_WEIGHT + VARIABLE_SCOPE SESSION +-VARIABLE_TYPE BIGINT UNSIGNED ++VARIABLE_TYPE INT UNSIGNED + VARIABLE_COMMENT The maximum weight of the SEL_ARG graph. Set to 0 for no limit + NUMERIC_MIN_VALUE 0 +-NUMERIC_MAX_VALUE 18446744073709551615 ++NUMERIC_MAX_VALUE 4294967295 + NUMERIC_BLOCK_SIZE 1 + ENUM_VALUE_LIST NULL + READ_ONLY NO +@@ -2414,7 +2414,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_PRUNE_LEVEL VARIABLE_SCOPE SESSION @@ -743,7 +756,7 @@ VARIABLE_COMMENT Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from the optimizer search space. Meaning: 0 - do not apply any heuristic, thus perform exhaustive search; 1 - prune plans based on number of retrieved rows NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1 -@@ -2414,7 +2414,7 @@ READ_ONLY NO +@@ -2424,7 +2424,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SEARCH_DEPTH VARIABLE_SCOPE SESSION @@ -752,7 +765,7 @@ VARIABLE_COMMENT Maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to compile a query. Values smaller than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will automatically pick a reasonable value. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 62 -@@ -2424,7 +2424,7 @@ READ_ONLY NO +@@ -2434,7 +2434,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SELECTIVITY_SAMPLING_LIMIT VARIABLE_SCOPE SESSION @@ -761,7 +774,7 @@ VARIABLE_COMMENT Controls number of record samples to check condition selectivity NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 4294967295 -@@ -2454,17 +2454,17 @@ READ_ONLY NO +@@ -2464,17 +2464,17 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_TRACE_MAX_MEM_SIZE VARIABLE_SCOPE SESSION @@ -782,7 +795,7 @@ VARIABLE_COMMENT Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial join when it searches for the best execution plan Meaning: 1 - use selectivity of index backed range conditions to calculate the cardinality of a partial join if the last joined table is accessed by full table scan or an index scan, 2 - use selectivity of index backed range conditions to calculate the cardinality of a partial join in any case, 3 - additionally always use selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join, 4 - use histograms to calculate selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join.5 - additionally use selectivity of certain non-range predicates calculated on record samples NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 5 -@@ -2484,7 +2484,7 @@ READ_ONLY YES +@@ -2494,7 +2494,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME PERFORMANCE_SCHEMA_ACCOUNTS_SIZE VARIABLE_SCOPE GLOBAL @@ -791,7 +804,7 @@ VARIABLE_COMMENT Maximum number of instrumented user@host accounts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2494,7 +2494,7 @@ READ_ONLY YES +@@ -2504,7 +2504,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_DIGESTS_SIZE VARIABLE_SCOPE GLOBAL @@ -809,7 +822,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_STAGES_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2514,7 +2514,7 @@ READ_ONLY YES +@@ -2524,7 +2524,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -818,7 +831,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STAGES_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2524,7 +2524,7 @@ READ_ONLY YES +@@ -2534,7 +2534,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STATEMENTS_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -827,7 +840,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_STATEMENTS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2534,7 +2534,7 @@ READ_ONLY YES +@@ -2544,7 +2544,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STATEMENTS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -836,7 +849,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_STATEMENTS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2544,7 +2544,7 @@ READ_ONLY YES +@@ -2554,7 +2554,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL @@ -845,7 +858,7 @@ VARIABLE_COMMENT Number of rows in EVENTS_WAITS_HISTORY_LONG. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2554,7 +2554,7 @@ READ_ONLY YES +@@ -2564,7 +2564,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE VARIABLE_SCOPE GLOBAL @@ -854,7 +867,7 @@ VARIABLE_COMMENT Number of rows per thread in EVENTS_WAITS_HISTORY. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1024 -@@ -2564,7 +2564,7 @@ READ_ONLY YES +@@ -2574,7 +2574,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_HOSTS_SIZE VARIABLE_SCOPE GLOBAL @@ -863,7 +876,7 @@ VARIABLE_COMMENT Maximum number of instrumented hosts. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2574,7 +2574,7 @@ READ_ONLY YES +@@ -2584,7 +2584,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_COND_CLASSES VARIABLE_SCOPE GLOBAL @@ -872,7 +885,7 @@ VARIABLE_COMMENT Maximum number of condition instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2584,7 +2584,7 @@ READ_ONLY YES +@@ -2594,7 +2594,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_COND_INSTANCES VARIABLE_SCOPE GLOBAL @@ -881,7 +894,7 @@ VARIABLE_COMMENT Maximum number of instrumented condition objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2594,7 +2594,7 @@ READ_ONLY YES +@@ -2604,7 +2604,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_DIGEST_LENGTH VARIABLE_SCOPE GLOBAL @@ -890,7 +903,7 @@ VARIABLE_COMMENT Maximum length considered for digest text, when stored in performance_schema tables. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2604,7 +2604,7 @@ READ_ONLY YES +@@ -2614,7 +2614,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_CLASSES VARIABLE_SCOPE GLOBAL @@ -899,7 +912,7 @@ VARIABLE_COMMENT Maximum number of file instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2614,7 +2614,7 @@ READ_ONLY YES +@@ -2624,7 +2624,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_HANDLES VARIABLE_SCOPE GLOBAL @@ -908,7 +921,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented files. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2624,7 +2624,7 @@ READ_ONLY YES +@@ -2634,7 +2634,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_FILE_INSTANCES VARIABLE_SCOPE GLOBAL @@ -917,7 +930,7 @@ VARIABLE_COMMENT Maximum number of instrumented files. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2634,7 +2634,7 @@ READ_ONLY YES +@@ -2644,7 +2644,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MUTEX_CLASSES VARIABLE_SCOPE GLOBAL @@ -926,7 +939,7 @@ VARIABLE_COMMENT Maximum number of mutex instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2644,7 +2644,7 @@ READ_ONLY YES +@@ -2654,7 +2654,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES VARIABLE_SCOPE GLOBAL @@ -935,7 +948,7 @@ VARIABLE_COMMENT Maximum number of instrumented MUTEX objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2654,7 +2654,7 @@ READ_ONLY YES +@@ -2664,7 +2664,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_RWLOCK_CLASSES VARIABLE_SCOPE GLOBAL @@ -944,7 +957,7 @@ VARIABLE_COMMENT Maximum number of rwlock instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2664,7 +2664,7 @@ READ_ONLY YES +@@ -2674,7 +2674,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES VARIABLE_SCOPE GLOBAL @@ -953,7 +966,7 @@ VARIABLE_COMMENT Maximum number of instrumented RWLOCK objects. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 104857600 -@@ -2674,7 +2674,7 @@ READ_ONLY YES +@@ -2684,7 +2684,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SOCKET_CLASSES VARIABLE_SCOPE GLOBAL @@ -962,7 +975,7 @@ VARIABLE_COMMENT Maximum number of socket instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2684,7 +2684,7 @@ READ_ONLY YES +@@ -2694,7 +2694,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_SOCKET_INSTANCES VARIABLE_SCOPE GLOBAL @@ -971,7 +984,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented sockets. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2694,7 +2694,7 @@ READ_ONLY YES +@@ -2704,7 +2704,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STAGE_CLASSES VARIABLE_SCOPE GLOBAL @@ -980,7 +993,7 @@ VARIABLE_COMMENT Maximum number of stage instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2704,7 +2704,7 @@ READ_ONLY YES +@@ -2714,7 +2714,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES VARIABLE_SCOPE GLOBAL @@ -989,7 +1002,7 @@ VARIABLE_COMMENT Maximum number of statement instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2714,7 +2714,7 @@ READ_ONLY YES +@@ -2724,7 +2724,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES VARIABLE_SCOPE GLOBAL @@ -998,7 +1011,7 @@ VARIABLE_COMMENT Maximum number of opened instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2724,7 +2724,7 @@ READ_ONLY YES +@@ -2734,7 +2734,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_TABLE_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1007,7 +1020,7 @@ VARIABLE_COMMENT Maximum number of instrumented tables. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2734,7 +2734,7 @@ READ_ONLY YES +@@ -2744,7 +2744,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_THREAD_CLASSES VARIABLE_SCOPE GLOBAL @@ -1016,7 +1029,7 @@ VARIABLE_COMMENT Maximum number of thread instruments. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 256 -@@ -2744,7 +2744,7 @@ READ_ONLY YES +@@ -2754,7 +2754,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES VARIABLE_SCOPE GLOBAL @@ -1025,7 +1038,7 @@ VARIABLE_COMMENT Maximum number of instrumented threads. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2754,7 +2754,7 @@ READ_ONLY YES +@@ -2764,7 +2764,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SESSION_CONNECT_ATTRS_SIZE VARIABLE_SCOPE GLOBAL @@ -1034,7 +1047,7 @@ VARIABLE_COMMENT Size of session attribute string buffer per thread. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2764,7 +2764,7 @@ READ_ONLY YES +@@ -2774,7 +2774,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SETUP_ACTORS_SIZE VARIABLE_SCOPE GLOBAL @@ -1043,7 +1056,7 @@ VARIABLE_COMMENT Maximum number of rows in SETUP_ACTORS. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1024 -@@ -2774,7 +2774,7 @@ READ_ONLY YES +@@ -2784,7 +2784,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_SETUP_OBJECTS_SIZE VARIABLE_SCOPE GLOBAL @@ -1052,7 +1065,7 @@ VARIABLE_COMMENT Maximum number of rows in SETUP_OBJECTS. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1048576 -@@ -2784,7 +2784,7 @@ READ_ONLY YES +@@ -2794,7 +2794,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_USERS_SIZE VARIABLE_SCOPE GLOBAL @@ -1061,7 +1074,7 @@ VARIABLE_COMMENT Maximum number of instrumented users. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2834,7 +2834,7 @@ READ_ONLY YES +@@ -2844,7 +2844,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PRELOAD_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1070,7 +1083,7 @@ VARIABLE_COMMENT The size of the buffer that is allocated when preloading indexes NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 -@@ -2854,7 +2854,7 @@ READ_ONLY NO +@@ -2864,7 +2864,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME PROFILING_HISTORY_SIZE VARIABLE_SCOPE SESSION @@ -1079,7 +1092,7 @@ VARIABLE_COMMENT Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See SHOW PROFILES. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 100 -@@ -2864,7 +2864,7 @@ READ_ONLY NO +@@ -2874,7 +2874,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PROGRESS_REPORT_TIME VARIABLE_SCOPE SESSION @@ -1088,7 +1101,7 @@ VARIABLE_COMMENT Seconds between sending progress reports to the client for time-consuming statements. Set to 0 to disable progress reporting. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2924,7 +2924,7 @@ READ_ONLY NO +@@ -2934,7 +2934,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME QUERY_ALLOC_BLOCK_SIZE VARIABLE_SCOPE SESSION @@ -1097,7 +1110,7 @@ VARIABLE_COMMENT Allocation block size for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 -@@ -2934,7 +2934,7 @@ READ_ONLY NO +@@ -2944,7 +2944,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_LIMIT VARIABLE_SCOPE GLOBAL @@ -1106,7 +1119,7 @@ VARIABLE_COMMENT Don't cache results that are bigger than this NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2944,7 +2944,7 @@ READ_ONLY NO +@@ -2954,7 +2954,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME QUERY_CACHE_MIN_RES_UNIT VARIABLE_SCOPE GLOBAL @@ -1115,7 +1128,7 @@ VARIABLE_COMMENT The minimum size for blocks allocated by the query cache NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -2957,7 +2957,7 @@ VARIABLE_SCOPE GLOBAL +@@ -2967,7 +2967,7 @@ VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The memory allocated to store results from old queries NUMERIC_MIN_VALUE 0 @@ -1124,7 +1137,7 @@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -2994,7 +2994,7 @@ READ_ONLY NO +@@ -3004,7 +3004,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME QUERY_PREALLOC_SIZE VARIABLE_SCOPE SESSION @@ -1133,7 +1146,7 @@ VARIABLE_COMMENT Persistent buffer for query parsing and execution NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 4294967295 -@@ -3007,7 +3007,7 @@ VARIABLE_SCOPE SESSION ONLY +@@ -3017,7 +3017,7 @@ VARIABLE_SCOPE SESSION ONLY VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@ -1142,7 +1155,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3017,14 +3017,14 @@ VARIABLE_SCOPE SESSION ONLY +@@ -3027,14 +3027,14 @@ VARIABLE_SCOPE SESSION ONLY VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Sets the internal state of the RAND() generator for replication purposes NUMERIC_MIN_VALUE 0 @@ -1159,7 +1172,7 @@ VARIABLE_COMMENT Allocation block size for storing ranges during optimization NUMERIC_MIN_VALUE 4096 NUMERIC_MAX_VALUE 4294967295 -@@ -3037,14 +3037,14 @@ VARIABLE_SCOPE GLOBAL +@@ -3047,14 +3047,14 @@ VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Maximum speed(KB/s) to read binlog from master (0 = no limit) NUMERIC_MIN_VALUE 0 @@ -1176,7 +1189,7 @@ VARIABLE_COMMENT Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value NUMERIC_MIN_VALUE 8192 NUMERIC_MAX_VALUE 2147483647 -@@ -3064,7 +3064,7 @@ READ_ONLY NO +@@ -3074,7 +3074,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME READ_RND_BUFFER_SIZE VARIABLE_SCOPE SESSION @@ -1185,7 +1198,7 @@ VARIABLE_COMMENT When reading rows in sorted order after a sort, the rows are read through this buffer to avoid a disk seeks NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 2147483647 -@@ -3264,10 +3264,10 @@ READ_ONLY YES +@@ -3274,10 +3274,10 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME ROWID_MERGE_BUFF_SIZE VARIABLE_SCOPE SESSION @@ -1198,7 +1211,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3284,20 +3284,20 @@ READ_ONLY NO +@@ -3294,20 +3294,20 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME RPL_SEMI_SYNC_MASTER_TIMEOUT VARIABLE_SCOPE GLOBAL @@ -1223,7 +1236,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3354,10 +3354,10 @@ READ_ONLY NO +@@ -3364,10 +3364,10 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME RPL_SEMI_SYNC_SLAVE_TRACE_LEVEL VARIABLE_SCOPE GLOBAL @@ -1236,7 +1249,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3394,7 +3394,7 @@ READ_ONLY YES +@@ -3404,7 +3404,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SERVER_ID VARIABLE_SCOPE SESSION @@ -1245,7 +1258,7 @@ VARIABLE_COMMENT Uniquely identifies the server instance in the community of replication partners NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 4294967295 -@@ -3524,7 +3524,7 @@ READ_ONLY NO +@@ -3534,7 +3534,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLAVE_DOMAIN_PARALLEL_THREADS VARIABLE_SCOPE GLOBAL @@ -1254,7 +1267,7 @@ VARIABLE_COMMENT Maximum number of parallel threads to use on slave for events in a single replication domain. When using multiple domains, this can be used to limit a single domain from grabbing all threads and thus stalling other domains. The default of 0 means to allow a domain to grab as many threads as it wants, up to the value of slave_parallel_threads. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16383 -@@ -3554,7 +3554,7 @@ READ_ONLY YES +@@ -3564,7 +3564,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLAVE_MAX_ALLOWED_PACKET VARIABLE_SCOPE GLOBAL @@ -1263,7 +1276,7 @@ VARIABLE_COMMENT The maximum packet length to sent successfully from the master to slave. NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 1073741824 -@@ -3574,7 +3574,7 @@ READ_ONLY NO +@@ -3584,7 +3584,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLAVE_PARALLEL_MAX_QUEUED VARIABLE_SCOPE GLOBAL @@ -1272,7 +1285,7 @@ VARIABLE_COMMENT Limit on how much memory SQL threads should use per parallel replication thread when reading ahead in the relay log looking for opportunities for parallel replication. Only used when --slave-parallel-threads > 0. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 2147483647 -@@ -3594,7 +3594,7 @@ READ_ONLY NO +@@ -3604,7 +3604,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME SLAVE_PARALLEL_THREADS VARIABLE_SCOPE GLOBAL @@ -1281,7 +1294,7 @@ VARIABLE_COMMENT If non-zero, number of threads to spawn to apply in parallel events on the slave that were group-committed on the master or were logged with GTID in different replication domains. Note that these threads are in addition to the IO and SQL threads, which are always created by a replication slave NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16383 -@@ -3604,7 +3604,7 @@ READ_ONLY NO +@@ -3614,7 +3614,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLAVE_PARALLEL_WORKERS VARIABLE_SCOPE GLOBAL @@ -1290,7 +1303,7 @@ VARIABLE_COMMENT Alias for slave_parallel_threads NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16383 -@@ -3644,7 +3644,7 @@ READ_ONLY NO +@@ -3654,7 +3654,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME SLAVE_TRANSACTION_RETRIES VARIABLE_SCOPE GLOBAL @@ -1299,7 +1312,7 @@ VARIABLE_COMMENT Number of times the slave SQL thread will retry a transaction in case it failed with a deadlock, elapsed lock wait timeout or listed in slave_transaction_retry_errors, before giving up and stopping NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -3664,7 +3664,7 @@ READ_ONLY YES +@@ -3674,7 +3674,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLAVE_TRANSACTION_RETRY_INTERVAL VARIABLE_SCOPE GLOBAL @@ -1308,7 +1321,7 @@ VARIABLE_COMMENT Interval of the slave SQL thread will retry a transaction in case it failed with a deadlock or elapsed lock wait timeout or listed in slave_transaction_retry_errors NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 3600 -@@ -3684,7 +3684,7 @@ READ_ONLY NO +@@ -3694,7 +3694,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLOW_LAUNCH_TIME VARIABLE_SCOPE GLOBAL @@ -1317,7 +1330,7 @@ VARIABLE_COMMENT If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 31536000 -@@ -3727,7 +3727,7 @@ VARIABLE_SCOPE SESSION +@@ -3737,7 +3737,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Each thread that needs to do a sort allocates a buffer of this size NUMERIC_MIN_VALUE 1024 @@ -1326,7 +1339,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -3944,7 +3944,7 @@ READ_ONLY NO +@@ -3954,7 +3954,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME STORED_PROGRAM_CACHE VARIABLE_SCOPE GLOBAL @@ -1335,7 +1348,7 @@ VARIABLE_COMMENT The soft upper limit for number of cached stored routines for one connection. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 524288 -@@ -4044,7 +4044,7 @@ READ_ONLY NO +@@ -4054,7 +4054,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME TABLE_DEFINITION_CACHE VARIABLE_SCOPE GLOBAL @@ -1344,7 +1357,7 @@ VARIABLE_COMMENT The number of cached table definitions NUMERIC_MIN_VALUE 400 NUMERIC_MAX_VALUE 2097152 -@@ -4054,7 +4054,7 @@ READ_ONLY NO +@@ -4064,7 +4064,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TABLE_OPEN_CACHE VARIABLE_SCOPE GLOBAL @@ -1353,7 +1366,7 @@ VARIABLE_COMMENT The number of cached open tables NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 1048576 -@@ -4114,7 +4114,7 @@ READ_ONLY NO +@@ -4124,7 +4124,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME THREAD_CACHE_SIZE VARIABLE_SCOPE GLOBAL @@ -1362,7 +1375,7 @@ VARIABLE_COMMENT How many threads we should keep in a cache for reuse. These are freed after 5 minutes of idle time NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16384 -@@ -4124,7 +4124,7 @@ READ_ONLY NO +@@ -4134,7 +4134,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME THREAD_CONCURRENCY VARIABLE_SCOPE GLOBAL @@ -1371,7 +1384,7 @@ VARIABLE_COMMENT Permits the application to give the threads system a hint for the desired number of threads that should be run at the same time.This variable has no effect, and is deprecated. It will be removed in a future release. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 512 -@@ -4287,7 +4287,7 @@ VARIABLE_SCOPE SESSION +@@ -4297,7 +4297,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Max size for data for an internal temporary on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 1024 @@ -1380,7 +1393,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -4297,7 +4297,7 @@ VARIABLE_SCOPE SESSION +@@ -4307,7 +4307,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. Same as tmp_table_size. NUMERIC_MIN_VALUE 1024 @@ -1389,7 +1402,7 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -4307,14 +4307,14 @@ VARIABLE_SCOPE SESSION +@@ -4317,14 +4317,14 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Alias for tmp_memory_table_size. If an internal in-memory temporary table exceeds this size, MariaDB will automatically convert it to an on-disk MyISAM or Aria table. NUMERIC_MIN_VALUE 1024 @@ -1406,7 +1419,7 @@ VARIABLE_COMMENT Allocation block size for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 -@@ -4324,7 +4324,7 @@ READ_ONLY NO +@@ -4334,7 +4334,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME TRANSACTION_PREALLOC_SIZE VARIABLE_SCOPE SESSION @@ -1415,7 +1428,7 @@ VARIABLE_COMMENT Persistent buffer for transactions to be stored in binary log NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 134217728 -@@ -4464,7 +4464,7 @@ READ_ONLY YES +@@ -4474,7 +4474,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME WAIT_TIMEOUT VARIABLE_SCOPE SESSION @@ -1424,7 +1437,7 @@ VARIABLE_COMMENT The number of seconds the server waits for activity on a connection before closing it NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 31536000 -@@ -4491,7 +4491,7 @@ order by variable_name; +@@ -4501,7 +4501,7 @@ order by variable_name; VARIABLE_NAME LOG_TC_SIZE GLOBAL_VALUE_ORIGIN AUTO VARIABLE_SCOPE GLOBAL

1 0

[Commits] 3bbb233bf5c: MDEV-9750: Quick memory exhaustion with 'extended_keys=on', part#6
by psergey 19 Jan '21

19 Jan '21

revision-id: 3bbb233bf5c3de11a623905bd1678ffe5553b6e9 (mariadb-10.4.11-425-g3bbb233bf5c) parent(s): 63e8f07e468c5cf44a0910e955887d01f87e7d96 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-19 19:11:21 +0300 message: MDEV-9750: Quick memory exhaustion with 'extended_keys=on', part#6 Move the heuristic as requested. Make it use the user-settable parameter --- mysql-test/main/range_notembedded.result | 27 +++++++++------- sql/opt_range.cc | 55 ++++++++++++++++++++++++++------ 2 files changed, 61 insertions(+), 21 deletions(-) diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result index 7d5dc41d7ed..82ae2eb3cd4 100644 --- a/mysql-test/main/range_notembedded.result +++ b/mysql-test/main/range_notembedded.result @@ -87,29 +87,32 @@ left(@json, 2500) [ { - "enforce_sel_arg_weight_limit": + "sel_arg_weight_heuristic": { - "index": "key1", - "old_weight": 110, - "new_weight": 10 + "key1_field": "kp1", + "key2_field": "kp2", + "key1_weight": 10, + "key2_weight": 10 } }, { - "enforce_sel_arg_weight_limit": + "sel_arg_weight_heuristic": { - "index": "key1", - "old_weight": 110, - "new_weight": 10 + "key1_field": "kp1", + "key2_field": "kp3", + "key1_weight": 10, + "key2_weight": 10 } }, { - "enforce_sel_arg_weight_limit": + "sel_arg_weight_heuristic": { - "index": "key1", - "old_weight": 110, - "new_weight": 10 + "key1_field": "kp1", + "key2_field": "kp4", + "key1_weight": 10, + "key2_weight": 10 } } ] diff --git a/sql/opt_range.cc b/sql/opt_range.cc index b67a85457c5..df9d6ba68e0 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -418,6 +418,9 @@ static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts); static SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, SEL_ARG *sel_arg); +static +bool sel_arg_and_weight_heuristic(RANGE_OPT_PARAM *param, SEL_ARG *key1, + SEL_ARG *key2); #include "opt_range_mrr.cc" @@ -9723,6 +9726,9 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, SEL_ARG *next; ulong use_count=key1->use_count; + if (sel_arg_and_weight_heuristic(param, key1, key2)) + return key1; + if (key1->elements != 1) { key2->use_count+=key1->elements-1; //psergey: why we don't count that key1 has n-k-p? @@ -9800,14 +9806,7 @@ key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) } // key1->part < key2->part - /* - Do not combine the trees if their total weight is likely to exceed the - MAX_WEIGHT. - (It is possible that key1 has next_key_part that has empty overlap with - key2. In this case, the combined tree will have a smaller weight than we - predict. We assume this is rare.) - */ - if (key1->weight + key1->elements*key2->weight > SEL_ARG::MAX_WEIGHT) + if (sel_arg_and_weight_heuristic(param, key1, key2)) return key1; key1->use_count--; @@ -10771,6 +10770,9 @@ void prune_sel_arg_graph(SEL_ARG *sel_arg, uint max_part) We start with maximum used keypart and then remove one keypart after another until the graph's weight is within the limit. + @seealso + sel_arg_and_weight_heuristic(); + @return tree pointer The tree after processing, NULL If it was not possible to reduce the weight of the tree below the @@ -10784,6 +10786,7 @@ SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, !param->thd->variables.optimizer_max_sel_arg_weight) return sel_arg; + Field *field= sel_arg->field; uint weight1= sel_arg->weight; while (1) @@ -10808,7 +10811,11 @@ SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, { Json_writer_object wrapper(param->thd); Json_writer_object obj(param->thd, "enforce_sel_arg_weight_limit"); - obj.add("index", param->table->key_info[param->real_keynr[keyno]].name); + if (param->using_real_indexes) + obj.add("index", param->table->key_info[param->real_keynr[keyno]].name); + else + obj.add("pseudo_index", field->field_name); + obj.add("old_weight", (longlong)weight1); obj.add("new_weight", (longlong)weight2); } @@ -10816,6 +10823,36 @@ SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, } +/* + @detail + Do not combine the trees if their total weight is likely to exceed the + MAX_WEIGHT. + (It is possible that key1 has next_key_part that has empty overlap with + key2. In this case, the combined tree will have a smaller weight than we + predict. We assume this is rare.) +*/ + +static +bool sel_arg_and_weight_heuristic(RANGE_OPT_PARAM *param, SEL_ARG *key1, + SEL_ARG *key2) +{ + DBUG_ASSERT(key1->part < key2->part); + + ulong max_weight= param->thd->variables.optimizer_max_sel_arg_weight; + if (max_weight && key1->weight + key1->elements*key2->weight > max_weight) + { + Json_writer_object wrapper(param->thd); + Json_writer_object obj(param->thd, "sel_arg_weight_heuristic"); + obj.add("key1_field", key1->field->field_name); + obj.add("key2_field", key2->field->field_name); + obj.add("key1_weight", (longlong)key1->weight); + obj.add("key2_weight", (longlong)key2->weight); + return true; // Discard key2 + } + return false; +} + + SEL_ARG * SEL_ARG::insert(SEL_ARG *key) {

1 0

[Commits] 70076f0: MDEV-16940 Server crashes in unsafe_key_update upon attempt to update view
by IgorBabaev 19 Jan '21

19 Jan '21

revision-id: 70076f0151fb8d218da73f81d23df549e27dff5a (mariadb-10.2.31-684-g70076f0) parent(s): cf6114ebea0224f265d1ac25592fce9b984b0e6e author: Igor Babaev committer: Igor Babaev timestamp: 2021-01-19 08:02:37 -0800 message: MDEV-16940 Server crashes in unsafe_key_update upon attempt to update view through 2nd execution of SP This bug caused a server crash on the second call of any stored procedure that contained an UPDATE statement over a multi-table view reporting an error message at the prepare stage. On the first call of the stored procedure after reporting an error at the preparation stage of the UPDATE statement finished without calling the function SELECT_LEX::save_prep_leaf_tables() for the SELECT used as the definition of the view. This left the SELECT_LEX structure used by the UPDATE statement in an inconsistent state for second call of the stored procedure. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/r/view.result | 16 ++++++++++++++++ mysql-test/t/view.test | 22 ++++++++++++++++++++++ sql/sql_lex.cc | 3 +++ sql/sql_update.cc | 3 +++ 4 files changed, 44 insertions(+) diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 0da11c7..e48a99f 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -6802,5 +6802,21 @@ drop database db1; create database test; use test; # +# MDEV-16940: update of multi-table view returning error used in SP +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (2), (3); +CREATE VIEW v1 AS SELECT a, b FROM t1,t2; +CREATE PROCEDURE sp1() UPDATE v1 SET a = 8, b = 9; +CALL sp1; +ERROR HY000: Can not modify more than one base table through a join view 'test.v1' +CALL sp1; +ERROR HY000: Can not modify more than one base table through a join view 'test.v1' +DROP PROCEDURE sp1; +DROP VIEW v1; +DROP TABLE t1, t2; +# # End of 10.2 tests # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 2486887..b5ce6a1 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -6522,5 +6522,27 @@ create database test; use test; --echo # +--echo # MDEV-16940: update of multi-table view returning error used in SP +--echo # + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (2), (3); + +CREATE VIEW v1 AS SELECT a, b FROM t1,t2; + +CREATE PROCEDURE sp1() UPDATE v1 SET a = 8, b = 9; + +--error ER_VIEW_MULTIUPDATE +CALL sp1; +--error ER_VIEW_MULTIUPDATE +CALL sp1; + +DROP PROCEDURE sp1; +DROP VIEW v1; +DROP TABLE t1, t2; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 77e6b2b..5059e4f 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4585,6 +4585,9 @@ bool LEX::save_prep_leaf_tables() bool st_select_lex::save_prep_leaf_tables(THD *thd) { + if (prep_leaf_list_state == SAVED) + return FALSE; + List_iterator_fast<TABLE_LIST> li(leaf_tables); TABLE_LIST *table; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 7454d16..01743a6 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1398,6 +1398,9 @@ bool Multiupdate_prelocking_strategy::handle_end(THD *thd) if (select_lex->handle_derived(thd->lex, DT_MERGE)) DBUG_RETURN(1); + if (thd->lex->save_prep_leaf_tables()) + DBUG_RETURN(1); + List<Item> *fields= &lex->select_lex.item_list; if (setup_fields_with_no_wrap(thd, Ref_ptr_array(), *fields, MARK_COLUMNS_WRITE, 0, 0))

1 0

[Commits] 63e8f07e468: MDEV-9750: Quick memory exhaustion with 'extended_keys=on', part#5
by psergey 19 Jan '21

19 Jan '21

revision-id: 63e8f07e468c5cf44a0910e955887d01f87e7d96 (mariadb-10.4.11-424-g63e8f07e468) parent(s): b6a5d3e87a8c33540d7cda3e4a512598fa5b1233 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-19 18:16:58 +0300 message: MDEV-9750: Quick memory exhaustion with 'extended_keys=on', part#5 Fix tests. --- mysql-test/main/mysqld--help.result | 4 + mysql-test/main/range.result | 172 --------------------- mysql-test/main/range.test | 67 -------- mysql-test/main/range_mrr_icp.result | 46 ------ mysql-test/main/range_notembedded.result | 172 +++++++++++++++++++++ mysql-test/main/range_notembedded.test | 71 +++++++++ .../sys_vars/r/sysvars_server_notembedded.result | 10 ++ 7 files changed, 257 insertions(+), 285 deletions(-) diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index 45b1e4afe05..abfd86f8d50 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -680,6 +680,9 @@ The following specify which files/extra groups are read (specified before remain max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of file descriptors (Automatically configured unless set explicitly) + --optimizer-max-sel-arg-weight=# + The maximum weight of the SEL_ARG graph. Set to 0 for no + limit --optimizer-prune-level=# Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from @@ -1595,6 +1598,7 @@ old-alter-table DEFAULT old-mode old-passwords FALSE old-style-user-limits FALSE +optimizer-max-sel-arg-weight 32000 optimizer-prune-level 1 optimizer-search-depth 62 optimizer-selectivity-sampling-limit 100 diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 982a70f94f8..b708628b625 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3135,178 +3135,6 @@ drop table t1,ten,t2; # # End of 10.2 tests # -# -# MDEV-9750: Quick memory exhaustion with 'extended_keys=on'... -# -create table t1 ( -kp1 int, -kp2 int, -kp3 int, -kp4 int, -key key1(kp1, kp2, kp3,kp4) -); -insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); -show variables like 'optimizer_max_sel_arg_weight'; -Variable_name Value -optimizer_max_sel_arg_weight 32000 -set @tmp_9750=@@optimizer_trace; -set optimizer_trace=1; -explain select * from t1 where -kp1 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and -kp2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and -kp3 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and -kp4 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) -; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index -set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) -from information_schema.optimizer_trace); -# This will show 3-component ranges. -# The ranges were produced, but the optimizer has cut away kp4 -# to keep the number of ranges at manageable level: -select left(@json, 500); -left(@json, 500) -[ - - [ - - { - "index": "key1", - "ranges": - [ - "(1,1,1) <= (kp1,kp2,kp3) <= (1,1,1)", - "(1,1,2) <= (kp1,kp2,kp3) <= (1,1,2)", - "(1,1,3) <= (kp1,kp2,kp3) <= (1,1,3)", - "(1,1,4) <= (kp1,kp2,kp3) <= (1,1,4)", - "(1,1,5) <= (kp1,kp2,kp3) <= (1,1,5)", - "(1,1,6) <= (kp1,kp2,kp3) <= (1,1,6)", - "(1,1,7) <= (kp1,kp2,kp3) <= (1,1,7)", - " -## Repeat the above with low max_weight: -set @tmp9750_weight=@@optimizer_max_sel_arg_weight; -set optimizer_max_sel_arg_weight=20; -explain select * from t1 where -kp1 in (1,2,3,4,5,6,7,8,9,10) and -kp2 in (1,2,3,4,5,6,7,8,9,10) and -kp3 in (1,2,3,4,5,6,7,8,9,10) and -kp4 in (1,2,3,4,5,6,7,8,9,10) -; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index -set @trace= (select trace from information_schema.optimizer_trace); -set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); -select left(@json, 500); -left(@json, 500) -[ - - [ - - { - "index": "key1", - "ranges": - [ - "(1) <= (kp1) <= (1)", - "(2) <= (kp1) <= (2)", - "(3) <= (kp1) <= (3)", - "(4) <= (kp1) <= (4)", - "(5) <= (kp1) <= (5)", - "(6) <= (kp1) <= (6)", - "(7) <= (kp1) <= (7)", - "(8) <= (kp1) <= (8)", - "(9) <= (kp1) <= (9)", - "(10) <= (kp1) <= (10)" - -set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); -select left(@json, 2500); -left(@json, 2500) -[ - - [ - - { - "enforce_sel_arg_weight_limit": - { - "index": "key1", - "old_weight": 110, - "new_weight": 10 - } - }, - - { - "enforce_sel_arg_weight_limit": - { - "index": "key1", - "old_weight": 110, - "new_weight": 10 - } - }, - - { - "enforce_sel_arg_weight_limit": - { - "index": "key1", - "old_weight": 110, - "new_weight": 10 - } - } - ] -] -## Repeat the above with a bit higher max_weight: -set @tmp9750_weight=@@optimizer_max_sel_arg_weight; -set optimizer_max_sel_arg_weight=120; -explain select * from t1 where -kp1 in (1,2,3,4,5,6,7,8,9,10) and -kp2 in (1,2,3,4,5,6,7,8,9,10) and -kp3 in (1,2,3,4,5,6,7,8,9,10) and -kp4 in (1,2,3,4,5,6,7,8,9,10) -; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index -set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) -from information_schema.optimizer_trace); -select left(@json, 1500); -left(@json, 1500) -[ - - [ - - { - "index": "key1", - "ranges": - [ - "(1,1) <= (kp1,kp2) <= (1,1)", - "(1,2) <= (kp1,kp2) <= (1,2)", - "(1,3) <= (kp1,kp2) <= (1,3)", - "(1,4) <= (kp1,kp2) <= (1,4)", - "(1,5) <= (kp1,kp2) <= (1,5)", - "(1,6) <= (kp1,kp2) <= (1,6)", - "(1,7) <= (kp1,kp2) <= (1,7)", - "(1,8) <= (kp1,kp2) <= (1,8)", - "(1,9) <= (kp1,kp2) <= (1,9)", - "(1,10) <= (kp1,kp2) <= (1,10)", - "(2,1) <= (kp1,kp2) <= (2,1)", - "(2,2) <= (kp1,kp2) <= (2,2)", - "(2,3) <= (kp1,kp2) <= (2,3)", - "(2,4) <= (kp1,kp2) <= (2,4)", - "(2,5) <= (kp1,kp2) <= (2,5)", - "(2,6) <= (kp1,kp2) <= (2,6)", - "(2,7) <= (kp1,kp2) <= (2,7)", - "(2,8) <= (kp1,kp2) <= (2,8)", - "(2,9) <= (kp1,kp2) <= (2,9)", - "(2,10) <= (kp1,kp2) <= (2,10)", - "(3,1) <= (kp1,kp2) <= (3,1)", - "(3,2) <= (kp1,kp2) <= (3,2)", - "(3,3) <= (kp1,kp2) <= (3,3)", - "(3,4) <= (kp1,kp2) <= (3,4)", - "(3,5) <= (kp1,kp2) <= (3,5)", - "(3,6) <= (kp1,kp2) <= (3,6)", - "(3,7) <= (kp1,kp2) <= (3,7)", - "(3,8) <= (kp1,kp2) <= (3,8)", - "(3,9) <= (kp1,kp2) <= (3,9)", - "(3,10) <= (kp1,kp2 -set optimizer_max_sel_arg_weight= @tmp9750_weight; -set optimizer_trace=@tmp_9750; -drop table t1; set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 6a1c51f8939..b5980a8f616 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2119,73 +2119,6 @@ drop table t1,ten,t2; --echo # End of 10.2 tests --echo # ---echo # ---echo # MDEV-9750: Quick memory exhaustion with 'extended_keys=on'... ---echo # - -create table t1 ( - kp1 int, - kp2 int, - kp3 int, - kp4 int, - key key1(kp1, kp2, kp3,kp4) -); - -insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); - -show variables like 'optimizer_max_sel_arg_weight'; - -# 20 * 20 * 20 *20 = 400*400 = 160,000 ranges -set @tmp_9750=@@optimizer_trace; -set optimizer_trace=1; -explain select * from t1 where - kp1 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and - kp2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and - kp3 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and - kp4 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) -; - -set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) - from information_schema.optimizer_trace); ---echo # This will show 3-component ranges. ---echo # The ranges were produced, but the optimizer has cut away kp4 ---echo # to keep the number of ranges at manageable level: -select left(@json, 500); - ---echo ## Repeat the above with low max_weight: -set @tmp9750_weight=@@optimizer_max_sel_arg_weight; -set optimizer_max_sel_arg_weight=20; -explain select * from t1 where - kp1 in (1,2,3,4,5,6,7,8,9,10) and - kp2 in (1,2,3,4,5,6,7,8,9,10) and - kp3 in (1,2,3,4,5,6,7,8,9,10) and - kp4 in (1,2,3,4,5,6,7,8,9,10) -; -set @trace= (select trace from information_schema.optimizer_trace); -set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); -select left(@json, 500); - -set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); -select left(@json, 2500); - ---echo ## Repeat the above with a bit higher max_weight: -set @tmp9750_weight=@@optimizer_max_sel_arg_weight; -set optimizer_max_sel_arg_weight=120; -explain select * from t1 where - kp1 in (1,2,3,4,5,6,7,8,9,10) and - kp2 in (1,2,3,4,5,6,7,8,9,10) and - kp3 in (1,2,3,4,5,6,7,8,9,10) and - kp4 in (1,2,3,4,5,6,7,8,9,10) -; -set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) - from information_schema.optimizer_trace); -select left(@json, 1500); - -set optimizer_max_sel_arg_weight= @tmp9750_weight; -set optimizer_trace=@tmp_9750; -drop table t1; - - set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 128f23d71f6..04c3ad2780d 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -3132,52 +3132,6 @@ drop table t1,ten,t2; # # End of 10.2 tests # -# -# MDEV-9750: Quick memory exhaustion with 'extended_keys=on'... -# -create table t1 ( -kp1 int, -kp2 int, -kp3 int, -kp4 int, -key key1(kp1, kp2, kp3,kp4) -); -insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); -set @tmp_9750=@@optimizer_trace; -set optimizer_trace=1; -explain select * from t1 where -kp1 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and -kp2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and -kp3 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and -kp4 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) -; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index -set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) -from information_schema.optimizer_trace); -# This will show 3-component ranges. -# The ranges were produced, but the optimizer has cut away kp4 -# to keep the number of ranges at manageable level: -select left(@json, 500); -left(@json, 500) -[ - - [ - - { - "index": "key1", - "ranges": - [ - "(1,1,1) <= (kp1,kp2,kp3) <= (1,1,1)", - "(1,1,2) <= (kp1,kp2,kp3) <= (1,1,2)", - "(1,1,3) <= (kp1,kp2,kp3) <= (1,1,3)", - "(1,1,4) <= (kp1,kp2,kp3) <= (1,1,4)", - "(1,1,5) <= (kp1,kp2,kp3) <= (1,1,5)", - "(1,1,6) <= (kp1,kp2,kp3) <= (1,1,6)", - "(1,1,7) <= (kp1,kp2,kp3) <= (1,1,7)", - " -set optimizer_trace=@tmp_9750; -drop table t1; set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result new file mode 100644 index 00000000000..7d5dc41d7ed --- /dev/null +++ b/mysql-test/main/range_notembedded.result @@ -0,0 +1,172 @@ +# +# MDEV-9750: Quick memory exhaustion with 'extended_keys=on'... +# +create table t1 ( +kp1 int, +kp2 int, +kp3 int, +kp4 int, +key key1(kp1, kp2, kp3,kp4) +); +insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); +show variables like 'optimizer_max_sel_arg_weight'; +Variable_name Value +optimizer_max_sel_arg_weight 32000 +set @tmp_9750=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t1 where +kp1 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and +kp2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and +kp3 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and +kp4 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) +from information_schema.optimizer_trace); +# This will show 3-component ranges. +# The ranges were produced, but the optimizer has cut away kp4 +# to keep the number of ranges at manageable level: +select left(@json, 500); +left(@json, 500) +[ + + [ + + { + "index": "key1", + "ranges": + [ + "(1,1,1) <= (kp1,kp2,kp3) <= (1,1,1)", + "(1,1,2) <= (kp1,kp2,kp3) <= (1,1,2)", + "(1,1,3) <= (kp1,kp2,kp3) <= (1,1,3)", + "(1,1,4) <= (kp1,kp2,kp3) <= (1,1,4)", + "(1,1,5) <= (kp1,kp2,kp3) <= (1,1,5)", + "(1,1,6) <= (kp1,kp2,kp3) <= (1,1,6)", + "(1,1,7) <= (kp1,kp2,kp3) <= (1,1,7)", + " +## Repeat the above with low max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=20; +explain select * from t1 where +kp1 in (1,2,3,4,5,6,7,8,9,10) and +kp2 in (1,2,3,4,5,6,7,8,9,10) and +kp3 in (1,2,3,4,5,6,7,8,9,10) and +kp4 in (1,2,3,4,5,6,7,8,9,10) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index +set @trace= (select trace from information_schema.optimizer_trace); +set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); +select left(@json, 500); +left(@json, 500) +[ + + [ + + { + "index": "key1", + "ranges": + [ + "(1) <= (kp1) <= (1)", + "(2) <= (kp1) <= (2)", + "(3) <= (kp1) <= (3)", + "(4) <= (kp1) <= (4)", + "(5) <= (kp1) <= (5)", + "(6) <= (kp1) <= (6)", + "(7) <= (kp1) <= (7)", + "(8) <= (kp1) <= (8)", + "(9) <= (kp1) <= (9)", + "(10) <= (kp1) <= (10)" + +set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); +select left(@json, 2500); +left(@json, 2500) +[ + + [ + + { + "enforce_sel_arg_weight_limit": + { + "index": "key1", + "old_weight": 110, + "new_weight": 10 + } + }, + + { + "enforce_sel_arg_weight_limit": + { + "index": "key1", + "old_weight": 110, + "new_weight": 10 + } + }, + + { + "enforce_sel_arg_weight_limit": + { + "index": "key1", + "old_weight": 110, + "new_weight": 10 + } + } + ] +] +## Repeat the above with a bit higher max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=120; +explain select * from t1 where +kp1 in (1,2,3,4,5,6,7,8,9,10) and +kp2 in (1,2,3,4,5,6,7,8,9,10) and +kp3 in (1,2,3,4,5,6,7,8,9,10) and +kp4 in (1,2,3,4,5,6,7,8,9,10) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) +from information_schema.optimizer_trace); +select left(@json, 1500); +left(@json, 1500) +[ + + [ + + { + "index": "key1", + "ranges": + [ + "(1,1) <= (kp1,kp2) <= (1,1)", + "(1,2) <= (kp1,kp2) <= (1,2)", + "(1,3) <= (kp1,kp2) <= (1,3)", + "(1,4) <= (kp1,kp2) <= (1,4)", + "(1,5) <= (kp1,kp2) <= (1,5)", + "(1,6) <= (kp1,kp2) <= (1,6)", + "(1,7) <= (kp1,kp2) <= (1,7)", + "(1,8) <= (kp1,kp2) <= (1,8)", + "(1,9) <= (kp1,kp2) <= (1,9)", + "(1,10) <= (kp1,kp2) <= (1,10)", + "(2,1) <= (kp1,kp2) <= (2,1)", + "(2,2) <= (kp1,kp2) <= (2,2)", + "(2,3) <= (kp1,kp2) <= (2,3)", + "(2,4) <= (kp1,kp2) <= (2,4)", + "(2,5) <= (kp1,kp2) <= (2,5)", + "(2,6) <= (kp1,kp2) <= (2,6)", + "(2,7) <= (kp1,kp2) <= (2,7)", + "(2,8) <= (kp1,kp2) <= (2,8)", + "(2,9) <= (kp1,kp2) <= (2,9)", + "(2,10) <= (kp1,kp2) <= (2,10)", + "(3,1) <= (kp1,kp2) <= (3,1)", + "(3,2) <= (kp1,kp2) <= (3,2)", + "(3,3) <= (kp1,kp2) <= (3,3)", + "(3,4) <= (kp1,kp2) <= (3,4)", + "(3,5) <= (kp1,kp2) <= (3,5)", + "(3,6) <= (kp1,kp2) <= (3,6)", + "(3,7) <= (kp1,kp2) <= (3,7)", + "(3,8) <= (kp1,kp2) <= (3,8)", + "(3,9) <= (kp1,kp2) <= (3,9)", + "(3,10) <= (kp1,kp2 +set optimizer_max_sel_arg_weight= @tmp9750_weight; +set optimizer_trace=@tmp_9750; +drop table t1; diff --git a/mysql-test/main/range_notembedded.test b/mysql-test/main/range_notembedded.test new file mode 100644 index 00000000000..c1224550a1c --- /dev/null +++ b/mysql-test/main/range_notembedded.test @@ -0,0 +1,71 @@ +# +# Range tests that require optimizer trace (which is not available in embedded +# server) +# +-- source include/not_embedded.inc + +--echo # +--echo # MDEV-9750: Quick memory exhaustion with 'extended_keys=on'... +--echo # + +create table t1 ( + kp1 int, + kp2 int, + kp3 int, + kp4 int, + key key1(kp1, kp2, kp3,kp4) +); + +insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); + +show variables like 'optimizer_max_sel_arg_weight'; + +# 20 * 20 * 20 *20 = 400*400 = 160,000 ranges +set @tmp_9750=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t1 where + kp1 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and + kp2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and + kp3 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and + kp4 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) +; + +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) + from information_schema.optimizer_trace); +--echo # This will show 3-component ranges. +--echo # The ranges were produced, but the optimizer has cut away kp4 +--echo # to keep the number of ranges at manageable level: +select left(@json, 500); + +--echo ## Repeat the above with low max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=20; +explain select * from t1 where + kp1 in (1,2,3,4,5,6,7,8,9,10) and + kp2 in (1,2,3,4,5,6,7,8,9,10) and + kp3 in (1,2,3,4,5,6,7,8,9,10) and + kp4 in (1,2,3,4,5,6,7,8,9,10) +; +set @trace= (select trace from information_schema.optimizer_trace); +set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); +select left(@json, 500); + +set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); +select left(@json, 2500); + +--echo ## Repeat the above with a bit higher max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=120; +explain select * from t1 where + kp1 in (1,2,3,4,5,6,7,8,9,10) and + kp2 in (1,2,3,4,5,6,7,8,9,10) and + kp3 in (1,2,3,4,5,6,7,8,9,10) and + kp4 in (1,2,3,4,5,6,7,8,9,10) +; +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) + from information_schema.optimizer_trace); +select left(@json, 1500); + +set optimizer_max_sel_arg_weight= @tmp9750_weight; +set optimizer_trace=@tmp_9750; +drop table t1; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 2543cb12453..87f488e3653 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -2402,6 +2402,16 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME OPTIMIZER_MAX_SEL_ARG_WEIGHT +VARIABLE_SCOPE SESSION +VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_COMMENT The maximum weight of the SEL_ARG graph. Set to 0 for no limit +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_PRUNE_LEVEL VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED

1 0

[Commits] b6a5d3e87a8: MDEV-9750: Quick memory exhaustion with 'extended_keys=on', Part #4
by psergey 19 Jan '21

19 Jan '21

revision-id: b6a5d3e87a8c33540d7cda3e4a512598fa5b1233 (mariadb-10.4.11-423-gb6a5d3e87a8) parent(s): 00675d2596ff3b15f676a2ee329ea6045adb08b4 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-19 15:31:47 +0300 message: MDEV-9750: Quick memory exhaustion with 'extended_keys=on', Part #4 Part #4: - Fix comment - Make tree pruning visible in the optimizer trace. --- mysql-test/main/range.result | 39 +++++++++++++++++++++-- mysql-test/main/range.test | 7 +++-- sql/opt_range.cc | 73 ++++++++++++++++++++++++++------------------ sql/opt_range.h | 29 +++++++++++------- 4 files changed, 103 insertions(+), 45 deletions(-) diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index c5178885d7f..982a70f94f8 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3193,8 +3193,8 @@ kp4 in (1,2,3,4,5,6,7,8,9,10) ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index -set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) -from information_schema.optimizer_trace); +set @trace= (select trace from information_schema.optimizer_trace); +set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); select left(@json, 500); left(@json, 500) [ @@ -3216,6 +3216,41 @@ left(@json, 500) "(9) <= (kp1) <= (9)", "(10) <= (kp1) <= (10)" +set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); +select left(@json, 2500); +left(@json, 2500) +[ + + [ + + { + "enforce_sel_arg_weight_limit": + { + "index": "key1", + "old_weight": 110, + "new_weight": 10 + } + }, + + { + "enforce_sel_arg_weight_limit": + { + "index": "key1", + "old_weight": 110, + "new_weight": 10 + } + }, + + { + "enforce_sel_arg_weight_limit": + { + "index": "key1", + "old_weight": 110, + "new_weight": 10 + } + } + ] +] ## Repeat the above with a bit higher max_weight: set @tmp9750_weight=@@optimizer_max_sel_arg_weight; set optimizer_max_sel_arg_weight=120; diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index a9f374afa3d..6a1c51f8939 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2161,10 +2161,13 @@ explain select * from t1 where kp3 in (1,2,3,4,5,6,7,8,9,10) and kp4 in (1,2,3,4,5,6,7,8,9,10) ; -set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) - from information_schema.optimizer_trace); +set @trace= (select trace from information_schema.optimizer_trace); +set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); select left(@json, 500); +set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); +select left(@json, 2500); + --echo ## Repeat the above with a bit higher max_weight: set @tmp9750_weight=@@optimizer_max_sel_arg_weight; set optimizer_max_sel_arg_weight=120; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 26d76075e60..b67a85457c5 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -399,9 +399,9 @@ static SEL_ARG *key_or(RANGE_OPT_PARAM *param, static SEL_ARG *key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag); -static SEL_ARG *key_or_with_limit(RANGE_OPT_PARAM *param, +static SEL_ARG *key_or_with_limit(RANGE_OPT_PARAM *param, uint keyno, SEL_ARG *key1, SEL_ARG *key2); -static SEL_ARG *key_and_with_limit(RANGE_OPT_PARAM *param, +static SEL_ARG *key_and_with_limit(RANGE_OPT_PARAM *param, uint keyno, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag); static bool get_range(SEL_ARG **e1,SEL_ARG **e2,SEL_ARG *root1); @@ -415,7 +415,9 @@ static bool null_part_in_key(KEY_PART *key_part, const uchar *key, uint length); static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts); -static SEL_ARG *enforce_sel_arg_weight_limit(THD *thd, SEL_ARG *sel_arg); +static +SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, + SEL_ARG *sel_arg); #include "opt_range_mrr.cc" @@ -713,7 +715,8 @@ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param, SEL_ARG *key1= (*or_tree)->keys[key_no]; SEL_ARG *key2= tree->keys[key_no]; key2->incr_refs(); - if ((result->keys[key_no]= key_or_with_limit(param, key1, key2))) + if ((result->keys[key_no]= key_or_with_limit(param, key_no, key1, + key2))) { result_keys.set_bit(key_no); @@ -5440,7 +5443,7 @@ TABLE_READ_PLAN *merge_same_index_scans(PARAM *param, SEL_IMERGE *imerge, if ((*tree)->keys[key_idx]) (*tree)->keys[key_idx]->incr_refs(); if (((*changed_tree)->keys[key_idx]= - key_or_with_limit(param, key, (*tree)->keys[key_idx]))) + key_or_with_limit(param, key_idx, key, (*tree)->keys[key_idx]))) (*changed_tree)->keys_map.set_bit(key_idx); *tree= NULL; removed_cnt++; @@ -9102,7 +9105,8 @@ int and_range_trees(RANGE_OPT_PARAM *param, SEL_TREE *tree1, SEL_TREE *tree2, key2->incr_refs(); } SEL_ARG *key; - if ((result->keys[key_no]= key =key_and_with_limit(param, key1, key2, flag))) + if ((result->keys[key_no]= key= key_and_with_limit(param, key_no, + key1, key2, flag))) { if (key && key->type == SEL_ARG::IMPOSSIBLE) { @@ -9645,7 +9649,7 @@ tree_or(RANGE_OPT_PARAM *param,SEL_TREE *tree1,SEL_TREE *tree2) key1->incr_refs(); key2->incr_refs(); } - if ((result->keys[key_no]= key_or_with_limit(param, key1, key2))) + if ((result->keys[key_no]= key_or_with_limit(param, key_no, key1, key2))) result->keys_map.set_bit(key_no); } result->type= tree1->type; @@ -9968,11 +9972,12 @@ uint SEL_ARG::verify_weight() } #endif -static SEL_ARG *key_or_with_limit(RANGE_OPT_PARAM *param, - SEL_ARG *key1, SEL_ARG *key2) +static +SEL_ARG *key_or_with_limit(RANGE_OPT_PARAM *param, uint keyno, + SEL_ARG *key1, SEL_ARG *key2) { - SEL_ARG *res= enforce_sel_arg_weight_limit(param->thd, key_or(param, - key1, key2)); + SEL_ARG *res= key_or(param, key1, key2); + res= enforce_sel_arg_weight_limit(param, keyno, res); #ifndef DBUG_OFF if (res) res->verify_weight(); @@ -9981,13 +9986,12 @@ static SEL_ARG *key_or_with_limit(RANGE_OPT_PARAM *param, } -static SEL_ARG *key_and_with_limit(RANGE_OPT_PARAM *param, - SEL_ARG *key1, SEL_ARG *key2, - uint clone_flag) +static +SEL_ARG *key_and_with_limit(RANGE_OPT_PARAM *param, uint keyno, + SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) { - SEL_ARG *res= enforce_sel_arg_weight_limit(param->thd, key_and(param, key1, - key2, - clone_flag)); + SEL_ARG *res= key_and(param, key1, key2, clone_flag); + res= enforce_sel_arg_weight_limit(param, keyno, res); #ifndef DBUG_OFF if (res) res->verify_weight(); @@ -10773,33 +10777,42 @@ void prune_sel_arg_graph(SEL_ARG *sel_arg, uint max_part) limit. */ -SEL_ARG *enforce_sel_arg_weight_limit(THD *thd, SEL_ARG *sel_arg) +SEL_ARG *enforce_sel_arg_weight_limit(RANGE_OPT_PARAM *param, uint keyno, + SEL_ARG *sel_arg) { if (!sel_arg || sel_arg->type != SEL_ARG::KEY_RANGE || - !thd->variables.optimizer_max_sel_arg_weight) + !param->thd->variables.optimizer_max_sel_arg_weight) return sel_arg; + uint weight1= sel_arg->weight; + while (1) { - if (sel_arg->weight <= thd->variables.optimizer_max_sel_arg_weight) - return sel_arg; + if (sel_arg->weight <= param->thd->variables.optimizer_max_sel_arg_weight) + break; uint max_part= sel_arg->get_max_key_part(); if (max_part == sel_arg->part) - return NULL; - -#ifdef EXTRA_DEBUG - uint weight1= sel_arg->weight; -#endif + { + sel_arg= NULL; + break; + } max_part--; prune_sel_arg_graph(sel_arg, max_part); + } -#ifdef EXTRA_DEBUG - DBUG_PRINT("info", ("enforce_sel_arg_weight_limit: %d->%d", weight1, - sel_arg->weight)); -#endif + uint weight2= sel_arg? sel_arg->weight : 0; + + if (weight2 != weight1) + { + Json_writer_object wrapper(param->thd); + Json_writer_object obj(param->thd, "enforce_sel_arg_weight_limit"); + obj.add("index", param->table->key_info[param->real_keynr[keyno]].name); + obj.add("old_weight", (longlong)weight1); + obj.add("new_weight", (longlong)weight2); } + return sel_arg; } diff --git a/sql/opt_range.h b/sql/opt_range.h index 14b52d358b0..fe4e86c76e6 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -228,36 +228,43 @@ class RANGE_OPT_PARAM; A SEL_ARG graph has a property we call weight, and we define it as follows: + <definition> If the SEL_ARG graph does not have any node with multiple incoming next_key_part edges, then its weight is the number of SEL_ARG objects used. - If there is a node with multiple next_key_part edges, clone the node (and - the nodes connected via prev/next links to it) and redirect one of the - incoming next_key_part to the clone. (If the node has "peer" nodes - connected to it via prev/next links, they will have to be cloned as well) + If there is a node with multiple incoming next_key_part edges, clone that + node, (and the nodes connected to it via prev/next links) and redirect one + of the incoming next_key_part edges to the clone. - Repeat this until we get a graph without multiple next_key_part edges - coming into the same node. Then, the number of SEL_ARG objects in the - graph is the weight. + Continue with cloning until we get a graph that has no nodes with multiple + incoming next_key_part edges. Then, the number of SEL_ARG objects in the + graph is the weight of the original graph. + </definition> Example: + kp1 $ kp2 $ kp3 + $ $ | +-------+ $ $ \->| kp1=2 |--$--------------$-+ +-------+ $ $ | +--------+ | $ $ ==>| kp3=11 | +-------+ $ $ | +--------+ - | kp1=3 |--$--------------$-+ | + | kp1>3 |--$--------------$-+ | +-------+ $ $ +--------+ $ $ | kp3=14 | $ $ +--------+ + $ $ | + $ $ +--------+ + $ $ | kp3=14 | + $ $ +--------+ - Here, the weight is 2 + 2*2=6. + Here, the weight is 2 + 2*3=8. - The rationale behind the weight is: + The rationale behind using this definition of weight is: - it has the same order-of-magnitude as the number of ranges that the SEL_ARG graph is describing, - - it is a lot easier to compute, + - it is a lot easier to compute than computing the number of ranges, - it can be updated incrementally when performing AND/OR operations on parts of the graph. */

1 0

[Commits] 730710f: MDEV-16940 Server crashes in unsafe_key_update upon attempt to update view
by IgorBabaev 19 Jan '21

19 Jan '21

revision-id: 730710f8f0ad3cee943607fc01b2bb4b1ba70f59 (mariadb-10.2.31-684-g730710f) parent(s): cf6114ebea0224f265d1ac25592fce9b984b0e6e author: Igor Babaev committer: Igor Babaev timestamp: 2021-01-18 22:49:15 -0800 message: MDEV-16940 Server crashes in unsafe_key_update upon attempt to update view through 2nd execution of SP This bug caused a server crash on the second call of any stored procedure that contained an UPDATE statement over a multi-table view reporting an error message at the prepare stage. On the first call of the stored procedure after reporting an error at the preparation stage of the UPDATE statement finished without calling the function SELECT_LEX::save_prep_leaf_tables() for the SELECT used as the definition of the view. This left the SELECT_LEX structure used by the UPDATE statement in an inconsistent state for second call of the stored procedure. --- mysql-test/r/view.result | 16 ++++++++++++++++ mysql-test/t/view.test | 22 ++++++++++++++++++++++ sql/sql_lex.cc | 3 +++ sql/sql_update.cc | 3 +++ 4 files changed, 44 insertions(+) diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 0da11c7..e48a99f 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -6802,5 +6802,21 @@ drop database db1; create database test; use test; # +# MDEV-16940: update of multi-table view returning error used in SP +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (2), (3); +CREATE VIEW v1 AS SELECT a, b FROM t1,t2; +CREATE PROCEDURE sp1() UPDATE v1 SET a = 8, b = 9; +CALL sp1; +ERROR HY000: Can not modify more than one base table through a join view 'test.v1' +CALL sp1; +ERROR HY000: Can not modify more than one base table through a join view 'test.v1' +DROP PROCEDURE sp1; +DROP VIEW v1; +DROP TABLE t1, t2; +# # End of 10.2 tests # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 2486887..b5ce6a1 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -6522,5 +6522,27 @@ create database test; use test; --echo # +--echo # MDEV-16940: update of multi-table view returning error used in SP +--echo # + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (2), (3); + +CREATE VIEW v1 AS SELECT a, b FROM t1,t2; + +CREATE PROCEDURE sp1() UPDATE v1 SET a = 8, b = 9; + +--error ER_VIEW_MULTIUPDATE +CALL sp1; +--error ER_VIEW_MULTIUPDATE +CALL sp1; + +DROP PROCEDURE sp1; +DROP VIEW v1; +DROP TABLE t1, t2; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 77e6b2b..5059e4f 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4585,6 +4585,9 @@ bool LEX::save_prep_leaf_tables() bool st_select_lex::save_prep_leaf_tables(THD *thd) { + if (prep_leaf_list_state == SAVED) + return FALSE; + List_iterator_fast<TABLE_LIST> li(leaf_tables); TABLE_LIST *table; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 7454d16..01743a6 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1398,6 +1398,9 @@ bool Multiupdate_prelocking_strategy::handle_end(THD *thd) if (select_lex->handle_derived(thd->lex, DT_MERGE)) DBUG_RETURN(1); + if (thd->lex->save_prep_leaf_tables()) + DBUG_RETURN(1); + List<Item> *fields= &lex->select_lex.item_list; if (setup_fields_with_no_wrap(thd, Ref_ptr_array(), *fields, MARK_COLUMNS_WRITE, 0, 0))

1 0

[Commits] 00675d2596f: MDEV-9750: Quick memory exhaustion with 'extended_keys=on' ...
by psergey 18 Jan '21

18 Jan '21

revision-id: 00675d2596ff3b15f676a2ee329ea6045adb08b4 (mariadb-10.4.11-422-g00675d2596f) parent(s): 805fcc4c202d601b6bf4f2a39aa15c1859c6f4db author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-18 19:07:42 +0300 message: MDEV-9750: Quick memory exhaustion with 'extended_keys=on' ... Part #3: Introduce a user-visible @@optimizer_max_sel_arg_weight to control the optimization. --- mysql-test/main/range.result | 91 ++++++++++++++++++++++++++++++++++++++++++++ mysql-test/main/range.test | 30 +++++++++++++++ sql/opt_range.cc | 16 +++++--- sql/sql_class.h | 1 + sql/sys_vars.cc | 6 +++ 5 files changed, 138 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 9800d931dd6..c5178885d7f 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3146,6 +3146,9 @@ kp4 int, key key1(kp1, kp2, kp3,kp4) ); insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); +show variables like 'optimizer_max_sel_arg_weight'; +Variable_name Value +optimizer_max_sel_arg_weight 32000 set @tmp_9750=@@optimizer_trace; set optimizer_trace=1; explain select * from t1 where @@ -3179,6 +3182,94 @@ left(@json, 500) "(1,1,6) <= (kp1,kp2,kp3) <= (1,1,6)", "(1,1,7) <= (kp1,kp2,kp3) <= (1,1,7)", " +## Repeat the above with low max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=20; +explain select * from t1 where +kp1 in (1,2,3,4,5,6,7,8,9,10) and +kp2 in (1,2,3,4,5,6,7,8,9,10) and +kp3 in (1,2,3,4,5,6,7,8,9,10) and +kp4 in (1,2,3,4,5,6,7,8,9,10) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) +from information_schema.optimizer_trace); +select left(@json, 500); +left(@json, 500) +[ + + [ + + { + "index": "key1", + "ranges": + [ + "(1) <= (kp1) <= (1)", + "(2) <= (kp1) <= (2)", + "(3) <= (kp1) <= (3)", + "(4) <= (kp1) <= (4)", + "(5) <= (kp1) <= (5)", + "(6) <= (kp1) <= (6)", + "(7) <= (kp1) <= (7)", + "(8) <= (kp1) <= (8)", + "(9) <= (kp1) <= (9)", + "(10) <= (kp1) <= (10)" + +## Repeat the above with a bit higher max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=120; +explain select * from t1 where +kp1 in (1,2,3,4,5,6,7,8,9,10) and +kp2 in (1,2,3,4,5,6,7,8,9,10) and +kp3 in (1,2,3,4,5,6,7,8,9,10) and +kp4 in (1,2,3,4,5,6,7,8,9,10) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) +from information_schema.optimizer_trace); +select left(@json, 1500); +left(@json, 1500) +[ + + [ + + { + "index": "key1", + "ranges": + [ + "(1,1) <= (kp1,kp2) <= (1,1)", + "(1,2) <= (kp1,kp2) <= (1,2)", + "(1,3) <= (kp1,kp2) <= (1,3)", + "(1,4) <= (kp1,kp2) <= (1,4)", + "(1,5) <= (kp1,kp2) <= (1,5)", + "(1,6) <= (kp1,kp2) <= (1,6)", + "(1,7) <= (kp1,kp2) <= (1,7)", + "(1,8) <= (kp1,kp2) <= (1,8)", + "(1,9) <= (kp1,kp2) <= (1,9)", + "(1,10) <= (kp1,kp2) <= (1,10)", + "(2,1) <= (kp1,kp2) <= (2,1)", + "(2,2) <= (kp1,kp2) <= (2,2)", + "(2,3) <= (kp1,kp2) <= (2,3)", + "(2,4) <= (kp1,kp2) <= (2,4)", + "(2,5) <= (kp1,kp2) <= (2,5)", + "(2,6) <= (kp1,kp2) <= (2,6)", + "(2,7) <= (kp1,kp2) <= (2,7)", + "(2,8) <= (kp1,kp2) <= (2,8)", + "(2,9) <= (kp1,kp2) <= (2,9)", + "(2,10) <= (kp1,kp2) <= (2,10)", + "(3,1) <= (kp1,kp2) <= (3,1)", + "(3,2) <= (kp1,kp2) <= (3,2)", + "(3,3) <= (kp1,kp2) <= (3,3)", + "(3,4) <= (kp1,kp2) <= (3,4)", + "(3,5) <= (kp1,kp2) <= (3,5)", + "(3,6) <= (kp1,kp2) <= (3,6)", + "(3,7) <= (kp1,kp2) <= (3,7)", + "(3,8) <= (kp1,kp2) <= (3,8)", + "(3,9) <= (kp1,kp2) <= (3,9)", + "(3,10) <= (kp1,kp2 +set optimizer_max_sel_arg_weight= @tmp9750_weight; set optimizer_trace=@tmp_9750; drop table t1; set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 642ae3f8a08..a9f374afa3d 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2133,6 +2133,8 @@ create table t1 ( insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); +show variables like 'optimizer_max_sel_arg_weight'; + # 20 * 20 * 20 *20 = 400*400 = 160,000 ranges set @tmp_9750=@@optimizer_trace; set optimizer_trace=1; @@ -2150,9 +2152,37 @@ set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternative --echo # to keep the number of ranges at manageable level: select left(@json, 500); +--echo ## Repeat the above with low max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=20; +explain select * from t1 where + kp1 in (1,2,3,4,5,6,7,8,9,10) and + kp2 in (1,2,3,4,5,6,7,8,9,10) and + kp3 in (1,2,3,4,5,6,7,8,9,10) and + kp4 in (1,2,3,4,5,6,7,8,9,10) +; +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) + from information_schema.optimizer_trace); +select left(@json, 500); + +--echo ## Repeat the above with a bit higher max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=120; +explain select * from t1 where + kp1 in (1,2,3,4,5,6,7,8,9,10) and + kp2 in (1,2,3,4,5,6,7,8,9,10) and + kp3 in (1,2,3,4,5,6,7,8,9,10) and + kp4 in (1,2,3,4,5,6,7,8,9,10) +; +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) + from information_schema.optimizer_trace); +select left(@json, 1500); + +set optimizer_max_sel_arg_weight= @tmp9750_weight; set optimizer_trace=@tmp_9750; drop table t1; + set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index aef9654c646..26d76075e60 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -415,7 +415,7 @@ static bool null_part_in_key(KEY_PART *key_part, const uchar *key, uint length); static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts); -static SEL_ARG *enforce_sel_arg_weight_limit(SEL_ARG *sel_arg); +static SEL_ARG *enforce_sel_arg_weight_limit(THD *thd, SEL_ARG *sel_arg); #include "opt_range_mrr.cc" @@ -9971,7 +9971,8 @@ uint SEL_ARG::verify_weight() static SEL_ARG *key_or_with_limit(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2) { - SEL_ARG *res= enforce_sel_arg_weight_limit(key_or(param, key1, key2)); + SEL_ARG *res= enforce_sel_arg_weight_limit(param->thd, key_or(param, + key1, key2)); #ifndef DBUG_OFF if (res) res->verify_weight(); @@ -9984,7 +9985,9 @@ static SEL_ARG *key_and_with_limit(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) { - SEL_ARG *res= enforce_sel_arg_weight_limit(key_and(param, key1, key2, clone_flag)); + SEL_ARG *res= enforce_sel_arg_weight_limit(param->thd, key_and(param, key1, + key2, + clone_flag)); #ifndef DBUG_OFF if (res) res->verify_weight(); @@ -10770,14 +10773,15 @@ void prune_sel_arg_graph(SEL_ARG *sel_arg, uint max_part) limit. */ -SEL_ARG *enforce_sel_arg_weight_limit(SEL_ARG *sel_arg) +SEL_ARG *enforce_sel_arg_weight_limit(THD *thd, SEL_ARG *sel_arg) { - if (!sel_arg || sel_arg->type != SEL_ARG::KEY_RANGE) + if (!sel_arg || sel_arg->type != SEL_ARG::KEY_RANGE || + !thd->variables.optimizer_max_sel_arg_weight) return sel_arg; while (1) { - if (sel_arg->weight <= SEL_ARG::MAX_WEIGHT) + if (sel_arg->weight <= thd->variables.optimizer_max_sel_arg_weight) return sel_arg; uint max_part= sel_arg->get_max_key_part(); diff --git a/sql/sql_class.h b/sql/sql_class.h index 64cd1ed6ba3..6063c51bccf 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -766,6 +766,7 @@ typedef struct system_variables uint column_compression_threshold; uint column_compression_zlib_level; uint in_subquery_conversion_threshold; + ulong optimizer_max_sel_arg_weight; ulonglong max_rowid_filter_size; vers_asof_timestamp_t vers_asof_timestamp; diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 1c84f308a81..6b859363453 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -6367,6 +6367,12 @@ static Sys_var_uint Sys_in_subquery_conversion_threshold( SESSION_VAR(in_subquery_conversion_threshold), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, UINT_MAX), DEFAULT(IN_SUBQUERY_CONVERSION_THRESHOLD), BLOCK_SIZE(1)); +static Sys_var_ulong Sys_optimizer_max_sel_arg_weight( + "optimizer_max_sel_arg_weight", + "The maximum weight of the SEL_ARG graph. Set to 0 for no limit", + SESSION_VAR(optimizer_max_sel_arg_weight), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(0, ULONG_MAX), DEFAULT(SEL_ARG::MAX_WEIGHT), BLOCK_SIZE(1)); + static Sys_var_enum Sys_secure_timestamp( "secure_timestamp", "Restricts direct setting of a session " "timestamp. Possible levels are: YES - timestamp cannot deviate from "

1 0