PageRenderTime 28ms CodeModel.GetById 16ms RepoModel.GetById 0ms app.codeStats 0ms

/mysql-test/r/variables.result

https://bitbucket.org/Habibutsu/mysql
Unknown | 1778 lines | 1733 code | 45 blank | 0 comment | 0 complexity | 8a9382881098aa11363879234512a695 MD5 | raw file
Possible License(s): GPL-2.0, BSD-3-Clause

Large files files are truncated, but you can click here to view the full file

  1. drop table if exists t1,t2;
  2. set @my_binlog_cache_size =@@global.binlog_cache_size;
  3. set @my_connect_timeout =@@global.connect_timeout;
  4. set @my_delayed_insert_timeout =@@global.delayed_insert_timeout;
  5. set @my_delayed_queue_size =@@global.delayed_queue_size;
  6. set @my_flush =@@global.flush;
  7. set @my_flush_time =@@global.flush_time;
  8. set @my_key_buffer_size =@@global.key_buffer_size;
  9. set @my_max_binlog_cache_size =@@global.max_binlog_cache_size;
  10. set @my_max_binlog_size =@@global.max_binlog_size;
  11. set @my_max_connect_errors =@@global.max_connect_errors;
  12. set @my_max_connections =@@global.max_connections;
  13. set @my_max_delayed_threads =@@global.max_delayed_threads;
  14. set @my_max_heap_table_size =@@global.max_heap_table_size;
  15. set @my_max_insert_delayed_threads=@@global.max_insert_delayed_threads;
  16. set @my_max_join_size =@@global.max_join_size;
  17. set @my_myisam_data_pointer_size =@@global.myisam_data_pointer_size;
  18. set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size;
  19. set @my_net_buffer_length =@@global.net_buffer_length;
  20. set @my_net_write_timeout =@@global.net_write_timeout;
  21. set @my_net_read_timeout =@@global.net_read_timeout;
  22. set @my_query_cache_limit =@@global.query_cache_limit;
  23. set @my_query_cache_type =@@global.query_cache_type;
  24. set @my_rpl_recovery_rank =@@global.rpl_recovery_rank;
  25. set @my_server_id =@@global.server_id;
  26. set @my_slow_launch_time =@@global.slow_launch_time;
  27. set @my_storage_engine =@@global.default_storage_engine;
  28. set @my_thread_cache_size =@@global.thread_cache_size;
  29. set @my_max_allowed_packet =@@global.max_allowed_packet;
  30. set @my_join_buffer_size =@@global.join_buffer_size;
  31. set @`test`=1;
  32. select @test, @`test`, @TEST, @`TEST`, @"teSt";
  33. @test @`test` @TEST @`TEST` @"teSt"
  34. 1 1 1 1 1
  35. set @TEST=2;
  36. select @test, @`test`, @TEST, @`TEST`, @"teSt";
  37. @test @`test` @TEST @`TEST` @"teSt"
  38. 2 2 2 2 2
  39. set @"tEST"=3;
  40. select @test, @`test`, @TEST, @`TEST`, @"teSt";
  41. @test @`test` @TEST @`TEST` @"teSt"
  42. 3 3 3 3 3
  43. set @`TeST`=4;
  44. select @test, @`test`, @TEST, @`TEST`, @"teSt";
  45. @test @`test` @TEST @`TEST` @"teSt"
  46. 4 4 4 4 4
  47. select @`teST`:=5;
  48. @`teST`:=5
  49. 5
  50. select @test, @`test`, @TEST, @`TEST`, @"teSt";
  51. @test @`test` @TEST @`TEST` @"teSt"
  52. 5 5 5 5 5
  53. set @select=2,@t5=1.23456;
  54. select @`select`,@not_used;
  55. @`select` @not_used
  56. 2 NULL
  57. set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL;
  58. select @test_int,@test_double,@test_string,@test_string2,@select;
  59. @test_int @test_double @test_string @test_string2 @select
  60. 10 0.0000000001 abcdeghi abcdefghij NULL
  61. set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello";
  62. select @test_int,@test_double,@test_string,@test_string2;
  63. @test_int @test_double @test_string @test_string2
  64. hello hello hello hello
  65. set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello";
  66. select @test_int,@test_double,@test_string,@test_string2;
  67. @test_int @test_double @test_string @test_string2
  68. hellohello hellohello hellohello hellohello
  69. set @test_int=null,@test_double=null,@test_string=null,@test_string2=null;
  70. select @test_int,@test_double,@test_string,@test_string2;
  71. @test_int @test_double @test_string @test_string2
  72. NULL NULL NULL NULL
  73. select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
  74. @t1:=(@t2:=1)+@t3:=4 @t1 @t2 @t3
  75. 5 5 1 4
  76. explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
  77. id select_type table type possible_keys key key_len ref rows filtered Extra
  78. 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
  79. Warnings:
  80. Note 1003 select (@t1:=((@t2:=1) + (@t3:=4))) AS `@t1:=(@t2:=1)+@t3:=4`,(@t1) AS `@t1`,(@t2) AS `@t2`,(@t3) AS `@t3`
  81. select @t5;
  82. @t5
  83. 1.23456
  84. CREATE TABLE t1 (c_id INT(4) NOT NULL, c_name CHAR(20), c_country CHAR(3), PRIMARY KEY(c_id));
  85. INSERT INTO t1 VALUES (1,'Bozo','USA'),(2,'Ronald','USA'),(3,'Kinko','IRE'),(4,'Mr. Floppy','GB');
  86. SELECT @min_cid:=min(c_id), @max_cid:=max(c_id) from t1;
  87. @min_cid:=min(c_id) @max_cid:=max(c_id)
  88. 1 4
  89. SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;
  90. c_id c_name c_country
  91. 1 Bozo USA
  92. 4 Mr. Floppy GB
  93. SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;
  94. c_id c_name c_country
  95. 1 Bozo USA
  96. 4 Mr. Floppy GB
  97. ALTER TABLE t1 DROP PRIMARY KEY;
  98. select * from t1 where c_id=@min_cid OR c_id=@max_cid;
  99. c_id c_name c_country
  100. 1 Bozo USA
  101. 4 Mr. Floppy GB
  102. drop table t1;
  103. set GLOBAL max_join_size=10;
  104. set max_join_size=100;
  105. show variables like 'max_join_size';
  106. Variable_name Value
  107. max_join_size 100
  108. select * from information_schema.session_variables where variable_name like 'max_join_size';
  109. VARIABLE_NAME VARIABLE_VALUE
  110. MAX_JOIN_SIZE 100
  111. show global variables like 'max_join_size';
  112. Variable_name Value
  113. max_join_size 10
  114. select * from information_schema.global_variables where variable_name like 'max_join_size';
  115. VARIABLE_NAME VARIABLE_VALUE
  116. MAX_JOIN_SIZE 10
  117. set GLOBAL max_join_size=2000;
  118. show global variables like 'max_join_size';
  119. Variable_name Value
  120. max_join_size 2000
  121. select * from information_schema.global_variables where variable_name like 'max_join_size';
  122. VARIABLE_NAME VARIABLE_VALUE
  123. MAX_JOIN_SIZE 2000
  124. set max_join_size=DEFAULT;
  125. show variables like 'max_join_size';
  126. Variable_name Value
  127. max_join_size 2000
  128. select * from information_schema.session_variables where variable_name like 'max_join_size';
  129. VARIABLE_NAME VARIABLE_VALUE
  130. MAX_JOIN_SIZE 2000
  131. set GLOBAL max_join_size=DEFAULT;
  132. show global variables like 'max_join_size';
  133. Variable_name Value
  134. max_join_size HA_POS_ERROR
  135. select * from information_schema.global_variables where variable_name like 'max_join_size';
  136. VARIABLE_NAME VARIABLE_VALUE
  137. MAX_JOIN_SIZE HA_POS_ERROR
  138. set @@max_join_size=1000, @@global.max_join_size=2000;
  139. select @@local.max_join_size, @@global.max_join_size;
  140. @@local.max_join_size @@global.max_join_size
  141. 1000 2000
  142. select @@identity, length(@@version)>0;
  143. @@identity length(@@version)>0
  144. 0 1
  145. select @@VERSION=version();
  146. @@VERSION=version()
  147. 1
  148. select last_insert_id(345);
  149. last_insert_id(345)
  150. 345
  151. explain extended select last_insert_id(345);
  152. id select_type table type possible_keys key key_len ref rows filtered Extra
  153. 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
  154. Warnings:
  155. Note 1003 select last_insert_id(345) AS `last_insert_id(345)`
  156. select @@IDENTITY,last_insert_id(), @@identity;
  157. @@IDENTITY last_insert_id() @@identity
  158. 345 345 345
  159. explain extended select @@IDENTITY,last_insert_id(), @@identity;
  160. id select_type table type possible_keys key key_len ref rows filtered Extra
  161. 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
  162. Warnings:
  163. Note 1003 select @@IDENTITY AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,@@identity AS `@@identity`
  164. set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
  165. set global concurrent_insert=2;
  166. show variables like 'concurrent_insert';
  167. Variable_name Value
  168. concurrent_insert ALWAYS
  169. select * from information_schema.session_variables where variable_name like 'concurrent_insert';
  170. VARIABLE_NAME VARIABLE_VALUE
  171. CONCURRENT_INSERT ALWAYS
  172. set global concurrent_insert=1;
  173. show variables like 'concurrent_insert';
  174. Variable_name Value
  175. concurrent_insert AUTO
  176. select * from information_schema.session_variables where variable_name like 'concurrent_insert';
  177. VARIABLE_NAME VARIABLE_VALUE
  178. CONCURRENT_INSERT AUTO
  179. set global concurrent_insert=0;
  180. show variables like 'concurrent_insert';
  181. Variable_name Value
  182. concurrent_insert NEVER
  183. select * from information_schema.session_variables where variable_name like 'concurrent_insert';
  184. VARIABLE_NAME VARIABLE_VALUE
  185. CONCURRENT_INSERT NEVER
  186. set global concurrent_insert=DEFAULT;
  187. select @@concurrent_insert;
  188. @@concurrent_insert
  189. AUTO
  190. set global timed_mutexes=ON;
  191. show variables like 'timed_mutexes';
  192. Variable_name Value
  193. timed_mutexes ON
  194. select * from information_schema.session_variables where variable_name like 'timed_mutexes';
  195. VARIABLE_NAME VARIABLE_VALUE
  196. TIMED_MUTEXES ON
  197. set global timed_mutexes=0;
  198. show variables like 'timed_mutexes';
  199. Variable_name Value
  200. timed_mutexes OFF
  201. select * from information_schema.session_variables where variable_name like 'timed_mutexes';
  202. VARIABLE_NAME VARIABLE_VALUE
  203. TIMED_MUTEXES OFF
  204. set default_storage_engine=MYISAM, default_storage_engine="HEAP", global default_storage_engine="MERGE";
  205. show local variables like 'default_storage_engine';
  206. Variable_name Value
  207. default_storage_engine MEMORY
  208. select * from information_schema.session_variables where variable_name like 'default_storage_engine';
  209. VARIABLE_NAME VARIABLE_VALUE
  210. DEFAULT_STORAGE_ENGINE MEMORY
  211. show global variables like 'default_storage_engine';
  212. Variable_name Value
  213. default_storage_engine MRG_MYISAM
  214. select * from information_schema.global_variables where variable_name like 'default_storage_engine';
  215. VARIABLE_NAME VARIABLE_VALUE
  216. DEFAULT_STORAGE_ENGINE MRG_MYISAM
  217. set GLOBAL query_cache_size=102400;
  218. set GLOBAL myisam_max_sort_file_size=2000000;
  219. Warnings:
  220. Warning 1292 Truncated incorrect myisam_max_sort_file_size value: '2000000'
  221. show global variables like 'myisam_max_sort_file_size';
  222. Variable_name Value
  223. myisam_max_sort_file_size 1048576
  224. select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
  225. VARIABLE_NAME VARIABLE_VALUE
  226. MYISAM_MAX_SORT_FILE_SIZE 1048576
  227. set GLOBAL myisam_max_sort_file_size=default;
  228. show global variables like 'myisam_max_sort_file_size';
  229. Variable_name Value
  230. myisam_max_sort_file_size FILE_SIZE
  231. select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
  232. VARIABLE_NAME VARIABLE_VALUE
  233. MYISAM_MAX_SORT_FILE_SIZE FILE_SIZE
  234. set global net_retry_count=10, session net_retry_count=10;
  235. set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300;
  236. show global variables like 'net_%';
  237. Variable_name Value
  238. net_buffer_length 1024
  239. net_read_timeout 300
  240. net_retry_count 10
  241. net_write_timeout 200
  242. select * from information_schema.global_variables where variable_name like 'net_%' order by 1;
  243. VARIABLE_NAME VARIABLE_VALUE
  244. NET_BUFFER_LENGTH 1024
  245. NET_READ_TIMEOUT 300
  246. NET_RETRY_COUNT 10
  247. NET_WRITE_TIMEOUT 200
  248. show session variables like 'net_%';
  249. Variable_name Value
  250. net_buffer_length 16384
  251. net_read_timeout 30
  252. net_retry_count 10
  253. net_write_timeout 60
  254. select * from information_schema.session_variables where variable_name like 'net_%' order by 1;
  255. VARIABLE_NAME VARIABLE_VALUE
  256. NET_BUFFER_LENGTH 16384
  257. NET_READ_TIMEOUT 30
  258. NET_RETRY_COUNT 10
  259. NET_WRITE_TIMEOUT 60
  260. set global net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000;
  261. Warnings:
  262. Warning 1292 Truncated incorrect net_buffer_length value: '8000'
  263. show global variables like 'net_%';
  264. Variable_name Value
  265. net_buffer_length 7168
  266. net_read_timeout 900
  267. net_retry_count 10
  268. net_write_timeout 1000
  269. select * from information_schema.global_variables where variable_name like 'net_%' order by 1;
  270. VARIABLE_NAME VARIABLE_VALUE
  271. NET_BUFFER_LENGTH 7168
  272. NET_READ_TIMEOUT 900
  273. NET_RETRY_COUNT 10
  274. NET_WRITE_TIMEOUT 1000
  275. set global net_buffer_length=1;
  276. Warnings:
  277. Warning 1292 Truncated incorrect net_buffer_length value: '1'
  278. show global variables like 'net_buffer_length';
  279. Variable_name Value
  280. net_buffer_length 1024
  281. select * from information_schema.global_variables where variable_name like 'net_buffer_length';
  282. VARIABLE_NAME VARIABLE_VALUE
  283. NET_BUFFER_LENGTH 1024
  284. set global net_buffer_length=2000000000;
  285. Warnings:
  286. Warning 1292 Truncated incorrect net_buffer_length value: '2000000000'
  287. show global variables like 'net_buffer_length';
  288. Variable_name Value
  289. net_buffer_length 1048576
  290. select * from information_schema.global_variables where variable_name like 'net_buffer_length';
  291. VARIABLE_NAME VARIABLE_VALUE
  292. NET_BUFFER_LENGTH 1048576
  293. set character set cp1251_koi8;
  294. show variables like "character_set_client";
  295. Variable_name Value
  296. character_set_client cp1251
  297. select * from information_schema.session_variables where variable_name like 'character_set_client';
  298. VARIABLE_NAME VARIABLE_VALUE
  299. CHARACTER_SET_CLIENT cp1251
  300. select @@timestamp>0;
  301. @@timestamp>0
  302. 1
  303. set @@rand_seed1=10000000,@@rand_seed2=1000000;
  304. select ROUND(RAND(),5);
  305. ROUND(RAND(),5)
  306. 0.02887
  307. ==+ Testing %alloc% system variables +==
  308. ==+ NOTE: These values *must* be a multiple of 1024 +==
  309. ==+ Other values will be rounded down to nearest multiple +==
  310. ==+ Show initial values +==
  311. SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
  312. 'query_alloc_block_size', 'query_prealloc_size',
  313. 'transaction_alloc_block_size', 'transaction_prealloc_size');
  314. Variable_name Value
  315. query_alloc_block_size 8192
  316. query_prealloc_size 8192
  317. range_alloc_block_size 4096
  318. transaction_alloc_block_size 8192
  319. transaction_prealloc_size 4096
  320. ==+ information_schema data +==
  321. SELECT * FROM information_schema.session_variables
  322. WHERE variable_name IN ('range_alloc_block_size',
  323. 'query_alloc_block_size', 'query_prealloc_size',
  324. 'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1;
  325. VARIABLE_NAME VARIABLE_VALUE
  326. QUERY_ALLOC_BLOCK_SIZE 8192
  327. QUERY_PREALLOC_SIZE 8192
  328. RANGE_ALLOC_BLOCK_SIZE 4096
  329. TRANSACTION_ALLOC_BLOCK_SIZE 8192
  330. TRANSACTION_PREALLOC_SIZE 4096
  331. Testing values that are multiples of 1024
  332. set @@range_alloc_block_size=1024*15+1024;
  333. set @@query_alloc_block_size=1024*15+1024*2;
  334. set @@query_prealloc_size=1024*18-1024;
  335. set @@transaction_alloc_block_size=1024*21-1024*1;
  336. set @@transaction_prealloc_size=1024*21-2048;
  337. ==+ Check manipulated values ==+
  338. select @@query_alloc_block_size;
  339. @@query_alloc_block_size
  340. 17408
  341. SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
  342. 'query_alloc_block_size', 'query_prealloc_size',
  343. 'transaction_alloc_block_size', 'transaction_prealloc_size');
  344. Variable_name Value
  345. query_alloc_block_size 17408
  346. query_prealloc_size 17408
  347. range_alloc_block_size 16384
  348. transaction_alloc_block_size 20480
  349. transaction_prealloc_size 19456
  350. ==+ information_schema data +==
  351. SELECT * FROM information_schema.session_variables
  352. WHERE variable_name IN ('range_alloc_block_size',
  353. 'query_alloc_block_size', 'query_prealloc_size',
  354. 'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1;
  355. VARIABLE_NAME VARIABLE_VALUE
  356. QUERY_ALLOC_BLOCK_SIZE 17408
  357. QUERY_PREALLOC_SIZE 17408
  358. RANGE_ALLOC_BLOCK_SIZE 16384
  359. TRANSACTION_ALLOC_BLOCK_SIZE 20480
  360. TRANSACTION_PREALLOC_SIZE 19456
  361. ==+ Manipulate variable values +==
  362. Testing values that are not 1024 multiples
  363. set @@range_alloc_block_size=1024*16+1023;
  364. Warnings:
  365. Warning 1292 Truncated incorrect range_alloc_block_size value: '17407'
  366. set @@query_alloc_block_size=1024*17+2;
  367. Warnings:
  368. Warning 1292 Truncated incorrect query_alloc_block_size value: '17410'
  369. set @@query_prealloc_size=1024*18-1023;
  370. Warnings:
  371. Warning 1292 Truncated incorrect query_prealloc_size value: '17409'
  372. set @@transaction_alloc_block_size=1024*20-1;
  373. Warnings:
  374. Warning 1292 Truncated incorrect transaction_alloc_block_size value: '20479'
  375. set @@transaction_prealloc_size=1024*21-1;
  376. Warnings:
  377. Warning 1292 Truncated incorrect transaction_prealloc_size value: '21503'
  378. ==+ Check manipulated values ==+
  379. SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
  380. 'query_alloc_block_size', 'query_prealloc_size',
  381. 'transaction_alloc_block_size', 'transaction_prealloc_size');
  382. Variable_name Value
  383. query_alloc_block_size 17408
  384. query_prealloc_size 17408
  385. range_alloc_block_size 16384
  386. transaction_alloc_block_size 19456
  387. transaction_prealloc_size 20480
  388. ==+ information_schema data +==
  389. SELECT * FROM information_schema.session_variables
  390. WHERE variable_name IN ('range_alloc_block_size',
  391. 'query_alloc_block_size', 'query_prealloc_size',
  392. 'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1;
  393. VARIABLE_NAME VARIABLE_VALUE
  394. QUERY_ALLOC_BLOCK_SIZE 17408
  395. QUERY_PREALLOC_SIZE 17408
  396. RANGE_ALLOC_BLOCK_SIZE 16384
  397. TRANSACTION_ALLOC_BLOCK_SIZE 19456
  398. TRANSACTION_PREALLOC_SIZE 20480
  399. ==+ Set values back to the default values +==
  400. set @@range_alloc_block_size=default;
  401. set @@query_alloc_block_size=default, @@query_prealloc_size=default;
  402. set transaction_alloc_block_size=default, @@transaction_prealloc_size=default;
  403. ==+ Check the values now that they are reset +==
  404. SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
  405. 'query_alloc_block_size', 'query_prealloc_size',
  406. 'transaction_alloc_block_size', 'transaction_prealloc_size');
  407. Variable_name Value
  408. query_alloc_block_size 8192
  409. query_prealloc_size 8192
  410. range_alloc_block_size 4096
  411. transaction_alloc_block_size 8192
  412. transaction_prealloc_size 4096
  413. SELECT @@version LIKE 'non-existent';
  414. @@version LIKE 'non-existent'
  415. 0
  416. SELECT @@version_compile_os LIKE 'non-existent';
  417. @@version_compile_os LIKE 'non-existent'
  418. 0
  419. set big_tables=OFFF;
  420. ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
  421. set big_tables="OFFF";
  422. ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
  423. set unknown_variable=1;
  424. ERROR HY000: Unknown system variable 'unknown_variable'
  425. set max_join_size="hello";
  426. ERROR 42000: Incorrect argument type to variable 'max_join_size'
  427. set default_storage_engine=UNKNOWN_TABLE_TYPE;
  428. ERROR 42000: Unknown storage engine 'UNKNOWN_TABLE_TYPE'
  429. set default_storage_engine=MERGE, big_tables=2;
  430. ERROR 42000: Variable 'big_tables' can't be set to the value of '2'
  431. show local variables like 'default_storage_engine';
  432. Variable_name Value
  433. default_storage_engine MEMORY
  434. set SESSION query_cache_size=10000;
  435. ERROR HY000: Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
  436. set character_set_client=UNKNOWN_CHARACTER_SET;
  437. ERROR 42000: Unknown character set: 'UNKNOWN_CHARACTER_SET'
  438. set collation_connection=UNKNOWN_COLLATION;
  439. ERROR HY000: Unknown collation: 'UNKNOWN_COLLATION'
  440. set character_set_client=NULL;
  441. ERROR 42000: Variable 'character_set_client' can't be set to the value of 'NULL'
  442. set collation_connection=NULL;
  443. ERROR 42000: Variable 'collation_connection' can't be set to the value of 'NULL'
  444. select @@global.timestamp;
  445. ERROR HY000: Variable 'timestamp' is a SESSION variable
  446. set @@version='';
  447. ERROR HY000: Variable 'version' is a read only variable
  448. set @@concurrent_insert=1;
  449. ERROR HY000: Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL
  450. set myisam_max_sort_file_size=100;
  451. ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
  452. set @@SQL_WARNINGS=NULL;
  453. ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL'
  454. set autocommit=1;
  455. set big_tables=1;
  456. select @@autocommit, @@big_tables;
  457. @@autocommit @@big_tables
  458. 1 1
  459. set global binlog_cache_size=100;
  460. Warnings:
  461. Warning 1292 Truncated incorrect binlog_cache_size value: '100'
  462. set bulk_insert_buffer_size=100;
  463. set character set cp1251_koi8;
  464. set character set default;
  465. set @@global.concurrent_insert=1;
  466. set global connect_timeout=100;
  467. select @@delay_key_write;
  468. @@delay_key_write
  469. ON
  470. set global delay_key_write="OFF";
  471. select @@delay_key_write;
  472. @@delay_key_write
  473. OFF
  474. set global delay_key_write=ALL;
  475. select @@delay_key_write;
  476. @@delay_key_write
  477. ALL
  478. set global delay_key_write=1;
  479. select @@delay_key_write;
  480. @@delay_key_write
  481. ON
  482. set global delayed_insert_limit=100;
  483. set global delayed_insert_timeout=100;
  484. set global delayed_queue_size=100;
  485. set global flush=1;
  486. set global flush_time=100;
  487. set insert_id=1;
  488. set interactive_timeout=100;
  489. set join_buffer_size=100;
  490. Warnings:
  491. Warning 1292 Truncated incorrect join_buffer_size value: '100'
  492. set last_insert_id=1;
  493. set global local_infile=1;
  494. set long_query_time=0.000001;
  495. select @@long_query_time;
  496. @@long_query_time
  497. 0.000001
  498. set long_query_time=100.000001;
  499. select @@long_query_time;
  500. @@long_query_time
  501. 100.000001
  502. set low_priority_updates=1;
  503. set global max_allowed_packet=100;
  504. Warnings:
  505. Warning 1292 Truncated incorrect max_allowed_packet value: '100'
  506. Warning 1708 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length'
  507. set global max_binlog_cache_size=100;
  508. Warnings:
  509. Warning 1292 Truncated incorrect max_binlog_cache_size value: '100'
  510. set global max_binlog_size=100;
  511. Warnings:
  512. Warning 1292 Truncated incorrect max_binlog_size value: '100'
  513. set global max_connect_errors=100;
  514. set global max_connections=100;
  515. set global max_delayed_threads=100;
  516. set max_heap_table_size=100;
  517. Warnings:
  518. Warning 1292 Truncated incorrect max_heap_table_size value: '100'
  519. set max_join_size=100;
  520. set max_sort_length=100;
  521. set max_tmp_tables=100;
  522. set global max_user_connections=100;
  523. select @@max_user_connections;
  524. @@max_user_connections
  525. 100
  526. set global max_write_lock_count=100;
  527. set myisam_sort_buffer_size=100;
  528. Warnings:
  529. Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '100'
  530. set global net_buffer_length=100;
  531. Warnings:
  532. Warning 1292 Truncated incorrect net_buffer_length value: '100'
  533. set net_read_timeout=100;
  534. set net_write_timeout=100;
  535. set global query_cache_limit=100;
  536. set global query_cache_size=100;
  537. Warnings:
  538. Warning 1292 Truncated incorrect query_cache_size value: '100'
  539. set global query_cache_type=demand;
  540. set read_buffer_size=100;
  541. Warnings:
  542. Warning 1292 Truncated incorrect read_buffer_size value: '100'
  543. set read_rnd_buffer_size=100;
  544. set global rpl_recovery_rank=100;
  545. Warnings:
  546. Warning 1287 '@@rpl_recovery_rank' is deprecated and will be removed in a future release.
  547. set global server_id=100;
  548. set global slow_launch_time=100;
  549. set sort_buffer_size=100;
  550. Warnings:
  551. Warning 1292 Truncated incorrect sort_buffer_size value: '100'
  552. set @@max_sp_recursion_depth=10;
  553. select @@max_sp_recursion_depth;
  554. @@max_sp_recursion_depth
  555. 10
  556. set @@max_sp_recursion_depth=0;
  557. select @@max_sp_recursion_depth;
  558. @@max_sp_recursion_depth
  559. 0
  560. set sql_auto_is_null=1;
  561. select @@sql_auto_is_null;
  562. @@sql_auto_is_null
  563. 1
  564. set @@sql_auto_is_null=0;
  565. select @@sql_auto_is_null;
  566. @@sql_auto_is_null
  567. 0
  568. set sql_big_selects=1;
  569. set sql_big_tables=1;
  570. set sql_buffer_result=1;
  571. set sql_log_bin=1;
  572. set sql_log_off=1;
  573. set sql_low_priority_updates=1;
  574. set sql_quote_show_create=1;
  575. set sql_safe_updates=1;
  576. set sql_select_limit=1;
  577. set sql_select_limit=default;
  578. set sql_warnings=1;
  579. set global table_open_cache=100;
  580. set default_storage_engine=myisam;
  581. set global thread_cache_size=100;
  582. set timestamp=1, timestamp=default;
  583. set tmp_table_size=100;
  584. Warnings:
  585. Warning 1292 Truncated incorrect tmp_table_size value: '100'
  586. set tx_isolation="READ-COMMITTED";
  587. set wait_timeout=100;
  588. set log_warnings=1;
  589. set global log_warnings=1;
  590. select @@session.insert_id;
  591. @@session.insert_id
  592. 1
  593. set @save_insert_id=@@session.insert_id;
  594. set session insert_id=20;
  595. select @@session.insert_id;
  596. @@session.insert_id
  597. 20
  598. set session last_insert_id=100;
  599. select @@session.insert_id;
  600. @@session.insert_id
  601. 20
  602. select @@session.last_insert_id;
  603. @@session.last_insert_id
  604. 100
  605. select @@session.insert_id;
  606. @@session.insert_id
  607. 20
  608. set @@session.insert_id=@save_insert_id;
  609. select @@session.insert_id;
  610. @@session.insert_id
  611. 1
  612. create table t1 (a int not null auto_increment, primary key(a));
  613. create table t2 (a int not null auto_increment, primary key(a));
  614. insert into t1 values(null),(null),(null);
  615. insert into t2 values(null),(null),(null);
  616. set global key_buffer_size=100000;
  617. Warnings:
  618. Warning 1292 Truncated incorrect key_buffer_size value: '100000'
  619. select @@key_buffer_size;
  620. @@key_buffer_size
  621. 98304
  622. select * from t1 where a=2;
  623. a
  624. 2
  625. select * from t2 where a=3;
  626. a
  627. 3
  628. check table t1,t2;
  629. Table Op Msg_type Msg_text
  630. test.t1 check status OK
  631. test.t2 check status OK
  632. select max(a) +1, max(a) +2 into @xx,@yy from t1;
  633. drop table t1,t2;
  634. select @@xxxxxxxxxx;
  635. ERROR HY000: Unknown system variable 'xxxxxxxxxx'
  636. select 1;
  637. 1
  638. 1
  639. select @@session.key_buffer_size;
  640. ERROR HY000: Variable 'key_buffer_size' is a GLOBAL variable
  641. set ft_boolean_syntax = @@init_connect;
  642. ERROR HY000: Variable 'ft_boolean_syntax' is a GLOBAL variable and should be set with SET GLOBAL
  643. set global ft_boolean_syntax = @@init_connect;
  644. ERROR 42000: Variable 'ft_boolean_syntax' can't be set to the value of ''
  645. set init_connect = NULL;
  646. ERROR HY000: Variable 'init_connect' is a GLOBAL variable and should be set with SET GLOBAL
  647. set global init_connect = NULL;
  648. set ft_boolean_syntax = @@init_connect;
  649. ERROR HY000: Variable 'ft_boolean_syntax' is a GLOBAL variable and should be set with SET GLOBAL
  650. set global ft_boolean_syntax = @@init_connect;
  651. ERROR 42000: Variable 'ft_boolean_syntax' can't be set to the value of ''
  652. set global myisam_max_sort_file_size=4294967296;
  653. show global variables like 'myisam_max_sort_file_size';
  654. Variable_name Value
  655. myisam_max_sort_file_size MAX_FILE_SIZE
  656. select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
  657. VARIABLE_NAME VARIABLE_VALUE
  658. MYISAM_MAX_SORT_FILE_SIZE MAX_FILE_SIZE
  659. set global myisam_max_sort_file_size=default;
  660. select @@global.max_user_connections,@@local.max_join_size;
  661. @@global.max_user_connections @@local.max_join_size
  662. 100 100
  663. set @svc=@@global.max_user_connections, @svj=@@local.max_join_size;
  664. select @@global.max_user_connections,@@local.max_join_size;
  665. @@global.max_user_connections @@local.max_join_size
  666. 100 100
  667. set @@global.max_user_connections=111,@@local.max_join_size=222;
  668. select @@global.max_user_connections,@@local.max_join_size;
  669. @@global.max_user_connections @@local.max_join_size
  670. 111 222
  671. set @@global.max_user_connections=@@local.max_join_size,@@local.max_join_size=@@global.max_user_connections;
  672. select @@global.max_user_connections,@@local.max_join_size;
  673. @@global.max_user_connections @@local.max_join_size
  674. 222 111
  675. set @@global.max_user_connections=@svc, @@local.max_join_size=@svj;
  676. select @@global.max_user_connections,@@local.max_join_size;
  677. @@global.max_user_connections @@local.max_join_size
  678. 100 100
  679. set @a=1, @b=2;
  680. set @a=@b, @b=@a;
  681. select @a, @b;
  682. @a @b
  683. 2 1
  684. set @@global.global.key_buffer_size= 1;
  685. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size= 1' at line 1
  686. set GLOBAL global.key_buffer_size= 1;
  687. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size= 1' at line 1
  688. SELECT @@global.global.key_buffer_size;
  689. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size' at line 1
  690. SELECT @@global.session.key_buffer_size;
  691. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size' at line 1
  692. SELECT @@global.local.key_buffer_size;
  693. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size' at line 1
  694. set @tstlw = @@log_warnings;
  695. show global variables like 'log_warnings';
  696. Variable_name Value
  697. log_warnings 1
  698. select * from information_schema.global_variables where variable_name like 'log_warnings';
  699. VARIABLE_NAME VARIABLE_VALUE
  700. LOG_WARNINGS 1
  701. set global log_warnings = 0;
  702. show global variables like 'log_warnings';
  703. Variable_name Value
  704. log_warnings 0
  705. select * from information_schema.global_variables where variable_name like 'log_warnings';
  706. VARIABLE_NAME VARIABLE_VALUE
  707. LOG_WARNINGS 0
  708. set global log_warnings = 42;
  709. show global variables like 'log_warnings';
  710. Variable_name Value
  711. log_warnings 42
  712. select * from information_schema.global_variables where variable_name like 'log_warnings';
  713. VARIABLE_NAME VARIABLE_VALUE
  714. LOG_WARNINGS 42
  715. set global log_warnings = @tstlw;
  716. show global variables like 'log_warnings';
  717. Variable_name Value
  718. log_warnings 1
  719. select * from information_schema.global_variables where variable_name like 'log_warnings';
  720. VARIABLE_NAME VARIABLE_VALUE
  721. LOG_WARNINGS 1
  722. create table t1 (
  723. c1 tinyint,
  724. c2 smallint,
  725. c3 mediumint,
  726. c4 int,
  727. c5 bigint);
  728. show create table t1;
  729. Table Create Table
  730. t1 CREATE TABLE `t1` (
  731. `c1` tinyint(4) DEFAULT NULL,
  732. `c2` smallint(6) DEFAULT NULL,
  733. `c3` mediumint(9) DEFAULT NULL,
  734. `c4` int(11) DEFAULT NULL,
  735. `c5` bigint(20) DEFAULT NULL
  736. ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  737. drop table t1;
  738. set @arg00= 8, @arg01= 8.8, @arg02= 'a string', @arg03= 0.2e0;
  739. create table t1 as select @arg00 as c1, @arg01 as c2, @arg02 as c3, @arg03 as c4;
  740. show create table t1;
  741. Table Create Table
  742. t1 CREATE TABLE `t1` (
  743. `c1` bigint(20) DEFAULT NULL,
  744. `c2` decimal(65,30) DEFAULT NULL,
  745. `c3` longtext,
  746. `c4` double DEFAULT NULL
  747. ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  748. drop table t1;
  749. SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7;
  750. SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE';
  751. Variable_name Value
  752. myisam_data_pointer_size 7
  753. SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'MYISAM_DATA_POINTER_SIZE';
  754. VARIABLE_NAME VARIABLE_VALUE
  755. MYISAM_DATA_POINTER_SIZE 7
  756. SET GLOBAL table_open_cache=-1;
  757. Warnings:
  758. Warning 1292 Truncated incorrect table_open_cache value: '-1'
  759. SHOW VARIABLES LIKE 'table_open_cache';
  760. Variable_name Value
  761. table_open_cache 1
  762. SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache';
  763. VARIABLE_NAME VARIABLE_VALUE
  764. TABLE_OPEN_CACHE 1
  765. SET GLOBAL table_open_cache=DEFAULT;
  766. set character_set_results=NULL;
  767. select ifnull(@@character_set_results,"really null");
  768. ifnull(@@character_set_results,"really null")
  769. really null
  770. set names latin1;
  771. select @@have_innodb;
  772. @@have_innodb
  773. #
  774. *** Various tests with LC_TIME_NAMES
  775. *** LC_TIME_NAMES: testing case insensitivity
  776. set @@lc_time_names='ru_ru';
  777. select @@lc_time_names;
  778. @@lc_time_names
  779. ru_RU
  780. *** LC_TIME_NAMES: testing with a user variable
  781. set @lc='JA_JP';
  782. set @@lc_time_names=@lc;
  783. select @@lc_time_names;
  784. @@lc_time_names
  785. ja_JP
  786. *** LC_TIME_NAMES: testing with string expressions
  787. set lc_time_names=concat('de','_','DE');
  788. select @@lc_time_names;
  789. @@lc_time_names
  790. de_DE
  791. set lc_time_names=concat('de','+','DE');
  792. ERROR HY000: Unknown locale: 'de+DE'
  793. select @@lc_time_names;
  794. @@lc_time_names
  795. de_DE
  796. LC_TIME_NAMES: testing with numeric expressions
  797. set @@lc_time_names=1+2;
  798. select @@lc_time_names;
  799. @@lc_time_names
  800. sv_SE
  801. set @@lc_time_names=1/0;
  802. ERROR 42000: Incorrect argument type to variable 'lc_time_names'
  803. select @@lc_time_names;
  804. @@lc_time_names
  805. sv_SE
  806. set lc_time_names=en_US;
  807. LC_TIME_NAMES: testing NULL and a negative number:
  808. set lc_time_names=NULL;
  809. ERROR 42000: Variable 'lc_time_names' can't be set to the value of 'NULL'
  810. set lc_time_names=-1;
  811. ERROR HY000: Unknown locale: '-1'
  812. select @@lc_time_names;
  813. @@lc_time_names
  814. en_US
  815. LC_TIME_NAMES: testing locale with the last ID:
  816. set lc_time_names=109;
  817. select @@lc_time_names;
  818. @@lc_time_names
  819. el_GR
  820. LC_TIME_NAMES: testing a number beyond the valid ID range:
  821. set lc_time_names=110;
  822. ERROR HY000: Unknown locale: '110'
  823. select @@lc_time_names;
  824. @@lc_time_names
  825. el_GR
  826. LC_TIME_NAMES: testing that 0 is en_US:
  827. set lc_time_names=0;
  828. select @@lc_time_names;
  829. @@lc_time_names
  830. en_US
  831. select @@global.lc_time_names, @@lc_time_names;
  832. @@global.lc_time_names @@lc_time_names
  833. en_US en_US
  834. set @@global.lc_time_names=fr_FR;
  835. select @@global.lc_time_names, @@lc_time_names;
  836. @@global.lc_time_names @@lc_time_names
  837. fr_FR en_US
  838. New connection
  839. select @@global.lc_time_names, @@lc_time_names;
  840. @@global.lc_time_names @@lc_time_names
  841. fr_FR fr_FR
  842. set @@lc_time_names=ru_RU;
  843. select @@global.lc_time_names, @@lc_time_names;
  844. @@global.lc_time_names @@lc_time_names
  845. fr_FR ru_RU
  846. Returnung to default connection
  847. select @@global.lc_time_names, @@lc_time_names;
  848. @@global.lc_time_names @@lc_time_names
  849. fr_FR en_US
  850. set lc_time_names=default;
  851. select @@global.lc_time_names, @@lc_time_names;
  852. @@global.lc_time_names @@lc_time_names
  853. fr_FR fr_FR
  854. set @@global.lc_time_names=default;
  855. select @@global.lc_time_names, @@lc_time_names;
  856. @@global.lc_time_names @@lc_time_names
  857. en_US fr_FR
  858. set @@lc_time_names=default;
  859. select @@global.lc_time_names, @@lc_time_names;
  860. @@global.lc_time_names @@lc_time_names
  861. en_US en_US
  862. set @test = @@query_prealloc_size;
  863. set @@query_prealloc_size = @test;
  864. select @@query_prealloc_size = @test;
  865. @@query_prealloc_size = @test
  866. 1
  867. set global sql_mode=repeat('a',80);
  868. ERROR 42000: Variable 'sql_mode' can't be set to the value of 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
  869. End of 4.1 tests
  870. create table t1 (a int);
  871. select a into @x from t1;
  872. Warnings:
  873. Warning 1329 No data - zero rows fetched, selected, or processed
  874. show warnings;
  875. Level Code Message
  876. Warning 1329 No data - zero rows fetched, selected, or processed
  877. drop table t1;
  878. set @@warning_count=1;
  879. ERROR HY000: Variable 'warning_count' is a read only variable
  880. set @@global.error_count=1;
  881. ERROR HY000: Variable 'error_count' is a read only variable
  882. set @@max_heap_table_size= 4294967296;
  883. select @@max_heap_table_size > 0;
  884. @@max_heap_table_size > 0
  885. 1
  886. set global max_heap_table_size= 4294967296;
  887. select @@max_heap_table_size > 0;
  888. @@max_heap_table_size > 0
  889. 1
  890. set @@max_heap_table_size= 4294967296;
  891. select @@max_heap_table_size > 0;
  892. @@max_heap_table_size > 0
  893. 1
  894. select @@character_set_system;
  895. @@character_set_system
  896. utf8
  897. set global character_set_system = latin1;
  898. ERROR HY000: Variable 'character_set_system' is a read only variable
  899. set @@global.version_compile_os='234';
  900. ERROR HY000: Variable 'version_compile_os' is a read only variable
  901. set character_set_filesystem=latin1;
  902. select @@character_set_filesystem;
  903. @@character_set_filesystem
  904. latin1
  905. set @@global.character_set_filesystem=latin2;
  906. set character_set_filesystem=latin1;
  907. select @@character_set_filesystem;
  908. @@character_set_filesystem
  909. latin1
  910. set @@global.character_set_filesystem=latin2;
  911. set character_set_filesystem=default;
  912. select @@character_set_filesystem;
  913. @@character_set_filesystem
  914. latin2
  915. set @@global.character_set_filesystem=default;
  916. select @@global.character_set_filesystem;
  917. @@global.character_set_filesystem
  918. binary
  919. set @old_sql_big_selects = @@sql_big_selects;
  920. set @@sql_big_selects = 1;
  921. show variables like 'sql_big_selects';
  922. Variable_name Value
  923. sql_big_selects ON
  924. select * from information_schema.session_variables where variable_name like 'sql_big_selects';
  925. VARIABLE_NAME VARIABLE_VALUE
  926. SQL_BIG_SELECTS ON
  927. set @@sql_big_selects = @old_sql_big_selects;
  928. set @@sql_notes = 0, @@sql_warnings = 0;
  929. show variables like 'sql_notes';
  930. Variable_name Value
  931. sql_notes OFF
  932. select * from information_schema.session_variables where variable_name like 'sql_notes';
  933. VARIABLE_NAME VARIABLE_VALUE
  934. SQL_NOTES OFF
  935. show variables like 'sql_warnings';
  936. Variable_name Value
  937. sql_warnings OFF
  938. select * from information_schema.session_variables where variable_name like 'sql_warnings';
  939. VARIABLE_NAME VARIABLE_VALUE
  940. SQL_WARNINGS OFF
  941. set @@sql_notes = 1, @@sql_warnings = 1;
  942. show variables like 'sql_notes';
  943. Variable_name Value
  944. sql_notes ON
  945. select * from information_schema.session_variables where variable_name like 'sql_notes';
  946. VARIABLE_NAME VARIABLE_VALUE
  947. SQL_NOTES ON
  948. show variables like 'sql_warnings';
  949. Variable_name Value
  950. sql_warnings ON
  951. select * from information_schema.session_variables where variable_name like 'sql_warnings';
  952. VARIABLE_NAME VARIABLE_VALUE
  953. SQL_WARNINGS ON
  954. select @@system_time_zone;
  955. @@system_time_zone
  956. #
  957. select @@version, @@version_comment, @@version_compile_machine,
  958. @@version_compile_os;
  959. @@version @@version_comment @@version_compile_machine @@version_compile_os
  960. # # # #
  961. select @@basedir, @@datadir, @@tmpdir;
  962. @@basedir @@datadir @@tmpdir
  963. # # #
  964. show variables like 'basedir';
  965. Variable_name Value
  966. basedir #
  967. select * from information_schema.session_variables where variable_name like 'basedir';
  968. VARIABLE_NAME VARIABLE_VALUE
  969. BASEDIR #
  970. show variables like 'datadir';
  971. Variable_name Value
  972. datadir #
  973. select * from information_schema.session_variables where variable_name like 'datadir';
  974. VARIABLE_NAME VARIABLE_VALUE
  975. DATADIR #
  976. show variables like 'tmpdir';
  977. Variable_name Value
  978. tmpdir #
  979. select * from information_schema.session_variables where variable_name like 'tmpdir';
  980. VARIABLE_NAME VARIABLE_VALUE
  981. TMPDIR #
  982. select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key;
  983. @@ssl_ca @@ssl_capath @@ssl_cert @@ssl_cipher @@ssl_key
  984. # # # # #
  985. show variables like 'ssl%';
  986. Variable_name Value
  987. ssl_ca #
  988. ssl_capath #
  989. ssl_cert #
  990. ssl_cipher #
  991. ssl_key #
  992. select * from information_schema.session_variables where variable_name like 'ssl%' order by 1;
  993. VARIABLE_NAME VARIABLE_VALUE
  994. SSL_CA #
  995. SSL_CAPATH #
  996. SSL_CERT #
  997. SSL_CIPHER #
  998. SSL_KEY #
  999. select @@log_queries_not_using_indexes;
  1000. @@log_queries_not_using_indexes
  1001. 0
  1002. show variables like 'log_queries_not_using_indexes';
  1003. Variable_name Value
  1004. log_queries_not_using_indexes OFF
  1005. select * from information_schema.session_variables where variable_name like 'log_queries_not_using_indexes';
  1006. VARIABLE_NAME VARIABLE_VALUE
  1007. LOG_QUERIES_NOT_USING_INDEXES OFF
  1008. select @@"";
  1009. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '""' at line 1
  1010. select @@&;
  1011. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '&' at line 1
  1012. select @@@;
  1013. ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@' at line 1
  1014. select @@hostname;
  1015. @@hostname
  1016. #
  1017. set @@hostname= "anothername";
  1018. ERROR HY000: Variable 'hostname' is a read only variable
  1019. show variables like 'hostname';
  1020. Variable_name Value
  1021. hostname #
  1022. #
  1023. # BUG#37408 - Compressed MyISAM files should not require/use mmap()
  1024. #
  1025. # Test 'myisam_mmap_size' option is not dynamic
  1026. SET @@myisam_mmap_size= 500M;
  1027. ERROR HY000: Variable 'myisam_mmap_size' is a read only variable
  1028. #
  1029. # Bug #52315: utc_date() crashes when system time > year 2037
  1030. #
  1031. SET TIMESTAMP=2*1024*1024*1024;
  1032. #Should not crash
  1033. SELECT UTC_DATE();
  1034. SET TIMESTAMP=DEFAULT;
  1035. End of 5.0 tests
  1036. set join_buffer_size=1;
  1037. Warnings:
  1038. Warning 1292 Truncated incorrect join_buffer_size value: '1'
  1039. set @save_join_buffer_size=@@join_buffer_size;
  1040. set join_buffer_size=@save_join_buffer_size;
  1041. set global binlog_cache_size =@my_binlog_cache_size;
  1042. set global connect_timeout =@my_connect_timeout;
  1043. set global delayed_insert_timeout =@my_delayed_insert_timeout;
  1044. set global delayed_queue_size =@my_delayed_queue_size;
  1045. set global flush =@my_flush;
  1046. set global flush_time =@my_flush_time;
  1047. set global key_buffer_size =@my_key_buffer_size;
  1048. set global max_binlog_cache_size =@my_max_binlog_cache_size;
  1049. set global max_binlog_size =@my_max_binlog_size;
  1050. set global max_connect_errors =@my_max_connect_errors;
  1051. set global max_connections =@my_max_connections;
  1052. set global max_delayed_threads =@my_max_delayed_threads;
  1053. set global max_heap_table_size =@my_max_heap_table_size;
  1054. set global max_insert_delayed_threads=@my_max_insert_delayed_threads;
  1055. set global max_join_size =@my_max_join_size;
  1056. set global max_user_connections =default;
  1057. set global max_write_lock_count =default;
  1058. set global myisam_data_pointer_size =@my_myisam_data_pointer_size;
  1059. set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size;
  1060. set global net_buffer_length =@my_net_buffer_length;
  1061. Warnings:
  1062. Warning 1708 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length'
  1063. set global net_write_timeout =@my_net_write_timeout;
  1064. set global net_read_timeout =@my_net_read_timeout;
  1065. set global query_cache_limit =@my_query_cache_limit;
  1066. set global query_cache_type =@my_query_cache_type;
  1067. set global rpl_recovery_rank =@my_rpl_recovery_rank;
  1068. Warnings:
  1069. Warning 1287 '@@rpl_recovery_rank' is deprecated and will be removed in a future release.
  1070. set global server_id =@my_server_id;
  1071. set global slow_launch_time =@my_slow_launch_time;
  1072. set global default_storage_engine =@my_storage_engine;
  1073. set global thread_cache_size =@my_thread_cache_size;
  1074. set global max_allowed_packet =@my_max_allowed_packet;
  1075. set global join_buffer_size =@my_join_buffer_size;
  1076. show global variables where Variable_name='table_definition_cache';
  1077. Variable_name Value
  1078. table_definition_cache #
  1079. # --
  1080. # -- Bug#34820: log_output can be set to illegal value.
  1081. # --
  1082. SET GLOBAL log_output = '';
  1083. ERROR 42000: Variable 'log_output' can't be set to the value of ''
  1084. SET GLOBAL log_output = 0;
  1085. ERROR 42000: Variable 'log_output' can't be set to the value of '0'
  1086. # -- End of Bug#34820.
  1087. #
  1088. SHOW VARIABLES like 'ft_max_word_len';
  1089. Variable_name Value
  1090. ft_max_word_len 84
  1091. SELECT @@session.ft_max_word_len;
  1092. ERROR HY000: Variable 'ft_max_word_len' is a GLOBAL variable
  1093. SELECT @@global.ft_max_word_len;
  1094. @@global.ft_max_word_len
  1095. 84
  1096. SET @@session.ft_max_word_len= 7;
  1097. ERROR HY000: Variable 'ft_max_word_len' is a read only variable
  1098. SET @@global.ft_max_word_len= 7;
  1099. ERROR HY000: Variable 'ft_max_word_len' is a read only variable
  1100. #
  1101. SHOW VARIABLES like 'ft_min_word_len';
  1102. Variable_name Value
  1103. ft_min_word_len 4
  1104. SELECT @@session.ft_min_word_len;
  1105. ERROR HY000: Variable 'ft_min_word_len' is a GLOBAL variable
  1106. SELECT @@global.ft_min_word_len;
  1107. @@global.ft_min_word_len
  1108. 4
  1109. SET @@session.ft_min_word_len= 7;
  1110. ERROR HY000: Variable 'ft_min_word_len' is a read only variable
  1111. SET @@global.ft_min_word_len= 7;
  1112. ERROR HY000: Variable 'ft_min_word_len' is a read only variable
  1113. #
  1114. SHOW VARIABLES like 'ft_query_expansion_limit';
  1115. Variable_name Value
  1116. ft_query_expansion_limit 20
  1117. SELECT @@session.ft_query_expansion_limit;
  1118. ERROR HY000: Variable 'ft_query_expansion_limit' is a GLOBAL variable
  1119. SELECT @@global.ft_query_expansion_limit;
  1120. @@global.ft_query_expansion_limit
  1121. 20
  1122. SET @@session.ft_query_expansion_limit= 7;
  1123. ERROR HY000: Variable 'ft_query_expansion_limit' is a read only variable
  1124. SET @@global.ft_query_expansion_limit= 7;
  1125. ERROR HY000: Variable 'ft_query_expansion_limit' is a read only variable
  1126. #
  1127. SHOW VARIABLES like 'ft_stopword_file';
  1128. Variable_name Value
  1129. ft_stopword_file (built-in)
  1130. SELECT @@session.ft_stopword_file;
  1131. ERROR HY000: Variable 'ft_stopword_file' is a GLOBAL variable
  1132. SELECT @@global.ft_stopword_file;
  1133. @@global.ft_stopword_file
  1134. (built-in)
  1135. SET @@session.ft_stopword_file= 'x';
  1136. ERROR HY000: Variable 'ft_stopword_file' is a read only variable
  1137. SET @@global.ft_stopword_file= 'x';
  1138. ERROR HY000: Variable 'ft_stopword_file' is a read only variable
  1139. #
  1140. SHOW VARIABLES like 'back_log';
  1141. Variable_name Value
  1142. back_log 50
  1143. SELECT @@session.back_log;
  1144. ERROR HY000: Variable 'back_log' is a GLOBAL variable
  1145. SELECT @@global.back_log;
  1146. @@global.back_log
  1147. 50
  1148. SET @@session.back_log= 7;
  1149. ERROR HY000: Variable 'back_log' is a read only variable
  1150. SET @@global.back_log= 7;
  1151. ERROR HY000: Variable 'back_log' is a read only variable
  1152. #
  1153. SHOW VARIABLES like 'large_files_support';
  1154. Variable_name Value
  1155. large_files_support #
  1156. SELECT @@session.large_files_support;
  1157. ERROR HY000: Variable 'large_files_support' is a GLOBAL variable
  1158. SELECT @@global.large_files_support;
  1159. @@global.large_files_support
  1160. #
  1161. SET @@session.large_files_support= true;
  1162. ERROR HY000: Variable 'large_files_support' is a read only variable
  1163. SET @@global.large_files_support= true;
  1164. ERROR HY000: Variable 'large_files_support' is a read only variable
  1165. #
  1166. SHOW VARIABLES like 'character_sets_dir';
  1167. Variable_name Value
  1168. character_sets_dir #
  1169. SELECT @@session.character_sets_dir;
  1170. ERROR HY000: Variable 'character_sets_dir' is a GLOBAL variable
  1171. SELECT @@global.character_sets_dir;
  1172. @@global.character_sets_dir
  1173. #
  1174. SET @@session.character_sets_dir= 'x';
  1175. ERROR HY000: Variable 'character_sets_dir' is a read only variable
  1176. SET @@global.character_sets_dir= 'x';
  1177. ERROR HY000: Variable 'character_sets_dir' is a read only variable
  1178. #
  1179. SHOW VARIABLES like 'init_file';
  1180. Variable_name Value
  1181. init_file #
  1182. SELECT @@session.init_file;
  1183. ERROR HY000: Variable 'init_file' is a GLOBAL variable
  1184. SELECT @@global.init_file;
  1185. @@global.init_file
  1186. #
  1187. SET @@session.init_file= 'x';
  1188. ERROR HY000: Variable 'init_file' is a read only variable
  1189. SET @@global.init_file= 'x';
  1190. ERROR HY000: Variable 'init_file' is a read only variable
  1191. #
  1192. SHOW VARIABLES like 'lc_messages_dir';
  1193. Variable_name Value
  1194. lc_messages_dir #
  1195. SELECT @@session.lc_messages_dir;
  1196. ERROR HY000: Variable 'lc_messages_dir' is a GLOBAL variable
  1197. SELECT @@global.lc_messages_dir;
  1198. @@global.lc_messages_dir
  1199. #
  1200. SET @@session.lc_messages_dir= 'x';
  1201. ERROR HY000: Variable 'lc_messages_dir' is a read only variable
  1202. SET @@global.lc_messages_dir= 'x';
  1203. ERROR HY000: Variable 'lc_messages_dir' is a read only variable
  1204. #
  1205. SHOW VARIABLES like 'large_page_size';
  1206. Variable_name Value
  1207. large_page_size #
  1208. SELECT @@session.large_page_size;
  1209. ERROR HY000: Variable 'large_page_size' is a GLOBAL variable
  1210. SELECT @@global.large_page_size;
  1211. @@global.large_page_size
  1212. #
  1213. SET @@session.large_page_size= 7;
  1214. ERROR HY000: Variable 'large_page_size' is a read only variable
  1215. SET @@global.large_page_size= 7;
  1216. ERROR HY000: Variable 'large_page_size' is a read only variable
  1217. #
  1218. SHOW VARIABLES like 'large_pages';
  1219. Variable_name Value
  1220. large_pages #
  1221. SELECT @@session.large_pages;
  1222. ERROR HY000: Variable 'large_pages' is a GLOBAL variable
  1223. SELECT @@global.large_pages;
  1224. @@global.large_pages
  1225. #
  1226. SET @@session.large_pages= true;
  1227. ERROR HY000: Variable 'large_pages' is a read only variable
  1228. SET @@global.large_pages= true;
  1229. ERROR HY000: Variable 'large_pages' is a read only variable
  1230. #
  1231. SHOW VARIABLES like 'log_bin';
  1232. Variable_name Value
  1233. log_bin OFF
  1234. SELECT @@session.log_bin;
  1235. ERROR HY000: Variable 'log_bin' is a GLOBAL variable
  1236. SELECT @@global.log_bin;
  1237. @@global.log_bin
  1238. 0
  1239. SET @@session.log_bin= true;
  1240. ERROR HY000: Variable 'log_bin' is a read only variable
  1241. SET @@global.log_bin= true;
  1242. ERROR HY000: Variable 'log_bin' is a read only variable
  1243. #
  1244. SHOW VARIABLES like 'log_error';
  1245. Variable_name Value
  1246. log_error #
  1247. SELECT @@session.log_error;
  1248. ERROR HY000: Variable 'log_error' is a GLOBAL variable
  1249. SELECT @@global.log_error;
  1250. @@global.log_error
  1251. #
  1252. SET @@session.log_error= 'x';
  1253. ERROR HY000: Variable 'log_error' is a read only variable
  1254. SET @@global.log_error= 'x';
  1255. ERROR HY000: Variable 'log_error' is a read only variable
  1256. #
  1257. SHOW VARIABLES like 'lower_case_file_system';
  1258. Variable_name Value
  1259. lower_case_file_system #
  1260. SELECT @@session.lower_case_file_system;
  1261. ERROR HY000: Variable 'lower_case_file_system' is a GLOBAL variable
  1262. SELECT @@global.lower_case_file_system;
  1263. @@global.lower_case_file_system
  1264. #
  1265. SET @@session.lower_case_file_system= true;
  1266. ERROR HY000: Variable 'lower_case_file_system' is a read only variable
  1267. SET @@global.lower_case_file_system= true;
  1268. ERROR HY000: Variable 'lower_case_file_system' is a read only variable
  1269. #
  1270. SHOW VARIABLES like 'lower_case_table_names';
  1271. Variable_name Value
  1272. lower_case_table_names #
  1273. SELECT @@session.lower_case_table_names;
  1274. ERROR HY000: Variable 'lower_case_table_names' is a GLOBAL variable
  1275. SELECT @@global.lower_case_table_names;
  1276. @@global.lower_case_table_names
  1277. #
  1278. SET @@session.lower_case_table_names= 7;
  1279. ERROR HY000: Variable 'lower_case_table_names' is a read only variable
  1280. SET @@global.lower_case_table_names= 7;
  1281. ERROR HY000: Variable 'lower_case_table_names' is a read only variable
  1282. #
  1283. SHOW VARIABLES like 'myisam_recover_options';
  1284. Variable_name Value
  1285. myisam_recover_options OFF
  1286. SELECT @@session.myisam_recover_options;
  1287. ERROR HY000: Variable 'myisam_recover_options' is a GLOBAL variable
  1288. SELECT @@global.myisam_recover_options;
  1289. @@global.myisam_recover_options
  1290. OFF
  1291. SET @@session.myisam_recover_options= 'x';
  1292. ERROR HY000: Variable 'myisam_recover_options' is a read only variable
  1293. SET @@global.myisam_recover_options= 'x';
  1294. ERROR HY000: Variable 'myisam_recover_options' is a read only variable
  1295. #
  1296. SHOW VARIABLES like 'open_files_limit';
  1297. Variable_name Value
  1298. open_files_limit #
  1299. SELECT @@session.open_files_limit;
  1300. ERROR HY000: Variable 'open_files_limit' is a GLOBAL variable
  1301. SELECT @@global.open_files_limit;
  1302. @@global.open_files_limit
  1303. #
  1304. SET @@session.open_files_limit= 7;
  1305. ERROR HY000: Variable 'open_files_limit' is a read only variable
  1306. SET @@global.open_files_limit= 7;
  1307. ERROR HY000: Variable 'open_files_limit' is a read only variable
  1308. #
  1309. SHOW VARIABLES like 'pid_file';
  1310. Variable_name Value
  1311. pid_file #
  1312. SELECT @@session.pid_file;
  1313. ERROR HY000: Variable 'pid_file' is a GLOBAL variable
  1314. SELECT @@global.pid_file;
  1315. @@global.pid_file
  1316. #
  1317. SET @@session.pid_file= 'x';
  1318. ERROR HY000: Variable 'pid_file' is a read only variable
  1319. SET @@global.pid_file= 'x';
  1320. ERROR HY000: Variable 'pid_file' is a read only variable
  1321. #
  1322. SHOW VARIABLES like 'plugin_dir';
  1323. Variable_name Value
  1324. plugin_dir #
  1325. SELECT @@session.plugin_dir;
  1326. ERROR HY000: Variable 'plugin_dir' is a GLOBAL variable
  1327. SELECT @@global.plugin_dir;
  1328. @@global.plugin_dir
  1329. #
  1330. SET @@session.plugin_dir= 'x';
  1331. ERROR HY000: Variable 'plugin_dir' is a read only variable
  1332. SET @@global.plugin_dir= 'x';
  1333. ERROR HY000: Variable 'plugin_dir' is a read only variable
  1334. #
  1335. SHOW VARIABLES like 'port';
  1336. Variable_name Value
  1337. port #
  1338. SELECT @@session.port;
  1339. ERROR HY000: Variable 'port' is a GLOBAL variable
  1340. SELECT @@global.port;
  1341. @@global.port
  1342. #
  1343. SET @@session.port= 7;
  1344. ERROR HY000: Variable 'port' is a read only variable
  1345. SET @@global.port= 7;
  1346. ERROR HY000: Variable 'port' is a read only variable
  1347. #
  1348. SHOW VARIABLES like 'protocol_version';
  1349. Variable_name Value
  1350. protocol_version 10
  1351. SELECT @@session.protocol_version;
  1352. ERROR HY000: Variable 'protocol_version' is a GLOBAL variable
  1353. SELECT @@global.protocol_version;
  1354. @@global.protocol_version
  1355. 10
  1356. SET @@session.protocol_version= 7;
  1357. ERROR HY000: Variable 'protocol_version' is a read only variable
  1358. SET @@global.protocol_version= 7;
  1359. ERROR HY000: Variable 'protocol_version' is a read only variable
  1360. #
  1361. SHOW VARIABLES like 'skip_external_locking';
  1362. Variable_name Value
  1363. skip_external_locking ON
  1364. SELECT @@session.skip_external_locking;
  1365. ERROR HY000: Variable 'skip_external_locking' is a GLOBAL variable
  1366. SELECT @@global.skip_external_locking;
  1367. @@global.skip_external_locking
  1368. 1
  1369. SET @@session.skip_external_locking= true;
  1370. ERROR HY000: Variable 'skip_external_locking' is a read only variable
  1371. SET @@global.skip_external_locking= true;
  1372. ERROR HY000: Variable 'skip_external_locking' is a read only variable
  1373. #
  1374. SHOW VARIABLES like 'skip_networking';
  1375. Variable_name Value
  1376. skip_networking OFF
  1377. SELECT @@session.skip_networking;
  1378. ERROR HY000: Variable 'skip_networking' is a GLOBAL variable
  1379. SELECT @@global.skip_networking;
  1380. @@global.skip_networking
  1381. 0
  1382. SET @@session.skip_networking= true;
  1383. ERROR HY000: Variable 'skip_networking' is a read only variable
  1384. SET @@global.skip_networking= true;
  1385. ERROR HY000: Variable 'skip_networking' is a read only variable
  1386. #
  1387. SHOW VARIABLES like 'skip_show_database';
  1388. Var

Large files files are truncated, but you can click here to view the full file