MYSQL connection - changing database source requires M query

Question

Hi,&nbsp;I am developing Power BI reports off a Dev MYSQL Database and now want to point the reports at the UAT database. Both databases have the same tables/structure and are hosted on the same server.When I create the connection to MYSQL, I have to enter a database name. The database name is then added to the start of every table name, eg. Dev Table1, Dev Table2, Dev Table3. A connection to UAT would have table names UAT Table1, etc.&nbsp;Therefore I cannot simply change the datasource database and refresh the data. I have to change the datasource AND change the M query for each table individually in the Advanced Editor, example below.&nbsp;Original Table Querylet&nbsp;&nbsp;&nbsp; Source = MySQL.Database("000.000.00.00", "Dev", [ReturnSingleDatabase=true]),&nbsp;&nbsp;&nbsp; Dev_Table1 = Source{[Schema="Dev",Item=" Table1 "]}[Data]in&nbsp;&nbsp;&nbsp; Dev_ Table1&nbsp;New Table Querylet&nbsp;&nbsp;&nbsp; Source = MySQL.Database("000.000.00.00", " UAT", [ReturnSingleDatabase=true]),&nbsp;&nbsp;&nbsp; UAT_ Table1 = Source{[Schema=" UAT",Item=" Table1 "]}[Data]in&nbsp;&nbsp;&nbsp; UAT_ Table1&nbsp;This becomes more complicated if steps were applied in the query editor (column renaming, etc). Changing each table is fine for one small report but when you want to change 30 large reports from Dev to UAT it is time-consuming and error-prone.&nbsp;In comparison, a SQL server connection does not add the database name to the start of the table name, so I can simply change the database in the data source settings and refresh.&nbsp;Any help/advice would be appreciated, even if it is just a better workaround then the one I am using.&nbsp;Thanks

anonymous · Answer

Hi&nbsp;gazumpt, In&nbsp; your scenario, you can create parameter for database names in Query Editor as shown in the following screenshot.   After you create reports for your Dev MySQL Database, you are able to switch the database to UAT database by changing the parameters’ values. For more details about creating parameter in Power BI Desktop, please review the following blog:http://biinsight.com/power-bi-desktop-query-parameters-part-1/
Another method is to paste the codes of Advanced Editor to Text file, press Ctrl+H, replace "Dev" with "UAT" in the codes, then copy the codes back to&nbsp;Advanced Editor. Thanks, Lydia Zhang

gazumpt · Answer

Hi Lydia,&nbsp;Thanks for your reply. Unfortunately, that does not solve the issue - the table names remain with the original database prefixed and must be changed one by one.I think that the issue here is that there is no difference between a schema and a database in MYSQL; they are synonymous.&nbsp;&nbsp;MYSQL is almost unique in this regard. If I make a connection to SQL Server, Oracle, etc with staging and dimension schemas and import stage.table1 and dim.table1, the table names will keep the schema prefixed in order to differentiate tables from different schemas, ie. stage table1, dim table1, which makes sense. In MYSQL the schema and the database are the same thing; so the database name is prefixed to the table.So, I need Power BI to accept the database name while ignoring&nbsp;the schema name (which is the same thing) when importing from Dev and&nbsp;also when I change the database to UAT.&nbsp;Not sure if this can be done using parameters or by adding a SQL query to the connection?&nbsp;Thanks

anonymous · Answer

Hi&nbsp;gazumpt,Could you please post a screenshot about your scenario when using parameter to define database name of MySQL? We are not able to make&nbsp;Power BI to accept the database name while ignoring&nbsp;the schema name.Thanks,Lydia Zhang

gazumpt · Answer

Hi Anonymous,&nbsp;&nbsp;Thanks again for your help with this. However, I cannot provide screenshots as the data is sensitive.&nbsp;I can tell you that the issue is exactly the same using parameters as without using parameters.&nbsp;For example, I created a database parameter with Dev and UAT.&nbsp;I set the parameter to Dev and import the data without issue. The table query in Advanced Editor is below -&nbsp;let&nbsp;&nbsp;&nbsp; Source = MySQL.Database("000.000.00.00", "Dev", [ReturnSingleDatabase=true]),&nbsp;&nbsp;&nbsp; Dev_Table1 = Source{[Schema="Dev",Item=" Table1 "]}[Data]in&nbsp;&nbsp;&nbsp; Dev_ Table1&nbsp;Then I change the parameter value to UAT and apply the changes. The data fails to refresh, giving the error 'The key didn't match any rows in the table.'Looking at the table query,&nbsp;we can see why -&nbsp;let&nbsp;&nbsp;&nbsp; Source = MySQL.Database("000.000.00.00", "UAT", [ReturnSingleDatabase=true]),&nbsp;&nbsp;&nbsp; Dev_Table1 = Source{[Schema="Dev",Item=" Table1 "]}[Data]in&nbsp;&nbsp;&nbsp; Dev_ Table1&nbsp;The database name has been changed to UAT, but the schema name and table prefix remains on Dev.&nbsp;&nbsp;Just to mention, I am also working with SSRS on this project and can change mysql databases without issue because it does not prefix each table with the database/schema name.With Power BI, however, I have to update each table query.&nbsp;Thanks

anonymous · Answer

Hi gazumpt,
The parameter works without issues when I use SQL Server databases.I made a test using MySQL databases and utilizing parameter, and I get the same error as yours. As Power BI Desktop prefixs each MySQL table with the database/schema name, we are only able to change the code in Advanced Editor using the second alternative in my original post.Thanks,Lydia Zhang

Forum Discussion

MYSQL connection - changing database source requires M query

8 Replies

Related content

Database connectivity.

Change Source Connection on Mirrored Database

Change database port on connection

Notebook connect to Fabric SQL Database

connecting notion database