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:
None 
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
Description:
I have upgraded from 5.1 to 5.5 having these issues. I already performed "mysql_upgrade", it was successful. Server has 32GB of RAM. 

From logs:

120320  6:16:53  InnoDB: Operating system error number 17 in a file operation.
InnoDB: Error number 17 means 'File exists'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: The file already exists though the corresponding table did not
InnoDB: exist in the InnoDB data dictionary. Have you moved InnoDB
InnoDB: .ibd files around without using the SQL commands
InnoDB: DISCARD TABLESPACE and IMPORT TABLESPACE, or did
InnoDB: mysqld crash in the middle of CREATE TABLE? You can
InnoDB: resolve the problem by removing the file './usedatabase/wp_statpress.ibd'
InnoDB: under the 'datadir' of MySQL.
InnoDB: Error: tablespace id is 105307 in the data dictionary
InnoDB: but in file ./usedatabase/wp_statpress.ibd it is 105656!
120320  6:17:29  InnoDB: Assertion failure in thread 139645070407424 in file fil0fil.c line 765
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.
11:17:29 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=167772160
read_buffer_size=2097152
max_used_connections=46
max_threads=250
thread_count=2
connection_count=1
It is possible that mysqld could use up to.
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1702685 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x463fbf0
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 = 7f01a6d0eda8 thread_stack 0x40000
/usr/libexec/mysqld(my_print_stacktrace+0x29)[0x7835f9]
/usr/libexec/mysqld(handle_fatal_signal+0x473)[0x66e193]
/lib64/libpthread.so.0(+0xf500)[0x7f01d8372500]
/lib64/libc.so.6(gsignal+0x35)[0x7f01d67f2285]
/lib64/libc.so.6(abort+0x17b)[0x7f01d67f3b9b]
/usr/libexec/mysqld[0x8252db]
/usr/libexec/mysqld[0x8253c9]
/usr/libexec/mysqld[0x82f3e9]
/usr/libexec/mysqld[0x82f439]
/usr/libexec/mysqld[0x80f4cb]
/usr/libexec/mysqld[0x7b5072]
/usr/libexec/mysqld[0x7b593d]
/usr/libexec/mysqld[0x87b7e8]
/usr/libexec/mysqld[0x87bd57]
/usr/libexec/mysqld[0x7a565c]
/usr/libexec/mysqld[0x7a76e5]
/usr/libexec/mysqld[0x791a6a]
/usr/libexec/mysqld[0x66e20f]
/usr/libexec/mysqld(_Z24plugin_foreach_with_maskP3THDPFcS0_P13st_plugin_intPvEijS3_+0x229)[0x5997d9]
/usr/libexec/mysqld(_Z11mysql_rm_dbP3THDPcbb+0x5d8)[0x573658]
/usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x204a)[0x58e76a]
/usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x1b4)[0x592ee4]
/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x137f)[0x59428f]
/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x122)[0x61f302]
/usr/libexec/mysqld(handle_one_connection+0x50)[0x61f3b0]
/lib64/libpthread.so.0(+0x7d90)[0x7f01d836ad90]
/lib64/libc.so.6(clone+0x6d)[0x7f01d68acf5d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f01909a5770): DROP DATABASE IF EXISTS `userdatabase`
Connection ID (thread ID): 438535
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.
120320 06:17:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120320  6:17:30 [Note] Plugin 'FEDERATED' is disabled.
120320  6:17:31 InnoDB: The InnoDB memory heap is disabled
120320  6:17:31 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120320  6:17:31 InnoDB: Compressed tables use zlib 1.2.5
120320  6:17:31 InnoDB: Using Linux native AIO
120320  6:17:31 InnoDB: Initializing buffer pool, size = 128.0M
120320  6:17:31 InnoDB: Completed initialization of buffer pool
120320  6:17:31 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 22704736421
120320  6:17:31  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...

How to repeat:
Trying to drop certain databases containing innodb tables.
[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.