0

I've been given an phpmyadmin username and password to the firms MySQL database.

I exported the database with an SQL dump of what I thought was all the data. However it looks like there is a certain table I got the structure of but not the data.. I noticed that table is the type of "View" and if I try to export it separately it shows: CREATE ALGORITHM=UNDEFINED DEFINER=...

Does this mean I need some user with a higher permission to get access to export the the data?

Here is the line from when I export the view: 

CREATE ALGORITHM=UNDEFINED DEFINER=`**********` SQL SECURITY DEFINER VIEW `NERVMUSCACCP` AS select `NERVNAME`.`NerveCode` AS `Code`,`NERVNAME`.`NerveName` AS `NAME`,`NERVNAME`.`ShortNerveName` AS `ShortName` from `NERVNAME` union select `MUSCNAME`.`MuscleCode` AS `MuscleCode`,`MUSCNAME`.`MuscleName` AS `MuscleName`,`MUSCNAME`.`ShortMuscleName` AS `ShortMuscleName` from `MUSCNAME` union select `ACCPNAME`.`Code` AS `Code`,`ACCPNAME`.`Name` AS `Name`,`ACCPNAME`.`ShortAccPName` AS `ShortAccPName` from `ACCPNAME`;
Fred
  • 3
  • 5
  • 3
    Views are virtual tables they don't contain data.https://stackoverflow.com/questions/6015175/difference-between-view-and-table-in-sql – P.Salmon Sep 05 '18 at 07:38
  • But I can see in the old database that it contains data? – Fred Sep 05 '18 at 08:03
  • Can you please check from which table(s) is the view created and try to create/update it in your system similarly. – Arihant Sep 05 '18 at 08:23
  • I have created it in my new environment, that's is how I discovered it did not have any data. Although the other tables that it looks like it is using are stored with data . – Fred Sep 05 '18 at 08:35
  • If you select from your view and it returns nothing then one or more of the tables which are accessed by the view may be empty or join conditions may result in 0 rows returned or the where condition (if there is one) may may result in 0 rows. I suggest you check each of the tables contains data, then check the join conditions then check the where conditions. And if the view qualifies table names with database name and if so that the qualifier is correct. Adding the view code and sample data to the question as text might help. – P.Salmon Sep 05 '18 at 10:24
  • @P.Salmon I updated the post with the line from the export of the view. As you can see its lots of tables, thought that would be too much to put that in my post. It's some very old code that I've been handed, so maybe its some kind of older SQL verison issue I'm facing? just a thought. – Fred Sep 05 '18 at 11:41
  • I just noticed if I try to run the sql line manually it gives me this error: Access denied; you need (at least one of) the SUPER privilege(s) for this operation – Fred Sep 05 '18 at 12:09
  • I made it work! I deleted the define view.. and used Create instead. Thank you for your help! – Fred Sep 05 '18 at 12:12

0 Answers0