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