Linux Mint 15. SSD boot drive ext4 with ide data drive NTFS. Intel Dual Core 2.7Mhz. 4Gb Ram.

Hi,

Why won't you try to skip step 11 in your description and see if just restarting server solves the problem? If the reason is in the order of MariaDB start / drive mounting on reboot, by step 10 the drives should surely be mounted, so the server restart should help. If not, then it's not the reason...

Hi Elena
Did as you said and got more info from the log:
"Cannot find or open table disks/cat 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."
Similar messages for all my databases appear.t The reboot/mount suggestion was just a stab in the dark. As mentioned earlier by Sergii it looks like a problem with the Innodb internal path.

Hi Michael,

Have you figured out the reason of your problem? It's not quite clear from your last comment whether you found that the system was misconfigured, or still suspect there is a bug on the server side.

Your initial description also doesn't seem complete. At the very least, you should have updated your server config file some time beween 1 and 3, did you?

If you still think there is a bug, please provide your cnf file(s) that the server users, SHOW GLOBAL VARIABLES output, and the full contents of the server error log from server start and up and including the InnoDB error.

Thanks.

Hi Elena,
Yes, I did change the cnf file between 1 and 3. I realised after I posted it and hoped you would assume I has done it. I am attaching all the data you requested and hope this is useful to you. One final thought; could it be related to the use of an SSD drive? I raise this because I use Ghost4Linux which works well but it will never restore an SSD image to an IDE drive or an IDE image to an SSD drive. Something to do with drive geometry I believe but beyond my capabilities.

Hi Michael,

Thanks.
We can't assume anything as long as the reason of the problem is unclear, so please bear with me for a while, even although the next questions will also seem obvious.

How exactly did you do the copying? Did you copy the entire private folder, or did you create a new one and copied the contents of the old one into the new one, and if so, did you copy all contents or only schema folders?
Could you please run

ls -l /mnt/25Media/mariadb/mysql/private/
ls -l /mnt/25Media/mariadb/mysql/private/disks/
ls -l /var/lib/mysql/private/
ls -l /var/lib/mysql/private/disks/

so that we could see that the InnoDB data files are there indeed?

Is the path to the mounted datadir a real path, or does it contain any links?

Aren't there any disk errors by any chance?

Regarding the "bad" log that you provided – is it the very first attempt to start server on the mounted datadir?
Also, do you happen to know what was the command that was executed at 130702 14:34:16 (it's interesting since it seems to have attempted to open all tables at once). Was it something from Workbench?

>> could it be related to the use of an SSD drive?
I haven't heard of any problems with SSD like that, but I don't have one to experiment with, so I'll ask my colleagues if they know whether it can be a reason. It sounds unlikely, but nothing is impossible.

Hi Elena,
Results of your commands are attached. The only oddity was the size of the ../mysql folder which differs considerably in each installation. I have therefore added commands to show the contents of this folder.
The path to the mounted dir does not include any links.
The command executed at 130702 14:34:16 - I was simply using workbench to start and stop the server and modify the cnf file. At this time it would have been a start command after having stopped, copied files and modified the cnf file.
Regarding how I copied the files - I tried different methods. First just copying the whole private folder. Then deleting all contents of the folder, creating schemas using workbench and running scripts to create all the databases and data; using scripts created with "sudo mysqldump". Both methods produced the same errors.

I have done further tests as follows:
1. Move database from default /var/lib/mysql/private to /usr/private on same physical ssd drive and set permissions correctly. Everything works fine.
2. Move database to sata drive, extended partition. Same errors as previously reported.
3. Move database to a different sata drive, root partition. Same errors as before. Normally MariaDb starts ok but reports errors after reboot.
All tests folowed same procedure. Stop MariaDb, copy whole "private" dir to new location, set permissions, modify cnf file, restart MariaDb.
It points to an incompatibility between ssd and sata drives but this is outside my area of expertise.

Hi,

>> It points to an incompatibility between ssd and sata drives

It's a contradiction with your previous comment, if I understand it correctly... You said:

>> deleting all contents of the folder, creating schemas using workbench and running scripts to create all the databases and data; using scripts created with "sudo mysqldump". Both methods produced the same errors.

When you do it this way, there are no files moved from ssd to sata at all, you create all data files from scratch on the sata drive simply by executing plain SQL on your brand-new empty database. If you are still getting the same error on newly created data like that, it might be a game changer.

So, you had a server running on /var/lib/mysql/private and created a data dump using sudo mysqldump --all-databases.
Then you stopped the server, modified the config to point at /mnt/.../private, emptied the folder... What's next? How did you create the initial schema, system tables, to start the server? Was it through workbench, or did you run mysql_install_db, or?..
Then you started the server, loaded the previously created data dump which created the schemata and tables. When did the error about not found data occur, right after loading or after server restart?

I've installed Linux Mint 15 in a VM, created an NTFS primary partition and installed workbench, so I'll try to reproduce the whole scenario with mysqldump once I know how exactly you did it (I had no luck with moving data files around, but then again, there was a possibility that it's ssd-related, the mysqldump scenario should rule it out).

Thanks.

Hi,
Initially I copied my private folder to /mnt/.../private and got the errors. I then used workbench to drop all my schema. I then re-created the schema using workbench then ran my scripts to create the tables and data. The error always occurs after restarting the server.
Here is another reason why I think it is related to ssd drives;nothing to do with MariaDb. I have 2 Linux systems in the same machine for testing purposes. One is Mint 15 on a 120Gb ssd. One is LMDE on a 120Gb sata partition. They are both set up identically and each occupy about 7Gb. If I create images with Ghost4Linux using the same compression the ssd image is 4.8Gb the sata image is 36Gb. If i create uncompressed images the ssd is 7Gb and the sata is 120Gb. Clearly the way ssd stores data is very different to sata.

Hi Michael,

Elena asked me to have a look at this issue. To me it boils down to this: if you copy the MariaDB datadir to a NTFS partition then MariaDB works ok with the copy. However after rebooting your server, certain data is missing in the NTFS partition. Resulting in MariaDD refusing to start or giving errors on access.

The common point seems to be the NTFS file system you are using. May I ask why you chose NTFS? Do you have to access the same partition from Windows?

My theory of what happens, is this: the Linux kernel keeps modified disk blocks in memory and writes them back later when the disk is idle. Normally when you reboot Linux, it flushes all file systems first. It's quite possible that this is broken for NTFS. Or maybe your way to "reboot the server" makes it skip that point. Can you elaborate on how exactly you do that reboot?

In order to check my theory I ask you to do this:

1. create a fresh copy of the datadir on your NTFS partition
2. verify with MariaDB that the copy is ok
3. stop MariaDB
4. manually unmount the NTFS partition; this will force all data to disk and can hence take a while
5. Reboot
6. check if the NTFS copy works

Thanks

Hi Axel,
Thanks for your interest. I chose NTFS as it is universally accessible
although I rarely use Windows. I restart from the Mint menu which, I
think, closes all processes correctly. I thought I would also try
copying my data files to an EXT4 drive so I chose a USB memory stick but
this was not successful. Summary as follows:

1. Followed your instructions to the letter using a sata ntfs drive..
2. MariaDb worked fine until reboot.
3. After reboot MariaDb started on bootup but logged a load of errors.
See log line 32.
4. When I tried to access any table I got errors. See log line 192.
5. Repeated your in structions using a USB memory stick ext4.
6. MariaDb failed to start. See log line 225. I note an error 13 but the
USB device was mounted as root from fstab and I gave read/write
permissions to all users.

I hope this is helpful to you.

Regards
Michael

Hi Michael,

three points:

1. did you manually unmount the NTFS partition before rebooting? How is it mounted, btw? Is it in fstab or is this some automatic mount mechanism?

2. the errors for the USB stick are related to file permissions. When you copy the files, make sure you also copy ownership and permissions. As for any removable media, you should use some form of "safe remove" before unplugging the device. I never used MINT, but I guess it has some means for that. Else simply unmount manually.

3. in all cases it seems that MariaDB was not stopped before rebooting, because InnoDB claimed an unclean shutdown. Please make sure that the MariaDB is stopped first. The correct order of actions is: stop MariaDB server, unmount filesystem, reboot

Thanks.

Hi Axel,
1. Yes, I unmounted using Gparted. It is mounted automatically in fstab.
2. I gave ownership to sudo and read/write access to all users, not
mysql specifically. Yes I always unmount removable media before
removing. I this case the removable media was not unplugged it was
started from fstab.
3. This unclean error only appears when the data is on the NTFS drive.
On the SSD drive it reboots every morning without error. I'm pretty sure
that the Mint shutdown stops all processes correctly but I will
experiment a bit more to rule out these possibilities.

As an aside, have you considered the different geometry between ssd
drives and spinning disc drives. I have had correspondence with the
author of Ghost4Linux because an image of an ssd drive cannot be
restored to a convention disc drive. No solution yet found. There is
also extensive correspondence on the CloneZilla site because an image of
an ssd drive cannot be restored to the same drive even if the drive was
reformatted; it always reports too few sectors. This is because an ssd
does not have physical sectors, it has firmware that mimics the layout
of a conventional drive. One author found the solution the solution from
the ssd manufacturer; apparently it was an extremely complex fix which
has to be re-applied after every image.
The point is that ssd drives are fundamentally different and this may
manifest itself in many ways. I will continue to investigate because I
am retired and have time on my hands but I wouldn't want to take you
away from more important work. Of course, this might be an important
issue when all drives are ssd and all MariaDb users need to migrate
their data

Thanks
Michael

taking over

Axel session log

Hi Michael,

I have now tried a very similar test as yours. I have formatted a 16GB USB stick with NTFS, created a MariaDB instance on it, rebooted and started MariaDB again. All data from former MariaDB run was ok and new data could be added. Please see attached file axel.log for details (I used 3 screen sessions to record this)

Re your other questions:

> have you considered the different geometry between ssd
> drives and spinning disc drives.

Drive geometries are obsolete for decades. Rotating disks use http://en.wikipedia.org/wiki/Zone_bit_recording and displayed disk geometries are bogus (because number of sectors is not constant over cylinders). Further more, addressing of disk sectors is not longer using http://en.wikipedia.org/wiki/Cylinder-head-sector addresses, but http://en.wikipedia.org/wiki/Logical_block_addressing. This change was also necessary to overcome limitation of disk size (CHS was limited to 1024 cylinders, 16 heads, 63 sectors, totaling to 504MB).

On top of that: MariaDB is storing data in files. Anything from block device addressing over device block size up to the file system used is completely invisible to MariaDB.

> I have had correspondence with the
> author of Ghost4Linux because an image of an ssd drive cannot be
> restored to a convention disc drive. No solution yet found.
...
> This is because an ssd
> does not have physical sectors, it has firmware that mimics the layout
> of a conventional drive.

This is a pinch of truth in an ocean of nonsense. Indeed SSD don't have sectors in the conventional meaning. But then also rotating disks lie about their geometry and happily convert fake CHS addresses or LBA addresses to their hidden physical geometry. Recent hard disk even use 4KB sectors internally but mimic old fashioned 512 byte sectors on their interface.

The truth is that mass storage addressing and volume sizes in Linux use logical addresses for ages now. And the size of a volume is simply a multiple of the device block (sector) size. If you don't believe me: look at the partition table of a device once with "fdisk -l <device>" - this shows CHS coordinates. And with "fdisk -lu <device>" - this shows LBA block numbers. If you run fdisk with the -u option, you can start a partition on any sector you want, even in the mid of a cylinder. It simply doesn't matter.

Finally: the machine I'm typing on now and which I ran my tests on, is my laptop - running an SSD. It was delivered with a hard disk though and I simply used ntfsclone (from the ntfs-3g toolkit) to copy the pre-installed Windows image to the SSD. I could have used dd or even cat as well, but I didn't want to copy the unused blocks. So as far as cloning of partitions goes, there is no difference between a rotating hard disk and a SSD whatsoever. The only constraint is that the target partition must be at least as big as the source partition.

Thanks Axel,
Sorry to put you to so much trouble. I have done 2 final tests.
Previously my data was copied to a NTFS extended partition. This time I
used a primary partition but the result and error messages were the
same. Then I created a new extended partition formatted ext4 and ran
same tests as before. It worked perfectly, even after a reboot. I again
retested on the NTFS partition to ensure no other factors were involved
and got the old errors.
The only major difference in the 2 data set-ups is permissions. In ext4
I can set ownership of the data directory to "mysql" which is the
account under which mariaDb runs. In the ntfs partitions ownership is
forced to "root" and read/write access is set to "all".
Perhaps we can put this problem down to my particular system. I will use
ext4 as my default data partition and you may move on to more urgent
problems.

Regarding ssd drives. I do not pretend to be an expert and mainly quoted
from the CloneZilla forum. If you are interested:
http://sourceforge.net/p/clonezilla/discussion/Clonezilla_live/thread/0e9ffb51/?limit=50#2f81

Thanks Again
Michael

Hi Michael,

> Perhaps we can put this problem down to my particular system. I will use
> ext4 as my default data partition and you may move on to more urgent
> problems.

OK, I will close this ticket then.

BR, Axel