Modern MySQL++, MySQL++ v3.2.3

TangentSoft

Forks

… of unknown currency or quality.

Abandoned

Alternatives

Bringing up MySQL (MariaDB) v10.0 on Fedora 23 on an Intel NUC

Components

Configuration

Non-Standard Storage Area

Prepare the new storage area.

$ cat > mysql.semanage << EOF
fcontext -a -t mysqld_db_t "/data/mysql/storage(/.*)?"
EOF
$ sudo semanage -i ./mysql.semanage
$ sudo restorecon -v -v -R /data/mysql
restorecon reset /data/mysql context unconfined_u:object_r:etc_runtime_t:s0->unconfined_u:object_r:default_t:s0
restorecon reset /data/mysql/selinux context unconfined_u:object_r:etc_runtime_t:s0->unconfined_u:object_r:default_t:s0
restorecon reset /data/mysql/storage context unconfined_u:object_r:etc_runtime_t:s0->unconfined_u:object_r:mysqld_db_t:s0
restorecon reset /data/mysql/tmp context unconfined_u:object_r:etc_runtime_t:s0->unconfined_u:object_r:default_t:s0
restorecon reset /data/mysql/tmp/mysql.semanage context unconfined_u:object_r:etc_runtime_t:s0->unconfined_u:object_r:default_t:s0

validate…

$ cat /etc/selinux/targeted/contexts/files/file_contexts.local
# This file is auto-generated by libsemanage
# Do not edit directly.

/data/mysql/storage(/.*)?    system_u:object_r:mysqld_db_t:s0

Configure the new storage area either in /etc/my.cnf or /etc/my.cnf.d/mariadb-server.cnf

$ cat /etc/my.cnf /etc/my.cnf.d/mariadb-server.cnf
<snip/>
[mysqld]
datadir=/data/mysql/storage
<snip/>

SSL Authentication & Authorization

# ssl-cipher is defaulted
ssl-ca = /etc/pki/emerson/databasists/all.crt
# ssl-capath = unused
ssl-cert = /etc/pki/mysql/server.crt
ssl-key = /etc/pki/mysql/server.key

Bringup

$ mysqladmin -u root password a574e703-e87a-4013-8a54-179cfed91809
$ mysql -u root -h localhost -p
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.21-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create user wbaker;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'wbaker'@'%'
->    REQUIRE
->        ISSUER '/C=US/ST=California/L=Palo Alto/O=Baker/OU=Emerson/CN=Baker Emerson Database Authority 1'
-> AND SUBJECT '/C=US/ST=California/L=Palo Alto/O=Baker/OU=Emerson/CN=wbaker/emailAddress=wbaker@emerson.baker.org';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for wbaker@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for wbaker@%                                                                                                                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wbaker'@'%' REQUIRE ISSUER '/C=US/ST=California/L=Palo Alto/O=Baker/OU=Emerson/CN=Baker Emerson Database Authority 1' SUBJECT '/C=US/ST=California/L=Palo Alto/O=Baker/OU=Emerson/CN=wbaker/emailAddress=wbaker@emerson.baker.org' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
MariaDB [(none)]>

Validation

MariaDB [(none)]> show global variables like '%ssl%'; 
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| have_openssl  | YES                                  |
| have_ssl      | DISABLED                             |
| ssl_ca        | /etc/pki/emerson/databasists/all.crt |
| ssl_capath    |                                      |
| ssl_cert      | /etc/pki/mysql/server.crt            |
| ssl_cipher    |                                      |
| ssl_crl       |                                      |
| ssl_crlpath   |                                      |
| ssl_key       | /etc/pki/mysql/server.key            |
+---------------+--------------------------------------+
9 rows in set (0.00 sec)

If have_ssl is DISABLED then the server is compiled with SSL support, but somehow it is not enabled. This can occur (silently) if the server key files are specified, but not readable by the mysql user (e.g. they are owned and/or only readable by root).

$ find /etc/pki/mysql -ls
398268    4 drwxr-xr-x   2 root     root         4096 Nov 25 12:23 /etc/pki/mysql
398270    4 -r--r--r--   1 mysql    mysql        1736 Nov 25 12:22 /etc/pki/mysql/server.crt
398271    4 -r--------   1 mysql    mysql        1679 Nov 25 12:23 /etc/pki/mysql/server.key

$ ls -alsZ /etc/pki/mysql
total 16
4 drwxr-xr-x.  2 root  root  unconfined_u:object_r:cert_t:s0 4096 Nov 25 12:23 .
4 drwxr-xr-x. 12 root  root  system_u:object_r:cert_t:s0     4096 Nov 25 12:26 ..
4 -r--r--r--.  1 mysql mysql system_u:object_r:cert_t:s0     1736 Nov 25 12:22 server.crt
4 -r--------.  1 mysql mysql system_u:object_r:cert_t:s0     1679 Nov 25 12:23 server.key
$ mysql -h perfect
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.21-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 5.5.39-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		3
Current database:	
Current user:		wbaker@vast.sanguine.emerson.baker.org
SSL:			Cipher in use is DHE-RSA-AES256-SHA
Current pager:		less
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.0.21-MariaDB MariaDB Server
Protocol version:	10
Connection:		perfect via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			8 sec

Threads: 1  Questions: 5  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 63  Queries per second avg: 0.625
--------------

MariaDB [(none)]> 

SSL not supported on via localhost

A reminder: the use of SSL is not supported via localhost (the Unix domain socket). The hostname localhost is treated specially and is interpreted to mean the Unix domain socket. The use of SSL for identification (and for security) therefore is only available via TCP.

References

  • SSL System Variables; In MariaDB Documentation; 2013-06?
    • If the server supports SSL connections, will be set to YES, otherwise will be set to NO.
    • If set to DISABLED, the server was compiled with SSL support, but was not started with SSL support (see the mysqld options). See also have_openssl.
  • mysqld Options (full list); In MariaDB Documentation; circa 2010-09.
  • SELinux and MySQL; Jeremy Smyth (Oracle); In Their Blog; 2013-03-22.
  • MySQL Changing Database Location, Configuration Examples; Documentation for Red Hat Enterprise Linux 6.

Actualities

$ df -h
Filesystem                 Size  Used Avail Use% Mounted on
devtmpfs                   7.8G     0  7.8G   0% /dev
tmpfs                      7.9G     0  7.9G   0% /dev/shm
tmpfs                      7.9G  1.1M  7.9G   1% /run
tmpfs                      7.9G     0  7.9G   0% /sys/fs/cgroup
/dev/mapper/perfect-root    24G  1.7G   22G   8% /
tmpfs                      7.9G  4.0K  7.9G   1% /tmp
/dev/sdb1                  477M   98M  351M  22% /boot
/dev/mapper/perfect-home   4.7G   22M  4.5G   1% /home
/dev/mapper/perfect-var     48G  568M   45G   2% /var
/dev/mapper/perfect-local  137G   60M  130G   1% /local
/dev/mapper/bulk-data      1.8T   68M  1.7T   1% /data
sudo dnf install -y mariadb-server
Last metadata expiration check performed 2:30:35 ago on Wed Nov 25 08:28:20 2015.
Dependencies resolved.
=========================================================================================================
 Package                   Arch            Version                      Repository                  Size
=========================================================================================================
Installing:
 mariadb                   x86_64          1:10.0.21-1.fc23             collected-by-file          6.0 M
 mariadb-common            x86_64          1:10.0.21-1.fc23             collected-by-file           74 k
 mariadb-config            x86_64          1:10.0.21-1.fc23             collected-by-file           25 k
 mariadb-errmsg            x86_64          1:10.0.21-1.fc23             collected-by-file          199 k
 mariadb-libs              x86_64          1:10.0.21-1.fc23             collected-by-file          637 k
 mariadb-server            x86_64          1:10.0.21-1.fc23             collected-by-file           18 M
 perl-DBD-MySQL            x86_64          4.033-1.fc23                 collected-by-file          153 k
 perl-DBI                  x86_64          1.633-6.fc23                 collected-by-file          727 k
 perl-Math-BigInt          noarch          1.9997-349.fc23              collected-by-file          188 k

Transaction Summary
=========================================================================================================
Install  9 Packages

Total size: 26 M
Installed size: 132 M
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Installing  : mariadb-config-1:10.0.21-1.fc23.x86_64                                               1/9 
  Installing  : mariadb-common-1:10.0.21-1.fc23.x86_64                                               2/9 
  Installing  : mariadb-errmsg-1:10.0.21-1.fc23.x86_64                                               3/9 
  Installing  : mariadb-libs-1:10.0.21-1.fc23.x86_64                                                 4/9 
  Installing  : mariadb-1:10.0.21-1.fc23.x86_64                                                      5/9 
  Installing  : perl-Math-BigInt-1.9997-349.fc23.noarch                                              6/9 
  Installing  : perl-DBI-1.633-6.fc23.x86_64                                                         7/9 
  Installing  : perl-DBD-MySQL-4.033-1.fc23.x86_64                                                   8/9 
  Installing  : mariadb-server-1:10.0.21-1.fc23.x86_64                                               9/9 
  Verifying   : mariadb-server-1:10.0.21-1.fc23.x86_64                                               1/9 
  Verifying   : mariadb-config-1:10.0.21-1.fc23.x86_64                                               2/9 
  Verifying   : perl-DBD-MySQL-4.033-1.fc23.x86_64                                                   3/9 
  Verifying   : perl-DBI-1.633-6.fc23.x86_64                                                         4/9 
  Verifying   : mariadb-common-1:10.0.21-1.fc23.x86_64                                               5/9 
  Verifying   : mariadb-errmsg-1:10.0.21-1.fc23.x86_64                                               6/9 
  Verifying   : perl-Math-BigInt-1.9997-349.fc23.noarch                                              7/9 
  Verifying   : mariadb-libs-1:10.0.21-1.fc23.x86_64                                                 8/9 
  Verifying   : mariadb-1:10.0.21-1.fc23.x86_64                                                      9/9 

Installed:
  mariadb.x86_64 1:10.0.21-1.fc23                     mariadb-common.x86_64 1:10.0.21-1.fc23            
  mariadb-config.x86_64 1:10.0.21-1.fc23              mariadb-errmsg.x86_64 1:10.0.21-1.fc23            
  mariadb-libs.x86_64 1:10.0.21-1.fc23                mariadb-server.x86_64 1:10.0.21-1.fc23            
  perl-DBD-MySQL.x86_64 4.033-1.fc23                  perl-DBI.x86_64 1.633-6.fc23                      
  perl-Math-BigInt.noarch 1.9997-349.fc23            

Complete!

On using SSL Certificates for auth in MySQL and LAMP in general

Context

mariadb-10.0.20-1.fc21.x86_64
php-5.6.10-1.fc21.x86_64
php-pdo-5.6.10-1.fc21.x86_64

Complaints

On the use MariaDB v10.0 (MySQL)

of localhost
  • MySQL treats localhost as meaning “use the Unix domain socket” rather than “use TCP on the loopback interface.”  This affects the use of SSL.
  • Arguably, one shouldn’t use SSL on the Unix domain socket since that neither more nor less secure than certificates and certificate keys lying around in the filesystem.
of SSL (OpenSSL)
  • MySQL allows SSL certificates to be in PEM format or in TXT format (!!!); the file suffix is irrelevant; contrast with PHP which requires PEM format

On the use of PHP v5.6

of PHP PDO
  • Doesn’t accept \PDO::MYSQL_ATTR_SSL_CAPATH
    • The warning is emitted
      PHP Warning:  no valid certs found cafile stream: filename
  • Does accept \PDO::MYSQL_ATTR_SSL_CA
    • so one must use a bundled certificate file for the certificate path
  • Seems like the issue is that PDO doesn’t communicate down to the openssl layer
of PHP OpenSSL
  • PHP requires SSL certificates to be in PEM format; contrast with MySQL which supports TXT data (!!!) even misnamed into a .pem suffix file)
  • PHP doesn’t support capath (ssl-capath), it only supports cafile (“ssl-ca”); see …/ext/openssl/xp_ssl.c
  • Added openssl_get_cert_locations() in v5.6
    and run php < dump.php (see below)
  • PHP doesn’t support Subject Alternative Name (SAN)
    <quote ref=”cite“>SAN support for PHP will be introduced in PHP 5.6.</quote>
  • PHP runs HTTP over CURL or its own PHP Socket & PHP Streams.

ArrestDB in PHP

  • Stock implementation is alixaxel/ArrestDB
    • does not support null or empty password.
    • does not support IPv6 address syntax.
    • has limited error reporting
      • does not return exception messaging in the REST output
      • returns 503 with no further logging or messaging.

OpenSSL

  • Differences in support levels between PHP and MySQL (MariaDB)

References

Actualities

<?php
try {
    $dbh = new PDO('mysql:host=ipv6-address;port=port;dbname=dbname', dbusername, dbpassword);
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "
";
    die();
}
?>
array(8) {
  ["default_cert_file"]=>
  string(21) "/etc/pki/tls/cert.pem"
  ["default_cert_file_env"]=>
  string(13) "SSL_CERT_FILE"
  ["default_cert_dir"]=>
  string(18) "/etc/pki/tls/certs"
  ["default_cert_dir_env"]=>
  string(12) "SSL_CERT_DIR"
  ["default_private_dir"]=>
  string(20) "/etc/pki/tls/private"
  ["default_default_cert_area"]=>
  string(12) "/etc/pki/tls"
  ["ini_cafile"]=>
  string(0) ""
  ["ini_capath"]=>
  string(0) ""
}

MySQL++ v3.1.0 does not support SSL, but v3.2.2 does

Problem Statement

  • Contact a MySQL; c.f. MariaDB v5.5
  • Use mysql++ for C++
    mysql++-3.1.0-13.fc20.x86_64
  • Use SSL for client auth
  • Fedora 20

Indications

  • mysql++ is not built with SSL support.

Diagnosis

  • MySQL++ SSL Support on Linux; some dude using the self-asserted identity token Ryan; On Stack Overflow; 2011-10-17.
    tl;dr => The MySQL v3.1.0 configure fails to find mysql_ssl_set; the bug is fixed in MySQL++ 3.2.0, (claimed) to have been released 2011-10.

Background

Evidences

$ g++ -std=c++11 -o mysqlpp-ssl mysqlpp-ssl.cpp -DMYSQLPP_MYSQL_HEADERS_BURIED $(mysql_config --cflags --libs) -lmysqlpp
$ ./mysq2lpp-ssl
./mysq2lpp-ssl: error, bad option 'Option not supported by database driver v5.5.41-MariaDB'
#include "mysql++/mysql++.h"
#include <iostream>

namespace {
  char const *const NAME = "mysq2lpp-ssl";
}
auto main(int argc, char *argv[]) -> int {
  try {
    char const *const database = "mysql";
    char const *const hostname = "db.example.com";
    char const *const username = "wbaker";
    char const *const password = "password";
    char const *const key = "/etc/pki/myclient/key.pem";
    char const *const cert = "/etc/pki/myclient/cert.pem";
    char const *const ca = nullptr;
    char const *const capath = "/etc/pki/myclient/ca";
    char const *const cipher = nullptr;
    mysqlpp::Connection conn;
    conn.set_option(new mysqlpp::SslOption(key, cert, ca, capath, cipher));
    if ( !conn.connect(database, hostname, username, password) || !conn.connected()) {
      throw mysqlpp::ConnectionFailed("because");
    }
    std::cout << "OK!\n";
    return 0;
  } catch (mysqlpp::BadOption const &e) {
    std::cerr << NAME << ": error, bad option '" << e.what() << "'\n";
  }
  return 1;
}
$ mock --rebuild ~/Downloads/mysql++-3.2.2-1.src.rpm
...todo...

Using SSL with MariaDB (MySQL)


# Create clean environment
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts

# Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 3600 \
         -key ca-key.pem -out ca.pem

# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
         -nodes -keyout server-key.pem -out server-req.pem
shell> openssl rsa -in server-key.pem -out server-key.pem
shell> openssl x509 -req -in server-req.pem -days 3600 \
         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 \
         -nodes -keyout client-key.pem -out client-req.pem
shell> openssl rsa -in client-key.pem -out client-key.pem
shell> openssl x509 -req -in client-req.pem -days 3600 \
         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

mysql> show global variables like '%ssl%'; 
+---------------+---------------------------------------+
| Variable_name | Value                                 |
+---------------+---------------------------------------+
| have_openssl  | YES                                   |
| have_ssl      | YES                                   |
| ssl_ca        | /etc/pki/mysql/root/ca-bundle.pem     |
| ssl_capath    |                                       |
| ssl_cert      | /etc/pki/mysql/server/cert.pem        |
| ssl_cipher    |                                       |
| ssl_key       | /etc/pki/mysql/server/key.pem         |
+---------------+---------------------------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 5.5.41-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		5
Current database:	
Current user:		wbaker@devbox.example.com
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		5.5.41-MariaDB MariaDB Server
Protocol version:	10
Connection:		mysql.example.com via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			8 min 50 sec

Threads: 1  Questions: 10  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 26  Queries per second avg: 0.018
--------------

GRANT ALL PRIVILEGES ON test.* TO 'someuser'@'somehost'
  REQUIRE
      ISSUER '/C=US/ST=RedState/L=Thistown/O=MySQL Trust Authority 99/CN=The Man/emailAddress=ca@example.com'
  AND SUBJECT '/C=US/ST=BlueState/L=Thattown/O=ACME Widgets/CN=John Doe/emailAddress=john.doe@example.com';

MySQL++ v3.2.2 User Manual

Documents

Mentions

  • Specialized SQL Structures (SSQLS)

Classes

SOLVED: Which MySQL users have access to the database?

show databases;
SELECT * FROM mysql.db WHERE db = 'database_name';
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;

Example

select * from mysql.db where db = 'specimen';
+---------------------------------+-----------+----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host                            | Db        | User     | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+---------------------------------+-----------+----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost                       | specimen  | role     | Y           | Y           | Y           | Y           | Y           | Y         | Y          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| localhost                       | specimen  | wbaker   | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| host.emerson.baker.org          | specimen  | wbaker   | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
+---------------------------------+-----------+----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
3 rows in set (0.00 sec)

References

SOLVED: MySQL command ‘describe TABLE’ fails with ERROR 1 (HY000): Can’t create/write to file ‘/tmp/#sql_718d_0.MYI’ (Errcode: 2)

Diagnostic

mysql> describe TABLE;
ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_718d_0.MYI' (Errcode: 2)

Remediation

Restart mysqld, which will create its own temporary areas upon startup

systemctl restart mysqld.service

Create a tmpfiles specification that preserves the temporary spaces

$ cd /etc/tmpfiles.d
$ sudo vi mysql.conf
$ cat mysql.conf 
x /tmp/systemd-namespace-* x /tmp/systemd-namespace-*/private

Modify in place the cron entry for tmpwatch in /etc/cron.daily/tmpwatch to preserve the temporary spaces

$ cat /etc/cron.daily/tmpwatch 
#! /bin/sh
flags=-umc
/usr/sbin/tmpwatch "$flags" -x /tmp/.X11-unix -x /tmp/.XIM-unix \
	-x /tmp/.font-unix -x /tmp/.ICE-unix -x /tmp/.Test-unix \
	-X '/tmp/systemd-namespace*' \
	-X '/tmp/hsperfdata_*' 10d /tmp
/usr/sbin/tmpwatch "$flags" 30d /var/tmp
for d in /var/{cache/man,catman}/{cat?,X11R6/cat?,local/cat?}; do
    if [ -d "$d" ]; then
	/usr/sbin/tmpwatch "$flags" -f 30d "$d"
    fi
done

Explanation

MySQL uses a temporary directory within /tmp that is only visible to mysqld.  If this directory is not used enough then it is removed by either or both tmpwatch or systemd-tmpfiles.

Background

$ grep names /proc/22489/mountinfo 
148 104 253:2 /tmp/systemd-namespace-UAhzsT/private /tmp rw,relatime - ext4 /dev/mapper/vg_hangie-lv_root rw,seclabel,data=ordered

Referenced

Recipe for subversion-to-git for mysqlpp

Whereas

mysql++ has some minor issues, it is time to develop & present the remediations.

Previously

For mod-ndb we had Recipe for subversion-to-git for mod-ndb
For dbd-modules we had git svn, svnsync | Git and Subversion

Current

mysql++ seems to be mastered at Gna!

Recipe

$ cd /vault/git/svn
$ svnadmin create mysqlpp
$ cat > mysqlpp/hooks/pre-revprop-change
#!/bin/sh
exit 0;
^D
$ chmod +x mysqlpp/hooks/pre-revprop-change
$ svnsync init file:///vault/git/svn/mysqlpp http://svn.gna.org/svn/mysqlpp
Copied properties for revision 0.

$ svnsync sync file:///vault/git/svn/mysqlpp 
... hundreds of lines ...

$ cd /vault/git/clones
$ git svn clone file:///vault/git/svn/mysqlpp -T trunk -b branches -t tags

Also, -s is the same as -T trunk -b branches -t tags

HOWTO Move a WordPress Blog to a New Domain, refinements to the recipe

Previously: On Moving a WordPress Blog to a New Domain, 2013-01-02.
Especially

On Moving a WordPress Blog to a New Domain

Necessary but not sufficient advice.

What is missing in this recipe is that the graphical content, hyperlinks and banners which are referenced within the blog are not updated to point to the new domain. It seems there are (at least) two cases. The outline here is not fully general but was sufficient for the purposes at hand

Task

Move the blog at admin.emerson.baker.org; to administration.emerson.baker.org

Procedure

  1. Execute the recipe shown above as the admin user.
  2. Clean up the remaining internal pointers the banners in the wp_options table.
  3. Clean up the remaining internal pointers in hyper links within the article content.

Indications

$ rpm -q wordpress mysql
wordpress-3.4.2-2.fc16.noarch
mysql-5.5.28-1.fc16.i686
$ cat /etc/fedora-release
Fedora release 16 (Verne)

Actualities

Substantially what we’re looking to do here is the moral equivalent of this perl code:

s!//admin.emerson.baker.org!//administration.emerson.baker.org!g;

However we need this code executed on every field of every row of every table in the WordPress MySQL database. There does not seem to be an obvious way to do that, so we approximate and guess which tables and which fields need modification.

$ mysql -u wordpress -p
mysql> show tables;
+-----------------------------------+
| Tables_in_org_baker_emerson_admin |
+-----------------------------------+
| wp_commentmeta                    |
| wp_comments                       |
| wp_links                          |
| wp_options                        |
| wp_postmeta                       |
| wp_posts                          |
| wp_term_relationships             |
| wp_term_taxonomy                  |
| wp_terms                          |
| wp_usermeta                       |
| wp_users                          |
+-----------------------------------+
11 rows in set (0.00 sec)

Articles

How bad are the articles?

mysql> select id from wp_posts where post_content like '%//admin.emerson%';
+----+
| id |
+----+
|  4 |
|  5 |
|  6 |
| 13 |
| 16 |
| 20 |
| 27 |
| 42 |
| 79 |
| 81 |
| 82 |
| 83 |
| 84 |
+----+
13 rows in set (0.01 sec)

Remediation

The articles will have to be cleaned up by hand in the WordPress in-browser editor..

Options

The options includes the configurations for the banners and other internal eye candy.

mysql> select option_id, option_name from wp_options where option_value like '%//admin.emerson%';
+-----------+--------------------------------------------------+
| option_id | option_name                                      |
+-----------+--------------------------------------------------+
|       113 | dashboard_widget_options                         |
|       161 | theme_mods_twentyeleven                          |
|       581 | _transient_dash_20494a3d90a6669585674ed0eb8dcd8f |
+-----------+--------------------------------------------------+
3 rows in set (0.01 sec)

mysql> describe wp_options;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| option_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| option_name  | varchar(64)         | NO   | UNI |         |                |
| option_value | longtext            | NO   |     | NULL    |                |
| autoload     | varchar(20)         | NO   |     | yes     |                |
+--------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

The options are stored as a giant JSON-flavored TLV blob. Watch out for the very very long lines.

mysql> select option_value from wp_options where option_id = 161;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| option_value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a:3:{s:12:"header_image";s:91:"http://admin.emerson.baker.org/wp-content/uploads/2013/01/cropped-2832-Emerson-Street-3.png";s:17:"header_image_data";O:8:"stdClass":5:{s:13:"attachment_id";i:6;s:3:"url";s:91:"http://admin.emerson.baker.org/wp-content/uploads/2013/01/cropped-2832-Emerson-Street-3.png";s:13:"thumbnail_url";s:91:"http://admin.emerson.baker.org/wp-content/uploads/2013/01/cropped-2832-Emerson-Street-3.png";s:6:"height";i:287;s:5:"width";i:1000;}s:18:"nav_menu_locations";a:1:{s:7:"primary";i:0;}} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Remediation

Being careful to the string lengths when the string lengths change. In this case, the length of the URLs changes from 91 characters to 100 characters.

mysql> update wp_options set option_value = 'a:3:{s:12:"header_image";s:100:"http://administration.emerson.baker.org/wp-content/uploads/2013/01/cropped-2832-Emerson-Street-3.png";s:17:"header_image_data";O:8:"stdClass":5:{s:13:"attachment_id";i:6;s:3:"url";s:100:"http://administration.emerson.baker.org/wp-content/uploads/2013/01/cropped-2832-Emerson-Street-3.png";s:13:"thumbnail_url";s:100:"http://administration.emerson.baker.org/wp-content/uploads/2013/01/cropped-2832-Emerson-Street-3.png";s:6:"height";i:287;s:5:"width";i:1000;}s:18:"nav_menu_locations";a:1:{s:7:"primary";i:0;}}' where 161 = option_id;
Query OK, 1 row affected (0.53 sec)
Rows matched: 1  Changed: 1  Warnings: 0

dotCloud

About

Concept

  • Product: Docker
  • Linux Container Engine (LCE)
    • not a virtual machine solution
  • Requires special kernel versions (to run the container)
  • Docker and Red Hat have had incompatible versions of the Linux kernel (hence the collaboration announcement)

Future

Support roadmap (all future tense)

  • (Docker will be supported on …)
    • Fedora => soon
    • OpenShift => subsequently
    • Red Hat Enterprise Linux (RHEL) => eventually.
  • Introduction of libvirt for container interface management
  • Remove Docker’s dependency on AuFS
    • Something vague about a “new approach to provisioning” based on device mapper.
  • Harmonize Docker and OpenShift Gears
  • Docker with OpenShift’s cartridge model for application orchestration.

Outreach

Exemplars & Projects

Who

  • Solomon Hykes, founder
  • Ben Golub, CEO

Funding

Mentions

  • Joe Fernandes, OpenShift product management, Red Hat.
  • Requires “container” support in kernel; conceptually supported as

Promotion

Related

Via: backfill

Bring up WordPress on Fedora 17

Design

  • apache httpd runs as user apache:apache
  • /var/wordpress/NEWBLOG contains the blog
  • /etc/wordpress/NEWBLOG.php contains the blog configuration (wp-config.php)
  • /var/http/VIRTHOST contains other virtual hosts served by apache httpd
  • /var/www contains the original apache httpd content area (with its SELinux labels)

SELinux Considerations

  • You will have issues as you are installing in nonstandard places.
  • See the recipe herein.
  • Stick with it, you want the protection

Known Problems

Bug 891764 php-simplepie 1.3.1 breaks WordPress

Install Packages

n.b. this may not be the minimal set

  • (sudo) yum install -y mysql-libs mysql mysql-server >& o.yum_install.out
  • (sudo) yum install -y php-{cli,xml,gd,IDNA_Convert,soap,pdo,mysql,simplepie,common} >& o.yum_install.out
  • (sudo) yum install -y wordpress wordpress-plugin-defaults wordpress-plugin-bad-behavior >& o.yum_install.out

Bring up MySQL

  • (sudo) systemctl enable mysqld.service
  • (sudo) systemctl start mysqld.service
  • mysqladmin -u root password $uuid1
  • mysql -u root -p
    • supply password $uuid1
    • create user wordpress identified by '$uuid2';
    • select password('$uuid2');
    • create database NEWBLOG;
    • grant all privileges on NEWBLOG.* to wordpress@localhost identified by password 'hashed-uuid2';
    • quit

Install & Configure WordPress

  • (sudo) mkdir /var/wordpress
  • cd /var/wordpress
  • (sudo) cp -rpc /usr/share/wordpress NEWBLOG/.
    • -r is recursive
    • -p is preserve permissions
    • -c is preserve SELinux context labels
  • (sudo) chown -R apache:apache NEWBLOG/.
  • cd NEWBLOG
  • (sudo) rm wp-config.php
  • (sudo) ln -s ../../../etc/wordpress/NEWBLOG.php wp-config.php
  • (sudo) vi /etc/wordpress/NEWBLOG.php

Hack the Permissions

Permissions on /etc/wordpress

$ ls -la /etc/wordpress
total 24
drwxr-xr-x.   2 root root  4096 Jan 10 16:15 .
drwxr-xr-x. 137 root root 12288 Jan 10 15:01 ..
-rw-r--r--.   1 root root  3178 Jan 10 16:15 NEWBLOG.php
-rw-r--r--.   1 root root  3177 Dec 12 05:55 wp-config.php

$ sudo chown -R apache:apache /etc/wordpress

$ sudo chmod o-rx -R /etc/wordpress 

$ ls -la /etc/wordpress
ls: cannot open directory /etc/wordpress: Permission denied

$ sudo ls -la /etc/wordpress
total 24
drwxr-x---.   2 apache apache  4096 Jan 10 16:15 .
drwxr-xr-x. 137 root   root   12288 Jan 10 15:01 ..
-rw-r-----.   1 apache apache  3178 Jan 10 16:15 NEWBLOG.php
-rw-r-----.   1 apache apache  3177 Dec 12 05:55 wp-config.php

SELinux Labels on /var/wordpress/NEWBLOG

The following label patterns need to be available

semanage -i - <

Then the tree needs to be “relabelable” so that in case a restorecon action happens, the system isn’t broken. So in concept:

  • copy_context “NEWBLOG
  • semanage_patterns “NEWBLOG
  • relabel_tree “NEWBLOG

The packaged script is fixup. To use

    1. (sudo) mkdir /var/wordpress/selinux
    2. download it to /var/wordpress/selinux/fixup
    3. (sudo) chmod a+x /var/wordpress/selinux/fixup
    4. cd /var/wordpress
    5. (sudo) selinux/fixup NEWBLOG

Once done, see

      • /var/wordpress/NEWBLOG/o.chcon.out
      • /var/wordpress/NEWBLOG/o.semanage.out
      • /var/wordpress/NEWBLOG/o.restorecon.out

Once done, inspect the SELinux label patterns:

$ sudo semanage -o -
boolean -D
login -D
login -a -s unconfined_u -r 's0-s0:c0.c1023' __default__
login -a -s unconfined_u -r 's0-s0:c0.c1023' root
login -a -s system_u -r 's0-s0:c0.c1023' system_u
user -D
port -D
interface -D
node -D
fcontext -D
fcontext -a -f 'all files' -t httpd_sys_rw_content_t '/var/wordpress/NEWBLOG/.htaccess'
fcontext -a -f 'all files' -t httpd_sys_rw_content_t '/var/wordpress/NEWBLOG/wp-content'
fcontext -a -f 'all files' -t httpd_sys_rw_content_t '/var/wordpress/NEWBLOG/wp-content/blogs.dir(/.*)?'
fcontext -a -f 'all files' -t httpd_sys_rw_content_t '/var/wordpress/NEWBLOG/wp-content/cache(/.*)?'
fcontext -a -f 'all files' -t httpd_sys_rw_content_t '/var/wordpress/NEWBLOG/wp-content/plugins(/.*)?'
fcontext -a -f 'all files' -t httpd_sys_rw_content_t '/var/wordpress/NEWBLOG/wp-content/themes(/.*)?'
fcontext -a -f 'all files' -t httpd_sys_rw_content_t '/var/wordpress/NEWBLOG/wp-content/upgrade(/.*)?'
fcontext -a -f 'all files' -t httpd_sys_rw_content_t '/var/wordpress/NEWBLOG/wp-content/uploads(/.*)?'
fcontext -a -f 'all files' -t httpd_sys_rw_content_t '/var/wordpress/NEWBLOG/wp-content/wp-cache-config.php'

Configuring Apache httpd

In /etc/httpd/conf/httpd.conf is the declaration of name-based virtual hosting:

NameVirtualHost *:80
Include vhost/*.conf
<VirtualHost *:80>
    ServerAdmin webmaster@localhost
    DocumentRoot /var/www/html
    ServerName _default_
    ServerAlias *
    ErrorLog logs/error_log
    CustomLog logs/access_log common
</VirtualHost>

The NEWBLOG entry is constructed with a ServerAlias such that it will always match, and since it appears “First” in the ordering of the VirtualHost declarations, then it will be the only match. The default will never match. And in /etc/httpd/vhost/NEWBLOG.conf is the declaration:

<VirtualHost *:80>
    ServerAdmin webmaster@localhost
    DocumentRoot /var/wordpress/NEWBLOG
    ServerName _dummy_
    ServerAlias *
    ErrorLog logs/NEWBLOG/error_log
    CustomLog logs/NEWBLOG/access_log common
</VirtualHost>

Be sure to create the log file directory

  • (sudo) mkdir /var/log/httpd/NEWBLOG

(re)Start the server

$ sudo systemctl restart httpd.service

Installing and operating Xibo on Fedora 17

tl;dr

  • server  => “easy as WordPress”
  • client => you’re on your own with GPU-level source code assembly.

Who: Xibo

Server

  • The server install is pretty walk-through easy
  • You install and build on-site from source (a copied php tree).
  • As such there are SELinux issues because there are no appropriate labels
    • but there is a semanage fcontext pattern set herein.

Robustness

  • The server install workflow self-checks for errors.
  • It rechecks the self-checks to ensure that changes didn’t occur with the install flow.
  • It conveniently restarts the install flow again if it didn’t work.

Client

  • Requires lots of work; Ubuntu 10.04 is the “no work” path
    • The “source code” distributes with a prebuilt libbrowsernode.so
    • Only available/tested on 32-bit OSes (Ubuntu 10.04 32-bit)
  • Has a python installer
    • sudo bash .../xibo-1.4.0-ubuntu.all-pyclient.sh
  • Has low-level hardware compatibility issues
  • libbrowsernode is barely supported

Architecture

Clients are (stationary, static) venues connect to the server which contains the media schedule and the media library.  Venues are only defined in this dynamic sense by the act of a client connection. Once a client connects, it can operate in a sporadic offline mode.  If there are no clients connecting, yet or ever, there are no venues.  Thus there is no way to prepopulate the definition and schedule for a media campaign.  Venues are expected to be fullscreen (i.e. this isn’t an in-browser type ad server).

Server (Admin UI and Media Database)

  • LAMP

Client (Venue Player)

  • Bespoke with direct GPU rendering
    • i.e. not Firefox in Kiosk mode
    • i.e. not mplayer
    • i.e. not anything else easy.
    • There are no other client options besides direct GPU hacking
  • Components
  • libbrowsernode Build Instructions

Cultural Fit

The Xibo system seems tuned for Unixalike-on-Windows with a .NET base and Python plus XAMPP being a suggested variant stack; and thence backported into Ubuntu with a straight-up LAMP stack as  a secondary option. Subtly this means that Fedora should work but there will be “issues.” There is some wording that there are Windows-specific and Ubuntu-specific components that are necessary for the client player.

Details

See the pages on Xibo