================================================================================
================================ Install mysql =================================
================================================================================
$ pkg install database/mysql-57 database/mysql-57/client
================================================================================
========================== Init database & run daemon ==========================
================================================================================
set -x
MYSQL_TEST_DATADIR=/var/tmp/mysql
MYSQL_VERSION=5.7
MYSQL_BINDIR=/usr/mysql/$MYSQL_VERSION/bin
MYSQL_TEST_USER=root
MYSQL_TEST_PASSWORD=new-password
rm -rf "$MYSQL_TEST_DATADIR"
"$MYSQL_BINDIR/mysqld" \
--initialize-insecure \
--datadir="$MYSQL_TEST_DATADIR" \
--basedir=/usr/mysql/$MYSQL_VERSION
# Run the daemon in background.
# --gdb makes it possible to terminate mysqld via Ctrl+C
"$MYSQL_BINDIR/mysqld" \
--skip-networking \
-u "$MYSQL_TEST_USER" \
--datadir="$MYSQL_TEST_DATADIR" \
--socket="$MYSQL_TEST_DATADIR"/socket \
--pid-file="$MYSQL_TEST_DATADIR"/pid \
--gdb &
sleep 10 # wait for db to come up
--socket "$MYSQL_TEST_DATADIR"/socket \
-u "$MYSQL_TEST_USER" \
password "$MYSQL_TEST_PASSWORD"
--socket "$MYSQL_TEST_DATADIR"/socket \
-u "$MYSQL_TEST_USER" \
--password="$MYSQL_TEST_PASSWORD" \
create test
================================================================================
============ In perl craete DB, table, trigger and insert some data ============
================================================================================
#!/usr/perl5/5.22/bin/perl
use DBI;
my $dbname="perl_test_db";
$dsn = "DBI:mysql:;mysql_socket=/var/tmp/mysql/socket;";
$dbh = DBI->connect($dsn, 'root', 'new-password');
# List all databases available
say STDOUT join " : ", $dbh->func('_ListDBs');
# Call mysql specific function
bless ($dbh, "DBD::mysql::db");
$dbh->admin('createdb', $dbname);
# Connect again, this time directly to DB
$dsn = "DBI:mysql:$dbname;mysql_socket=/var/tmp/mysql/socket;";
$dbh = DBI->connect($dsn, 'root', 'new-password');
eval { $dbh->do("DROP TABLE IF EXISTS foo") };
$dbh->do("CREATE TABLE foo (id INTEGER, name VARCHAR(20))");
$dbh->do("
CREATE TRIGGER only_even
BEFORE INSERT ON foo FOR EACH ROW
BEGIN
IF NEW.id % 2 = 1
THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insert only odd numbers!';
ELSE
END IF;
END;
");
# First fails, it's odd number
$dbh->do("INSERT INTO foo VALUES (1, " . $dbh->quote("Tim") . ")");
$dbh->do("INSERT INTO foo VALUES (?, ?)", undef, 2, "Jochen");
$dbh->do("INSERT INTO foo VALUES (4, " . $dbh->quote("Pete") . ")");
my $sth = $dbh->prepare("SELECT * FROM foo");
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n";
}
$sth->finish();
$dbh->disconnect();
================================================================================
============================== Perl script output ==============================
================================================================================
information_schema : mysql : performance_schema : test
DBD::mysql::db do failed: Insert only odd numbers! at ./a.pl line 39.
Found a row: id = 10, name = Jochen
Found a row: id = 20, name = Pete
(please note that first line may vary, it just lists all currently available
databases)
================================================================================
===================== Verify from shell the data are there =====================
================================================================================
$ echo 'select * from foo;' |
mysql --socket=/var/tmp/mysql/socket -u root --password=new-password perl_test_db
Warning: Using a password on the command line interface can be insecure.
id name
10 Jochen
20 Pete