| Bug #10487 | DROP TABLE does not delete .ibd files when "One File Per Table" is set | ||
|---|---|---|---|
| Submitted: | 9 May 2005 18:10 | Modified: | 1 Jun 2005 16:10 |
| Reporter: | Anker Berg-Sonne | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 4.1.11 | OS: | Windows (Windows Server 2000 SP1) |
| Assigned to: | CPU Architecture: | Any | |
[9 May 2005 18:10]
Anker Berg-Sonne
[11 May 2005 7:25]
Hartmut Holzgraefe
Couldn't reproduce this on Linux, looks like a windows only issue?
[11 May 2005 7:50]
Heikki Tuuri
Anker,
please post the complete, unedited contents of the .err log. It is in the datadir of MySQL. You have probably a mismatch between existing .ibd files and the internal data dictionary of InnoDB.
DROP TABLE does work in the Windows version of 4.1.11, also if innodb_file_per_table is set.
Regards,
Heikki
C:\Program Files\MySQL\MySQL Server 4.1\bin>mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.11
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show variables;
+---------------------------------+---------------------------------------------
----------------+
| Variable_name | Value
|
+---------------------------------+---------------------------------------------
----------------+
| back_log | 50
|
| basedir | C:\Program Files\MySQL\MySQL Server 4.1\
|
| binlog_cache_size | 32768
|
| bulk_insert_buffer_size | 8388608
|
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_results | latin1
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 4.1\shar
e\charsets/ |
| collation_connection | latin1_swedish_ci
|
| collation_database | latin1_swedish_ci
|
| collation_server | latin1_swedish_ci
|
| concurrent_insert | ON
|
| connect_timeout | 5
|
| datadir | C:\Program Files\MySQL\MySQL Server 4.1\data
\ |
| date_format | %Y-%m-%d
|
| datetime_format | %Y-%m-%d %H:%i:%s
|
| default_week_format | 0
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| expire_logs_days | 0
|
| flush | OFF
|
| flush_time | 1800
|
| ft_boolean_syntax | + -><()~*:""&|
|
| ft_max_word_len | 84
|
| ft_min_word_len | 4
|
| ft_query_expansion_limit | 20
|
| ft_stopword_file | (built-in)
|
| group_concat_max_len | 1024
|
| have_archive | NO
|
| have_bdb | NO
|
| have_blackhole_engine | NO
|
| have_compress | YES
|
| have_crypt | NO
|
| have_csv | NO
|
| have_example_engine | NO
|
| have_geometry | YES
|
| have_innodb | YES
|
| have_isam | NO
|
| have_ndbcluster | NO
|
| have_openssl | NO
|
| have_query_cache | YES
|
| have_raid | NO
|
| have_rtree_keys | YES
|
| have_symlink | YES
|
| init_connect |
|
| init_file |
|
| init_slave |
|
| innodb_additional_mem_pool_size | 1048576
|
| innodb_autoextend_increment | 8
|
| innodb_buffer_pool_awe_mem_mb | 0
|
| innodb_buffer_pool_size | 8388608
|
| innodb_data_file_path | ibdata1:10M:autoextend
|
| innodb_data_home_dir |
|
| innodb_fast_shutdown | ON
|
| innodb_file_io_threads | 4
|
| innodb_file_per_table | ON
|
| innodb_flush_log_at_trx_commit | 1
|
| innodb_flush_method | unbuffered
|
| innodb_force_recovery | 0
|
| innodb_lock_wait_timeout | 50
|
| innodb_locks_unsafe_for_binlog | OFF
|
| innodb_log_arch_dir |
|
| innodb_log_archive | OFF
|
| innodb_log_buffer_size | 1048576
|
| innodb_log_file_size | 5242880
|
| innodb_log_files_in_group | 2
|
| innodb_log_group_home_dir | .\
|
| innodb_max_dirty_pages_pct | 90
|
| innodb_max_purge_lag | 0
|
| innodb_mirrored_log_groups | 1
|
| innodb_open_files | 300
|
| innodb_table_locks | ON
|
| innodb_thread_concurrency | 8
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 8388600
|
| key_cache_age_threshold | 300
|
| key_cache_block_size | 1024
|
| key_cache_division_limit | 100
|
| language | C:\Program Files\MySQL\MySQL Server 4.1\shar
e\english\ |
| large_files_support | ON
|
| license | GPL
|
| local_infile | ON
|
| log | OFF
|
| log_bin | OFF
|
| log_error |
|
| log_slave_updates | OFF
|
| log_slow_queries | OFF
|
| log_update | OFF
|
| log_warnings | 1
|
| long_query_time | 10
|
| low_priority_updates | OFF
|
| lower_case_file_system | OFF
|
| lower_case_table_names | 1
|
| max_allowed_packet | 1048576
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connect_errors | 10
|
| max_connections | 100
|
| max_delayed_threads | 20
|
| max_error_count | 64
|
| max_heap_table_size | 16777216
|
| max_insert_delayed_threads | 20
|
| max_join_size | 4294967295
|
| max_length_for_sort_data | 1024
|
| max_relay_log_size | 0
|
| max_seeks_for_key | 4294967295
|
| max_sort_length | 1024
|
| max_tmp_tables | 32
|
| max_user_connections | 0
|
| max_write_lock_count | 4294967295
|
| myisam_data_pointer_size | 4
|
| myisam_max_extra_sort_file_size | 2147483648
|
| myisam_max_sort_file_size | 2147483647
|
| myisam_recover_options | OFF
|
| myisam_repair_threads | 1
|
| myisam_sort_buffer_size | 8388608
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| new | OFF
|
| old_passwords | OFF
|
| open_files_limit | 510
|
| pid_file | C:\Program Files\MySQL\MySQL Server 4.1\data
\koticompaq.pid |
| port | 3306
|
| preload_buffer_size | 32768
|
| protocol_version | 10
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 1048576
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 0
|
| query_cache_type | ON
|
| query_cache_wlock_invalidate | OFF
|
| query_prealloc_size | 8192
|
| range_alloc_block_size | 2048
|
| read_buffer_size | 131072
|
| read_only | OFF
|
| read_rnd_buffer_size | 262144
|
| relay_log_purge | ON
|
| relay_log_space_limit | 0
|
| rpl_recovery_rank | 0
|
| secure_auth | OFF
|
| shared_memory | OFF
|
| shared_memory_base_name | MYSQL
|
| server_id | 0
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slave_net_timeout | 3600
|
| slave_transaction_retries | 0
|
| slow_launch_time | 2
|
| sort_buffer_size | 2097144
|
| sql_mode |
|
| storage_engine | MyISAM
|
| sql_notes | ON
|
| sql_warnings | ON
|
| sync_binlog | 0
|
| sync_replication | 0
|
| sync_replication_slave_id | 0
|
| sync_replication_timeout | 0
|
| sync_frm | ON
|
| system_time_zone | Normaaliaika
|
| table_cache | 64
|
| table_type | MyISAM
|
| thread_cache_size | 0
|
| thread_stack | 196608
|
| time_format | %H:%i:%s
|
| time_zone | SYSTEM
|
| tmp_table_size | 33554432
|
| tmpdir |
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| version | 4.1.11
|
| version_comment | Official MySQL binary
|
| version_compile_machine | ia32
|
| version_compile_os | Win32
|
| wait_timeout | 28800
|
+---------------------------------+---------------------------------------------
----------------+
184 rows in set (0.05 sec)
mysql> create table anker(a int) type = innodb;
Query OK, 0 rows affected, 1 warning (0.30 sec)
mysql> drop table anker;
Query OK, 0 rows affected (0.09 sec)
mysql> create database test2
-> ;
ERROR 1007 (HY000): Can't create database 'test2'; database exists
mysql> use test2
Database changed
mysql> create table anker(a int) type = innodb;
Query OK, 0 rows affected, 1 warning (0.20 sec)
mysql> drop table anker;
Query OK, 0 rows affected (0.05 sec)
mysql> create table anker(a int) type = innodb;
Query OK, 0 rows affected, 1 warning (0.24 sec)
mysql>
[11 May 2005 12:33]
Anker Berg-Sonne
Hi Heikki, I am afraid the log files have been deleted. Later the same day I had to do a hard reboot of the system, and when it came back up again the database was so badly corrupted that the server went into a loop complaining. I decided to delete all InnoDB files and the database and rebuild the database from a backup. It is possible that all these problems stem from me using mixed case table names? The database is managed by a database independent front end that allows me to switch between database vendors relatively easily. It has its own schema meta data that is all mixed case. I can make the MySQL interface force table names in CREATE TABLE to lower case. I'll try to replicate the problem with the new instance of the database. Also, if I can cause the database to get corrupted again, I'll save more evidence. Its kind of hard to discipline yourself to do that because of the pressure of getting it up and running again. I have had two instances of corruption. The first was triggered by running out of disk space, the second by the hard reboot. When this database goes into production, it needs to not get corrupted because it will quickly grow to over a TB and restoring from a normal backup is going to be awfully slow. Thanks for all your help, and sorry I can't give you more to work with/Anker
[1 Jun 2005 16:10]
Heikki Tuuri
Hi! This bug may have been caused by a mismatch between .ibd files and .frm files. Regards, Heikki
