Debian Codegraph @

mysql-test/suite/sys_vars/t/sql_generate_invisible_primary_key_basic.test

raw
1 # Variable Name: sql_generate_invisible_primary_key
2 # Scope: Global & Session
3 # Data type: Boolean
4 # Default Value: OFF
5
6 # Save the initial number of concurrent sessions.
7 --source include/count_sessions.inc
8
9 SET @saved_global_sql_generate_invisible_primary_key = @@global.sql_generate_invisible_primary_key;
10 SET @saved_session_sql_generate_invisible_primary_key = @@session.sql_generate_invisible_primary_key;
11
12 ####################################################################
13 # Displaying default value #
14 ####################################################################
15
16 --let $assert_text= Default GLOBAL value is OFF
17 --let $assert_cond = [SELECT @@global.sql_generate_invisible_primary_key = 0]
18 --source include/assert.inc
19
20 --let $assert_text= Default SESSION value is OFF
21 --let $assert_cond = [SELECT @@session.sql_generate_invisible_primary_key = 0]
22 --source include/assert.inc
23
24 ####################################################################
25 # Check if Value can set and that P_S reports the correct value #
26 ####################################################################
27
28 --let $json_label = varscope
29 --let $json_keys = scope, ps_table
30 --source include/create_json_unpacking_iterator.inc
31
32 let $json_array = [
33 { "scope": "SESSION", "ps_table": "session_variables" },
34 { "scope": "GLOBAL", "ps_table": "global_variables" },
35 { "scope": "PERSIST", "ps_table": "persisted_variables" },
36 { "scope": "PERSIST_ONLY", "ps_table": "persisted_variables" }
37 ];
38
39 --source $json_varscope_start
40 while (!$json_varscope_done) {
41 --let $json_label = values
42 --let $json_keys = value_to_set, expected_result
43 --source include/create_json_unpacking_iterator.inc
44 let $json_array = [
45 { "value_to_set": "0", "expected_result": "OFF" },
46 { "value_to_set": "DEFAULT", "expected_result": "OFF" },
47 { "value_to_set": "OFF", "expected_result": "OFF" },
48 { "value_to_set": "FALSE", "expected_result": "OFF" },
49 { "value_to_set": "ON", "expected_result": "ON" },
50 { "value_to_set": "TRUE", "expected_result": "ON" },
51 { "value_to_set": "1", "expected_result": "ON" }
52 ];
53
54 --source $json_values_start
55 while (!$json_values_done) {
56 --eval SET @@$scope.sql_generate_invisible_primary_key = $value_to_set
57 --let $reported=`SELECT variable_value FROM performance_schema.$ps_table WHERE variable_name = 'sql_generate_invisible_primary_key'`
58
59 --let $assert_text= Reported value matches the one set (set $value_to_set in $scope scope)
60 --let $assert_cond = "$reported" = "$expected_result"
61 --source include/assert.inc
62
63 --source $json_values_next
64 }
65
66 --source $json_varscope_next
67 }
68 --source include/destroy_json_functions.inc
69
70 ####################################################################
71 # Privileges #
72 ####################################################################
73
74 CREATE USER user1;
75 --connect(conn_user1,localhost,user1,,)
76
77 # assert that one cannot change the value without privileges
78 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
79 SET GLOBAL sql_generate_invisible_primary_key = ON;
80 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
81 SET SESSION sql_generate_invisible_primary_key = ON;
82
83 # assert that one can change the value if SYSTEM_VARIABLES_ADMIN is given
84 --connection default
85 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO user1@'%';
86 --connection conn_user1
87 SET GLOBAL sql_generate_invisible_primary_key=ON;
88 SET SESSION sql_generate_invisible_primary_key=ON;
89
90 # assert that one can change the session value if SESSION_VARIABLES_ADMIN is given
91 --connection default
92 REVOKE SYSTEM_VARIABLES_ADMIN ON *.* FROM user1@'%';
93 GRANT SESSION_VARIABLES_ADMIN ON *.* TO user1@'%';
94 --connection conn_user1
95 SET SESSION sql_generate_invisible_primary_key = ON;
96
97 --connection default
98 DROP USER user1;
99 --disconnect conn_user1
100
101 ####################################################################
102 # End of tests #
103 ####################################################################
104 RESET PERSIST;
105
106 SET SESSION sql_generate_invisible_primary_key = @saved_session_sql_generate_invisible_primary_key;
107 SET GLOBAL sql_generate_invisible_primary_key = @saved_global_sql_generate_invisible_primary_key;
108 # Check that all connections opened by test cases in this file are really gone
109 # so execution of other tests won't be affected by their presence.
110 --source include/wait_until_count_sessions.inc