How to connect to local MySQL Server 8.0 with DBIish in Perl6

Question

I'm working on a Perl6 project, but having difficulty connecting to MySQL. Even when using the DBIish (or perl6.org tutorial) example code, the connection fails. Any suggestions or advice is appreciated! User credentials have been confirmed accurate too.

I'm running this on Windows 10 with MySQL Server 8.0 and standard Perl6 with Rakudo Star. I have tried modifying the connection string in numerous ways like :$password :password<> :password() etc. but can't get a connection established. Also should note that I have the ODBC, C, C++, and.Net connectors installed.

#!/usr/bin/perl6
use v6.c;
use lib 'lib';
use DBIish;
use Register::User;

# Windows support
%*ENV<DBIISH_MYSQL_LIB> = "C:/Program Files/MySQL/MySQL Server 8.0/liblibmysql.dll"
    if $*DISTRO.is-win;

my $dbh = DBIish.connect('mysql', :host<localhost>, :port(3306), :database<dbNameHere>, :user<usernameHere>, :password<pwdIsHere>) or die "couldn't connect to database"; 
my $sth = $dbh.prepare(q:to/STATEMENT/);
    SELECT *
    FROM users
    STATEMENT

$sth.execute();

my @rows = $sth.allrows();

for @rows { .print }
say @rows.elems;

$sth.finish;
$dbh.dispose;

This should be connecting to the DB. Then the app runs a query, followed by printing out each resulting row. What actually happens is the application hits the 'die' message every time.

Integument

What I don't see here, and which is part of the default setup, is the host: github.com/perl6/DBIish#mysql Might be that the default does not work for Windows, or that simply there's no default. From here: github.com/perl6/DBIish/blob/… the default host is localhost. Does that work on Windows? – Superstratum

Good point, it didn't work with or without and I simply forgot to add it back in. I have tried both :host<localhost> and :host<127.0.0.1> without success. – Integument

Untested, and just a guess, but perhaps %*ENV<DBIISH_MYSQL_LIB> is set too late? Try BEGIN %*ENV<DBIISH_MYSQL_LIB> = "C:/Program Files/MySQL/MySQL Server 8.0/liblibmysql.dll" if $*DISTRO.is-win;. – Matty

@Matty Doesn't seem to make a difference with or without, could the .dll be wrong? I have tried that one and the C connector one – Integument

Answer 1

This is more of a work around, but being unable to use use a DB is crippling. So even when trying to use the NativeLibs I couldn't get a connection via DBIish. Instead I have opted to using DB::MySQL which is proving to be quite helpful. With a few lines of code this module has your DB needs covered:

use DB::MySQL;

my $mysql = DB::MySQL.new(:database<databaseName>, :user<userName>, :password<passwordHere>);
my @users = $mysql.query('select * from users').arrays;

for @users { say "user #$_[0]: $_[1] $_[2]"; }

#Results would be:
#user #1: FirstName LastName
#user #2: FirstName LastName
#etc...

This will print out a line for each user formatted as shown above. It's not as familiar as DBIish, but this module gets the job done as needed. There's plenty more you can do with it to, so I highly recommend reading the docs.

Integument

Answer 2

According to this github DBIish issue 127

The environmental variable DBIISH_MYSQL_LIB was removed. I don't know if anyone brought it back.

However if you add the library's path, and the file is named mysql.dll, it will work. Not a good result for the scientific method.

So more testing is needed - and perhaps

C:\Program Files\MySQL\MySQL Server 8.0\lib>mklink mysql.dll .\libmysql.dll

Oviously you can create your own lib directory and add that to your path and then add this symlink to that directory.

Hope this helps. I've spent hours..

EDIT: Still spending time - accounting later.

Something very transitory is going on. I reset the machine (perhaps always do this from now on), and still got the missing mysql.dll errors. Tried going into the MySQL lib directory to execute raku from there.. worked. changed directories.. didn't work.

Launched administrator cmd - from home directory, tried the raku command. Worked. Ok - not good, but perhaps consistent. Launched non admin cmd, tried it from the MySQL lib directory, worked. And just for giggles, tried it outside of that directory.. worked.

Now I can't get it not to work. Will explore NativeLibs::Searcher as Valle Lukas suggested!

Pinnace

Answer 3

Maybe the example in the dbiish repository is not valid anymore.

The DBIISH_MYSQL_LIB Env seems to be replaced by NativeLibs::Searcher with commit 9bc4191

Looking at NativeLibs::Searcher may help to find the root cause of the problem.

Heppman