Cannot change MariaDB data directory to mounted shared folder on VMware

[HollyJ]

First of all, I hope I'm posting on the right board 😇

I cannot get mariadb.service to start under the following scenario:

• VMware Workstation 16 host: Windows 10
• VMware Workstation 16 guest: Ubuntu server 20.04
• MariaDB is installed in Ubuntu server

I am trying to change MariaDB data directory to one of the folders inside VMware hgfs shared folder.

Both systemctl status mariadb.service and journalctl -xe give no specific info.

/var/log/mysql/error.log reports what follows:

innodb could not set the file size of './ibtmp1'. probably out of disk space

I have checked that the mounted unit:

• has available space with df
• is owned by mysql:mysql
• mysql user has 777 permissions

Still, everytime I try to start the service, I get this error, with InnoDB plugin failing with code 5 while trying to initialize its file.

Please help me, thank you!

[RaSystemlord]

I'm not quite sure how MySQL server-client communication is supposed to work. But since you are talking about shared folders, I assume, it works based on connected/mounted remote directories. If not, the items below do not directly help.

If so, this should work:

- make sure that Samba is installed in Ubuntu server (an Ubuntu issue)

- configure Samba to allow mounting from another computer (an Ubuntu issue)

- test that mounting and editing works

- if not, make sure that both computers, "see" each other on the ip-level. For instance, in Windows, make sure that "ping hostname" returns the ip-address of the Ubuntu client. If not, edit "hosts" file to make sure and pass DNS functionality. Even if ping works, reboot, because other services may not work without reboot after editing "hosts"

- the above is important, because the ip-connections must work into both directions in many cases when hostname is used in configuration (as usual)

- the above is assuming NAT-connection. DNS will get confused, in many cases, because VMs may be copies with the same hostname and different ip ... then DNS cannot work immediately, but editing hosts file will correct the situation

- your client should always see the Host, but it doesn't hurt to verify also that with "ping hostname"

If this is your case, it should work.

[HollyJ]

Hi Ra,

thank you for your reply.

What I'm trying to do is to setup a LEMP stack (Linux, Nginx, MySQL, Python/PHP) for web development.

I've been able to get my Windows 10 host browser to get static content served from Nginx in the Ubuntu server guest in the VM.

I'm not even trying to have a MySQL server-client communication as the server itself cannot start when its data folder is located on a shared folder.

I've already tested both the mounting and the editing of the mount while using my non-root user and it's working great.

What I'm stuck with is that even though I've configured the VMware tools to mount the unit setting its owner to be mysql:mysql and giving it full access (777), it seems to be unable to create files on the mount like if it was read only 😞

[darksidemaze]

Maybe this Docker link has something similar. 

Could not set the file size of ./ibtmp1. Probably out of disk space - ITPro Helper

database - How to clean or resize the ibtmp1 file in MySQL? - Stack Overflow

If you cant get anywhere with mariaDB, try another db. All you are doing is running a VM guest, which runs a db of sorts for access under specific conditions, so maybe try a db you are more comfortable with or has more information, maybe even some instructions/blogs aligned to you use case. 

[RaSystemlord]

Sorry, still don't know about the use of MySQL and MariaDB - to me the idea of using a database through shared directory, is unknown. Or perhaps you are trying to create files in Windows, from Ubuntu, using the mount - I don't know your system&software and thus cannot know what you actually need to accomplish on the generic level of things.

But my point was that, if VMware sharing doesn't do what you want, try operating system related sharing, which is Samba in this case (or it could be NFS, but that requires things on Windows side as well).

As for permissions, if this somehow requires permissions in Windows, here are a few points:

- if you have a service, like a db, you need to give permissions to the service username, which isn't your interactive user

- if the service runs on internal users, like system or network, like it very well could run, it isn't a real user. Looking from the outside, it is the computer, not any user. There are three groups for setting permissions: user, group and computer. This is the latter.

[wila]

Hi,

As a software developer for over 25 years, with over 15 years of MySQL/MariaDB experience... don't even try to do this.

Your database works best on a local filesystem.
If you put it on a remote filesystem then there will be a variety of issues.

- Performance will tank (hgfs is notoriously slow when writing small chunks of data)
- There will be issues with dataloss (I would not count on file locks between host and guest to be 100% correct at all times)

Locking issues and sync issues will happen.
An easy example is a host reboot with the guest not having written out the data to the database yet, or power loss at the host with some of the data in a buffer somewhere and not yet in one of the database logs.
The result of that would be database corruption.
Repairing mySQL databases is another fun thing to experience and resolve.

If you want to insist on putting the database on an external filesystem, then use a standard network file system such as NFS (or SMB for that matter, but SMB has oplock issues)

But really... don't do this, it's a really bad idea and I know this because of experience with having databases on remote file systems on real hardware.. which is a much better scenario than having this in a VM.

--
Wil

[darksidemaze]

I havent had any problems with ISAM or RDBMS systems in 30+ years provided the below is followed. 

upto date take on Opportunistic Locks - Win32 apps | Microsoft Docs

Still use the below to this day.

Configuring opportunistic locking in Windows

Microsoft Windows Server 2003 Standard Edition (32-bit x86) More...

Summary

By default, opportunistic locking is enabled for server message block (SMB) clients that run one of the Windows operating systems that is listed in the "Applies to" section. Opportunistic locking lets clients lock files and locally cache information without the risk of another user changing the file. This increases performance for many file operations but may decrease performance in other operations because the server that grants the opportunistic lock must manage the breaking of that lock when another user requests access to the file.

Notes for Windows Vista and later Operating Systems (i.e. Windows 7)

• The opportunistic locking registry keys are valid only for traditional SMB (SMB1). You cannot turn off opportunistic locking for SMB2. SMB2 was introduced in Windows Vista to enable faster communication between computer that are running Windows Vista and Windows Server 2008 or Windows Server 2008 R2.

• If you disable opportunistic locking, the offline files feature in Windows Vista or Windows 7 fails.

More Information

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

322756 How to back up and restore the registry in Windows

The location of the client registry entry for opportunistic locking has changed from the location in Microsoft Windows NT. In later versions of Windows, you can disable opportunistic locking by setting the following registry entry to 1:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MRXSmb\Parameters\

OplocksDisabled REG_DWORD 0 or 1
Default: 0 (not disabled)Note The OplocksDisabled entry configures Windows clients to request or not to request opportunistic locks on a remote file.

You can also deny the granting of opportunistic locks by setting the following registry entry to 0:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters

EnableOplocks REG_DWORD 0 or 1
Default: 1 (enabled)Note The EnableOplocks entry configures Windows-based servers to allow or to deny opportunistic locks on local files. These servers include workstations that share files.

In addition, you can use the following values to tune opportunistic locking for Windows-based computers that have granted opportunistic locks.

The following value specifies the minimum link throughput that the server allows before it disables raw and opportunistic locks for this connection:

MinLinkThroughput REG_DWORD 0 to infinite bytes per second
Default: 0 The following value specifies the maximum time that is allowed for a link delay. If delays exceed this number, the server disables raw I/O and opportunistic locking for this connection.

MaxLinkDelay REG_DWORD 0 to 100,000 seconds
Default: 60 The following value specifies the time that the server waits for a client to respond to an oplock break request. Smaller values allow detection of crashed clients more quickly, but might potentially cause loss of cached data.

OplockBreakWait REG_DWORD 10 to 180 seconds
Default: 35Note You must restart the computer for these registry changes to take effect.

[wila]

Hi,

I know all about oplocks, your settings are for SMB v1 and possibly also v2, which are both legacy.
Once you apply those settings at both client AND server, the performance tanks and not just a little bit.

But it is all moot as the filesystem discussed here is HGFS, not SMB. I only mentioned oplocks as an indication of the difficulties that are there.

This is also about a VM, which has additional implications.. I stand by my earlier statement, but hey.. it is up to you what you do.
--
Wil

[RaSystemlord]

OK, this discussion explains. I guess, you can tweak through it, but for me the idea of using a RDBMS through file sharing was alien.

In all professional use of RDBMS, a client/server approach is used with systems that I know, even in a standalone system. That applies to Oracle (since 80s) and SQL Server by Microsoft (where basic client is integrated with OS, "admin client" is separate). Informix had a similar concept, if not used as a standalone system.

By quickly googling, MarianDB supports client/server-connections, too. I guess, this is self-evident for any real RDBMS.

[wila]

RaSystemLord,

File sharing a RDBMS was done in the time where you used MS Access etc..
In that scenario the clients connect directly to the database files and directly write to those database files.

With a client server database, the server takes care of all the write actions.
Any database administrator worth their salt would never want to put their database files on a remote file system. It just makes no sense - for many reasons. As TS has found out, it isn't even easy to get to work (one might wonder why?)

MariaDB/MySQL is a client server database. Most setups however are in a LAMP stack where the server is accessed over localhost.

--
Wil

[RaSystemlord]

Wila,

yes, sure. Maybe slightly besides the point, but ...

"File sharing a RDBMS was done in the time where you used MS Access etc"

I think that MS Access isn't a RDBMS - just the next step from Excel. Some people think that it is - if you look into the link, MS Access doesn't really do much of anything, except the elementary functions required in a stand-alone use:

https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

Oracle goes well into 80s on UNIX and MS Access has never an option. MS Access is a placeholder for DB info and has its own purpose, like standalone reporting from that - you don't need proper server/client functionality for that, just an easy reporting mechanism ... as just one example where I have found it useful.

[darksidemaze]

So my understanding is SMBx controls a File System locking mechanism, some FS's are more integrated than others, but it depends on the OS thats running the File System, ie NTFS works best when Windows runs its, not when a *nix runs it. 

c-ISAM database files like dbase/access (Jet engine) are just DB files with the DB logic stored in the app. Problem with this approach is the app has to download all the records inorder to sort or create a view. This is not network efficient especially when two or more workstations are using the same window in the app and thus pulling down a copy of the same records and related tables in order to create the view.

RDBMS like Pervasive BTrieve, MS SQL, My SQL take the relevant DB application logic and runs its typically on the same machine (server) in memory. A ISAM application will run fastest on the machine where the data files are store.

RDBMS is like network accessible layer which exposes DB functionality with extra code to keep the speed up like storing popular tables/view's in memory handling the CRUD disk actions behind the scenes, working with Raid configurations and controller caches at the OS level. This works when networks can be poor (lots of retrys) or slow (dialup) connections, something which was a bigger problem in the past, although DB sizes have also grown over the years as well. So because only the answers ie the pertinent records are sent out to the workstation app, all the behind the scenes sorting, creating views take place on the server reducing disk to logic processing roundtrip time as its not having to send all files over the network to be sorted. Thus RDBMS is nearly always efficient for networks for average programs. Highly tuned programs, like the type seen in Hedge Funds for HFT typically build even their OS to reduce as much hardware lag and latency in the various buffers/cache where possible. You can get isam applications like this which can still outperform RDBMS in some settings, but it takes more work and very very very few entities have something like this. 

One of the reasons for using a VM is portability, sometimes its nice to work on a big powerful desktop and other times its nice to head off to the holiday home/Dacha and work on the laptop. This is what Vmware brings to the table for some devops, so running a db on a vm is necessary even for testing scenarios before the final release onto bare metal.

So knowing how Microsoft provides backwards compatibility with Windows, I would expect SMB1 oplock settings to have an effect for backward compatibility reasons even today, even if its going through a new updated layer like SMB2 or others. 

[darksidemaze]

Access(Jet)/Excel have a number of interfaces in order to access the data store, ODBC, COM and Ado.

ODBC is the typical/popular method to connect to RDBMS data stores. 

ISAM files (dbase) have a "driver" code which uses the OS disk api's to handle the reading and writing on disk, all the record fetching and sorting so some database drivers were faster than going through the more generic ODBC interface as a result, also seen when COM became a thang and ADO became a thang. COM is like the next version upgrade of ODBC, or it takes from ODBC and builds upon it. ODBC just provides a framework to access some files stored on a FS somewhere. 

Just different layers of code running on different machines to get at data, some are more efficient in some scenarios than others.

Thats all there is to it really, but dont tell the marketing depts... 😊

[wila]

Hi,

---

@darksidemaze wrote:

So knowing how Microsoft provides backwards compatibility with Windows, I would expect SMB1 oplock settings to have an effect for backward compatibility reasons even today, even if its going through a new updated layer like SMB2 or others. 

---

I think this is all getting very off topic for the original question, but since you are so interested in oplocks.
Have a look at this paper:
http://www.dataaccess.com/kbasepublic/files/2476.tuning%20microsoft%20networks%20for%20the%20legacy%...

Those settings are not backwards compatible. In fact, you can't even turn off oplocks in SMBv2 and up.
FWIW me and some friends worked on software that would automatically set and verify the oplock registry settings for those ISAM databases when starting an application using this type of database. This was used by thousands of developers. But with SMBv2 and up the push to stay on ISAM has been a lot less due to the inability to turn off oplocks now (switching off SMBv2 or SMBv3 as discussed in the pdf is not a good suggestion as SMBv1 has serious security issues)

I seem to have some recollection that we actually did have some settings for SMBv2 (not SMBv3), but would have to look at the code base to verify. It's been over a decade, so my memory is a bit fuzzy on this.

note: edited to add some details.

--
Wil

[darksidemaze]

How to detect, enable and disable SMBv1, SMBv2, and SMBv3 in Windows | Microsoft Docs

"Disabling SMBv2 or SMBv3 for troubleshooting"

"Client oplock leasing model - limits the data transferred between the client and server, improving performance on high-latency"

Overview of file sharing using the SMB 3 protocol in Windows Server | Microsoft Docs

"Performance over WAN" 

"Directory opportunistic locks (oplocks) and oplock leases were introduced in SMB 3.0. For typical office/client workloads, oplocks/leases are shown to reduce network round trips by approximately 15%.

In SMB 3, the Windows implementation of SMB has been refined to improve the caching behavior on the client as well as the ability to push higher throughputs.

SMB 3 features improvements to the CopyFile() API, as well as to associated tools such as Robocopy, to push significantly more data over the network."

I dont think you'll find oplocks ever went away in one form or another.

[wila]

Hello,

---

@darksidemaze wrote:

I dont think you'll find oplocks ever went away in one form or another.

---

Not sure if you're implying I said that it did?

What I said:

"In fact, you can't even turn off oplocks in SMBv2 and up."

Yes you are correct that the oplock implementation has improved over time.

But can we please stop this off topic debate?
I think we are actually more in agreement about all this than our discussion implies. While talking technicalities can be fun, I'd rather help to solve the original question.

The oplock discussion has absolutely no value for the topic starter, nor is it VMware specific (oplocks work fine with VMs).
Until the TS is telling us that they want to host the MariaDB database on SMB, I fail to see the relevance.

Thank you for your understanding.

--
Wil