Unable to access custom View from MariaDB. ERROR [HY000]

[Anonymous]

Dear community,

I am trying to get information from a view from a MariaDB database through an ODBC connection. All the tables and most of the views work fine, meaning that I can access them without any problem. However, when I try to access to two of the views, Power Query gives me the following error message:

DataSource.Error: ODBC: ERROR [HY000] [MySQL][ODBC 8.0(w) Driver][mysqld-5.5.5-10.3.18-MariaDB-0+deb10u1]View 'schema.view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Details:
DataSourceKind=Odbc
DataSourcePath=dsn=database
OdbcErrors=[Table]

After that, I decided to investigate in the database. I opened a database manager studio, in my case HeidiSQL. I checked if I was able to access those views with the same user, and there was not a problem at all. Just in case, I decided to grant all the possible rights to the user for all the databases in the instance, but the output was the same:  In HeidiSQL, I am able to get the information from the views, but not in Power Query.

Later I decided to download backups from the databases within the instance, so I could install them in a new localhost instance. First of all, I decided to use the most updated and stable version of Maria DB. With a root user, I installed the databases and defined the views. The views work perfectly again, but after configuring the ODBC connector for the new localhost instance and connecting to it in Power Query, I get the same output again. Everything works except those two views. The error message in this scenario is slightly different, but only changes the version of Maria DB and the DataSourcePath.

Does anybody have an idea of why this can be happening?

Thank you!

[dax]

Hi @Anonymous , 

In your scenario, it seems that only two data view can't work , right? And your error information is about invaild reference or permission. You have checked the permission problem, I think you need to check the column to see whether there is something wrong in column or function. Compare it with other view to see which part is different, and also need to check whether column name is correct or whether it contains special characters.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Dear Zoe Zhi ( @dax ),

Thank you for your reply. I have been working since then in trying to identify what was the source of this error. I did found that the problem was coming from a view (called weeks) that was being used in my view. When trying to import the "weeks" view in Power BI I was getting the same error, and when I removed it from my view, the problem wasn't there anymore.

I started then searching for the error within the view "weeks". Of course, both views were working perfectly when querying them in HeidiSQL, but not when loading them into Power BI. That view is fully independent, and it doesn't need other tables or views to be build. It shows two columns: weeks and year. All the weeks numbers within the current year. I debugged it, removing parts of it and loading it into Power BI. I found that:

• This piece of code was working when loading it in PowerBI:

SELECT         *    FROM
        (SELECT             ADDDATE('1970-01-01', t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) datelist        FROM
            (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3, (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) AS v

• This was not working:

SELECT         WEEK(datelist, 3) AS `week`, YEAR(datelist) AS `year`    FROM
        (SELECT             ADDDATE('1970-01-01', t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) datelist        FROM
            (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3, (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) AS v

I thought it was weird, so I decided to rebuild the view from scratch. I know I could do a table instead of a view, but this wouldn't fullfill the requirements. I found that the new view was having a similar problem, but even stranger:

• This was working:

select (t*10+u+1) x from
            (select 0 t union select 1 union select 2 union select 3 union select 4 union
            select 5 union select 6 union select 7 union select 8 union select 9) A,            (select 0 u union select 1 union select 2 union select 3 union select 4 union
            select 5 union select 6 union select 7 union select 8 union select 9) B

• But this was not working:

SELECT *from(        select (t*10+u+1) x from
            (select 0 t union select 1 union select 2 union select 3 union select 4 union
            select 5 union select 6 union select 7 union select 8 union select 9) A,            (select 0 u union select 1 union select 2 union select 3 union select 4 union
            select 5 union select 6 union select 7 union select 8 union select 9) B)AS c

I am not very familiar yet with MariaDB and mySQL syntaxis, but with my experience in MSQL I would say that the last pair of querys are exactly the same. Additionally, I would like to point out that in this pair of views, I have tried several different aliases and syntaxis such as select x from (t*10+u+1) x or select x from (t*10+u+1) as x, and equivalents, without success.

Thank you for your time

Sergio