Skip to Content
It is currently December 12th, 2017, 11:54 am

All times are UTC - 6 hours [ DST ]




Post new topic Reply to topic  [ 13 posts ] 
Author Message
PostPosted: November 23rd, 2015, 2:50 pm 
Offline
New User
New User

Joined: February 17th, 2014, 5:25 pm
Posts: 5
Location: Huntington Beach, California
The newest version of MySQL V5.7 (5.7.6 and newer) causes a new Backup failure error...

Backup failed!! The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

For those who get the aforementioned error message with SAM 2014.n & SAM 2015.n when backing up their MySQL 5.7.6 or greater databases under Windows 7, Windows 8 and now Windows 10
just do these two things:

GRANT THE ABILITY TO ISSUE DATABASE DUMPS AS FOLLOWS

1) Shutdown MySQL from the Services Panel
2) Navigate to your C:\ProgramData\MySQL\MySQL Server 5.7 folder and edit the my.ini configuration file. [ProgramData is a hidden directory]
3) Locate the following secure-file-priv option and comment it out [#] or erase it all together:

# Secure File Priv - This command limits the location that are allowed to be specified when creating a database backup or dump.

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

4) Save the my.ini file
5) Start MySQL from the Services Panel

If you then start receiving the following error:

Can't create/write to file C:\Users\youraccountname\AppData\Local\SpacialAudio\SAMBC\Backup\Backup_2015-11-18.... Errcode 13 - Permission denied)

just do this:

1) Navigate to C:\Users\[login account]\AppData\Local\SpacialAudio
2) Select the SAMBC folder
3) Right-mouse click and select the properties option
4) Select the Security tab
5) Press the [ Edit ] button
6) Press the [ Add ] button
7) Type "NETWORK SERVICE" in the Object Name window and press the OK button.
This adds the NETWORK SERVICE credentials to the access control list
8) Select the new NETWORK SERVICE entry and then press the [Full Access] permission
9) Press the [ Apply ] button
10) Back out all the way and then launch SAM and test the Backup.

That should fix you all up [again] nicely.

_________________
Always,

KarenMichelle


Last edited by KarenMichelle on November 24th, 2015, 12:11 pm, edited 2 times in total.

Top
 Profile  
Reply with quote  
PostPosted: November 23rd, 2015, 3:16 pm 
Offline
SVS Member
SVS Member

Joined: December 6th, 2004, 9:00 am
Posts: 7380
Location: Aachen (Germany)
Thanks for the hint. Just googled what the setting is supposed to do and when it was introduced.
Apparently secure-file-priv has been around for quite a while but it's default value was empty so far.
The setting limits the directories from where you can load / to where you can store directly from MySQL queries. (SAM is quite the exempt from the rule here, most software will read the entries and then create an SQL dump from that itself, SAM doesn't do that, but merely instructs the database to export the data to a CSV file directly)

I'll make use of my privileges and change the topic to be more specific, as this doesn't affect all MySQL 5.7 releases, but only the very recent ones (MySQL up to and including 5.7.5 had the default empty value for that config directive and only the 5.7.6 and onwards will have a platform specific default value set here)
Nonetheless, thank you very much for bringing this problem up and even supplying a solution right away.

_________________
Benedikt Bauer - SVS (Spacial Volunteer Support)

We're offering custom PAL / PHP code and general SAM assistance at palscripts.com

My Project:
Send "Now Playing" from SAM to Twitter and/or Facebook | Sourcecode


Top
 Profile  
Reply with quote  
PostPosted: November 24th, 2015, 12:04 pm 
Offline
New User
New User

Joined: February 17th, 2014, 5:25 pm
Posts: 5
Location: Huntington Beach, California
Thank you Mastacheata for adding that correction/clarification.

:D

_________________
Always,

KarenMichelle


Top
 Profile  
Reply with quote  
PostPosted: May 21st, 2016, 7:57 pm 
Offline
Senior User
Senior User

Joined: March 11th, 2003, 9:20 am
Posts: 111
Location: Charlotte NC
See Below...DP...oops.

_________________
-- JE
.::.(())::. Urban Hitz Radio™ USA .::.(())::. The People' Choice for Hip-Hop & R&B!
Listen to Urban Hitz RadioGET A Local or TOLL-FREE Request Line NOW...Only $2 Monthly – Request Line: (855) 588-6767 *SAM:Licensed 2016.3*


Last edited by UrbanHitzRadio on May 22nd, 2016, 8:05 am, edited 1 time in total.

Top
 Profile  
Reply with quote  
PostPosted: May 21st, 2016, 7:58 pm 
Offline
Senior User
Senior User

Joined: March 11th, 2003, 9:20 am
Posts: 111
Location: Charlotte NC
Excellent Source. This needs to be a sticky of some-sort. I appreciate it.

I will be moving my station from a Windows 2008 to 2012 and a new server with a new company, so this came in very handy. I just want to be 100% prepared to flip the switch.
You're the only person with the CORRECT ANSWERS all over the internet: KarenMichelle.

Let me ask you all - is SAM supposed to move over:
Code:
Encoders, Relays and PALs
? Because mine didn't. Everything else is there. I moved from MySQL 5.5 to 5.7.12.

Thanks for this very thorough tutorial.

_________________
-- JE
.::.(())::. Urban Hitz Radio™ USA .::.(())::. The People' Choice for Hip-Hop & R&B!
Listen to Urban Hitz RadioGET A Local or TOLL-FREE Request Line NOW...Only $2 Monthly – Request Line: (855) 588-6767 *SAM:Licensed 2016.3*


Top
 Profile  
Reply with quote  
PostPosted: May 23rd, 2016, 5:25 am 
Offline
SVS Member
SVS Member

Joined: December 6th, 2004, 9:00 am
Posts: 7380
Location: Aachen (Germany)
Encoders, relays, PALs and just about everything else from the config screen is stored in the SAMBC.core.xml file and not in the database.
The database backup only saves the library part of SAM.

I've picked up your hint on pinning this thread. Not exactly what you asked, but I've added a notice to the already-sticky thread about problems with MySQL 5.6.

_________________
Benedikt Bauer - SVS (Spacial Volunteer Support)

We're offering custom PAL / PHP code and general SAM assistance at palscripts.com

My Project:
Send "Now Playing" from SAM to Twitter and/or Facebook | Sourcecode


Top
 Profile  
Reply with quote  
PostPosted: October 20th, 2016, 8:16 pm 
Offline
Senior User
Senior User

Joined: August 22nd, 2006, 7:38 am
Posts: 225
Location: Santo Domingo, Dominican Republic
I'm now running a remote MySqL DB in a Linux server with Cpanel, and having this "backup failed! Access denied error for user..." Can someone help in how to fix that.
Running MySqL V.5.1.73
and newer SAMBC version

_________________
Image


Top
 Profile  
Reply with quote  
PostPosted: October 22nd, 2016, 5:55 am 
Offline
SVS Member
SVS Member

Joined: December 6th, 2004, 9:00 am
Posts: 7380
Location: Aachen (Germany)
Your problem is slightly different I suppose.
SAM instructs the Database server to dump SAM's database as a CSV file onto the harddisk and specifies C:\Users\youraccountname\AppData\Local\SpacialAudio\SAMBC\Backup\Backup_2015-11-18... as the path to store the file.
There's two problems with that:
It's the server performing the dump and save stuff, not the client. Thus, your linux server needs to have a LOCAL path C:\Users\youraccountname\AppData\Local\SpacialAudio\SAMBC\Backup\Backup_2015-11-18... to write to. (Pro Tip: Linux paths don't look like that)
That won't work because:
#1 That's not a legit path on linux.
#2 Even if it could write to that path, your SAM wouldn't see any of it as the DB server is writing on the server harddisk, not on your SAM client harddisk.


My tip: Forget about SAM internal backup feature and do the backups yourself. Your Webhost is probably already performing regular automated database backups on the server. So all you need to care about is to copy the SAMBC.core.xml config file to a safe location.

_________________
Benedikt Bauer - SVS (Spacial Volunteer Support)

We're offering custom PAL / PHP code and general SAM assistance at palscripts.com

My Project:
Send "Now Playing" from SAM to Twitter and/or Facebook | Sourcecode


Top
 Profile  
Reply with quote  
PostPosted: October 23rd, 2016, 3:50 pm 
Offline
Senior User
Senior User

Joined: August 22nd, 2006, 7:38 am
Posts: 225
Location: Santo Domingo, Dominican Republic
Thank you so much Matacheata, as always give us very good help.
You're right, it's what I'll do.

_________________
Image


Top
 Profile  
Reply with quote  
PostPosted: November 18th, 2016, 12:04 am 
Offline
New User
New User

Joined: November 16th, 2013, 7:41 pm
Posts: 6
hello everyone i'm still having the same issue i did everything posted on the first post but sam giving me the same error
Backup failed!! The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
did i miss Something ?


Top
 Profile  
Reply with quote  
PostPosted: March 7th, 2017, 12:36 am 
Offline
New User
New User

Joined: October 28th, 2015, 10:44 pm
Posts: 9
OK! I got it :-)

for MS Windows 7 MySQL 5.7

1. Stop MySQL57 service
2. secure-file-priv="" //delete path set between "" in my.ini config file
3. > Services > set Log On tab to Local System
4. Restart MySQL57 service

That's it :-)


Top
 Profile  
Reply with quote  
PostPosted: October 19th, 2017, 2:46 am 
Offline
Senior User
Senior User

Joined: March 11th, 2003, 9:20 am
Posts: 111
Location: Charlotte NC
UPDATE... BACKUP error returns using updated MySQL 5.7.16 and later (Backup failed!! The MySQL server is running with the --secure-file-priv option)...
So, as you know, those of us who like to keep our software updated, we usually run into repeat problems that rear their ugly heads. In this case, that infamous Backup failed! MySQL error. This error would re-appear for those users that have since updated their version of MySQL, as mentioned above - to a version of 5.7.16 and newer (5.7.20). etc.
If you followed KarenMichelles advice from 5.7.6-5.7.11, and you have NOT updated MySQL since and your backups are working fine, you need to do NOTHING!
IF, you have since , updated MySQL and the Backup error has returned...

I searched Google for an answer, reading countless MySQL respositories and thanks to the good souls over at StackOverflow...

H E R E * I S * T H E * S O L U T I O N:

As KarenMichelle advised (with a twist):

GRANT THE ABILITY TO ISSUE DATABASE DUMPS AS FOLLOWS
    1) Shutdown MySQL from the Services Panel
    2) Navigate to your C:\ProgramData\MySQL\MySQL Server 5.7 folder and edit the my.ini configuration file. [Make sure your text editor is launched in Administrator mode]
    3) Locate the following syntax:
Code:
# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.6/Uploads"


this time, instead if erasing it all together or commenting it out, leave: secure-file-priv= as a BLANK STRING, like so...
Code:
secure-file-priv=""

SAVE your my.ini file.

******** N E X T S T E P ********

OPEN POWERSHELL FROM THE MySQL BIN FOLDER
    1) Navigate to the: C:\Program Files\MySQL\MySQL Server 5.7\bin folder.
    2) In the ADDRESS BAR, type POWERSHELL and hit Enter.
    3) You should now see a nice blue POWERSHELL window.
    4) Type: ./mysqld - hit Enter. (your MySQL daemon should have restarted successfully)

Restart SAM broadcaster. Run the Backup command. You should now see SAM advise that a backup has been generated...VIOLA!

- And, also, as KarenMichelle advised -

Quote:
If you then start receiving the following error:

Can't create/write to file C:\Users\youraccountname\AppData\Local\SpacialAudio\SAMBC\Backup\Backup_2015-11-18.... Errcode 13 - Permission denied)

just do this:

1) Navigate to C:\Users\[login account]\AppData\Local\SpacialAudio
2) Select the SAMBC folder
3) Right-mouse click and select the properties option
4) Select the Security tab
5) Press the [ Edit ] button
6) Press the [ Add ] button
7) Type "NETWORK SERVICE" in the Object Name window and press the OK button.
This adds the NETWORK SERVICE credentials to the access control list
8) Select the new NETWORK SERVICE entry and then press the [Full Access] permission
9) Press the [ Apply ] button
10) Back out all the way and then launch SAM and test the Backup.

_________________
-- JE
.::.(())::. Urban Hitz Radio™ USA .::.(())::. The People' Choice for Hip-Hop & R&B!
Listen to Urban Hitz RadioGET A Local or TOLL-FREE Request Line NOW...Only $2 Monthly – Request Line: (855) 588-6767 *SAM:Licensed 2016.3*


Top
 Profile  
Reply with quote  
PostPosted: October 19th, 2017, 1:08 pm 
Offline
SVS Member
SVS Member

Joined: December 6th, 2004, 9:00 am
Posts: 7380
Location: Aachen (Germany)
We should urge Spacial to rebuild the backup mechanism and create their own dump instead of leaving that to the server.
Obviously MySQL isn't meant to be used that way anymore.

From a security perspective this makes complete sense. If you let an arbitrary program with low privileges on MySQL instruct the database server to dump a file this is practically a privilege escalation. In this case SAM gets to write files to arbitrary locations with the privileges of the MySQL server daemon instead of its own.

The CSV export of MySQL was never meant to be a backup mechanism in the first place.
Sorry guys the shortcut is closed and you need to do your homework this time :P

_________________
Benedikt Bauer - SVS (Spacial Volunteer Support)

We're offering custom PAL / PHP code and general SAM assistance at palscripts.com

My Project:
Send "Now Playing" from SAM to Twitter and/or Facebook | Sourcecode


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 

All times are UTC - 6 hours [ DST ]


Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 20 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group