Shulou(Shulou.com)05/31 Report--

This article focuses on "how to solve the Errcode: 17-File exists problem in MYSQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to solve the Errcode: 17-File exists problem in MYSQL.

Phenomenon:

Execute the following errors in the show fields from a Times

Error: Can't create/write to file'/ tmp/#sql_419_0.MYI' (Errcode: 17-File exists): SHOW FULL FIELDS FROM `a`

Solution:

Under / tmp, just remove the related # sql file

Reproduce:

[root@10-19-187240 tmp] # ll | grep sql-rw-r--r-- 1 root root 531453 January 10 19:55 a.sqllrwxrwxrwx 1 root root 25 January 9 18:14 mysql.sock- > / var/lib/mysql/mysql.sock-rw-rw---- 1 mysql mysql 98304 January 21 21:23 test.ibd-rw-rw---- 1 mysql mysql 7 January 22 11:15 t_myisam.MYD -rw-rw---- 1 mysql mysql 1024 January 22 11:15 when t_myisam.MYIshow fileds starts execution: [root@10-19-187240 tmp] # ll | grep sql-rw-r--r-- 1 root root 531453 January 10 19:55 a.sqllrwxrwxrwx 1 root root 25 January 9 18:14 mysql.sock- > / var/lib/mysql/mysql.sock-rw-rw---- 1 mysql mysql 02 February 14 14:20 # sql_5ec_0.MYD-rw-rw---- 1 mysql mysql 1024 February 14 14:20 # sql_5ec_0.MYI-rw-rw---- 1 mysql mysql 98304 January 21 21:23 test.ibd-rw-rw---- 1 mysql mysql 7 January 22 11:15 tacticimyisam.MYDMui RWQ RWLV Lok-1 mysql mysql 1024 January 22 11:15 t _ myisam.MYI [root @ 10-19-187240tmp] # [root@10-19187240tmp] # [root@10-19187240tmp] # cp "# sql_5ec_0.MYD" ~ / [root@10-19187240tmp] # cp "# sql_5ec_0.MYI" ~ / show filelds after execution: [root@10-19187240tmp] # ll | grep sql-rw-r--r-- 1 root root 531453 January 10 19:55 a.sqllrwxrwxrwx 1 root root 25 January 9 18:14 mysql.sock- > / var/lib/mysql/mysql.sock-rw-rw---- 1 mysql mysql 98304 January 21 21:23 test.ibd-rw-rw---- 1 mysql mysql 7 January 22 11:15 tactimyisam.myisam.Myi [root @ 10-19-187- 240tmp] # [root@10-19187240tmp] # return the original file copy [root@10-19187240tmp] # cp ~ / "# sql_5ec_0.MYD". / [root@10-19187240tmp] # cp ~ / "# sql_5ec_0.MYI". / [root@10-1918187240tmp] # ll Total usage 20532Furer sql_5ec_0.MYI-1 root root 531453 January 10 19:55 a.sqldrwxr-xr-x 6 root root 4096 January 12 14:50 data-rw-r--r-- 1 root root 20376794 January 14 21:34 data.ziplrwxrwxrwx 1 root root 25 January 9 18:14 mysql.sock- > / var/lib/mysql/mysql.sock-rw-r- 1 root root 0 February 14 14:21 # sql_5ec_0.MYD-rw-r- 1 root root 1024 February 14 14:21 # sql_5ec_0.MYI-rw-rw---- 1 mysql mysql 98304 January 21 21:23 test.ibd-rw-rw---- 1 mysql mysql 7 January 22 11:15 tympanmyisam.MYDMUR RWKui RWKui-1 mysql mysql 1024 January 22 11:15 t_myisam.MYI

Execute show fields again

You can reproduce the above error.

Calling procedure:

# 0 fill_schema_table_by_open (thd=0x2293ee0, is_show_fields_or_keys=true, table=0x7fbed405c310, schema_table=0x1531980, orig_db_name=0x7fbf00dfea20, orig_table_name=0x7fbf00dfea10, open_tables_state_backup=0x7fbf00dfe8b0, can_deadlock=false) at / data/mysql-5.6.35/sql/sql_show.cc:3470#1 0x000000000084107e in get_all_tables (thd=0x2293ee0, tables=0x7fbed4006090 Cond=0x0) at / data/mysql-5.6.35/sql/sql_show.cc:4131#2 0x000000000084f655 in do_fill_table (thd=0x2293ee0, table_list=0x7fbed4006090, join_table=0x7fbed4065358) at / data/mysql-5.6.35/sql/sql_show.cc:7433#3 0x000000000084fa2c in get_schema_tables_result (join=0x7fbed405aae0 Executed_place=PROCESSED_BY_JOIN_EXEC) at / data/mysql-5.6.35/sql/sql_show.cc:7534#4 0x00000000008238c7 in JOIN::prepare_result (this=0x7fbed405aae0, columns_list=0x7fbf00dfeda0) at / data/mysql-5.6.35/sql/sql_select.cc:822#5 0x00000000007c10ef in JOIN::exec (this=0x7fbed405aae0) at / data/mysql-5.6.35/sql/sql_executor.cc:116#6 0x0000000000824408 in mysql_execute_select (thd=0x2293ee0, select_lex=0x2296478) Free_join=true) at / data/mysql-5.6.35/sql/sql_select.cc:1101#7 0x000000000082471f in mysql_select (thd=0x2293ee0, tables=0x7fbed4006090, wild_num=0, fields=..., conds=0x0, order=0x2296640, group=0x2296578, having=0x0, select_options=2684619520, result=0x7fbed405aab8, unit=0x2295e30, select_lex=0x2296478) at / data/mysql-5.6.35/sql/sql_select.cc:1222#8 0x000000000082273b in handle_select (thd=0x2293ee0, result=0x7fbed405aab8) Setup_tables_done_option=0) at / data/mysql-5.6.35/sql/sql_select.cc:110#9 0x00000000007fb93e in execute_sqlcom_select (thd=0x2293ee0, all_tables=0x7fbed4006090) at / data/mysql-5.6.35/sql/sql_parse.cc:5181#10 0x00000000007f4081 in mysql_execute_command (thd=0x2293ee0) at / data/mysql-5.6.35/sql/sql_parse.cc:2689---Type to continue Or q to quit---#11 0x00000000007fe4bc in mysql_parse (thd=0x2293ee0, rawbuf=0x7fbed4005030 "show fields from a", length=18, parser_state=0x7fbf00e00670) at / data/mysql-5.6.35/sql/sql_parse.cc:6433#12 0x00000000007f0f7d in dispatch_command (command=COM_QUERY, thd=0x2293ee0, packet=0x2368eb1 "show fields from a" Packet_length=18) at / data/mysql-5.6.35/sql/sql_parse.cc:1372#13 0x00000000007eff56 in do_command (thd=0x2293ee0) at / data/mysql-5.6.35/sql/sql_parse.cc:1039#14 0x00000000007b5f35 in do_handle_one_connection (thd_arg=0x2293ee0) at / data/mysql-5.6.35/sql/sql_connect.cc:982#15 0x00000000007b5cca in handle_one_connection (arg=0x2293ee0) at / data/mysql-5.6.35/sql/sql_connect.cc:899#16 0x0000000000b33da0 in pfs_spawn_thread (arg=0x21e64c0) at / data/mysql-5.6.35/storage/perfschema/pfs.cc:1860#17 0x00007fbf2c56faa1 in start_thread () from / lib64/libpthread.so.0#18 0x00007fbf2b2d8aad in clone () from / lib64/libc.so.6

After a step-by-step inspection, it was found that

Create_tmp_table (thd=0x2293ee0, param=0x7fbed405a050

Fields=..., group=0x0, distinct=false

Save_sum_fields=false, select_options=2147752704

Rows_limit=18446744073709551615

Table_alias=0x7fbed4006088 "COLUMNS")

At / data/mysql-5.6.35/sql/ http://sql_tmp_table.cc:455

The generated temporary table.

Actually looking at the table in information_schema

At this point, I believe you have a deeper understanding of "how to solve the Errcode: 17-File exists problem in MYSQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.