Bug #64704 | InnoDB error | ||
---|---|---|---|
Submitted: | 20 Mar 2012 11:36 | Modified: | 30 Jan 2013 15:39 |
Reporter: | Claudiu Cc | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S1 (Critical) |
Version: | 5.5.25 | OS: | Linux (InnoDB) |
Assigned to: | CPU Architecture: | Any | |
Tags: | innodb |
[20 Mar 2012 11:36]
Claudiu Cc
[20 Mar 2012 12:06]
Valeriy Kravchuk
Please, send your my.cnf file content. Please, send the output of: show tables from usedatabase; also.
[20 Mar 2012 18:36]
Claudiu Cc
This is the output for show tables. Empty set (0.00 sec) Checking dir /var/lib/mysql/userdatabase I found this file only: wp_statpress.ibd, db.opt is not there as user tried to drop his database then server crashed. Now, if I'm trying to drop that database, server is always crashing showing the error above, my.cnf doesn't matter because I've also used original file. If I remember correctly, I never had such a problem with mysql 5.0 or 5.1. --- actual my.cnf server has 32GB of ram running other services not just mysql ---- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 innodb_file_per_table max_connections=250 long_query_time=4 query_cache_size=256M query_cache_limit=2M query_cache_type=1 query_prealloc_size=16k thread_cache_size=8 table_open_cache=12k join_buffer_size=1M sort_buffer_size=4M read_buffer_size=2M read_rnd_buffer_size=4M thread_concurrency=16 # 8 CPUs interactive_timeout=15 wait_timeout=15 connect_timeout=5 key_buffer_size=160M bulk_insert_buffer_size = 24M max_allowed_packet=3M max_connect_errors=5 myisam_sort_buffer_size=32M bind-address=127.0.0.1 max_heap_table_size=64M tmp_table_size=64M concurrent_insert=2 low_priority_updates=1 innodb_additional_mem_pool_size = 4M innodb_log_file_size = 15M innodb_log_buffer_size=4M myisam_recover memlock [mysqld_safe] open_files_limit = 8192 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick max_allowed_packet = 32M [mysql] no-auto-rehash [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout ---------------------------------------------
[20 Mar 2012 19:29]
Valeriy Kravchuk
Please, upload the entire error log (compressed if it's big enough). I wonder what InnoDB says after recovery.
[20 Mar 2012 20:09]
Claudiu Cc
120320 6:17:31 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 22704760563 120320 6:21:39 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 120320 6:21:40 InnoDB: Error: table 'userdatabase/wp_statpress' InnoDB: in InnoDB data dictionary has tablespace id 105307, InnoDB: but a tablespace with that id does not exist. There is InnoDB: a tablespace of name ./userdatabase/wp_statpress.ibd and id 105656, though. Have InnoDB: you deleted or moved .ibd files? InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. 120320 6:21:40 InnoDB: Waiting for the background threads to start 120320 6:21:41 InnoDB: 1.1.8 started; log sequence number 22704760563 120320 6:21:41 [ERROR] /usr/libexec/mysqld: Table './mysql/user' is marked as crashed and should be repaired 120320 6:21:41 [Warning] Checking table: './mysql/user' 120320 6:21:41 [ERROR] 1 client is using or hasn't closed the table properly 120320 6:21:41 [ERROR] /usr/libexec/mysqld: Table './mysql/db' is marked as crashed and should be repaired 120320 6:21:41 [Warning] Checking table: './mysql/db' 120320 6:21:41 [ERROR] 1 client is using or hasn't closed the table properly 120320 6:21:42 [Note] Event Scheduler: Loaded 0 events and a lot of repairs as auto repair for myisam is enabled. Nothing more. Trying to drop that database will crash the server again and again.
[24 Mar 2012 12:16]
Claudiu Cc
I see the version 5.5.22 is out now. I'll upgrade next week, maybe the issue was already fixed... Maybe it is other bug, trying to drop a innodb database with errors, sometime I get errors like this instead of server crash DBD::mysql::db do failed: Error dropping database (can't rmdir './userdatabase', errno: 39). Error dropping database (can't rmdir './userdatabase', errno: 39). Chmoding 777 the dir is helpless for drop. Deleting mysql dir by hand and doing again the drop will solve the problem.
[27 Mar 2012 17:52]
Claudiu Cc
I have upgraded to 5.5.22, but bug is not solved. Any resolution on this? If you need more info from me about this issue please request it.
[17 Apr 2012 6:06]
Claudiu Cc
Still unsolved under 5.5.23.
[26 Apr 2012 16:45]
Sveta Smirnova
Thank you for the feedback. Error log says table data file ./usedatabase/wp_statpress.ibd has id different than its correspondence in shared tablespace. Did you move this table manually before?
[28 Apr 2012 19:47]
Claudiu Cc
No I didn't. Please note that I don't have just that error, that was just an example. Almost each day I have problems like this on different servers. At certain times (maybe when server is very busy??) user is trying to drop an innodb database then server is crashing. After recovery I see error "InnoDB: open the tablespace file..." The only fix I have is a script which dump the database then drop database, recreate it and import the dump, else my logs would be full of errors "InnoDB: open the tablespace file...".
[28 Apr 2012 19:55]
Claudiu Cc
A log sample. This time user droped his database, but server didn't crashed. However after server restart I still have the errors. 120427 19:18:20 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 120427 19:18:20 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './example/example.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a temporary table #sql..., InnoDB: and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. 120427 19:18:22 InnoDB: Waiting for the background threads to start 120427 19:18:23 InnoDB: 1.1.8 started; log sequence number 57946767184 120427 19:18:24 [Note] Event Scheduler: Loaded 0 events 120427 19:18:24 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.22' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) Other times server is crashing the I have the same error message complaining about those specific tables.
[29 Apr 2012 5:04]
Claudiu Cc
I just found the server down. These are the logs. Right now server is recovering. After restart I have errors for other databases, not that one involved in crash. Maybe those databases have been accessed at the time of crash. 120428 23:48:16 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: File name ./example/example.ibd InnoDB: File operation call: 'open'. 120428 23:48:16 InnoDB: Assertion failure in thread 139852931450624 in file fil0fil.c line 831 InnoDB: Failing assertion: ret InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 04:48:16 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed,. something is definitely wrong and this may fail. key_buffer_size=268435456 read_buffer_size=2097152 max_used_connections=91 max_threads=250 thread_count=4 connection_count=1 It is possible that mysqld could use up to. key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1800989 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x39a2c90 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f320c4cbda8 thread_stack 0x40000 /usr/libexec/mysqld(my_print_stacktrace+0x29)[0x783299] /usr/libexec/mysqld(handle_fatal_signal+0x473)[0x66e273] /lib64/libpthread.so.0(+0xf500)[0x7f3211711500] /lib64/libc.so.6(gsignal+0x35)[0x7f320fb91285] /lib64/libc.so.6(abort+0x17b)[0x7f320fb92b9b] /usr/libexec/mysqld[0x87fd60] /usr/libexec/mysqld[0x87ff69] /usr/libexec/mysqld[0x888e63] /usr/libexec/mysqld[0x86554a] /usr/libexec/mysqld[0x865c52] /usr/libexec/mysqld[0x856c8e] /usr/libexec/mysqld[0x843f6c] /usr/libexec/mysqld[0x80a6a1] /usr/libexec/mysqld[0x7e7f9f] /usr/libexec/mysqld[0x7e5467] /usr/libexec/mysqld[0x7f04d7] /usr/libexec/mysqld(_Z13rr_sequentialP11READ_RECORD+0x15)[0x72a835] /usr/libexec/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x61)[0x5b0ee1] /usr/libexec/mysqld[0x5b80a5] /usr/libexec/mysqld(_ZN4JOIN4execEv+0x8b0)[0x5c7f90] /usr/libexec/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x16a)[0x5c3cea] /usr/libexec/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x184)[0x5c9864] /usr/libexec/mysqld[0x587384] /usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x27f5)[0x58eef5] /usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x1b4)[0x592ec4] /usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x137f)[0x59426f] /usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x122)[0x61f2a2] /usr/libexec/mysqld(handle_one_connection+0x50)[0x61f350] /lib64/libpthread.so.0(+0x7d90)[0x7f3211709d90] /lib64/libc.so.6(clone+0x6d)[0x7f320fc4bf5d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f3092015e40): SELECT * FROM example_table LIMIT 1 Connection ID (thread ID): 1121785 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 120428 23:48:17 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 120428 23:48:17 [Note] Plugin 'FEDERATED' is disabled. 120428 23:48:17 InnoDB: The InnoDB memory heap is disabled 120428 23:48:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins 120428 23:48:17 InnoDB: Compressed tables use zlib 1.2.5 120428 23:48:17 InnoDB: Using Linux native AIO 120428 23:48:17 InnoDB: Initializing buffer pool, size = 512.0M 120428 23:48:17 InnoDB: Completed initialization of buffer pool 120428 23:48:17 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 10813945029 120428 23:48:17 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 10816401796 120428 23:51:52 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 6 InnoDB: Apply batch completed 120428 23:51:54 InnoDB: Error: table 'other_database/table' InnoDB: in InnoDB data dictionary has tablespace id 89976, InnoDB: but tablespace with that id or name does not exist. Have InnoDB: you deleted or moved .ibd files? InnoDB: This may also be a table created with CREATE TEMPORARY TABLE InnoDB: whose .ibd and .frm files MySQL automatically removed, but the InnoDB: table still exists in the InnoDB internal data dictionary. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. 120428 23:51:54 InnoDB: Error: table 'other_database/other_table' InnoDB: in InnoDB data dictionary has tablespace id 89979, InnoDB: but tablespace with that id or name does not exist. Have InnoDB: you deleted or moved .ibd files? InnoDB: This may also be a table created with CREATE TEMPORARY TABLE InnoDB: whose .ibd and .frm files MySQL automatically removed, but the InnoDB: table still exists in the InnoDB internal data dictionary. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. and many errors like these. The quick only fix is to drop the database then to recreate it. This is very annoying, server is crashing almost each day.
[4 May 2012 17:39]
Sveta Smirnova
Thank you for the feedback. Please send us output of SHOW VARIABLES LIKE 'innodb%', `ls -l /var/lib/mysql` and `ls -l /var/lib/mysql/some_database`
[4 May 2012 17:41]
Sveta Smirnova
See also bug #65199
[4 May 2012 18:55]
Claudiu Cc
+---------------------------------+------------------------+ | Variable_name | Value | +---------------------------------+------------------------+ | innodb_adaptive_flushing | ON | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 20971520 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 536870912 | | innodb_change_buffering | all | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 2000 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_method | O_DIRECT | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_io_capacity | 200 | | innodb_large_prefix | OFF | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 12582912 | | innodb_log_file_size | 104857600 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 0 | | innodb_open_files | 300 | | innodb_purge_batch_size | 20 | | innodb_purge_threads | 0 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_spin_wait_delay | 6 | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | OFF | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 32 | | innodb_thread_sleep_delay | 10000 | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_version | 1.1.8 | | innodb_write_io_threads | 4 | +---------------------------------+------------------------+
[4 May 2012 18:58]
Claudiu Cc
About `ls -l /var/lib/mysql` and `ls -l /var/lib/mysql/some_database`, I really don't understand what do you expect to find out. There are database names without special characters, just alphanumeric names. Tables are from various scripts, drupal, wordpress, joomla etc. This issue is happening frequently and not for the same database.
[4 May 2012 19:00]
Claudiu Cc
I also want to add that I have rebuild ibdata from the scratch, dumping databases then import each of them. So upgrade from 5.1 to 5.5 has nothing to do with the issue. With the same settings, I never had such errors with 5.1.
[4 May 2012 19:03]
Claudiu Cc
Those innodb variables are taken from a server with 8CPU and 32GB of RAM.
[4 May 2012 19:20]
Sveta Smirnova
Thank you for the feedback. I want to examine modification dates and permissions of files in the data directory. I also don't need list of every database, any of those which have problems is fine.
[5 May 2012 5:59]
Claudiu Cc
All databases have the same permissions. Few examples below. drwx------. 2 mysql mysql 4096 Apr 21 10:29 db1 drwx------. 2 mysql mysql 4096 Apr 21 10:29 db2 drwx------. 2 mysql mysql 4096 Apr 21 10:29 db3 drwx------. 2 mysql mysql 4096 Apr 21 10:29 db4 -rw-rw---- 1 mysql mysql 102760448 May 5 00:43 ibdata1 -rw-rw---- 1 mysql mysql 104857600 May 5 00:44 ib_logfile0 -rw-rw---- 1 mysql mysql 104857600 May 4 22:45 ib_logfile1 drwx------. 2 mysql mysql 4096 Apr 21 10:29 mysql srwxrwxrwx 1 mysql mysql 0 May 4 00:43 mysql.sock drwx------. 2 mysql mysql 4096 Mar 11 16:05 performance_schema The same for tables. I have checked few databases and permissions are identical: -rw-rw---- 1 mysql mysql 65 Apr 10 11:05 db.opt -rw-rw---- 1 mysql mysql 9696 Apr 21 10:29 table.frm -rw-rw---- 1 mysql mysql 17825792 May 5 00:41 table.ibd SELinux is disabled, but maybe systemctl settings are able to cause these issues? Server is crashing only when someone is trying to drop a database, but not anytime, just at certain times. Usually droping a database is working just fine.
[5 May 2012 12:50]
Sveta Smirnova
Thank you for the feedback. Everything looks correct now. But error 39 means "OS error code 39: Directory not empty" This usually happen when not-database files exist in daatabase directory. So, next time when problem occurs, before manually deleting anything (this should be done never anyway), run `ls -l /var/lib/mysql/problem_database` and paste to the report. Also check value of tmpdir and ensure it is set and it is not datadir.
[5 May 2012 18:21]
Claudiu Cc
I think you don't understand!! I see what you also told me on bug #65199 and you marked it "not a bug" without to find a solution, based just on erroneus supposing that I'm deleting myself the files which is wrong. Bug #65199 is indeed a bug! And yes, file permissions are looking ok, because this has nothing to do with file permissions. That's why I was curious why did you ask me about those "ls -es" when issue is happening randomly. Nobody is deleting nothing before server is crashing! After I see those errors related to a database, the only way to clean up the database, after server previously crashed!! (keep in mind this), is to delete myself the files then try again to drop the database.
[5 May 2012 18:31]
Claudiu Cc
mysql.log
Attachment: mysqld.log (application/octet-stream, text), 10.70 KiB.
[5 May 2012 18:36]
Claudiu Cc
ls -l /var/lib/mysql/database total 2484 -rw-rw---- 1 mysql mysql 61 May 2 05:36 db.opt -rw-rw---- 1 mysql mysql 8688 May 4 14:20 wp_commentmeta.frm -rw-rw---- 1 mysql mysql 131072 May 4 14:20 wp_commentmeta.ibd -rw-rw---- 1 mysql mysql 13380 May 4 14:20 wp_comments.frm -rw-rw---- 1 mysql mysql 180224 May 4 14:20 wp_comments.ibd -rw-rw---- 1 mysql mysql 13176 May 4 14:20 wp_links.frm -rw-rw---- 1 mysql mysql 114688 May 4 14:20 wp_links.ibd -rw-rw---- 1 mysql mysql 8734 May 4 14:20 wp_options.frm -rw-rw---- 1 mysql mysql 524288 May 4 14:20 wp_options.ibd -rw-rw---- 1 mysql mysql 8682 May 4 14:20 wp_postmeta.frm -rw-rw---- 1 mysql mysql 196608 May 4 14:20 wp_postmeta.ibd -rw-rw---- 1 mysql mysql 9588 May 4 14:20 wp_posts.frm -rw-rw---- 1 mysql mysql 262144 May 4 14:20 wp_posts.ibd -rw-rw---- 1 mysql mysql 17186 May 4 14:20 wp_tanalyzer_pre.frm -rw-rw---- 1 mysql mysql 98304 May 4 14:20 wp_tanalyzer_pre.ibd -rw-rw---- 1 mysql mysql 8672 May 4 14:20 wp_tanalyzer_resources.frm -rw-rw---- 1 mysql mysql 98304 May 4 14:20 wp_tanalyzer_resources.ibd -rw-rw---- 1 mysql mysql 17184 May 4 14:20 wp_tanalyzer_visits.frm -rw-rw---- 1 mysql mysql 98304 May 4 14:20 wp_tanalyzer_visits.ibd -rw-rw---- 1 mysql mysql 8666 May 4 14:20 wp_term_relationships.frm -rw-rw---- 1 mysql mysql 114688 May 4 14:20 wp_term_relationships.ibd -rw-rw---- 1 mysql mysql 8668 May 4 14:20 wp_terms.frm -rw-rw---- 1 mysql mysql 131072 May 4 14:20 wp_terms.ibd -rw-rw---- 1 mysql mysql 8768 May 4 14:20 wp_term_taxonomy.frm -rw-rw---- 1 mysql mysql 131072 May 4 14:20 wp_term_taxonomy.ibd -rw-rw---- 1 mysql mysql 8684 May 4 14:20 wp_usermeta.frm -rw-rw---- 1 mysql mysql 131072 May 4 14:20 wp_usermeta.ibd -rw-rw---- 1 mysql mysql 8968 May 4 14:20 wp_users.frm -rw-rw---- 1 mysql mysql 131072 May 4 14:20 wp_users.ibd This seems other bug for me, but I'm still reporting here as you closed my other bug report. I again remember you that I never had such issues with 5.1. The problems started once I upgraded to Fedora 16 and mysql 5.5.
[8 May 2012 18:39]
Sveta Smirnova
Thank you for the feedback. Tables, such as wproot_terms.ibd and other mentioned in the log file do not exist in database directory, so it is not surprise why you get such errors in the log. Original problem about crash is still interesting. You wrote you did not do binary upgrade, but loaded dump, created by mysqldump. Please confirm or reject. Please also send us your configuration file. Did you use InnoDB Plugin in version 5.1?
[8 May 2012 19:30]
Claudiu Cc
I have used mysql 5.1 provided by Fedora repository. A copy of my.cnf was presented later. Some buffers are bigger, but that's my default file. It seems that user is dropping those tables, they are physically deleted, but entries are not really erased from tablespace. Regarding drop crash, I have created a perl script and when I see a database with errors, the script 1. creates a dump of that database 2. drop database (sometimes server is again crashing on dump, sometimes not, I have checked the files, but I didn't find something suspicious for database involved. Problem seems related to manipulation of innodb tablespace. After crash and server restart, drop is working anytime.) 3. import dump On server restart, errors are no longer appearing. To avoid any confusion, I only run my script when I already have errors in the logs.
[11 May 2012 18:26]
Sveta Smirnova
Thank you for the feedback. I tried to repeat the problem using same pattern which you described in the previous comment and options provided, but did not succeed. I run test for 48 hours: no problem with tablespace. Please check your disk for errors and if it has no error, add option core to mysqld options, add proper operating system limits and send us core file next time when it crashes. Also, please, next crash run command `ls -la /path/to/problem/database`, so listing includes all invisible files also.
[12 May 2012 6:36]
Claudiu Cc
Hi, it can't be server hardware because I have this problem on many servers. After I have analyzed few users actions, databases, dumps and so, I think I found the source for this bugs. It is only happen for databases imported from dumps created with later versions of mysql, specially 5.1 with MySQL dump 10.13. It looks like an incompatibility issue with dumps created by older versions. I've also found that wordpress, drupal etc. are using now Innodb by default. Old dumps have myisam and if user is trying to update his scripts, result is a database mix of myisam/innodb that for some reason crashes. Now, I'm trying to create a script that automatically rebuild those old dumps and set innodb for all tables. I'm sure the error will be solved this way. Maybe you will also find a similar way to automatically solve errors caused by old dump imports.
[15 May 2012 5:49]
Claudiu Cc
I have upgraded mysql to 5.5.24 on some of my servers. It seems that you also did some innodb improvement because for 2 days on the servers with 5.5.24 I no longer register errors. Maybe it's too early to conclude, but I'll keep you updated.
[17 May 2012 6:47]
Claudiu Cc
Still have issues with 5.5.24 :( The only solution seems to block my users to import old dumps or to rebuild their databases. Maybe you will find a server solution however.
[25 May 2012 19:12]
Sveta Smirnova
Thank you for update. Interesting affect of mixed MyISAM and InnoDB tables. Would be good if you could create repeatable test case from one of such dumps, although I understand this is not easy. Btw if your users use MyISAM by default you can either ask them to create tables with option engine=MyISAM or set MySQL as default storage engine (--default-storage-engine=myisam).
[25 May 2012 19:21]
Sveta Smirnova
Second though. ----<q>---- After I have analyzed few users actions, databases, dumps and so, I think I found the source for this bugs. It is only happen for databases imported from dumps created with later versions of mysql, specially 5.1 with MySQL dump 10.13. It looks like an incompatibility issue with dumps created by older versions. I've also found that wordpress, drupal etc. are using now Innodb by default. Old dumps have myisam and if user is trying to update his scripts, result is a database mix of myisam/innodb that for some reason crashes. Now, I'm trying to create a script that automatically rebuild those old dumps and set innodb for all tables. I'm sure the error will be solved this way. Maybe you will also find a similar way to automatically solve errors caused by old dump imports. ----</q>---- What do you mean by dumps? How your users create them? Do they use mysqldump or some other software? What you describe and error in the log file looks like error with binary backup/restore, not with re-creation of text SQL dumps. Please clarify.
[5 Jun 2012 5:21]
Claudiu Cc
Those dumps have been created with mysqldump, but using older versions of mysql 5.0 and 5.1. Yes, errors appear for mysql drop, but analyzing dump files I have found that pattern. With 5.5.24 all problems appear for databases imported from old dumps.
[8 Jun 2012 9:34]
Claudiu Cc
A new log of crashed server with mysql 5.5.25
[8 Jun 2012 9:35]
Claudiu Cc
recent logfile of crashed server - complete
Attachment: mysqld.log-8June2012 (application/octet-stream, text), 227.27 KiB.
[8 Jun 2012 9:48]
Claudiu Cc
ls -l /var/lib/mysql/drupal_new total 0 Mysql dir of dropped/crashed database exists, but it's empty.
[16 Jul 2012 19:29]
Claudiu Cc
This problem is related to default_storage_engine which is Innodb now. Problem appears for databases created with previous versions where myisam was default.
[30 Jan 2013 15:39]
Matthew Lord
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a clear and verifiable bug. A bug report requires a repeatable test case. The work involved in creating one is not done via the bug report. I don't need a log, I need a test case to try and verify. Do you have a mysqldump file from 5.0 or 5.1 that you can use to repeat the problem? I'm assuming that the steps would be: 1) import dump file 2) drop a table ... Your test case is NOT adequate: "Trying to drop certain databases containing innodb tables." All of our users drop databases and tables containing innodb tables, and a large percentage of users have used upgraded from 5.0 and 5.1 to 5.5, so there must be something particular about the steps taken on your end. It's those precise steps you take to repeat the problem that we need for a useful bug report. It now seems that you're getting closer to a test case? If you can provide a repeatable test case then I'd be happy to verify it. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.