Initialize database tables in puppet manifest

[jeff1evesque]

We will replace our python database scripts with a puppet manifest database.pp. This file will be called by the corresponding Vagrantfile.

Note: we will need to create a corresponding sql user account.

[jeff1evesque]

We notice that our Ubuntu Server 14.04 installs Mariadb 5.5.44 via apt-get:

vagrant@vagrant-ubuntu-trusty-64:~$ dpkg -l mariadb-server
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name           Version      Architecture Description
+++-==============-============-============-=================================
ii  mariadb-server 5.5.44-1ubun all          MariaDB database server (metapack

Since we've installed puppetlabs-mysql, we need to compare the difference with an installation of the above MariaDB. This can be done by implementing SHOW GLOBAL VARIABLES on the MariaDB instance to check for specific variables that are desired. Specifically, we will be implementing additional features (variables) from MariaDB that vanilla MySQL does not support (in the puppet module).

We can approach comparing the difference between MySQL 5.6, and MariaDB 5.5 by looking at the changelogs between current, and previous versions; checking the GLOBAL VARIABLES; and, checking the documentation to ensure the puppet module can be adjusted for missing features.

[jeff1evesque]

We still need to properly define our python-mariadb bindings. Also, the above comment regarding the comparison of vanilla MySQL, and MariaDB is no longer relevant.

[jeff1evesque]

7b85805: we've removed python ../../app.py from setup_database.pp, since start_webserver.pp will be responsible for starting app.py, when the system starts up.

[jeff1evesque]

0fed2b7, 095619d, 9013a0d, 856a3d9: were all committed incorrectly to this issue, and have been removed via git reset --hard HEAD~3, and git commit --amend -m "#1963: ...".

[jeff1evesque]

We need to move the sequence of setup_database.pp, after start_webserver.pp in our Vagrantfile. Otherwise, setup_tables.py will not have reference to db_query.py, relative to the flask server, app.py.

[jeff1evesque]

Currently our vagrant build, has the following error traceback:

...
==> default: Running provisioner: puppet...
==> default: Running Puppet with setup_database.pp...
==> default: stdin: is not a tty
==> default: Warning: Setting templatedir is deprecated. See http://links.puppetlabs.com env-settings-deprecations
==> default:    (at /usr/lib/ruby/vendor_ruby/puppet/settings.rb:1139:in `issue_deprecation_warning')
==> default: Warning: Could not retrieve fact fqdn
==> default: Warning: Config file /etc/puppet/hiera.yaml not found, using Hiera defaults
==> default: Notice: Compiled catalog for vagrant-ubuntu-trusty-64 in environment production in 4.37 seconds
==> default: Notice: /Stage[main]/Mysql::Server::Install/Package[mysql-server]/ensure: ensure changed 'purged' to 'present'
==> default: Notice: /Stage[main]/Mysql::Server::Config/File[mysql-config-file]/content: content changed '{md5}77f15d6c87f9c136c4efcda072017f71' to '{md5}0574089359c965e31cf588a39c0d446d'
==> default: Notice: /Stage[main]/Mysql::Server::Service/File[/var/log/mysql/error.log]/group: group changed 'adm' to 'mysql'
==> default: Notice: /Stage[main]/Mysql::Server::Root_password/Mysql_user[root@localhost]/password_hash: defined 'password_hash' as '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
==> default: Notice: /Stage[main]/Mysql::Server::Root_password/File[/root/.my.cnf]/ensure: defined content as '{md5}6bfac0e7e9decf8667dfc388be899b33'
==> default: Notice: /Stage[main]/Mysql::Server::Providers/Mysql_user[provisioner@localhost]/ensure: created
==> default: Notice: /Stage[main]/Mysql::Server::Providers/Mysql_database[db_machine_learning]/ensure: created
==> default: Notice: /Stage[main]/Mysql::Server::Providers/Mysql_user[authenticated@localhost]/ensure: created
==> default: Notice: /Stage[main]/Mysql::Server::Providers/Mysql_grant[authenticated@localhost/db_machine_learning.*]/ensure: created
==> default: Notice: /Stage[main]/Mysql::Server::Providers/Mysql_grant[provisioner@localhost/db_machine_learning.*]/ensure: created
==> default: Notice: /Stage[main]/Mysql::Client::Install/Package[mysql_client]/ensure: ensure changed 'purged' to 'present'
==> default: Notice: /Stage[main]/Mysql::Bindings::Python/Package[python-mysqldb]/ensure: ensure changed 'purged' to 'present'
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: Traceback (most recent call last):
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "setup_tables.py", line 71, in <module>
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: cur.execute(sql_statement)
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: self.errorhandler(self, exc, value)
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: raise errorclass, errorvalue
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: _mysql_exceptions.OperationalError: (1142, "REFERENCES command denied to user 'prov
isioner'@'localhost' for table 'tbl_dataset_entity'")
==> default: Error: python setup_tables.py returned 1 instead of one of [0]
==> default: Error: /Stage[main]/Main/Exec[create-database-tables]/returns: change from notrun to 0 failed: python setup_tables.py returned 1 instead of one of [0]
==> default: Notice: Finished catalog run in 14.67 seconds
The SSH command responded with a non-zero exit status. Vagrant
assumes that this means the command failed. The output for this command
should be in the log above. Please read the output to determine what
went wrong.

We also know that the creation of a foreign key constraint requires at least one of the SELECT, INSERT, UPDATE, DELETE, or REFERENCES privileges for the parent table. However, we are not using foreign keys as intended. Rather we are linking tables by common table columns. Therefore, we can remove any creation of foreign keys in setup_tables.py.