Export View Fails

[Keith Davis]

I created this View

CREATE ALGORITHM = UNDEFINED DEFINER=`root`@`localhost`

VIEW `charges_by_submission_date` AS

SELECT

ps.SubmissionDate SubmissionDate,

ps.TransactionNo,

ps.FormNumber

FROM

premier_submissions ps

UNION

SELECT

psh.LastSubmissionDate SubmissionDate,

psh.TransactionNo,

psh.FormNumber

FROM

premier_submissionhistory psh

UNION

SELECT

SPLIT_STR (ps.SubmissionDates, '*', 1) SubmissionDate,

ps.TransactionNo,

ps.FormNumber

FROM

premier_submissions ps

UNION

SELECT

SPLIT_STR (psh.ResubmissionDates, '*', 1) SubmissionDate,

psh.TransactionNo,

psh.FormNumber

FROM

premier_submissionhistory psh

When I run a SQLyog export of [All – Includinging MySQL system database], I get this error:

Scheduled export started at: Thu Dec 31 03:00:43 2015



Exporting Database pride...



Exporting function SPLIT_STR...

Export successful...

View 'pride.charges_by_submission_date' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Exporting view case_key_by_account...

Export successful...

I can do an export of just this view or the function SPLIT_STR, or even the whole database pride, but only if I export all databases. I’ve not had this issue with any other object (including SPLIT_STR).

 

 

[Keith Davis]

No ideas?

[peterlaursen]

Sorry for the late reply. We will try to reproduce “but only if I export all databases [including ‘mysql’]”.

 

However please:

1) tell the server version. 

2) are underlying tables MyISAM or InnoDB? 

3) Your settings for lock and flush on source. Please see image. [attachment=2056:lockandflush.PNG]

 

[Keith Davis]

MySQL 5.6.26

InnoDB

 

I’m not using Backup Databases as SQL Dump, I’m using Scheduled Backups.

 

[attachment=2057:Capture.JPG]

 

 

 

 

[peterlaursen]

OK …we will try to dig into this.  Are you ‘root’@’localhost’ when backing up? And does he/you have privileges to underlying tables? Simply connect (as same user) and execute “SHOW GRANTS;”

[Keith Davis]

 

 

Are you ‘root’@’localhost’ when backing up? 

 

No. 

 

mysqlbackup@[email protected]

 

 

And does he/you have privileges to underlying tables?

 

I thought so, but now that I look at these again, I believe that the problem is that this user needs execute, right? The problem is, why doesn’t this fail if run by that same user for only that view?

SHOW GRANTS;



GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'mysqlbackup'@'app02.pridedallas.com' IDENTIFIED BY PASSWORD 

[Keith Davis]

So, that was the problem. Still don’t understand my test results though (which is why I didn’t check grants further), but oh well. 🙂

[Author]

Posts