Jump to content
Covecube Inc.
  • 0

MySQL (Mariadb) errors only when using drivepool.


Question

Does anyone have experience with a SQL database on drivepool? When checking logs after moving my database to the drivepool I noticed some errors:

2018-03-01 10:32:23 7100 [Note] InnoDB: innodb_page_size=32768
2018-03-01 10:32:23 7100 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2018-03-01 10:32:23 7100 [Note] InnoDB: Uses event mutexes
2018-03-01 10:32:23 7100 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-03-01 10:32:23 7100 [Note] InnoDB: Number of pools: 1
2018-03-01 10:32:23 7100 [Note] InnoDB: Using SSE2 crc32 instructions
2018-03-01 10:32:23 7100 [Note] InnoDB: Initializing buffer pool, total size = 896M, instances = 1, chunk size = 128M
2018-03-01 10:32:23 7100 [Note] InnoDB: Completed initialization of buffer pool
2018-03-01 10:32:23 7100 [Note] InnoDB: Highest supported file format is Barracuda.
2018-03-01 10:32:25 7100 [Note] InnoDB: 128 out of 128 rollback segments are active.
2018-03-01 10:32:25 7100 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-03-01 10:32:25 7100 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-03-01 10:32:25 7100 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2018-03-01 10:32:25 7100 [ERROR] InnoDB: Operating system error number 24 in a file operation.
2018-03-01 10:32:25 7100 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2018-03-01 10:32:25 7100 [ERROR] InnoDB: File \\.\D:: 'DeviceIoControl(IOCTL_STORAGE_QUERY_PROPERTY)' returned OS error 224.
2018-03-01 10:32:25 7100 [Note] InnoDB: Waiting for purge to start
2018-03-01 10:32:25 7100 [Note] InnoDB: 5.7.20 started; log sequence number 33980985041
2018-03-01 10:32:25 6684 [Note] InnoDB: Loading buffer pool(s) from D:\ServerFiles\MariaDB\ib_buffer_pool
2018-03-01 10:32:25 7100 [Note] Plugin 'FEEDBACK' is disabled.
2018-03-01 10:32:25 7100 [Note] Server socket created on IP: '::'.
2018-03-01 10:32:26 7100 [Note] Reading of all Master_info entries succeded
2018-03-01 10:32:26 7100 [Note] Added new Master_info '' to hash table
2018-03-01 10:32:26 7100 [Note] C:\Program Files\MariaDB 10.2\bin\mysqld.exe: ready for connections.
Version: '10.2.12-MariaDB-log'  socket: ''  port: 3306  mariadb.org binary distribution
2018-03-01 10:32:27 6684 [Note] InnoDB: Buffer pool(s) load completed at 180301 10:32:27

That error is reported every time the service is started if the database is on drivepool.

EDIT: The SQL service still runs but I am not sure how safe it is.

Link to post
Share on other sites

24 answers to this question

Recommended Posts

  • 0

What version of StableBit DrivePool are you using? 

And have you tried upgrading to the Public RC build? 

 

If you're on the RC build, and you're still seeing this issue then stop the service, enable file system logging, start the service and wait for it to fail. 

Once it does, stop the logging, and upload the logs to us. 

http://wiki.covecube.com/StableBit_DrivePool_2.x_Log_Collection

 

 

Link to post
Share on other sites
  • 0

Yes I am on latest RC.

Quote

If you're on the RC build, and you're still seeing this issue then stop the service, enable file system logging, start the service and wait for it to fail. 

Would it be okay to turn mariadb service off, enable logging, turn mariadb back on with error reported, stop mariadb, and then send you the logs? It's used for a small website that I just had it throw errors saying it hit it's max connections which it has never done before. I am trying to avoid more downtime.

EDIT: Database logs show same errors reported many times before the max connection outage:

2018-03-01 11:15:49 9192 [ERROR] InnoDB: Operating system error number 24 in a file operation.
2018-03-01 11:15:49 9192 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2018-03-01 11:15:49 9192 [ERROR] InnoDB: File \\.\D:: 'DeviceIoControl(IOCTL_STORAGE_QUERY_PROPERTY)' returned OS error 224.


 

 

Link to post
Share on other sites
  • 0

Hmmm, strange error.

According to Microsoft you can use GetLastError on DeviceIoControl, so if we look here Microsoft then error 224 is:

ERROR_FORMS_AUTH_REQUIRED 224 (0xE0) Access Denied. Before opening files in this location, you must first add the web site to your trusted sites list, browse to the web site, and select the option to login automatically.

If the error is in Hex then we get: ERROR_DEVICE_ALREADY_ATTACHED 548 (0x224) An attempt was made to attach to a device that was already attached to another device.

None of which makes much sense...

Regards,

Link to post
Share on other sites
  • 0
19 hours ago, Scuro said:

Would it be okay to turn mariadb service off, enable logging, turn mariadb back on with error reported, stop mariadb, and then send you the logs? It's used for a small website that I just had it throw errors saying it hit it's max connections which it has never done before. I am trying to avoid more downtime.

That would be perfect, actually. 

And I definitely understand about downtime. 

Link to post
Share on other sites
  • 0

Done. I have submitted the service logs as requested.

I also verified that Mariadb generated the same errors as before when I moved it back to drivepool.

It otherwise runs without issue when the database is not used on drivepool.

Link to post
Share on other sites
  • 0

I just tested this out.  Works fine.  

So, it may be a "simple" permission issue.  On the SQL data directory on the pool, make sure that the account that is running MariaDB has full control.  This should be "SYSTEM" by default. 

If that doesn't have full control, then it could/would cause this. 

Link to post
Share on other sites
  • 0
12 minutes ago, Christopher (Drashna) said:

I just tested this out.  Works fine.  

So, it may be a "simple" permission issue.  On the SQL data directory on the pool, make sure that the account that is running MariaDB has full control.  This should be "SYSTEM" by default. 

If that doesn't have full control, then it could/would cause this. 

Thanks for your testing Christopher. I have more information that you could maybe test.

It's 100% replicable for me and is not permissions. The folder has the exact same permissions while on the drivepool, that it has off of it.

It is not run as system, but as a service account, and I gave that service account the correct permissions to the directory. Same permissions on multiple test directories on different drives do not result in the errors if the directory is not in the pool.

Mariadb was also a duplicated directory when it was on the pool.

Be warned that this has caused massive unstability for me. I have since removed mariadb + www (was also a drivepool duplicated directory) directories off of drivepool. The IO errors, php/apache2 errors, and the database errors have all stopped after removing them from the pool.

Link to post
Share on other sites
  • 0

The service account could be why it's causing issues.

However, the instability is odd. I've ran MySQL/MariaDB on the pool for a while, and never had issues with it (unless a disk went missing).  So this is very odd, indeed.   
My guess would be that you have one or more disks that are having I/O issues, and causing this.  
I've seen this sort of behavior on my system, and have had more than a few tickets where this was the case.  A single bad drive can cause all sorts of issues. 

Link to post
Share on other sites
  • 0

Have you tested it with folder duplication? 

I have tested it outside of the pool now on the two drives I had it duplicated on with the exact same permissions. Both drives have completely solved all instability errors and problems if I use the database outside of the pool. The errors come back immediately if I copy it to the drivepool. 

Link to post
Share on other sites
  • 0

Apache/PHP were also throwing errors at random for parse_ini_file() saying "parse_ini_file failed to open stream" when duplication was enabled. 

Placing the ini file used for the script outside of the pool on the same drives that were duplicated in the pool solved that problem as well. 

Edit: The drives that were used in the the drivepool for the database were SSDs that one shares with the OS and the other VMs. Neither have shown problems outside of using drivepool for mariadb or apache/php files with duplication enabled. 

Link to post
Share on other sites
  • 0
2 hours ago, Christopher (Drashna) said:

Also, what OS are you using and what version of StableBit DrivePool do you have installed? 

Windows 10 Pro version 1709 with stablebit 2.2.0.904 RC.

I have had zero problems and errors after moving the database off the drivepool. I had been hammering my head trying to figure out where all these IO errors were coming from and had thought it was a power supply in another thread (every drive was throwing IO errors in event log). Turns out I was getting those IO errors because every drive was part of the pool. Once I removed mariadb from the pool, it has been running flawlessly.

Due to the instability of the OS as well as IO errors in event log, I don't believe incorrect permissions for a database would lead to OS hardlocks (no BSOD, computer would become unresponsive with hard drive LEDs stuck on).

Server uptime is now 4 days 13 hours with zero reported disk errors or mariadb errors.

EDIT: I was able to move VMs back without issue. It's some kind of conflict with mariadb and my database on drivepool with duplication.

Link to post
Share on other sites
  • 0

That's .... really bizarre.  Because that's specifically what I tested, and it seemed to work fine. 

On 3/6/2018 at 11:57 AM, Scuro said:

Due to the instability of the OS as well as IO errors in event log, I don't believe incorrect permissions for a database would lead to OS hardlocks (no BSOD, computer would become unresponsive with hard drive LEDs stuck on).

No, it wouldn't, but I/O errors COULD cause it to hard lock, actually. 
Unfortunately, I have seen issues similar to this before, and the problem eventually was tracked back to a bad drive. 

 

Just in case, could you run the StableBit Troubleshooter, so we can see what was going on.  It may be helpful to pinpoint the issue. 
http://wiki.covecube.com/StableBit_Troubleshooter
Use "27751" for the Contact ID

 

(for reference: 27751)

Link to post
Share on other sites
  • 0

Thanks for your time Christopher.

I already submitted a troubleshoot report in this thread: 

I am not sure if it would just be redundant for me to submit another.

In that thread I had thought my problems were because of a new power supply I had gotten around the time the errors first started happening. After thinking about it, I remembered I switched the mariadb database to drivepool at roughly the same time I got the new case/power supply. So it should contain all the same data as mariadb was still on the drivepool at the time the troubleshooter was run.

Link to post
Share on other sites
  • 0
14 minutes ago, Christopher (Drashna) said:

I can see the DrivePool logs, but I don't see Troubleshooter data.

These are two different things, and the Troubleshooter grabs a lot more data (about the system, not just our software). 
 

I submitted the troubleshooter with the requested ticket ID in the thread I linked. Was that data lost or never make it? 

Link to post
Share on other sites
  • 0
On 3/1/2018 at 6:03 PM, Scuro said:

2018-03-01 11:15:49 9192 [ERROR] InnoDB: Operating system error number 24 in a file operation. 2018-03-01 11:15:49 9192 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html 2018-03-01 11:15:49 9192 [ERROR] InnoDB: File \\.\D:: 'DeviceIoControl(IOCTL_STORAGE_QUERY_PROPERTY)' returned OS error 224.

I had a go with Google at "Operating system error 24" and found this. Apparently this means too many opened files and you can fix it by changing a setting in the database config file? Not sure why this is not also happening on the plain disks. Still no idea what error 224 means...

Link to post
Share on other sites
  • 0
13 hours ago, B00ze said:

I had a go with Google at "Operating system error 24" and found this. Apparently this means too many opened files and you can fix it by changing a setting in the database config file? Not sure why this is not also happening on the plain disks. Still no idea what error 224 means...

Thanks for your help B00ze. I however believe that is a Linux OS error code. Windows seems to have a limit around 17 million open files.

Link to post
Share on other sites
  • 0
18 hours ago, Christopher (Drashna) said:

I can see the DrivePool logs, but I don't see Troubleshooter data.

These are two different things, and the Troubleshooter grabs a lot more data (about the system, not just our software). 
 

Christopher, I have submitted another troubleshooter report to avoid confusion. I made sure mariadb was back on the pool before submitting. The errors returned again after moving it back to the pool. I moved it back off the pool after the troubleshooter was submitted.

Link to post
Share on other sites
  • 0

As an update I was able to narrow down where the problem occurs.

 

innodb_temp_data_file_path=/temp/ibtmp1:12M:autoextend:max:1G

This variable, if selected on a drivepool mount, will generate the errors:

2018-12-05 11:03:42 0 [ERROR] InnoDB: Operating system error number 24 in a file operation.
2018-12-05 11:03:42 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2018-12-05 11:03:42 0 [ERROR] InnoDB: File \\.\D:: 'DeviceIoControl(IOCTL_STORAGE_QUERY_PROPERTY)' returned OS error 224.

And cause instability problems.

It defaults to the data directory of your database. 

If I use a junction to point the temp directory to a directory outside of the pool it runs without errors.

 

On 3/12/2018 at 12:54 PM, Christopher (Drashna) said:

Even watching the startup on my system, and well.... looking at my databases, it shouldn't have an issue with the number of open files.

 

But this is definitely an odd issue. 

Christopher, you might not be seeing these problems as you may need to have SQL queries that generate temp tables.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...