MySQL 5.5: InnoDB data on NTFS resets at reboot
On a dual boot system with Linux Mint 15 and Windows XP I had installed MySQL server 5.5 in both OS:es. Then I had configured my.cnf and my.ini so they used the same directory located on the Windows XP NTFS partition for database storage. This made it possible to access the database from both Linux Mint and Windows XP. This worked fine when I had Ubuntu 11.04 installed instead of Linux Mint 15. After replacing Ubuntu with Mint I started loosing the InnoDB database data at every startup of the system. It did not happen when I restarted the service, just when the whole system went down and up. Example of output from /var/log/mysql/error.log: 131015 21:25:20 [Note] Plugin 'FEDERATED' is disabled. 131015 21:25:22 InnoDB: The InnoDB memory heap is disabled 131015 21:25:22 InnoDB: Mutexes and rw_locks use GCC atomic builtins 131015 21:25:22 InnoDB: Compressed tables use zlib 1.2.7 131015 21:25:22 InnoDB: Using Linux native AIO 131015 21:25:22 InnoDB: Initializing buffer pool, size = 128.0M 131015 21:25:22 InnoDB: Completed initialization of buffer pool InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 131015 21:25:22 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 131015 21:25:24 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 131015 21:25:25 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: 127 rollback segment(s) active. InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 131015 21:25:28 InnoDB: Waiting for the background threads to start 131015 21:25:29 InnoDB: 5.5.32 started; log sequence number 0 131015 21:25:29 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306 131015 21:25:29 [Note] - '127.0.0.1' resolves to '127.0.0.1'; 131015 21:25:29 [Note] Server socket created on IP: '127.0.0.1'. 131015 21:25:33 [Note] Event Scheduler: Loaded 0 events 131015 21:25:33 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.32-0ubuntu0.13.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu) 131015 21:25:35 [ERROR] Cannot find or open table example/test from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table contains indexes that this version of the engine doesn't support. I've tried to use bindfs and make the directory owned by mysql user with no effect. I tried the same setup on a laptop, also with Mint 15 and Windows XP and an NTFS partition and got the exact same behaviour. It keeps resetting the data on every reboot when data is located on the NTFS partition. Problem: InnoDB data cannot be located on a NTFS partition when running MySQL server 5.5 on Linux (Mint). Current workaround is to dump the data using mysqldump, convert the tables to MyISAM by replacing InnoDB with MyISAM in the dump, disable innodb completely, set myisam to default storage engine and then import them again, only using MyISAM as storage engine. To completely disable the innodb engine and use myisam, i did the following in my.cnf: Commented out all lines starting with innodb_ Added innodb=OFF to [mysqld] section Added/edited default-storage-engine=myisam to [mysqld] section I made a thread about this on the MySQL forums: http://forums.mysql.com/read.php?22,597535,597535#msg-597535
This is a personal note. Last updated: 2013-10-15 23:08:14.