I have been struggling to get PSU to work with MySql. Tried some modules via module management, tried to install some things myself.
A constant barrage of issues and errors. So many and so obscure that I feel I was fighting modules and PSU to make things fit. That is not the way.
So instead of me fighting with PSU to get MySql connectivity, I thought to ask here:
What do you use to connect and query MySql in PSU?
Some more info
I tried to do this as simple as I can. I have the DLL for MySql stored in the Modules folder. I have double checked, that file is present.
[system.reflection.Assembly]::LoadFrom("/root/.PowerShellUniversal/Repository/Modules/MySql/MySql.Data.dll")
The results are :
[information] GAC Version Location
[information] --- ------- --------
[information] False v4.0.30319 /root/.PowerShellUniversal/Repository/Modules/InvokeQuer…
When I try to query the object to see what is inside:
[MySql.Data.MySqlClient.MySqlCommand] | gm
[error] Unable to find type [MySql.Data.MySqlClient.MySqlCommand].
When I tried the exact same on the Linux server hosting Docker it works, I get a list with all properties/methods of that type. Note that the DLL is the exact same as loaded inside the container as this is a volume shared with the host.
[system.reflection.Assembly]::LoadFrom('/home/steven/docker/universal/universal-data/.PowerShellUniversal/Repository/Modules/MySql/MySql.Data.dll')
Results:
GAC Version Location
--- ------- --------
False v4.0.30319 /home/steven/docker/universal/universal-data/.PowerShellUniversal/Repository/Modules/MySql/MySql…
When I try to query what is inside the object, I get results as expected:
[MySql.Data.MySqlClient.MySqlCommand] | gm
TypeName: System.RuntimeType
Name MemberType Definition
---- ---------- ----------
AsType Method type AsType()
Clone Method System.Object Clone(), System.Object ICloneable.Clone()
Equals Method bool Equals(System.Object obj), bool Equals(type o)
FindInterfaces Method type[] FindInterfaces(System.Reflection.TypeFilter filter, System.Obj…
FindMembers Method System.Reflection.MemberInfo[] FindMembers(System.Reflection.MemberTy…
GetArrayRank Method int GetArrayRank()
Etc....
So something inside the PSU container is blocking this I think.
Not sure why this would work in Linux. It looks like InvokeQuery is using version 6.9.9 of the MySql.Data.Dll and that version doesn’t support .NET Core\Linux.
The latest version does support .NET Core\Linux.
There is an open issue for this but I am not actually seeing this myself.
It seems like the InvokeQuery author should update the MySql.Data version. You could also try loading the new version directly. That said, I tried running this in the stock docker container for PSU.
Import-Module InvokeQuery
Get-Module
Invoke-MySqlQuery -Sql "Test"
[MySql.Data.MySqlClient.MySqlCommand].GetMembers() | Select Name
This was the output. So it seems like it’s loading properly for me.
[information] ModuleType Version PreRelease Name ExportedCo
[information] mmands
[information] ---------- ------- ---------- ---- ----------
[information] Binary 1.0.2 InvokeQuery {Get-DbPr…
[information] Script 3.8.11 Universal {Connect-…
[error] Unable to connect to any of the specified MySQL hosts.
[information] Name : get_LastInsertedId
[information] Name : get_CommandText
[information] Name : set_CommandText
[information] Name : get_CommandTimeout
[information] Name : set_CommandTimeout
[information] Name : get_CommandType
[information] Name : set_CommandType
[information] Name : get_IsPrepared
[information] Name : get_Connection
[information] Name : set_Connection
[information] Name : get_Parameters
[information] Name : get_Transaction
[information] Name : set_Transaction
[information] Name : get_EnableCaching
[information] Name : set_EnableCaching
[information] Name : get_CacheAge
[information] Name : set_CacheAge
[information] Name : Cancel
[information] Name : CreateParameter
[information] Name : ExecuteNonQuery
[information] Name : ExecuteReader
[information] Name : ExecuteReader
[information] Name : ExecuteScalar
[information] Name : Prepare
[information] Name : BeginExecuteReader
[information] Name : BeginExecuteReader
[information] Name : EndExecuteReader
[information] Name : BeginExecuteNonQuery
[information] Name : BeginExecuteNonQuery
[information] Name : EndExecuteNonQuery
[information] Name : Clone
[information] Name : Dispose
[information] Name : get_UpdatedRowSource
[information] Name : set_UpdatedRowSource
[information] Name : get_DesignTimeVisible
[information] Name : set_DesignTimeVisible
[information] Name : get_Connection
[information] Name : set_Connection
[information] Name : get_Parameters
[information] Name : get_Transaction
[information] Name : set_Transaction
[information] Name : CreateParameter
[information] Name : ExecuteReader
[information] Name : ExecuteReader
[information] Name : ExecuteNonQueryAsync
[information] Name : ExecuteNonQueryAsync
[information] Name : ExecuteReaderAsync
[information] Name : ExecuteReaderAsync
[information] Name : ExecuteReaderAsync
[information] Name : ExecuteReaderAsync
[information] Name : ExecuteScalarAsync
[information] Name : ExecuteScalarAsync
[information] Name : PrepareAsync
[information] Name : DisposeAsync
[information] Name : add_Disposed
[information] Name : remove_Disposed
[information] Name : get_Site
[information] Name : set_Site
[information] Name : Dispose
[information] Name : get_Container
[information] Name : ToString
[information] Name : GetLifetimeService
[information] Name : InitializeLifetimeService
[information] Name : GetType
[information] Name : Equals
[information] Name : GetHashCode
[information] Name : .ctor
[information] Name : .ctor
[information] Name : .ctor
[information] Name : .ctor
[information] Name : LastInsertedId
[information] Name : CommandText
[information] Name : CommandTimeout
[information] Name : CommandType
[information] Name : IsPrepared
[information] Name : Connection
[information] Name : Parameters
[information] Name : Transaction
[information] Name : EnableCaching
[information] Name : CacheAge
[information] Name : UpdatedRowSource
[information] Name : DesignTimeVisible
[information] Name : Connection
[information] Name : Parameters
[information] Name : Transaction
[information] Name : Site
[information] Name : Container
[information] Name : Disposed
Can you let me know which image and host OS you are running?
Hi Adam,
I did indeed replace that DLL for that module but I think there are more issues in that module so I will most probably not use it.
I downloaded ‘mysql-connector-net-8.0.33-noinstall.zip’ and replaced the DLL from InvokeQuery with the one from here. Might be I took the wrong DLL/Zip file?
Anyway, what is the recommended way to access MySql from PSU? Is there such a thing or is it a matter of fighting to make it work?
This is where I am now, with a module called ‘MySql’ can find it on PSGallery.
The module is picked up by PSU and is present in the list of modules in PSU.
This module needs the connector so I copied the DLL from the MySql.Data package:
There is a line that tries to import the MySql.Data but it does not exists in memory of course.
So in ‘MySql.psm1’ I changed line:
$null = [System.Reflection.Assembly]::LoadWithPartialName('MySql.Data')
to
Import-Module "/root/.PowerShellUniversal/Repository/Modules/MySql/MySql.Data.dll"
I also tried to load via these commands:
#[void][system.reflection.Assembly]::LoadFrom("$PSScriptRoot/Modules/MySql/MySql.Data.dll")
#[void][system.reflection.Assembly]::LoadFrom("/root/.PowerShellUniversal/Repository/Modules/MySql/MySql.Data.dll")
I then only do this in a Script in PSU:
Import-Module -Name MySql -Force
Import-Module -Name $PSScriptRoot/Modules/Credentials.psm1 -DisableNameChecking
Get-Module
$mysqlServer = 'mc_mysql'
$mysqlId = 'xxxxxxxx'
$mysqlPassword = 'xxxxxxxxx'
$mysqlPort = 3306
$mysqlDatabase = 'universal'
$Cred = Create-PSCredentials -User $mysqlId -Password $mysqlPassword
Connect-MySqlServer -ComputerName $mysqlServer -Credential $Cred -Database $mysqlDatabase -Port $mysqlPort
The results are:
[error] Could not load file or assembly 'System.Runtime, Version=7.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified.
[information] ModuleType Version PreRelease Name ExportedCo
[information] mmands
[information] ---------- ------- ---------- ---- ----------
[information] Script 0.0 Credentials {Create-P…
[information] Script 2.0 MySql {Add-MySq…
[information] Script 3.8.11 Universal {Connect-…
[error] The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: Cannot find type [MySql.Data.MySqlClient.MySqlConnection]: verify that the assembly containing this type is loaded.
Do I need to take the 6.0 DLL of the package?
Sorry, I forgot to answer this:
I run 3.8.11 of PSU and the host is Alpine
NAME=“Alpine Linux”
ID=alpine
VERSION_ID=3.17.3
PRETTY_NAME=“Alpine Linux v3.17”
Yep. Try using the .NET 6 version. PSU 4 will be .NET 7 but 3 is using .NET 6.
Hi Adam,
Well, I got this to work. I will document what I do to make this work so others might benefit from this.
The key thing to take away from this is:
If you look inside the container and start Powershell you will see that this Powershell 7.3
Any experiment in there is not valid for what PSU is using, you will be testing PS 7.3 with .Net v7
If you start a Terminal inside PSU you will see it is Powershell v7.2 and that is the correct version to base your installation needs on.
So knowing we have PSU v3 and using .Net v6, we need to get the .Net Core version of the MySql Data DLL which is compatible with .Net v6
Here is a location to get that .Net Core version of the DLL:
NuGet Gallery | MySql.Data 8.2.0
https://www.nuget.org/api/v2/package/MySql.Data/8.0.33
Note: the downloaded file ‘mysql.data.8.0.33.nupkg’ can be renamed to ‘mysql.data.8.0.33.zip’ so you can unpack it
Now pay attention to the folder structure inside the ZIP/package, you will see many variations for this Data DLL and you will need to get the ‘MySql.Data.dll’ from the folder ‘net6.0’ inside the ZIP/Package.
Ok, now you have the right DLL. I will assume that the need is to query MySql so we will use the module ‘MySql’ from Github:
Download the package and put the ‘MySql’ folder in your Modules folder on your running instance of PSU. In my case it is a Docker container so I copied the ‘MySql’ folder to “/root/.PowerShellUniversal/Repository/Modules”. Copy the “MySql.Data.dll” from the ZIP/Package to this same folder as well.
Now we need to change the psd1 and the psm1 files from that module package ‘MySql’
Open the files “MySql.psd1” and “MySql.psm1” in an editor and change these like so.
MySql.psd1
Change this line:
RootModule = 'MySql.psm1'
To this:
RootModule = "$PSScriptRoot/MySql.psm1"
MySql.psm1
Change this line:
$null = [System.Reflection.Assembly]::LoadWithPartialName('MySql.Data')
To this:
[void][system.reflection.Assembly]::LoadFrom("$PSScriptRoot/MySql.Data.dll")
If all worked then restart PSU so that this module is picked up automatically by PSU.
Check once PSU is running again in the modules section of PSU:
Platform → Modules
Search Local for “MySql”
You should see the module listed by PSU:
Now to test this out you could create a Script (Automation → Scripts) and use this as content:
Import-Module -Name MySql
[MySql.Data.MySqlClient.MySqlCommand] | gm
If you see a message like:
Cannot find type [MySql.Data.MySqlClient.MySqlConnection]
or:
[error] Could not load file or assembly 'System.Runtime, Version=7.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified.
Then you still have an issue to work out. The first one indicates that the DLL is not loaded, the second message indicates you are using the wrong DLL version (you must use the v6 version of the Data DLL).
If you get some output listing the methods on the Type ‘MySql.Data.MySqlClient.MySqlCommand’ then you are probably in a good state.
Test further with this type of test as content for the Script (replacing the previous test):
Import-Module -Name MySql
$mysqlServer = '<server_address>'
$mysqlId = '<mysql_username>'
$mysqlPassword = '<mysql_password>'
$mysqlPort = 3306
$mysqlDatabase = '<mysql_database>'
$Sql = 'SELECT version()'
[securestring]$secStringPassword = ConvertTo-SecureString $mysqlPassword -AsPlainText -Force
[pscredential]$Cred = New-Object System.Management.Automation.PSCredential ($mysqlId, $secStringPassword)
$Conn = Connect-MySqlServer -ComputerName $mysqlServer -Credential $Cred -Database $mysqlDatabase -Port $mysqlPort
Invoke-MySqlQuery -Connection $Conn -Query $Sql
The results should look something like this:
[information] version()
[information] ---------
[information] 8.0.33
Good luck!