MySQL unstable behavior with external stroge

Question

In our project we are using MySQL 5.0.90(InnoDB engine) server with an external storage. We store MySQL data files in an external storage. When the external storage down for a reason we have unstable behaviours. So we made some tests.

In Windows Server 2008

We closed external storage physically. MySQL service stoped and we could not reach the server. Then we opened the storage unit and we could start service

Logs

2. 120618 14:49:30 InnoDB: Operating system error number 21 in a file operation.
3. InnoDB: Some operating system error numbers are described at
4. InnoDB: http://dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html
5. InnoDB: File name E:\Data\ibdata1
6. InnoDB: File operation call: 'aio write'.
7. InnoDB: Cannot continue operation.

We made storage unit offline from operating system. After 3-4 minutes and some insert trials(some insert trials succeded) MySQL service stoped and we could not reach the server.

Logs

1. 120618 14:27:21 InnoDB: Operating system error number 21 in a file operation.
2. InnoDB: Some operating system error numbers are described at
3. InnoDB: http://dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html
4. InnoDB: File name E:\Data\ibdata1
5. InnoDB: File operation call: 'aio read'.
6. InnoDB: Cannot continue operation.

Then we made storage unit online and tried to start the service

Logs

1. InnoDB: The first specified data file E:\ibdata1 did not exist:
2. InnoDB: a new database to be created!
3. 120618 14:29:00 InnoDB: Setting file E:\ibdata1 size to 10 MB
4. InnoDB: Database physically writes the file full: wait...
5. InnoDB: Error: all log files must be created at the same time.
6. InnoDB: All log files must be created also in database creation.
7. InnoDB: If you want bigger or smaller log files, shut down the
8. InnoDB: database and make sure there were no errors in shutdown.
9. InnoDB: Then delete the existing log files. Edit the .cnf file
10. InnoDB: and start the database again.
11. 120618 14:29:00 [ERROR] Default storage engine (InnoDB) is not available
12. 120618 14:29:00 [ERROR] Aborting

Then we tried to reconfigure MySQL

Logs

1. InnoDB: End of page dump
2. 120618 14:34:02 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432
3. InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
4. InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
5. InnoDB: Page number (if stored to page already) 0,
6. InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
7. 120618 14:34:02 - mysqld got exception 0xc0000005 ;

8. 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.

9. key_buffer_size=0

10. read_buffer_size=65536
11. max_used_connections=0
12. max_connections=100
13. threads_connected=0

14. It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 32000 K bytes of memory Hope that's ok; if not, decrease some variables in the equation.

15. thd=00000000

16. 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...
17. 006D2DB6 mysqld-nt.exe!page_cur_search_with_match()[page0cur.c:347]
18. 0067A777 mysqld-nt.exe!btr_cur_search_to_nth_level()[btr0cur.c:500]
19. 006B2E0E mysqld-nt.exe!btr_pcur_open_on_user_rec()[btr0pcur.c:549]
20. 006A5615 mysqld-nt.exe!dict_load_indexes()[dict0load.c:604]
21. 006A6424 mysqld-nt.exe!dict_load_sys_table()[dict0load.c:1023]
22. 006BBB20 mysqld-nt.exe!dict_boot()[dict0boot.c:378]
23. 00668A79 mysqld-nt.exe!innobase_start_or_create_for_mysql()[srv0start.c:1462]
24. 00444462 mysqld-nt.exe!innobase_init()[ha_innodb.cc:1427]
25. 0044B30D mysqld-nt.exe!ha_init()[handler.cc:483]
26. 004B923E mysqld-nt.exe!init_server_components()[mysqld.cc:3431]
27. 004BD070 mysqld-nt.exe!win_main()[mysqld.cc:3806]
28. c004BD43B mysqld-nt.exe!mysql_service()[mysqld.cc:3967]
29. 006E28EF mysqld-nt.exe!_threadstart()[thread.c:196]
30. 75583677 kernel32.dll!BaseThreadInitThunk()
31. 77359D72 ntdll.dll!RtlInitializeExceptionChain()
32. 77359D45 ntdll.dll!RtlInitializeExceptionChain()
33. 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. 120618 14:29:00 [Note] C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete

In Windows Server 2003

We made storage unit offline. After 3-4 minutes and some insert trials(some insert trials succeded) MySQL service stoped and we could not reach the server.

Logs

1. InnoDB: Log scan progressed past the checkpoint lsn 0 9834427 120618 14:09:59 InnoDB: Database was not shut down normally!
2. InnoDB: Starting crash recovery.
3. InnoDB: Reading tablespace information from the .ibd files...
4. InnoDB: Restoring possible half-written data pages from the doublewrite
5. InnoDB: buffer...
6. InnoDB: Doing recovery: scanned up to log sequence number 0 9834574
7. 120618 14:09:59 InnoDB: Starting an apply batch of log records to the database...
8. InnoDB: Progress in percents: 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
9. InnoDB: Apply batch completed
10. 120618 14:10:00 InnoDB: Started; log sequence number 0 9834574
11. 120618 14:10:00 [Note] C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
12. Version: '5.0.90-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL)
13. 120618 14:12:36 InnoDB: Operating system error number 21 in a file operation.
14. InnoDB: Some operating system error numbers are described at
15. InnoDB: http://dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html
16. InnoDB: File name E:\Data\ibdata1
17. InnoDB: File operation call: 'aio read'.
18. InnoDB: Cannot continue operation.

Then we made storage unit online we could not start the service until we reinstalled MySQL. Before reinstall we tried to reconfigure but it did not work.

Logs

1. 120618 14:16:53 InnoDB: Operating system error number 3 in a file operation.
2. InnoDB: The error means the system cannot find the path specified.
3. InnoDB: If you are installing InnoDB, remember that you must create
4. InnoDB: directories yourself, InnoDB does not create them.
5. InnoDB: File name E:\Data\ibdata1
6. InnoDB: File operation call: 'create'.
7. InnoDB: Cannot continue operation.

Could not start the MySQL service on Local Computer. Error 1067: The process terminated unexpectedly.(ERROR MESSAGE)

We closed external storage physically. MySQL service stoped and we could not reach the server. After that we opened the storage unit and we could start service(not automatically)

Logs

1. 120618 14:01:26 InnoDB: Operating system error number 21 in a file operation.
2. InnoDB: Some operating system error numbers are described at
3. InnoDB: http://dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html
4. InnoDB: File name E:\Data\ibdata1
5. InnoDB: File operation call: 'aio write'.
6. InnoDB: Cannot continue operation.

We expect service to autostart after storage unit is online/open. But these tests show unstable behaviors. Is there any solutions to this.

Answer 1

iSCSI is simply a protocol that allows a server to access a remotely emulated SCSI disk. Without knowing more about the actual storage (how many controllers, is there write cache, is it mirrored), I can't be certain about my answer. That said, the issue might be one of consistency.

If you pull the plug on storage attached to a running database, all the in flight IO has to be handled or else you risk inconsistent data. When you do a write to external storage, it usually ends up being acknowledged immediately as soon as it's in cache. Once that happens, the data in cache is destaged to disk, however not in the order it was received. Any power loss that makes you lose the cached in-flight IO will cause the disks to be missing writes.

Answer 2

It seems to me that your errors are rooted in the underlying device. I'd do tests with IO using other applications to the device and see if you can debug your error with that. Device not ready and path not found errors seem to be the root cause, suggesting that your external storage link isn't well-behaved.